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

Abstract

The embodiment of the present application provides a method and device for establishing a database table field index, an electronic device, and a storage medium, which belongs to the field of financial technology. The method includes: obtaining a target query statement and statement execution plan information; splitting the target query statement to obtain statement key data; determining the total number of rows of the candidate database table based on the historical index library; extracting index usage data from the candidate database table based on the statement execution plan information; determining a new field index from the candidate column index based on the candidate discrimination of the candidate column index; verifying the new field index based on the statement execution plan information, the historical index library, the new discrimination of the new field index, and the candidate discrimination to obtain an index verification result; if the index verification result is that the index establishment verification passes, the field index is established based on the newly created field index. The embodiment of the present application can automatically establish a database table field index that is more suitable for the current query request to improve data query efficiency.

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 allows for quick access to specific information in a database table. With the continuous development of internet technology, users' daily internet activities generate massive amounts of data. For example, in the insurance sales field of FinTech, due to the large number of policies sold daily and the extensive information required for each policy, when sales personnel need to quickly find specific information for a specific policy, they need to use an index to quickly retrieve the corresponding data.

[0003] Currently, related technologies generally use the following methods to index database table fields: one is to index all database table fields, but this approach creates too many indexes that are relatively isolated, often with too many unnecessary indexes, which takes up a large amount of disk space and easily leads to slow queries during queries; the second is to not create any indexes before the system goes online, and then manually troubleshoot slow queries after they occur, and then create corresponding indexes. This approach 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, even affecting the processing of other business requests. Based on this, the methods proposed in related technologies to optimize slow database query problems rely too much on manual operations, which easily wastes database resources, and the large amount of data processing makes data query efficiency low. Therefore, how to propose a method that can automatically create database table field indexes that are more suitable for the current query request to optimize slow query problems during data query, thereby effectively improving data query efficiency, 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 objectives, a first aspect of an embodiment of the present application provides 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] Splitting 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;

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

[0009] Performing index extraction on 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 executing the target query statement;

[0010] Determining 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, the index verification result including a "pass" of the index establishment verification, which 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, the 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 candidate combination field indexes;

[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.

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

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

[0022] Updating 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, 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 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, and determining the index discrimination check result, includes:

[0030] Comparing the new discrimination value with a preset discrimination value threshold 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, calculating the quotient of the new discrimination and the candidate discrimination 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, 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 conditions when the index was 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 conditions when the index was 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 created;

[0037] If the newly created field index meets the index use condition, the index storage condition, and the discrimination condition when the index is created, the index verification result is determined to be the index creation verification passed.

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

[0039] An acquisition module, configured 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, configured to perform sentence splitting on the target query statement to obtain sentence key data, wherein the sentence key data is used to indicate a field selection condition of a candidate database table associated with the target query statement;

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

[0042] An extraction module, configured to perform index extraction on 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 executing the target query statement;

[0043] a second determining module, configured 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, configured 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, and obtain an index verification result, wherein the index verification result includes a "pass" of the index establishment verification, which indicates that the newly created field index meets the conditions for index establishment;

[0045] The index establishment module is configured to establish a field index based on the newly created field index if the index verification result indicates that the index establishment verification has passed.

[0046] To achieve the above-mentioned purpose, the 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 the processor implements the method described in the first aspect when executing the computer program.

[0047] To achieve the above-mentioned purpose, the fourth aspect of the embodiments of the present application proposes a computer-readable storage medium, which stores a computer program. When the computer program is executed by a processor, it implements the method described in the first aspect.

[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 in the candidate database table is determined based on the historical index library; the candidate database table is indexed and extracted 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, and 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; 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 verification result, and the index verification result includes an index establishment verification pass, and the index establishment verification pass indicates that the new field index meets the conditions for index establishment; if the index verification result is an index establishment verification 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 This is a flow chart of a method for establishing a database table field index provided by 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 This is a schematic diagram of the structure of a device for establishing a database table field index provided by an embodiment of the present application;

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

[0057] In order to make the purpose, technical solutions and advantages of this application more clear, the following further describes this application in detail with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain this application and are not intended to limit this application.

[0058] It should be noted that although the device schematics illustrate functional module divisions and the flowcharts illustrate logical sequences, in certain circumstances, the steps shown or described may be performed in a sequence that differs from the module divisions in the device or the sequence in the flowcharts. The terms "first," "second," and so on, in the specification, claims, and drawings, are used to distinguish similar items and are not necessarily used to describe a specific sequence or precedence.

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

[0060] First, let’s analyze some of the terms used in this application:

[0061] Artificial Intelligence (AI) is a new technical discipline that studies and develops theories, methods, technologies, and application systems for simulating, extending, and expanding human intelligence. A branch of computer science, AI seeks to understand the essence of intelligence and create new intelligent machines that can respond in a manner similar to human intelligence. Research in this field includes robotics, speech recognition, image recognition, natural language processing, and expert systems. AI can simulate the information processes of human consciousness and thinking. It also encompasses theories, methods, technologies, and application systems that use digital computers or digital computer-controlled machines to simulate, extend, and expand human intelligence, perceive the environment, acquire knowledge, and use that knowledge to achieve optimal results.

[0062] Indexes can be created based on one or more fields. Indexes are data structures used in database management systems to speed up data retrieval. Similar to the table of contents in a book, indexes allow you to quickly locate specific rows in a table.

[0063] A field is a column in a database table that stores a specific type of data. Each field has a name and a data type, such as an integer, string, or date. A field defines a specific attribute for each row in the table. For example, in a table of employee information, fields might include "Employee ID," "Position," and "Job Date."

[0064] An index is a structure that sorts the values ​​of one or more columns in a database table. Using an index allows for quick access to specific information in a database table. With the continuous development of internet technology, users' daily internet activities generate massive amounts of data. For example, in the insurance sales field of FinTech, due to the large number of policies sold daily and the extensive information required for each policy, when sales personnel need to quickly find specific information for a specific policy, they need to use an index to quickly retrieve the corresponding data.

[0065] Among them, 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 querying, joining or sorting, or too many unnecessary indexes are created (that is, indexes are created in the database table that exceed actual needs and have limited improvement in query performance). (2) complex query logic: SQL statements contain complex joins, subqueries, aggregation operations, etc., which require the database to perform a large amount of calculations and data processing. (3) too much data: the amount of data in the table is huge, and 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 lead to slow system response, excessive resource consumption, and even affect the stability of the entire application system and user experience. 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 database response time and 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 improving 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 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 and optimizing slow SQL statements is crucial for ensuring efficient and stable operation of database systems, meeting business needs, controlling costs, and improving user experience.

[0066] Currently, related technologies generally use the following methods to index database table fields: one is to index all database table fields, but this approach creates too many indexes that are relatively isolated, often with too many unnecessary indexes, which takes up a large amount of disk space and easily leads to slow queries during queries; the second is to not create any indexes before the system goes online, and then manually troubleshoot slow queries after they occur, and then create corresponding indexes. This approach 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, even affecting the processing of other business requests. Based on this, the methods proposed in related technologies to optimize slow database query problems rely too much on manual operations, which easily wastes database resources, and the large amount of data processing makes data query efficiency low. Therefore, how to propose a method that can automatically create database table field indexes that are more suitable for the current query request to optimize slow query problems during data query, thereby effectively improving data query efficiency, 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. 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 achieve optimal results.

[0069] Fundamental AI technologies generally include sensors, dedicated AI chips, cloud computing, distributed storage, big data processing, operating / interaction systems, and mechatronics. AI software technologies primarily encompass computer vision, robotics, biometrics, speech processing, natural language processing, 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, and the like. 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, and the like 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 via 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 embodiment of the present application, when it comes to the need to perform relevant processing on data related to the identity or characteristics of the object based on the query or execution status of the database table of the object, the permission or consent of the object will be obtained first, and the collection, use and processing of such 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 consent of the object will be obtained through a pop-up window or by jumping to a confirmation page. After clearly obtaining the separate permission or 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 This is an optional flow chart of the method for establishing a database table field index provided by the 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: split the target query statement to obtain key data of the statement;

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

[0077] Step S140: extract 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 indexes based on the candidate discrimination of the candidate column indexes and the statement 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 and the candidate discrimination of the newly created field index, and obtaining an index verification result;

[0080] Step S170: If the index verification result indicates 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 a 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 and 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 an SQL statement. It describes how the database engine will process and execute the statement. The execution plan information can include a series of operational steps and related information, such as: table access method (such as full table scan, index scan, etc.), join operation type (such as inner join, outer join, etc.), sorting and grouping operations, etc. By reviewing the execution plan, you can further understand the execution efficiency of the statement (determine whether there is unnecessary overhead or inefficient operations), index usage (confirm whether indexes are 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 or every 12 hours) and select the first n (n is a positive integer, such as 20, 10, etc.) and save it to 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 slow SQL statement conditions can include: (1) using the database's own performance testing tools (because most database systems provide performance testing and logging functions, you can view SQL statements with long execution times and their related performance indicators, such as execution time, CPU usage, I / O operations, etc.); (2) using application-side logging (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 testing tools (there are many third-party tools specifically used to test 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 slow SQL can be identified by analyzing the database's slow query log. There are other methods, which are not limited. After identifying slow SQL, you can further analyze its execution plan, index usage, data volume and other factors to determine the specific cause of 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 waste database resources and even affect business processing. By the time 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 developer's SQL optimization capabilities and on-the-spot performance, which increases operation and maintenance costs.

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

[0086] It should be noted that statement key data includes primary table screening conditions, primary table association conditions, secondary table screening conditions, and secondary table association conditions. The primary table refers to the main candidate database table in the target query statement and is usually the core source of the query result set (for example, it can be the first database table after the Select statement). Secondary tables refer to other candidate database tables associated with the primary table and are usually used to provide additional information or details (for example, they can be tables other than the primary table in the Select statement). Primary table screening conditions refer to the conditions for selecting specific records from the primary table (for example, they can be the field selection conditions related to the primary table after the where keyword in the Select statement). Primary table association conditions refer to the conditions for selecting specific records based on the field association relationship between the primary table and its associated tables (for example, they can be the field selection conditions that need to be met when the primary table is associated with other data tables in the Select statement). Secondary table screening conditions refer to the conditions for selecting specific records from the secondary table. Secondary table association conditions refer to the conditions for selecting specific records based on the field association relationship between the secondary table and its associated tables. For example, the target query statement is "Select * from pg_catalog.pg_class a left join pg_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 join information_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'". After splitting the target query statement, it can be found 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 main table a is "a.table_schema = 'public'", and the join conditions of main table a are "a.oid = c.attrelid" and "cl.table_name = a.relname". Therefore, the column indexes used when executing main table a are "a.table_schema", "a.oid" and "a.relname".Similarly, the filter condition for sub-table c is empty, and its join conditions are "a.oid = c.attrelid," "d.objoid = c.attrelid," "d.objsubid = c.attnum," and "cl.column_name = c.attname." Therefore, the column indexes used during the execution of sub-table c are "c.attrelid," "c.attnum," and "c.attname." The filter condition for sub-table d is empty, and its join conditions are "d.objoid = c.attrelid" and "d.objsubid = c.attnum." Therefore, the column indexes used during the execution of sub-table d are "d.objoid = c.attrelid" and "d.objsubid." The screening condition of sub-table cl is "cl.table_schema = 'public'", and the join conditions of sub-table cl are "cl.table_name = a.relname" and "cl.column_name = c.attname". Therefore, the column indexes used when executing sub-table cl are "d.objsubid", "cl.table_name", and "cl.column_name".

[0087] In some embodiments, step S130 further involves obtaining the total number of rows in each candidate database table based on the historical index repository to facilitate subsequent discrimination calculations. For example, the total number of rows in a table can represent the total number of records in the table, denoted as CT, and the query to obtain the rows can be "Select count(*)ct from Table." The historical index repository refers to a structure used to store candidate database tables and historical index lists.

[0088] In some embodiments, step S140 may further scan statement execution plan information to perform index extraction on candidate database tables, thereby obtaining used index information, namely, index usage data. This index usage data includes candidate column indexes used when executing the target query statement. Specific examples of candidate column indexes can be found in the specific examples in step S120. Thus, the index usage dataset obtained in this application may include indexes used by the primary table and indexes used by the secondary tables.

[0089] In some embodiments, step S150 may further determine a new field index from the candidate column indexes based on the candidate discrimination of the candidate column indexes and the statement key data. Specifically, the decision on whether to create a new field index is made by analyzing the candidate discrimination of the candidate column indexes (i.e., the proportion of different field values ​​in the total number of rows) and the statement key data. The field index is designed for candidate uniqueness and query optimization, ensuring improved query efficiency.

[0090] It should be noted that the candidate discriminability is determined based on the ratio of the number of distinct field values ​​in the candidate column index to the total number of rows in the table. Determining index discriminability is a key indicator of index efficiency. Slow SQL is often caused by using an index with low discriminability. Index discriminability is a key metric for measuring index effectiveness in databases. It represents the proportion of unique values ​​in the column index and can also be understood as the index's uniqueness. Higher index discriminability means greater diversity in the values ​​in the index, with relatively few rows sharing the same index value. Such indexes are more efficient for queries. This is because highly discriminable indexes can quickly exclude rows that do not meet the criteria, reducing the database scan range. The formula for calculating index discriminability is: Discriminability = count(distinct_column) / count(*), where count(*) represents the total number of rows in the table. distinct is an SQL keyword used to return unique values. count(distinct_column) removes duplicate rows from the result set, retaining only unique records and counting the total number of unique records, representing the number of distinct field values ​​in the candidate column index. The closer this ratio is to 1, the higher the discrimination of the index is, the greater the value of the index is, and the higher the cost-effectiveness of the index for retrieval.

[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 checked, 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 indexes based on the candidate discrimination of the candidate column indexes 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 specific usernames. 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 frequently 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 in the group with the largest number of rows are selected as the corresponding new field index.

[0096] In some specific embodiments, a composite index, also known as a multi-column index or a joint index, refers to an index created simultaneously on two or more columns of a table. This type of 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 that includes these two columns. If 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 queries like SELECT * FROM table WHERE columnA = value AND columnB = value are frequently 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 index 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 indexes in this application are not select columns, but all the filter columns (i.e., the column indexes involved after the where keyword) and associated columns (the column indexes 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 the where and join on keywords in the target query statement.

[0098] In some embodiments, determining the total number of index columns for a candidate column index based on statement key data may specifically include: extracting filter condition column data and association condition column data from the statement key data; and performing column statistics based on the number of columns in the filter condition column data and the number of columns in the association condition column data to obtain the total number of index columns. The filter condition column data indicates column data corresponding to the primary table filter condition and the secondary table filter condition, and the association condition column data indicates column data corresponding to the primary 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, performing index combination on 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 of the candidate combined field index to obtain the discrimination of the combined index;

[0102] Step S230: Determine 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 a 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 the combination formula can be used. Perform index combinations, 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, or 3 can be used in sequence). In this way, a combination of 3 candidate column indexes can be selected from the 5 candidate column indexes, a combination of 2 candidate column indexes can be selected from the 5 candidate column indexes, a combination of 1 candidate column index can be selected from the 5 candidate column indexes, and so on. In other words, a candidate combination field index can be selected from a maximum of 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 rows in the table.

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

[0106] See also Figure 3 , Figure 3This 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 the field index weight value of the 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: Determine 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 statement key data, 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. Furthermore, 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 field index weight value determined previously. The field combination corresponding to the updated index discrimination is effective 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, this application considers both single-column indexes and combined indexes when creating a new index. The combined index also considers its discriminability when determining the combined index, and can further determine a more accurate combined field index based on the field index weight. This can better automate the index optimization process, reduce the workload of manual analysis, and improve the overall performance of the database.

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

[0113] See also Figure 4 , Figure 4This 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 an 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 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 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 "index stored", i.e., 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 "index not stored", i.e., 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, an index discrimination check can be performed on the newly created field index based on the new discrimination and candidate discrimination of the newly created field index to determine the index discrimination check result. The index discrimination check is used to verify whether the new index is more efficient than the old index. For example, the number of different field values ​​in the column index included 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 execution plan information is generated. The number of different field values ​​in the column index included in the newly created field index 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 This 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 a 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 quotient of the new discrimination and the candidate discrimination is 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 a preset ratio threshold.

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

[0126] In some embodiments, in 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 is calculated as a quotient of the candidate discrimination to obtain a discrimination ratio. For example, if the new discrimination is CN1 / CT and the candidate discrimination is CN2 / CT, 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, an 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 failed, the index verification result is determined to be a verification index establishment verification failure. In other words, the index verification result can only be determined to be a verification index establishment verification pass if the index usage status, the index storage status, and the index discrimination verification result all indicate that the newly created field index verification passed.

[0129] In the above embodiment, the present application can verify the newly created field index from multiple aspects, such as the verification of whether the newly created field index is executed as planned, the verification of whether the newly created field index already exists, the comparison between the new discrimination of the newly created field index and the candidate discrimination, and the verification of the proportion of main table data, 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 This 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 conditions when the index was 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 conditions when the index was 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 was created;

[0134] In step S640 , if the newly created field index meets the index usage condition, index storage condition, and discrimination condition during index creation, the index verification result is determined to be index creation verification 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, index storage conditions, and discrimination conditions during index creation, then it can be determined that the index validation of the newly created field index has passed from multiple aspects, and thus the index validation result can be determined as passing the index creation validation. In this way, a database table field index that is more suitable for the current query request can be more accurately and automatically established to optimize slow query problems 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 composite index, the corresponding SQL statement for establishing the field index can be "CREATE INDEX idx_tablename_column_name1_column_name2 ON Table(column_name1, column_name2);", and the composite index field depends on the composite field.

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

[0138] The database table field index establishment method provided in the embodiment of the present application can scan the database slow log 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 statement key data; further, obtain the main and secondary table data volume and index information (already existing old index) through the statement key data; obtain the table screening condition field and association condition field through the statement key data, obtain the combination field according to the combination formula, perform index discrimination judgment on the combination 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 system performance. Resource optimization is achieved by creating a new index that better meets the requirements, avoiding unnecessary system resource consumption, such as CPU, memory and I / O, so that resources are more reasonably allocated and utilized. This reduces system freezes, crashes, or deadlocks caused by slow SQL queries, reduces the time and effort required to manually troubleshoot and optimize slow SQL queries, enhances system stability and reliability, and reduces the workload and costs of operations and maintenance personnel. Furthermore, by automatically identifying and recording relevant information about slow SQL queries, it provides a data foundation for subsequent problem tracing, performance analysis, and optimization strategy adjustments, ensuring data accuracy and completeness.

[0139] See also Figure 7 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. The device includes:

[0140] An acquisition module 710 is configured 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 conditions of the candidate database table associated with the target query statement;

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

[0143] Extraction module 740, configured 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 the target query statement;

[0144] A second determining module 750 is configured to determine a new field index from the candidate column indexes based on the candidate discrimination of the candidate column indexes 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 indexes to the total number of rows in the table;

[0145] An index verification module 760 is configured to perform index verification on a newly created field index based on statement execution plan information, a historical index library, the new discriminability of the newly created field index, and candidate discriminabilities, and obtain an index verification result. The index verification result includes a "pass" in the index establishment verification, which indicates that the newly created field index meets the conditions for index establishment.

[0146] The index establishment module 770 is configured to establish a field index based on the newly created field index if the index verification result indicates that the index establishment 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 above-mentioned database table field index establishment method, and will not be repeated here.

[0148] The present application also provides an electronic device comprising a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the above-mentioned method for establishing a database table field index. The electronic device can be any smart terminal including a tablet computer, an in-vehicle computer, or the like.

[0149] See also Figure 8 , Figure 8 The hardware structure of an electronic device according to another embodiment is shown. The electronic device includes:

[0150] The processor 810 may be implemented as a general-purpose central processing unit (CPU), a microprocessor, an application-specific integrated circuit (ASIC), or one or more integrated circuits, and is configured 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 solutions provided in the embodiments of this specification are implemented through software or firmware, the relevant program code is stored in the memory 820 and is called by the processor 810 to execute the database table field index establishment method of the embodiments of this application.

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

[0153] Communication interface 840, used to implement communication interaction between this device and other devices, which can be achieved through wired means (such as USB, network cable, etc.) or wireless means (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 storing a computer program, which implements the above-mentioned method for establishing a database table field index when executed by a processor.

[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 arranged 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 this application are intended to more clearly illustrate the technical solutions of the embodiments of this application and do not constitute a limitation on the technical solutions provided by the embodiments of this 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 this application are also applicable to similar technical problems.

[0159] Those skilled in the art will understand 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. The units described as separate components may or may not be physically separate, i.e., they may be located in one place or distributed across multiple network units. Some or all of the modules may be selected based on actual needs to achieve the objectives of this embodiment.

[0161] Those skilled in the art will appreciate that all or some of the steps in the methods, systems, and functional modules / units in the devices disclosed above may be implemented as software, firmware, hardware, or appropriate combinations 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 sequential order. 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 variations thereof are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device that includes 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 this 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 previous and next associated objects are in an "or" relationship. "At least one of the following items" or similar expressions refers to any combination of these items, including any combination of single items 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 this 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 merely schematic. For example, the division of the above-mentioned units is only a logical function division. In actual implementation, there may be other division methods, 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 separate, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed across multiple network units. Some or all of these units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0166] In addition, the functional units in the various embodiments of the present application may be integrated into a single processing unit, or each unit may exist physically separately, or two or more units may be integrated into a single unit. The aforementioned integrated units may be implemented in the form of hardware or 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, which is stored in a storage medium and includes multiple instructions for enabling 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: various media that can store programs, such as a USB flash drive, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk.

[0168] The preferred embodiments of the present invention are described above with reference to the accompanying drawings, but are not intended to limit the scope of the present invention. Any modifications, equivalent substitutions, and improvements made by those skilled in the art without departing from the scope and essence of the present invention should be within the scope 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; Splitting 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; Determine the total number of rows in the candidate database table based on the historical index library; Performing index extraction on 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 executing the target query statement; 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, 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; determining the 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 new field index; if the candidate column index is a combined index, determining the total number of index columns of the candidate column index based on the statement key data, and determining the new field index from the candidate column index based on the total number of index columns; 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, the index verification result including a "pass" of the index establishment verification, which 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, the 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 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 candidate combination field indexes; 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.

3. The method according to claim 2, characterized in that The determining the newly created field index from the candidate combined field indexes based on the combined index discrimination includes: Determine a field index weight value of the candidate combined field index based on the statement key data; Updating 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.

4. The method according to any one of claims 1 to 3, characterized in that The 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 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.

5. The method according to claim 4, 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, and determining the index discrimination check result, includes: Comparing the new discrimination value with a preset discrimination value threshold 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, calculating the quotient of the new discrimination and the candidate discrimination 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.

6. 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 conditions when the index was 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 conditions when the index was 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 created; If the newly created field index meets the index use condition, the index storage condition, and the discrimination condition when the index is created, the index verification result is determined to be the index creation verification passed.

7. A device for establishing a database table field index, characterized in that: The device comprises: An acquisition module, configured 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, configured to perform sentence splitting on the target query statement to obtain sentence key data, wherein the sentence key data is used to indicate a field selection condition of a candidate database table associated with the target query statement; A first determining module, configured to determine the total number of rows in the candidate database table based on a historical index library; An extraction module, configured to perform index extraction on 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 executing the target query statement; A second determination module is configured to determine a newly created 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; the determining of the newly created field index from the candidate column index based on the candidate discrimination of the candidate column index and the statement key data comprises: 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 combined index, determining the total number of index columns of the candidate column index based on the statement key data, and determining the newly created field index from the candidate column index based on the total number of index columns; an index verification module, configured 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, and obtain an index verification result, wherein the index verification result includes a "pass" of the index establishment verification, which indicates that the newly created field index meets the conditions for index establishment; The index establishment module is configured to establish a field index based on the newly created field index if the index verification result indicates that the index establishment verification has passed.

8. An electronic device, characterized in that: The electronic device includes 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 6 when executing the computer program.

9. 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 6 is implemented.

Images