A database index processing method, system, device and readable storage medium
By adding a hashId to the MySQL database table and associating it with the index field, and using the hashId to replace the original index field, the problem of reduced query speed caused by excessively long B-tree index fields was solved, resulting in faster query speeds.
Patent Information
- Application Number
- CN202310656384.1
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2023-06-05
- Publication Date
- 2026-02-27
- Estimated Expiration
- 2043-06-05
AI Technical Summary
In existing technologies, the speed of file path queries using B-tree index fields in MySQL databases decreases significantly as the table data increases, and there is a lack of effective solutions. Usually, business planning is used to avoid querying long fields, but the effect is limited.
Add a compressed value field hashId to the database table and associate it with the index field. The hashId is obtained by calculating the MD5 hash value of the index field and is used in the SQL statement to replace the original index field. The hashId is used as the new index for querying.
Replacing the original index field with hashId improves query speed and reduces query time.
Smart Images
Figure CN116719815B_ABST
Abstract
Description
TECHNICAL FIELD
[0001] The present application relates to the technical field of data processing, in particular to a database index processing method, system, device and readable storage medium. BACKGROUND
[0002] The mysql database is a database used by most Internet enterprises in China. The index fields built in the database are all B-tree index fields. The B-tree index field has a feature that when the built index field is long, it will seriously affect the query speed. For example, in a file management table, the file path is generally stored. In use, the file path is often used to query the detailed information of the file, such as shown in the following table, so an index field is generally built for the file path. However, the file path is generally long, so even if an index field is added to the file path, the query speed is optimized, but when the table data becomes more and more, the query speed will still be significantly reduced. At present, there is no good solution for such similar situations (i.e. the commonly used query field is too long) in the industry. Generally, it is planned in advance from the business to avoid querying the field as much as possible. Figure 2 SUMMARY
[0003] In order to overcome the defects of the prior art, the present application provides a database index processing method, system, device and readable storage medium to solve the above problems.
[0004] The technical scheme adopted by the present application to solve its technical problems is: a database index processing method, comprising:
[0005] S1: adding a compression value field hashId in the table of the database, and associating the compression value field hashId with the index field in the table;
[0006] S2: calculating the value of hashId according to the field value of the index field;
[0007] S3: storing the value of hashId in the compression value field hashId, and modifying the sql statement of the database according to the value of hashId.
[0008] It is worth noting that in the step S2, the field value of the index field is calculated by MD5 hash, a 16-byte array is obtained, then the high 8 bits and the low 8 bits in the byte array are XORed to obtain an 8-byte array, and finally the byte array is converted into a long type number as the value of hashId.
[0009] Preferably, the database index processing method is based on the spring framework.
[0010] Optionally, the spring interception layer in the spring framework is marked by an @Aspect tag provided by the spring framework, and the method of the object to be intercepted is defined by a @Pointcut provided by the spring framework, and the method of the object is called by the spring framework.
[0011] Specifically, when the method of the object is called by the spring framework, the spring framework transmits a ProceedingJoinPoint point to the spring interception layer to obtain an index field; when the index field is directly marked by a @HashId tag, the value of the hashId is calculated based on the field value of the index field; when the index field is an entity object, the index field marked by the @HashId tag is searched, and the value of the hashId is calculated; and finally, the value of the HashId is recorded in the sqlContext.
[0012] It is worth noting that the database index processing method is based on a Mybatis framework.
[0013] Specifically, the Mybatis interception layer of the Mybatis framework obtains a sql statement to be executed from an intercept(Invocation invocation) method in an interface of the Mybatis interception layer, modifies the sql statement based on the value of the HashId in the sqlContext, and then sends the modified sql statement to the database for execution.
[0014] Preferably, a database index processing system includes a table establishing module, a hashId value calculating module, and a sql modifying module.
[0015] The table establishing module is configured to add a compressed value field hashId in a table of a database and associate the compressed value field hashId with an index field in the table.
[0016] The hashId value calculating module is configured to calculate the value of the hashId based on the field value of the index field.
[0017] The sql modifying module is configured to store the value of the hashId in the compressed value field hashId and modify a sql statement of the database based on the value of the hashId.
[0018] Optionally, a computer device includes a memory, a processor, and a computer program stored in the memory and executable on the processor, and the processor implements the steps of the database index processing method when executing the computer program.
[0019] Preferably, a computer readable storage medium, having stored thereon a computer program, the computer program being executed by a processor to implement the steps of the database index processing method.
[0020] The present application has the advantage that in the database index processing method, the original excessively long index field is replaced by the value of hashId, the value of hashId has a unique constraint relationship with the original excessively long index field, and by automatically modifying the sql statement and adding the value of hashId to the sql statement, the value of hashId can be used as a new index for querying, thereby improving the speed of querying. BRIEF DESCRIPTION OF DRAWINGS
[0021] Figure 1 Flow chart of the database index processing method in an embodiment of the present application;
[0022] Figure 2 Existing database table;
[0023] Figure 3 Database table formed in an embodiment of the present application;
[0024] Figure 4 Java code under the spring+mybatis framework in an embodiment of the present application;
[0025] Figure 5 Java code under the spring+mybatis framework in an embodiment of the present application;
[0026] Figure 6 Structural block diagram of the spring interception layer, the Mybatis interception layer and the sqlContext in an embodiment of the present application;
[0027] Figure 7 Java code of the label @HashId used in the parameter of the query method in an embodiment of the present application;
[0028] Figure 8 Java code of the label @HashId used in the field of the entity object in an embodiment of the present application;
[0029] Figure 9 Java code of the interception Figure 4 All methods of the UploadFileMapper in an embodiment of the present application;
[0030] Figure 10 Java code of the Mybatis interceptor interface implemented in an embodiment of the present application;
[0031] Figure 11 The flow chart of the Mybatis interception layer processing sql statements. DETAILED DESCRIPTION
[0032] The specific embodiments of the present application will be further described below with reference to the drawings. It should be noted that the description of these embodiments is used to help understand the present application, but does not constitute a limitation on the present application. In addition, the technical features involved in the various embodiments of the present application described below can be combined with each other as long as they do not conflict with each other.
[0033] As shown in Figures 1-11 , a database index processing method comprises:
[0034] S1: adding a compressed value field hashId in the table of the database, and associating it with the index field in the table;
[0035] S2: calculating the value of hashId according to the field value of the index field; specifically, the value of hashId is a hash code;
[0036] S3: storing the value of hashId in the compressed value field hashId, and modifying the sql statement of the database according to the value of hashId.
[0037] In the database index processing method, the original excessively long index field is replaced by the value of hashId, and the value of hashId has a unique constraint relationship with the original excessively long index field. In this way, by automatically modifying the sql statement and adding the value of hashId to the sql statement, the value of hashId can be used as a new index for querying, thereby improving the query speed.
[0038] It is worth noting that in the step S2, the field value of the index field is calculated by MD5 hash, obtaining a 16-length byte array, then the high 8 bits and low 8 bits in the byte array are XORed to obtain an 8-bit byte array, and finally the byte array is converted into a long type number as the value of hashId.
[0039] When building a table, a compressed value field needs to be added to the original field (corresponding to Figure 2 file_path) as shown in Figure 3 , and then the hashId in Figure 3 (corresponding to the original file_path) is modified as shown in Figure 3For example, the file_path value is "hi / world / hello / old / 180322 / 15216854979727799615.jpg". When the data is added and modified, the dao layer interception layer realized in the application automatically calculates the hashId value corresponding to the file_path value, and then automatically modifies the sql statement to save the hashId value corresponding to the file_path value into the table. When the data is queried, when the file_path value is the query condition "file_path='hi / world / hello / old / 180322 / 15216854979727799615.jpg'", the dao layer interception layer realized in the application automatically calculates the hashId value corresponding to the file_path value, and then automatically modifies the sql statement to modify the query condition from "file_path='hi / world / hello / old / 180322 / 15216854979727799615.jpg'" to "file_path='hi / world / hello / old / 180322 / 15216854979727799615.jpg' and hashId='-6133037869937340564'", thereby achieving the purpose of accelerating the query speed. For ordinary programmers, for this table, whether it is addition, deletion, modification or query, they only need to pass the file_path field value in the dao layer.
[0040] Optionally, the database index processing method is based on a spring framework. The database index processing method is based on a Mybatis framework. The overall structure of the application is shown in Figure 6 , which is divided into a spring interception layer, a Mybatis interception layer and a sqlContext. When spring+mybatis is used, only the interface needs to be written, and the sql corresponding to the interface needs to be written in the xml file. As shown in Figure 4 and Figure 5 , a java interface (such as Figure 4 UploadFileMapper) corresponds to an xml ( Figure 5The MyBatis framework generates an implementation of the java interface using the xml file (the file name is: uploadFileMapper.xml), and the Spring framework generates a proxy object for the interface, which directly calls the implementation generated by the MyBatis framework. When the getByPath method of the UploadFileMapper object is called in the business code, the getByPath method of the proxy object generated by the Spring framework is actually called. The method inside the proxy object calls the implementation of the MyBatis, which generates the sql statement written in the xml and executes the statement.
[0041] Preferably, the spring intercepting layer in the Spring framework is annotated by the @Aspect tag provided by the Spring framework, the method of the object to be intercepted is defined by the @Pointcut provided by the Spring framework, and the method of the object is called by the Spring framework.
[0042] Specifically, when the method of the object is called by the Spring framework, the Spring framework transmits the ProceedingJoinPoint point to the spring intercepting layer to obtain the index field; when the index field is directly annotated by the @HashId tag, the hashId value is calculated using the field value of the index field; when the index field is an entity object, the index field annotated by the @HashId tag is found, and the hashId value is calculated; and finally, the hashId value is recorded in the sqlContext.
[0043] The application provides a tag @HashId, which, when used on the parameter of a query method (for example, Figure 7 ), tells the spring interceptor that the hashId value is used as a query condition in the sql statement, and the hashId value is calculated from the field value of the annotated index field. When the tag @HashId is used on the field of an entity object (the entity object is a java object mapped with a table in the database) (for example, Figure 8 ), the interceptor is told that when there is an addition or an update, if the annotated field has a value, the hashId field value needs to be added or updated, and the hashId value is calculated from the annotated field. The spring intercepting layer is a java object, for example, Figure 9 , which needs to be annotated by the @Aspect tag provided by the Spring, and the method of the object is defined by the @Pointcut in the method of the object, Figure 9 The definition in the Spring framework will intercept Figure 4All methods of the UploadFileMapper. When the method is called, the spring framework passes the parameter ProceedingJoinPoint point to the spring interception layer, and the index field can be obtained from the parameter. When the index field is directly marked by @HashId, the hashId value is calculated using the field value of the index field. When the index field is an entity object, the index field marked by @HashId is found, and the hashId value is calculated. If the hashId value can be finally obtained, the value is recorded in the sqlContext.
[0044] It is worth noting that the Mybatis interception layer of the Mybatis framework obtains the sql statement to be executed from the parameter invocation of the intercept(Invocation invocation) method in the interface of the Mybatis interception layer, modifies the sql statement according to the HashId value in the sqlContext, and then sends the modified sql statement to the database for execution.
[0045] The Mybatis interception layer is an object that implements the Mybatis interceptor interface. Figure 10 Before executing the sql statement, the Mybatis interception layer calls the intercept(Invocation invocation) method in the interface, and the sql statement to be executed can be obtained from the parameter invocation of the method. Specifically, when the HashId value exists in the sqlContext, the processing as shown in Figure 11
[0046] If it is an INSERT INTO statement, there are two cases of statements:
[0047] a. "INSERT INTO table_name set column1=value1, column2=value2..." will add "hash_id=hashi value" in the set part, and be modified to: "INSERT INTO table_name set hash_id=hashi value, column1=value1, column2=value2..."
[0048] b. "INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...)" will be modified to "INSERT INTO table_name (hash_id, column1, column2,...) VALUES (hashi value, value1, value2,...)" by adding the field name "hash_id" in the first bracket and the value of hashi in the second bracket.
[0049] If it is an UPDATE statement: "UPDATE table name SET column name = new value WHERE column name = certain value", the "hash_id = hashi value" is added in the SET part to be modified to "IUPDATE table name SET column name = new value, hash_id = hashi value WHERE column name = certain value".
[0050] If it is a SELECT or DELETE statement, "and hash_id = hashi value" is added in the from part.
[0051] A database index processing system, comprising a table establishing module, a hashId value calculating module and a sql modifying module;
[0052] The table establishing module is configured to add a compressed value field hashId in a table of a database and associate the compressed value field hashId with an index field in the table.
[0053] The hashId value calculating module is configured to calculate a value of hashId according to a field value of the index field.
[0054] The sql modifying module is configured to store the value of hashId in the compressed value field hashId and modify a sql statement of the database according to the value of hashId.
[0055] A computer device, comprising a memory, a processor and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the steps of the database index processing method.
[0056] A computer readable storage medium, having a computer program stored thereon, wherein the computer program is executable by a processor to implement the steps of the database index processing method.
[0057] The embodiments of the present application are described in detail above with reference to the drawings, but the present application is not limited to the described embodiments. For those skilled in the art, various changes, modifications, replacements and variations of the embodiments can be made without departing from the principles and spirits of the present application, and still fall within the protection scope of the present application.
Claims
1. A database index processing method, characterized in that, include: S1: Add a compressed value field hashId to the table in the database and associate it with the index field in the table; S2: Calculate the hashId value based on the field value of the index field; In step S2, the field value of the index field is hashed using MD5 to obtain a byte array of length 16. Then, the high 8 bits and low 8 bits of the byte array are XORed to obtain an 8-bit byte array. Finally, the byte array is converted into a long type number as the hashId value. S3: Store the hashId value in the compressed value field hashId, and modify the SQL statement in the database according to the hashId value.
2. The database indexing method according to claim 1, characterized in that: The database index processing method is based on the Spring framework.
3. The database indexing method according to claim 2, characterized in that: The Spring interception layer in the Spring framework annotates objects with the @Aspect tag provided by the Spring framework, defines the methods of the objects to be intercepted with the @Pointcut tag provided by the Spring framework, and then the methods of the objects are called by the Spring framework.
4. The database indexing method according to claim 3, characterized in that: When an object's method is called by the Spring framework, the Spring framework sends a ProceedingJoinPoint to the Spring interception layer to retrieve the index field. When the index field is directly annotated with the @HashId tag, the hashId value is calculated using the field value of this index field. When the index field is an entity object, the index field annotated with the @HashId tag is found, and the hashId value is calculated. Finally, the hashId value is recorded in the sqlContext.
5. A database indexing method according to claim 4, characterized in that: The database indexing method is based on the Mybatis framework.
6. A database indexing method according to claim 5, characterized in that: The Mybatis interception layer of the Mybatis framework obtains the SQL statement to be executed from the invocation parameter of the intercept(Invocation invocation) method in its own interface by calling the intercept(Invocation invocation) method, modifies the SQL statement according to the HashId value in sqlContext, and then sends the modified SQL statement to the database for execution.
7. A database indexing processing system, characterized in that: The database index processing method according to claim 1 includes a table creation module, a hashId value calculation module, and an SQL modification module; The table creation module is used to add a compressed value field hashId to the table in the database and associate it with the index field in the table; The hashId value calculation module is used to calculate the hashId value based on the field value of the index field; The SQL modification module is used to store the hashId value in the compressed value field hashId, and modify the SQL statement in the database according to the hashId value.
8. A computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 6.
9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.