A statement analysis method and related equipment

Abstract

This application discloses a statement analysis method and related equipment. The method includes: acquiring a structured query statement to be analyzed; performing syntax parsing on the structured query statement to obtain a syntax parsing result; obtaining metadata of a target data table from a first storage area corresponding to the test environment; obtaining the table data volume of the target data table and the column discrimination of the target column in the target data table from a second storage area corresponding to the production environment; determining the execution plan of the structured query statement based on the production database; and determining the quality analysis result of the structured query statement based on the syntax parsing result, the execution plan, the metadata of the target data table, the table data volume of the target data table, and the column discrimination of the target column. This application utilizes a combination of test environment data and production environment data to analyze structured query statements, effectively improving the accuracy of structured query statement analysis without affecting the production environment.

Description

Technical Field

[0001] This application relates to the field of computer technology, and in particular to a statement analysis method and related equipment. Background Technology

[0002] Structured Query Language (SQL) is a special-purpose programming language primarily used for accessing, querying, updating, and managing relational database systems. During application development, developers need to write structured query statements (SQL statements) that meet business requirements and data structures. SQL and database data structures are located at the lowest level of the application and are extremely important. Poor SQL statement performance will inevitably impact the overall performance of the application, and may even lead to application errors or crashes. Therefore, it is necessary to perform syntax and performance checks on SQL statements.

[0003] In related technologies, the traditional SQL tuning method, which relies on the knowledge and experience of developers and repeated modifications, not only has a high learning and debugging cost, but also easily introduces human factors, leading to uncontrollable final results. On the other hand, the method of automatic program analysis not only consumes a lot of system resources and affects the normal operation of other programs in the production environment, but also has low accuracy. Summary of the Invention

[0004] To address the problems of high cost, high resource consumption, and poor accuracy in existing structured query analysis technologies, this application provides a query analysis method and related equipment:

[0005] According to a first aspect of this application, a statement analysis method is provided, comprising:

[0006] Obtain the structured query statement to be analyzed;

[0007] The structured query statement is parsed to obtain the parsing result;

[0008] Obtain the metadata of the target data table from the first storage area corresponding to the test environment; the target data table is determined based on the table name in the structured query statement.

[0009] The table data volume of the target data table and the column discrimination of the target column in the target data table are obtained from the second storage area corresponding to the production environment. The second storage area stores the table data volume of each data table and the column discrimination of each column in the production database corresponding to the production environment. The table data volume of each data table and the column discrimination of each column are updated according to the first preset period. The column discrimination of each column represents the proportion of non-repeating fields in each column.

[0010] The execution plan for structured query statements is determined based on the production database;

[0011] Based on the syntax parsing results, execution plan, metadata of the target data table, table data volume of the target data table, and column discrimination of the target columns, determine the quality analysis results of the structured query statement.

[0012] According to a second aspect of this application, a statement analysis apparatus is provided, comprising:

[0013] The first acquisition module is used to acquire the structured query statement to be analyzed;

[0014] The parsing module is used to perform syntax parsing on structured query statements and obtain the syntax parsing results;

[0015] The second acquisition module is used to acquire the metadata of the target data table from the first storage area corresponding to the test environment; the target data table is determined according to the table name in the structured query statement.

[0016] The third acquisition module is used to acquire the table data volume of the target data table and the column discrimination of the target column in the target data table from the second storage area corresponding to the production environment. The second storage area stores the table data volume of each data table and the column discrimination of each column in the production database corresponding to the production environment. The table data volume of each data table and the column discrimination of each column are updated according to the first preset period. The column discrimination of each column represents the proportion of non-repeating fields in each column.

[0017] The first determination module is used to determine the execution plan of structured query statements based on the production database;

[0018] The second determination module is used to determine the quality analysis results of the structured query statement based on the syntax parsing results, execution plan, metadata of the target data table, table data volume of the target data table, and column discrimination of the target columns.

[0019] According to a third aspect of this application, an electronic device is provided, comprising a processor and a memory, wherein the memory stores at least one instruction or at least one program, and the at least one instruction or at least one program is loaded and executed by the processor to implement the statement analysis method described above.

[0020] According to a fourth aspect of this application, a computer storage medium is provided, which stores at least one instruction or at least one program, wherein the at least one instruction or at least one program is loaded and executed by a processor to implement the above-described statement analysis method.

[0021] According to a fifth aspect of this application, a computer program product is provided, comprising at least one instruction or at least one program segment, wherein the at least one instruction or at least one program segment is loaded and executed by a processor to implement the statement analysis method described above.

[0022] The statement analysis method and related equipment provided in this application have the following technical effects:

[0023] This application obtains the metadata of the target data table from the first storage area corresponding to the test environment, which can reduce the impact on the production environment. Obtaining the table data volume and column discrimination of the target columns in the target data table from the second storage area corresponding to the production environment can ensure the accuracy of the final quality analysis results. Furthermore, the second storage area regularly counts and updates the table data volume and column discrimination of each data table in the production database, so that it can be read from the second storage area during structured query analysis, which can improve data reading efficiency and further reduce the impact on the production environment. Thus, this application uses a combination of test environment data and production environment data to analyze structured query statements, which can effectively improve the accuracy of structured query statement analysis without affecting the production environment. Attached Figure Description

[0024] To more clearly illustrate the technical solutions and advantages in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0025] Figure 1 This is a schematic diagram of an application environment provided in an embodiment of this application;

[0026] Figure 2 This is a flowchart illustrating a statement analysis method provided in an embodiment of this application;

[0027] Figure 3 This is a flowchart illustrating a statement analysis method provided in an embodiment of this application;

[0028] Figure 4 This is a schematic diagram of a process for obtaining metadata of a target data table from a first storage area corresponding to a test environment, provided in an embodiment of this application.

[0029] Figure 5 This is a flowchart illustrating a statement analysis method provided in an embodiment of this application;

[0030] Figure 6This is a flowchart illustrating a process for obtaining the table data volume of a target data table and the column discrimination of a target column in a target data table from a second storage area corresponding to the production environment, as provided in an embodiment of this application.

[0031] Figure 7 This is a flowchart illustrating a method for determining the quality analysis results of a structured query statement, as provided in an embodiment of this application.

[0032] Figure 8 This is an interactive schematic diagram of statement analysis provided in an embodiment of this application;

[0033] Figure 9 This is a block diagram of a statement analysis device provided in an embodiment of this application;

[0034] Figure 10 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

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

[0036] It should be noted that the terms "comprising" and "having" and any variations thereof in the specification, claims and accompanying drawings of this application are intended to cover non-exclusive inclusion. For example, a process, method, system, product or server that includes a series of steps or units is not necessarily limited to those steps or units that are explicitly listed, but may include other steps or units that are not explicitly listed or that are inherent to such processes, methods, products or devices.

[0037] Before providing a further detailed description of the embodiments of this application, the nouns and terms involved in the embodiments of this application will be explained, and the nouns and terms involved in the embodiments of this application shall be interpreted as follows.

[0038] Production environment: refers to the environment in which services are officially provided to users.

[0039] Test environment: refers to the environment simulated by testers using tools and relevant data.

[0040] Execution plan: The specific steps a database takes to execute a structured query statement.

[0041] Please see Figure 1 , Figure 1This is a schematic diagram of an application environment provided in an embodiment of this application. This application environment may include a client 01, a server 02, a first storage area 03 corresponding to a test environment, and a second storage area 04 corresponding to a production environment. In practical applications, the client 01 can initiate a data query request to the server 02. After receiving the data query request, the server 02 can initiate a query to the second storage area 04 corresponding to the production environment. After completing the query, the second storage area 04 corresponding to the production environment returns the result to the server 02. After receiving the result, the server 02 returns a request response to the client 01. After receiving the request response, the client 01 renders the data on the page.

[0042] During the aforementioned interaction, when server 02 initiates a query to the second storage area 04 corresponding to the production environment, it uses Structured Query Language (SQL). SQL is a special-purpose programming language primarily used for accessing, querying, updating, and managing relational database systems. Server 02 performs database operations through SQL statements. However, if server 02 uses incorrect or poorly performing SQL statements, it will affect the response speed to client 01. Therefore, to ensure the smoothness of the above interaction in actual applications, the SQL statements need to be syntax and performance checked before actual application.

[0043] In some possible embodiments, this application combines the first storage area 03 corresponding to the test environment and the second storage area 04 corresponding to the production environment to perform syntax and performance analysis on the SQL statement and determine the quality analysis result of the SQL statement.

[0044] The aforementioned server 02 can be a standalone physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server providing basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, CDN (Content Delivery Network), and big data and artificial intelligence platforms. The server may include network communication units, processors, and memory, etc. The server can provide backend services to the corresponding clients.

[0045] The aforementioned client 01 can be a physical device such as a smartphone, computer (e.g., desktop computer, tablet, laptop), augmented reality (AR) / virtual reality (VR) device, digital assistant, smart voice interaction device (e.g., smart speaker), smart wearable device, smart home appliance, in-vehicle terminal, etc., or it can be software running on the physical device, such as a computer program. The operating system corresponding to the client can be Android, iOS (a mobile operating system developed by Apple), Linux (an operating system), Microsoft Windows, etc.

[0046] It should be noted that, Figure 1 The application scenario is merely one example.

[0047] The following describes a specific embodiment of a statement analysis method according to this application. Figure 2 This is a flowchart illustrating a statement analysis method provided in an embodiment of this application. This application provides the operational steps of the method described in the embodiment or flowchart, but based on conventional or non-inventive methods, it may include more or fewer operational steps. The order of steps listed in the embodiment is merely one possible execution order among many and does not represent the only execution order. In actual systems or products, the method can be executed sequentially or in parallel (e.g., in a parallel processor or multi-threaded processing environment) as shown in the embodiment or drawings. Specifically, as... Figure 2 As shown, the method may include:

[0048] S201: Obtain the structured query statement to be analyzed.

[0049] In this embodiment of the application, after the developer completes the writing of the structured query statement (hereinafter referred to as SQL statement), the written SQL statement is used as the structured query statement to be analyzed, and its quality is analyzed through the following steps S203 to S211 to obtain the quality analysis result of the SQL statement.

[0050] S203: Perform syntax parsing on the structured query statement to obtain the syntax parsing result.

[0051] In this embodiment, the syntax of the SQL statement to be analyzed is first parsed to obtain the syntax parsing result. The syntax parsing result is determined according to the actual syntax parsing method used; for example, the syntax parsing result may include whether it meets preset syntax rules.

[0052] In some possible embodiments, the above-described parsing of the structured query statement to obtain the parsing result may include the following steps:

[0053] S2031: Obtain the preset syntax rule library.

[0054] The preset syntax rule base includes rules for the use of query fields in structured query statements. For example, rule 1: SQL statements cannot use prefix matching queries, as it will cause indexes to become ineffective; rule 2: SQL statements cannot use select*, and specific column names must be specified.

[0055] S2033: Based on a preset syntax rule base, the syntax of the structured query statement is parsed to obtain the syntax parsing result.

[0056] Specifically, it can determine whether an SQL statement violates the usage rules of the corresponding query fields, count the violations, determine the corresponding syntax score based on the count, and use the syntax score as the syntax parsing result; or it can directly use the determination result of whether the corresponding query field usage rules are violated as the syntax parsing result.

[0057] For example, a certain SQL query statement is as follows:

[0058] select*from user_db.t_user_info where Fname like'%tom'and Fage=18;

[0059] Based on the above rules, the SQL query statement is parsed. First, the SQL query statement uses prefix matching like '%tom', which may cause the index to become invalid, so it is determined to violate rule 1; second, the SQL query statement uses select * without specifying specific field names, so it is determined to violate rule 2.

[0060] The preset syntax rule base in the above embodiments may mainly include basic writing specifications for SQL statements, that is, the syntax parsing result indicates whether the SQL statement meets the basic writing specifications. In subsequent steps, the SQL statement to be analyzed will also be subjected to syntax analysis from other aspects.

[0061] S205: Obtain the metadata of the target data table from the first storage area corresponding to the test environment; the target data table is determined based on the table name in the structured query statement.

[0062] In the example above, user_db.t_user_info is the table name, and the data table corresponding to this table name is the target data table.

[0063] In related technologies, to avoid the problem of excessive system resource consumption and disruption to the normal operation of the production environment caused by directly analyzing SQL statements in the production environment, the analysis of SQL statements is often performed in a test environment. However, the data in the test environment differs significantly from that in the production environment, and different data has a significant impact on the SQL analysis results. That is, analyzing the same SQL statement under different data may yield different results, thus the accuracy of SQL analysis results obtained in the test environment is relatively low.

[0064] In this embodiment of the application, the SQL statement is analyzed by combining test environment data and production environment data, which can reduce the impact on the production environment and improve the accuracy of SQL statement analysis.

[0065] First, in this embodiment, the metadata of the target data table is obtained from the first storage area corresponding to the test environment. The metadata can also refer to table structure information, meaning it may include column names, data types, field types, lengths, etc. The first storage area is used to store test environment data.

[0066] In some possible embodiments, the first storage area stores metadata for each data table in the production database; wherein, the production database is the database accessed by the server in the production environment to implement various applications.

[0067] Correspondingly, such as Figure 3 As shown, the statement analysis method in this application embodiment may further include the following steps:

[0068] S301: According to the second preset cycle, retrieve the metadata of each data table from the production database.

[0069] The second preset period can be determined based on the data changes in the actual business scenario. For example, the second preset period can be 1 day, 3 days, 5 days or 7 days.

[0070] Specifically, a scheduled task can be configured to retrieve metadata for each table from the production database. To further reduce the impact on the production environment, this scheduled task can be configured to retrieve metadata for each table from the production database during periods of low resource utilization in the production environment.

[0071] S303: Store the metadata of each data table in the first storage area.

[0072] Specifically, the server synchronizes the metadata of the data tables in the production database to the first storage area according to the second preset period for use during testing. The server can synchronize the metadata of all data tables in the production database within the second preset period, thus achieving data synchronization between the test and production environments in a single operation. Alternatively, the server can synchronize all data tables in batches, that is, only synchronizing the metadata of one batch of data tables within each second preset period. Within a round, different data tables' metadata are synchronized in different second preset periods. Upon completion of one round, the metadata of all data tables in the production database can be synchronized. This saves synchronization time each time and reduces the impact on the production environment.

[0073] In some possible embodiments, the above-described acquisition of the target data table's metadata from the first storage area corresponding to the test environment may include, for example: Figure 4 The following steps are shown:

[0074] S401: Match the table names in the structured query statement with the table names of each data table stored in the first storage area to obtain the data table that matches the table names in the structured query statement.

[0075] In this step, the corresponding data tables are obtained by searching for table names in the first storage area. Specifically, based on the table names in the SQL statement to be analyzed, a search is performed in the first storage area, that is, the table names in the SQL statement to be analyzed are matched with the table names of each data table in the first storage area to find the data table with the same table name as the table in the SQL statement.

[0076] For example, in the example above, you can use user_db.t_user_info to perform a matching search to find the corresponding data table.

[0077] S403: Select the data table that matches the table name in the structured query statement as the target data table and obtain the metadata of the target data table.

[0078] In this step, the data table in the first storage area that has the same table name as the table in the SQL statement is taken as the target data table. At the same time, the metadata of the target data table that has been synchronized from the production database in the first storage area is obtained.

[0079] In the above embodiments, considering that the metadata of the corresponding data table is needed when analyzing SQL statements, and given that the metadata changes little, the results of obtaining metadata from the test environment or from the production environment are roughly the same. Therefore, in this embodiment, the metadata of the target data table is obtained from the first storage area corresponding to the test environment, which can reduce the impact on the production environment; and the first storage area updates the metadata synchronously according to the second preset period, which can maintain the accuracy of the data.

[0080] S207: Obtain the table data volume of the target data table and the column discrimination of the target column in the target data table from the second storage area corresponding to the production environment; the second storage area stores the table data volume of each data table and the column discrimination of each column in the production database corresponding to the production environment, and the table data volume of each data table and the column discrimination of each column are updated according to the first preset period; the column discrimination of each column represents the proportion of non-repeating fields in each column.

[0081] In this embodiment of the application, the server can count the amount of table data in each data table in the production database and the column distinguishability of each column in each data table according to a first preset period, and then store them in a second storage area for quick retrieval.

[0082] In some possible embodiments, such as Figure 5 As shown, the statement analysis method in this application embodiment may further include the following steps:

[0083] S501: According to the first preset cycle, the amount of table data in each table of all data tables in the production database is counted.

[0084] The first preset period can be determined based on data changes in the actual business scenario. For example, the first preset period can be 1 hour, 6 hours, 12 hours, or 24 hours. The table data volume can refer to the number of rows in the table or the table capacity. The following explanation uses the number of rows as an example.

[0085] Specifically, a scheduled task can be configured to initiate updates to the table data volume and column discrimination in the second storage area. To further reduce the impact on the production environment, the scheduled task can be configured to initiate updates to the table data volume and column discrimination in the second storage area during periods of low resource utilization in the production environment.

[0086] S503: Determine whether the amount of data in each data table is greater than or equal to a first preset value. If the amount of data in the table is greater than or equal to the first preset value, proceed to step S505; otherwise, stop the statistics.

[0087] In practical applications, a statistical threshold, or first preset value, can be set. Data tables with values ​​below this first preset value show little change in data volume or column discrimination, and therefore do not require further statistical analysis. The first preset value can be determined based on the data volume of the actual business scenario; for reference, a first preset value could be 50,000 rows.

[0088] S505: Determine the data increment corresponding to the current period.

[0089] In this step, the amount of table data in the current period of each data table is compared with the amount of table data counted in the previous period, and the difference between the two is calculated. This difference is the data increment corresponding to the current period.

[0090] S507: Determine whether the data increment corresponding to the current period is greater than or equal to the second preset value. If the data increment corresponding to the current period is greater than or equal to the second preset value, then execute steps S509 to S511; otherwise, stop the statistics.

[0091] The second preset value is determined based on the data volume of the actual business scenario. For reference, when the data table has hundreds of thousands of rows, the second preset value can be 50,000 rows; when the data table has millions of rows, the second preset value can be 500,000 rows.

[0092] S509: Determine the data increment corresponding to the current period and the column discrimination of each column in the data table.

[0093] In this step, column discrimination refers to the proportion of fields in the column that are not repeated. The formula for calculating discrimination is: count(distinct col) / count(*); where distinct() is used to remove duplicate fields and count() is used to count them.

[0094] S511: Store the table data volume and corresponding column discrimination of the data table in the second storage area.

[0095] In this step, the server stores the column discrimination and table data volume of data tables with a data volume greater than or equal to a first preset value and a data increment greater than or equal to a second preset value in the second storage area. For data tables with a small data volume and a small data increment, no statistics or updates are performed. In this way, partial data synchronization updates can be achieved, which can save server resources and reduce the impact on other applications in the production environment.

[0096] In some possible embodiments, the amount of table data in the target data table and the column discrimination of the target column in the target data table obtained from the second storage area corresponding to the production environment may include, for example: Figure 6 The following steps are shown:

[0097] S601: Match the table names in the structured query statement with the table names of each data table stored in the second storage area to obtain the data table that matches the table names in the structured query statement.

[0098] In this step, the corresponding data tables are obtained by searching for table names in the second storage area. Specifically, based on the table names in the SQL statement to be analyzed, a search is performed in the second storage area, that is, the table names in the SQL statement to be analyzed are matched with the table names of each data table in the second storage area to find the data table that matches the table name in the SQL statement.

[0099] Here, the second storage area serves as a temporary cache area for storing table data volume and column differentiation. The server can directly and quickly find and read data tables that match the table names in the structured query statement from the second storage area. Compared to searching and reading from the production database, this reduces database access operations and improves data query efficiency.

[0100] S603: Use the data table that matches the table name in the structured query statement as the target data table, and obtain the column discrimination of each column in the target data table.

[0101] In this step, the data table in the second storage area that has the same table name as the table in the SQL statement is used as the target data table. At the same time, the column discrimination of each column in the target data table that is pre-stored in the second storage area is obtained.

[0102] S605: Select columns in the target data table whose column discrimination is greater than or equal to the preset discrimination as target columns.

[0103] In practical applications, the higher the column's granularity, the faster the query speed when used as an index. Therefore, in this step, columns with a granularity greater than or equal to a preset granularity are selected as target columns, which can serve as candidate indexes. The preset granularity can be 0.5, in which case there can be multiple target columns; alternatively, the preset granularity can be determined by the actual ranking of the column granularities. For example, selecting the column with the highest granularity as the preset granularity results in only one target column, which is the optimal index.

[0104] In the above embodiments, considering that the size of the data and the column discrimination play a very important role in SQL statement analysis, the table data volume of the target data table and the column discrimination of the target column in the target data table are obtained from the second storage area corresponding to the production environment to ensure the accuracy of the final quality analysis results. In addition, the second storage area regularly counts and updates the table data volume and column discrimination of each data table in the production database. In this way, when analyzing SQL statements, the table data volume of the target data table and the column discrimination of the target column in the target data table can be directly obtained from the second storage area, saving the time and computing resources of obtaining relevant data from the production database and then recalculating, and reducing the impact on the production environment.

[0105] S209: Determine the execution plan for structured query statements based on the production database.

[0106] In this embodiment of the application, when determining the execution plan of the SQL statement to be analyzed, the production database is connected, and the execution plan is obtained based on the data in the production database, which can improve the accuracy of the analysis.

[0107] In some possible embodiments, the above-described execution plan for determining structured query statements based on a production database may include:

[0108] Based on the production database, the execution plan of the structured query statement is obtained through preset instructions; the execution plan includes at least one of the following: table name, access type, actual index field value, and number of rows scanned.

[0109] Specifically, the default command can utilize the `explain` command provided by MySQL to obtain the execution plan of the SQL statement to be analyzed. The execution plan includes the following information:

[0110] The query sequence number "id" indicates the order in which the select clauses are executed;

[0111] Find the table name "table"; access type "type", which is a relatively important field in the execution plan. The result values ​​from best to worst are system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all;

[0112] The actual index field "key" will return the actual column name used. If it is NULL, then no index was used.

[0113] The number of rows scanned indicates the estimated number of rows that need to be read to find the desired record.

[0114] S211: Determine the quality analysis results of the structured query statement based on the syntax parsing results, execution plan, metadata of the target data table, table data volume of the target data table, and column discrimination of the target column.

[0115] In this embodiment, the quality analysis results of the structured query statement can be obtained by manually performing a comprehensive analysis based on the above-mentioned syntax parsing results, execution plan, metadata of the target data table, table data volume of the target data table, and column discrimination of the target column. Alternatively, judgment criteria can be pre-defined, and an automated method can be used to detect whether any one or more of the following combinations of syntax parsing results, execution plan, metadata of the target data table, table data volume of the target data table, and column discrimination of the target column meet the judgment criteria, thereby obtaining the quality analysis results of the structured query statement. The quality analysis results can be in the form of a scoring system or a rating system; this application does not limit the form of the quality analysis results.

[0116] In some possible embodiments, the metadata of the target data table includes the field type corresponding to each column in the target data table;

[0117] Accordingly, the quality analysis results of determining the structured query statement based on the syntax parsing results, execution plan, metadata of the target data table, table data volume of the target data table, and column discrimination of the target columns can include, for example: Figure 7 The following steps are shown:

[0118] S701: Determine the first syntax quality score based on the syntax parsing results and execution plan.

[0119] Considering that in practical applications, SQL statements may use syntax that could cause index failure, but when executing the SQL statement, the syntax does not cause index failure, in this case, the syntax quality of the SQL statement cannot be determined solely by the syntax parsing result. Therefore, in this step, the first syntax quality score is determined from both the syntax parsing result and the execution plan.

[0120] Specifically, based on the syntax parsing results, the relevant syntax violations that caused the index to fail are determined in the SQL statement. For example, in the previous example, the SQL query used prefix matching like '%tom', violating rule 1. Here, assuming that the result value of the actual index field in the execution plan obtained by the explain command is primary, it means that the primary key was used as the index, i.e., the index was hit. This indicates that the like '%tom' prefix matching condition did not cause the index to fail, and therefore, although rule 1 was violated, it did not affect the final first syntax quality score. If the result value of the actual index field in the execution plan is NULL, it means that the index was not hit, indicating that the like '%tom' prefix matching condition caused the index to fail, thus affecting the final first syntax quality score.

[0121] In determining the first grammar quality score, a deduction system can be implemented. For example, the first grammar quality score can be preset to 25 points. Combining the grammar parsing results and the execution plan, and under the verification of the execution plan, points can be deducted based on the actual number of grammar rule violations, up to a maximum of 25 points. If the grammar parsing results indicate a rule violation, but the execution plan verification shows no actual impact, then that point deduction is waived.

[0122] S703: Based on the field type corresponding to each column in the target data table, perform type detection on the field values ​​of the query fields in the structured query statement to determine the second syntax quality score.

[0123] Similar to the previous step, a deduction system is implemented in determining the second grammar quality score, with the second grammar quality score pre-set at 25 points.

[0124] The above SQL query, `select * from user_db.t_user_info where Fname like '%tom' and Fage=18`, serves as an example. Here, the query fields include `Fname`, and the corresponding field value includes `'%tom'`. The field value type is varchar. If the actual data table's `Fname` field value is not of varchar type, the data query will fail.

[0125] Therefore, in order to avoid such invalid queries, this step involves performing type checks on the field values ​​of the query fields in the SQL statement based on the metadata of the target data table, specifically according to the field types corresponding to each column defined in the metadata. If inconsistencies are found, all or part of the second syntax quality score can be deducted.

[0126] S705: Determine the index quality score based on the execution plan and the column distinctiveness of the target column.

[0127] Similar to the previous step, a deduction system is implemented in the process of determining the index quality score, with the index quality score set at 25 points in advance.

[0128] As mentioned earlier, the higher the column distinctiveness of the target column, the higher the index efficiency. Therefore, in this step, the index quality score is determined by judging whether the index fields actually used in the execution plan include the target column. For example, when there are multiple target columns, the target column with the highest distinctiveness can be determined based on the column distinctiveness of each target column. Then, it is judged whether the index fields actually used in the execution plan include the target column with the highest distinctiveness to determine whether the optimal index is used. If so, the index quality score is 25 points; otherwise, the corresponding points are deducted, and at the same time, the optimal index usage suggestion is generated.

[0129] S707: Determine the query efficiency score based on the execution plan and the amount of table data in the target data table.

[0130] Similar to the previous step, a deduction system is implemented in the process of determining the query efficiency score, with the query efficiency score set in advance at 25 points.

[0131] In this step, the query efficiency score is determined based on the number of rows scanned in the execution plan and the amount of data in the target table. Specifically, it first checks whether the number of rows scanned in the execution plan is greater than or equal to a preset number of rows. This preset number of rows is determined based on the amount of data in the actual scenario; for example, the preset number of rows could be 10,000 rows. If the number of rows scanned in the execution plan is greater than or equal to 10,000 rows, it then checks whether the ratio of the number of rows scanned to the amount of data in the table is greater than or equal to a preset ratio, which could be 1 / 100. If the ratio of the number of rows scanned to the amount of data in the table is greater than or equal to the preset ratio, it indicates that the SQL statement scans too many rows, resulting in very low query efficiency. In this case, a deduction is made from the query efficiency score.

[0132] S709: Determine the quality analysis results based on the first syntax quality score, the second syntax quality score, the index quality score, and the query efficiency score.

[0133] In this step, the quality analysis results are calculated by combining the first syntax quality score, the second syntax quality score, the index quality score, and the query efficiency score.

[0134] Specifically, the first syntax quality score, the second syntax quality score, the index quality score, and the query efficiency score can be added together, and the result can be used as the quality analysis result. Alternatively, different weights can be assigned to each item, and a weighted sum can be taken as the quality analysis result. This approach can adapt to different emphases on syntax and performance requirements in different scenarios.

[0135] The following is combined Figure 8 The steps S201 to S211 and some possible embodiments of the statement analysis method of this application are described in general. First, the server executes step S801 to obtain the SQL statement to be analyzed; the SQL statement to be analyzed can be generated based on the front-end test request or manually entered by the developer; second, the server executes step S803 to perform syntax parsing on the SQL statement based on a preset syntax rule base to obtain the syntax parsing result; third, the server executes step S805 to obtain the metadata of the target data table from the first storage area corresponding to the test environment; here, the server updates the metadata in the first storage area according to the production database of the production environment in step S804, according to the second preset period; at the same time, the server also executes step... S807, obtain the table data volume of the target data table and the column discrimination of the target column in the target data table from the second storage area corresponding to the production environment; here, the server updates the table data volume and column discrimination in the second storage area according to the production database of the production environment based on the first preset period in step S806; next, the server executes step S809 to connect to the production database to determine the execution plan of the SQL statement; finally, the server executes step S811 to determine the quality analysis result of the SQL statement based on the above syntax parsing results, execution plan, metadata of the target data table, table data volume of the target data table and column discrimination of the target column.

[0136] In summary, in this embodiment, obtaining the metadata of the target data table from the first storage area corresponding to the test environment can reduce the impact on the production environment; obtaining the table data volume and column discrimination of the target column in the target data table from the second storage area corresponding to the production environment can ensure the accuracy of the final quality analysis results. Furthermore, the second storage area periodically calculates and updates the table data volume and column discrimination of each data table in the production database, so that it can be read from the second storage area during SQL statement analysis, improving data reading efficiency and further reducing the impact on the production environment. Thus, this application utilizes a combination of test environment data and production environment data to analyze SQL statements, effectively improving the accuracy of SQL statement analysis without affecting the production environment.

[0137] This application also provides a statement analysis device, such as... Figure 9 As shown, the statement analysis device 90 includes:

[0138] The first acquisition module 901 is used to acquire the structured query statement to be analyzed;

[0139] Parsing module 902 is used to perform syntax parsing on structured query statements and obtain the syntax parsing results;

[0140] The second acquisition module 903 is used to acquire the metadata of the target data table from the first storage area corresponding to the test environment; the target data table is determined according to the table name in the structured query statement;

[0141] The third acquisition module 904 is used to acquire the table data volume of the target data table and the column discrimination of the target column in the target data table from the second storage area corresponding to the production environment; the second storage area stores the table data volume of each data table and the column discrimination of each column in the production database corresponding to the production environment, and the table data volume of each data table and the column discrimination of each column are updated according to a first preset period; the column discrimination of each column represents the proportion of non-repeating fields in each column;

[0142] The first determining module 905 is used to determine the execution plan of the structured query statement based on the production database;

[0143] The second determining module 906 is used to determine the quality analysis results of the structured query statement based on the syntax parsing results, execution plan, metadata of the target data table, table data volume of the target data table, and column discrimination of the target column.

[0144] In some possible embodiments, the parsing module 902 is further configured to obtain a preset syntax rule base; based on the preset syntax rule base, parse the syntax of the structured query statement to obtain the syntax parsing result; wherein, the preset syntax rule base includes the usage rules of the query fields in the structured query statement.

[0145] In some possible embodiments, the first storage area stores metadata for each data table in the production database;

[0146] The second acquisition module 903 is further configured to match the table name in the structured query statement with the table name of each data table stored in the first storage area to obtain the data table that matches the table name in the structured query statement; take the data table that matches the table name in the structured query statement as the target data table, and obtain the metadata of the target data table.

[0147] In some possible embodiments, the apparatus further includes: a first update module, configured to retrieve metadata of each data table from the production database according to a second preset period; and store the metadata of each data table in a first storage area.

[0148] In some possible embodiments, the apparatus further includes: a second update module, configured to: count the table data volume of each data table in all data tables of the production database according to a first preset period; for each data table: if the table data volume of the data table is greater than or equal to a first preset value, determine the data increment corresponding to the current period; if the data increment is greater than or equal to a second preset value, determine the column discrimination of each column in the data table; and store the table data volume and the corresponding column discrimination of the data table in a second storage area.

[0149] In some possible embodiments, the first determining module 905 is further configured to obtain the execution plan of the structured query statement based on the production database through preset instructions; wherein the execution plan includes at least one of the lookup table name, access type, actual index field value and number of rows scanned.

[0150] In some possible embodiments, the third acquisition module 904 is further configured to match the table name in the structured query statement with the table name of each data table stored in the second storage area to obtain the data table that matches the table name in the structured query statement; take the data table that matches the table name in the structured query statement as the target data table, and obtain the column discrimination of each column in the target data table; take the column discrimination of the target data table that is greater than or equal to the preset discrimination as the target column.

[0151] In some possible embodiments, the metadata of the target data table includes the field type corresponding to each column in the target data table;

[0152] The second determining module 906 is also used to determine the first syntax quality score based on the syntax parsing results and the execution plan; to perform type detection on the field values ​​of the query fields in the structured query statement based on the field types corresponding to each column in the target data table, and to determine the second syntax quality score; to determine the index quality score based on the execution plan and the column discrimination of the target columns; to determine the query efficiency score based on the execution plan and the table data volume of the target data table; and to determine the quality analysis results based on the first syntax quality score, the second syntax quality score, the index quality score, and the query efficiency score.

[0153] It should be noted that the apparatus and method embodiments described in the device embodiments are based on the same inventive concept.

[0154] This application provides an electronic device including a processor and a memory. The memory stores at least one instruction or at least one program segment, which is loaded and executed by the processor to implement the statement analysis method provided in the above method embodiments.

[0155] Furthermore, Figure 10A schematic diagram of the hardware structure of an electronic device for implementing the statement analysis method provided in the embodiments of this application is shown. The electronic device may participate in or include the statement analysis apparatus provided in the embodiments of this application. Figure 10 As shown, the electronic device 100 may include one or more processors 1002 (shown as 1002a, 1002b, ..., 1002n in the figure) (processor 1002 may include, but is not limited to, a microprocessor MCU or a programmable logic device FPGA, etc.), a memory 1004 for storing data, and a transmission device 1006 for communication functions. In addition, it may also include: a display, an input / output interface (I / O interface), a universal serial bus (USB) port (which may be included as one of the ports of the I / O interface), a network interface, a power supply, and / or a camera. Those skilled in the art will understand that... Figure 10 The structure shown is for illustrative purposes only and does not limit the structure of the electronic device described above. For example, the electronic device 100 may also include... Figure 10 The more or fewer components shown, or having the same Figure 10 The different configurations shown.

[0156] It should be noted that the aforementioned one or more processors 1002 and / or other data processing circuits are generally referred to herein as "data processing circuits". These data processing circuits may be embodied, in whole or in part, in software, hardware, firmware, or any other combination thereof. Furthermore, the data processing circuits may be a single, independent processing module, or may be wholly or partially integrated into any other element within the electronic device 100 (or mobile device). As involved in the embodiments of this application, the data processing circuit serves as a processor control mechanism (e.g., selection of a variable resistor termination path connected to an interface).

[0157] The memory 1004 can be used to store software programs and modules of application software, such as the program instructions / data storage device corresponding to the statement analysis method described in the embodiments of this application. The processor 1002 executes various functional applications and data processing by running the software programs and modules stored in the memory 1004, thereby implementing the aforementioned statement analysis method. The memory 1004 may include high-speed random access memory, and may also include non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory 1004 may further include memory remotely located relative to the processor 1002, and these remote memories can be connected to the electronic device 100 via a network. Examples of such networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof.

[0158] The transmission device 1006 is used to receive or send data via a network. Specific examples of the network described above may include a wireless network provided by the communication provider of the electronic device 100. In one example, the transmission device 1006 includes a network interface controller (NIC), which can connect to other network devices via a base station to communicate with the Internet. In one embodiment, the transmission device 1006 may be a radio frequency (RF) module for wireless communication with the Internet.

[0159] The display can be, for example, a touchscreen liquid crystal display (LCD) that allows a user to interact with the user interface of the electronic device 100 (or mobile device).

[0160] Embodiments of this application also provide a computer-readable storage medium, which can be disposed in an electronic device to store at least one instruction or at least one program related to implementing a statement analysis method in the method embodiment. The at least one instruction or the at least one program is loaded and executed by the processor to implement the statement analysis method provided in the above method embodiment.

[0161] Optionally, in this embodiment, the storage medium may be located in at least one of the multiple network servers in a computer network. Optionally, in this embodiment, the storage medium may include, but is not limited to, various media capable of storing program code, such as USB flash drives, read-only memory (ROM), random access memory (RAM), portable hard drives, magnetic disks, or optical disks.

[0162] It should be noted that the order of the embodiments described above is merely for descriptive purposes and does not represent the superiority or inferiority of the embodiments. Furthermore, the above description focuses on specific embodiments of this application. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps described in the claims can be performed in a different order than that shown in the embodiments and still achieve the desired results. Additionally, the processes depicted in the drawings do not necessarily require a specific or sequential order to achieve the desired results. In some implementations, multitasking and parallel processing are also possible or may be advantageous.

[0163] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the device and electronic device embodiments are basically similar to the method embodiments, so the descriptions are relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0164] Those skilled in the art will understand that all or part of the steps of the above embodiments can be implemented by hardware or by a program instructing related hardware. The program can be stored in a computer-readable storage medium, such as a read-only memory, a disk, or an optical disk.

[0165] The above description is only a preferred embodiment of this application and is not intended to limit this application. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the protection scope of this application.

Claims

1. A statement analysis method, characterized in that, include: Obtain the structured query statement to be analyzed; The structured query statement is parsed to obtain the parsing result; Obtain the metadata of the target data table from the first storage area corresponding to the test environment; The target data table is determined based on the table name in the structured query statement; Obtain the table data volume of the target data table and the column discrimination of the target column in the target data table from the second storage area corresponding to the production environment; The second storage area stores the table data volume of each data table and the column discrimination of each column in the production database corresponding to the production environment. The table data volume of each data table and the column discrimination of each column are updated according to a first preset period. The column discrimination of each column represents the proportion of fields in each column that are not repeated; The execution plan for the structured query statement is determined based on the production database; The quality analysis results of the structured query statement are determined based on the syntax parsing results, the execution plan, the metadata of the target data table, the table data volume of the target data table, and the column discrimination of the target column.

2. The statement analysis method according to claim 1, characterized in that, The process of parsing the structured query statement to obtain the parsing result includes: Obtain the preset syntax rule library; Based on the preset syntax rule base, the syntax of the structured query statement is parsed to obtain the syntax parsing result; The preset syntax rule base includes rules for the use of query fields in the structured query statement.

3. The statement analysis method according to claim 1, characterized in that, The first storage area stores the metadata of each data table in the production database; The step of obtaining the metadata of the target data table from the first storage area corresponding to the test environment includes: The table names in the structured query statement are matched with the table names of each data table stored in the first storage area to obtain the data table that matches the table names in the structured query statement. The data table whose name matches the table name in the structured query statement is taken as the target data table, and the metadata of the target data table is obtained.

4. The statement analysis method according to claim 3, characterized in that, The method further includes: According to the second preset cycle, the metadata of each data table is obtained from the production database; The metadata of each data table is stored in the first storage area.

5. The statement analysis method according to claim 1, characterized in that, The method further includes: According to the first preset period, the amount of table data in each data table in all data tables of the production database is statistically analyzed; For each data table: if the amount of data in the data table is greater than or equal to a first preset value, determine the data increment corresponding to the current period; if the data increment is greater than or equal to a second preset value, determine the column discrimination of each column in the data table; store the amount of data in the data table and the corresponding column discrimination in the second storage area.

6. The statement analysis method according to claim 1, characterized in that, Determining the execution plan for the structured query statement based on the production database includes: Based on the production database, the execution plan of the structured query statement is obtained through preset instructions; The execution plan includes at least one of the following: table name, access type, actual index field value, and number of rows scanned.

7. The statement analysis method according to claim 1, characterized in that, The step of obtaining the table data volume of the target data table and the column discrimination of the target column in the target data table from the second storage area corresponding to the production environment includes: The table names in the structured query statement are matched with the table names of each data table stored in the second storage area to obtain the data table that matches the table names in the structured query statement. The data table that matches the table name in the structured query statement is taken as the target data table, and the column discrimination of each column in the target data table is obtained; The columns in the target data table whose column discrimination is greater than or equal to the preset discrimination are taken as the target columns.

8. The statement analysis method according to claim 1, characterized in that, The metadata of the target data table includes the field type corresponding to each column in the target data table; The process of determining the quality analysis results of the structured query statement based on the syntax parsing results, the execution plan, the metadata of the target data table, the table data volume of the target data table, and the column discrimination of the target columns includes: Based on the syntax parsing results and the execution plan, a first syntax quality score is determined; Based on the field type corresponding to each column in the target data table, type detection is performed on the field values ​​of the query fields in the structured query statement to determine the second syntax quality score; Based on the execution plan and the column discrimination of the target column, the index quality score is determined; Based on the execution plan and the amount of data in the target data table, a query efficiency score is determined; The quality analysis result is determined based on the first syntax quality score, the second syntax quality score, the index quality score, and the query efficiency score.

9. A statement analysis device, characterized in that, include: The first acquisition module is used to acquire the structured query statement to be analyzed; The parsing module is used to perform syntax parsing on the structured query statement and obtain the syntax parsing result; The second acquisition module is used to acquire the metadata of the target data table from the first storage area corresponding to the test environment; The target data table is determined based on the table name in the structured query statement; The third acquisition module is used to acquire the table data volume of the target data table and the column discrimination of the target column in the target data table from the second storage area corresponding to the production environment; The second storage area stores the table data volume of each data table and the column discrimination of each column in the production database corresponding to the production environment. The table data volume of each data table and the column discrimination of each column are updated according to a first preset period. The column discrimination of each column represents the proportion of fields in each column that are not repeated; The first determining module is used to determine the execution plan of the structured query statement based on the production database; The second determining module is used to determine the quality analysis result of the structured query statement based on the syntax parsing result, the execution plan, the metadata of the target data table, the table data volume of the target data table, and the column discrimination of the target column.

10. An electronic device, characterized in that, The electronic device includes a processor and a memory, the memory storing at least one instruction or at least one program, the at least one instruction or the at least one program being loaded and executed by the processor to implement the statement analysis method as described in any one of claims 1-8.

11. A computer storage medium, characterized in that, The storage medium stores at least one instruction or at least one program segment, which is loaded and executed by a processor to implement the statement analysis method as described in any one of claims 1-8.

12. A computer program product, characterized in that, The computer program product includes at least one instruction or at least one program segment, which is loaded and executed by a processor to implement the statement analysis method as described in any one of claims 1-8.

Images