Database fragment design method and device

Abstract

Database fragmentation is a method for solving the problems that the data volume of a single table is too large and the performance of an operation table is reduced. However, by intercepting the sql statements and then analyzing and rewriting the sql statements, the types of supported databases are limited, the accuracy is not high, the use is complex and the like. And after the fragment node structure is changed, recording the to-be-moved part to other nodes. The invention provides a database fragment design method and device. Ideal fragments should be transparent to users, and only simple configuration rules need to be mastered without increasing additional cost. Influences on an original system should be reduced as much as possible through introduction of the fragmentation, an interface module does not need to be adjusted, and the SQL statement after fragmentation is similar to the SQL statement without fragmentation. The database is operated in an object-oriented mode, and the method has the advantages that the sql statement is generated after the data structure is obtained, so that the generated statement is more accurate. After the fragment node structure is changed, the ideal scheme does not need to migrate old data, so that the influence on the system is reduced.

Description

Technical Field

[0001] The present invention relates to the fields of computer technology and software technology, and in particular to a database sharding design method and device. Background Art

[0002] For the convenience of writing, the background technology and related terms / abbreviations are first explained.

[0003] Database sharding is to split a database into multiple databases, and table sharding is to split a table into multiple tables. There are vertical and horizontal splitting. Vertical splitting generally divides tables into different databases according to business (this is not within the scope of this invention). Horizontal splitting is to split the data of a table into multiple tables (this is the research object of this invention). When a table stores too much data, the data of the table can be split into multiple tables. These split tables can be in the same database or not. There are three types: splitting tables only, splitting databases only, and splitting databases and tables. Figure 1 shown. Figure 1The sharding of databases and tables shown in the figure is to split an orders table into four tables, orders0 and orders1 are in the database ds0, and orders2 and orders3 are in the database ds1. Among them, orders0 to orders3 are actual tables / physical tables, and 0, 1, 2, and 3 are the subscripts / suffixes of the actual tables; and orders is the basic table (basic table name). The associated table (primary table and secondary table) refers to the primary table and secondary table with a primary and foreign key relationship, and the data is stored in two tables according to certain DB design principles. For example: orders, orders_item; users, users_item; they generally have a foreign key relationship (not necessarily using foreign key constraints). In addition, there are tables that do not need to be sharded and only exist in one database, which are original tables / unsharded tables, because not all tables need to be sharded; broadcast tables refer to tables that exist in all sharded databases (the same broadcast table has one in each node, and the data is the same), and the table structure and its data are completely consistent in each database. It is suitable for scenarios where the data volume is not large and needs to be associated with tables with massive data, such as dictionary tables. Sharding key is used to find the fields in the table corresponding to the library and table; sharding key includes sharding key and sharding table key. Sharding key refers to the field in the table, whose value is used to find the field of the unique database according to a certain algorithm. For example, in the orders table, userid (user id) can be used as sharding key. Sharding table key refers to the field in the table, whose value is used to find the field of the unique actual table according to a certain algorithm. For example, in the orders table, userid (user id) can be used as sharding table key. Data node is the smallest unit of data sharding, which consists of data source name and actual table, such as dsl.ordersl. Full data node (fullNodes) refers to all databases and corresponding actual tables involved in a basic table. Data source (ds) refers to the source configured with database url, user name, password and other information. In Java, it refers to javax.sql.DataSource. Data source corresponds to database. If there is no confusion, the two terms can be regarded as synonyms. The types of libraries and tables routed to are the types of sub-libraries and sub-tables or sharding. According to the number of libraries and tables found by the sharding key, they are divided into: one library and one table, one library and multiple tables, multiple libraries and multiple tables, and all libraries and all tables. Sharding refers to sub-libraries and sub-tables, and finding or operating the required data through certain methods. The framework or device of sharding should allow users to feel that the data they operate is the same when there is sharding and when there is no sharding; of course, the printed log can indicate which node has performed what operation.

[0004] SUID:

[0005] Query, modify, add, and delete are the acronyms of the four SQL operations Select, Update, Insert, and Delete, and are synonymous with CRUD. Change operations include modify, add, and delete.

[0006] Coding complexity C(n):

[0007] Coding complexity refers to the changing trend of the amount of work required to write code when solving a problem as the problem size n increases. If the amount of work required to write code increases in direct proportion to the problem size n, then the coding complexity of the problem is 0(n); if the amount of work required to write code is independent of the problem size n and remains unchanged, then the coding complexity of the problem is 0(1). Therefore, the coding complexity C(n) can be represented by the function 0, which reflects the relationship between the amount of coding work required to solve the problem and the problem size. Obviously, to solve the same problem, a device, framework or solution with a coding complexity of 0(1) will require less coding.

[0008] With the development of the Internet, more and more data is generated. Once the amount of data in the business table is large, from the perspective of maintenance and performance, no matter what kind of CRUD operation, it is extremely resource-consuming for the database. Even if the index is set, it still cannot cover up the fact that the database performance has declined due to the large amount of data. At this time, the database should be sharded, and the massive data originally maintained by one table should be allocated to N sub-tables for storage and maintenance. However, in the existing sharding technology, there are many problems in the way of intercepting SQL statements and then parsing and rewriting SQL statements (such as shardingsphere), such as: the types of databases that can be supported are limited, the accuracy is not high, and the use is complicated. The object-oriented 0RM tool, such as 0RM Bee, generates a sharding SQL statement that is too different from that when it is not sharded. The logical SQL of the sharding is not easy to format and difficult to read. After the sharding node structure changes, due to the new routing algorithm, some records do not point to the original node, and these records need to be moved to the new node. When the amount of data is large, it will have a greater impact on the system. Summary of the invention

[0009] The present invention provides a database sharding design method and device, aiming to solve at least one of the above-mentioned technical problems and to use sharding in a simple manner.

[0010] To this end, one object of the present invention is to provide a method for solving the above-mentioned problem; another object of the present invention is to provide a device for solving the above-mentioned problem.

[0011] In order to achieve the above two purposes, the present invention proposes the following technical solutions:

[0012] The system is divided into at least a SUID interface module, a parsing module, a JDBC interaction module and a sharding engine module, as well as a context module and a configuration information module.

[0013] 1) The SUID interface module is an interface provided to users, which can separate the interface from the implementation, allowing users to complete what they need to do by just calling the interface without having to pay attention to the specific implementation details.

[0014] 2) Parsing module, including analysis and collection of sharding information, determining whether there is sharding, and recording sharding type, actual table list, ds list, table subscript, correspondence between table and ds and other information into the context; parsing and generating SQL statements, putting ds, table and other information into the context; SQL rewriting; calling JDBC interaction module; processing the results before returning, and processing the context information of the current operation; returning the results.

[0015] 3) The JDBC interaction module includes determining whether it is sharding, processing of sharding main thread and sharding sub-thread, sharding main thread calling sharding engine module processing, processing of non-sharding requests, calling the underlying JDBC and processing the returned information and return results.

[0016] 4) The sharding engine module processes various sharding types; generates SQL executed by each shard, rewrites the SQL into SQL using the actual table and records the corresponding ds; executes the operation using sub-threads, merges and processes the results obtained after all sub-thread operations, and query record types including sorting, obtaining a specified number of records, and aggregation including avg recalculation, count, sum further processing and calculation, etc.

[0017] 5) The configuration information module manages and stores configuration-related information; the context module manages and stores context information used by the system. Other templates can use the information of these two templates.

[0018] When the sharding engine module parses the sharding, a) if it is not a sharding, it returns. b) if it is a broadcast table query, a ds is randomly selected and set to the previous thread context; if it is a broadcast table change operation, the sharding ID is recorded, and the sharding type is recorded as full database and full table. c) Analyze the sharding information. If it is one database and one table, ds and table are set to the context of the current thread; if it is not one database and one table, record the database and table types, record the sharding ID, ds list and table list.

[0019] When the parsing module parses and generates SQL, if it is judged that there is sharding and it is not a broadcast table and the table name is not set in the current thread context, the basic table name + special character subscript is used as the table name, and when operating on multiple tables, the basic table name is used as the alias; if it is a single table operation, it will not cause field name confusion, so there is no need to use a table alias.

[0020] Propose a suitable routing algorithm so that old data does not need to be transferred after the shard node structure changes.

[0021] The beneficial effects achieved by the present invention are:

[0022] In addition to the sharding configuration information, sharding is transparent to users. Whether the system is sharded or not does not require too much additional cost for users. Users can use it by mastering the configuration rules. Sharding upgrades require little change to the original system, and the interface of the referenced interface module does not need to be adjusted. When sharding, the difference between the executed SQL statements and those when not sharding is small. Sharded SQL statements are easy to format in database client tools. After the sharding node structure changes, the old data can be left unchanged. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] Figure 1 , the example diagram of sub-library and sub-table;

[0024] Figure 2 , SUID operation overall flow chart;

[0025] Figure 3 , the parsing engine refines the flow chart;

[0026] Figure 4 , JDBC interaction module refinement flow chart;

[0027] Figure 5 , Sharding engine module flow chart;

[0028] Figure 6 , the effect diagram of the SQL statement with table subscript placeholder after being formatted by the database client tool;

[0029] Figure 7 , Condition and 0p interface method diagram. DETAILED DESCRIPTION

[0030] The specific implementation of the present invention provides a method and device for implementing database sharding design.

[0031] The embodiment described below in conjunction with the accompanying drawings (when involving specific language descriptions, the computer languages ​​used are Java and SQL) is only one implementation of the present invention and should not be construed as limiting the present invention.

[0032] The device of the present invention at least includes: a SUID interface module, a parsing module, a JDBC interaction module and a sharding engine module, as well as a context module and a configuration information module. The overall process of SUID operation is as follows: Figure 2 As shown; the parsing engine refines the process, such as Figure 3 As shown; the JDBC interaction module detailed process is as follows Figure 4As shown; the sharding engine module process is as follows Figure 5 As shown in the figure, the SUID interface module calls the parsing module to complete the database operation; the parsing module can call the sharding interceptor and parse SQL; the parsing module can call the JDBC interaction module; the JDBC interaction module can call the sharding engine module.

[0033] The source code of the SUID interface part of the SUID interface module is as follows:

[0034]

[0035] The Suid in the interface module can accept parameters of entity and condition types.

[0036] In the parsing module, the implementation of the select(T entity, Condition condition) interface method is as follows: Figure 3 The process structure shown in the figure. doBeforePasreEntity(entity, SuidType.SELECT) is an interceptor stack, in which the shard interceptor will parse and collect shard-related information, including the analysis and collection of shard information, determine whether there is sharding, and record the shard type, actual table list, ds list, table subscript, the correspondence between table and ds, and other information into the context. Figure 3 The SQL rewriting step does not involve the SQL rewriting of the shards. One SQL is turned into one execution node and completed in the sharding engine module.

[0037]

[0038]

[0039] When parsing the SQL statement String sql = getObjToSQL().toSelectSQL(entity, condition), there will be statements similar to the following:

[0040] sqlBuffer.append(K.select).append(″″).append(columnNames).append(″″).append(K.from).append(″″);

[0041] sqlBuffer.append(tableName);

[0042] When getting tableName, the useTableIndex method will determine whether to add special characters of the subscript placeholder, such as ShardingTableIndexStr = "'[$#(index)#$]'". If the subscript placeholder is used, when the sharding engine module creates a new subthread to execute the sql, the subscript placeholder will be replaced with the actual subscript; if there are multiple subscripts, multiple sql statements will be replaced, and the corresponding number of subthreads will be used for execution. In addition, in the sql statement, the parameter value is replaced with a question mark placeholder, so when replacing the subscript, there is no situation where the value containing the subscript placeholder is also replaced. The source code of the useTableIndex method is shown below.

[0043]

[0044] When parsing and generating SQL, if it is determined that there is sharding and it is not a broadcast table and the table name is not set in the current thread context, the basic table name + special character subscript is used as the table name, and when operating on multiple tables, the basic table name is used as the alias; if it is a single table operation, there is no need to use a table alias because it will not cause field name confusion.

[0045] In the case of multiple tables, when parsing and generating SQL, according to the specific situation, if a table subscript placeholder is needed, an alias is used; if no subscript placeholder is used, an alias is not needed. The following logical SQL uses a table subscript placeholder.

[0046] from clazz'[$#(index)#$]'clazz left join student'[$#(index)#$]'student on clazz.id=student.classno

[0047] or

[0048] from clazz'[$#(index)#$]'clazz,student'[$#(index)#$]'student whereclazz.id=student.classno

[0049] The table name in the field list uses an alias (the alias here is the base table name).

[0050] For paging, avg average query, etc., you need to add corresponding structures according to the specific situation so that you can generate appropriate SQL and assist in SQL rewriting in the sharding engine module.

[0051] In the code example given in the above parsing module introduction, the statement list = getBeeSql().select(sql, toClassT(entity)) will call the corresponding method of the JDBC interaction module. The source code is as follows, corresponding to Figure 4 The process structure shown. _selectSomeField(sql, entityClass) calls the underlying JDBC to operate the database, process the results and return. And both the non-sharded and sharded sub-threads execute the _selectSomeField method. From here, it can also be seen that the device can support sharding of some tables and non-sharding of some tables, and does not require all tables to be sharded. Users can select tables with larger data volumes for sharding according to actual business needs.

[0052]

[0053]

[0054] This embodiment can be regarded as a further improvement based on ORM Bee 2.2. Since ORM Bee 2.2 supports at least MySQL, MariaDB, Oracle, H2, SQLite, and PostgreSQL, the inventive device will also support the above databases.

[0055] The flow chart of the sharding engine module is as follows Figure 5 As shown. When the sharding engine module parses the sharding, a) if it is not a sharding, it returns. b) If it is a broadcast table query, a ds is randomly selected and set to the previous thread context; if it is a broadcast table change operation, the sharding ID is recorded, and the sharding type is recorded as full database and full table. c) Analyze the sharding information. If it is one database and one table, ds and table are set to the context of the current thread; if it is not one database and one table, record the database and table types, record the sharding ID, ds list and table list.

[0056] Convert one sql to one node to execute one, that is, change the table subscript placeholder in the logical sql to the table subscript of the actual node, and record the corresponding ds. For paging, avg average query, etc., the sql needs to be rewritten according to the specific situation to get the new sql. Get the sql array and ds array, generate child threads, and set the sql and ds used by each thread, let the child threads perform the task of operating the DB, merge and process the results, and return the final result after the processing is completed. The query type sharding engine source code ShardingSelectEngine can refer to ShardingSelectEngine.java in the appendix Java source code. Different SUID operation types will have different implementation details. For example, when inserting multiple records, when merging and processing the results, the number of records affected by each node is accumulated. The query record types include sorting, obtaining a specified number of records, and aggregation includes avg recalculation, count, sum further processing calculation, etc.

[0057] The device of the present invention parses the relatively complex SQL statement of the three-table association query as follows:

[0058]

[0059] The parameter value will be placed in the context. The above statement with table subscript placeholder is formatted in the database client tool, such as Figure 6 As shown in the figure, it can be formatted, which makes it convenient for users to analyze SQL statements.

[0060] After being rewritten by the sharding engine module, the execution will be like the following SQL statement with a specific table name (the following is an executable statement, the question mark placeholder has been replaced with an actual value, and when the device is executed, the statement with? is still used and PreparedStatement is used):

[0061]

[0062] For the same statement, after replacing the subscript placeholder with the actual subscripts of different tables, the generated SQL statement has only different table aliases. The table names of the declared columns such as clazz.id and clazz.classname are all basic table names, which are more different and easier to compare. Compared with the corresponding SQL without sharding, only the table aliases are different.

[0063] In addition, the association information of multiple tables can be added in Javabean using annotations. For example:

[0064] @JoinTable(mainField="id″,subField="classno″,joinType=JoinType.LEFT_JOIN,subClazz=Student1.class)

[0065] private List <student1>studentList=new ArrayList<>();

[0066] After sharding, a problem arises: how to find the table to be operated, which is the routing / mapping problem.

[0067] The mapping relationship between the basic table and the actual table can be divided into four types of distribution of the actual table: uniform sequential distribution, uniform polling distribution, uniform distribution only, and custom distribution.

[0068] The following examples illustrate the characteristics of various table distributions. There are three databases with the basic name ds, six tables, and the basic name of the tables is user. The following is the distribution of databases and corresponding table subscripts.

[0069] Uniform sequential distribution:

[0070] ds0=[0,1], ds1=[2,3], ds2=[4,5]

[0071] Uniform polling distribution:

[0072] ds0=[0,3], ds1=[1,4], ds2=[2,5]

[0073] Uniform distribution only:

[0074] ds0=[0,1], ds1=[0,1], ds2=[0,1]

[0075] Custom: Maybe

[0076] ds0=[0,], ds1=[1, 2, 3], ds2=[4, 5]

[0077] Through observation and judgment, it is found that the names of the tables in each database are the same when they are evenly distributed. Only the table name is known, and the corresponding database cannot be inferred from the table name. Therefore, it is not suitable for general sharding processing; uniform polling distribution also cannot infer the corresponding database of the table from the table name; customization has no rules and is not suitable either.

[0078] In the sharding key with uniform sequential distribution, only the sharding table key can be used, and then the library to which it belongs can be inferred based on the determined table name; thus, the sharding library key can be omitted. As in the example above, if the sharding key uses userid, userid%6 can be used to find the table subscript, and userid%6 / 2 can be used to find the ds subscript. 6 is the number of all sharding tables of the basic table, and 2 is the data of the sharding table that a node has. In addition, ds[0..2].orders[0..5] is used to represent the complete data node (fullNodes), which is parsed by the device into a uniform sequential distribution. userid%6 and userid%6 / 2 can also be parsed by the device, and ds can also be obtained by reverse querying the table. For the corresponding sharding, the Java configuration method is as follows:

[0079]

[0080] In the sharding key, you can only use the sharding key, and then infer the database based on the determined table name; thus omitting the sharding key.

[0081] shardingBean.setFullNodes("ds[0..1].orders[]"); / / Only shard the database, not the table (each database has the same table name; but there are multiple databases)

[0082] shardingBean.setFullNodes(″ds[].orders[0..2]″); / / Only shard the table, not the database (only one database)

[0083] In the case of only sharding, its table subscript corresponds to an empty value. You can replace the subscript placeholder to get the real table name. In the case of only sharding tables, there is only one ds and the subscript is empty; table sharding is the same as sharding databases and tables.

[0084] By using the device of the present invention, the sharding information of a basic table only needs to be configured with three attributes: fullNodes, tabField sharding key and basic table name to use the sharding function. Other sharding information can be the default value or obtained by calculation by the device of the present invention.

[0085] The following is an example of code usage to illustrate what needs to be done before and after the upgrade. The following example is an example of a query that associates multiple tables. When the table corresponding to Clazz (original table) and its associated table are upgraded to shards, there is no need to change these codes. You only need to add the configuration information of the shards, and the device described in the present invention will handle the rest. The work involved in the upgrade will create tables used for the new shards, and split the original data into different shard tables according to the rules. Because the sharding keys and node distribution used in the sharding are all from the sharding configuration information. The value corresponding to the sharding key comes from the following information, and it is what it was originally, and does not need to be changed.

[0086]

[0087] The above embodiment uses an object-oriented approach to operate the database, which has the advantage of having a data structure before generating SQL statements, and the generated statements are more accurate. Figure 7 As shown), it can construct expressions used by about 90% of SQL. The invention device uses entity and Condition as the data structure for loading the database information that the user wants to operate. Object-oriented can parse SQL to complete non-sharded SUID operations. When upgrading to sharding, it only needs to add sharding configuration information to the corresponding table; generally, databases that support Java JDBC and have corresponding drivers can use the invention device to operate this type of database, and tables with sharding information can use the sharding function.

[0088] In Suid, the parameter T entity of the method is generic and can be passed in different Javabean entity structures. Therefore, for the table, the coding complexity of the invention device is 0(1). For example, in the following code, the entity of Orders type is passed to the suid.select method, and other entity types such as OrdersItem can also be passed.

[0089]

[0090] When the orders table is to be upgraded to a sharded table, for the device of the present invention, only corresponding configuration information needs to be added, such as:

[0091] ShardingConfig.addShardingBean("orders", new ShardingBean("ds[0..1].orders[0..5]"","orderid"));

[0092] The first parameter of the addShardingBean method is the table name, and the second parameter is the object of the shardingBean structure. The first parameter of the ShardingBean construction method is fullNodes, and the second parameter is the sharding table key. In the case of minimal parameters, only the three types of parameters mentioned above are needed to achieve the purpose of sharding. In the case of sharding, or some tables are sharded and some are not, the suid.select method can be used. Therefore, when the sharding function is available, for the table, the encoding complexity of the invention device is still 0(1).

[0093] When using the SUID module, if the passed in entity is not T entity, but the Class type corresponding to the entity, such as selectById(Class <t>entityClass, Integer id), deleteById(Class c, Long id) and other similar methods can pass the id into the context, and then get the id value from the context when the doBeforePasreEntity interceptor parses and collects the fragment information. Because the Class type already has the structure of the entity, and the id is the value of the entity; it is just that the entity object can load the values ​​of all the attributes of the entity. In methods such as xxxById, only the id needs to be passed.

[0094] When using the SUID module, if the written sql statement is passed in instead of the entity object, the SUID module should provide other interfaces and define corresponding methods to accept sql statement parameters. The values ​​used by sql can be passed in using Map or array. For this type of direct sql statement as a parameter, to implement sharding, you need to parse the sql statement into an abstract sql syntax tree, find the node corresponding to from, and when the table uses an alias, change the name of the table to the name of the actual table. The alias does not need to be changed. If the table is not used, add the actual table name and space in front of the table (equivalent to changing it to the actual table name and using the original table name as the alias). To determine whether sharding is needed, use the obtained table name to find the sharding configuration information to see if the table is a sharding table (the configuration information structure is the same as the object-oriented method). If so, continue to obtain the value of the where part. After obtaining the value of the field, the judgment method is similar to the object-oriented method. For paging, avg check average, etc., you need to rewrite the sql according to the specific situation to get the new sql.

[0095] After the shard node structure changes, some records do not point to the original nodes according to the new routing algorithm, so these records need to be moved to the new nodes. When the amount of data is large, it will have a greater impact on the system. The device described in the present invention provides a solution, which does not require moving data after the shard node structure changes.

[0096] The following are the data structures of the sharding-related classes ShardingBean and Calculate.

[0097]

[0098]

[0099] tabAlgorithmClass is an algorithm class for calculating table shards. By default, the invention device can use userid%6 mentioned above to calculate the table corresponding to the shard. If the shards increase from the original 6 tables to 12, then the following routing rules can be maintained in the custom algorithm class.

[0100]

[0101] The max1 is the maximum value of the shard key field in the old data. You can find it in advance when the system is upgraded. ds routing can use a similar method.

[0102] You can also make further improvements by preparing an algorithm class in advance to dynamically read the expression configured online and the maximum value of the shard key, or presetting a sufficiently large maximum value in advance (and use it as the boundary value between new and old data). In addition, prepare the required new database and table (the configuration information must also support dynamic refresh). In this way, you can dynamically change the structure of the shard node without stopping the server.

[0103] Another method of database sharding is "range sharding", which allows you to expand capacity without moving data. Range sharding is to shard data based on the value range of a field. For example, data can be distributed to different database instances based on the range of user IDs. In this way, when you need to expand capacity, you can simply add new database instances and assign new data ranges to these instances without moving existing data.

[0104] Implementation steps

[0105] Select a shard key: Select a suitable field as the shard key, such as user ID, timestamp, etc.

[0106] Define a range: Define a data range for each database instance. For example:

[0107] Example A: User ID 1-1000

[0108] Instance B: User ID 1001-2000

[0109] Instance C: User ID 2001-3000

[0110] Routing requests: Implement routing logic at the application layer to determine which database instance to send the request to based on the shard key value in the request.

[0111] Capacity expansion: When capacity expansion is required, add a new database instance and define a new range. For example:

[0112] Instance D: User ID 3001-4000

[0113] Example E: User ID 4001-5000

[0114] Data insertion: New data will be directly inserted into the corresponding database instance according to its shard key value, without moving existing data.

[0115] In addition, the time field can also be used as a shard. For example, use the yyyyMM format, which is a 4-digit year and a 2-digit month, as the table subscript, and add a sheet for each month. This method also does not require moving data.

[0116] In addition to the above, you can also use composite sharding, that is, combining multiple sharding methods. For example, combining multiple sharding strategies, such as first performing range sharding and then performing hash sharding within each range. An example is as follows:

[0117] Select multiple sharding keys, first perform range sharding by the first key, and then perform hash sharding by the second key. When capacity expansion is required, new instances can be added to each range. The advantage of this method is that it can better control data distribution and adapt to different load conditions. However, it is complex to implement and more difficult to manage.

[0118] Although the embodiments of the present invention have been given and described above, they are not intended to limit the present invention. For those skilled in the art, various changes and variations may be made in the present application, including different computer languages ​​for implementation. Without departing from the principles and design ideas of the present invention, various changes, modifications, substitutions and variations of these embodiments shall all be included within the scope of the claims of the present invention.

[0119] Appendix: Java source code

[0120] The Java source code used as an example in the embodiment is shown below.

[0121]

[0122] < / t>

Claims

1. A database sharding design method, comprising a configuration information module and a context module, which are respectively used to store relevant configuration information of the device and used context information, characterized in that: include: SUID interface module, parsing module, JDBC interaction module and sharding engine module; 1) The SUID interface module is an interface provided to users, which can separate the interface from the implementation, so that users can complete what they need by just calling the interface without paying attention to the specific implementation details; 2) Parsing module, including analysis and collection of sharding information, determining whether there is sharding, and recording the sharding type, actual table list, ds list, table subscript, and the correspondence between table and ds in the context; parsing and generating SQL statements, putting ds, table and other information into the context; SQL rewriting; Call the JDBC interaction module; process the results before returning and process the context information of the current operation; Return result; 3) The JDBC interaction module includes determining whether it is sharding, processing of sharding main thread and sharding sub-thread, sharding main thread calling sharding engine module processing, processing of non-sharding requests, calling the underlying JDBC and processing the returned information and return results; 4) The sharding engine module processes various sharding types; generates SQL executed by each shard, rewrites the SQL into SQL using the actual table and records the corresponding ds; executes the operation in a child thread manner, merges and processes the results obtained after all child threads operate; query record types include sorting, obtaining a specified number of records, and aggregation includes recalculating avg, and further processing and calculating count and sum; 5) The configuration information module manages and stores configuration-related information; the context module manages and stores context information used by the system; other templates can use the information of these two templates; When the sharding engine module parses the sharding, a) if it is not a sharding, it returns; b) if it is a broadcast table query, a ds is randomly selected and set to the previous thread context; if it is a broadcast table change operation, the sharding identifier is recorded, and the sharding type is recorded as full database and full table; c) the sharding information is analyzed, if it is one database and one table, ds and table are set to the context of the current thread; if it is not one database and one table, the database and table types are recorded, and the sharding identifier, ds list and table list are recorded; When the parsing module parses and generates SQL, if it is judged that there is sharding and it is not a broadcast table and the table name is not set in the current thread context, the basic table name + special character subscript is used as the table name, and when operating on multiple tables, the basic table name is used as an alias; single-table operations do not require table aliases; Supported databases include MySQL, MariaDB, Oracle, H2, SQLite, and PostgreSQL; Support sharding of some tables and non-sharding of others; Sharding configuration information is simple; You can use only the table key without using the database key. To change the original table to a sharded table, you only need to add sharding configuration, and the code for operating the database corresponding to the original table does not need to be changed; The encoding complexity is 0(1).

2. The method according to claim 1, characterized in that: After the shard node structure changes, the old data does not need to be transferred; the shard node structure changes can be completed without shutting down the server.

3. The method according to claim 1, characterized in that: The database can be sharded by operating in an object-oriented manner; the sharding information is obtained by parsing entities and Conditions instead of parsing SQL strings.

4. The method according to claim 1, characterized in that: Table subscript placeholders are strings containing special characters enclosed in single quotes.

5. The method according to claim 1, characterized in that: The methods of the SUID interface module that only pass Class type parameters and id values ​​also support fragmentation.

6. A database sharding design method, characterized in that: Its shard routing algorithm supports multiple node structures. After the shard node structure changes, the old data does not need to be transferred, and the change of the shard node structure can be completed without shutting down the system.

7. A database sharding device, comprising a configuration information module and a context module, respectively used to store relevant configuration information of the device and used context information, characterized in that: include: SUID interface module, parsing module, JDBC interaction module and sharding engine module; 1) The SUID interface module is an interface provided to users, which can separate the interface from the implementation, so that users can complete what they need by just calling the interface without paying attention to the specific implementation details; 2) Parsing module, including analysis and collection of sharding information, determining whether there is sharding, and recording the sharding type, actual table list, ds list, table subscript, and the correspondence between table and ds in the context; parsing and generating SQL statements, putting ds, table and other information into the context; SQL rewriting; Call the JDBC interaction module; process the results before returning and process the context information of the current operation; Return result; 3) The JDBC interaction module includes determining whether it is sharding, processing of sharding main thread and sharding sub-thread, sharding main thread calling sharding engine module processing, processing of non-sharding requests, calling the underlying JDBC and processing the returned information and return results; 4) The sharding engine module processes various sharding types; generates SQL executed by each shard, rewrites the SQL into SQL using the actual table and records the corresponding ds; executes the operation in a child thread manner, merges and processes the results obtained after all child threads operate; query record types include sorting, obtaining a specified number of records, and aggregation includes recalculating avg, and further processing and calculating count and sum; 5) The configuration information module manages and stores configuration-related information; the context module manages and stores context information used by the system; other templates can use the information of these two templates; When the sharding engine module parses the sharding, a) if it is not a sharding, it returns; b) if it is a broadcast table query, a ds is randomly selected and set to the previous thread context; if it is a broadcast table change operation, the sharding identifier is recorded, and the sharding type is recorded as full database and full table; c) the sharding information is analyzed, if it is one database and one table, ds and table are set to the context of the current thread; if it is not one database and one table, the database and table types are recorded, and the sharding identifier, ds list and table list are recorded; When the parsing module parses and generates SQL, if it is judged that there is sharding and it is not a broadcast table and the table name is not set in the current thread context, the basic table name + special character subscript is used as the table name, and when operating on multiple tables, the basic table name is used as an alias; single-table operations do not require table aliases; Supported databases include MySQL, MariaDB, Oracle, H2, SQLite, and PostgreSQL; Support sharding of some tables and non-sharding of others; Sharding configuration information is simple; You can use only the table key without using the database key. To change the original table to a sharded table, you only need to add sharding configuration, and the code for operating the database corresponding to the original table does not need to be changed; After the shard node structure changes, the old data does not need to be transferred; Sharding node structure changes can be completed without downtime; The encoding complexity is 0(1).

8. The device according to claim 7, characterized in that The SUID interface module calls the parsing module to complete the database operation; The parsing module can call the sharding interceptor and parse the SQL; The parsing module can call the JDBC interaction module; The JDBC interaction module can call the sharding engine module.

Images