Method and device for analyzing rationality of database index

Abstract

The present invention discloses a method and device for analyzing the rationality of a database index, and relates to the field of computer technology. A specific implementation of the method includes: obtaining multiple executable data manipulation statements; determining an execution plan related to each executable data manipulation statement; parsing the execution plan to determine the index applied to execute each of the executable data manipulation statements and the number of data rows scanned by the index; and analyzing the rationality of the index based on the index and the number of data rows scanned by the index. This implementation method parses the execution plans of multiple executable data manipulation statements to determine the index applied to each executable data manipulation statement and the number of data rows scanned by the index, and based on each index and the number of data rows scanned by the index, it can actively discover data manipulation statements without indexes or with poor index effects, thereby analyzing the rationality of the index, and then adjusting the index distribution to balance retrieval and writing performance.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a method and device for analyzing the rationality of a database index. Background Art

[0002] In the use of databases, an index is a separate, physical storage structure that arranges the values ​​of one or more columns in a database table. It is a decentralized data structure created to speed up the retrieval of data rows in a table. The role of an index is equivalent to the catalog of a book, and the required content can be quickly found according to the page number in the catalog. Indexes are very critical and directly affect the performance of the database itself, thereby affecting system performance, such as the time consumed by SQL (Structured Query Language) execution, the utilization rate of the database server CPU (Central Processing Unit) or the utilization rate of disk IO (Input / Output). In order to improve system performance, R&D personnel need to establish a considerable number of indexes on the table. If they are missed, a large amount of data will be scanned when executing SQL, and the reading and writing time will be very high. At the same time, it may also use a large amount of system resources, such as CPU and disk IO. In severe cases, it will occupy all CPUs, causing the database to be unable to respond to other requests, thereby causing the application that relies on the database to be unable to continue to provide services to users. If too many indexes are established, that is, unused or rarely used indexes are established, the database storage overhead will be increased, and the processing overhead for insert, delete, and update operations will also increase, which has a greater negative impact. The above two aspects are the pain points of R&D execution system optimization. After a period of time, the system is very likely to miss indexes, resulting in scanning the entire table and causing system accidents. Too many indexes affect write performance. In addition, because there is no reference, it is uncertain whether which indexes are used, so they cannot be deleted.

[0003] Currently, you can analyze the rationality of the index by subscribing and analyzing the logs.

[0004] In the process of implementing the present invention, the inventors found that there are at least the following problems in the prior art:

[0005] By subscribing to and analyzing logs to analyze the rationality of indexes, you can find SQLs with high read and write time consumption, but you cannot determine whether the high read and write time consumption is due to the lack of indexes or the inappropriate indexes that are created. In addition, analyzing logs cannot find indexes that are created but not used. Summary of the invention

[0006] In view of this, an embodiment of the present invention provides a method and device for analyzing the rationality of database indexes, which can actively discover data manipulation statements without indexes or with poor index effects, so as to analyze the rationality of indexes and further adjust index distribution to balance retrieval and writing performance.

[0007] To achieve the above-mentioned purpose, according to one aspect of an embodiment of the present invention, a method for analyzing the rationality of a database index is provided, comprising: obtaining a plurality of executable data manipulation statements; determining an execution plan associated with each of the executable data manipulation statements; parsing the execution plan to determine the index applied to execute each of the executable data manipulation statements and the number of data rows scanned by the index; and analyzing the rationality of the index based on the index and the number of data rows scanned by the index.

[0008] Optionally, obtaining executable data manipulation statements includes: using javaagent technology to obtain pre-executed data manipulation statements in a source database; assembling the pre-executed data manipulation statements, context information and parameter information of the pre-executed data manipulation statements to obtain executable database manipulation statements.

[0009] Optionally, the method further includes: storing the executable database manipulation statement in a storage unit, and marking the executable database manipulation statement as not replayed, and after determining an execution plan related to the unreplayed, executable database manipulation statement, marking the unreplayed, executable database manipulation statement as replayed.

[0010] Optionally, determining the execution plan associated with each of the executable data manipulation statements includes: determining a playback data source identifier based on context information of the non-played back, executable data manipulation statements to determine a playback database; and sending an execution plan acquisition instruction to the playback database to obtain the execution plan associated with the non-played back, executable data manipulation statements.

[0011] Optionally, before sending an instruction to obtain an execution plan to the playback database, the method further includes: determining whether the playback database supports directly obtaining an execution plan related to the non-playback, executable data manipulation statement based on the type of the non-playback, executable data manipulation statement and the version of the playback database; if not, rewriting the non-playback, executable data manipulation statement into a target statement according to a preset rewriting rule, wherein the playback database supports directly obtaining an execution plan related to the target statement.

[0012] Optionally, the method further comprises: parsing the execution plan to determine the data table applied to execute each of the executable data manipulation statements;

[0013] According to the index and the number of data rows scanned by the index, analyzing the rationality of the index includes: determining the number of executions of each index; according to the data table, determining the indexes belonging to the same data table to calculate the total number of executions of all indexes on the data table; for each index, determining the utilization rate of the index according to the ratio of the number of executions of the index and the total number of executions of all indexes on the data table to which the index belongs; determining the performance value of the index according to the ratio of the number of data rows scanned by the index and the total number of rows of the data table to which the index belongs; analyzing the rationality of the index according to the utilization rate and performance value of each index.

[0014] To achieve the above-mentioned purpose, according to one aspect of an embodiment of the present invention, there is provided a device for analyzing the rationality of a database index, comprising: an acquisition module for acquiring a plurality of executable data manipulation statements; an execution plan determination module for determining an execution plan associated with each of the executable data manipulation statements; an index determination module for parsing the execution plan to determine an index applied to execute each of the executable data manipulation statements and the number of data rows scanned by the index; and an analysis module for analyzing the rationality of an index based on the index and the number of data rows scanned by the index.

[0015] Optionally, the acquisition module is further used to: use javaagent technology to acquire pre-executed data manipulation statements in the source database; assemble the pre-executed data manipulation statements, context information and parameter information of the pre-executed data manipulation statements to obtain executable database manipulation statements.

[0016] Optionally, the device also includes a storage unit for storing the executable database manipulation statement; a marking module for marking the executable database manipulation statement as not played back when storing the executable database manipulation statement in the storage unit, and after determining the execution plan related to the non-played back, executable database manipulation statement, marking the non-played back, executable database manipulation statement as played back.

[0017] Optionally, the execution plan determination module is also used to: determine the playback data source identifier based on the context information of the non-played back and executable data manipulation statements to determine the playback database; send an execution plan acquisition instruction to the playback database to obtain the execution plan related to the non-played back and executable data manipulation statements.

[0018] Optionally, the execution plan determination module is also used to determine whether the playback database supports direct acquisition of execution plans related to the non-playback, executable data manipulation statements based on the type of the non-playback, executable data manipulation statements and the version of the playback database; if not, rewriting the non-playback, executable data manipulation statements into target statements according to preset rewriting rules, wherein the playback database supports direct acquisition of execution plans related to the target statements.

[0019] Optionally, the index determination module is further used to: parse the execution plan to determine the data table applied to execute each executable data manipulation statement;

[0020] The analysis module is also used to: determine the number of executions of each index; determine the indexes on the same data table based on the data table to calculate the total number of executions of all indexes on the data table; for each index, determine the utilization of the index based on the ratio of the number of executions of the index to the total number of executions of all indexes on the data table to which the index belongs; determine the performance value of the index based on the ratio of the number of data rows scanned by the index to the total number of rows in the data table to which the index belongs; and analyze the rationality of the index based on the utilization and performance value of each index.

[0021] To achieve the above-mentioned purpose, according to one aspect of an embodiment of the present invention, there is provided an electronic device, comprising: one or more processors; a storage device for storing one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors implement the method for analyzing the rationality of a database index according to an embodiment of the present invention.

[0022] To achieve the above objective, according to one aspect of an embodiment of the present invention, a computer-readable medium is provided, on which a computer program is stored, and when the program is executed by a processor, the method for analyzing the rationality of a database index according to an embodiment of the present invention is implemented.

[0023] An embodiment of the above invention has the following advantages or beneficial effects: because it adopts the method of obtaining multiple executable data manipulation statements in the source database; determining the execution plan related to each of the executable data manipulation statements; parsing the execution plan to determine the index applied to execute each of the executable data manipulation statements and the number of data rows scanned by the index; and analyzing the rationality of the index of the source database according to the index and the number of data rows scanned by the index, it can actively discover data manipulation statements without indexes or with poor index effects, thereby analyzing the rationality of the index, and then adjusting the index distribution, balancing the retrieval and writing performance, and solving the technical problem that as the system demand grows, a large number of indexes are established on the table, and there is no effective reference data to evaluate the index benefits.

[0024] The further effects of the above-mentioned non-conventional optional manner will be described below in conjunction with the specific implementation manner. BRIEF DESCRIPTION OF THE DRAWINGS

[0025] The accompanying drawings are used to better understand the present invention and do not constitute an improper limitation of the present invention.

[0026] Figure 1 is a schematic diagram of the main process of a method for analyzing rationality of a database index according to an embodiment of the present invention;

[0027] Figure 2 is a schematic diagram of a sub-process of a method for analyzing rationality of a database index according to an embodiment of the present invention;

[0028] Figure 3 is a schematic diagram of the main process of a method for analyzing rationality of a database index according to another embodiment of the present invention;

[0029] Figure 4 is a schematic diagram of the main process of a method for analyzing rationality of a database index according to another embodiment of the present invention;

[0030] Figure 5 is a schematic diagram of main modules of an apparatus for analyzing rationality of a database index according to an embodiment of the present invention;

[0031] Figure 6 is a schematic diagram of main modules of an apparatus for analyzing rationality of a database index according to another embodiment of the present invention;

[0032] Figure 7 is an exemplary device architecture diagram to which embodiments of the present invention may be applied;

[0033] Figure 8 It is a schematic diagram of the structure of a computer device of a terminal device or a server suitable for implementing an embodiment of the present invention. DETAILED DESCRIPTION

[0034] The following is a description of exemplary embodiments of the present invention in conjunction with the accompanying drawings, including various details of the embodiments of the present invention to facilitate understanding, which should be considered as merely exemplary. Therefore, it should be recognized by those of ordinary skill in the art that various changes and modifications may be made to the embodiments described herein without departing from the scope and spirit of the present invention. Similarly, for clarity and conciseness, the description of well-known functions and structures is omitted in the following description.

[0035] Figure 1 FIG. 1 is a schematic diagram of the main process of the method for analyzing the rationality of a database index according to an embodiment of the present invention. Figure 1 As shown, the method includes:

[0036] Step S101: Get multiple executable data manipulation statements. Among them, data manipulation statements (Data Manipulation Language, DML) are a set of instructions in the SQL language that are responsible for running data access work on database objects. They are centered on four instructions: SELECT, INSERT, UPDATE, and DELETE, which represent selection, insertion, update, and deletion, respectively. They are instructions that must be used when developing data-centric applications. Different from database definition language (Data Definition Language, DDL) and database control language (Data Control Language, DCL), the performance of data manipulation statements (hereinafter referred to as DML statements) is more dependent on index efficiency, so the rationality of indexes can be analyzed through data manipulation statements.

[0037] In an optional embodiment, the executable DML statement may be determined according to the following process:

[0038] Use javaagent technology to obtain pre-executed data manipulation statements in the source database;

[0039] The pre-executed data manipulation statement, context information of the pre-executed data manipulation statement, and parameter information are assembled to obtain an executable database manipulation statement.

[0040] Among them, javaagent technology is a technology that can modify bytecode without affecting normal compilation. Using javament technology to obtain pre-executed DML statements can achieve the AOP (Aspect Oriented Programming) effect at the JVM (Java Virtual Machine) level, which is a non-intrusive collection method for existing businesses. During the process of executing DML statements in the target application, DML statements are obtained without perception, and the target application can use the method of the embodiment of the present invention without modifying the code.

[0041] As a specific example, Figure 2As shown in the figure, the original JDBC Statement is enhanced by javament technology, and the execute, executeQuery, and executeUpdate behaviors are rewritten to obtain the pre-executed DML statements, and then the DML statement context information and parameter information are assembled to obtain executable DML statements. Among them, JDBC (Java DataBaseConnectivity) is a Java API for executing SQL statements, which can provide unified access to multiple relational databases. It consists of a set of classes and interfaces written in Java. Statement is the interface of JDBC, which provides three methods for executing DML statements: executeQuery, executeUpdate, and execute. Which method is used is determined by the content generated by the DML statement. The executeQuery method is used for statements that generate a single result set, such as SELECT statements. The executeUpdate method is used to execute INSERT, UPDATE, or DELETE statements and DDL (Data Definition Language) statements, such as CREATE TABLE and DROP TABLE. The execute method is used to execute statements that return multiple result sets, multiple update counts, or a combination of the two.

[0042] The context information of the pre-executed data manipulation statement refers to some information about the DML statement execution environment, such as the type of DML statement, the execution time of the DML statement, the database URL (Uniform Resource Locator), the database name, the application identifier, the client IP, etc. Parameter information refers to the parameters used in the DML statement.

[0043] In an optional embodiment, in order to compress the executable DML statements as much as possible, the format of the executable DML statements may be defined as shown in Table 1 below.

[0044] Table 1:

[0045]

[0046]

[0047] Among them, appId refers to the application identifier; sqlType indicates the DML statement type, such as SELECT\UPDATE\DELETE; clientIp indicates the client IP; dataSourceTag indicates the playback data source identifier, and the mapping relationship between appId and dataSourceTag can be maintained through the configuration center; dbType indicates the database type; dbVersion indicates the database version.

[0048] Step S102: Determine the execution plan associated with each executable data manipulation statement. The execution plan refers to the execution path and execution steps determined after analyzing the SQL statement inside the database. It is the way an SQL statement executes data, indicating how and in what order to operate the database table.

[0049] Specifically, the execution plan associated with the executable data manipulation statement can be determined according to the following process:

[0050] Determine a playback data source identifier according to context information of the executable data manipulation statement to determine a playback database;

[0051] An execution plan acquisition instruction is sent to the playback database to acquire an execution plan associated with the data manipulation statement that is not played back.

[0052] In an optional embodiment, this step can be implemented through a distributed scheduling service.

[0053] Step S103: parsing the execution plan to determine the index applied to execute each executable data manipulation statement and the number of data rows scanned by the index.

[0054] The execution plan obtained is slightly different on different databases. Taking the MYSQL database as an example, the execution plan obtained is as follows:

[0055]

[0056]

[0057] The meaning of each field is as follows: id indicates the order of executing SELECT statements; select_type indicates whether the corresponding row is a simple or complex SELECT statement; table indicates the table name; type indicates what type of connection is used; possible_keys indicates the index that may be applied to this table; key indicates the actual index used; key_len indicates the length of the actual index used; ref indicates which column of the index is used; rows indicates how many rows of data are scanned by the DML statement; Extra indicates additional information on how MYSQL handles and unloads queries. The embodiment of the present invention can determine the index applied by the DML statement according to the value of the key field. If the value of the key field is empty, it means that there is no corresponding index according to the DML statement, and the value of the rows field determines the number of data rows scanned by the index. The embodiment of the present invention can also determine the data table applied to the executable DML statement according to the value of the table field.

[0058] Step S104: Analyze the rationality of the index based on the index and the number of data rows scanned by the index.

[0059] Specifically, the rationality of the index of the source database can be analyzed according to the following process:

[0060] Determine the number of times each index is executed based on the index applied by each executable DML statement;

[0061] According to the data table, determine the indexes belonging to the same data table to calculate the total execution times of all indexes on the data table;

[0062] For each index, the utilization rate of the index is determined based on the ratio of the number of executions of the index to the total number of executions of all indexes on the data table to which the index belongs; the performance value of the index is determined based on the ratio of the number of data rows scanned by the index to the total number of rows in the data table to which the index belongs;

[0063] Analyze the rationality of indexes based on the utilization and performance value of each index.

[0064] More specifically, the execution count of each index can be obtained as follows: a counter can be maintained for each index on the table (which can be obtained by using show index from table name). When determining the execution plan related to the executable DML statement, if the value of the key field is not empty, 1 is added to the corresponding counter. The final value of each counter is the execution count of the corresponding index. The total execution count of all indexes on the table can be obtained as follows: a counter can be maintained for each table. If the index on the table is applied, 1 is added to the corresponding counter. The final value of each counter is the total execution count of all indexes on the table.

[0065] In an optional embodiment, an index with a performance value lower than a preset threshold (eg, 30%) may be used as an index of low performance.

[0066] In an optional embodiment, the utilization rate, performance value and analysis results of the index may be generated into a data report and pushed to the R&D personnel via email or text message.

[0067] The embodiment of the present invention provides quantifiable indicators: index utilization and index performance value. R&D personnel can use this indicator to adjust the index distribution on the table, balance the retrieval and write performance, and solve the technical problem that as the demand of the system grows, a large number of indexes are established on the table, but there is no effective reference data to evaluate the index benefits. The embodiment of the present invention can actively discover the operation of SQL statements without indexes or SQL statements with poor index effects, and can push them to R&D personnel through emails, text messages, etc., providing an effective basis for system optimization.

[0068] Figure 3 FIG. 1 is a schematic diagram of the main process of the method for analyzing the rationality of a database index according to an embodiment of the present invention. Figure 3 As shown, the method includes:

[0069] Step S301: using javaagent technology to obtain pre-executed data manipulation statements in the source database;

[0070] Step S302: Assembling the pre-executed data manipulation statement, context information and parameter information of the pre-executed data manipulation statement to obtain an executable database manipulation statement;

[0071] Step S303: storing the executable database manipulation statement in a storage unit, and marking the executable database manipulation statement as not played back;

[0072] Step S304: determining a playback data source identifier according to the context information of the unplayed and executable data manipulation statement to determine the playback database;

[0073] Step S305: Sending an execution plan acquisition instruction to the playback database to acquire an execution plan related to the unplayed and executable data manipulation statement, and after acquiring the execution plan, marking the unplayed and executable database manipulation statement as played back;

[0074] Step S306: parsing the execution plan to determine the index applied to execute each executable data manipulation statement, the number of data rows scanned by the index, and the applied data table;

[0075] Step S307: Determine the index utilization and index performance value according to the index, the number of data rows scanned by the index, and the data table to which it is applied, so as to analyze the rationality of the index of the source database.

[0076] This embodiment and Figure 1 The embodiment shown is different in that: this embodiment stores the obtained executable DML statements in a storage unit for persistent storage, and marks the executable database manipulation statements as unplayed when storing; after obtaining the execution plan, the unplayed, executable database manipulation statements are marked as played back, and repeated execution can be avoided when determining the execution plan by marking. The storage unit can adopt a column-oriented open source database Hbase or a search server Elasticsearch, etc., and the present invention does not limit this.

[0077] As a specific example, the executable DML statements may be stored in the storage unit in the form of a table, as shown in the following Table 2:

[0078]

[0079]

[0080] In an optional embodiment, before sending the instruction to obtain the execution plan to the playback database in step S305, the unplayed DML statements in the storage unit may be fragmented according to the rowKey, and the Scheduler respectively obtains the DML statements belonging to the respective fragments.

[0081] In other optional embodiments, an alias is declared in a DML statement, and the value of the table field will display the alias corresponding to the table name, which needs to be analyzed and replaced with the actual table name.

[0082] Specifically, 1. Determine whether the table column value is an alias: use the show tables command to obtain all table names in the playback database, and compare them with the value of the table field. If there is no identical value, the value of the table field is the table alias, and you need to obtain the table name corresponding to the alias; 2. Split the DML statement into an array based on spaces; 3. Obtain the table name corresponding to the alias: find the array coordinates of the alias from the array, and look forward to the previous array element. The array element is the table name corresponding to the alias, and replace the alias in the execution plan with the table.

[0083] For example:

[0084] EXPLAIN SELECT * FROM table1t1

[0085] WHERE EXISTS(

[0086] SELECT 1

[0087] FROM table3

[0088] WHERE column_id=t1.column_id)

[0089] Split the DML statement into an array by spaces:

[0090]

[0091] The array element table1 before the alias t1 is the corresponding table name.

[0092] Figure 4 FIG. 1 is a schematic diagram of the main process of the method for analyzing the rationality of a database index according to an embodiment of the present invention. Figure 4 As shown, the method includes:

[0093] Step S401: using javaagent technology to obtain pre-executed data manipulation statements in the source database;

[0094] Step S402: Assembling the pre-executed data manipulation statement, context information and parameter information of the pre-executed data manipulation statement to obtain an executable database manipulation statement;

[0095] Step S403: storing the executable database manipulation statement in a storage unit, and marking the executable database manipulation statement as not played back;

[0096] Step S404: determining a playback data source identifier according to the context information of the unplayed and executable data manipulation statement to determine the playback database;

[0097] Step S405: determining whether the playback database supports directly obtaining an execution plan related to the unplayed and executable data manipulation statement according to the type of the unplayed and executable data manipulation statement and the version of the playback database;

[0098] Step S406: If not, rewrite the unplayed data manipulation statement into a target statement according to a preset rewriting rule, wherein the playback database supports directly obtaining an execution plan related to the target statement.

[0099] Step S407: Sending an execution plan acquisition instruction to the playback database to acquire an execution plan related to the unplayed and executable data manipulation statement or target statement, and after acquiring the execution plan, marking the unplayed and executable database manipulation statement or target statement as played back;

[0100] Step S408: parsing the execution plan to determine the index applied to execute each executable data manipulation statement, the number of data rows scanned by the index, and the applied data table;

[0101] Step S409: Determine the index utilization and index performance value according to the index, the number of data rows scanned by the index, and the applied data table, so as to analyze the rationality of the index of the source database.

[0102] This embodiment and Figure 3 The embodiment shown is different in that: before sending the instruction to obtain the execution plan to the playback database, the embodiment first determines whether the DML statement that has not been played back needs to be rewritten, because some low-version databases (such as MYSQL databases with a version lower than 5.6) or certain types of data manipulation statements (such as Update type data manipulation statements) do not support direct acquisition of the execution plan, so it is necessary to rewrite the DML statement that does not support direct acquisition of the execution plan into a target statement that supports direct acquisition of the execution plan. The rewriting rule can be determined according to the specific database version or the specific type of data manipulation statement.

[0103] The following uses the UPDATE statement of a MYSQL database with a version lower than 5.6 as an example to illustrate the rewriting rules.

[0104] The UPDATE statement is defined as follows:

[0105] UPDATE[row-limitation]table-list]

[0106] SET set-item,...

[0107] [FROM table-expression[,...]]

[0108] [WHERE search-condition]

[0109] [ORDER BY expression[ASC|DESC],...]

[0110] [OPTION(query-hint,...)]

[0111] table-list:

[0112] table-name[,...]

[0113] table-name:

[0114] [owner.]table-name[[AS]correlation-name]

[0115] |[owner.]view-name[[AS]correlation-name]

[0116] |derived-table

[0117] derived-table:

[0118] (select-statement)

[0119] [AS]correlation-name[(column-name[,...])]

[0120] table-expression:

[0121] A full table expression that can include joins.

[0122] 1. If the SET clause of the UPDATE statement contains a subquery (SELECT) statement, first disassemble the subquery in the UPDATE statement, for example:

[0123] UPDATE table1alias

[0124] SET(column_name,column_name)=(

[0125] SELECT(column_name,column_name)

[0126] FROM table2

[0127] WHERE column_name=alias.column_name)

[0128] WHERE column_name = VALUE;

[0129] Will be broken down into two statements:

[0130]

[0131]

[0132] After disassembly, rewrite the UPDATE or DELETE statement into a SELECT statement:

[0133]

[0134] 2. If the WHERE clause in an UPDATE or DELETE statement contains a subquery (SELECT) statement, there is no need to disassemble it. Just rewrite the UPDATE or DELETE statement directly, for example:

[0135] UPDATE table1alias

[0136] SET column_name = VALUE1

[0137] WHERE column_id IN (

[0138] SELECT column_id

[0139] FROM table2)

[0140] The rewritten SELECT statement is as follows:

[0141]

[0142] 3. The UPDATE statement for multi-table associations is disassembled using the same principle, as follows:

[0143] UPDATE table1t1

[0144] SET t1.column_name=(

[0145] SELECT t2.column_name

[0146] FROM tables2t2

[0147] WHERE t2.column_id=t1.column_id)

[0148] WHERE EXISTS(

[0149] SELECT 1

[0150] FROM table3t3

[0151] WHERE t3.column_id=t1.column_id)

[0152] After disassembly and rewriting, it is as follows:

[0153]

[0154] Figure 5is a schematic diagram of the main modules of the apparatus 500 for analyzing the rationality of a database index according to an embodiment of the present invention, such as Figure 5 As shown, the device 500 includes:

[0155] An acquisition module 501 is used to acquire a plurality of executable data manipulation statements;

[0156] An execution plan determination module 502, used to determine an execution plan associated with each of the executable data manipulation statements;

[0157] An index determination module 503 is used to parse the execution plan to determine the index applied to execute each executable data manipulation statement and the number of data rows scanned by the index;

[0158] The analysis module 504 is used to analyze the rationality of the index according to the index and the number of data rows scanned by the index.

[0159] Optionally, the acquisition module 501 is further used for:

[0160] Use javaagent technology to obtain pre-executed data manipulation statements in the source database;

[0161] The pre-executed data manipulation statement, context information of the pre-executed data manipulation statement, and parameter information are assembled to obtain an executable database manipulation statement.

[0162] Optionally, the execution plan determination module 502 is further used to: determine a playback data source identifier according to context information of the executable data manipulation statement to determine a playback database;

[0163] An execution plan acquisition instruction is sent to the playback database to acquire an execution plan associated with the executable data manipulation statement.

[0164] Optionally, the index determination module 503 is further used to: parse the execution plan to determine the data table applied to execute each executable data manipulation statement;

[0165] The analysis module 504 is also used for:

[0166] Determine the number of executions for each index;

[0167] According to the data table, determine the indexes belonging to the same data table to calculate the total execution times of all indexes on the data table;

[0168] For each index, the utilization rate of the index is determined based on the ratio of the number of executions of the index to the total number of executions of all indexes on the data table to which the index belongs; the performance value of the index is determined based on the ratio of the number of data rows scanned by the index to the total number of rows in the data table to which the index belongs;

[0169] Analyze the rationality of indexes based on the utilization and performance value of each index.

[0170] Figure 6 is a schematic diagram of the main modules of the apparatus 600 for analyzing the rationality of a database index according to an embodiment of the present invention, such as Figure 6 As shown, the device 600 includes:

[0171] An acquisition module 601 is used to acquire a plurality of executable data manipulation statements;

[0172] The storage unit 602 is used to store the executable database manipulation statement;

[0173] The marking module 603 is used to mark the executable database manipulation statement as not played back when storing the executable database manipulation statement in the storage unit, and after determining the execution plan related to the unplayed and executable database manipulation statement, mark the unplayed and executable database manipulation statement as played back;

[0174] An execution plan determination module 604, used to determine an execution plan associated with each of the unplayed and executable data manipulation statements;

[0175] An index determination module 605 is used to parse the execution plan to determine the index applied to execute each executable data manipulation statement and the number of data rows scanned by the index;

[0176] The analysis module 606 is used to analyze the rationality of the index according to the index and the number of data rows scanned by the index.

[0177] Optionally, the execution plan determination module 605 is also used to

[0178] According to the type of the unreplayed and executable data manipulation statement and the version of the replay database, determining whether the replay database supports directly obtaining an execution plan related to the unreplayed and executable data manipulation statement;

[0179] If not, the unplayed and executable data manipulation statement is rewritten into a target statement according to a preset rewriting rule, wherein the playback database supports directly obtaining an execution plan related to the target statement.

[0180] The above device can execute the method provided by the embodiment of the present invention, and has the functional modules and beneficial effects corresponding to the execution method. For technical details not described in detail in this embodiment, please refer to the method provided by the embodiment of the present invention.

[0181] Figure 7 An exemplary device architecture 700 is shown to which the method for analyzing database index rationality or the device for analyzing database index rationality according to the embodiments of the present invention can be applied.

[0182] like Figure 7 As shown, the device architecture 700 may include terminal devices 701, 702, 703, a network 704 and a server 705. The network 704 is used to provide a medium for communication links between the terminal devices 701, 702, 703 and the server 705. The network 704 may include various connection types, such as wired, wireless communication links or optical fiber cables, etc.

[0183] Users can use terminal devices 701, 702, 703 to interact with server 705 through network 704 to receive or send messages, etc. Various communication client applications can be installed on terminal devices 701, 702, 703, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, social platform software, etc.

[0184] The terminal devices 701 , 702 , and 703 may be various electronic devices having a display screen and supporting web browsing, including but not limited to smart phones, tablet computers, laptop computers, and desktop computers.

[0185] The server 705 may be a server that provides various services, such as a backend management server that provides support for shopping websites browsed by users using the terminal devices 701, 702, and 703. The backend management server may analyze and process the received data such as product information query requests, and feed back the processing results (such as target push information and product information) to the terminal device.

[0186] It should be noted that the method for analyzing the rationality of a database index provided in the embodiment of the present invention is generally executed by the server 705 , and accordingly, the device for analyzing the rationality of a database index is generally disposed in the server 705 .

[0187] It should be understood that Figure 7 The number of terminal devices, networks and servers in the embodiment is only for illustration. Any number of terminal devices, networks and servers may be provided according to implementation requirements.

[0188] Reference below Figure 8 , which shows a schematic structural diagram of a computer device 800 of a terminal device suitable for implementing an embodiment of the present invention. Figure 8 The terminal device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present invention.

[0189] like Figure 8 As shown, the computer device 800 includes a central processing unit (CPU) 801, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 802 or a program loaded from a storage part 808 to a random access memory (RAM) 803. In the RAM 803, various programs and data required for the operation of the device 800 are also stored. The CPU 801, the ROM 802, and the RAM 803 are connected to each other via a bus 804. An input / output (I / O) interface 805 is also connected to the bus 804.

[0190] The following components are connected to the I / O interface 805: an input section 806 including a keyboard, a mouse, etc.; an output section 807 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage section 808 including a hard disk, etc.; and a communication section 809 including a network interface card such as a LAN card, a modem, etc. The communication section 809 performs communication processing via a network such as the Internet. A drive 810 is also connected to the I / O interface 805 as needed. A removable medium 811, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 810 as needed, so that a computer program read therefrom is installed into the storage section 808 as needed.

[0191] In particular, according to the embodiments disclosed in the present invention, the process described above with reference to the flowchart can be implemented as a computer software program. For example, the embodiments disclosed in the present invention include a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program contains program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from the network through the communication part Y09, and / or installed from the removable medium Y11. When the computer program is executed by the central processing unit (CPU) Y01, the above-mentioned functions defined in the device of the present invention are executed.

[0192] It should be noted that the computer-readable medium shown in the present invention may be a computer-readable signal medium or a computer-readable storage medium or any combination of the above two. The computer-readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor device, device or component, or any combination of the above. More specific examples of computer-readable storage media may include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present invention, a computer-readable storage medium may be any tangible medium containing or storing a program that can be used by or in combination with an instruction execution device, device or component. In the present invention, a computer-readable signal medium may include a data signal propagated in a baseband or as part of a carrier wave, which carries a computer-readable program code. This propagated data signal may take a variety of forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. The computer-readable signal medium may also be any computer-readable medium other than a computer-readable storage medium, which may send, propagate or transmit a program for use by or in conjunction with an instruction execution device, apparatus or device. The program code contained on the computer-readable medium may be transmitted using any appropriate medium, including but not limited to: wireless, wire, optical cable, RF, etc., or any suitable combination of the above.

[0193] The flow charts and block diagrams in the accompanying drawings illustrate the possible architecture, functions and operations of the devices, methods and computer program products according to various embodiments of the present invention. In this regard, each box in the flow chart or block diagram can represent a module, a program segment, or a part of a code, and the above-mentioned module, program segment, or a part of the code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flow chart, and the combination of boxes in the block diagram or flow chart, can be implemented with a dedicated hardware-based device that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0194] The modules involved in the embodiments of the present invention may be implemented in software or hardware. The modules described may also be set in a processor. For example, they may be described as: a processor includes a sending module, an acquisition module, a determination module, and a first processing module. The names of these modules do not, in some cases, constitute a limitation on the unit itself. For example, the sending module may also be described as a "module for sending a picture acquisition request to the connected server."

[0195] As another aspect, the present invention further provides a computer-readable medium, which may be included in the device described in the above embodiment; or may exist independently without being assembled into the device. The above computer-readable medium carries one or more programs, and when the above one or more programs are executed by a device, the device includes:

[0196] Get multiple executable data manipulation statements;

[0197] determining an execution plan associated with each of the executable data manipulation statements;

[0198] Parsing the execution plan to determine the index applied to execute each of the executable data manipulation statements and the number of data rows scanned by the index;

[0199] The rationality of the index is analyzed based on the index and the number of data rows scanned by the index.

[0200] The technical solution of the embodiment of the present invention can proactively discover data manipulation statements without indexes or with poor index effects, thereby analyzing the rationality of the indexes, and further adjusting the index distribution, balancing retrieval and writing performance, and solving the technical problem that as the system demand grows, a large number of indexes are established on the table, but there is no effective reference data to evaluate the index benefits.

[0201] The above specific implementations do not constitute a limitation on the protection scope of the present invention. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions may occur depending on design requirements and other factors. Any modification, equivalent substitution and improvement made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A method for analyzing the rationality of a database index, characterized in that: include: Get multiple executable data manipulation statements; determining an execution plan associated with each of the executable data manipulation statements; Parsing the execution plan to determine the index applied to execute each of the executable data manipulation statements and the number of data rows scanned by the index; Analyze the rationality of the index based on the index and the number of data rows scanned by the index; Obtaining executable data manipulation statements includes: Use javaagent technology to obtain pre-executed data manipulation statements in the source database; Assembling the pre-executed data manipulation statement, context information of the pre-executed data manipulation statement, and parameter information to obtain an executable database manipulation statement, wherein the context information includes information of an execution environment of the data manipulation statement; The method further comprises: storing the executable database manipulation statement in a storage unit, and marking the executable database manipulation statement as not played back; Determine a playback data source identifier based on context information of the unplayed and executable data manipulation statements to determine a playback database; Send an execution plan acquisition instruction to the playback database to obtain an execution plan related to the non-playback, executable data manipulation statements, wherein before sending the execution plan acquisition instruction to the playback database, the non-playback data manipulation statements in the storage unit are fragmented according to the row key, and the scheduler obtains the data manipulation statements belonging to the respective fragments respectively.

2. The method according to claim 1, characterized in that The method further comprises: After determining the execution plan associated with the unreplayed, executable data manipulation statement, the unreplayed, executable data manipulation statement is marked as replayed.

3. The method according to claim 1, characterized in that Before sending the instruction to obtain the execution plan to the playback database, the method further includes: According to the type of the unreplayed and executable data manipulation statement and the version of the replay database, determining whether the replay database supports directly obtaining an execution plan related to the unreplayed and executable data manipulation statement; If not, the unplayed and executable data manipulation statement is rewritten into a target statement according to a preset rewriting rule, wherein the playback database supports directly obtaining an execution plan related to the target statement.

4. The method according to claim 1, characterized in that: The method further comprises: Parsing the execution plan to determine the data table to be applied to execute each of the executable data manipulation statements; Based on the index and the number of data rows scanned by the index, the rationality of the index is analyzed including: Determine the number of executions for each index; According to the data table, determine the indexes belonging to the same data table to calculate the total execution times of all indexes on the data table; For each index, the utilization rate of the index is determined based on the ratio of the number of executions of the index to the total number of executions of all indexes on the data table to which the index belongs; the performance value of the index is determined based on the ratio of the number of data rows scanned by the index to the total number of rows in the data table to which the index belongs; Analyze the rationality of indexes based on the utilization and performance value of each index.

5. A device for analyzing the rationality of a database index, characterized in that: include: An acquisition module, used for acquiring a plurality of executable data manipulation statements; An execution plan determination module, used to determine an execution plan associated with each of the executable data manipulation statements; An index determination module, used to parse the execution plan, determine the index applied to execute each executable data manipulation statement and the number of data rows scanned by the index; An analysis module, used for analyzing the rationality of an index based on the index and the number of data rows scanned by the index; The acquisition module is also used for: Use javaagent technology to obtain pre-executed data manipulation statements in the source database; Assembling the pre-executed data manipulation statement, context information of the pre-executed data manipulation statement, and parameter information to obtain an executable database manipulation statement, wherein the context information includes information of an execution environment of the data manipulation statement; The device also includes: A storage unit, used for storing the executable database manipulation statement; A marking module, used for marking the executable database manipulation statement as not played back when storing the executable database manipulation statement in the storage unit; The execution plan determination module is also used to: determine the playback data source identifier based on the context information of the unplayed and executable data manipulation statements to determine the playback database; send an execution plan acquisition instruction to the playback database to obtain the execution plan related to the unplayed and executable data manipulation statements, wherein before sending the execution plan acquisition instruction to the playback database, the unplayed data manipulation statements in the storage unit are fragmented according to the row key, and the scheduler respectively obtains the data manipulation statements belonging to each fragment.

6. The device according to claim 5, characterized in that The marking module is used to mark the unreplayed and executable database manipulation statement as replayed after determining the execution plan related to the unreplayed and executable database manipulation statement.

7. The device according to claim 5, characterized in that The execution plan determination module is also used to According to the type of the unreplayed and executable data manipulation statement and the version of the replay database, determining whether the replay database supports directly obtaining an execution plan related to the unreplayed and executable data manipulation statement; If not, the unplayed and executable data manipulation statement is rewritten into a target statement according to a preset rewriting rule, wherein the playback database supports directly obtaining an execution plan related to the target statement.

8. The device according to claim 5, characterized in that The index determination module is further used to: parse the execution plan to determine the data table applied to execute each executable data manipulation statement; The analysis module is also used for: Determine the number of executions for each index; According to the data table, determine the indexes belonging to the same data table to calculate the total execution times of all indexes on the data table; For each index, the utilization rate of the index is determined based on the ratio of the number of executions of the index to the total number of executions of all indexes on the data table to which the index belongs. Determine the performance value of the index based on the ratio of the number of data rows scanned by the index to the total number of rows in the data table to which the index belongs; Analyze the rationality of indexes based on the utilization and performance value of each index.

9. An electronic device, characterized in that: include: one or more processors; a storage device for storing one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the method according to any one of claims 1 to 4.

10. A computer readable medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method according to any one of claims 1 to 4 is implemented.

Images