Index creation methods, computing devices, storage media, and products

Abstract

This application provides an index creation method, computing device, storage medium, and product. The index creation method includes: obtaining column access information, whereby the target column access information is obtained from at least one structured query statement; determining at least one candidate data column based on the column access information; obtaining the database table corresponding to each candidate data column, and obtaining the row information and column information of the database table corresponding to each candidate data column; determining the index creation weight corresponding to each candidate data column based on the row information and column information of the database table corresponding to each candidate data column; and determining at least one target data column based on the index creation weight corresponding to each candidate data column, and creating an index for the database table corresponding to each target data column. This solves database performance problems caused by delayed or missed index creation, requires no manual resources, and can automatically identify frequently accessed data columns that trigger full table scans due to missing indexes.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to an index creation method. This application also relates to a computing device, a computer-readable storage medium, and a computer program product. Background Technology

[0002] Databases are used to organize, store, and manage data, while Structured Query Language (SQL) is a standard language for managing and manipulating databases. SQL allows for data definition, querying, manipulation, and control. To accelerate SQL queries, indexes can be set up. Indexes are used to sort the values ​​of columns in a database table and create a directory, enabling quick access to specific data within the table.

[0003] Typically, indexes require creation and maintenance by technical personnel. Creating an index necessitates a certain level of understanding of the business scenario and database-related knowledge. However, due to frequent business updates and the possibility of insufficient or overlooked knowledge of database principles among technical personnel, indexes that should have been created may be missed, leading to full table scans and inefficient SQL execution. Summary of the Invention

[0004] In view of this, embodiments of this application provide an index creation method. This application also relates to a computing device, a computer-readable storage medium, and a computer program product, to address the aforementioned problems in the prior art.

[0005] According to a first aspect of the embodiments of this application, an index creation method is provided, including:

[0006] Obtain column access information, wherein the target column access information is obtained by at least one structured query statement;

[0007] Based on the column access information, at least one candidate data column is determined;

[0008] Retrieve the database table corresponding to each candidate data column, and retrieve the row information and column information of the database table corresponding to each candidate data column.

[0009] Based on the row information of the database table corresponding to each candidate data column and the column information of each candidate data column, determine the index creation weight corresponding to each candidate data column;

[0010] Weights are created based on the indexes corresponding to each candidate data column, at least one target data column is determined, and an index is created for the database table corresponding to each target data column.

[0011] According to a second aspect of the embodiments of this application, a computing device is provided, comprising:

[0012] Memory and processor;

[0013] The memory is used to store computer programs / instructions, and the processor is used to execute the computer programs / instructions, which, when executed by the processor, implement the steps of the above-described index creation method.

[0014] According to a third aspect of the embodiments of this application, a computer-readable storage medium is provided that stores a computer program / instructions that, when executed by a processor, implement the steps of the above-described index creation method.

[0015] According to a fourth aspect of the embodiments of this application, a computer program product is provided, including a computer program / instructions that, when executed by a processor, implement the steps of the above-described index creation method.

[0016] The index creation method provided in this application can obtain column access information, wherein the target column access information is obtained by at least one structured query statement; determine at least one candidate data column based on the column access information; obtain the database table corresponding to each candidate data column, and obtain the row information and column information of the database table corresponding to each candidate data column; determine the index creation weight corresponding to each candidate data column based on the row information and column information of the database table corresponding to each candidate data column; determine at least one target data column based on the index creation weight corresponding to each candidate data column, and create an index for the database table corresponding to each target data column.

[0017] This application first determines candidate data columns based on column access information, and then determines the index creation weight of the candidate data columns based on the row information of the corresponding database table and the column information of the candidate data columns. By addressing both column access information and index creation weight, it can solve database performance problems caused by delayed or missed index creation. It requires no manual resources and can automatically identify frequently accessed data columns that trigger full table scans due to missing indexes, improving SQL execution efficiency. Furthermore, while automating index creation, it also improves the convenience, efficiency, and reliability of index creation. Attached Figure Description

[0018] Figure 1 This is a schematic diagram of the execution flow of an index creation method provided in an embodiment of this application;

[0019] Figure 2 This is a flowchart of an index creation method provided in an embodiment of this application;

[0020] Figure 3 This is a schematic diagram of a candidate data column provided in an embodiment of this application;

[0021] Figure 4 This is a schematic diagram of a process for determining a target data column according to an embodiment of this application;

[0022] Figure 5 This is a schematic diagram of an index creation process provided in an embodiment of this application;

[0023] Figure 6 This is a flowchart of an index management method provided in one embodiment of this application;

[0024] Figure 7 This is a schematic diagram of the structure of an index creation device provided in one embodiment of this application;

[0025] Figure 8 This is a structural block diagram of a computing device provided in one embodiment of this application. Detailed Implementation

[0026] Many specific details are set forth in the following description to provide a full understanding of this application. However, this application can be implemented in many other ways different from those described herein, and those skilled in the art can make similar extensions without departing from the spirit of this application; therefore, this application is not limited to the specific embodiments disclosed below.

[0027] The terminology used in one or more embodiments of this application is for the purpose of describing particular embodiments only and is not intended to limit the scope of one or more embodiments of this application. The singular forms “a,” “the,” and “the” used in one or more embodiments of this application and in the appended claims are also intended to include the plural forms unless the context clearly indicates otherwise. It should also be understood that the term “and / or” used in one or more embodiments of this application refers to and includes any or all possible combinations of one or more associated listed items.

[0028] It should be understood that although the terms first, second, etc., may be used to describe various information in one or more embodiments of this application, such information should not be limited to these terms. These terms are only used to distinguish information of the same type from one another. For example, first may also be referred to as second without departing from the scope of one or more embodiments of this application, and similarly, second may also be referred to as first. Depending on the context, the word "if" as used herein may be interpreted as "when," "when," or "in response to a determination."

[0029] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, use and processing of the relevant data must comply with the relevant laws, regulations and standards of the relevant regions, and corresponding operation entry points are provided for users to choose to authorize or refuse.

[0030] First, the terms and concepts used in one or more embodiments of this specification will be explained.

[0031] Structured Query Language (SQL) is a standard language for managing and manipulating databases. It is a high-level, non-procedural programming language that allows users to work on high-level data structures. It does not require users to specify how data is stored, nor does it require users to understand the specific data storage methods. Therefore, different database systems with completely different underlying structures can use the same Structured Query Language as an interface for data input and management. Structured Query Language statements can be nested, giving it great flexibility and powerful functionality.

[0032] Relational databases are database systems that organize data based on the relational model. They use tables to store and organize data, each table consisting of rows and columns. They support SQL for data manipulation and adhere to ACID transaction principles and normalization theory to optimize data. Common types of relational databases include Oracle, MySQL, and Microsoft SQL Server.

[0033] In the application development lifecycle of relational databases, SQL auditing is a critical step in ensuring the performance of online business applications. However, in actual operation, frequent business changes, insufficient understanding of database principles by developers, or oversights in the auditing process often lead to the omission of indexes that should have been created. Furthermore, when the amount of data in a database table grows to a certain scale, or when the data distribution changes, the lack of indexes can trigger a large number of full table scans, severely consuming the database's Central Processing Unit (CPU) and I / O resources. This can cause a sharp increase in SQL response time, thereby affecting business stability and user experience.

[0034] Typically, technical personnel, such as database administrators, can identify missing indexes through regular inspections, analysis of slow query logs, or the use of monitoring tools. However, these methods heavily rely on the individual experience of the technical personnel and are unlikely to cover all potential performance bottlenecks. Alternatively, database indexing suggestions can be managed using database indexing suggestions developed by service providers, but these suggestions are often tied to specific databases, lack versatility, and tend to be conservative, failing to achieve automation and intelligent management.

[0035] Based on the foregoing, this specification provides a method for creating an index. For example... Figure 1 As shown, Figure 1 This diagram illustrates the execution flow of an index creation method according to an embodiment of this specification. The execution flow of this index creation method can be implemented by three units: a data acquisition unit, a data analysis unit, and an index execution unit. The data acquisition unit collects raw data, which can be data related to database tables. This raw data includes SQL statements, column access information, row information and column information corresponding to the database tables, and may also include database table access information, the correspondence between database tables and SQL statements, etc. This specification does not limit the specific content of the raw data, as long as it is data related to database tables. The data analysis unit determines candidate data columns based on the raw data, and then determines the index creation weights corresponding to the candidate data columns. This allows the determination of the data columns for which indexes need to be created, i.e., the target data columns. The index execution unit creates indexes for the data columns for which indexes need to be created, i.e., creates indexes for the target data columns.

[0036] The index creation methods provided in this manual can resolve database performance issues caused by delayed or omitted index creation. Specifically, without consuming manual resources, it can automatically identify frequently accessed data columns that trigger full table scans due to missing indexes. It determines whether to create indexes for candidate data columns based on column access information and index creation weights. If indexes are needed for candidate data columns, it can also determine the type of index to create (e.g., single-column index or composite index). This automated index creation can complete the process before database performance problems become apparent, improving overall database efficiency and stability, and enhancing the user experience.

[0037] This application provides an index creation method, and also relates to an index creation apparatus, a computing device, a computer-readable storage medium, and a computer program product, which will be described in detail in the following embodiments.

[0038] like Figure 2 As shown, Figure 2 A flowchart of an index creation method provided in one embodiment of this specification includes the following steps:

[0039] Step 202: Obtain column access information, wherein the column access information is obtained by at least one structured query statement.

[0040] The index creation method provided in this specification can be applied to scenarios involving creating indexes for any relational database table. The execution entity of this index creation method can be any computing device with computing capabilities, such as a client, server, database system, or platform. The computing device may include at least one relational database, which may contain multiple database tables. Each database table may include at least one data row and at least one data column. In one or more embodiments of this specification, the computing device can automatically create indexes for database tables in a relational database.

[0041] It should be noted that, regardless of whether a database table has a primary key, the index creation method provided in this manual can be used to create indexes for data columns in the database table.

[0042] In this specification, a computing device can obtain column access information, which is obtained by at least one SQL statement. In one or more embodiments of this specification, column access information can be understood as the access information of SQL statements to columns in a database table. This column access information may include index information corresponding to the data column, the column access frequency corresponding to the data column, and of course, the column access information may also include the attribute value corresponding to the data column, the database table corresponding to the data column (i.e., the database table where the data column is located), etc. This specification does not specifically limit the data information that may be included in the column access information.

[0043] In one or more embodiments of this specification, obtaining column access information includes:

[0044] Obtain at least one structured query statement and the execution frequency of each structured query statement;

[0045] A structured query statement to be processed is determined, and if the execution frequency of the structured query statement to be processed is greater than a first preset frequency threshold, the structured query statement to be processed is taken as the target structured query statement, wherein the structured query statement to be processed is any one of the structured query statements;

[0046] Based on the structured query statements for each target, obtain column access information.

[0047] In one or more embodiments of this specification, a computing device can obtain multiple SQL statements that access a specific relational database and can obtain the execution frequency of each SQL statement, which can be understood as the number of times the SQL statement is executed.

[0048] When creating an index for a data column in a database table, the execution frequency of the SQL statement should be considered. Generally, a higher SQL execution frequency indicates a higher frequency of access to the corresponding database table and data column. Therefore, when creating an index, the access frequency of the data column should be considered. In the initial stage of index creation, the SQL execution frequency can be used to initially filter the SQL statements and remove those that are executed less frequently.

[0049] Therefore, in one or more embodiments of this specification, after obtaining the execution frequencies corresponding to multiple SQL statements, the SQL statements with execution frequencies greater than the first preset frequency threshold can be used as target SQL statements, thereby obtaining column access information based on the target SQL statements.

[0050] In one or more embodiments of this specification, the execution frequency of each SQL statement within a preset time period can also be detected. When the execution frequency of the SQL statement within the preset time period is greater than a third preset frequency threshold, the SQL statement is determined to be the target SQL statement.

[0051] It should be noted that multiple SQL statements can be either already executed SQL or SQL statements to be executed. Furthermore, the first preset frequency threshold and the third frequency threshold can be the same or different, depending on actual needs. This manual does not impose specific limitations on this. For example, the first and third frequency thresholds could be 100 times per day, 100 times per hour, etc. Only when the execution frequency of the corresponding SQL exceeds this frequency threshold can it be used as the target SQL to determine column access information.

[0052] Based on the execution frequency of SQL queries and preset frequency thresholds, SQL queries can be initially filtered, retaining those with relatively high execution frequencies. This helps improve the accuracy and reliability of subsequent index creation.

[0053] In one or more embodiments of this specification, after obtaining at least one target SQL, the target SQL can be parsed to obtain column access information. Specifically, each target SQL can be parsed using an SQL parsing tool to obtain the WHERE and JOIN column information of each target SQL. Then, the corresponding WHERE and JOIN column information is matched with the data dictionary of column access frequency of the record table in the database. The matched columns (which simultaneously satisfy the conditions of being a WHERE column or a JOIN column and being frequently accessed) are included in the subsequent index creation candidates. That is, in subsequent steps, candidate data columns can be determined based on column access information.

[0054] In another embodiment of this specification, the computing device may also obtain a dynamic performance view and statistics table corresponding to the relational database, and directly collect raw data related to SQL execution from the dynamic performance view and statistics table. The raw data may include SQL execution frequency, column access information, etc.

[0055] Obtaining column access information through the above methods ensures the accuracy of the information and provides a solid data foundation for subsequent index creation.

[0056] Step 204: Determine at least one candidate data column based on the column access information.

[0057] In one or more embodiments of this specification, after obtaining column access information, since not all data columns need to be indexed, some data columns with low execution frequency may not need to be indexed. Therefore, data columns can be filtered based on their access frequency to retain data columns with high access frequency, thereby ensuring that the indexes corresponding to the created data columns are frequently used in subsequent processes.

[0058] In practical applications, an index can only be created for a data column if it is determined that no index exists. In one or more embodiments of this specification, the column access information in step 202 above may include the index information corresponding to each data column.

[0059] Based on the column access information, at least one candidate data column is determined, including:

[0060] Determine the data column to be processed, wherein the data column to be processed is any one of the data columns corresponding to the column access information;

[0061] Based on the index information corresponding to the data column to be processed, if it is determined that the data column to be processed does not have an index, the data column to be processed is used as a candidate data column.

[0062] In practical applications, when obtaining column access information, or specifically, the index information corresponding to each data column, it's also possible to retrieve the index information for each data column based on the target database type. For example, if the target database is an Oracle database, you can query views such as `dba_ind_columns` to obtain the index information for each data column, thus determining whether an index has been created for each data column. `dba_ind_columns` is a data dictionary view that can be used to see which data columns are included in which indexes.

[0063] It should be understood that column access information is determined based on multiple SQL statements. These multiple SQL statements access the same target database, but they can access different database tables. The same SQL statement can also access different database tables. Therefore, column access information refers to the access information of multiple data columns determined based on multiple SQL statements.

[0064] As mentioned earlier, data columns can also be filtered based on their access frequency. Therefore, the column access information includes the column access frequency of each data column.

[0065] In one or more embodiments of this specification, when it is determined that the data column to be processed does not have an index, the data column to be processed is used as a candidate data column, including:

[0066] If it is determined that the data column to be processed does not have an index, the data column to be processed will be used as an intermediate data column;

[0067] Based on the column access frequency corresponding to the intermediate data column, if it is determined that the column access frequency corresponding to the intermediate data column is greater than the second preset frequency threshold, the intermediate data column is selected as a candidate data column.

[0068] The second preset frequency can be preset according to actual needs.

[0069] The aforementioned preliminary screening of SQL queries is based on SQL access frequency. However, a high SQL execution frequency does not necessarily mean that the access frequency of each data column accessed by the SQL query is high. In other words, there is a comparison of the access frequencies of each data column accessed by the SQL query. For example, the target SQL queries include 50 SQL1 queries and 50 SQL2 queries with high execution frequencies. SQL1 accesses the data columns Column1 and Column2 in database table 1 and Column1 in database table 2. SQL2 accesses the data columns Column1 in database table 1 and Column3 in database table 2. Relatively speaking, the access frequency of Column1 in database table 1 and Column3 in database table 2 is significantly higher than that of Column2 in database table 1. Therefore, Column2 in database table 1 can be eliminated, and Column1 in database table 1 and Column3 in database table 2 can be selected as candidate data columns.

[0070] It should be noted that in relational databases, a data column typically corresponds to an attribute of an entity. For example, in an employee database table, age, gender, and length of service can be represented by different data columns, each carrying attribute information about an employee. Furthermore, in one or more embodiments of this specification, if two data columns correspond to the same attribute—that is, if the two data columns semantically represent the same attribute, for example, Column1 represents length of service and Column3 also represents length of service—but these two data columns reside in different database tables, such as Column1 in database table 1 and Column3 in database table 2, then these two data columns are considered two different or independent data columns, and cannot be considered the same data column simply because they correspond to the same attribute.

[0071] Based on the foregoing, the access frequency of a data column can reflect its popularity or frequency of use. Therefore, filtering data columns by their execution frequency can retain those that are accessed frequently or are commonly used, avoiding the creation of infrequently used or invalid indexes. This not only automates index creation to save manual resources but also improves the accuracy and reliability of index creation.

[0072] Step 206: Obtain the database table corresponding to each candidate data column, and obtain the row information of the database table corresponding to each candidate data column and the column information of each candidate data column.

[0073] As mentioned earlier, the target database contains multiple database tables, and each candidate data column can reside in a different database table. Therefore, in order to create an index corresponding to a data column, the database table corresponding to each candidate data column can be obtained.

[0074] It should be noted that the database tables corresponding to each candidate data column can be obtained by referring to existing database metadata query mechanisms. For example, if the target database is an Oracle database, the mapping relationship between data columns and database tables can be obtained through `all_tab_columns`, thus obtaining the database tables corresponding to each candidate data column. Similarly, if the target database is a MySQL database, the mapping relationship between data columns and database tables can be obtained through `information_schema.columns`, thus obtaining the database tables corresponding to each candidate data column. The specific process will not be detailed in this manual; you can obtain the tables according to your actual situation.

[0075] After obtaining the database table corresponding to each candidate data column, the row information of the database table corresponding to each candidate data column and the column information of each candidate data column can also be obtained. Specifically, in one or more embodiments of this specification, the row information of the database table can be understood as information related to the data rows in the database table, which may include the number of data rows in the database table, the specific content corresponding to each data row, i.e., the attributes and attribute values ​​corresponding to each data row, etc. The column information of the candidate data column can be understood as information related to the candidate data column, which may include the attribute values ​​corresponding to the candidate data column, etc.

[0076] By obtaining the attribute information of the database table corresponding to each candidate data column and the column information of each data column, a data foundation is provided for subsequent index creation.

[0077] Step 208: Determine the index creation weight for each candidate data column based on the row information of the database table corresponding to each candidate data column and the column information of each candidate data column.

[0078] In one or more embodiments of this specification, the row information of the database table includes a first quantity, which is the number of data rows in the database table, and the column information of the candidate data column includes a second quantity, which is the number of non-repeating attribute values ​​in the candidate data column.

[0079] Based on the row information of the database table corresponding to each candidate data column and the column information of each candidate data column, determine the index creation weight corresponding to each candidate data column, including:

[0080] Calculate the index creation weight for each candidate data column based on the first and second quantities corresponding to each candidate data column.

[0081] In one or more embodiments of this specification, index creation weight can be understood as the degree of necessity to create an index for a candidate data column. The number of data rows in a database table can be understood as the total number of rows in the database table, and the number of non-duplicate attribute values ​​in a candidate data column can be understood as the number of unique attribute values ​​among all attribute values ​​corresponding to the candidate data column. The number of non-duplicate attribute values ​​refers to the number of attribute values ​​remaining after removing duplicate attribute values. For example... Figure 3 As shown, Figure 3 This is a schematic diagram of a candidate data column provided in an embodiment of this specification. As can be seen, the candidate data column is a seniority data column. The attribute values ​​corresponding to the seniority data column include 1, 3, 3, 5, 5, 5, 10, and 12. After removing duplicate attribute values, the attribute values ​​or unique attribute values ​​include 1, 3, 5, 10, and 12, that is, the number of non-duplicate attribute values ​​is 5.

[0082] In one or more embodiments of this specification, the ratio of a first quantity to a second quantity can be calculated, and the ratio of the first quantity to the second quantity can be used as the index weight corresponding to the candidate data column, as shown in Formula 1 below.

[0083] Formula 1

[0084] in, This could refer to the index creation weight corresponding to the i-th candidate data column. This can refer to the number of data rows in the database table corresponding to the i-th candidate data column, that is, the first number corresponding to the i-th candidate data column. It can refer to the number of non-repeating attribute values ​​in the i-th candidate data column, that is, the second number corresponding to the i-th candidate data column.

[0085] Since the first quantity is the total number of data rows in the database table where the candidate data column resides, and the second quantity is the number of unique attribute values ​​in the candidate data column, the ratio of the first quantity to the second quantity can characterize the degree to which a data column uniquely identifies a data row. In other words, this ratio characterizes the extent to which the value of a data column can uniquely identify a data row. That is, the index creation weight can characterize the degree to which a data column uniquely identifies a data row.

[0086] In this specification, the effectiveness of an index can be judged by its ability to reduce the amount of data that needs to be accessed. Therefore, the smaller this ratio, or the lower the index creation weight, the more duplicate attribute values ​​exist in the candidate data column, making it difficult to effectively distinguish data rows. This means more data can be filtered out, and the lower the degree to which the candidate data column uniquely identifies data rows. In other words, the less uniquely the candidate data column identifies data rows, the less effectively the index corresponding to the candidate data column can filter data. Conversely, the larger this ratio, or the higher the index creation weight, the fewer duplicate attribute values ​​exist in the candidate data column, making it easier to distinguish data rows. This means less data can be filtered out, and the higher the degree to which the candidate data column uniquely identifies data rows. In other words, the more uniquely the candidate data column identifies data rows, the more effectively the index corresponding to the candidate data column can filter data.

[0087] As described above, the ratio of the first quantity to the second quantity, i.e., the index creation weight, is used as a screening criterion for subsequent index creation of candidate data columns. Since this ratio can reflect the efficiency of filtering data for candidate data columns, determining whether to create an index for candidate data columns based on this ratio can improve the reliability and accuracy of index creation, avoid blindly creating indexes, improve the search efficiency of the created indexes, and thus reduce CPU and I / O overhead.

[0088] Step 210: Create weights based on the indexes corresponding to each candidate data column, determine at least one target data column, and create indexes for the database tables corresponding to each target data column.

[0089] As mentioned earlier, index creation weight represents the degree to which a data column uniquely identifies a data row. The smaller the index creation weight, the more data can be filtered out, and the lower the degree to which the candidate data column uniquely identifies the data row. Therefore, at least one target data column can be determined based on the index creation weight corresponding to each candidate data column.

[0090] In one or more embodiments of this specification, weights are created based on the indexes corresponding to each candidate data column to determine at least one target data column, including:

[0091] Determine the candidate data column to be processed, wherein the candidate data column to be processed is any one of the candidate data columns;

[0092] If the index creation weight corresponding to the candidate data column to be processed is greater than the preset weight threshold, the candidate data column to be processed will be used as the target data column.

[0093] In other words, for each candidate data column, if the index creation weight corresponding to that candidate data column is greater than the preset weight threshold, it means that the candidate data column meets the requirements. This means that the candidate data column can effectively filter data rows, or that the candidate data column can be considered to have a good effect in uniquely identifying data rows. Therefore, the candidate data column can be used as the target data column.

[0094] The preset weight threshold can be set according to actual needs.

[0095] By filtering candidate data columns using preset weight thresholds, we can retain candidate data columns with fewer preset data, which means they have a better filtering effect. This can improve the query efficiency of the created index and ensure the effectiveness of the index.

[0096] Furthermore, for any given SQL statement, there may be instances where multiple data columns from the same database table are accessed. For example, in SQL3: `select * from emp e inner join dept d on d.deptno =e.deptno where e.ename like 'EMP_0499%'`, this means accessing the data columns `ename` and `deptno` from the `emp` table in the employee database. Assuming both data columns pass the access frequency filter (i.e., `ename` and `deptno` are candidate data columns), and `ename` fails the index creation weight filter while `deptno` passes and is identified as the target data column, in SQL3, `ename` and `deptno` appear as a combination. Therefore, although the index creation weight of `ename` is not greater than the preset weight threshold, the combined index creation weight of `ename` and `deptno` can be based on `deptno`, thus the combined index creation weight can be greater than the preset weight threshold, still achieving a good filtering effect.

[0097] Therefore, in one or more embodiments of this specification, when the index creation weight corresponding to the candidate data column to be processed is greater than a preset weight threshold, the candidate data column to be processed is used as the target data column, including:

[0098] If the index creation weight corresponding to the candidate data column to be processed is greater than the preset weight threshold, the candidate data column to be processed will be used as the reference data column.

[0099] Obtain column association information, and based on the column association information, determine the associated data columns that have an association relationship with the reference data column among the candidate data columns other than the reference data column, wherein the column association information represents the association relationship of multiple candidate data columns in the same database table appearing simultaneously in at least one structured query statement;

[0100] The reference data column and the associated data column are used as the target data column.

[0101] It should be noted that the column association information can be understood as the co-occurrence relationship of multiple candidate data columns in the same database table being referenced in at least one SQL statement. In other words, the column association information can include the association relationship of multiple candidate data columns in the same database table determined by at least one structured query statement. Alternatively, it can represent the association relationship between candidate data columns in the same database table determined by each SQL statement. For example, the SQL query semantics might be to retrieve the names of employees less than 30 years old and with more than 5 years of service from the employee database table. In this query, both age and service years are referenced in the employee database table, thus forming an association relationship: age and service years. Of course, this association relationship can also be represented as age and service years in the employee database table. Therefore, the column association information can be understood as the correspondence between at least one candidate data column in the same database table obtained by at least one SQL statement; that is, the column association information is the association or correspondence between candidate data columns, referring to the appearance of the candidate data column in the database table corresponding to the SQL statement.

[0102] It should be understood that when SQL accesses only one target data column in a database table, the association information for that column may only include that target data column. When SQL accesses multiple target data columns in a database table, the association information may include the association relationship between the multiple target data columns. When SQL accesses multiple database tables and accesses multiple target data columns in each database table, the association information may include the association relationship between the multiple target data columns corresponding to each database table.

[0103] It should also be noted that, in one or more embodiments of this specification, for each SQL statement, it is necessary to generate the association relationship of the candidate data columns located in the database table corresponding to that SQL statement. In other words, an association relationship can be determined based on a SQL statement, and the association relationships corresponding to each SQL statement constitute column association information.

[0104] In one or more embodiments of this specification, obtaining column association information includes:

[0105] Parse each structured query statement to obtain at least one data column operated on by each operation clause targeting the same database table in each structured query statement;

[0106] Column association information is obtained based on at least one data column operated on by each clause of the structured query statement targeting the same database table.

[0107] Typically, an SQL statement is implemented by one or more operation clauses. Operation clauses refer to the key fields or commands in the SQL statement. Operation clauses include, but are not limited to, SELECT clauses, WHERE clauses, JOIN clauses, etc. Each operation clause can operate on one or more data columns in a database table.

[0108] Therefore, in one or more embodiments of this specification, each SQL statement can be parsed to obtain the data columns operated by each clause in each SQL statement, and for each SQL statement, the association relationship of the candidate data columns in the database table corresponding to the SQL statement can be obtained based on the data columns operated by each clause in the SQL statement, thereby obtaining column association information.

[0109] In practical applications, each SQL statement can be parsed using SQL parsing tools and large language models to obtain the operation clauses for the same database table in each SQL statement, as well as the at least one data column operated on by each operation clause for the same database table in each SQL statement. Thus, column association information can be obtained based on the at least one data column operated on by each operation clause for the same database table in each SQL statement.

[0110] In one or more embodiments of this specification, for each candidate data column, if the index creation weight corresponding to the candidate data column is greater than a preset weight threshold, the candidate data column can be used as a reference data column. Then, based on column association information, among the candidate data columns other than the candidate data column, an associated data column that has an association relationship with the candidate data column can be determined, and both the candidate data column and the associated data column can be used as target data columns.

[0111] By obtaining the association relationship through each SQL statement, multiple candidate data column combinations located in the same database table can be identified. This way, if a candidate data column is eliminated due to insufficient index creation weight, and a target data column in the same database table as the candidate data column is found in the association relationship, the candidate data column can be considered as the target data column as well. This avoids missing data columns and improves the accuracy and reliability of the created index.

[0112] Based on the above, a flowchart for determining the target data column is provided, as follows: Figure 4 As shown, Figure 4 This is a flowchart illustrating a method for determining a target data column, as provided in one embodiment of this specification. It can be seen that the target data column can be determined based on the SQL execution frequency, the data column access frequency, the index creation weight, and column association information.

[0113] Furthermore, after obtaining each target data column, an index can be created for the database table corresponding to each target data column. In one or more embodiments of this specification, creating an index for the database table corresponding to each target data column includes:

[0114] Obtain column association information, wherein the column association information represents the association relationship between multiple candidate data columns in the same database table that appear simultaneously in at least one structured query statement;

[0115] Based on the column association information and each target data column, a first data column set and a second data column set are determined, wherein the target data columns in the first data column set are target data columns that are not associated with other target data columns, and the target data columns in the second data column set are target data columns that are associated with other target data columns;

[0116] Create indexes for the database tables corresponding to each target data column in the first data column set, and create indexes for the database tables corresponding to each target data column in the second data column set.

[0117] It should be noted that the meaning and acquisition method of this column association information are the same as those of the aforementioned column association information, and will not be elaborated here.

[0118] As mentioned earlier, column association information represents the co-occurrence relationship of multiple candidate data columns in the same database table being referenced together in SQL. Therefore, based on this column association information, each target data column can be divided into two parts and added to a first data column set and a second data column set respectively. In other words, each target data column can be divided into a first data column set and a second data column set. Specifically, for each target data column, based on the column association information, if it is determined that the target data column has no association relationship with other target data columns, the target data column is added to the first data column set; if it is determined that the target data column has an association relationship with other target data columns, the target data column is added to the second data column set. Alternatively, other target data columns that have an association relationship with the target data column can also be added to the second data column set. Alternatively, for each target data column, based on the column association information, if it is determined that none of the other data columns in the database table corresponding to the target data column are target data columns, the target data column is added to the first data column set. If it is determined that any other data column in the database table corresponding to the target data column is the target data column, the target data column is added to the second data column set. Alternatively, other data columns in the database table corresponding to the target data column that are target data columns can be added to the second data column set.

[0119] In general, in one or more embodiments of this specification, the first set of data columns may include target data columns that do not co-occur with other target data columns in any SQL statement, and the second set of data columns may include target data columns that co-occur with at least one other target data column in at least one SQL statement.

[0120] Then, based on the first set of data columns, a single-column index can be created for each target data column on the corresponding database table within the first set of data columns. Specifically, creating indexes for the database tables corresponding to each target data column in the first set of data columns includes:

[0121] If the first data column set is not empty, a single index is created for the database table corresponding to each target data column in the first data column set, based on each target data column in the first data column set.

[0122] In practical applications, since the first set of data columns can be empty, we can first determine whether the first set of data columns is empty. If not, we can create a single-column index for each target data column in the first set of data columns on the database table corresponding to that target data column. Specifically, we can create a single-column index with that target data column as the key on the database table corresponding to that target data column, that is, we can create that target data column as the key column of the index of the database table corresponding to that target data column.

[0123] Correspondingly, based on the second set of data columns and the relationships between the target data columns within that set, a composite index containing at least two target data columns can be created on the database table corresponding to each data column combination consisting of at least two target data columns. Specifically, creating indexes for the database tables corresponding to each target data column in the second set of data columns includes:

[0124] If the second set of data columns is not empty, at least two target data columns located in the target database table are determined in the second set of data columns, wherein the target database table is any database table;

[0125] Create a composite index for the target database table based on at least two target data columns located in the target database table.

[0126] In practical applications, since the second data column set can be empty, we can first determine whether the second data column set is empty. If not, we can then determine at least two target data columns located in the target database table within the second data column set. This can be understood as determining the target data column sets located in the same database table within the second data column set. Specifically, based on the aforementioned column association information, we can determine the database table corresponding to each target data column in the second data column set and obtain multiple target data columns in the second data column set corresponding to each database table. Thus, for each database table, we can create a composite index for that database table based on each second target data column in the second data column set corresponding to that database table, using each second target data column in the second data column set corresponding to that database table as the key.

[0127] In other words, if the second set of data columns is not empty, for each target data column in the second set of data columns, at least one target data column located in the same database table as the target data column can be determined. The target data column and the at least one target data column located in the same database table as the target data column can be used as the data column combination corresponding to the target data column. Thus, a composite index can be created for the data column combination on the database table corresponding to the data column combination. Specifically, a composite index with the data column combination as the key can be created on the database table corresponding to the data column combination. That is, the data column combination is created as the key column of the index of the database table corresponding to the data column combination.

[0128] By using the above methods, creating a single-column index on the corresponding database table for a single data column referenced by the SQL, and aggregating frequently referenced target data columns in the same database table into a data column combination based on the co-occurrence relationship of the data columns corresponding to the SQL, and creating a composite index for the data column combination on the corresponding database table, the accuracy and effectiveness of the created data column index can be improved.

[0129] like Figure 5 As shown, Figure 5 This diagram illustrates an index creation process according to one embodiment of this specification. The index creation method described above can resolve database performance issues caused by delayed or missed index creation, reduce manual costs, and automatically identify frequently accessed data columns that trigger full table scans due to missing indexes. It determines whether to create an index for candidate data columns based on column access information and index creation weights. If an index needs to be created for a candidate data column, it can also determine the type of index to create (e.g., single-column index or composite index). Automated index creation can be completed before database performance problems become apparent, improving overall database operating efficiency and stability, and enhancing user experience.

[0130] The index creation method described above differs from traditional index creation methods that rely on manual experience and rule-based inference. It integrates two dimensions of data: the access frequency of the data column (which reflects how frequently the data column is used) and the index creation weight determined based on the data distribution of the database table where the data column resides (which characterizes the effectiveness of the index). This ensures that the created index is both necessary and effective, avoiding the storage and maintenance overhead caused by blindly creating invalid indexes. More importantly, it can improve the reliability and trustworthiness of automated index creation and solve the problem of missing indexes caused by insufficient experience, negligence, or insensitivity to changes in business data of SQL reviewers.

[0131] Furthermore, in one or more embodiments of this specification, the order of the data columns in the composite index is crucial. Creating a composite index for the target database table based on at least two target data columns located in the target database table includes:

[0132] Obtain the index creation weights corresponding to each target data column in the target database table;

[0133] Obtain the target data column sequence by following the index creation weights from largest to smallest;

[0134] Create a composite index for the target database table based on the target data column sequence.

[0135] As mentioned earlier, the index creation weight can reflect the amount of data filtered out. The higher the index creation weight, the less data is filtered out, and the better the filtering effect. Therefore, the data columns with larger index creation weights can be sorted first to filter out a relatively small amount of data. Then, within the data space corresponding to the smaller amount of data found based on the data columns with larger index creation weights, the data columns with smaller index creation weights can be used for subsequent searches to improve search efficiency.

[0136] Furthermore, as actual business operations are executed and data distribution changes, the created index may become invalid or its call frequency may decrease. Therefore, in one or more embodiments of this specification, the call status of the created index can be monitored to adjust the index status according to the call status, thereby achieving a closed loop in index management.

[0137] Specifically, after creating indexes for the database tables corresponding to each target data column, the method further includes:

[0138] Retrieve the call information of each created index in a preset period;

[0139] Update the status of each index based on the call information of each index.

[0140] In other words, the embodiments provided in this specification can not only automatically create indexes, but also continuously monitor and optimize the created indexes, forming a complete index management closed loop. That is, for the created indexes, their call status within a preset period is continuously monitored.

[0141] The preset period can be set in advance, and the call information refers to the usage information of the created index. Specifically, the call information can include the index usage frequency.

[0142] In practical applications, the call information can be determined based on the SQL executed within a preset period. Alternatively, the call information can be obtained by acquiring the relevant database dynamic performance view and collecting it from the view. For example, in the case of an Oracle database, Oracle's BA_INDEX_USAGE or V$SQL_PLAN can be obtained to collect index call information. This call information includes, but is not limited to, the index usage frequency, the number of times the index is used as an access path in SQL queries, the timestamp of the last use, etc.

[0143] In one or more embodiments of this specification, the status of each index is updated based on the call information of each index. Specifically, for each index, if it is determined that the usage frequency of the index corresponding to that index is less than a preset index usage frequency threshold, the index is deleted, or its status is adjusted to a deleted state. If it is determined that the usage frequency of the index corresponding to that index is not less than the preset index usage frequency threshold, the index is retained, that is, its retained state is maintained. In other words, the index status includes a deleted state and a retained state. The retained state means that the index is still generated and can be called.

[0144] The preset index usage frequency threshold can be preset according to actual needs.

[0145] In one or more embodiments of this specification, the state of each index is updated based on the call information of each index. Specifically, for each index, if it is determined that the index usage frequency corresponding to the index is less than a preset index usage frequency threshold, the index can be determined as a first type index; if it is determined that the index usage frequency corresponding to the index is not less than the preset index usage frequency threshold, the index can be determined as a second type index. Then, the state of each index can be updated according to the preset strategy corresponding to each type of index.

[0146] The first preset strategy for the first type of index can be disabling the index, deleting the index, etc., and the index status can include disabled status and deleted status. The second preset strategy for the second type of index can be retaining the index. That is to say, for each index, the type of the index is determined, and then the index can be processed according to the index status. Specifically, if the index type is determined to be a first type of index, the index can be deleted or disabled according to the first preset strategy, or the index status can be updated to deleted status or disabled status. If the index type is determined to be a second type of index, the index can be retained according to the second preset strategy, or the index status can be kept (updated to retained status).

[0147] In practical applications, when disabling a Type 1 index, database-related disabling operations can be performed, such as ALTER INDEX, UNUSABLE, or ALTER INDEX, INVISIBLE. These database-related disabling operations preserve the index metadata corresponding to the Type 1 index, but the Type 1 index is unavailable in the query optimizer. This avoids the additional I / O and CPU overhead incurred by DML operations (such as INSERT, UPDATE, DELETE, etc.) in maintaining the Type 1 index, while retaining the possibility of re-enabling the Type 1 index later.

[0148] It should be noted that DML operations refer to operations that add, delete, modify, and query data in a database. DML operations focus more on the data within a table and do not involve changes to the database structure. Unlike DDL, DML operations can be committed or rolled back through transaction control. DML operations are not automatically committed.

[0149] In practical applications, when deleting a Type I index, you can execute the DROP INDEX statement to remove the Type I index and completely release the disk storage space occupied by the Type I index.

[0150] like Figure 6 As shown, Figure 6 This specification provides a flowchart of an index management method according to an embodiment. This index management method can update the index status according to the actual usage of the index, improve the flexibility of index management, and avoid the situation where the index fails due to changes in actual business execution and data distribution, thus avoiding the occupation of storage space.

[0151] In practical applications, Data Definition Language (DDL) is a set of commands used to define database schemas and structures. It includes creating, modifying, and deleting the structure of databases and tables, as well as defining indexes, views, and other database objects. DDL commands are an indispensable part of database management, directly affecting the database's structure and storage method. Common DDL operations include creating a database (CREATE DATABASE), creating a table (CREATE TABLE), modifying a table structure (ALTER TABLE), and deleting a database or table (DROP DATABASE TABLE).

[0152] In step 210 above, when creating indexes for the database tables corresponding to each target data column, this can be achieved by executing the corresponding CREATE INDEX DDL statement on the target database. The specific process will not be described in detail in this manual.

[0153] In one or more embodiments of this specification, since DDL operations typically affect the structure of the entire database or database tables, and these operations are automatically committed and cannot be rolled back once executed, an index creation notification message can be generated to ensure security. This index creation notification message contains information on creating indexes for the database tables corresponding to each target data column. This index creation notification message can be sent to relevant technical personnel or other computing devices. The relevant technical personnel or other computing devices can review the index creation notification message. If the review is approved, a creation confirmation message is sent to the computing device. This creation confirmation message is used to instruct the computing device to create indexes for the database tables corresponding to each target data column. If the review is not approved, a creation rejection message is sent. This creation rejection message is used to instruct the computing device not to create indexes for the database tables corresponding to each target data column.

[0154] In one or more embodiments of this specification, in order to improve the security of the database, corresponding notification information can be generated for specific operations in the above-described index creation process, such as creating an index, deleting an index, disabling an index, etc. The notification information is sent to relevant technical personnel or other computing devices so that the relevant technical personnel or other computing devices can make final confirmation and ensure the security of the operation.

[0155] Corresponding to the above method embodiments, this application also provides an index creation apparatus embodiment. Figure 7 A schematic diagram of the structure of an index creation apparatus according to an embodiment of this application is shown. Figure 7 As shown, the device includes:

[0156] The first acquisition module 702 is configured to acquire column access information, wherein the target column access information is obtained by at least one structured query statement;

[0157] The first determining module 704 is configured to determine at least one candidate data column based on the column access information.

[0158] The second acquisition module 706 is configured to acquire the database table corresponding to each candidate data column, and acquire the row information of the database table corresponding to each candidate data column and the column information of each candidate data column.

[0159] The second determining module 708 is configured to determine the index creation weight corresponding to each candidate data column based on the row information of the database table corresponding to each candidate data column and the column information of each candidate data column.

[0160] The index creation module 710 is configured to determine at least one target data column based on the index creation weights corresponding to each candidate data column, and to create an index for the database table corresponding to each target data column.

[0161] Optionally, the first acquisition module 702 is further configured to: acquire at least one structured query statement and the execution frequency corresponding to each structured query statement; determine the structured query statement to be processed, and if the execution frequency corresponding to the structured query statement to be processed is greater than a first preset frequency threshold, use the structured query statement to be processed as the target structured query statement, wherein the structured query statement to be processed is any one of the structured query statements; and obtain column access information based on each target structured query statement.

[0162] Optionally, the column access information includes index information corresponding to each data column;

[0163] The first determining module 704 is further configured to determine a data column to be processed, wherein the data column to be processed is any one of the data columns corresponding to the column access information; and, based on the index information corresponding to the data column to be processed, if it is determined that the data column to be processed does not have an index, the data column to be processed is used as a candidate data column.

[0164] Optionally, the column access information includes the column access frequency corresponding to each data column;

[0165] The first determining module 704 is further configured to, when it is determined that the data column to be processed does not have an index, use the data column to be processed as an intermediate data column; and, based on the column access frequency corresponding to the intermediate data column, use the intermediate data column as a candidate data column when it is determined that the column access frequency corresponding to the intermediate data column is greater than a second preset frequency threshold.

[0166] Optionally, the row information of the database table includes a first quantity, which is the number of data rows in the database table, and the column information of the candidate data column includes a second quantity, which is the number of non-duplicate attribute values ​​in the candidate data column.

[0167] The second determining module 708 is further configured to calculate the index creation weight corresponding to each candidate data column based on the first quantity and the second quantity corresponding to each candidate data column.

[0168] Optionally, the index creation module 710 is further configured to determine a candidate data column to be processed, wherein the candidate data column to be processed is any one of the candidate data columns; and if the index creation weight corresponding to the candidate data column to be processed is greater than a preset weight threshold, the candidate data column to be processed is used as the target data column.

[0169] Optionally, the index creation module 710 is further configured to: when the index creation weight corresponding to the candidate data column to be processed is greater than a preset weight threshold, use the candidate data column to be processed as a reference data column; obtain column association information, and based on the column association information, determine the associated data column that has an association relationship with the reference data column among the candidate data columns other than the reference data column, wherein the column association information represents the association relationship of multiple candidate data columns in the same database table appearing simultaneously in at least one structured query statement; and use the reference data column and the associated data column as target data columns.

[0170] Optionally, the index creation module 710 is further configured to: obtain column association information, wherein the column association information represents the association relationship of multiple candidate data columns appearing simultaneously in at least one structured query statement in the same database table; determine a first data column set and a second data column set based on the column association information and each target data column, wherein the target data columns in the first data column set are target data columns that have no association relationship with other target data columns, and the target data columns in the second data column set are target data columns that have an association relationship with other target data columns; create indexes for the database tables corresponding to each target data column in the first data column set, and create indexes for the database tables corresponding to each target data column in the second data column set.

[0171] Optionally, the index creation module 710 is further configured to parse each structured query statement to obtain at least one data column operated on by each operation clause for the same database table in each structured query statement; and to obtain column association information based on at least one data column operated on by each operation clause for the same database table in each structured query statement.

[0172] Optionally, the index creation module 710 is further configured to create a single index for the database table corresponding to each target data column in the first data column set, based on each target data column in the first data column set, when the first data column set is not empty.

[0173] The index creation module 710 is further configured to, when the second data column set is not empty, determine at least two target data columns located in the target database table in the second data column set, wherein the target database table is any database table; and create a composite index for the target database table based on the at least two target data columns located in the target database table.

[0174] Optionally, the index creation module 710 is further configured to: obtain the index creation weights corresponding to each target data column in the target database table; obtain the target data column sequence according to the index creation weights in descending order; and create a composite index for the target database table based on the target data column sequence.

[0175] Optionally, the index creation device further includes a monitoring module 712, configured to acquire the call information of each created index in a preset period; and update the status of each index according to the call information of each index.

[0176] This embodiment first determines candidate data columns based on column access information, and then determines the index creation weight of the candidate data columns based on the row information of the corresponding database table and the column information of the candidate data columns. By addressing both column access information and index creation weight, it can solve database performance problems caused by delayed or missed index creation. It requires no manual resources and can automatically identify frequently accessed data columns that trigger full table scans due to missing indexes, improving SQL execution efficiency. Furthermore, while automating index creation, it improves the convenience, efficiency, and reliability of index creation.

[0177] The above is a schematic scheme of an index creation device according to this embodiment. It should be noted that the technical solution of this index creation device and the technical solution of the index creation method described above belong to the same concept. For details not described in detail in the technical solution of the index creation device, please refer to the description of the technical solution of the index creation method described above.

[0178] Figure 8 A structural block diagram of a computing device 800 according to an embodiment of this application is shown. The components of the computing device 800 include, but are not limited to, a memory 810 and a processor 820. The processor 820 is connected to the memory 810 via a bus 830, and a database 850 is used to store data.

[0179] The computing device 800 also includes an access device 840, which enables the computing device 800 to communicate via one or more networks 860. Examples of these networks include Public Switched Telephone Network (PSTN), Local Area Network (LAN), Wide Area Network (WAN), Personal Area Network (PAN), or combinations of communication networks such as the Internet. The access device 840 may include one or more of any type of wired or wireless network interface (e.g., a network interface card (NIC)), such as an IEEE 802.11 Wireless Local Area Network (WLAN) wireless interface, a Wi-MAX (Worldwide Interoperability for Microwave Access) interface, an Ethernet interface, a Universal Serial Bus (USB) interface, a cellular network interface, a Bluetooth interface, a Near Field Communication (NFC) interface, and so on.

[0180] In one embodiment of this application, the aforementioned components of the computing device 800 and Figure 8 Other components, not shown, can also be connected to each other, for example, via a bus. It should be understood that... Figure 8 The block diagram of the computing device shown is for illustrative purposes only and is not intended to limit the scope of this application. Those skilled in the art can add or replace other components as needed.

[0181] The computing device 800 can be any type of stationary or mobile computing device, including mobile computers or mobile computing devices (e.g., tablet computers, personal digital assistants, laptop computers, notebook computers, netbooks, etc.), mobile phones (e.g., smartphones), wearable computing devices (e.g., smartwatches, smart glasses, etc.) or other types of mobile devices, or stationary computing devices such as desktop computers or personal computers (PCs). The computing device 800 can also be a mobile or stationary server.

[0182] The processor 820 is used to execute the following computer program / instructions, which, when executed by the processor, implement the steps of the above-described index creation method.

[0183] The above is an illustrative scheme of a computing device according to this embodiment. It should be noted that the technical solution of this computing device and the technical solution of the index creation method described above belong to the same concept. For details not described in detail in the technical solution of the computing device, please refer to the description of the technical solution of the index creation method described above.

[0184] An embodiment of this specification also provides a computer-readable storage medium storing a computer program / instructions that, when executed by a processor, implement the steps of the index creation method described above.

[0185] The above is an illustrative scheme of a computer-readable storage medium according to this embodiment. It should be noted that the technical solution of this storage medium and the technical solution of the index creation method described above belong to the same concept. For details not described in detail in the technical solution of the storage medium, please refer to the description of the technical solution of the index creation method described above.

[0186] An embodiment of this specification also provides a computer program product, including a computer program / instructions that, when executed by a processor, implement the steps of the above-described index creation method.

[0187] The above is an illustrative scheme of a computer program product according to this embodiment. It should be noted that the technical solution of this computer program product and the technical solution of the index creation method described above belong to the same concept. For details not described in detail in the technical solution of the computer program product, please refer to the description of the technical solution of the index creation method described above.

[0188] The foregoing has described specific embodiments of this application. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in a different order than that shown in the embodiments and may still achieve the desired results. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0189] The computer instructions include computer program code, which may be in the form of source code, object code, executable file, or certain intermediate forms. The computer-readable medium may include: any entity or device capable of carrying the computer program code, recording media, USB flash drive, portable hard drive, magnetic disk, optical disk, computer memory, read-only memory (ROM), random access memory (RAM), electrical carrier signals, telecommunication signals, and software distribution media, etc. It should be noted that the content included in the computer-readable medium may be appropriately added or removed according to the requirements of patent practice. For example, in some regions, according to patent practice, computer-readable media may not include electrical carrier signals and telecommunication signals.

[0190] It should be noted that, for the sake of simplicity, the foregoing method embodiments are all described as a series of actions. However, those skilled in the art should understand that this application is not limited to the described order of actions, as some steps may be performed in other orders or simultaneously according to this application. Furthermore, those skilled in the art should also understand that the embodiments described in the specification are preferred embodiments, and the actions and modules involved are not necessarily essential to this application.

[0191] In the above embodiments, the descriptions of each embodiment have different focuses. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions in other embodiments.

[0192] The preferred embodiments disclosed above are merely illustrative of this application. The optional embodiments do not exhaustively describe all details, nor do they limit the invention to the specific implementations described. Clearly, many modifications and variations can be made based on the content of this application. These embodiments are selected and specifically described in this application to better explain the principles and practical applications of this application, thereby enabling those skilled in the art to better understand and utilize this application. This application is limited only by the claims and their full scope and equivalents.

Claims

1. An index creation method, characterized in that, include: Obtain column access information, wherein the column access information is obtained by at least one target structured query statement, and the target structured query statement is a structured query statement whose execution frequency is greater than a preset frequency threshold; Based on the column access information, at least one candidate data column is determined; Retrieve the database table corresponding to each candidate data column, and retrieve the row information and column information of the database table corresponding to each candidate data column. The index creation weight corresponding to each candidate data column is determined based on the first number in the row information of the database table corresponding to each candidate data column and the second number in the column information of each candidate data column. The first number is the number of data rows in the database table, and the second number is the number of non-repeating attribute values ​​in the candidate data column. Based on the indexes corresponding to each candidate data column, weights are created to determine at least one target data column, and indexes are created for the database tables corresponding to each target data column. The target data column is a candidate data column whose index creation weight is greater than a preset weight threshold.

2. The method as described in claim 1, characterized in that, Retrieve column access information, including: Obtain at least one structured query statement and the execution frequency of each structured query statement; A structured query statement to be processed is determined, and if the execution frequency of the structured query statement to be processed is greater than a first preset frequency threshold, the structured query statement to be processed is taken as the target structured query statement, wherein the structured query statement to be processed is any one of the structured query statements; Based on the structured query statements for each target, obtain column access information.

3. The method as described in claim 1, characterized in that, The column access information includes the index information corresponding to each data column; Based on the column access information, at least one candidate data column is determined, including: Determine the data column to be processed, wherein the data column to be processed is any one of the data columns corresponding to the column access information; Based on the index information corresponding to the data column to be processed, if it is determined that the data column to be processed does not have an index, the data column to be processed is used as a candidate data column.

4. The method as described in claim 3, characterized in that, The column access information includes the column access frequency corresponding to each data column; If it is determined that the data column to be processed does not have an index, the data column to be processed will be used as a candidate data column, including: If it is determined that the data column to be processed does not have an index, the data column to be processed will be used as an intermediate data column; Based on the column access frequency corresponding to the intermediate data column, if it is determined that the column access frequency corresponding to the intermediate data column is greater than the second preset frequency threshold, the intermediate data column is selected as a candidate data column.

5. The method as described in claim 1, characterized in that, Weights are created based on the indexes corresponding to each candidate data column, and at least one target data column is determined, including: Determine the candidate data column to be processed, wherein the candidate data column to be processed is any one of the candidate data columns; If the index creation weight corresponding to the candidate data column to be processed is greater than a preset weight threshold, the candidate data column to be processed will be used as the target data column.

6. The method as described in claim 5, characterized in that, If the index creation weight corresponding to the candidate data column to be processed is greater than a preset weight threshold, the candidate data column to be processed will be used as the target data column, including: If the index creation weight corresponding to the candidate data column to be processed is greater than the preset weight threshold, the candidate data column to be processed will be used as the reference data column. Obtain column association information, and based on the column association information, determine the associated data columns that have an association relationship with the reference data column among the candidate data columns other than the reference data column, wherein the column association information represents the association relationship of multiple candidate data columns in the same database table appearing simultaneously in at least one structured query statement; The reference data column and the associated data column are used as the target data column.

7. The method as described in claim 1, characterized in that, Create indexes for the database tables corresponding to each target data column, including: Obtain column association information, wherein the column association information represents the association relationship between multiple candidate data columns in the same database table that appear simultaneously in at least one structured query statement; Based on the column association information and each target data column, a first data column set and a second data column set are determined, wherein the target data columns in the first data column set are target data columns that are not associated with other target data columns, and the target data columns in the second data column set are target data columns that are associated with other target data columns; Create indexes for the database tables corresponding to each target data column in the first data column set, and create indexes for the database tables corresponding to each target data column in the second data column set.

8. The method as described in claim 6 or 7, characterized in that, Retrieve column association information, including: Parse each structured query statement to obtain at least one data column operated on by each operation clause targeting the same database table in each structured query statement; Column association information is obtained based on at least one data column operated on by each clause of the structured query statement targeting the same database table.

9. The method as described in claim 7, characterized in that, Create indexes for the database tables corresponding to each target data column in the first data column set, including: If the first data column set is not empty, create a single-column index for the database table corresponding to each target data column in the first data column set based on each target data column in the first data column set; Create indexes for the database tables corresponding to each target data column in the second set of data columns, including: If the second set of data columns is not empty, at least two target data columns located in the target database table are determined in the second set of data columns, wherein the target database table is any database table; Create a composite index for the target database table based on at least two target data columns located in the target database table.

10. The method as described in claim 9, characterized in that, Create a composite index for the target database table based on at least two target data columns located in the target database table, including: Obtain the index creation weights corresponding to each target data column in the target database table; Obtain the target data column sequence by following the index creation weights from largest to smallest; Create a composite index for the target database table based on the target data column sequence.

11. The method as described in claim 1, characterized in that, After creating indexes for the database tables corresponding to each target data column, the method further includes: Retrieve the call information of each created index in a preset period; Update the status of each index based on the call information of each index.

12. A computing device, characterized in that, include: Memory and processor; The memory is used to store computer programs / instructions, and the processor is used to execute the computer programs / instructions, which, when executed by the processor, implement the steps of the method according to any one of claims 1 to 11.

13. A computer-readable storage medium storing a computer program / instructions, characterized in that, When the computer program / instructions are executed by the processor, they implement the steps of the method according to any one of claims 1 to 11.

14. A computer program product comprising a computer program / instructions, characterized in that, When the computer program / instructions are executed by the processor, they implement the steps of the method according to any one of claims 1 to 11.

Images