Database and table separation method and device, electronic equipment and storage medium

Abstract

The application provides a database and table separation method and device, electronic equipment and storage medium, the database and table separation method comprises the following steps: obtaining SQL statements generated in the execution process of a target application program based on a preset SQL blood relationship analysis engine; analyzing the SQL statements to obtain upstream and downstream blood relationship in the SQL statements; constructing a table field relationship model based on the upstream and downstream blood relationship; the mask of the field in the table field relationship model corresponds to the unique identifier of the SQL statement; performing graph intersection comparison calculation on the table field relationship model to obtain a database and table separation scheme for executing data migration and application migration. The database and table separation method, device, electronic equipment and storage medium provided by the application can ensure low cost and high efficiency of database and table separation.

Description

Technical Field

[0001] This invention relates to the field of information technology, specifically to a method, apparatus, electronic device, and storage medium for database sharding and table partitioning. Background Technology

[0002] With the continuous development of internet applications, the data scale of various business systems is growing larger and larger. In order to solve the problem of database performance degradation caused by excessive data volume, database sharding and table partitioning are very common methods. Database sharding solves the pressure problem of excessive concurrent access connections to a single database, while table partitioning solves the problem of slow query performance caused by excessive data volume in a single table.

[0003] There are several common approaches to database sharding and table partitioning: 1. Upgrading hardware, which has the disadvantage of high hardware costs; 2. Refactoring the entire system to adapt to the sharding and partitioning model, which has the disadvantages of high time and manpower costs, and the risk of unstable program quality after deployment; 3. Choosing a conservative sharding and partitioning model, manually dividing the data into coarse-grained parts according to business understanding, or manually parsing all SQL statements used in the program and manually classifying tables and fields. The disadvantages are that the sharding and partitioning granularity may be insufficient, the effect may be poor, or omissions may occur during manual parsing, leading to SQL execution failures. Therefore, current database sharding and partitioning methods cannot guarantee both low cost and high efficiency. Summary of the Invention

[0004] In view of this, it is necessary to provide a method, apparatus, electronic device and storage medium for database sharding and table partitioning, so as to ensure low cost while ensuring efficiency in database sharding and table partitioning.

[0005] To achieve the above objectives, the present invention provides a database sharding and table partitioning method, comprising:

[0006] Based on a preset SQL lineage analysis engine, obtain the SQL statements generated during the execution of the target application;

[0007] The SQL statement is parsed to obtain the upstream and downstream lineage relationships in the SQL statement;

[0008] Based on the upstream and downstream lineage relationship, a table field relationship model is constructed; the mask of the field in the table field relationship model corresponds to the unique identifier of the SQL statement;

[0009] The graph intersection comparison calculation is performed on the table field relationship model to obtain the database sharding and table partitioning scheme used for data migration and application migration.

[0010] Furthermore, the construction of the table field relationship model based on the upstream and downstream bloodline relationship includes:

[0011] Create an SQL relational model table in the bloodline analysis database containing the aforementioned upstream and downstream bloodline relationships;

[0012] The SQL statement is converted into an abstract syntax tree, which includes tables, expressions, functions, and first-class fields;

[0013] Based on the tables in the abstract syntax tree, a mirror table is created in the SQL parsing engine, and an initial mask for the mirror table is generated.

[0014] Based on the SQL relational model table, the expressions and functions in the abstract syntax tree are parsed to obtain the second type of fields;

[0015] Based on the first type of fields and the second type of fields, a corresponding mask record function is generated. The mask record function includes the mask corresponding to the first type of fields and the second type of fields. The mask is obtained by performing a logical AND operation between the initial mask and the unique identifier of the SQL statement.

[0016] Generate a new SQL statement based on the mirror table and the mask record function;

[0017] Based on the new SQL statement, construct a table field relationship model.

[0018] Furthermore, the step of creating a mirror table in the SQL parsing engine based on the tables in the abstract syntax tree, and generating an initial mask for the mirror table, includes:

[0019] Based on the tables in the abstract syntax tree, obtain the column metadata of the tables;

[0020] Based on the column metadata, the mirror table is created in the SQL parsing engine;

[0021] Initialize the mask data of the mirror table to obtain the initial mask.

[0022] Furthermore, the construction of the table field relationship model based on the new SQL statement includes:

[0023] Execute the new SQL statement based on the bloodline analysis database to obtain the masks of the first type of fields and the second type of fields;

[0024] The masks of the first type of fields and the second type of fields are saved to the SQL relational model table to obtain the table field relational model.

[0025] Furthermore, the step of performing a graph intersection comparison calculation on the table field relationship model to obtain the database sharding and table partitioning scheme used for data migration and application migration includes:

[0026] Traverse the table field relationship model to generate a table field relationship graph;

[0027] The database sharding and table partitioning scheme is obtained by dividing the non-overlapping regions in the table field relationship graph and performing intersection comparison calculations on the table field relationship graph.

[0028] Furthermore, the data migration includes:

[0029] The database sharding and table partitioning scheme is converted into SQL table creation statements, and table partitioning is created in the database sharding cluster based on the SQL table creation statements;

[0030] The database sharding and table partitioning scheme is converted into an ETL data migration procedure, and based on the ETL data migration procedure, data from the application database is imported into the distributed cluster.

[0031] Furthermore, the application migration includes:

[0032] Based on the aforementioned database sharding and table partitioning scheme, SQL routing data is generated;

[0033] Based on a preset SQL routing proxy driver layer, the SQL routing data is sent to the database of the sharded database cluster so that the SQL routing data can be executed in the database of the sharded database cluster.

[0034] The present invention also provides a database sharding and table partitioning device, comprising:

[0035] The acquisition module is used to acquire SQL statements generated during the execution of the target application based on a preset SQL lineage analysis engine.

[0036] The parsing module is used to parse the SQL statement to obtain the upstream and downstream lineage relationships in the SQL statement;

[0037] A construction module is used to construct a table field relationship model based on the upstream and downstream bloodline relationship; the mask of the field in the table field relationship model corresponds to the unique identifier of the SQL statement;

[0038] The scheme generation module is used to perform graph intersection comparison calculation on the table field relationship model to obtain the database sharding and table partitioning scheme used to perform data migration and application migration.

[0039] The present invention also provides an electronic device, including a memory and a processor, wherein,

[0040] The memory is used to store programs;

[0041] The processor, coupled to the memory, is used to execute the program stored in the memory to implement the steps in any of the above-described database sharding and table partitioning methods.

[0042] The present invention also provides a non-transitory computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the database sharding and table partitioning method as described in any of the preceding claims.

[0043] The beneficial effects of the above implementation method are as follows: The database sharding method, device, electronic device, and storage medium provided by this invention obtain upstream and downstream lineage relationships by capturing and parsing SQL statements in the application, and construct a table field relationship model based on the upstream and downstream lineage relationships. The field masks in the table field relationship model correspond to the unique identifiers of the SQL statements to ensure a one-to-one correspondence between the fields in the table field relationship model and the tables. Finally, the graph intersection comparison calculation is performed through the table field relationship model to obtain the database sharding scheme used for data migration and application migration. The entire process does not require any hardware modifications to the server, thus automatically creating the database sharding scheme. Based on the database sharding scheme, historical data is migrated to the new database sharding environment. The application can switch to the database sharding environment non-intrusively by adding a customized driver and importing the database sharding routing relationship, achieving automatic database sharding and solving the problems of low efficiency and poor effect of manual database sharding. Therefore, this invention can automatically achieve database sharding and table partitioning without requiring hardware upgrades to the server, thus ensuring both low cost and the efficiency and effectiveness of database sharding and table partitioning. Attached Figure Description

[0044] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0045] Figure 1 This is a flowchart illustrating an embodiment of the database sharding and table partitioning method provided by the present invention;

[0046] Figure 2 A schematic diagram illustrating the deployment of the SQL lineage analysis engine in one embodiment of the database sharding and table partitioning method provided by the present invention;

[0047] Figure 3 A schematic diagram of field relationships in one embodiment of the database sharding and table partitioning method provided by the present invention;

[0048] Figure 4 This is a schematic diagram of the data migration process in one embodiment of the database sharding and table partitioning method provided by the present invention;

[0049] Figure 5 This is a schematic diagram of the application migration process in one embodiment of the database sharding and table partitioning method provided by the present invention;

[0050] Figure 6 This is a schematic diagram of the structure of an embodiment of the database sharding and table partitioning device provided by the present invention;

[0051] Figure 7 A schematic diagram of an embodiment of the electronic device provided by the present invention. Detailed Implementation

[0052] 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 a part of the embodiments of the present invention, and not all of them. All other embodiments obtained by those skilled in the art based on the embodiments of the present invention without creative effort are within the scope of protection of the present invention.

[0053] In the description of the embodiments of this application, unless otherwise stated, "a plurality of" means two or more.

[0054] In this embodiment of the invention, the terms "comprising" and "having" and any variations thereof are intended to cover non-exclusive inclusion, for example, a process, method, apparatus, product or device that includes a series of steps or modules is not necessarily limited to those steps or modules that are explicitly listed, but may include other steps or modules that are not explicitly listed or that are inherent to such process, method, product or device.

[0055] The naming or numbering of steps in the embodiments of the present invention does not mean that the steps in the method flow must be executed in the time / logical order indicated by the naming or numbering. The execution order of the named or numbered process steps can be changed according to the technical purpose to be achieved, as long as the same or similar technical effect can be achieved.

[0056] In this document, the term "embodiment" means that a particular feature, structure, or characteristic described in connection with an embodiment may be included in at least one embodiment of the invention. The appearance of this phrase in various places throughout the specification does not necessarily refer to the same embodiment, nor is it a separate or alternative embodiment mutually exclusive with other embodiments. It will be explicitly and implicitly understood by those skilled in the art that the embodiments described herein can be combined with other embodiments.

[0057] This invention provides a method, apparatus, electronic device, and storage medium for database sharding and table partitioning, which will be described below.

[0058] Please see Figure 1 This invention provides a database sharding and table partitioning method, including:

[0059] Step 110: Based on the preset SQL (Structured Query Language) lineage analysis engine, obtain the SQL statements generated during the execution of the target application.

[0060] Understandably, during the pre-generation phase of the database sharding and table partitioning scheme, a SQL lineage analysis engine needs to be deployed. This engine listens for all SQL pre-compacts generated during application execution to construct SQL lineage relationships. A deployment diagram of the SQL lineage analysis engine is shown below. Figure 2 As shown.

[0061] Specifically, a bypass SQL lineage analysis engine is added to the application's database access driver. Since it is a bypass analysis engine, it will not have any impact on the application's performance or usability.

[0062] Step 120: Parse the SQL statement to obtain the upstream and downstream lineage relationships in the SQL statement.

[0063] It's understandable that the upstream and downstream lineage relationships in an SQL statement include the source of upstream data and the destination of downstream data. The meaning of SQL lineage relationships is to output all tables included in the SQL statement.

[0064] Step 130: Based on the upstream and downstream bloodline relationship, construct a table field relationship model; the mask of the field in the table field relationship model corresponds to the unique identifier of the SQL statement.

[0065] Understandably, the table field relationship model contains the correspondence between tables and fields in SQL statements.

[0066] Since different tables may have fields with the same name, simply parsing out the table and field may not be accurate. To accurately determine the specific table corresponding to the field appearing in the SQL statement, a masking technique can be used. This makes the mask of the field in the table field relationship model correspond to the unique identifier of the SQL statement, thus making the fields of different tables unique.

[0067] Step 140: Perform graph intersection comparison calculation on the table field relationship model to obtain the database sharding and table partitioning scheme used for data migration and application migration.

[0068] Understandably, after analyzing all the SQL statements, a database sharding and table partitioning scheme is generated based on the table field relationship model corresponding to all the SQL statements.

[0069] Specifically, the graph intersection comparison calculation is performed on the table field relationship model. The table field relationship graph corresponding to the table field relationship model is divided into two or more unrelated regions, that is, it can be divided into two or more different databases, resulting in the final database sharding scheme. This database sharding scheme can be used to perform data migration and application migration.

[0070] In some embodiments, constructing a table field relationship model based on the upstream and downstream bloodline relationship includes:

[0071] Create an SQL relational model table in the bloodline analysis database containing the aforementioned upstream and downstream bloodline relationships;

[0072] The SQL statement is converted into an abstract syntax tree, which includes tables, expressions, functions, and first-class fields;

[0073] Based on the tables in the abstract syntax tree, a mirror table is created in the SQL parsing engine, and an initial mask for the mirror table is generated.

[0074] Based on the SQL relational model table, the expressions and functions in the abstract syntax tree are parsed to obtain the second type of fields;

[0075] Based on the first type of fields and the second type of fields, a corresponding mask record function is generated. The mask record function includes the mask corresponding to the first type of fields and the second type of fields. The mask is obtained by performing a logical AND operation between the initial mask and the unique identifier of the SQL statement.

[0076] Generate a new SQL statement based on the mirror table and the mask record function;

[0077] Based on the new SQL statement, construct a table field relationship model.

[0078] Understandably, in the kinship analysis database, the SQL relational tables are created as shown in Table 1:

[0079]

[0080]

[0081] Table 1

[0082] The function SAVE_MASK(COL, SQL_ID) creates a masked record in the lineage database. COL is the mask value for the field, and SQL_ID is the unique identifier of the currently executed SQL statement. The masked record function performs a logical AND operation between the mask value of COL and the mask value identified by SQL_ID, and overwrites the original mask value.

[0083] The JSqlParse component converts SQL into an Abstract Syntax Tree (AST). It then traverses the tables, expressions, functions, and fields that appear in the AST and performs syntax transformation on each element. The fields here are the first type of fields.

[0084] Based on the table elements parsed from the abstract syntax tree, a mirror table is created in the SQL parsing engine, all field types are changed to binary types, and a mask data is initialized to obtain the initial mask.

[0085] For the fields extracted from the expressions and functions parsed from the abstract syntax tree, i.e., the second type of fields, all of them are replaced with expressions that add the fields together. For example, func(A1,A2) or caseA1 when 1thenA2 else 2end are all replaced with A1+A2.

[0086] For the first and second type fields, replace all fields with the mask record function, and A1 and A2 become SAVE_MASK(A1,'001') and SAVE_MASK(A2,'001').

[0087] The mask generated by performing a logical AND operation between the initial mask and the unique identifier of the SQL statement is converted into the SQL relational model in the SQL relational model table mentioned above and saved.

[0088] In some embodiments, the step of creating a mirror table in the SQL parsing engine based on the tables in the abstract syntax tree and generating an initial mask for the mirror table includes:

[0089] Based on the tables in the abstract syntax tree, obtain the column metadata of the tables;

[0090] Based on the column metadata, the mirror table is created in the SQL parsing engine;

[0091] Initialize the mask data of the mirror table to obtain the initial mask.

[0092] Understandably, for the table elements parsed from the abstract syntax tree, the source database's data access driver (JDBC) is used to obtain the table's column metadata. A mirror table is then created in the SQL parsing engine, all field types are changed to binary types, and a mask data is initialized to obtain the initial mask.

[0093] In some embodiments, constructing a table field relationship model based on the new SQL statement includes:

[0094] Execute the new SQL statement based on the bloodline analysis database to obtain the masks of the first type of fields and the second type of fields;

[0095] The masks of the first type of fields and the second type of fields are saved to the SQL relational model table to obtain the table field relational model.

[0096] Understandably, the abstract syntax tree transformed through the above process is re-converted into a new SQL statement, for example: select A1,A2,B1fromA,B, which will be transformed into: select SAVE_MASK(A1,'001'), SAVE_MASK(A2,'001'), SAVE_MASK(B1,'001'), from A,B. Tables A and B have been replaced with mask tables. After the new SQL statement is executed through the lineage resolution database, the SAVE_MASK function will perform a logical AND operation between the initial masks of A1, A2, and B1 and the unique identifier of the SQL statement and save it to the SQL relational model table mentioned above, thus obtaining the table field relational model.

[0097] In some embodiments, the step of performing graph intersection comparison calculation on the table field relationship model to obtain the database sharding and table partitioning scheme for performing data migration and application migration includes:

[0098] Traverse the table field relationship model to generate a table field relationship graph;

[0099] The database sharding and table partitioning scheme is obtained by dividing the non-overlapping regions in the table field relationship graph and performing intersection comparison calculations on the table field relationship graph.

[0100] It is understandable that this involves traversing the table field relational models of all SQL relational model tables, where the table field relational models are JSON format records.

[0101] Specifically, a table field relationship graph of the SQL statement is generated by traversing the table field model. For example, using the following relationship models:

[0102] SQL1: {table: "A", cols: ["A1"]}, {table: "B", cols: ["B1", "B2"]};

[0103] SQL2: {table: "B", cols: ["B1"]}, {table: "C", cols: ["C1"]};

[0104] SQL3: {table: "A", cols: ["A2"]}, {table: "D", cols: ["D1"]};

[0105] It can be observed that A1 is related to B1 and B2, B1 is related to C1, and A2 is related to D1. The relationship diagram is as follows. Figure 3 As shown.

[0106] Database sharding and table partitioning schemes are generated by traversing the relationship graph of table fields. For example, refer to... Figure 3 The graph can be divided into two unrelated regions, which proves that it can be divided into two databases, named Database 1 and Database 2. Since the A1 and A2 fields of table A are unrelated, they can be stored in the two sub-tables respectively.

[0107] The proposed database sharding and table partitioning scheme is as follows:

[0108] Library 1: A(A1), B(B1,B2), C(C1);

[0109] Library 2: A(A2), D(D1);

[0110] Simultaneously record the SQL database sharding routing path to the routing field of 1.2.1:

[0111] SQL1: Database 1;

[0112] SQL2: Database 1;

[0113] SQL3: Database 2.

[0114] In some embodiments, the data migration includes:

[0115] The database sharding and table partitioning scheme is converted into SQL table creation statements, and table partitioning is created in the database sharding cluster based on the SQL table creation statements;

[0116] The database sharding and table partitioning scheme is converted into an ETL (Extract, Clean, Transform, Load) data migration procedure, which is used to import data from the application database into the distributed cluster.

[0117] Understandably, the generated database sharding and table partitioning scheme is then converted into SQL table creation statements to create partitioned tables in the sharded cluster. Furthermore, the generated scheme is then converted into an ETL data migration process to import data from the application database into the sharded cluster. A detailed data migration diagram is shown below. Figure 4 As shown.

[0118] Database sharding can be either vertical or horizontal.

[0119] Vertical database partitioning (database partitioning based on business functions): Distributed microservices are inherently vertical database partitioning. If a single SQL database cannot handle the data, multiple SQL services can be used to distribute the load. Vertical database partitioning involves dividing N tables in a database into multiple SQL databases according to modules / business functions. Each database has its own server for distributed deployment.

[0120] Horizontal database sharding (splitting the same business table into different databases): Horizontal database sharding refers to splitting a table in one database into multiple databases, with the databases being deployed in a distributed manner. For example, e-commerce platforms can shard their product databases according to users.

[0121] In some embodiments, the application migration includes:

[0122] Based on the aforementioned database sharding and table partitioning scheme, SQL routing data is generated;

[0123] Based on a preset SQL routing proxy driver layer, the SQL routing data is sent to the database of the sharded database cluster so that the SQL routing data can be executed in the database of the sharded database cluster.

[0124] Understandably, to enable a zero-intrusive application switch to a sharded cluster, an SQL routing proxy driver layer needs to be added to the original database driver layer. This proxy driver layer imports the SQL routing data generated based on the sharding scheme. Each SQL statement from the application is then routed through the proxy layer to the corresponding database in the sharded cluster for execution, achieving seamless database switching. A specific application migration diagram is shown below. Figure 5 As shown.

[0125] In summary, this invention provides a database sharding and table partitioning method, apparatus, electronic device, and storage medium, comprising: acquiring SQL statements generated during the execution of a target application based on a preset SQL lineage analysis engine; parsing the SQL statements to obtain upstream and downstream lineage relationships in the SQL statements; constructing a table field relationship model based on the upstream and downstream lineage relationships; the mask of the fields in the table field relationship model corresponding to the unique identifier of the SQL statement; and performing graph intersection comparison calculation on the table field relationship model to obtain the database sharding and table partitioning scheme used for performing data migration and application migration.

[0126] In the database sharding and table partitioning method provided by this invention, the upstream and downstream lineage relationships are obtained by capturing and parsing SQL statements in the application. A table field relationship model is then constructed based on these relationships. The field masks in the table field relationship model correspond to the unique identifiers of the SQL statements, ensuring a one-to-one correspondence between the fields and tables. Finally, a graph intersection comparison calculation is performed using the table field relationship model to obtain the database sharding and table partitioning scheme used for data migration and application migration. The entire process requires no hardware modifications to the server, automatically creating the database sharding and table partitioning scheme. Based on this scheme, historical data is migrated to the new database sharding and table partitioning environment. The application, by adding a customized driver and importing the database sharding and table partitioning routing relationships, can seamlessly switch to the database sharding and table partitioning environment, achieving automatic database sharding and table partitioning. This solves the problems of low efficiency and poor performance associated with manual database sharding and table partitioning. Therefore, this invention can automatically implement database sharding and table partitioning without requiring server hardware upgrades, ensuring both low cost and high efficiency and effectiveness.

[0127] like Figure 6 As shown, the present invention also provides a database sharding and table partitioning device 600, comprising:

[0128] The acquisition module 610 is used to acquire SQL statements generated during the execution of the target application based on a preset SQL lineage analysis engine.

[0129] The parsing module 620 is used to parse the SQL statement to obtain the upstream and downstream lineage relationships in the SQL statement;

[0130] The construction module 630 is used to construct a table field relationship model based on the upstream and downstream blood relationship; the mask of the field in the table field relationship model corresponds to the unique identifier of the SQL statement;

[0131] The scheme generation module 640 is used to perform graph intersection comparison calculation on the table field relationship model to obtain the database sharding and table partitioning scheme used to perform data migration and application migration.

[0132] The database sharding and table partitioning device provided in the above embodiments can realize the technical solutions described in the above database sharding and table partitioning method embodiments. The specific implementation principles of each module or unit can be found in the corresponding content in the above database sharding and table partitioning method embodiments, and will not be repeated here.

[0133] like Figure 7 As shown, the present invention also provides an electronic device 700. The electronic device 700 includes a processor 701, a memory 702, and a display 703. Figure 7Only some components of the electronic device 700 are shown, but it should be understood that it is not required to implement all the components shown, and more or fewer components may be implemented instead.

[0134] In some embodiments, memory 702 may be an internal storage unit of electronic device 700, such as a hard disk or memory of electronic device 700. In other embodiments, memory 702 may also be an external storage device of electronic device 700, such as a plug-in hard disk, smart media card (SMC), secure digital (SD) card, flash card, etc. equipped on electronic device 700.

[0135] Furthermore, the memory 702 may include both internal storage units of the electronic device 700 and external storage devices. The memory 702 is used to store application software and various types of data installed on the electronic device 700.

[0136] In some embodiments, processor 701 may be a central processing unit (CPU), microprocessor, or other data processing chip, used to run program code stored in memory 702 or process data, such as the database sharding and table partitioning method of the present invention.

[0137] In some embodiments, display 703 may be an LED display, a liquid crystal display, a touch-sensitive liquid crystal display, or an OLED (Organic Light-Emitting Diode) touchscreen. Display 703 is used to display information from electronic device 700 and to display a visual user interface. Components 701-703 of electronic device 700 communicate with each other via a system bus.

[0138] In some embodiments of the present invention, when the processor 701 executes the SQL parsing program for extracting data lineage in the memory 702, the following steps can be implemented:

[0139] Based on a preset SQL lineage analysis engine, obtain the SQL statements generated during the execution of the target application;

[0140] The SQL statement is parsed to obtain the upstream and downstream lineage relationships in the SQL statement;

[0141] Based on the upstream and downstream lineage relationship, a table field relationship model is constructed; the mask of the field in the table field relationship model corresponds to the unique identifier of the SQL statement;

[0142] The graph intersection comparison calculation is performed on the table field relationship model to obtain the database sharding and table partitioning scheme used for data migration and application migration.

[0143] It should be understood that when the processor 701 executes the SQL parsing program for extracting data lineage in the memory 702, in addition to the functions mentioned above, it can also perform other functions, as detailed in the description of the corresponding method embodiments above.

[0144] Furthermore, the embodiments of the present invention do not specifically limit the type of electronic device 700 mentioned. Electronic device 700 can be a mobile phone, tablet computer, personal digital assistant (PDA), wearable device, laptop computer, or other portable electronic device. Exemplary embodiments of portable electronic devices include, but are not limited to, portable electronic devices running iOS, Android, Microsoft, or other operating systems. The aforementioned portable electronic device can also be other portable electronic devices, such as a laptop computer with a touch-sensitive surface (e.g., a touch panel). It should also be understood that in some other embodiments of the present invention, electronic device 700 may not be a portable electronic device, but rather a desktop computer with a touch-sensitive surface (e.g., a touch panel).

[0145] In another aspect, the present invention also provides a non-transitory computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, is implemented to perform the database sharding and table partitioning methods provided by the above methods, the method comprising:

[0146] Based on a preset SQL lineage analysis engine, obtain the SQL statements generated during the execution of the target application;

[0147] The SQL statement is parsed to obtain the upstream and downstream lineage relationships in the SQL statement;

[0148] Based on the upstream and downstream lineage relationship, a table field relationship model is constructed; the mask of the field in the table field relationship model corresponds to the unique identifier of the SQL statement;

[0149] The graph intersection comparison calculation is performed on the table field relationship model to obtain the database sharding and table partitioning scheme used for data migration and application migration.

[0150] Those skilled in the art will understand that all or part of the processes of the methods described in the above embodiments can be implemented by a computer program instructing related hardware, and the program can be stored in a computer-readable storage medium. The computer-readable storage medium may be a disk, optical disk, read-only memory, or random access memory, etc.

[0151] The above provides a detailed description of the database sharding and table partitioning method, apparatus, electronic device, and storage medium provided by the present invention. Specific examples have been used to illustrate the principles and implementation methods of the present invention. The descriptions of the above embodiments are only for the purpose of helping to understand the method and core ideas of the present invention. At the same time, those skilled in the art will recognize that there will be changes in the specific implementation methods and application scope based on the ideas of the present invention. Therefore, the content of this specification should not be construed as a limitation of the present invention.

Claims

1. A method for database sharding and table partitioning, characterized in that, include: Based on a preset SQL lineage analysis engine, obtain the SQL statements generated during the execution of the target application; The SQL statement is parsed to obtain the upstream and downstream lineage relationships in the SQL statement; Based on the upstream and downstream lineage relationship, a table field relationship model is constructed; the mask of the field in the table field relationship model corresponds to the unique identifier of the SQL statement; The graph intersection comparison calculation is performed on the table field relationship model to obtain a database sharding and table partitioning scheme for performing data migration and application migration; The construction of a table field relationship model based on the upstream and downstream bloodline relationship includes: Create an SQL relational model table in the bloodline analysis database containing the aforementioned upstream and downstream bloodline relationships; The SQL statement is converted into an abstract syntax tree, which includes tables, expressions, functions, and first-class fields; Based on the tables in the abstract syntax tree, a mirror table is created in the SQL parsing engine, and an initial mask for the mirror table is generated. Based on the SQL relational model table, the expressions and functions in the abstract syntax tree are parsed to obtain the second type of fields; Based on the first type of fields and the second type of fields, a corresponding mask record function is generated. The mask record function includes the mask corresponding to the first type of fields and the second type of fields. The mask is obtained by performing a logical AND operation between the initial mask and the unique identifier of the SQL statement. Generate a new SQL statement based on the mirror table and the mask record function; Based on the new SQL statement, construct a table field relationship model.

2. The database sharding and table partitioning method according to claim 1, characterized in that, The process of creating a mirror table in the SQL parsing engine based on the tables in the abstract syntax tree and generating an initial mask for the mirror table includes: Based on the tables in the abstract syntax tree, obtain the column metadata of the tables; Based on the column metadata, the mirror table is created in the SQL parsing engine; Initialize the mask data of the mirror table to obtain the initial mask.

3. The database sharding and table partitioning method according to claim 1, characterized in that, The construction of the table field relationship model based on the new SQL statement includes: Execute the new SQL statement based on the bloodline analysis database to obtain the masks of the first type of fields and the second type of fields; The masks of the first type of fields and the second type of fields are saved to the SQL relational model table to obtain the table field relational model.

4. The database sharding and table partitioning method according to claim 1, characterized in that, The step of performing graph intersection comparison calculation on the relationship model of the table fields to obtain a database sharding and table partitioning scheme for performing data migration and application migration includes: Traverse the table field relationship model to generate a table field relationship graph; The data sharding and table partitioning scheme for performing data migration and application migration is obtained by dividing the data sharding and table partitioning into regions where there is no intersection in the table field relationship graph and performing intersection comparison calculations on the table field relationship graph.

5. The database sharding and table partitioning method according to claim 1, characterized in that, The data migration includes: The database sharding and table partitioning scheme is converted into SQL table creation statements, and table partitioning is created in the database sharding cluster based on the SQL table creation statements; The database sharding scheme is converted into an ETL data migration program, and data from the application database is imported into the sharded cluster based on the ETL data migration program.

6. The database sharding and table partitioning method according to any one of claims 1-5, characterized in that, The application migration includes: Based on the aforementioned database sharding and table partitioning scheme, SQL routing data is generated; Based on a preset SQL routing proxy driver layer, the SQL routing data is sent to the database of the sharded database cluster so that the SQL routing data can be executed in the database of the sharded database cluster.

7. A device for dividing databases and tables, characterized in that, include: The acquisition module is used to acquire SQL statements generated during the execution of the target application based on a preset SQL lineage analysis engine. The parsing module is used to parse the SQL statement to obtain the upstream and downstream lineage relationships in the SQL statement; A construction module is used to construct a table field relationship model based on the upstream and downstream bloodline relationship; the mask of the field in the table field relationship model corresponds to the unique identifier of the SQL statement; The scheme generation module is used to perform graph intersection comparison calculation on the table field relationship model to obtain a database sharding and table partitioning scheme for performing data migration and application migration. The construction of a table field relationship model based on the upstream and downstream bloodline relationship includes: Create an SQL relational model table in the bloodline analysis database containing the aforementioned upstream and downstream bloodline relationships; The SQL statement is converted into an abstract syntax tree, which includes tables, expressions, functions, and first-class fields; Based on the tables in the abstract syntax tree, a mirror table is created in the SQL parsing engine, and an initial mask for the mirror table is generated. Based on the SQL relational model table, the expressions and functions in the abstract syntax tree are parsed to obtain the second type of fields; Based on the first type of fields and the second type of fields, a corresponding mask record function is generated. The mask record function includes the mask corresponding to the first type of fields and the second type of fields. The mask is obtained by performing a logical AND operation between the initial mask and the unique identifier of the SQL statement. Generate a new SQL statement based on the mirror table and the mask record function; Based on the new SQL statement, construct a table field relationship model.

8. An electronic device, characterized in that, Including memory and processor, among which, The memory is used to store programs; The processor, coupled to the memory, is used to execute the program stored in the memory to implement the steps in the database sharding and table partitioning method according to any one of claims 1 to 6.

9. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the database sharding and table partitioning method as described in any one of claims 1 to 6.

Images