CN117609075A - Index use efficiency evaluation method and device, electronic equipment and storage medium - Google Patents

Index use efficiency evaluation method and device, electronic equipment and storage medium Download PDF

Info

Publication number
CN117609075A
CN117609075A CN202311649465.5A CN202311649465A CN117609075A CN 117609075 A CN117609075 A CN 117609075A CN 202311649465 A CN202311649465 A CN 202311649465A CN 117609075 A CN117609075 A CN 117609075A
Authority
CN
China
Prior art keywords
sql
index
sql statement
use efficiency
execution plan
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Pending
Application number
CN202311649465.5A
Other languages
Chinese (zh)
Inventor
申升星
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Agricultural Bank of China
Original Assignee
Agricultural Bank of China
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Agricultural Bank of China filed Critical Agricultural Bank of China
Priority to CN202311649465.5A priority Critical patent/CN117609075A/en
Publication of CN117609075A publication Critical patent/CN117609075A/en
Pending legal-status Critical Current

Links

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F11/00—Error detection; Error correction; Monitoring
    • G06F11/36—Preventing errors by testing or debugging software
    • G06F11/3604—Software analysis for verifying properties of programs
    • G06F11/3608—Software analysis for verifying properties of programs using formal methods, e.g. model checking, abstract interpretation
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/242—Query formulation
    • G06F16/2433—Query languages
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/28—Databases characterised by their database models, e.g. relational or object models
    • G06F16/284—Relational databases

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Databases & Information Systems (AREA)
  • Physics & Mathematics (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Data Mining & Analysis (AREA)
  • Quality & Reliability (AREA)
  • Mathematical Physics (AREA)
  • Computational Linguistics (AREA)
  • Computer Hardware Design (AREA)
  • Software Systems (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

The application discloses an index use efficiency evaluation method, an index use efficiency evaluation device, electronic equipment and a storage medium; the method comprises the following steps: extracting one or more SQL sentences from the target program; generating a corresponding execution plan for each SQL statement; and calculating index use efficiency of each SQL statement based on the execution plan corresponding to each SQL statement. According to the method and the device for evaluating the index use efficiency of the SQL sentences, the index use efficiency of each SQL sentence can be automatically evaluated, so that SQL sentences with low index use efficiency can be timely found, and the purposes of reducing development cost and improving development efficiency are achieved.

Description

Index use efficiency evaluation method and device, electronic equipment and storage medium
Technical Field
The embodiment of the application relates to the technical field of databases, in particular to an index use efficiency evaluation method, an index use efficiency evaluation device, electronic equipment and a storage medium.
Background
Software performance testing finds that one of the main reasons for inefficiency in software operation is the quality of SQL statements. The quality of an SQL statement refers to whether it can efficiently and accurately complete the required database operations, and whether it has good maintainability, extensibility and security.
In the prior art, the SQL statements include SQL statements that use an index and SQL statements that do not use an index. For SQL statements that do not use an index, a full table scan is required when executing the statement, and performance is often low and can be found quickly. For SQL sentences using indexes, because the index distinction of the matched index fields is not high enough, the scanning range is too large, and the performance is low, so that a problematic SQL sentence is difficult to find in the situation. In addition, for a data table with insufficient paving data, if problems occur in SQL sentences using the data table, the problems are also difficult to check.
Disclosure of Invention
The application provides an index use efficiency evaluation method, an index use efficiency evaluation device, electronic equipment and a storage medium, which can automatically evaluate the index use efficiency of each SQL statement, so that SQL statements with low index use efficiency can be timely found, the purposes of reducing development cost and improving development efficiency are achieved.
In a first aspect, an embodiment of the present application provides a method for evaluating index usage efficiency, where the method includes:
extracting one or more SQL sentences from the target program;
generating a corresponding execution plan for each SQL statement;
and calculating index use efficiency of each SQL statement based on the execution plan corresponding to each SQL statement.
In a second aspect, an embodiment of the present application further provides an apparatus for evaluating index usage efficiency, where the apparatus includes: the device comprises an extraction module, a generation module and a calculation module; wherein,
the extraction module is used for extracting one or more SQL sentences from the target program;
the generating module is used for generating a corresponding execution plan for each SQL statement;
the calculation module is used for calculating the index use efficiency of each SQL statement based on the execution plan corresponding to each SQL statement.
In a third aspect, an embodiment of the present application provides an electronic device, including:
one or more processors;
a memory for storing one or more programs,
the one or more programs, when executed by the one or more processors, cause the one or more processors to implement the method for evaluating index use efficiency described in any embodiment of the present application.
In a fourth aspect, embodiments of the present application provide a storage medium having stored thereon a computer program that, when executed by a processor, implements the index use efficiency evaluation method described in any embodiment of the present application.
The embodiment of the application provides an index use efficiency evaluation method, an index use efficiency evaluation device, electronic equipment and a storage medium, wherein one or more SQL sentences are extracted from a target program; then generating a corresponding execution plan for each SQL statement; and calculating the index use efficiency of each SQL sentence based on the execution plan corresponding to each SQL sentence. That is, in the technical solution of the present application, the index use efficiency of each SQL statement may be calculated by analyzing the execution plan corresponding to each SQL statement. In the prior art, for the SQL statement using the index, because the index distinction of the matched index field is not high enough, the scanning range is too large, the performance is lower, and the problem SQL statement is difficult to find in this case. In addition, for a data table with insufficient paving data, if problems occur in SQL sentences using the data table, the problems are also difficult to check. Therefore, compared with the prior art, the index use efficiency evaluation method, device, electronic equipment and storage medium provided by the embodiment of the application can automatically evaluate the index use efficiency of each SQL statement, so that SQL statements with low index use efficiency can be found in time, and the purposes of reducing development cost and improving development efficiency are achieved; in addition, the technical scheme of the embodiment of the application is simple and convenient to realize, convenient to popularize and wider in application range.
Drawings
FIG. 1 is a first flow chart of an index use efficiency evaluation method according to an embodiment of the present disclosure;
FIG. 2 is a second flow chart of an evaluation method for index utilization efficiency according to an embodiment of the present disclosure;
FIG. 3 is a third flow chart of an evaluation method for index utilization efficiency according to an embodiment of the present disclosure;
fig. 4 is a schematic structural diagram of an evaluation device for index use efficiency according to an embodiment of the present disclosure;
fig. 5 is a schematic structural diagram of an electronic device according to an embodiment of the present application.
Detailed Description
The present application is described in further detail below with reference to the drawings and examples. It is to be understood that the specific embodiments described herein are merely illustrative of the application and not limiting thereof. It should be further noted that, for convenience of description, only some, but not all of the structures related to the present application are shown in the drawings.
Example 1
Fig. 1 is a schematic flow chart of a first procedure of an index usage efficiency evaluation method provided in an embodiment of the present application, where the method may be performed by an index usage efficiency evaluation device or electronic device, and the device or electronic device may be implemented by software and/or hardware, and the device or electronic device may be integrated into any intelligent device with a network communication function. As shown in fig. 1, the index use efficiency evaluation method may include the steps of:
s101, extracting one or more SQL sentences from the target program.
In this step, the electronic device may extract one or more SQL statements in the target program. Specifically, the electronic device may extract the SQL statement from the target program by one of the following methods: 1) The method adopts a character string cutting mode: if the SQL statement in the target program is stored in the form of a string, it can be extracted by using a string cutting method. For example, in Python, a character string may be divided into a list according to a specific separator using a split () method, and then a corresponding element is selected to extract an SQL statement. 2) The regular expression mode is adopted: regular expressions are a powerful pattern matching tool that can be used to extract strings of a specific format. The appropriate regular expressions can be used to match and extract SQL statements, and different programming languages and tools may have different regular expression grammars.
S102, generating a corresponding execution plan for each SQL statement.
In this step, the electronic device may generate a corresponding execution plan for each SQL statement; wherein the execution plan includes: the name of the data table used by the SQL, the name of the index used by the SQL, the number of fields in the data table, and the individual index fields in the data table. Specifically, when generating a corresponding execution plan for each SQL statement, one or more pieces of key information may be respectively lifted out of each SQL statement; then generating a generation command of an execution plan corresponding to each SQL statement according to the one or more pieces of key information; and generating the execution plan corresponding to each SQL sentence based on the generation command of the execution plan corresponding to each SQL sentence. Further, an execution plan may be generated using the expain command. Most database management systems provide an EXPLAIN command that can be used to display the execution plan of the SQL query statement. By executing the EXPLAIN command, detailed information of the execution order of the query statement, the index used, the connection type, and the like can be checked.
S103, calculating index use efficiency of each SQL sentence based on an execution plan corresponding to each SQL sentence.
In this step, the electronic device may calculate index usage efficiency of each SQL statement based on the execution plan corresponding to each SQL statement. Specifically, the electronic device may estimate, according to a rule of a service related to the target program, a unique value number of each index field in a data table used by each SQL statement; and then calculating the index use efficiency of each SQL statement according to the unique value number of each index field in the data table used by each SQL statement and the data quantity of the predetermined data table. In one embodiment, the electronic device may multiply the unique value numbers of all the index fields in the data table used by each SQL statement to obtain a product of the unique value numbers of all the index fields; and dividing the product of the unique value numbers of all the index fields by the data quantity of the data table to obtain the index use efficiency of each SQL statement.
According to the index use efficiency evaluation method provided by the embodiment of the application, one or more SQL sentences are extracted from a target program; then generating a corresponding execution plan for each SQL statement; and calculating the index use efficiency of each SQL sentence based on the execution plan corresponding to each SQL sentence. That is, in the technical solution of the present application, the index use efficiency of each SQL statement may be calculated by analyzing the execution plan corresponding to each SQL statement. In the prior art, for the SQL statement using the index, because the index distinction of the matched index field is not high enough, the scanning range is too large, the performance is lower, and the problem SQL statement is difficult to find in this case. In addition, for a data table with insufficient paving data, if problems occur in SQL sentences using the data table, the problems are also difficult to check. Therefore, compared with the prior art, the index use efficiency evaluation method provided by the embodiment of the application can automatically evaluate the index use efficiency of each SQL statement, so that SQL statements with low index use efficiency can be found in time, and the purposes of reducing development cost and improving development efficiency are achieved; in addition, the technical scheme of the embodiment of the application is simple and convenient to realize, convenient to popularize and wider in application range.
Example two
Fig. 2 is a second flowchart of an evaluation method of index use efficiency according to an embodiment of the present application. Further optimization and expansion based on the above technical solution can be combined with the above various alternative embodiments. As shown in fig. 2, the index use efficiency evaluation method may include the steps of:
s201, scanning the target program to obtain the starting position and the ending position of one or more SQL sentences.
In this step, the electronic device may scan the target program to obtain a start position and an end position of one or more SQL statements. In one embodiment, the electronic device can scan the object program for the start and end positions of the SQL statement by using the appropriate regular expressions. In a regular expression, the beginning and end of an SQL statement may be matched according to common features of the statement. For example, in most cases, the starting position of an SQL statement will typically be at the beginning of a key, such as SELECT, INSERT, UPDATE or DELETE. In another embodiment, if the target program uses a certain programming language, the grammar rules of the programming language used by the target program can be analyzed to determine the start position and the end position of the SQL sentence.
S202, extracting one or more SQL sentences from the target program according to the starting position and the ending position of the one or more SQL sentences.
In this step, the electronic device may extract the one or more SQL statements from the target program according to the start position and the end position of the one or more SQL statements. Specifically, the electronic device may extract the SQL statement according to the following steps: first, a start position and an end position of the SQL statement are determined. These locations may be found using a text editor or using a string function in a programming language. The start position is typically the first character of the SQL statement and the end position is the position after the last character. Then, extracting the substring of the SQL sentence through string interception or substring operation. Depending on the starting and ending positions, the substring may be extracted using a corresponding intercept function or method.
S203, respectively lifting one or more pieces of key information in each SQL sentence.
In this step, one or more pieces of key information are respectively lifted in each SQL sentence. Specifically, the electronic device may first determine a structure of a generation command of the execution plan; and then respectively lifting one or more pieces of key information in each SQL statement according to the structure of the generation command of the execution plan. For example, the execution plan generation command may be an expain command, which is a command for generating an execution plan of an SQL query statement. In most relational database systems, execution plans for SQL query statements can be viewed through the use of EXPLAIN commands. An execution plan is a detailed description of the operational steps taken by the database system in evaluating and executing query statements. It provides information on the order of execution of each step in the query, the index used, the connection type, the number of rows expected, etc., which helps to optimize and debug the query performance. The output column of the expain command may include: id: indicating the order of the operations, there will be different ids if there are sub-queries. select_type: representing the type of query, such as simple query, sub-query, federation, derivative, etc. table: the table name concerned is indicated. parts of: partition information representing a partition table. type: indicating the connection type of the table, including All, index, range, ref, etc. Possible_keys: representing the index that may be used in the query. key: representing the index actually used. key_len: representing the length of the index. ref: columns representing connection conditions. rows: representing the number of rows scanned. filtered: representing the percentage of rows filtered. Extra: representing additional information such as Using Index, using wheel, etc.
S204, generating a generation command of an execution plan corresponding to each SQL statement according to the one or more pieces of key information.
In this step, the electronic device may generate, according to the one or more pieces of key information, a generation command of an execution plan corresponding to each SQL statement. Specifically, each piece of key information of the SQL statement may be added to each position corresponding to the generation command of the execution plan, so as to obtain the generation command of the execution plan corresponding to the SQL statement.
S205, generating the execution plan corresponding to each SQL sentence based on the generation command of the execution plan corresponding to each SQL sentence.
S206, calculating index use efficiency of each SQL statement based on the execution plan corresponding to each SQL statement.
In this step, the electronic device may calculate index usage efficiency of each SQL statement based on the execution plan corresponding to each SQL statement. Specifically, the electronic device may estimate, according to a rule of a service related to the target program, a unique value number of each index field in a data table used by each SQL statement; and then calculating the index use efficiency of each SQL statement according to the unique value number of each index field in the data table used by each SQL statement and the data quantity of the predetermined data table. Further, the electronic device may multiply the unique value numbers of all the index fields in the data table used by each SQL statement to obtain a product of the unique value numbers of all the index fields; and dividing the product of the unique value numbers of all the index fields by the data quantity of the data table to obtain the index use efficiency of each SQL statement.
According to the index use efficiency evaluation method provided by the embodiment of the application, one or more SQL sentences are extracted from a target program; then generating a corresponding execution plan for each SQL statement; and calculating the index use efficiency of each SQL sentence based on the execution plan corresponding to each SQL sentence. That is, in the technical solution of the present application, the index use efficiency of each SQL statement may be calculated by analyzing the execution plan corresponding to each SQL statement. In the prior art, for the SQL statement using the index, because the index distinction of the matched index field is not high enough, the scanning range is too large, the performance is lower, and the problem SQL statement is difficult to find in this case. In addition, for a data table with insufficient paving data, if problems occur in SQL sentences using the data table, the problems are also difficult to check. Therefore, compared with the prior art, the index use efficiency evaluation method provided by the embodiment of the application can automatically evaluate the index use efficiency of each SQL statement, so that SQL statements with low index use efficiency can be found in time, and the purposes of reducing development cost and improving development efficiency are achieved; in addition, the technical scheme of the embodiment of the application is simple and convenient to realize, convenient to popularize and wider in application range.
Example III
Fig. 3 is a third flow chart of an evaluation method for index use efficiency according to an embodiment of the present application. Further optimization and expansion based on the above technical solution can be combined with the above various alternative embodiments. As shown in fig. 3, the index use efficiency evaluation method may include the steps of:
s301, one or more SQL sentences are extracted from the target program.
S302, respectively lifting one or more pieces of key information in each SQL sentence.
S303, generating a generation command of an execution plan corresponding to each SQL statement according to the one or more pieces of key information.
S304, generating the execution plan corresponding to each SQL sentence based on the generation command of the execution plan corresponding to each SQL sentence.
S305, estimating the unique value number of each index field in the data table used by each SQL statement according to the rule of the service related to the target program.
In this step, the electronic device may estimate, according to the rule of the service related to the target program, the unique number of values of each index field in the data table used by each SQL statement. Specifically, the estimating of the unique value number of the index field in the data table used by the SQL statement may be performed according to the following steps: 1) The business rules related to the target program are acquired, and the business rules comprise a data table, an association relation, business logic and the like related to the target program. 2) Analyzing SQL sentences: the SQL statements used in the target program, in particular, the query statements relating to the data table are analyzed to obtain the query conditions, connection conditions, returned fields, etc. of the SQL statements. 3) Obtaining the structure of a data table: the structure of the data table used by each SQL statement includes fields and indexes of the table, etc. 4) Selection of an index field: based on the business rules and query conditions, the corresponding index fields are selected, which are typically the fields that are often used for queries and filtering. 5) Estimating the unique value number: and counting the unique value number of each index field in the data table according to the selected index field. The evaluation may be performed by looking at statistics of the data table, executing an SQL statement to perform COUNT (DISTINCT) operations on the fields, or using other suitable methods.
S306, calculating the index use efficiency of each SQL statement according to the unique value number of each index field in the data table used by each SQL statement and the data quantity of the predetermined data table.
In this step, the electronic device may calculate the index usage efficiency of each SQL statement according to the number of unique values of each index field in the data table used by each SQL statement and the predetermined data amount of the data table. Specifically, the electronic device may firstMultiplying the unique value numbers of all the index fields in the data table used by each SQL statement to obtain the product of the unique value numbers of all the index fields; and dividing the product of the unique value numbers of all the index fields by the data quantity of the data table to obtain the index use efficiency of each SQL statement. In one embodiment, the electronic device can calculate the index usage efficiency of each SQL statement according to the following formula: s=count (disttinct D1) ×count (disttinct D2) × … ×count (disttinct D) N ) Count (); s expresses the index use efficiency of each SQL statement; d1, D2, …, D N Respectively representing a first index field, a second index field, … and an nth index field; n is a natural number greater than or equal to 1; count (disttinct D1), count (disttinct D2), …, count (disttinct D N ) The unique value number of the first index field, the unique value number of the second index field, … and the unique value number of the Nth index field are respectively represented; count (x) represents the data amount of the predetermined data table.
In a specific embodiment of the present application, the electronic device may further screen out SQL statements with index use efficiency lower than a preset threshold value from all the SQL statements according to index use efficiency of each SQL statement; and then generating a performance optimization reminding file according to SQL sentences with index use efficiency lower than a preset threshold value.
According to the method and the device, the SQL is scanned for the target program, the execution plan is analyzed, the data volume of the database table is estimated, the index use efficiency is generated, the problem SQL can be positioned in the early stage of program development, the optimization suggestion is given, the development cost is reduced, and the development efficiency is improved.
According to the index use efficiency evaluation method provided by the embodiment of the application, one or more SQL sentences are extracted from a target program; then generating a corresponding execution plan for each SQL statement; and calculating the index use efficiency of each SQL sentence based on the execution plan corresponding to each SQL sentence. That is, in the technical solution of the present application, the index use efficiency of each SQL statement may be calculated by analyzing the execution plan corresponding to each SQL statement. In the prior art, for the SQL statement using the index, because the index distinction of the matched index field is not high enough, the scanning range is too large, the performance is lower, and the problem SQL statement is difficult to find in this case. In addition, for a data table with insufficient paving data, if problems occur in SQL sentences using the data table, the problems are also difficult to check. Therefore, compared with the prior art, the index use efficiency evaluation method provided by the embodiment of the application can automatically evaluate the index use efficiency of each SQL statement, so that SQL statements with low index use efficiency can be found in time, and the purposes of reducing development cost and improving development efficiency are achieved; in addition, the technical scheme of the embodiment of the application is simple and convenient to realize, convenient to popularize and wider in application range.
Example IV
Fig. 4 is a schematic structural diagram of an evaluation device for index use efficiency according to an embodiment of the present application. As shown in fig. 4, the index use efficiency evaluation device includes: an extraction module 401, a generation module 402, and a calculation module 403; wherein,
the extracting module 401 is configured to extract one or more SQL statements in the target program;
the generating module 402 is configured to generate a corresponding execution plan for each SQL statement;
the calculating module 403 is configured to calculate index usage efficiency of each SQL statement based on an execution plan corresponding to each SQL statement.
The index use efficiency evaluation device can execute the method provided by any embodiment of the application, and has the corresponding functional modules and beneficial effects of executing the method. Technical details not described in detail in this embodiment can be seen in the evaluation method of index use efficiency provided in any embodiment of the present application.
Example five
Fig. 5 is a schematic structural diagram of an electronic device according to an embodiment of the present application. Fig. 5 illustrates a block diagram of an exemplary electronic device suitable for use in implementing embodiments of the present application. The electronic device 12 shown in fig. 5 is merely an example and should not be construed as limiting the functionality and scope of use of embodiments of the present application.
As shown in fig. 5, the electronic device 12 is in the form of a general purpose computing device. Components of the electronic device 12 may include, but are not limited to: one or more processors or processing units 16, a system memory 28, a bus 18 that connects the various system components, including the system memory 28 and the processing units 16.
Bus 18 represents one or more of several types of bus structures, including a memory bus or memory controller, a peripheral bus, an accelerated graphics port, a processor, and a local bus using any of a variety of bus architectures. By way of example, and not limitation, such architectures include Industry Standard Architecture (ISA) bus, micro channel architecture (MAC) bus, enhanced ISA bus, video Electronics Standards Association (VESA) local bus, and Peripheral Component Interconnect (PCI) bus.
Electronic device 12 typically includes a variety of computer system readable media. Such media can be any available media that is accessible by electronic device 12 and includes both volatile and nonvolatile media, removable and non-removable media.
The system memory 28 may include computer system readable media in the form of volatile memory, such as Random Access Memory (RAM) 30 and/or cache memory 32. The electronic device 12 may further include other removable/non-removable, volatile/nonvolatile computer system storage media. By way of example only, storage system 34 may be used to read from or write to non-removable, nonvolatile magnetic media (not shown in FIG. 5, commonly referred to as a "hard disk drive"). Although not shown in fig. 5, a magnetic disk drive for reading from and writing to a removable non-volatile magnetic disk (e.g., a "floppy disk"), and an optical disk drive for reading from or writing to a removable non-volatile optical disk (e.g., a CD-ROM, DVD-ROM, or other optical media) may be provided. In such cases, each drive may be coupled to bus 18 through one or more data medium interfaces. Memory 28 may include at least one program product having a set (e.g., at least one) of program modules configured to carry out the functions of the embodiments of the present application.
A program/utility 40 having a set (at least one) of program modules 42 may be stored in, for example, memory 28, such program modules 42 including, but not limited to, an operating system, one or more application programs, other program modules, and program data, each or some combination of which may include an implementation of a network environment. Program modules 42 generally perform the functions and/or methods in the embodiments described herein.
The electronic device 12 may also communicate with one or more external devices 14 (e.g., keyboard, pointing device, display 24, etc.), one or more devices that enable a user to interact with the electronic device 12, and/or any devices (e.g., network card, modem, etc.) that enable the electronic device 12 to communicate with one or more other computing devices. Such communication may occur through an input/output (I/O) interface 22. Also, the electronic device 12 may communicate with one or more networks such as a Local Area Network (LAN), a Wide Area Network (WAN) and/or a public network, such as the Internet, through a network adapter 20. As shown, the network adapter 20 communicates with other modules of the electronic device 12 over the bus 18. It should be appreciated that although not shown in fig. 5, other hardware and/or software modules may be used in connection with electronic device 12, including, but not limited to: microcode, device drivers, redundant processing units, external disk drive arrays, RAID systems, tape drives, data backup storage systems, and the like.
The processing unit 16 executes various functional applications and data processing by running programs stored in the system memory 28, for example, implementing the index use efficiency evaluation method provided by the embodiment of the present application.
Example six
Embodiments of the present application provide a computer storage medium.
Any combination of one or more computer readable media may be employed in the computer readable storage media of the embodiments herein. The computer readable medium may be a computer readable signal medium or a computer readable storage medium. The computer readable storage medium can be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or a combination of any of the foregoing. More specific examples (a non-exhaustive list) of the computer-readable storage medium would include the following: an electrical connection having one or more wires, a portable computer diskette, 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 disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In this document, a computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device.
The computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, either in baseband or as part of a carrier wave. Such a propagated data signal may take any of a variety of forms, including, but not limited to, electro-magnetic, optical, or any suitable combination of the foregoing. A computer readable signal medium may also be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device.
Program code embodied on a computer readable medium may be transmitted using any appropriate medium, including but not limited to wireless, wireline, optical fiber cable, RF, etc., or any suitable combination of the foregoing.
Computer program code for carrying out operations of the present application may be written in one or more programming languages, including an object oriented programming language such as Java, smalltalk, C ++ and conventional procedural programming languages, such as the "C" programming language or similar programming languages. The program code may execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server. In the case of a remote computer, the remote computer may be connected to the user's computer through any kind of network, including a Local Area Network (LAN) or a Wide Area Network (WAN), or may be connected to an external computer (for example, through the Internet using an Internet service provider).
Note that the above is only a preferred embodiment of the present application and the technical principle applied. Those skilled in the art will appreciate that the present application is not limited to the particular embodiments described herein, but is capable of numerous obvious changes, rearrangements and substitutions as will now become apparent to those skilled in the art without departing from the scope of the present application. Therefore, while the present application has been described in connection with the above embodiments, the present application is not limited to the above embodiments, but may include many other equivalent embodiments without departing from the spirit of the present application, the scope of which is defined by the scope of the appended claims.

Claims (10)

1. A method of evaluating efficiency of index use, the method comprising:
extracting one or more SQL sentences from the target program;
generating a corresponding execution plan for each SQL statement;
and calculating index use efficiency of each SQL statement based on the execution plan corresponding to each SQL statement.
2. The method of claim 1, wherein the execution plan comprises: the name of the data table used by the SQL, the name of the index used by the SQL, the number of fields in the data table and the index fields in the data table.
3. The method of claim 1, wherein extracting one or more SQL statements in the target program comprises:
scanning the target program to obtain the starting position and the ending position of the one or more SQL sentences;
and extracting the one or more SQL sentences in the target program according to the starting position and the ending position of the one or more SQL sentences.
4. The method of claim 1, wherein generating a corresponding execution plan for each SQL statement comprises:
one or more key information is respectively lifted in each SQL sentence;
generating a generation command of an execution plan corresponding to each SQL statement according to the one or more pieces of key information;
and generating the execution plan corresponding to each SQL sentence based on the generation command of the execution plan corresponding to each SQL sentence.
5. The method of claim 2, wherein calculating the index usage efficiency of each SQL statement based on the execution plan corresponding to each SQL statement comprises:
estimating the unique value number of each index field in a data table used by each SQL statement according to the rule of the service related to the target program;
and calculating the index use efficiency of each SQL statement according to the unique value number of each index field in the data table used by each SQL statement and the predetermined data volume of the data table.
6. The method according to claim 5, wherein calculating the index efficiency of each SQL statement according to the unique number of values of each index field in the data table used by each SQL statement and the predetermined data amount of the data table comprises:
multiplying the unique value numbers of all the index fields in the data table used by each SQL statement to obtain the product of the unique value numbers of all the index fields;
dividing the product of the unique value numbers of all the index fields by the data volume of the data table to obtain the index use efficiency of each SQL statement.
7. The method according to claim 1, wherein the method further comprises:
according to the index use efficiency of each SQL sentence, screening out SQL sentences with index use efficiency lower than a preset threshold value from all SQL sentences;
and generating a performance optimization reminding file according to the SQL sentences with the index use efficiency lower than a preset threshold value.
8. An index use efficiency evaluation device, characterized in that the device comprises: the device comprises an extraction module, a generation module and a calculation module; wherein,
the extraction module is used for extracting one or more SQL sentences from the target program;
the generating module is used for generating a corresponding execution plan for each SQL statement;
the calculation module is used for calculating the index use efficiency of each SQL statement based on the execution plan corresponding to each SQL statement.
9. An electronic device, comprising:
one or more processors;
a memory for storing one or more programs,
when the one or more programs are executed by the one or more processors, the one or more processors are caused to implement the method of evaluating index use efficiency of any of claims 1 to 7.
10. A storage medium having stored thereon a computer program, wherein the program when executed by a processor implements the index use efficiency evaluation method according to any one of claims 1 to 7.
CN202311649465.5A 2023-12-04 2023-12-04 Index use efficiency evaluation method and device, electronic equipment and storage medium Pending CN117609075A (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202311649465.5A CN117609075A (en) 2023-12-04 2023-12-04 Index use efficiency evaluation method and device, electronic equipment and storage medium

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202311649465.5A CN117609075A (en) 2023-12-04 2023-12-04 Index use efficiency evaluation method and device, electronic equipment and storage medium

Publications (1)

Publication Number Publication Date
CN117609075A true CN117609075A (en) 2024-02-27

Family

ID=89949612

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202311649465.5A Pending CN117609075A (en) 2023-12-04 2023-12-04 Index use efficiency evaluation method and device, electronic equipment and storage medium

Country Status (1)

Country Link
CN (1) CN117609075A (en)

Similar Documents

Publication Publication Date Title
KR102485179B1 (en) Method, device, electronic device and computer storage medium for determining description information
CN110457277B (en) Service processing performance analysis method, device, equipment and storage medium
CN111400338B (en) SQL optimization method, device, storage medium and computer equipment
CN103186639B (en) Data creation method and system
JP5791149B2 (en) Computer-implemented method, computer program, and data processing system for database query optimization
CN109947791B (en) Database statement optimization method, device, equipment and storage medium
CN112000773B (en) Search engine technology-based data association relation mining method and application
CN109471851B (en) Data processing method, device, server and storage medium
CN112445775B (en) Fault analysis method, device, equipment and storage medium of photoetching machine
CN115061721A (en) Report generation method and device, computer equipment and storage medium
CN112445875B (en) Data association and verification method and device, electronic equipment and storage medium
US8572062B2 (en) Indexing documents using internal index sets
CN112181490B (en) Method, device, equipment and medium for identifying function category in function point evaluation method
CN111553556A (en) Business data analysis method and device, computer equipment and storage medium
CN111062208B (en) File auditing method, device, equipment and storage medium
CN110874364B (en) Query statement processing method, device, equipment and storage medium
CN110008448B (en) Method and device for automatically converting SQL code into Java code
CN117609075A (en) Index use efficiency evaluation method and device, electronic equipment and storage medium
CN113553309A (en) Log template determination method and device, electronic equipment and storage medium
CN110134435B (en) Code repair case acquisition method, device, equipment and storage medium
CN109918367B (en) Structured data cleaning method and device, electronic equipment and storage medium
CN112948419A (en) Query statement processing method and device
US8359329B2 (en) Method, computer apparatus and computer program for identifying unusual combinations of values in data
CN116257545B (en) Data query method and device, electronic equipment and storage medium
CN117389980B (en) Log file analysis method and device, computer equipment and readable storage medium

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination