An on-line transaction processing and on-line analytical processing hybrid system and a control method thereof

Abstract

The application provides an online transaction processing and online analytical processing hybrid system and a control method thereof, characterized in that the system comprises an intelligent routing module, a protocol compatible module, a real-time translator, a virtualization simulation module, a data synchronization engine and an embedded execution module; compatibility of the MySQL protocol, real-time translation of SQL dialect and the built-in data synchronization engine are realized in a single machine process, so that a user can obtain a hundred-fold analysis performance improvement brought by columnar storage at a very low hardware cost without modifying any code or deploying an ETL tool.

Description

Technical Field

[0001] This invention relates to the technical field of database systems, and specifically to a hybrid system of online transaction processing and online analytical processing, and its control method. Background Technology

[0002] In the field of modern data processing, database systems are generally divided into two main categories: Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP). OLTP systems (such as MySQL) use row-based storage and excel at handling small, high-concurrency transactions; OLAP systems (such as ClickHouse and DuckDB) use column-based storage and excel at handling aggregation analysis of large-scale data. With business growth, users increasingly need to meet the requirements of transaction processing and real-time analysis within the same system, i.e., Hybrid Transaction and Analytical Processing (HTAP).

[0003] In existing technologies, a common solution for implementing HTAP is a "storage-compute separation + ETL architecture". Users store transaction data in MySQL at the business layer, parse binary logs using tools such as Canal or Debezium, and then transmit the data to Hadoop or a dedicated columnar database (such as ClickHouse) for analysis via a Kafka message queue. While this solution is technically mature, it involves numerous system components and complex link maintenance, increasing not only potential points of failure but also significantly raising the manpower costs and technical barriers to operation and maintenance.

[0004] in, Canal provides incremental data subscription and consumption based on incremental log parsing of MySQL databases; Debezium is a CDC (ChangeDataCapture) tool that works similarly to Canal, DataBus, and Maxwell by extracting changes from database logs. Kafka is a distributed messaging system that can process large amounts of data in real time to meet various needs and scenarios. Hadoop is an open-source software framework used to store and process large amounts of data in a distributed computing environment. It allows for parallel processing of large datasets while handling big data.

[0005] Another existing technology is to use a distributed HTAP database (such as TiDB). This type of system uses the Raft protocol for data synchronization by simultaneously deploying row-store nodes (TiKV) and column-store nodes (TiFlash) in a cluster. While enabling real-time analytics, this distributed architecture is extremely complex to deploy and has a high minimum requirement for hardware resources (such as CPU cores, memory capacity, and network bandwidth), making it unsuitable for deployment on edge devices, standalone environments, or lightweight applications.

[0006] Furthermore, many high-performance single-machine columnar databases (such as DuckDB), while offering extremely fast analysis speeds, lack support for standard database protocols (such as the MySQL protocol). Most existing BI tools (Tableau, Power BI) and database management software (Navicat) only support the standard MySQL network protocol. This prevents users from directly accessing these high-performance engines using existing toolchains, requiring them to redevelop drivers or middleware, which presents a significant technical barrier.

[0007] In summary, the defects and shortcomings of the existing technology are mainly reflected in: 1) Bulky architecture and difficult maintenance: The ETL (Extract, Transform, Load) process involves multiple components such as MySQL, Canal, and Kafka. Failure in any of these components can lead to data synchronization interruption. For most small and medium-sized enterprises that do not need to handle petabytes (1PB = 1024TB) of data, maintaining such a complex distributed system is a huge waste of resources.

[0008] 2) High resource requirements and performance inversion: Most mainstream OLAP systems (such as ClickHouse and Greenplum) adopt a distributed architecture and are designed to handle petabyte-scale massive data. For most small and medium-sized enterprises (data scale in the GB to TB level), deploying such systems not only faces expensive hardware costs (high-configuration multi-node clusters), but also, in small and medium-sized data scenarios, due to the overhead of distributed coordination, data serialization, and network communication, their query performance is often inferior to that of embedded vectorization engines (such as DuckDB) that are deeply optimized for single machines. This results in a performance inversion phenomenon of "investing expensive resources but obtaining poor performance".

[0009] 3) Protocol incompatibility and toolchain gaps: While engines like DuckDB offer excellent single-machine performance, they typically use proprietary protocols or are used only as embedded libraries, failing to support the standard MySQL network protocol. Users cannot directly connect using existing tools, resulting in extremely high barriers to technology deployment.

[0010] Therefore, there is an urgent need to provide a new solution to address the defects and shortcomings of the existing technologies. Summary of the Invention

[0011] To address the deficiencies and shortcomings of the existing technologies, this invention provides a hybrid system for online transaction processing and online analytical processing, and its control method.

[0012] The specific solution provided by this invention is as follows: A hybrid system for online transaction processing and online analytical processing, characterized in that: the system comprises: The intelligent routing module routes services to the source database and the system respectively based on the service type. The protocol compatibility module is connected to the intelligent routing module to receive corresponding services. Based on the connection request initiated by the client, it generates a corresponding random salt value, constructs a standard initial handshake packet, and sends the constructed standard initial handshake packet to the client. It verifies the actual hash value of the connection request through a preset verification algorithm. After the verification is successful, it assigns a unique connection identifier to the connection and initializes the session context in memory. A real-time translator is connected to a protocol-compatible module to parse the string in the connection request into an abstract syntax tree, and then traverses the nodes of the abstract syntax tree and performs corresponding logical transformations to form the target language. A virtualization simulation module, which is data-connected to a protocol-compatible module, is used to rewrite a target query instruction as a corresponding local metadata query instruction when the target query instruction is detected, and to inject a virtual column into the selection list. The data synchronization engine connects to the source database to automatically check the local storage status when the system starts and pull the existing data according to the local storage status; after the full synchronization is completed, it simulates a preset node to send registration instructions and request instructions to the source database to achieve incremental synchronization; it receives and parses binary log event streams in real time and writes them into columnar storage units through a preset batch writing algorithm. An embedded execution module is connected to both the real-time transcriber and the data synchronization engine to submit the transcribed target language to a database instance within the same process for execution. The execution result is mapped into a column data packet binary file in memory and returned to the client.

[0013] As a further preferred embodiment of the present invention, the intelligent routing module determines the service type based on the statement characteristics in the connection request and routes it to the corresponding location accordingly: When the statement characteristics do not include the corresponding function or operation requirement, the business type is determined to be an online transaction processing business, and it is routed to the source database. When the statement characteristics include corresponding functions or computational requirements, the business type is determined to be an online analytical processing (OLAP) business, and it is routed to this system.

[0014] As a further preferred embodiment of the present invention, in the protocol compatibility module, the standard initial handshake packet includes a simulated server version number and a capability identifier, wherein the capability identifier indicates the corresponding function supported by the standard initial handshake packet.

[0015] As a further preferred embodiment of the present invention, in the protocol compatibility module, the preset verification algorithm includes the following steps: 1) The protocol compatibility module calculates the expected hash value based on the local password and the corresponding salt value; 2) The protocol compatibility module calculates the actual hash value based on the connection request password and the corresponding salt value; 3) Compare the actual hash value with the expected hash value byte by byte: The verification is considered successful if the username exists and the hash values ​​of the two usernames match. The verification fails if the username does not exist, the hash value length does not match, or the hash value content does not match.

[0016] As a further preferred embodiment of the present invention, the real-time transpiler, when parsing the string in the connection request into an abstract syntax tree, and then traversing the nodes of the abstract syntax tree and performing corresponding logical transformations, includes the following steps: 1) Parse the string in the connection request into an abstract syntax tree, and identify the identifier nodes and logical transformation quantities in the string; 2) Traverse the abstract syntax tree, retain the identifier nodes, and perform corresponding logical transformations on the quantities to be logically transformed; 3) Rewrite the rewritten abstract syntax tree into the target language in sequence.

[0017] As a further preferred embodiment of the present invention, the real-time translator performs at least the following corresponding logical transformations on the quantity to be logically transformed: 1) Symbol conversion: Convert all backticks to double quotes; 2) Function mapping: Converts default function names and their calling conventions; 3) System variable interception: Replace query commands with literal constant nodes; 4) Implicit type conversion: Automatically inject CAST nodes in online queries to align types; Furthermore, during a single traversal of the abstract syntax tree, corresponding logical transformations are performed synchronously based on the type of the quantity to be logically transformed.

[0018] As a further preferred embodiment of the present invention, in the data synchronization engine: If it is in the initialization state, a connection to the source database is established, table structure information is read, and multiple concurrent threads are used to pull the existing data. If it is not in the initialization state, it is assumed that the full synchronization has been completed and the existing data will not be fetched again.

[0019] As a further preferred embodiment of the present invention, the data synchronization engine, after full synchronization is completed, simulates a preset node and sends registration instructions and request instructions to the source database to achieve incremental synchronization, including the following steps: 1) First, send a registration instruction to the source database to register itself as a slave database of the source database; 2) Then send a request command to the source database to request the source database to push binary logs starting from the preset location node to achieve incremental synchronization.

[0020] As a further preferred embodiment of the present invention, the data synchronization engine, when receiving and parsing binary log event streams in real time and writing them into columnar storage units through a preset batch write algorithm, includes the following steps: 1) Store scattered binary events in different transactions by classifying them into tables; 2) When the internal storage volume of a transaction reaches a preset value, the transaction containing binary events is written into the columnar storage unit all at once.

[0021] Furthermore, the present invention also provides a control method applied to a hybrid online transaction processing and online analytical processing system, characterized in that the method includes the following steps: S100: The intelligent routing module routes services to the source database and the system respectively based on the service type; S200: The protocol compatibility module receives the corresponding service, generates a corresponding random salt value according to the connection request initiated by the client, constructs a standard initial handshake packet, and sends the constructed standard initial handshake packet to the client; it verifies the actual hash value of the connection request through a preset verification algorithm, and after the verification is successful, it assigns a unique connection identifier to the connection and initializes the session context in memory; S300: The real-time transcriber parses the string in the connection request into an abstract syntax tree, then traverses the nodes of the abstract syntax tree and performs corresponding logical transformations to form the target language; S400: When the virtualization simulation module detects a target query instruction, it rewrites it as the corresponding local metadata query instruction and injects a virtual column into the selection list; S500: The data synchronization engine automatically checks the local storage status when the system starts up and pulls the existing data according to the local storage status; after the full synchronization is completed, it simulates the preset node and sends registration instructions and request instructions to the source database to achieve incremental synchronization; it receives and parses binary log event streams in real time and writes them into columnar storage units through a preset batch writing algorithm. S600: The embedded execution module submits the translated target language to the database instance within the same process for execution. The execution result is mapped into a column data packet binary file in memory and returned to the client.

[0022] Compared with existing technologies, the technical effects that this invention can achieve include: 1) This invention provides a hybrid system for online transaction processing and online analytical processing, and its control method, aiming to solve the problems of high resource consumption and complex deployment in existing distributed solutions, as well as the incompatibility of embedded engines with existing MySQL ecosystem tools. By implementing MySQL protocol compatibility, real-time SQL dialect translation, and a built-in data synchronization engine within a single machine process, users can achieve a hundredfold performance improvement in analytical performance brought by columnar storage with extremely low hardware costs (single machine) without modifying any code or deploying ETL tools.

[0023] 2) This invention provides a hybrid system for online transaction processing and online analytical processing and its control method, achieving extremely low cost and lightweight: no need to deploy a distributed cluster of more than 3 nodes such as Kafka, it can run on a single machine process, with low memory consumption (MB level), perfectly meeting the analysis needs of most small and medium-sized data.

[0024] 3) This invention provides a hybrid system for online transaction processing and online analytical processing and its control method, which can achieve zero migration cost: it is fully compatible with the MySQL protocol, and existing BI reports (Tableau), management tools (Navicat), and business code (JDBC) do not require any modification. Only the connection IP and port need to be changed for seamless access.

[0025] 4) This invention provides a hybrid system for online transaction processing and online analytical processing and its control method, which can realize closed-loop management: the built-in data has synchronization capability, and users can have real-time columnar analysis capability without maintaining complex data pipelines.

[0026] 5) This invention provides a hybrid system for online transaction processing and online analytical processing and its control method, which has higher analytical query performance: by using columnar storage and vectorized execution engine, compared with native MySQL row storage, the performance of complex analytical queries (such as GroupBy, Join, etc.) can be improved by 10-200 times under the same hardware conditions.

[0027] 6) This invention provides a hybrid system for online transaction processing and online analytical processing, and its control method, which possesses multimodal data analysis capabilities: it can not only function as an AP node in an HTAP system to synchronize MySQL data in real time, but also be used as an independent standalone OLAP database. It supports direct mounting and analysis of open format files such as CSV and Parquet, flexibly adapting to various scenarios such as data lake analysis and offline reporting. Attached Figure Description

[0028] Figure 1 The diagram shown is a logical structure diagram of the system provided by the present invention.

[0029] Figure 2 The diagram shown is a flowchart of the steps of the method provided by the present invention. Detailed Implementation

[0030] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0031] In the description of this invention, it should be noted that the terms "upper," "lower," "inner," "outer," "front end," "rear end," "both ends," "one end," and "the other end," etc., indicate the orientation or positional relationship based on the orientation or positional relationship shown in the accompanying drawings. They are used only for the convenience of describing this invention and for simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, or be constructed and operated in a specific orientation. Therefore, they should not be construed as limitations on this invention. Furthermore, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance.

[0032] In the description of this invention, it should be noted that, unless otherwise explicitly specified and limited, the terms "installed," "equipped with," "connected," etc., should be interpreted broadly. For example, "connection" can be a fixed connection, a detachable connection, or an integral connection; it can be a mechanical connection or an electrical connection; it can be a direct connection or an indirect connection through an intermediate medium; it can be a connection within two components. Those skilled in the art can understand the specific meaning of the above terms in this invention based on the specific circumstances.

[0033] [First Embodiment] like Figure 1 The figure shown is a hybrid online transaction processing and online analytical processing system provided in the first embodiment of the present invention. The system includes: The intelligent routing module routes services to the source database and the system based on their type. In this embodiment, the intelligent routing module determines the service type based on the characteristics of the statement in the connection request and routes it to the corresponding location: When the statement characteristics do not include the corresponding function or operation requirement, the business type is determined to be an online transaction processing business, and it is routed to the source database. When the statement characteristics include corresponding functions or computational requirements, the business type is determined to be an online analytical processing (OLAP) business, and it is routed to this system.

[0034] The corresponding functions can be aggregate functions, etc., and the computational requirements can be row count estimation requirements, etc., so as to route high-frequency online transaction processing (OLTP) services to the source database MySQL, while routing complex online analytical processing (OLAP) services to this system.

[0035] like Figure 1 As shown, the system in this embodiment also includes a protocol compatibility module. The protocol compatibility module is connected to the intelligent routing module to receive the corresponding Online Analytical Processing (OLAP) service. In order to be compatible with the standard MySQL client, the protocol compatibility module generates a corresponding random salt value according to the connection request initiated by the client, constructs a standard initial handshake packet, and sends the constructed standard initial handshake packet to the client.

[0036] A corresponding random salt value is generated to facilitate the calculation of the expected and actual hash values ​​for subsequent connection requests. This is a password encryption method compatible with MySQL 8.0 systems. For example, code written using a MySQL 8.0 compatible connection method can be seamlessly connected to this system without modification.

[0037] Constructing and sending a standard initial handshake packet to the client is to declare to the client that the system is compatible with MySQL servers (such as MySQL 8.0) and to declare its supported authentication, compression, SSL protocol and other capabilities, so that existing MySQL clients can connect directly to the driver (Navicat, JDBC, pymysql, etc.) without modification.

[0038] In the protocol compatibility module, the standard initial handshake packet includes a simulated server version number (e.g., "8.0.32") and capability flags. The capability flags indicate the corresponding functions supported by the standard initial handshake packet, such as support for authentication, compression, SSL protocol (encrypted communication, authentication, and data integrity).

[0039] The actual hash value of the connection request is verified by a preset verification algorithm. After successful verification, a unique connection identifier is assigned to the connection, and the session context is initialized in memory to remember the content of the connection, the database selected for the connection, and whether it is read-only, so that subsequent requests on the same long connection do not need to be negotiated again, thus maintaining the continuous service of the same connection.

[0040] In the protocol compatibility module provided in this embodiment, the preset verification algorithm includes the following steps: 1) The protocol compatibility module calculates the expected hash value based on the local password and the corresponding salt value; 2) The protocol compatibility module calculates the actual hash value based on the connection request password and the corresponding salt value; 3) Compare the actual hash value with the expected hash value byte by byte: The verification is considered successful if the username exists and the hash values ​​of the two usernames match. The verification fails if the username does not exist, the hash value length does not match, or the hash value content does not match.

[0041] The purpose of verifying the actual hash value of the connection request using a preset verification algorithm is twofold: firstly, to verify the client's identity and confirm that the client holds a valid password; and secondly, to filter out standard-compatible clients by setting a protocol gate to only accept clients that send hash values ​​according to preset specifications (such as the MySQL specification). It is worth noting that the system provided in this embodiment only compares hash values ​​and does not store plaintext passwords. This is to ensure compatibility with MySQL data, so that the program code does not need to be modified, only the connection address needs to be changed to use the current system.

[0042] For example: First, add a new account via the server; its account information includes: Username=alice; Password=123456; This allows the client to use this account to make connection requests. The password corresponding to the account serves as both the password for this connection request and the local password pre-set within the protocol compatibility module that corresponds to this account.

[0043] When a client connects to the service, the protocol compatibility module generates a corresponding random salt value for the client, such as iokokp. The protocol compatibility module calculates the actual hash value e82ef5a4fcf990713a72f0b45ebf2fa775825d76 based on the connection request password 123456 and the corresponding salt value iokokp using a preset algorithm (algorithm: SHA1(123456) XOR SHA1(iokokp + SHA1(SHA1(123456))). Meanwhile, the protocol compatibility module calculates the expected hash value based on the preset local password 123456 corresponding to the account and the corresponding salt value iokokp: e82ef5a4fcf990713a72f0b45ebf2fa775825d76; The actual hash value and the expected hash value are then compared byte by byte. If the actual hash value and the expected hash value match, the verification is considered successful and the user is allowed to proceed, provided the username exists. If they do not match, the verification is considered unsuccessful and the user is rejected.

[0044] Only the hash value is transmitted throughout the entire process, so that the password 123456 is transmitted but not exposed.

[0045] The system in this embodiment also includes a real-time translator, which is connected to the protocol compatibility module to parse the string in the connection request into an abstract syntax tree, and then traverse the nodes of the abstract syntax tree and perform corresponding logical transformations to form the target language. In the real-time transpiler, the process of parsing the string in the connection request into an abstract syntax tree, and then traversing the nodes of the abstract syntax tree and performing corresponding logical transformations includes the following steps: 1) Parse the string in the connection request into an abstract syntax tree, and identify the identifier nodes and logical transformation quantities in the string; 2) Traverse the abstract syntax tree, retain the identifier nodes, and perform corresponding logical transformations on the quantities to be logically transformed; 3) Rewrite the rewritten abstract syntax tree into the target language in sequence.

[0046] For example, converting the backticks ` in MySQL data into double quotes "". The input for MySQL is: SELECT `name` FROM `users`; The translated output of this system is: SELECT "name" FROM "users"; 1) Parse SELECT `name` FROM `users` into an abstract syntax tree, where name and users are identified as identifier nodes; 2) Traverse the abstract syntax tree, and when an identifier node is encountered, replace the logical conversion variable enclosed in it, i.e., the backticks `, with double quotes "; 3) Rewrite the rewritten abstract syntax tree into the target language output in sequence, i.e., SELECT "name" FROM "users".

[0047] In a real-time translator, the corresponding logical transformations performed on the quantity to be logically transformed include at least the following: 1) Symbol conversion: Convert all backticks to double quotes, for example, convert all backticks (`) in MySQL to double quotes (") in DuckDB; 2) Function mapping: Converting default function names and their calling conventions (such as parameter order), for example, converting JSON_EXTRACT to json_extract, LOCATE to strpos, etc.; to rewrite MySQL functions equivalently to functions in this system.

[0048] For example: Convert the function name LOCATE(substring, original string) to strpos(original string, substring) and change the order of the parameters: Since this system does not have a function name LOCATE, it is necessary to first convert the function to strpos; Then change the parameter order, that is: By SELECT LOCATE('a', `name`) FROM `users`; Convert to SELECT strpos("name", 'a') FROM "users"; 3) System variable interception: Replace query commands with literal constant nodes, for example, replace query commands such as SELECT @@version_comment with literal constant nodes; 4) Implicit type conversion: Automatically inject CAST nodes in online queries to align types; Furthermore, during a single traversal of the abstract syntax tree, corresponding logical transformations are performed synchronously based on the type of the quantity to be logically transformed.

[0049] This is because the client sends an SQL string in MySQL syntax, while the underlying engine (DuckDB) uses a PostgreSQL-style dialect, resulting in differences between the two. Therefore, this embodiment does not use simple regular expression replacement, but instead employs deep translation based on an Abstract Syntax Tree (AST).

[0050] By introducing a dialect parser at the database proxy layer, the SQL statements of the source database (such as MySQL) are parsed into an abstract syntax tree. The nodes are traversed using the visitor pattern, and in-situ replacements or structural reorganizations are performed on identifier reference symbols, specific function calls, and system variable references. Finally, the executable target language, i.e., the SQL statements, of the target database (such as DuckDB) is regenerated.

[0051] like Figure 1As shown, the system in this embodiment also includes a virtualization simulation module. This module is data-connected to the protocol compatibility module to rewrite a target query command as a corresponding local metadata query command when it is detected. Virtual columns are injected into the selection list, such as 'InnoDB' AS Engine, 'Dynamic' AS Row_format, and 10 AS Version. This supports the "Design Table" and "View Object" functions of graphical tools (such as Navicat) by specifically intercepting target query commands such as information_schema. This ensures that client tools recognize the connection as a standard MySQL InnoDB database, thus correctly displaying the table structure and data. By intercepting query commands targeting the information_schema table and constructing subqueries or views containing virtual columns (such as Engine, Version, Row_format, etc.), simulated storage engine compatibility information is returned to the client, thereby supporting the self-checking process of graphical management tools.

[0052] like Figure 1 As shown, the system in this embodiment also includes a data synchronization engine. The data synchronization engine is connected to the source database to automatically check the local storage status when the system starts and pull the existing data according to the local storage status. After the full synchronization is completed, it simulates a preset node and sends registration instructions and request instructions to the source database to achieve incremental synchronization. It receives and parses binary log event streams in real time and writes them into columnar storage units through a preset batch write algorithm to ensure that the analysis data of online analytical processing (OLAP) can maintain near real-time consistency with the business data of online transaction processing (OLTP) at the millisecond or second level. In the data synchronization engine provided in this embodiment, the data synchronization engine is connected to the source database to automatically check the local storage status when the system starts up, and to retrieve stored data according to the local storage status: If it is in the initialization state, a connection to the source database is established, table structure information is read, and multiple concurrent threads are used to pull the existing data. If it is not in the initialization state, it is assumed that the full synchronization has been completed and the existing data will not be fetched again.

[0053] Simultaneously, after full synchronization is completed, when simulating a preset node and sending the registration command COM_REGISTER_SLAVE and the request command COM_BINLOG_DUMP to the source database to achieve incremental synchronization, the following steps are included: 1) First, send a registration instruction to the source database to register itself as a slave database of the source database; 2) Send a request command to the source database to request the source database to push binary logs from the preset node position to achieve incremental synchronization.

[0054] In other words, this system first disguises itself as a MySQL slave database: it first sends a registration command COM_REGISTER_SLAVE to the source database to register with the master database, and then sends a request command COM_BINLOG_DUMP to the source database to request the source database to push binary logs from a preset node position, thereby achieving incremental synchronization.

[0055] In addition, the data synchronization engine writes the binary log event streams received and parsed in real time into columnar storage units using a preset batch write algorithm, including the following steps: 1) Store scattered binary events in different transactions by classifying them into tables; 2) When the internal storage volume of a transaction reaches a preset value, the transaction containing binary events is written into the columnar storage unit all at once.

[0056] This reduces write amplification, increases synchronous data throughput, and records the location for subsequent resume transmission.

[0057] The embedded execution module connects to both the real-time transpiler and the data synchronization engine to submit the transpiled SQL string to a database instance within the same process for execution. The execution result is mapped into a column data packet binary file in memory and returned to the client. By using zero-copy and in-process calls, the serialization / deserialization overhead of cross-process and cross-network communication, such as MySQL→Kafka→ClickHouse, is avoided.

[0058] Furthermore, as a further preferred option, the system provided in this embodiment also supports use as a standalone OLAP database. Users can bypass the source database MySQL and directly load external data files such as CSV and Parquet using SQL statements (such as COPY or SELECT...FROM 'file.parquet', etc.) to perform high-performance analysis using the system's built-in columnar engine.

[0059] [Second Embodiment] The second embodiment of the present invention also provides a control method for a hybrid online transaction processing and online analytical processing system mentioned in the first embodiment, comprising the following steps: S100: The intelligent routing module routes services to the source database and the system respectively based on the service type; S200: The protocol compatibility module receives the corresponding service, generates a corresponding random salt value according to the connection request initiated by the client, constructs a standard initial handshake packet, and sends the constructed standard initial handshake packet to the client; it verifies the actual hash value of the connection request through a preset verification algorithm, and after the verification is successful, it assigns a unique connection identifier to the connection and initializes the session context in memory; S300: The real-time transcriber parses the string in the connection request into an abstract syntax tree, then traverses the nodes of the abstract syntax tree and performs corresponding logical transformations to form the target language; S400: When the virtualization simulation module detects a target query instruction, it rewrites it as the corresponding local metadata query instruction and injects a virtual column into the selection list; S500: The data synchronization engine automatically checks the local storage status when the system starts up and pulls the existing data according to the local storage status; after the full synchronization is completed, it simulates the preset node and sends registration instructions and request instructions to the source database to achieve incremental synchronization; it receives and parses binary log event streams in real time and writes them into columnar storage units through a preset batch writing algorithm. S600: The embedded execution module submits the translated target language to the database instance within the same process for execution. The execution result is mapped into a column data packet binary file in memory and returned to the client.

[0060] It will be apparent to those skilled in the art that the present invention is not limited to the details of the exemplary embodiments described above, and that the invention can be implemented in other specific forms without departing from its spirit or essential characteristics. Therefore, the embodiments should be considered in all respects as exemplary and non-limiting, and the scope of the invention is defined by the appended claims rather than the foregoing description. Thus, all variations falling within the meaning and scope of equivalents of the claims are intended to be included within the present invention. No reference numerals in the claims should be construed as limiting the scope of the claims.

Claims

1. A hybrid system for online transaction processing and online analytical processing, characterized in that: The system includes: The intelligent routing module routes services to the source database and the system respectively based on the service type. The protocol compatibility module is connected to the intelligent routing module to receive corresponding services. Based on the connection request initiated by the client, it generates a corresponding random salt value, constructs a standard initial handshake packet, and sends the constructed standard initial handshake packet to the client. It verifies the actual hash value of the connection request through a preset verification algorithm. After the verification is successful, it assigns a unique connection identifier to the connection and initializes the session context in memory. A real-time translator is connected to a protocol-compatible module to parse the string in the connection request into an abstract syntax tree, and then traverses the nodes of the abstract syntax tree and performs corresponding logical transformations to form the target language. A virtualization simulation module, which is data-connected to a protocol-compatible module, is used to rewrite a target query instruction as a corresponding local metadata query instruction when the target query instruction is detected, and to inject a virtual column into the selection list. The data synchronization engine connects to the source database to automatically check the local storage status when the system starts and pull the existing data according to the local storage status; after the full synchronization is completed, it simulates a preset node to send registration instructions and request instructions to the source database to achieve incremental synchronization; it receives and parses binary log event streams in real time and writes them into columnar storage units through a preset batch writing algorithm. An embedded execution module is connected to both the real-time transcriber and the data synchronization engine to submit the transcribed target language to a database instance within the same process for execution. The execution result is mapped into a column data packet binary file in memory and returned to the client.

2. The hybrid system of online transaction processing and online analytical processing according to claim 1, characterized in that: The intelligent routing module determines the service type based on the characteristics of the statements in the connection request and routes them to the corresponding locations accordingly: When the statement characteristics do not include the corresponding function or operation requirement, the business type is determined to be an online transaction processing business, and it is routed to the source database. When the statement characteristics include corresponding functions or computational requirements, the business type is determined to be an online analytical processing (OLAP) business, and it is routed to this system.

3. The hybrid system of online transaction processing and online analytical processing according to claim 1, characterized in that: In the protocol compatibility module, the standard initial handshake packet includes a simulated server version number and a capability identifier, the capability identifier indicating the corresponding function supported by the standard initial handshake packet.

4. The hybrid system of online transaction processing and online analytical processing according to claim 1, characterized in that: In the protocol compatibility module, the preset verification algorithm includes the following steps: 1) The protocol compatibility module calculates the expected hash value based on the local password and the corresponding salt value; 2) The protocol compatibility module calculates the actual hash value based on the connection request password and the corresponding salt value; 3) Compare the actual hash value with the expected hash value byte by byte: The verification is considered successful if the username exists and the hash values ​​of the two usernames match. The verification fails if the username does not exist, the hash value length does not match, or the hash value content does not match.

5. The hybrid system of online transaction processing and online analytical processing according to claim 1, characterized in that: In the real-time transpiler, when parsing the string in the connection request into an abstract syntax tree, and then traversing the nodes of the abstract syntax tree and performing corresponding logical transformations, the following steps are included: 1) Parse the string in the connection request into an abstract syntax tree, and identify the identifier nodes and logical transformation quantities in the string; 2) Traverse the abstract syntax tree, retain the identifier nodes, and perform corresponding logical transformations on the quantities to be logically transformed; 3) Rewrite the rewritten abstract syntax tree into the target language in sequence.

6. The hybrid system of online transaction processing and online analytical processing according to claim 5, characterized in that: The real-time translator performs at least the following logical transformations on the quantity to be logically transformed: 1) Symbol conversion: Convert all backticks to double quotes; 2) Function mapping: Converts default function names and their calling conventions; 3) System variable interception: Replace query commands with literal constant nodes; 4) Implicit type conversion: Automatically inject CAST nodes in online queries to align types; Furthermore, during a single traversal of the abstract syntax tree, corresponding logical transformations are performed synchronously based on the type of the quantity to be logically transformed.

7. The hybrid system of online transaction processing and online analytical processing according to claim 1, characterized in that: In the data synchronization engine: If it is in the initialization state, a connection to the source database is established, table structure information is read, and multiple concurrent threads are used to pull the existing data. If it is not in the initialization state, it is assumed that the full synchronization has been completed and the existing data will not be fetched again.

8. The hybrid system of online transaction processing and online analytical processing according to claim 1, characterized in that: In the data synchronization engine, after full synchronization is completed, when simulating a preset node and sending registration and request instructions to the source database to achieve incremental synchronization, the following steps are included: 1) First, send a registration instruction to the source database to register itself as a slave database of the source database; 2) Then send a request command to the source database to request the source database to push binary logs starting from the preset location node to achieve incremental synchronization.

9. The hybrid system of online transaction processing and online analytical processing according to claim 1, characterized in that: In the data synchronization engine, when receiving and parsing binary log event streams in real time and writing them into columnar storage units using a preset batch write algorithm, the following steps are included: 1) Store scattered binary events in different transactions by classifying them into tables; 2) When the internal storage volume of a transaction reaches a preset value, the transaction containing binary events is written into the columnar storage unit all at once.

10. A control method, said method being applied to a hybrid online transaction processing and online analytical processing system according to any one of claims 1-9, characterized in that: The method includes the following steps: S100: The intelligent routing module routes services to the source database and the system respectively based on the service type; S200: The protocol compatibility module receives the corresponding service, generates a corresponding random salt value according to the connection request initiated by the client, constructs a standard initial handshake packet, and sends the constructed standard initial handshake packet to the client; it verifies the actual hash value of the connection request through a preset verification algorithm, and after the verification is successful, it assigns a unique connection identifier to the connection and initializes the session context in memory; S300: The real-time transcriber parses the string in the connection request into an abstract syntax tree, then traverses the nodes of the abstract syntax tree and performs corresponding logical transformations to form the target language; S400: When the virtualization simulation module detects a target query instruction, it rewrites it as the corresponding local metadata query instruction and injects a virtual column into the selection list; S500: The data synchronization engine automatically checks the local storage status when the system starts up and pulls the existing data according to the local storage status; after the full synchronization is completed, it simulates the preset node and sends registration instructions and request instructions to the source database to achieve incremental synchronization; it receives and parses binary log event streams in real time and writes them into columnar storage units through a preset batch writing algorithm. S600: The embedded execution module submits the translated target language to the database instance within the same process for execution. The execution result is mapped into a column data packet binary file in memory and returned to the client.

Images