Data table classification method and device, electronic equipment and storage medium

Abstract

The invention discloses a data table classification method and device, electronic equipment and a storage medium, and relates to the technical field of databases. The data table classification method comprises the steps that a table list in a database is obtained, and the current record number of each table in the table list is determined; starting a database playback task to obtain data playback information, determining access modes corresponding to tables related to SQL statements in the data playback information, and determining the access times of each table for each access mode and the total access times of the tables; determining the execution time of the SQL statement, and determining the total record number and the unit time record number of the table according to the execution time and the current record number; and determining the category of each table based on a preset table category identification rule, the total access times, the access times, the total record number and the unit time record number. According to the embodiment of the invention, automatic identification of the category of the table is realized, the problems of high difficulty and high error rate of manual identification of the category of the table are avoided, and the user experience is improved.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a data table classification method, device, electronic equipment and storage medium. Background Art

[0002] Business systems typically run on databases with numerous tables, sometimes reaching thousands, and some tables contain a significant number of records. After the system goes into production, as business grows and the amount of table data increases, improperly designed table and index parameters can lead to a gradual increase in physical synchronous input / output (IO) requests, gradually degrading overall database performance and, in severe cases, even impacting normal business operations. Therefore, it's necessary to optimize access methods for these tables.

[0003] To improve overall database performance, various approaches can be adopted, including optimizing table and index design, rewriting Structured Query Language (SQL) statements, and modifying program logic. However, these approaches often require code adjustments, resulting in high regression testing workloads, long cycles, and high risks. These approaches also require the cooperation of application developers and require extensive functional and performance testing of the program, which may not meet the business requirements for rapid system performance improvement. Furthermore, database system operators cannot fully understand the access status of every table. Manually querying database metadata and statistics to identify table categories is extremely difficult and error-prone. Therefore, how to automatically classify data tables has become a pressing issue. Summary of the Invention

[0004] The present invention provides a data table classification method, device, electronic device and storage medium to solve the problem in the prior art that data tables cannot be automatically classified.

[0005] According to one aspect of the present invention, a method for classifying a data table is provided, wherein the method comprises:

[0006] Get a list of tables in the database and determine the current number of records in each table in the list;

[0007] Start a database playback task to obtain data playback information, determine the access mode corresponding to the table involved in the SQL statement in the data playback information, determine the number of accesses to each table for each access mode and the total number of accesses to the table;

[0008] Determine the execution time of the SQL statement, and determine the total number of records and the number of records per unit time of the table according to the execution time and the current number of records;

[0009] The category of each table is determined based on a preset table category identification rule, the total number of accesses, the number of accesses, the total number of records, and the number of records per unit time.

[0010] According to another aspect of the present invention, a data table classification device is provided, wherein the device comprises:

[0011] A list determination module is used to obtain a list of tables in the database and determine the current number of records in each table in the list of tables;

[0012] A number determination module is used to start a database playback task to obtain data playback information, determine the access mode corresponding to the table involved in the SQL statement in the data playback information, determine the number of accesses to each table for each access mode and the total number of accesses to the table;

[0013] A record number statistics module is used to determine the execution time of the SQL statement, and determine the total number of records in the table and the number of records per unit time according to the execution time and the current number of records;

[0014] The category determination module is configured to determine the category of each table based on a preset table category identification rule, the total number of accesses, the number of accesses, the total number of records, and the number of records per unit time.

[0015] According to another aspect of the present invention, an electronic device is provided, comprising:

[0016] at least one processor; and

[0017] a memory communicatively connected to the at least one processor; wherein,

[0018] The memory stores a computer program executable by the at least one processor. The computer program is executed by the at least one processor so that the at least one processor can perform a data table classification method according to any embodiment of the present invention.

[0019] According to another aspect of the present invention, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement a data table classification method according to any embodiment of the present invention when executed.

[0020] The technical solution of an embodiment of the present invention obtains a table list in a database, determines the current number of records of each table in the table list, starts a database playback task to obtain data playback information, determines the access method corresponding to the table involved in the SQL statement in the data playback information, determines the number of accesses to each table for each access method and the total number of accesses to the table, determines the execution time of the SQL statement, determines the total number of records and the number of records per unit time of the table according to the execution time and the current number of records, determines the category of each table based on preset table category identification rules, the total number of accesses, the number of accesses, the total number of records and the number of records per unit time, realizes automatic identification of the category of the table, avoids the problem that manual identification of the category of the table is difficult and prone to errors, and improves the user experience.

[0021] It should be understood that the content described in this section is not intended to identify the key or important features of the embodiments of the present invention, nor is it intended to limit the scope of the present invention. Other features of the present invention will become readily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0023] Figure 1 is a flow chart of a data table classification method provided according to the first embodiment of the present invention;

[0024] Figure 2 is a flow chart of a data table classification method provided according to the second embodiment of the present invention;

[0025] Figure 3 is a flow chart of a data table classification method provided according to the third embodiment of the present invention;

[0026] Figure 4 This is a flowchart of a method for obtaining access information provided in accordance with Embodiment 3 of the present invention;

[0027] Figure 5 This is a flowchart of a method for obtaining a memory table provided in accordance with the third embodiment of the present invention;

[0028] Figure 6 This is a flowchart of a method for obtaining a hashable organization table provided in accordance with a third embodiment of the present invention;

[0029] Figure 7 This is a flowchart of a method for obtaining a mandatory index access table provided in accordance with a third embodiment of the present invention;

[0030] Figure 8 This is a flowchart of a method for obtaining an index that can be accessed by only an index according to Embodiment 3 of the present invention;

[0031] Figure 9 This is a schematic structural diagram of a data table classification device provided in a third embodiment of the present invention;

[0032] Figure 10 The present invention is a schematic structural diagram of an electronic device for implementing a data table classification method according to an embodiment of the present invention. DETAILED DESCRIPTION

[0033] In order to enable those skilled in the art to better understand the solutions of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the embodiments described are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts should fall within the scope of protection of the present invention.

[0034] It should be noted that the terms "first", "second", etc. in the description and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that the numbers used in this way can be interchanged where appropriate, so that the embodiments of the present invention 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.

[0035] The acquisition, storage, use, and processing of data in this application's technical solution comply with relevant national laws and regulations.

[0036] It should be noted that in the embodiments of the present application, certain software, components, models and other existing solutions in the industry may be mentioned. They should be regarded as exemplary. Their purpose is only to illustrate the feasibility of implementing the technical solution of the present application, but it does not mean that the applicant has or will necessarily use the solution.

[0037] Example 1

[0038] Figure 1This is a flow chart of a data table classification method provided according to the first embodiment of the present invention. This embodiment is applicable to the case of classifying data tables. The method can be executed by a data table classification device. The data table classification device can be implemented in the form of hardware and / or software. The data table classification device can be configured in an electronic device. Figure 1 As shown, the method includes:

[0039] S110 , obtaining a table list in the database, and determining the current number of records in each table in the table list.

[0040] The table list refers to a list of all tables in the database, displaying metadata such as table names, structures, and attributes. A table is a two-dimensional structure used to store structured data in a database. The current record count refers to the actual number of data entries stored in the table, or it can be the number of rows in the table.

[0041] In an embodiment, a database may be connected, metadata of the database may be read, a list of all tables in the data may be obtained, and the number of records in each table at the current moment may be determined as the current number of records.

[0042] S120 , starting a database playback task to obtain data playback information, determining access modes corresponding to tables involved in SQL statements in the data playback information, and determining the number of accesses to each table for each access mode and the total number of accesses to the table.

[0043] A database replay task refers to a technique used to simulate, restore, or analyze database operations, reproducing the database's operations or state over a specific time period. Data replay information refers to the data information obtained through a database replay task. SQL statements can be used to track user database operations in detail. SQL statement operation types can include at least query, insert, update, and delete. Access method refers to the method used to access a table. Typically, database access to a table's data involves searching an index for the physical address of the data matching the query criteria in the table's data area. The data page corresponding to the physical address is then accessed and loaded into a buffer to complete the access. When accessing an index, access begins at the index's root node. The root node page data determines which second-level node page to access. The processing logic for subsequent third-level node pages, up to the index leaf pages, is the same as for the index root node page. If the node page data is in the buffer, it is directly retrieved from the buffer. If not, a synchronous read is performed to read the node page data from the disk where the index is stored and into the buffer.

[0044] In one embodiment, access methods may include primary key query, non-primary key query and full table scan. Among them, primary key query refers to the operation of querying data through the primary key field of the table. The primary key is a field or field combination that uniquely identifies a record in the database, and the database will automatically create an index for it. Non-primary key query refers to the operation of querying data through non-primary key fields. Non-primary key fields may have indexes (such as ordinary indexes, unique indexes), or may not have indexes. Full table scan does not use any indexes and scans all data in the table row by row until a record that meets the conditions is found or all rows are scanned. The number of accesses refers to the number of times the table is accessed for each access method, and the total number of accesses refers to the total number of times the table is accessed. Generally speaking, the total number of accesses is the sum of the number of accesses for each access method.

[0045] In an embodiment, a database replay task can be initiated via a database command to obtain data replay information, read the SQL statements in the data replay information, and determine the access method corresponding to the table involved in each SQL statement. In actual operation, the access method field corresponding to the SQL statement can be determined, and the access method corresponding to the table can be determined based on the access method field. The number of accesses to each table for each access method is recorded, and the total number of accesses to each table is determined as the total number of accesses to the table. In actual operation, after confirming the access method corresponding to the table, the access number of the access method and the total number of accesses can be increased by one.

[0046] S130: Determine the execution time of the SQL statement, and determine the total number of records in the table and the number of records per unit time according to the execution time and the current number of records.

[0047] Execution time refers to the time it takes to execute a SQL statement, from the time the database receives the SQL statement to the time it completes execution and returns the results. Records per unit time refers to the change in the number of records in the table per unit time.

[0048] In an embodiment, the execution time of each SQL statement can be determined to determine whether the execution time of each SQL statement is within the same time period as the execution time of the previous SQL statement. For example, it can be determined whether the execution time of each SQL statement is within the same hour as the execution time of the previous SQL statement. If they are within the same time period, the total number of records in the table and the number of records per unit time can be omitted. If they are not within the same time period, the current number of records in each table can be read from the database metadata as the total number of records in the table, and the difference between the total number of records and the current number of records can be determined as the number of records per unit time.

[0049] S140 , determining the category of each table based on a preset table category identification rule, the total number of accesses, the number of accesses, the total number of records, and the number of records per unit time.

[0050] The preset table category identification rules refer to pre-set rules for determining the category of a table. In actual operation, the preset table category identification rules may include thresholds for determining the table category, such as the maximum number of records in an in-memory table, the preset minimum percentage of in-memory tables, the minimum number of records in a hashable table, the preset minimum percentage of hashable tables, the minimum number of records in a table with forced index access, and the preset minimum percentage of forced index access. Table categories may include, but are not limited to, in-memory tables, hashable tables, and tables with forced index access.

[0051] In an embodiment, the parameters of the table category can be determined based on the thresholds in the preset table category identification rules according to the total number of accesses, the number of accesses, the total number of records, and the number of records per unit time. In one embodiment, the total number of records in the table can be compared with the numerical value of the maximum number of records in the memory-capable table. When it is determined that the total number of records in the table is less than the maximum number of records in the memory-capable table, the sum of the number of accesses for the primary key query and the number of accesses for the non-primary key query of the table is determined, and then the ratio of the sum of the number of accesses to the total number of accesses is determined. When it is determined that the ratio of the sum of the number of accesses to the total number of accesses is greater than the preset minimum percentage of the memory-capable table, the table can be determined to be a memory-capable table. If the table is not a memory-capable table, the total number of records in the table can be further compared with the numerical value of the minimum number of records in the hashable table. When it is determined that the total number of records in the table is greater than the minimum number of records in the hashable table, the ratio of the number of accesses for the primary key query of the table to the total number of accesses can be determined. When it is determined that the ratio of the number of accesses for the primary key query of the table to the total number of accesses is greater than the preset minimum percentage of the hashable table, the table can be determined to be a hashable table. If the table is not a hashable table, you can continue to confirm the relationship between the number of records per unit time and the minimum number of records in the forced index access table. When it is determined that the number of records per unit time is greater than the minimum number of records in the forced index access table, you can determine whether the difference in the number of records per unit time within a preset time (such as one day) is greater than a preset threshold (that is, whether the table records change significantly within the preset time). If so, determine the ratio of the number of accesses of the full disk scan to the total number of accesses to the table. When it is determined that the ratio of the number of accesses of the full disk scan to the total number of accesses is greater than the preset minimum percentage of the forced index access table, determine that the table is a forced index access table.

[0052] An embodiment of the present invention obtains a table list in a database, determines the current number of records of each table in the table list, starts a database playback task to obtain data playback information, determines the access mode corresponding to the table involved in the SQL statement in the data playback information, determines the number of accesses to each table for each access mode and the total number of accesses to the table, determines the execution time of the SQL statement, determines the total number of records and the number of records per unit time of the table according to the execution time and the current number of records, determines the category of each table based on preset table category identification rules, the total number of accesses, the number of accesses, the total number of records and the number of records per unit time, realizes automatic identification of the category of the table, avoids the problem that manual identification of the category of the table is difficult and prone to errors, and improves the user experience.

[0053] In one embodiment, the data table classification method further includes:

[0054] Determining index information for each table in the table list;

[0055] Update the index query field according to the access mode and index information, and determine the query access count of each index query field;

[0056] Determine the index query field corresponding to the maximum query access count as the target field, and the sum of the query access counts of all index query fields;

[0057] The ratio of the maximum query access number to the total is determined as the query ratio. When it is determined that the query ratio is greater than a preset minimum percentage of index-only access, the target field is determined to be index-only accessible.

[0058] Among them, the index query field refers to the query field used to access the table in an indexed manner. In actual operation, when the access method is a primary key query, the table needs to be accessed in an indexed manner; when the access method is a non-primary key query, the table needs to be partially accessed in an indexed manner; when the access method is a full table scan, the table does not need to be accessed in an indexed manner. The number of query accesses refers to the number of queries for each index query field. Index-only access means that all data required for the query can be obtained directly from the index without accessing the data table, which is an efficient query execution method. The preset minimum percentage of index-only access refers to the pre-set threshold for determining index-only access, which can be customized according to business needs.

[0059] In an embodiment, the index query fields may be updated using the access mode and index information, and the query access count for each index query field may be updated. The maximum query access count is determined, and the index query field corresponding to the maximum value is determined as the target field. The query access counts for all index query fields are summed, and the ratio of the query access count for the target field to the sum is determined as a query ratio. When the query ratio is determined to be greater than a preset minimum index-only access percentage, the target field may be determined to be index-only accessible.

[0060] In one embodiment, updating the index query fields according to the access mode and index information and determining the query access count of each index query field include:

[0061] When it is determined that the access method is through index access, it is determined whether the index access is a unique index access;

[0062] If so, determine whether the index access field of the index access contains only index information for the table;

[0063] If the index access field does not contain only index information of the table, confirm that the index access field satisfies the index access-only condition, and determine whether the field information in the index access field other than the index information is a pre-saved index query field;

[0064] If so, the query access count of the index query field is updated; if not, the field information is saved as a new index query field, and a new query access count of the index query field is generated.

[0065] In an embodiment, when it is determined that the access method is through index access, that is, the access method is a primary key query, or a non-primary key query that partially needs to be accessed through an index, it can be determined whether the index access is a unique index access. When the index access is a unique index access, it can be determined whether the index access field of the index access only contains the index information of the table, that is, whether the accessed field contains one or two fields that are not the constituent fields of this unique index in addition to all the constituent fields of a unique index. If the index access field does not contain only the index information of the table, it is confirmed that the index access field meets the index access-only condition. At this time, it can be determined whether the field information other than the index information in the index access field is a pre-saved index query field. If so, the query access count of the index query field is updated, that is, the query access count of the index query field is increased by 1. If not, the field information is saved as a new index query field, and the query access count of the new index query field is generated, that is, the query access count of the new index query field is set to 1, so as to facilitate subsequent determination that only index access is possible.

[0066] Example 2

[0067] Figure 2 This is a flow chart of a data table classification method provided according to the second embodiment of the present invention. This embodiment is based on the above embodiment to further optimize and expand, and can be combined with various optional technical solutions in the above embodiment. Figure 2 As shown, the method includes:

[0068] S201. Connect to a database, query a table list in the metadata of the database, and determine the current number of records in each table in the table list.

[0069] In an embodiment, a connection address of a pre-configured database and a corresponding user name and password may be obtained, the database may be connected to according to the connection address of the database and the corresponding user name and password, a table list may be queried in the metadata of the database, and the number of records in each table at the current moment may be determined in turn as the current number of records.

[0070] S202: Start a database playback task to obtain data playback information, extract the execution plan of the SQL statement in the data playback information, and determine the access mode field of the execution plan.

[0071] The execution plan refers to the specific execution plan of the SQL query. When the database executes the SQL statement, the execution plan will display the index used by the query.

[0072] In an embodiment, a database replay task can be initiated through a database command to obtain data replay information, analyze the execution plan of the SQL statement in the data replay information, and extract the access method field of the execution plan. In actual operation, a specified field of the execution plan can be searched and the data content of the specified field can be obtained as the access method field.

[0073] S203: Determine the access mode corresponding to the table according to the access mode field.

[0074] In an embodiment, when the access method field matches the primary key index, the access method is determined to be a primary key query; when the access method field matches a non-primary key index, the access method is determined to be a non-primary key query; when the access method field does not match the index, the access method is determined to be a full table scan.

[0075] S204: Record the number of accesses of each access mode in each table, and determine the sum of the number of accesses corresponding to each table as the total number of accesses of the table.

[0076] In an embodiment, after the access mode corresponding to the table is determined, the access count of the access mode may be increased by one to determine the access count of each access mode in each table, and the sum of the access counts may be used as the total access count of the table.

[0077] S205: Extract the execution time of the SQL statement and check whether the execution time is in the same period as the execution time of the previous SQL statement.

[0078] In an embodiment, the execution time of an SQL statement may be determined to determine whether the execution time is in the same period as the execution time of a previous SQL statement, that is, to determine whether the execution time of the SQL statement spans time points.

[0079] S206: If yes, the total number of records and the number of records per unit time in the table are not updated.

[0080] S207: If not, read the number of records of each table from the metadata of the database as the total number of records of the table, and determine the difference between the total number of records and the current number of records as the number of records per unit time.

[0081] In an embodiment, when it is determined that the execution time is not in the same period as the execution time of the previous SQL statement, the number of records of each table at the current moment can be read from the metadata of the database as the total number of records of the table, and the difference between the total number of records and the current number of records can be calculated, and the difference can be used as the number of records per unit time.

[0082] S208. Extract the maximum number of records in the memory-capable table, the preset minimum percentage of the memory-capable table, the minimum number of records in the hashable table, the preset minimum percentage of the hashable table, the minimum number of records in the forced index access table, and the preset minimum percentage of the forced index access table from the preset table category identification rules.

[0083] Among them, the maximum number of records in the memory-capable table refers to the maximum number of records that the memory-capable table can accommodate; the preset minimum percentage of the memory-capable table refers to the preset threshold for judging the memory-capable table; the minimum number of records in the hashable table refers to the minimum number of records in the hashable table; the preset minimum percentage of the hashable table refers to the preset threshold for judging the hashable table; the minimum number of records in the forced index access table refers to the minimum number of records in the forced index access table; the preset minimum percentage of the forced index access table refers to the preset threshold for judging the preset forced index access table. Generally speaking, the maximum number of records in the memory-capable table, the preset minimum percentage of the memory-capable table, the minimum number of records in the hashable table, the preset minimum percentage of the hashable table, the minimum number of records in the forced index access table and the preset minimum percentage of the forced index access table can all be set according to business needs.

[0084] S209. When it is determined that the total number of records in the table is less than the maximum number of records in the memory-capable table, determine the sum of the number of accesses for primary key queries and the number of accesses for non-primary key queries of the table, and determine the ratio of the sum of the number of accesses to the total number of accesses as a first ratio. When it is determined that the first ratio is greater than a preset minimum percentage of memory-capable tables, determine that the table is a memory-capable table.

[0085] In an embodiment, the relationship between the total number of records in a table and the maximum number of records that can be stored in memory can be determined. When the total number of records in a table is less than the maximum number of records that can be stored in memory, the sum of the number of accesses for primary key queries and non-primary key queries of the table is determined as the total number of accesses, and the ratio of the total number of accesses to the total number of accesses is calculated as a first ratio. If the first ratio is greater than a preset minimum percentage of a table that can be stored in memory, the table is considered to be a table that can be stored in memory.

[0086] S210. When it is determined that the total number of records in the table is greater than the minimum number of records in the hashable table, determine the ratio of the number of access times for the primary key query of the table to the total number of access times as a second ratio. When it is determined that the second ratio is greater than a preset minimum percentage of the hashable table, determine that the table is a hashable table.

[0087] In an embodiment, the relationship between the total number of records in a table and the minimum number of records in a hashable table can be determined. When the total number of records in a table is greater than the minimum number of records in a hashable table, the ratio of the number of accesses to the primary key query of the table to the total number of accesses is calculated as a second ratio. If the second ratio is greater than the preset minimum percentage of the hashable table, the table is considered to be a hashable table.

[0088] S211. When it is determined that the number of records per unit time is greater than the minimum number of records in the forced index access table, determine whether the difference between the number of records per unit time within the preset time is greater than the preset threshold value. If so, determine the ratio of the number of accesses of the full disk scan of the table to the total number of accesses as the third ratio. When it is determined that the third ratio is greater than the preset minimum percentage of the forced index access table, determine that the table is a forced index access table.

[0089] In an embodiment, the relationship between the number of records per unit time and the minimum number of records in the forced index access table can be determined. When it is determined that the number of records per unit time is greater than the minimum number of records in the forced index access table, it can be determined whether the difference between the number of records per unit time within the preset time is greater than the preset threshold. If the difference between the number of records per unit time is greater than the preset threshold, the ratio of the number of accesses of the full disk scan of the table to the total number of accesses is determined, and the ratio is used as the third ratio. When it is determined that the third ratio is greater than the preset minimum percentage of the forced index access table, the table is determined to be a forced index access table.

[0090] An embodiment of the present invention connects to a database, queries a table list in the metadata of the database, determines the current number of records of each table in the table list, starts a database playback task to obtain data playback information, extracts an execution plan of an SQL statement in the data playback information, determines an access method field of the execution plan, determines an access method corresponding to the table according to the access method field, records the number of accesses of each access method in each table, and determines the sum of the number of accesses corresponding to each table as the total number of accesses of the table, extracts the execution time of the SQL statement, determines whether the execution time is in the same period as the execution time of the previous SQL statement, updates the number of records per unit time, and determines the attribute information of the table; and determines the category of the table by extracting the maximum number of records of the memory-capable table, the preset minimum percentage of the memory-capable table, the minimum number of records of the hashable table, the preset minimum percentage of the hashable table, the minimum number of records of the forced index access table, and the preset minimum percentage of the forced index access table in the preset table category identification rules, thereby classifying the table according to its features and facilitating subsequent optimization of the table.

[0091] Example 3

[0092] Figure 3 This is a flow chart of a data table classification method provided by the third embodiment of the present invention. This embodiment is based on the above embodiment and takes the HashMap data structure as an example to store data information. Taking a hashable table as a hashable table, the sum of Select requests for primary key queries and non-primary key queries as an example, a data table classification method is further explained, as shown in the following figure. Figure 3 As shown, the method includes:

[0093] Step 1: Read the configuration file information.

[0094] Specifically, you can pre-set a configuration parameter file containing the following information: the database connection address and the corresponding user name and password; the user name and password for starting the database data access playback task; the start and end dates for starting the database playback; the table incremental data statistical period (the default is 1 hour); in-memory table identification information: the maximum number of records in the table, the average maximum number of records added per day, and the minimum percentage of Select requests (the sum of primary key accesses and non-primary key accesses) in the total accesses. Hashable table identification information: the minimum number of records in the table, the average number of records added per day, and the minimum percentage of primary key accesses in the total accesses. Forced index access table identification information: the number of table records, the percentage of full table scan accesses in the total accesses; the percentage of index-only accesses in the total index accesses (the preset minimum percentage of index-only accesses).

[0095] At the same time, three HashMap data structures are used to save the parsed information:

[0096] A HashMap stores table access-related information. The key is the table name, and the value is a one-dimensional array. The stored information includes the total number of table records, the total number of accesses, the number of primary key selects (the number of accesses for primary key queries), the number of non-primary key selects (the number of accesses for non-primary key queries), and the number of full table scan queries (the number of accesses for full table queries).

[0097] A HashMap stores the table record data of each table every hour; the key is the table name, and the value is a one-dimensional array that stores the table record data of the table every hour (number of records per time table);

[0098] A HashMap stores the unique index access information of the table: the key is the index name, and the value is a two-dimensional array that stores the field information (up to two) accessed after the index field (i.e., the index query field) and its query access count. The first element of the two-dimensional array stores the total access count of the index.

[0099] Step 2: Get the table name, number of table records, and index information of all tables in the database.

[0100] Specifically, the metadata of the database can be read to obtain a list of all tables and index information in the database, the current number of records in all tables can be obtained, and three HashMap data structures for storing parsed data can be initialized.

[0101] Step 3: Start database playback, parse the playback information, count the access information of each table and temporarily store it in memory.

[0102] In one embodiment, Figure 4 Flowchart of a method for obtaining access information according to Embodiment 3 of the present invention. In this embodiment, the access method corresponding to the table, the number of accesses to each table for each access method, the total number of accesses to the table, the total number of records in the table and the number of records per unit time, and the query access count for the index query field are all access information.

[0103] Step 3.1: Determine whether the playback information has been processed. If so, proceed to step 4; otherwise, continue.

[0104] Step 3.2: Get a SQL statement for database access.

[0105] Step 3.3: Analyze the tables and access information involved in this SQL statement.

[0106] Step 3.4: Determine whether the table information designed by this SQL statement has been processed. If so, jump to step 3.1; otherwise, continue.

[0107] Step 3.5: Get the table and access information involved in this SQL statement, and determine whether it is a primary key Select (primary key query), a non-primary key Select (non-primary key query), or a full table scan Select (full table scan).

[0108] Step 3.6. Update the total number of table accesses in the HashMap that stores table access information by +1. If it is a primary key access, add +1 to the primary key access count of the table. If it is a non-primary key index access, add +1 to the non-primary key Select count. If it is a full table scan, add +1 to the full table scan query count.

[0109] Step 3.7. Determine whether this access to the table is a unique index access. If so, add 1 to the access count of the unique index. Then determine whether the access satisfies the index-only access condition, that is, whether the accessed field contains one or two fields that are not components of the unique index in addition to all the fields that make up a unique index. If so, update the table information in the HashMap that stores the unique index access information of the table. If the relationship between the unique index and the field already exists, add 1 to the query access count. If not, add a record and set the query access count to 1.

[0110] Step 3.8: Determine whether the current SQL execution time has crossed a time point compared to the previous SQL execution time. If so, it indicates a new time point. Read the table record count for each table from the database metadata and update the table record data for each table at the corresponding time point in the HashMap that stores each table's hourly record data. Then, skip to step 3.1 to continue.

[0111] Step 4: Calculate and obtain a list of tables and indexes of various types that can be optimized.

[0112] Specifically, step 4.1, obtain the memory table. In one embodiment, Figure 5 This is a flow chart of a method for obtaining a memory table according to the third embodiment of the present invention. Figure 5 As shown, the method includes:

[0113] Step 4.1.1: Determine whether all tables have been processed. If not, obtain access information for a table from the HashMap that stores access information about the table. Otherwise, terminate the process of obtaining the list of tables that can be stored in memory.

[0114] Step 4.1.2: Determine whether the total number of records in the table is less than the maximum number of records that can be stored in memory. If so, continue; otherwise, skip to step 4.1.1.

[0115] Step 4.1.3: Calculate the percentage of Select requests to the total number of accesses to the table.

[0116] Step 4.1.4: Determine whether the query percentage is higher than the minimum percentage of Select requests for in-memory tables. If so, mark the table as an in-memory table and skip to step 4.1.1. Otherwise, skip directly to step 4.1.1.

[0117] Step 4.2: Obtain a hashable organization table. In one embodiment, Figure 6 is a flow chart of a method for obtaining a hashable organization table according to the third embodiment of the present invention. Figure 6 As shown, the method includes:

[0118] Step 4.2.1: Determine whether all tables have been processed. If not, obtain access information of a table from the HashMap that stores table access information. Otherwise, terminate the process of obtaining the hashable table list.

[0119] Step 4.2.2: Determine whether the number of records in the table is greater than the minimum number of records in a hashable table. If so, continue; otherwise, skip to step 4.2.1.

[0120] Step 4.2.3: Calculate the percentage of primary key access requests to the total number of access requests for the table.

[0121] Step 4.2.4: Determine whether the primary key access percentage is higher than the minimum percentage of primary key accesses to total accesses for a hashable table. If so, mark the table as a hashable table and skip to step 4.2.1. Otherwise, skip directly to step 4.2.1.

[0122] Step 4.3: Obtain the mandatory index access table. In one embodiment, Figure 7 FIG. 1 is a flowchart of a method for obtaining a mandatory index access table according to a third embodiment of the present invention. Figure 7 As shown, the method includes:

[0123] Step 4.3.1: Determine whether all tables have been processed. If not, obtain the access information of a table from the HashMap that stores the table access information. Otherwise, terminate the process of obtaining the list of tables that can be accessed by forced indexes.

[0124] Step 4.3.2: Determine whether the number of records in the table is greater than the minimum number of records in the table that can be accessed by a mandatory index. If so, continue; otherwise, skip to step 4.3.1.

[0125] Step 4.3.3, obtain the record information of the table from the HashMap that stores the hourly table record data of each table.

[0126] Step 4.3.4: Traverse the table record data every hour to determine whether the table record has decreased significantly within a day. If so, continue; otherwise, skip to step 4.3.1.

[0127] Step 4.3.5: Calculate the percentage of full table scan access requests to the total access requests.

[0128] Step 4.3.6: Determine whether the percentage of full table scan access requests for the table is higher than the minimum percentage of full table scan access requests for the mandatory index table. If so, mark the table as a mandatory index access table and skip to step 4.3.1. Otherwise, skip directly to step 4.3.1.

[0129] Step 4.4: Get the index that can be accessed only by index. In one embodiment, Figure 8 is a flowchart of a method for obtaining an index that can be accessed only by an index according to Embodiment 3 of the present invention. Figure 8 As shown, the method includes:

[0130] Step 4.4.1. Determine whether all unique index access information has been processed. If not, obtain access information of a unique index from the HashMap that stores the unique index access information of the table. Otherwise, terminate the process of obtaining the index-only access list.

[0131] Step 4.4.2: traverse the access information array of the unique index to obtain the maximum access count and corresponding field information of the access record in the index-only access count.

[0132] Step 4.4.3: Calculate the percentage of index-only accesses to the total number of accesses to the index.

[0133] Step 4.4.4: Determine whether the percentage is greater than the percentage of index-only accesses to total index accesses. If so, mark the index as index-only accessable and jump to step 4.4.1; otherwise, jump directly to step 4.4.1.

[0134] Step 5: Adjust the data table attributes and indexes according to the optimization list selected by the user.

[0135] For example, the following table or index lists with the following access conditions are filtered out, and corresponding optimization suggestions are provided for reference by database maintenance personnel in optimizing the system.

[0136] 1. The table has a small number of records, and the access method is basically query, with almost no update operations;

[0137] 2. The number of table records increases rapidly, but the number of table records changes periodically. Queries on the table are almost always single accesses or accesses based on the primary key.

[0138] 3. The number of records in the table is very large, almost unchanged or grows very slowly, and access to the table is almost always based on the primary key;

[0139] 4. The query on the table is scanned through the unique index, but the query returns not only the fields that make up the unique index, but also a non-index field.

[0140] The embodiment of the present invention obtains access information of each table and index in the database based on the metadata information of the database and the access information of each table in the database obtained from the database playback, and gives optimization suggestions by classification, thereby reducing the workload of database operation and maintenance personnel and improving work efficiency. It helps operation and maintenance personnel to improve the performance of the database by only modifying the properties of the table or index without understanding the business logic, modifying the table structure, or requiring developers to adjust the program code.

[0141] Example 4

[0142] Figure 9 FIG. 1 is a structural diagram of a data table classification device according to the third embodiment of the present invention. Figure 9 As shown, the device includes: a list determination module 91, a number determination module 92, a record number statistics module 93 and a category determination module 94.

[0143] The list determination module 91 is used to obtain a list of tables in the database and determine the current number of records in each table in the list of tables;

[0144] The number determination module 92 is used to start the database playback task to obtain data playback information, determine the access mode corresponding to the table involved in the SQL statement in the data playback information, determine the number of accesses to each table for each access mode and the total number of accesses to the table;

[0145] Record number statistics module 93, used to determine the execution time of the SQL statement, and determine the total number of records in the table and the number of records per unit time according to the execution time and the current number of records;

[0146] The category determination module 94 is used to determine the category of each table based on the preset table category identification rules, the total number of accesses, the number of accesses, the total number of records and the number of records per unit time.

[0147] The technical solution of the embodiment of the present invention is to obtain a list of tables in a database through a list determination module, determine the current number of records of each table in the table list, the number determination module starts a database playback task to obtain data playback information, the record number statistics module determines the access method corresponding to the table involved in the SQL statement in the data playback information, determines the number of accesses to each table for each access method and the total number of accesses to the table, determines the execution time of the SQL statement, and determines the total number of records and the number of records per unit time of the table based on the execution time and the current number of records. The category determination module determines the category of each table based on preset table category identification rules, the total number of accesses, the number of accesses, the total number of records and the number of records per unit time, thereby realizing automatic identification of the category of the table, avoiding the problem that manual identification of the category of the table is difficult and prone to errors, and improving the user experience.

[0148] In one embodiment, a data table classification device further includes:

[0149] An index determination module, used to determine index information of each table in the table list;

[0150] A query number determination module is used to update the index query field according to the access mode and index information, and determine the query access number of each index query field;

[0151] A sum determination module is used to determine the index query field corresponding to the maximum query access count as the target field, and the sum of the query access counts of all index query fields;

[0152] The index-only determination module is used to determine the ratio of the maximum query access times to the total as the query ratio. When the query ratio is determined to be greater than a preset index-only access minimum percentage, the target field is determined to be index-only accessible.

[0153] In one embodiment, the query number determination module is specifically configured to:

[0154] When it is determined that the access method is through index access, it is determined whether the index access is a unique index access;

[0155] If so, determine whether the index access field of the index access contains only index information for the table;

[0156] If the index access field does not contain only index information of the table, confirm that the index access field satisfies the index access-only condition, and determine whether the field information in the index access field other than the index information is a pre-saved index query field;

[0157] If so, the query access count of the index query field is updated; if not, the field information is saved as a new index query field, and a new query access count of the index query field is generated.

[0158] In one embodiment, the list determination module 91 includes:

[0159] The list determination unit is used to connect to the database, query the table list in the metadata of the database, and determine the current record number of each table in the table list.

[0160] In one embodiment, the number determination module 92 includes:

[0161] A method field extraction unit is used to extract the execution plan of the SQL statement in the data playback information and determine the access method field of the execution plan;

[0162] An access mode determination unit, configured to determine an access mode corresponding to a table according to an access mode field; wherein the access modes include primary key query, non-primary key query, and full table scan;

[0163] The number determination unit is used to record the number of accesses of each access mode in each table, and determine the sum of the number of accesses corresponding to each table as the total number of accesses of the table.

[0164] In one embodiment, the record number statistics module 93 includes:

[0165] A time period determination unit is used to extract the execution time of an SQL statement and determine whether the execution time is in the same time period as the execution time of the previous SQL statement;

[0166] The first recording unit is used for not updating the total number of records and the number of records per unit time in the table if yes;

[0167] The second recording unit is used to read the number of records of each table in the metadata of the database as the total number of records of the table if not, and determine the difference between the total number of records and the current number of records as the number of records per unit time.

[0168] In one embodiment, the category determination module 94 includes:

[0169] A data extraction unit is used to extract the maximum number of records in a memory-capable table, the preset minimum percentage of a memory-capable table, the minimum number of records in a hashable table, the preset minimum percentage of a hashable table, the minimum number of records in a forced index access table, and the preset minimum percentage of a forced index access table from the preset table category identification rules;

[0170] a first category determination unit configured to, when it is determined that the total number of records in the table is less than the maximum number of records in the memory-capable table, determine the sum of the number of accesses for primary key queries and the number of accesses for non-primary key queries of the table, determine a ratio of the sum of the number of accesses to the total number of accesses as a first ratio, and determine that the table is a memory-capable table when it is determined that the first ratio is greater than a preset minimum percentage of memory-capable tables;

[0171] a second category determination unit, configured to, when it is determined that the total number of records in the table is greater than the minimum number of records in the hashable table, determine a ratio of the number of accesses to the primary key query of the table to the total number of accesses as a second ratio, and determine that the table is a hashable table when it is determined that the second ratio is greater than a preset minimum percentage of hashable tables;

[0172] The third category determination unit is used to determine whether the difference in the number of records per unit time within the preset time is greater than a preset threshold when it is determined that the number of records per unit time is greater than the minimum number of records in the forced index access table. If so, the ratio of the number of accesses of the full disk scan of the table to the total number of accesses is determined as the third ratio. When it is determined that the third ratio is greater than the preset minimum percentage of the forced index access table, the table is determined to be a forced index access table.

[0173] The data table classification device provided in the embodiment of the present invention can execute the data table classification method provided in any embodiment of the present invention, and has the corresponding functional modules and beneficial effects of the execution method.

[0174] Example 5

[0175] Figure 10 1 is a schematic diagram of the structure of an electronic device that implements a data table classification method of an embodiment of the present invention. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices (such as helmets, glasses, watches, etc.) and other similar computing devices. The components shown herein, their connections and relationships, and their functions are only examples and are not intended to limit the implementation of the present invention described and / or required herein.

[0176] like Figure 10 As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12, a random access memory (RAM) 13, etc., which is communicatively connected to the at least one processor 11. The memory stores a computer program that can be executed by the at least one processor. The processor 11 can perform various appropriate actions and processes according to the computer program stored in the read-only memory (ROM) 12 or the computer program loaded from the storage unit 18 into the random access memory (RAM) 13. Various programs and data required for the operation of the electronic device 10 can also be stored in the RAM 13. The processor 11, ROM 12, and RAM 13 are connected to each other via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0177] Multiple components in the electronic device 10 are connected to the I / O interface 15, including an input unit 16, such as a keyboard, a mouse, etc.; an output unit 17, such as various types of displays, speakers, etc.; a storage unit 18, such as a magnetic disk, an optical disk, etc.; and a communication unit 19, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 19 allows the electronic device 10 to exchange information / data with other devices via a computer network such as the Internet and / or various telecommunication networks.

[0178] The processor 11 can be any general-purpose and / or specialized processing component with processing and computing capabilities. Some examples of the processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various specialized artificial intelligence (AI) computing chips, various processors that run machine learning model algorithms, a digital signal processor (DSP), and any appropriate processor, controller, microcontroller, etc. The processor 11 executes the various methods and processes described above, such as a data table classification method.

[0179] In some embodiments, a data table classification method can be implemented as a computer program, which is tangibly contained in a computer-readable storage medium, such as the storage unit 18. In some embodiments, part or all of the computer program can be loaded and / or installed on the electronic device 10 via the ROM 12 and / or the communication unit 19. When the computer program is loaded into the RAM 13 and executed by the processor 11, one or more steps of the data table classification method described above can be performed. Alternatively, in other embodiments, the processor 11 can be configured to perform a data table classification method in any other appropriate manner (for example, by means of firmware).

[0180] Various embodiments of the systems and techniques described herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), system-on-chip systems (SOCs), programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments can include being implemented in one or more computer programs that are executable and / or interpreted on a programmable system comprising at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.

[0181] Computer programs for implementing the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when the computer program is executed by the processor, the functions / operations specified in the flowcharts and / or block diagrams are implemented. The computer program may be executed entirely on the machine, partially on the machine, as a stand-alone software package, partially on the machine and partially on a remote machine, or entirely on a remote machine or server.

[0182] In the context of the present invention, computer-readable storage medium can be a tangible medium that can contain or store a computer program for use by an instruction execution system, device or equipment or used in combination with an instruction execution system, device or equipment. Computer-readable storage medium can include but is not limited to electronic, magnetic, optical, electromagnetic, infrared or semiconductor systems, devices or equipment, or any suitable combination of the foregoing. Alternatively, computer-readable storage medium can be a machine-readable signal medium. A more specific example of a machine-readable storage medium can include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0183] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user can provide input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, the feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including acoustic input, voice input, or tactile input).

[0184] The systems and techniques described herein can be implemented in a computing system that includes back-end components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes front-end components (e.g., a user computer with a graphical user interface or web browser through which a user can interact with implementations of the systems and techniques described herein), or a computing system that includes any combination of such back-end components, middleware components, or front-end components. The components of the system can be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.

[0185] A computing system may include clients and servers. The clients and servers are typically remote from each other and typically interact via a communication network. This client-server relationship arises through computer programs running on the respective computers, creating a client-server relationship. The server may be a cloud server, also known as a cloud computing server or cloud host. This server is a hosting product within the cloud computing service ecosystem that addresses the management difficulties and limited scalability of traditional physical hosting and VPS services.

[0186] It should be understood that the various forms of the processes shown above can be used to reorder, add, or delete steps. For example, the steps described in the present invention can be performed in parallel, sequentially, or in a different order, as long as the desired results of the technical solution of the present invention can be achieved. This is not limited herein.

[0187] The above specific embodiments do not limit the scope of protection of the present invention. Those skilled in the art will appreciate that various modifications, combinations, sub-combinations, and substitutions may be made based on design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention are intended to be included within the scope of protection of the present invention.

Claims

1. A data table classification method, characterized in that: include: Get a list of tables in the database and determine the current number of records in each table in the list; Start a database playback task to obtain data playback information, determine the access mode corresponding to the table involved in the SQL statement in the data playback information, determine the number of accesses to each table for each access mode and the total number of accesses to the table; Determine the execution time of the SQL statement, and determine the total number of records and the number of records per unit time of the table according to the execution time and the current number of records; The category of each table is determined based on a preset table category identification rule, the total number of accesses, the number of accesses, the total number of records, and the number of records per unit time.

2. The method according to claim 1, characterized in that The method further comprises: Determining index information for each table in the table list; updating the index query fields according to the access mode and the index information, and determining the query access count of each index query field; Determine the index query field corresponding to the maximum query access count as the target field, and the sum of the query access counts of all index query fields; A ratio of the maximum query access number to the total is determined as a query ratio, and when it is determined that the query ratio is greater than a preset index-only access minimum percentage, the target field is determined to be index-only accessible.

3. The method according to claim 2, characterized in that The updating of the index query fields according to the access mode and the index information, and determining the query access count of each index query field, includes: When it is determined that the access mode is index access, determining whether the index access is unique index access; If so, determining whether the index access field of the index access only contains index information of the table; If the index access field does not only include index information of the table, confirming that the index access field meets the index access-only condition, and determining whether field information in the index access field other than the index information is a pre-stored index query field; If so, the query access count of the index query field is updated; if not, the field information is saved as a new index query field, and a new query access count of the index query field is generated.

4. The method according to claim 1, wherein The step of obtaining a table list in a database and determining the current number of records in each table in the table list includes: A database is connected, a table list is queried in metadata of the database, and a current record count of each table in the table list is determined.

5. The method according to claim 1, characterized in that Determining the access mode corresponding to the table involved in the SQL statement in the data playback information, and determining the number of accesses to each table for each access mode and the total number of accesses to the table, includes: Extracting the execution plan of the SQL statement in the data playback information, and determining the access mode field of the execution plan; Determining the access mode corresponding to the table according to the access mode field; wherein the access mode includes primary key query, non-primary key query and full table scan; Record the number of accesses for each access method in each table, and determine the sum of the number of accesses corresponding to each table as the total number of accesses for the table.

6. The method according to claim 1, characterized in that Determining the execution time of the SQL statement, and determining the total number of records and the number of records per unit time of the table according to the execution time and the current number of records, includes: Extract the execution time of the SQL statement and confirm whether the execution time is in the same period as the execution time of the previous SQL statement; If yes, the total number of records and the number of records per unit time in the table are not updated; If not, the number of records of each table is read from the metadata of the database as the total number of records of the table, and the difference between the total number of records and the current number of records is determined as the number of records per unit time.

7. The method according to claim 1, characterized in that The determining the category of each table based on a preset table category identification rule, the total number of accesses, the number of accesses, the total number of records, and the number of records per unit time includes: Extracting the maximum number of records in the memory-capable table, the preset minimum percentage of the memory-capable table, the minimum number of records in the hashable table, the preset minimum percentage of the hashable table, the minimum number of records in the forced index access table, and the preset minimum percentage of the forced index access table from the preset table category identification rules; When it is determined that the total number of records in the table is less than the maximum number of records in the memory-capable table, determining the sum of the number of accesses for primary key queries and the number of accesses for non-primary key queries of the table, determining a ratio of the sum of the number of accesses to the total number of accesses as a first ratio, and when it is determined that the first ratio is greater than the preset minimum percentage of memory-capable tables, determining that the table is a memory-capable table; When it is determined that the total number of records in the table is greater than the minimum number of records in the hashable table, determining a ratio of the number of accesses to the primary key query of the table to the total number of accesses as a second ratio, and when it is determined that the second ratio is greater than the preset minimum percentage of the hashable table, determining that the table is a hashable table; When it is determined that the number of records per unit time is greater than the minimum number of records in the forced index access table, determine whether the difference in the number of records per unit time within the preset time is greater than the preset threshold. If so, determine the ratio of the number of accesses of the full disk scan of the table to the total number of accesses as the third ratio. When it is determined that the third ratio is greater than the preset minimum percentage of the forced index access table, determine that the table is a forced index access table.

8. A data table classification device, characterized in that: include: A list determination module is used to obtain a list of tables in the database and determine the current number of records in each table in the list of tables; A number determination module is used to start a database playback task to obtain data playback information, determine the access mode corresponding to the table involved in the SQL statement in the data playback information, determine the number of accesses to each table for each access mode and the total number of accesses to the table; A record number statistics module is used to determine the execution time of the SQL statement, and determine the total number of records in the table and the number of records per unit time according to the execution time and the current number of records; The category determination module is configured to determine the category of each table based on a preset table category identification rule, the total number of accesses, the number of accesses, the total number of records, and the number of records per unit time.

9. An electronic device, characterized in that: The electronic device comprises: at least one processor; and a memory communicatively connected to the at least one processor; wherein, The memory stores a computer program executable by the at least one processor. The computer program is executed by the at least one processor to enable the at least one processor to perform a data table classification method according to any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that The computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement a data table classification method according to any one of claims 1 to 7 when executed.

Images