Database table field index establishment method and device, electronic equipment and storage medium

Abstract

The embodiment of the invention provides a database table field index establishment method and device, electronic equipment and a storage medium, and belongs to the technical field of financial science and technology. The method comprises the steps of obtaining a target query statement and statement execution plan information; splitting the target query statement to obtain statement key data; determining the total row number of the candidate database table based on the historical index database; extracting index use data from the candidate database table based on the statement execution plan information; determining a new field index from the candidate column indexes based on the candidate distinction degree of the candidate column indexes; verifying the new field index based on the statement execution plan information, the historical index database, the new region division degree of the new field index and the candidate region division degree to obtain an index verification result; and if the index verification result is that the index establishment verification is passed, performing field index establishment based on the new field index. According to the embodiment of the invention, the database table field index more suitable for the current query request can be automatically established, so that the data query efficiency is improved.

Description

Technical Field

[0001] The present application relates to the field of financial technology, and in particular to a method and device for establishing a database table field index, an electronic device, and a storage medium. Background Art

[0002] An index is a structure that sorts the values ​​of one or more columns in a database table. Using an index, you can quickly access specific information in a database table. With the continuous development of Internet technology, users' daily Internet activities will generate massive amounts of data. For example, in the insurance sales field of financial technology, due to the large number of insurances sold every day and the large amount of relevant information filled in for each insurance, when sales staff need to quickly find specific information about a specific object, they need to use indexes to quickly query the corresponding data.

[0003] At present, there are generally several methods for indexing database table fields in related technologies: one is to index all database table fields, but this method creates too many indexes and they are relatively isolated, and there are usually too many unnecessary indexes, which takes up a lot of disk space and is prone to slow query problems during query; the second is to not create indexes before the system goes online, and then manually troubleshoot problems after slow queries occur after going online, and create corresponding indexes. This method consumes a lot of manpower and material resources, and due to the huge amount of data, the process of indexing after troubleshooting is also very slow, and even affects the processing of other business requests. Based on this, the method for optimizing the slow query problem of the database proposed by the related technology relies too much on manual operation, which is easy to waste database resources, and a large amount of data processing makes the efficiency of data query low. Therefore, how to propose a method that can automatically establish a database table field index that is more suitable for the current query request to optimize the slow query problem during data query, thereby effectively improving the efficiency of data query, has become a technical problem that needs to be solved urgently. Summary of the invention

[0004] The main purpose of the embodiments of the present application is to propose a method and device for establishing a database table field index, an electronic device, and a storage medium, aiming to automatically establish a database table field index that is more suitable for the current query request, so as to optimize the slow query problem during data query, thereby effectively improving data query efficiency.

[0005] To achieve the above-mentioned purpose, a first aspect of an embodiment of the present application proposes a method for establishing a database table field index, the method comprising:

[0006] Obtaining a target query statement and statement execution plan information of the target query statement, wherein the target query statement is a statement that meets a preset slow query statement condition;

[0007] Perform sentence splitting on the target query sentence to obtain sentence key data, where the sentence key data is used to indicate a field selection condition of a candidate database table associated with the target query sentence;

[0008] Determine the total number of rows in the candidate database table based on the historical index library;

[0009] Extracting indexes from the candidate database table based on the statement execution plan information to obtain index usage data, wherein the index usage data includes candidate column indexes used when the target query statement is executed;

[0010] Determine a new field index from the candidate column index based on a candidate discrimination degree of the candidate column index and the statement key data, wherein the candidate discrimination degree is determined based on a ratio of the number of different field values ​​in the candidate column index to the total number of rows in the table;

[0011] Performing index verification on the newly created field index based on the statement execution plan information, the historical index library, the new discrimination of the newly created field index, and the candidate discrimination, to obtain an index verification result, wherein the index verification result includes a pass in the index establishment verification, and the pass in the index establishment verification indicates that the newly created field index meets the conditions for index establishment;

[0012] If the index verification result is that the index establishment verification is passed, a field index is established based on the newly created field index.

[0013] In some embodiments, determining a new field index from the candidate column index based on the candidate discrimination of the candidate column index and the statement key data includes:

[0014] If the candidate column index is a single column index, determining the candidate column index as the newly created field index;

[0015] If the candidate column index is a composite index, the total number of index columns of the candidate column index is determined based on the statement key data, and a new field index is determined from the candidate column index based on the total number of index columns.

[0016] In some embodiments, determining a new field index from the candidate column indexes based on the total number of index columns includes:

[0017] Performing index combinations on the candidate column indexes based on the total number of index columns and a preset number of combinations to obtain a candidate combination field index;

[0018] Calculating the discrimination of the candidate combined field index to obtain the discrimination of the combined index;

[0019] The newly created field index is determined from the candidate combined field indexes based on the combined index discrimination degree.

[0020] In some embodiments, determining the newly created field index from the candidate combined field indexes based on the combined index discrimination degree includes:

[0021] Determine a field index weight value of the candidate combined field index based on the statement key data;

[0022] Update the combined index discrimination based on the field index weight value to obtain an updated index discrimination;

[0023] The newly created field index is determined from the candidate combined field indexes based on the updated index discrimination.

[0024] In some embodiments, the index verification of the newly created field index based on the statement execution plan information, the historical index library, the new discrimination of the newly created field index, and the candidate discrimination to obtain the index verification result includes:

[0025] Performing index usage verification on the newly created field index based on the statement execution plan information to determine the index usage status of the newly created field index;

[0026] Performing index storage verification on the newly created field index based on the historical index library to determine the index storage status of the newly created field index;

[0027] Performing index discrimination check on the newly created field index based on the new discrimination of the newly created field index and the candidate discrimination, and determining an index discrimination check result;

[0028] The index verification result is determined based on the index usage status, the index storage status and the index discrimination verification result.

[0029] In some embodiments, performing index discrimination check on the newly created field index based on the new discrimination of the newly created field index and the candidate discrimination to determine the index discrimination check result includes:

[0030] Comparing the new discrimination degree with the preset discrimination degree threshold value to obtain a numerical comparison result;

[0031] If the numerical comparison result indicates that the new discrimination is less than or equal to the preset discrimination threshold, the quotient of the new discrimination and the candidate discrimination is calculated to obtain a discrimination ratio;

[0032] The index discrimination check result is determined based on a numerical comparison result of the discrimination ratio and a preset ratio threshold.

[0033] In some embodiments, the determining the index verification result based on the index usage status, the index storage status and the index discrimination verification result includes:

[0034] If the index usage status indicates that the newly created field index is not an index used in the statement execution plan information, determining that the newly created field index meets the index usage condition when the index is created;

[0035] If the index storage status indicates that the newly created field index is not an index stored in the historical index library, determining that the newly created field index meets the index storage condition when the index is created;

[0036] If the index discrimination check result indicates that the discrimination ratio is less than or equal to the preset ratio threshold, it is determined that the newly created field index meets the discrimination condition when the index is established;

[0037] If the newly created field index meets the index use condition, the index storage condition and the discrimination condition when the index is established, it is determined that the index verification result is passed.

[0038] To achieve the above-mentioned purpose, a second aspect of an embodiment of the present application proposes a device for establishing a database table field index, the device comprising:

[0039] An acquisition module, used to acquire a target query statement and statement execution plan information of the target query statement, wherein the target query statement is a statement that meets a preset slow query statement condition;

[0040] A splitting module, used to split the target query statement to obtain statement key data, where the statement key data is used to indicate a field selection condition of a candidate database table associated with the target query statement;

[0041] A first determination module, configured to determine the total number of rows of the candidate database table based on a historical index library;

[0042] An extraction module, configured to extract indexes from the candidate database table based on the statement execution plan information to obtain index usage data, wherein the index usage data includes candidate column indexes used when the target query statement is executed;

[0043] A second determination module is used to determine a new field index from the candidate column index based on a candidate discrimination degree of the candidate column index and the statement key data, wherein the candidate discrimination degree is determined based on a ratio of the number of different field values ​​in the candidate column index to the total number of rows in the table;

[0044] An index verification module is used to perform index verification on the newly created field index based on the statement execution plan information, the historical index library, the new discrimination of the newly created field index, and the candidate discrimination, to obtain an index verification result, wherein the index verification result includes a pass in the index establishment verification, and the pass in the index establishment verification indicates that the newly created field index meets the conditions for index establishment;

[0045] An index building module is used to build a field index based on the newly created field index if the index verification result is that the index building verification has passed.

[0046] To achieve the above objectives, a third aspect of an embodiment of the present application proposes an electronic device, which includes a memory and a processor, wherein the memory stores a computer program, and when the processor executes the computer program, the method described in the first aspect is implemented.

[0047] To achieve the above-mentioned purpose, the fourth aspect of an embodiment of the present application proposes a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method described in the first aspect is implemented.

[0048] The present application proposes a method and device for establishing a database table field index, an electronic device, and a storage medium. The method obtains a target query statement and statement execution plan information of the target query statement, wherein the target query statement is a statement that meets the preset slow query statement condition; further, the target query statement is split to obtain statement key data, and the statement key data is used to indicate the field selection condition of the candidate database table associated with the target query statement; the total number of rows of the candidate database table is determined based on the historical index library; the candidate database table is indexed based on the statement execution plan information to obtain index usage data, and the index usage data includes the number of rows used when the target query statement is executed. candidate column index; further, based on the candidate discrimination of the candidate column index and the key data of the statement, a new field index is determined from the candidate column index, the candidate discrimination being determined based on the ratio of the number of different field values ​​in the candidate column index to the total number of rows in the table; further, based on the statement execution plan information, the historical index library, the new discrimination of the new field index and the candidate discrimination, the new field index is index-checked to obtain an index check result, the index check result includes an index establishment check pass, and the index establishment check pass indicates that the new field index meets the conditions for index establishment; if the index check result is an index establishment check pass, the field index is established based on the new field index. Compared with the related art that uses manual troubleshooting of slow query problems and then creates a new index, the present application can automatically establish a database table field index for statements in the database that meet the preset slow query statement conditions, so as to establish a database table field index that is more suitable for the current query request, thereby optimizing the slow query problem during data query and effectively improving data query efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0049] Figure 1 It is a flow chart of a method for establishing a database table field index provided in an embodiment of the present application;

[0050] Figure 2 yes Figure 1 A flowchart of step S150 in FIG.

[0051] Figure 3 yes Figure 2 A flowchart of step S230 in FIG.

[0052] Figure 4 yes Figure 1 A flowchart of step S160 in FIG.

[0053] Figure 5 yes Figure 4 A flowchart of step S430 in FIG.

[0054] Figure 6 yes Figure 4 A flowchart of step S440 in FIG.

[0055] Figure 7 It is a structural schematic diagram of a device for establishing a database table field index provided in an embodiment of the present application;

[0056] Figure 8 It is a hardware structure diagram of the electronic device provided in the embodiment of the present application. DETAILED DESCRIPTION

[0057] In order to make the purpose, technical solution and advantages of the present application more clearly understood, the present application is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0058] It should be noted that, although the functional modules are divided in the device schematic diagram and the logical order is shown in the flowchart, in some cases, the steps shown or described may be performed in a different order than the module division in the device or the order in the flowchart. The terms "first", "second", etc. in the specification, claims and the above drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence.

[0059] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as those commonly understood by those skilled in the art to which this application belongs. The terms used herein are only for the purpose of describing the embodiments of this application and are not intended to limit this application.

[0060] First, some nouns involved in this application are analyzed:

[0061] Artificial Intelligence (AI): It is a new technical science that studies and develops theories, methods, technologies and application systems for simulating, extending and expanding human intelligence. AI is a branch of computer science. AI attempts to understand the essence of intelligence and produce a new intelligent machine that can respond in a similar way to human intelligence. Research in this field includes robots, language recognition, image recognition, natural language processing and expert systems. AI can simulate the information process of human consciousness and thinking. AI is also a theory, method, technology and application system that uses digital computers or machines controlled by digital computers to simulate, extend and expand human intelligence, perceive the environment, acquire knowledge and use knowledge to obtain the best results.

[0062] Index: can be created based on one or more fields. Index is a data structure used in database management system to improve data retrieval speed. Index is similar to the table of contents of a book. By creating an index, you can quickly locate a specific data row in the table.

[0063] Field: A column in a database table that stores a specific type of data. Each field has a name and a data type, such as integer, string, date, etc. A field defines a certain attribute of each row in the table. For example, in an employee information table, the fields may include "employee ID", "position", "entry date", etc.

[0064] An index is a structure that sorts the values ​​of one or more columns in a database table. Using an index, you can quickly access specific information in a database table. With the continuous development of Internet technology, users' daily Internet activities will generate massive amounts of data. For example, in the insurance sales field of financial technology, due to the large number of insurances sold every day and the large amount of relevant information filled in for each insurance, when sales staff need to quickly find specific information about a specific object, they need to use indexes to quickly query the corresponding data.

[0065] Slow Structured Query Language (SQL) refers to SQL statements that take longer than expected or significantly longer to execute in the database. There are many reasons for the slow execution of SQL statements, such as: (1) Unreasonable index design: No appropriate index is created for columns that are frequently used for query, connection or sorting, or too many unnecessary indexes are created (that is, more indexes are created in the database table than are actually needed and have limited effect on query performance). (2) Complex query logic: SQL statements contain complex connections, subqueries, aggregation operations, etc., which require the database to perform a large amount of calculations and data processing. (3) Excessive data volume: The amount of data in the table is huge, but the query does not perform effective paging or limit the number of returned results. (4) Lack of necessary condition filtering: The database needs to scan a large amount of unnecessary data. (5) Improper database server configuration: For example, insufficient memory allocation, unreasonable cache settings, etc. Slow QL will have a negative impact on database performance, which may cause slow system response, excessive resource consumption, and even affect the stability and user experience of the entire application system. Therefore, timely detection and optimization of slow SQL is one of the important tasks of database management and performance optimization. In addition, optimizing slow SQL has the following important significance: (1) Improving system performance: Optimized SQL statements can reduce the response time of the database, speed up data retrieval, insertion, update and deletion operations, thereby improving the speed and efficiency of the entire system and providing users with a smoother experience. (2) Saving resources: By optimizing SQL, the central processing unit (CPU) usage, memory usage and I / O operations of the database server can be reduced, enabling the server to handle more concurrent requests and improve resource utilization. (3) Enhancing system stability: Reducing database blocking and deadlock caused by slow SQL, reducing the risk of system failure, enhancing system stability and reliability, and ensuring the continuous operation of the business. (4) Improving user satisfaction: A fast-responding system can meet users' needs for timely data operations and improve user satisfaction and trust in the application. (5) Adapting to business growth: As the amount of business data continues to increase, optimized SQL can ensure that the system still maintains good performance when the data scale expands, without the need for frequent large-scale architectural adjustments. (6) Facilitating maintenance and management: Clear and efficient SQL statements are easier to understand and maintain, reducing the difficulty and workload of developers and maintenance personnel. Therefore, identifying slow SQL and optimizing it is of vital importance to ensure efficient and stable operation of the database system, meet business needs, control costs, and improve user experience.

[0066] At present, there are generally several methods for indexing database table fields in related technologies: one is to index all database table fields, but this method creates too many indexes and they are relatively isolated, and there are usually too many unnecessary indexes, which takes up a lot of disk space and is prone to slow query problems during query; the second is to not create indexes before the system goes online, and then manually troubleshoot problems after slow queries occur after going online, and create corresponding indexes. This method consumes a lot of manpower and material resources, and due to the huge amount of data, the process of indexing after troubleshooting is also very slow, and even affects the processing of other business requests. Based on this, the method for optimizing the slow query problem of the database proposed by the related technology relies too much on manual operation, which is easy to waste database resources, and a large amount of data processing makes the efficiency of data query low. Therefore, how to propose a method that can automatically establish a database table field index that is more suitable for the current query request to optimize the slow query problem during data query, thereby effectively improving the efficiency of data query, has become a technical problem that needs to be solved urgently.

[0067] Based on this, the embodiments of the present application provide a method and device for establishing a database table field index, an electronic device, and a storage medium, which can automatically establish a database table field index that is more suitable for the current query request to optimize the slow query problem during data query, thereby effectively improving data query efficiency.

[0068] The embodiments of the present application can acquire and process relevant data based on artificial intelligence technology. Among them, artificial intelligence (AI) is the theory, method, technology and application system that uses digital computers or machines controlled by digital computers to simulate, extend and expand human intelligence, perceive the environment, acquire knowledge and use knowledge to obtain the best results.

[0069] AI basic technologies generally include sensors, dedicated AI chips, cloud computing, distributed storage, big data processing technology, operation / interaction systems, mechatronics, etc. AI software technologies mainly include computer vision technology, robotics technology, biometrics technology, speech processing technology, natural language processing technology, and machine learning / deep learning.

[0070] The method for establishing a database table field index provided in the embodiment of the present application relates to the field of artificial intelligence technology. The method for establishing a database table field index provided in the embodiment of the present application can be applied to a terminal, can be applied to a server side, or can be software running in a terminal or a server side. In some embodiments, the terminal can be a smart phone, a tablet computer, a laptop computer, a desktop computer, etc.; the server side can be configured as an independent physical server, or can be configured as a server cluster or a distributed system composed of multiple physical servers, or can be configured as a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, CDN, and big data and artificial intelligence platforms; the software can be an application that implements the method for establishing a database table field index, etc., but is not limited to the above forms.

[0071] The present application can be used in many general or special computer system environments or configurations. For example: personal computers, server computers, handheld or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, set-top boxes, programmable consumer electronics, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, etc. The present application can be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform specific tasks or implement specific abstract data types. The present application can also be practiced in distributed computing environments, in which tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media including storage devices.

[0072] It should be noted that in each specific implementation of the present application, when it comes to the need to perform relevant processing based on the query of the database table of the object, the execution status and other data related to the identity or characteristics of the object, the permission or consent of the object will be obtained first, and the collection, use and processing of these data will comply with relevant laws, regulations and standards. In addition, when the embodiment of the present application needs to obtain the sensitive personal information of the object, the separate permission or separate consent of the object will be obtained through a pop-up window or jump to the confirmation page. After clearly obtaining the separate permission or separate consent of the object, the necessary user-related data for the normal operation of the embodiment of the present application will be obtained.

[0073] See also Figure 1 , Figure 1 is an optional flow chart of a method for establishing a database table field index provided in an embodiment of the present application. In some embodiments of the present application, Figure 1The method may specifically include but is not limited to steps S110 to S170.

[0074] Step S110, obtaining a target query statement and statement execution plan information of the target query statement;

[0075] Step S120, splitting the target query statement to obtain key data of the statement;

[0076] Step S130, determining the total number of rows of the candidate database table based on the historical index library;

[0077] Step S140, extracting indexes from candidate database tables based on the statement execution plan information to obtain index usage data;

[0078] Step S150, determining a new field index from the candidate column index based on the candidate discrimination of the candidate column index and the sentence key data;

[0079] Step S160, performing index verification on the newly created field index based on the statement execution plan information, the historical index library, the new discrimination degree and the candidate discrimination degree of the newly created field index, and obtaining an index verification result;

[0080] Step S170: If the index verification result is that the index establishment verification has passed, a field index is established based on the newly created field index.

[0081] In step S110 of some embodiments, the target query statement is a statement that meets the preset slow query statement conditions. Statements that meet the preset slow query statement conditions refer to SQL statements in database queries when the query execution time exceeds the preset time threshold or the statement structure is significantly longer. The system will identify those SQL query statements that meet the preset slow query conditions, as well as the execution plan information of these query statements. The execution plan information includes the execution strategy generated by the query optimizer for the target query statement, which helps determine the query efficiency of the target query statement.

[0082] It should be noted that the execution plan information is an internal plan generated by the database management system when executing SQL statements, which is used to describe how the database engine will process and execute the statement. The execution plan information can contain a series of operation steps and related information, such as: table access methods (such as full table scan, index scan, etc.), connection operation types (such as inner join, outer join, etc.), sorting and grouping operations, etc. By viewing the execution plan, you can further understand the execution efficiency of the statement (determine whether there is unnecessary overhead or inefficient operation), the use of indexes (confirm whether the index is used correctly to improve query performance), resource consumption estimation (predict the time and memory resources required for statement execution), etc.

[0083] It should be noted that the database system of the present application can scan the slow query log of the database according to a preset time period (such as every day, every 12 hours), and select the first n (n is a positive integer, such as 20, 10, etc.) to save in the slow query statement list. In this way, each statement in the slow query statement list is the target query statement.

[0084] It should be noted that the process of identifying target query statements that meet the conditions of slow SQL statements may include: (1) using the performance detection tool provided by the database (because most database systems provide performance detection and logging functions, you can view SQL statements with long execution time and their related performance indicators, such as execution time, CPU usage, I / O operations, etc.); (2) using the logging records on the application side (because the execution time of each SQL statement is recorded in the application, and those that exceed the set threshold are marked as slow SQL); (3) using third-party detection tools (there are many third-party tools specifically used to detect database performance) to assist in collecting and analyzing database performance data to identify slow SQL); (4) regular performance testing: by simulating actual business scenarios and loads, the database is performance tested to identify slow SQL statements; (5) the database will record SQL statements whose execution time exceeds a specific threshold in the slow query log, and identify slow SQL by analyzing the slow query log of the database. There are other ways, which are not limited. After identifying the slow SQL, its execution plan, index usage, data volume and other factors can be further analyzed to determine the specific reasons for its slow execution and take corresponding optimization measures. However, the slow SQL optimization measures adopted by related technologies rely too much on manual intervention. Developers may be negligent and fail to identify slow SQL, which will cause waste of database resources and even affect business processing. When the operation and maintenance personnel receive database alarm emails and identify slow SQL and then repair it, the database has already been affected and there is a certain lag. Moreover, whether the identified slow SQL can be repaired depends heavily on the SQL optimization ability and on-the-spot performance of the developer, which increases the operation and maintenance costs.

[0085] In step S120 of some embodiments, after determining the slow query statement list that needs to be optimized, the slow query statement list can be scanned one by one, and each target query statement therein is first split to obtain statement key data. The statement key data is used to indicate the field selection condition of the candidate database table associated with the target query statement.

[0086] It should be noted that the key data of the statement includes the main table screening condition, the main table association condition, the secondary table screening condition, and the secondary table association condition. Among them, the main table refers to the main candidate database table in the target query statement, which is usually the core source of the query result set (for example, it can be the first database table after the Select statement). The secondary table refers to other candidate database tables associated with the main table, which are usually used to provide additional information or details (for example, it can be other tables in the Select statement except the main table). The main table screening condition refers to the condition for selecting specific records from the main table (for example, it can be the field selection condition related to the main table after the where keyword in the Select statement). The main table association condition refers to the condition for selecting specific records based on the field association relationship between the main table and its associated table (for example, it can be the field selection condition that needs to be met when the main table is associated with other data tables in the Select statement). The secondary table screening condition refers to the condition for selecting specific records from the secondary table. The secondary table association condition refers to the condition for selecting specific records based on the field association relationship between the secondary table and its associated table. For example, the target query statement is "Select * from pg_catalog.pg_class a left joinpg_catalog.pg_attribute c on a.oid=c.attrelid left join pg_catalog.pg_description d on d.objoid=c.attrelid and d.objsubid=c.attnum left joininformation_schema.columns cl on cl.table_name=a.relname and cl.column_name=c.attname where a.table_schema='public'and cl.table_schema='public'". By splitting the target query statement, it can be obtained that database table a is the primary table, and database tables c, d, and cl are all secondary tables. Among them, the screening condition of the main table a is "a.table_schema = 'public'", and the association conditions of the main table a are "a.oid = c.attrelid" and "cl.table_name = a.relname", so the column indexes used when executing the main table a are "a.table_schema", "a.oid" and "a.relname".Similarly, the filter condition of sub-table c is empty, and the association conditions of sub-table c are "a.oid=c.attrelid", "d.objoid=c.attrelid", "d.objsubid=c.attnum" and "cl.column_name=c.attname", so the column indexes used when sub-table c is executed are "c.attrelid", "c.attnum" and "c.attname". The filter condition of sub-table d is empty, and the association conditions of sub-table d are "d.objoid=c.attrelid" and "d.objsubid=c.attnum", so the column indexes used when sub-table d is executed are "d.objoid" and "d.objsubid". The screening condition of sub-table cl is "cl.table_schema = 'public'", and the association conditions of sub-table cl are "cl.table_name = a.relname" and "cl.column_name = c.attname", so the column indexes used when executing sub-table cl are "d.objsubid", "cl.table_name" and "cl.column_name".

[0087] In step S130 of some embodiments, the total number of rows of each candidate database table is further obtained based on the historical index library to facilitate the subsequent calculation of the discrimination. For example, the total number of rows of the table can represent the total number of records in the table, which can be recorded as CT, and the statement obtained can be "Select count(*)ct from Table". The historical index library refers to a structure for storing candidate database tables and historical index lists.

[0088] In step S140 of some embodiments, further, the statement execution plan information can be scanned to extract indexes from the candidate database tables, thereby obtaining the used index information, i.e., index usage data, which includes the candidate column indexes used when the target query statement is executed. For specific examples of candidate column indexes, see the specific examples in step S120. In this way, the index usage data set obtained by the present application can include the indexes used by the main table and the indexes used by the secondary table.

[0089] In step S150 of some embodiments, further, a new field index may be determined from the candidate column index based on the candidate discrimination of the candidate column index and the key data of the statement, that is, by analyzing the candidate discrimination of the candidate column index (that is, the proportion of different field values ​​in the total number of rows) and the key data of the statement, it is determined whether to create a new field index. The field index is aimed at candidate uniqueness and query optimization to ensure that query efficiency can be improved.

[0090] It should be noted that the candidate discrimination is determined based on the ratio of the number of different field values ​​in the candidate column index to the total number of rows in the table. The index discrimination is an important basis for determining whether the index is efficient. Slow SQL is generally caused by the low discrimination of the index used. The discrimination of the index is an important indicator for measuring the effectiveness of the index in the database. It can represent the ratio of non-repeated values ​​in the column index, and can also be understood as the uniqueness of the index. The higher the discrimination of the index, the more diverse the values ​​in the index, and relatively few data rows have the same index value. Such an index is more conducive to improving the efficiency of the query. Because a high-discrimination index can quickly exclude data rows that do not meet the conditions and reduce the scope of database scanning. The calculation formula of the index discrimination can be expressed as: discrimination = count (distinct specific column) / count (*), count (*) represents the total number of rows in the table, distinct is a keyword in SQL, which is used to return unique and different values, count (distinct specific column) means removing duplicate rows in the result set, leaving only unique records, and counting the total number of these unique records, that is, the number of different field values ​​in the candidate column index. The closer this ratio is to 1, the higher the discrimination of the index is, and the greater the value of the index is, which means that the cost-effectiveness for retrieval is high.

[0091] It should be noted that count(*) can be written as CT, and in addition to obtaining index usage data, this application can also obtain all existing indexes in the database table, and the existing indexes may not be used in this query. For example, the statement to obtain all existing indexes can be "select * from pg_catalog.pg_indexes t where t.tablename='Table'", where pg_catalog.pg_indexes refers to the table whose data needs to be detected, and contains information about all indexes in the database. t is an alias for this table, and WHERE t.tablename='Table' is a filter condition used to limit the results to only those rows whose tablename column is equal to 'Table'.

[0092] In some embodiments, the process of determining a new field index from the candidate column index based on the candidate discrimination of the candidate column index and the statement key data in step S150 may specifically include at least one of the following:

[0093] If the candidate column index is a single column index, the candidate column index is determined as a new field index;

[0094] If the candidate column index is a composite index, the total number of index columns of the candidate column index is determined based on the statement key data, and a new field index is determined from the candidate column index based on the total number of index columns.

[0095] In some specific embodiments, the index types of the database may include single-column indexes and composite indexes. A singleton index refers to an index created on a single column of a table. This type of index improves query efficiency based only on the value of one column. For example, if a table has a column named username, and this column is often used in query conditions, then creating a singleton index on this column can improve the efficiency of querying a specific username. If, after analyzing the target query statement, it is considered that a single column (single-column index) is sufficient to meet the needs of query optimization, then it is decided to create a new index on the single column. For example, if queries like SELECT*FROM table WHERE columnA=value are often executed, then columnA is a candidate for a single-column index, and a new index can be created on this column to improve the efficiency of such queries. For example, when determining a single-column index, the corresponding statement can be "Select column_name, count(*)cn from Table group by column_name order by cn desc limit 1;", which means that from a table named Table, the values ​​of a column column_name are grouped, the number of rows in each group is calculated, and then the rows are sorted in descending order. Finally, the records of the group with the largest number of rows are selected as the corresponding new field index.

[0096] In some specific embodiments, a composite index is also called a multi-column index or a joint index, which refers to an index created simultaneously on two or more columns of a table. This index can improve query efficiency based on the values ​​of multiple columns. For example, if you often need to query based on the username and created_at columns at the same time, you can create a composite index containing these two columns. If the analysis shows that a combination of multiple columns is needed to optimize the query (i.e., a composite index), then you need to determine the total number of these columns (i.e., the total number of index columns), and decide which columns to index based on this total number. For example, if a query like SELECT*FROM table WHERE columnA=value AND columnB=value is often executed, then columnA and columnB together constitute a candidate for a composite index. In this case, you need to decide whether to create indexes on these two columns at the same time. After determining the total number of index columns, you can select specific columns to create indexes based on the key data in the query statement (i.e., which columns often appear together in the query conditions). For example, when determining a composite index, the corresponding statement can be "Select column_name1,column_name2,count(*)cn from Table group bycolumn_name1,column_name2 order by cn desc limit 1;", where cn represents the total number of index columns, which is the maximum number of records after grouping, that is, an alias for count(*) after grouping.

[0097] It should be noted that the candidate column index of this application is not the Select column, but all the filter columns (i.e., the column index involved after the where keyword) and associated columns (the column index involved after the join on keyword) in the target query statement. In this way, the total number of index columns can refer to the total number of column indexes after where and join on in the target query statement.

[0098] In some embodiments, determining the total number of index columns of the candidate column index based on the statement key data may specifically include: extracting the screening condition column data and the association condition column data from the statement key data; performing column statistics based on the number of columns of the screening condition column data and the number of columns of the association condition column data to obtain the total number of index columns. The screening condition column data is used to indicate the column data corresponding to the main table screening condition and the secondary table screening condition, and the association condition column data is used to indicate the column data corresponding to the main table association condition and the secondary table association condition.

[0099] See also Figure 2 , Figure 2This is a specific flow chart of determining the newly created field index corresponding to the combined index in step S150 provided in an embodiment of the present application. In some embodiments of the present application, the process of determining the newly created field index corresponding to the combined index may specifically include but is not limited to steps S210 to S230.

[0100] Step S210, index combining the candidate column indexes based on the total number of index columns and the preset number of combinations to obtain a candidate combination field index;

[0101] Step S220, calculating the discrimination degree of the candidate combined field index to obtain the discrimination degree of the combined index;

[0102] Step S230: determining a new field index from the candidate combined field indexes based on the combined index discrimination.

[0103] In step S210 of some embodiments, the preset combination number is used to represent the maximum number of column indexes contained in a newly created field index established based on a combined index. For example, because the combined index generally does not exceed 3 fields, the preset combination number can be set to 3. Based on this, step S210 can specifically include: if the total number of index columns is greater than the preset combination number, index combination is performed on the candidate column indexes to obtain multiple candidate combined field indexes. The specific process of index combination can adopt an unordered permutation combination. For example, if the total number of index columns is 5 and the preset combination number is 3, then according to the combination formula Perform index combination, where n represents the total number of index columns, and r represents a positive integer less than or equal to the preset number of combinations (i.e., 1, 2, and 3 can be selected in sequence). In this way, a combination of 3 candidate column indexes can be selected from 5 candidate column indexes, a combination of 2 candidate column indexes can be selected from 5 candidate column indexes, a combination of 1 candidate column index can be selected from 5 candidate column indexes, and so on. That is, a candidate combination field index can take at most three column indexes to obtain multiple candidate combination field indexes.

[0104] In step S220 of some embodiments, further, a discrimination calculation is performed on each candidate combination field index to obtain a combination index discrimination corresponding to each candidate combination field index. The combination index discrimination is calculated as the ratio of the number of different combination field values ​​in the candidate combination field index to the total number of table rows.

[0105] In step S230 of some embodiments, further, a numerical comparison is performed on the combined index discriminations corresponding to a plurality of candidate combined field indexes, and the candidate combined field index with the highest discrimination is determined as the newly created field index.

[0106] See also Figure 3 , Figure 3It is a specific flow chart of step S230 provided in an embodiment of the present application. In some embodiments of the present application, step S230 may specifically include but is not limited to steps S310 to S330.

[0107] Step S310, determining a field index weight value of a candidate combined field index based on the statement key data;

[0108] Step S320, updating the combined index discrimination based on the field index weight value to obtain an updated index discrimination;

[0109] Step S330: determining a new field index from the candidate combined field indexes based on the updated index discrimination.

[0110] In steps S310 to S330 of some embodiments, the field index weight value refers to a quantitative indicator of the impact of each field index in the candidate combined field index on the query performance. The field index weight value can be determined based on factors such as the frequency of the field index in the query in the key data of the statement, the efficiency of the field as a filtering condition in the query, etc. For example, if field A appears as a condition in multiple queries and has a great effect on reducing the result set, it may be assigned a higher weight value. Further, the combined index discrimination refers to the sum of the query optimization capabilities when these fields are used as indexes together. In this way, the discrimination of the combined index can be updated or calculated based on the previously determined field index weight value. The field combination corresponding to the updated index discrimination is the effectiveness of the index as an index. The higher the discrimination, the greater the improvement of the query performance by the combined index. Further, the candidate combined field index corresponding to the largest updated index discrimination can be determined as a new field index.

[0111] In the above embodiment, the present application will consider single column index and combined index when creating a new index, and will consider the combined index discrimination when determining the combined index, and can further determine a more accurate combined new field index based on the field index weight value. In this way, the automated index optimization process can be better implemented, the workload of manual analysis can be reduced, and the overall performance of the database can be improved.

[0112] In step S160 of some embodiments, after determining the newly created field index, it is also necessary to verify whether the newly created field index meets the conditions for index establishment. The index verification results include the index establishment verification passing and the index establishment verification failing. The index establishment verification passing indicates that the newly created field index meets the conditions for index establishment, and the index establishment verification failing indicates that the newly created field index does not meet the conditions for index establishment.

[0113] See also Figure 4 , Figure 4It is a specific flow chart of step S160 provided in an embodiment of the present application. In some embodiments of the present application, step S160 may specifically include but is not limited to steps S410 to S440.

[0114] Step S410, performing index usage verification on the newly created field index based on the statement execution plan information to determine the index usage status of the newly created field index;

[0115] Step S420, performing index storage verification on the newly created field index based on the historical index library to determine the index storage status of the newly created field index;

[0116] Step S430, performing index discrimination check on the newly created field index based on the new discrimination and candidate discrimination of the newly created field index, and determining the index discrimination check result;

[0117] Step S440: determining the index verification result based on the index usage status, the index storage status and the index discrimination verification result.

[0118] In step S410 of some embodiments, index usage verification refers to verifying whether the newly created field index is the index used in the statement execution plan information of the target query statement. If the newly created field index is the index used in the statement execution plan information, that is, this index already exists in the database and has been used by the query execution plan. In this way, it can be determined that the index usage status of the newly created field index is the index used, that is, the corresponding index usage verification fails (because this index has already sufficiently optimized the query performance, and building another identical index is redundant, or may cause an increase in maintenance costs). If the newly created field index is not the same as the index used in the statement execution plan information, the index usage status of the newly created field index is determined to be the index not used, and the corresponding index usage verification passes.

[0119] In step S420 of some embodiments, index storage verification refers to verifying whether the newly created field index is an existing index in the historical index library. Specifically, the index storage verification can be performed by scanning the historical index library. If the newly created field index is an existing index in the historical index library, the index storage status of the newly created field index is determined to be the index stored, that is, the corresponding index storage verification fails. If the newly created field index is an index that does not exist in the historical index library, the index storage status of the newly created field index is determined to be the index not stored, that is, the corresponding index storage verification passes. In this way, the creation of invalid new field indexes can be avoided.

[0120] In step S430 of some embodiments, further, the newly created field index can be subjected to index discrimination check based on the new discrimination and candidate discrimination of the newly created field index to determine the index discrimination check result, and the index discrimination check is used to check whether the new index is more efficient than the old index. For example, the number of different field values ​​in the column index contained in the newly created field index is CN1, and the corresponding total number of table rows is CT, then the new discrimination = CN1 / CT. The candidate discrimination refers to the discrimination of the index used when the statement executes the plan information, and the number of different field values ​​in the column index contained in it is CN2, and the corresponding total number of table rows is CT, then the candidate discrimination = CN2 / CT.

[0121] See also Figure 5 , Figure 5 It is a specific flow chart of step S430 provided in an embodiment of the present application. In some embodiments of the present application, step S430 may specifically include but is not limited to steps S510 to S530.

[0122] Step S510, performing a numerical comparison between the new discrimination and the preset discrimination threshold to obtain a numerical comparison result;

[0123] Step S520, if the numerical comparison result indicates that the new discrimination is less than or equal to the preset discrimination threshold, the new discrimination and the candidate discrimination are calculated to obtain a discrimination ratio;

[0124] Step S530: determining the index discrimination verification result based on the numerical comparison result between the discrimination ratio and the preset ratio threshold.

[0125] In step S510 of some embodiments, the preset discrimination threshold is used to characterize the preset discrimination threshold of the main table data ratio. If the new discrimination is less than or equal to the preset discrimination threshold, it means that the verification of the main table data ratio has passed, and then further verification is conducted to see whether the new index is more efficient than the historical index; if the new discrimination is greater than the preset discrimination threshold, it means that the verification of the main table data ratio has failed, and then the index discrimination verification result is directly determined to be verification failure. For example, the preset discrimination threshold is 0.03, and the new discrimination is CN1 / CT. At this time, if (CN1 / CT)<=0.03, then further verification is conducted to see whether the new index is more efficient than the historical index.

[0126] In step S520 of some embodiments, if the numerical comparison result indicates that the new discrimination is less than or equal to the preset discrimination threshold, the new discrimination and the candidate discrimination are calculated to obtain a discrimination ratio. For example, the new discrimination is CN1 / CT, and the candidate discrimination is CN2 / CT. In this case, the discrimination ratio obtained by calculating the quotient of the new discrimination and the candidate discrimination is (CN1 / CT) / (CN2 / CT)=CN1 / CN2.

[0127] In step S530 of some embodiments, if the discrimination ratio is less than or equal to the preset ratio threshold, the index discrimination check result is determined to be passed; if the discrimination ratio is greater than the preset ratio threshold, the index discrimination check result is determined to be failed.

[0128] In step S440 of some embodiments, the index verification result is further determined based on the index usage status, the index storage status, and the index discrimination verification result. If at least one of the index usage status, the index storage status, and the index discrimination verification result indicates that the newly created field index verification fails, the index verification result is determined to be the verification index establishment verification fails. In other words, the index usage status, the index storage status, and the index discrimination verification result all need to indicate that the newly created field index verification passes before the index verification result can be determined to be the verification index establishment verification passes.

[0129] In the above embodiments, the present application can verify the newly created field index from multiple aspects, such as the verification of whether the newly created field index mentioned in the above embodiments is used for execution, the verification of whether the newly created field index already exists, the comparison between the new discrimination degree of the newly created field index and the candidate discrimination degree, and can also include the verification of the main table data ratio, etc. In other words, the present application can select at least two verification methods according to actual needs, and if at least one of the selected verification methods fails to pass the verification, it means that the newly created field index does not meet the requirements for index field establishment. In this way, the accuracy and flexibility of the newly created index can be improved, so that a database table field index that is more suitable for the current query request can be automatically established to optimize the slow query problem during data query.

[0130] See also Figure 6 , Figure 6 It is a specific flow chart of step S440 provided in an embodiment of the present application. In some embodiments of the present application, step S440 may specifically include but is not limited to steps S610 to S640.

[0131] Step S610, if the index usage status indicates that the newly created field index is not an index used in the statement execution plan information, it is determined that the newly created field index meets the index usage condition when the index is created;

[0132] Step S620: If the index storage status indicates that the newly created field index is not an index stored in the historical index library, it is determined that the newly created field index meets the index storage condition when the index is created;

[0133] Step S630, if the index discrimination check result indicates that the discrimination ratio is less than or equal to the preset ratio threshold, it is determined that the newly created field index meets the discrimination condition when the index is created;

[0134] Step S640: If the newly created field index meets the index use condition, index storage condition and discrimination condition when the index is created, it is determined that the index verification result is passed.

[0135] In some embodiments, in steps S610 to S640, if it is determined that the newly created field index meets the index usage conditions when the index is created, meets the index storage conditions when the index is created, and meets the discrimination conditions when the index is created, it can be determined that the index verification of the newly created field index from multiple aspects has passed, so that the index verification result can be determined as the index creation verification passed. In this way, a database table field index that is more suitable for the current query request can be automatically and more accurately established to optimize the slow query problem during data query, thereby effectively improving data query efficiency.

[0136] In step S170 of some embodiments, if the index verification result is that the index establishment verification has passed, the system will perform the actual index establishment operation based on the newly created field index. For example, a specific SQL statement can be used to establish the index. If the newly created field index is a single-column index, the corresponding SQL statement for establishing the field index can be "CREATE INDEXidx_tablename_column_name ON Table(column_name);". If the newly created field index is a combined index, the corresponding SQL statement for establishing the field index can be "CREATE INDEX idx_tablename_column_name1_column_name2ON Table(column_name1,column_name2);", and the combined index field depends on the combined field.

[0137] It should be noted that the non-Company software tools or components appearing in the embodiments of the present application are merely examples and do not represent actual use.

[0138] The method for establishing a database table field index provided in the embodiment of the present application can scan the slow log of the database at a fixed time, save it in the slow SQL list, scan the slow SQL list one by one, split the target query statement, and obtain the key data of the statement; further, through the key data of the statement, obtain the data volume and index information of the main and secondary tables (old indexes that already exist); obtain the table screening condition field and the association condition field through the key data of the statement, obtain the combined field according to the combination formula, judge the index discrimination of the combined field, and obtain the optimal index field. Further, the newly created field index is index-checked through the statement execution plan information, the historical index library, the new discrimination of the newly created field index and the candidate discrimination to determine whether to establish the index. Based on this, the present application can significantly reduce the database response time, improve the overall operating speed and efficiency of the system, and thus improve the system performance. Resource optimization is achieved by creating a new index that meets the requirements, avoiding unnecessary system resource consumption, such as CPU, memory and I / O, so that resources can be more reasonably allocated and utilized. In this way, problems such as system freezes, crashes, or deadlocks caused by slow SQL can be reduced, the time and effort invested in manually troubleshooting and optimizing slow SQL can be reduced, the stability and reliability of the system can be enhanced, and the workload and maintenance costs of operation and maintenance personnel can be reduced. At the same time, by automatically identifying and recording relevant information about slow SQL, a data foundation is provided for subsequent problem tracing, performance analysis, and optimization strategy adjustment, ensuring the accuracy and integrity of the data.

[0139] See also Figure 7 The embodiment of the present application also provides a device for establishing a database table field index, which can implement the above-mentioned method for establishing a database table field index, and the device includes:

[0140] An acquisition module 710 is used to acquire a target query statement and statement execution plan information of the target query statement, where the target query statement is a statement that meets a preset slow query statement condition;

[0141] A splitting module 720 is used to split the target query statement to obtain statement key data, where the statement key data is used to indicate the field selection condition of the candidate database table associated with the target query statement;

[0142] A first determination module 730 is used to determine the total number of rows of the candidate database table based on the historical index library;

[0143] An extraction module 740 is used to extract indexes from candidate database tables based on statement execution plan information to obtain index usage data, where the index usage data includes candidate column indexes used when executing a target query statement;

[0144] A second determination module 750 is used to determine a new field index from the candidate column index based on the candidate discrimination of the candidate column index and the statement key data, wherein the candidate discrimination is determined based on the ratio of the number of different field values ​​in the candidate column index to the total number of rows in the table;

[0145] An index verification module 760 is used to perform index verification on the newly created field index based on the statement execution plan information, the historical index library, the new discrimination degree and the candidate discrimination degree of the newly created field index, and obtain an index verification result, wherein the index verification result includes that the index establishment verification is passed, and the index establishment verification is passed, indicating that the newly created field index meets the conditions for index establishment;

[0146] The index building module 770 is used to build a field index based on the newly created field index if the index verification result is that the index building verification has passed.

[0147] The specific implementation of the database table field index establishment device in the embodiment of the present application is basically the same as the specific implementation of the database table field index establishment method described above, and will not be repeated here.

[0148] The embodiment of the present application also provides an electronic device, the electronic device includes a memory and a processor, the memory stores a computer program, and the processor implements the above database table field index establishment method when executing the computer program. The electronic device can be any smart terminal including a tablet computer, a car computer, etc.

[0149] See also Figure 8 , Figure 8 The hardware structure of an electronic device of another embodiment is illustrated, and the electronic device includes:

[0150] The processor 810 may be implemented by a general-purpose central processing unit (CPU), a microprocessor, an application-specific integrated circuit (ASIC), or one or more integrated circuits, and is used to execute relevant programs to implement the technical solutions provided in the embodiments of the present application;

[0151] The memory 820 can be implemented in the form of a read-only memory (ROM), a static storage device, a dynamic storage device, or a random access memory (RAM). The memory 820 can store an operating system and other application programs. When the technical solution provided in the embodiment of this specification is implemented by software or firmware, the relevant program code is stored in the memory 820, and the processor 810 calls and executes the database table field index establishment method of the embodiment of this application;

[0152] Input / output interface 830, used to implement information input and output;

[0153] The communication interface 840 is used to realize the communication interaction between the device and other devices. The communication can be realized through a wired manner (such as USB, network cable, etc.) or a wireless manner (such as mobile network, WIFI, Bluetooth, etc.);

[0154] bus 850 , which transmits information between the various components of the device (e.g., processor 810 , memory 820 , input / output interface 830 , and communication interface 840 );

[0155] The processor 810 , the memory 820 , the input / output interface 830 and the communication interface 840 are connected to each other in communication within the device via a bus 850 .

[0156] An embodiment of the present application further provides a computer-readable storage medium, which stores a computer program. When the computer program is executed by a processor, the above-mentioned method for establishing a database table field index is implemented.

[0157] The memory, as a non-transient computer-readable storage medium, can be used to store non-transient software programs and non-transient computer executable programs. In addition, the memory may include a high-speed random access memory, and may also include a non-transient memory, such as at least one disk storage device, a flash memory device, or other non-transient solid-state storage device. In some embodiments, the memory may optionally include a memory remotely disposed relative to the processor, and these remote memories may be connected to the processor via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0158] The embodiments described in the embodiments of the present application are intended to more clearly illustrate the technical solutions of the embodiments of the present application and do not constitute a limitation on the technical solutions provided in the embodiments of the present application. Those skilled in the art will appreciate that with the evolution of technology and the emergence of new application scenarios, the technical solutions provided in the embodiments of the present application are also applicable to similar technical problems.

[0159] Those skilled in the art will appreciate that the technical solutions shown in the figures do not constitute a limitation on the embodiments of the present application, and may include more or fewer steps than shown in the figures, or a combination of certain steps, or different steps.

[0160] The device embodiments described above are merely illustrative, and the units described as separate components may or may not be physically separated, that is, they may be located in one place or distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0161] Those skilled in the art will appreciate that all or some of the steps in the methods disclosed above, and the functional modules / units in the systems and devices may be implemented as software, firmware, hardware, or a suitable combination thereof.

[0162] The terms "first", "second", "third", "fourth", etc. (if any) in the specification of the present application and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchangeable where appropriate, so that the embodiments of the present application described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device comprising a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0163] It should be understood that in the present application, "at least one (item)" means one or more, and "plurality" means two or more. "And / or" is used to describe the association relationship of associated objects, indicating that three relationships may exist. For example, "A and / or B" can mean: only A exists, only B exists, and A and B exist at the same time, where A and B can be singular or plural. The character " / " generally indicates that the objects associated before and after are in an "or" relationship. "At least one of the following" or similar expressions refers to any combination of these items, including any combination of single or plural items. For example, at least one of a, b or c can mean: a, b, c, "a and b", "a and c", "b and c", or "a and b and c", where a, b, c can be single or multiple.

[0164] In the several embodiments provided in the present application, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are only schematic. For example, the division of the above units is only a logical function division. There may be other division methods in actual implementation, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of devices or units, which can be electrical, mechanical or other forms.

[0165] The units described above as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0166] In addition, each functional unit in each embodiment of the present application may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.

[0167] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art, or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including multiple instructions to enable a computer device (which can be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods of various embodiments of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (Read-Only Memory, referred to as ROM), random access memory (Random Access Memory, referred to as RAM), disk or optical disk and other media that can store programs.

[0168] The preferred embodiments of the present invention are described above with reference to the accompanying drawings, but the scope of the rights of the present invention is not limited thereto. Any modification, equivalent substitution and improvement made by a person skilled in the art without departing from the scope and essence of the present invention should be within the scope of the rights of the present invention.

Claims

1. A method for establishing a database table field index, characterized in that: The method comprises: Obtaining a target query statement and statement execution plan information of the target query statement, wherein the target query statement is a statement that meets a preset slow query statement condition; Perform sentence splitting on the target query sentence to obtain sentence key data, where the sentence key data is used to indicate a field selection condition of a candidate database table associated with the target query sentence; Determine the total number of rows in the candidate database table based on the historical index library; Extracting indexes from the candidate database table based on the statement execution plan information to obtain index usage data, wherein the index usage data includes candidate column indexes used when the target query statement is executed; Determine a new field index from the candidate column index based on a candidate discrimination degree of the candidate column index and the statement key data, wherein the candidate discrimination degree is determined based on a ratio of the number of different field values ​​in the candidate column index to the total number of rows in the table; Performing index verification on the newly created field index based on the statement execution plan information, the historical index library, the new discrimination of the newly created field index, and the candidate discrimination, to obtain an index verification result, wherein the index verification result includes a pass in the index establishment verification, and the pass in the index establishment verification indicates that the newly created field index meets the conditions for index establishment; If the index verification result is that the index establishment verification is passed, a field index is established based on the newly created field index.

2. The method according to claim 1, characterized in that The determining a new field index from the candidate column index based on the candidate discrimination of the candidate column index and the statement key data includes: If the candidate column index is a single column index, determining the candidate column index as the newly created field index; If the candidate column index is a composite index, the total number of index columns of the candidate column index is determined based on the statement key data, and a new field index is determined from the candidate column index based on the total number of index columns.

3. The method according to claim 2, characterized in that The determining a new field index from the candidate column indexes based on the total number of index columns includes: Performing index combinations on the candidate column indexes based on the total number of index columns and a preset number of combinations to obtain a candidate combination field index; Calculating the discrimination of the candidate combined field index to obtain the discrimination of the combined index; The newly created field index is determined from the candidate combined field indexes based on the combined index discrimination degree.

4. The method according to claim 3, characterized in that The determining the newly created field index from the candidate combined field indexes based on the combined index discrimination degree includes: Determine a field index weight value of the candidate combined field index based on the statement key data; Update the combined index discrimination based on the field index weight value to obtain an updated index discrimination; The newly created field index is determined from the candidate combined field indexes based on the updated index discrimination.

5. The method according to any one of claims 1 to 4, characterized in that: The index verification of the newly created field index based on the statement execution plan information, the historical index library, the new discrimination of the newly created field index, and the candidate discrimination to obtain an index verification result includes: Performing index usage verification on the newly created field index based on the statement execution plan information to determine the index usage status of the newly created field index; Performing index storage verification on the newly created field index based on the historical index library to determine the index storage status of the newly created field index; Performing index discrimination check on the newly created field index based on the new discrimination of the newly created field index and the candidate discrimination, and determining an index discrimination check result; The index verification result is determined based on the index usage status, the index storage status and the index discrimination verification result.

6. The method according to claim 5, characterized in that The performing index discrimination check on the newly created field index based on the new discrimination of the newly created field index and the candidate discrimination to determine the index discrimination check result includes: Comparing the new discrimination degree with the preset discrimination degree threshold value to obtain a numerical comparison result; If the numerical comparison result indicates that the new discrimination is less than or equal to the preset discrimination threshold, the quotient of the new discrimination and the candidate discrimination is calculated to obtain a discrimination ratio; The index discrimination check result is determined based on a numerical comparison result of the discrimination ratio and a preset ratio threshold.

7. The method according to claim 5, characterized in that The determining the index verification result based on the index usage status, the index storage status and the index discrimination verification result includes: If the index usage status indicates that the newly created field index is not an index used in the statement execution plan information, determining that the newly created field index meets the index usage condition when the index is created; If the index storage status indicates that the newly created field index is not an index stored in the historical index library, determining that the newly created field index meets the index storage condition when the index is created; If the index discrimination check result indicates that the discrimination ratio is less than or equal to the preset ratio threshold, it is determined that the newly created field index meets the discrimination condition when the index is established; If the newly created field index meets the index use condition, the index storage condition and the discrimination condition when the index is established, it is determined that the index verification result is passed.

8. A device for establishing a database table field index, characterized in that: The device comprises: An acquisition module, used to acquire a target query statement and statement execution plan information of the target query statement, wherein the target query statement is a statement that meets a preset slow query statement condition; A splitting module, used to split the target query statement to obtain statement key data, where the statement key data is used to indicate a field selection condition of a candidate database table associated with the target query statement; A first determination module, configured to determine the total number of rows of the candidate database table based on a historical index library; An extraction module, configured to extract indexes from the candidate database table based on the statement execution plan information to obtain index usage data, wherein the index usage data includes candidate column indexes used when the target query statement is executed; A second determination module is used to determine a new field index from the candidate column index based on a candidate discrimination degree of the candidate column index and the statement key data, wherein the candidate discrimination degree is determined based on a ratio of the number of different field values ​​in the candidate column index to the total number of rows in the table; An index verification module is used to perform index verification on the newly created field index based on the statement execution plan information, the historical index library, the new discrimination of the newly created field index, and the candidate discrimination, to obtain an index verification result, wherein the index verification result includes a pass in the index establishment verification, and the pass in the index establishment verification indicates that the newly created field index meets the conditions for index establishment; An index building module is used to build a field index based on the newly created field index if the index verification result is that the index building verification has passed.

9. An electronic device, characterized in that: The electronic device comprises a memory and a processor, the memory stores a computer program, and the processor implements the method according to any one of claims 1 to 7 when executing the computer program.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the method according to any one of claims 1 to 7 is implemented.

Images