CN113760890A - Relational database management method and apparatus - Google Patents
Relational database management method and apparatus Download PDFInfo
- Publication number
- CN113760890A CN113760890A CN202011340995.8A CN202011340995A CN113760890A CN 113760890 A CN113760890 A CN 113760890A CN 202011340995 A CN202011340995 A CN 202011340995A CN 113760890 A CN113760890 A CN 113760890A
- Authority
- CN
- China
- Prior art keywords
- query
- index
- field
- slow
- relational database
- 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
Links
- 238000007726 management method Methods 0.000 title description 18
- 238000000034 method Methods 0.000 claims abstract description 27
- 238000001914 filtration Methods 0.000 claims description 11
- 238000004590 computer program Methods 0.000 claims description 9
- 238000010586 diagram Methods 0.000 description 11
- 238000004891 communication Methods 0.000 description 7
- 230000006870 function Effects 0.000 description 5
- 150000001875 compounds Chemical class 0.000 description 4
- 230000003287 optical effect Effects 0.000 description 4
- 238000012545 processing Methods 0.000 description 4
- 230000004048 modification Effects 0.000 description 3
- 238000012986 modification Methods 0.000 description 3
- 230000008569 process Effects 0.000 description 3
- 230000004044 response Effects 0.000 description 3
- 230000008901 benefit Effects 0.000 description 2
- 238000011161 development Methods 0.000 description 2
- 230000000694 effects Effects 0.000 description 2
- 239000000835 fiber Substances 0.000 description 2
- 230000000644 propagated effect Effects 0.000 description 2
- 230000008439 repair process Effects 0.000 description 2
- 239000004065 semiconductor Substances 0.000 description 2
- 238000010276 construction Methods 0.000 description 1
- 238000013461 design Methods 0.000 description 1
- 230000006872 improvement Effects 0.000 description 1
- 239000004973 liquid crystal related substance Substances 0.000 description 1
- 238000012423 maintenance Methods 0.000 description 1
- 239000013307 optical fiber Substances 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
- 238000013024 troubleshooting Methods 0.000 description 1
Images
Classifications
-
- 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/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
- G06F16/2272—Management thereof
-
- 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/245—Query processing
- G06F16/2453—Query optimisation
-
- 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/245—Query processing
- G06F16/2455—Query execution
-
- 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)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a method and a device for managing a relational database, and relates to the technical field of computers. One embodiment of the method comprises: acquiring a slow query statement from a slow log record of a relational database; creating a field index according to the query field of the slow query statement; and updating the index directory of the relational database according to the field index. According to the embodiment, the query execution time of the query statement can be reduced and the query efficiency can be improved by analyzing the relational database slow log to search the overtime query statement and optimizing the index directory.
Description
Technical Field
The invention relates to the technical field of computers, in particular to a method and a device for managing a relational database.
Background
The index directory is a data structure for helping the relational database to efficiently acquire data, and the response time of the query relational database to return results depends on the execution efficiency of the query statement in the index directory. If no field index is built in the query statement, the server sequentially traverses the query to scan all records of the entire table starting with the first record until a satisfactory record is found. Such sequential reading of disk data without index consumes resources such as system I/O, memory, etc., which not only affects execution of the query statement of this time, but also affects execution efficiency of query statements of other threads.
Disclosure of Invention
In view of this, embodiments of the present invention provide a method and an apparatus for managing a relational database, where the method and apparatus can reduce query execution time of query statements and improve query efficiency by analyzing a slow log of the relational database to search for overtime query statements and optimize an index directory.
To achieve the above object, according to an aspect of an embodiment of the present invention, there is provided a method for relational database management, including:
acquiring a slow query statement from a slow log record of a relational database;
creating a field index according to the query field of the slow query statement;
and updating the index directory of the relational database according to the field index.
Optionally, obtaining the slow query statement from a slow log record of the relational database includes:
scanning the slow log of the relational database according to a preset scanning time interval, and filtering the query sentences of which the execution time is greater than or equal to an overtime threshold value in the scanning time interval to obtain the slow query sentences.
Optionally, obtaining the slow query statement from a slow log record of the relational database includes:
scanning the slow log of the relational database according to a preset scanning time interval, and filtering query statements with execution time greater than or equal to an overtime threshold and overtime times greater than or equal to a preset query overtime time within the scanning time interval to obtain the slow query statements.
Optionally, creating a field index according to the query field of the slow query statement includes:
judging whether the query field of the slow query statement is one or not and whether the query field of the slow query statement exists in an index directory of the relational database or not;
if the slow query statement only has one query field and the query field does not exist in an index directory of the relational database, taking the query field as the field index;
and if the slow query statement has more than two query fields and each query field does not exist in the index directory of the relational database, creating a combined index according to a leftmost matching principle and the more than two query fields.
Optionally, using the query field as the field index includes:
judging whether the query field is an identity or not; if so, creating a main key index according to the query field to obtain the field index; if not, a common index is created according to the query field, and the field index is obtained.
Optionally, creating a field index according to the query field of the slow query statement includes:
if the slow query statement has more than two query fields, and the index directory of the relational database does not have a field index corresponding to a first query field or the index directory of the relational database has a common index corresponding to the first query field, creating a combined index according to a leftmost matching principle and the more than two query fields;
and if the slow query statement has more than two query fields, the index directory of the relational database has a combined index corresponding to the first query field, and at least one query field does not exist in the index directory of the relational database, creating a combined index according to a leftmost matching principle and the more than two query fields.
Optionally, the number of fields of the combined index is less than or equal to 3.
According to still another aspect of an embodiment of the present invention, there is provided an apparatus for relational database management, including:
the data acquisition module is used for acquiring slow query statements from slow log records of the relational database;
the index creating module is used for creating a field index according to the query field of the slow query statement;
and the index updating module updates the index catalog of the relational database according to the field index.
Optionally, the data obtaining module obtains the slow query statement from a slow log record of the relational database, including:
scanning the slow log of the relational database according to a preset scanning time interval, and filtering the query sentences of which the execution time is greater than or equal to an overtime threshold value in the scanning time interval to obtain the slow query sentences.
Optionally, the data obtaining module obtains the slow query statement from a slow log record of the relational database, including:
scanning the slow log of the relational database according to a preset scanning time interval, and filtering query statements with execution time greater than or equal to an overtime threshold and overtime times greater than or equal to a preset query overtime time within the scanning time interval to obtain the slow query statements.
Optionally, the creating a field index according to the query field of the slow query statement by an index creating module includes:
judging whether the query field of the slow query statement is one or not and whether the query field of the slow query statement exists in an index directory of the relational database or not;
if the slow query statement only has one query field and the query field does not exist in an index directory of the relational database, taking the query field as the field index;
and if the slow query statement has more than two query fields and each query field does not exist in the index directory of the relational database, creating a combined index according to a leftmost matching principle and the more than two query fields.
Optionally, the index creating module takes the query field as the field index, and includes:
judging whether the query field is an identity or not; if so, creating a main key index according to the query field to obtain the field index; if not, a common index is created according to the query field, and the field index is obtained.
Optionally, the creating a field index according to the query field of the slow query statement by an index creating module includes:
if the slow query statement has more than two query fields, and the index directory of the relational database does not have a field index corresponding to a first query field or the index directory of the relational database has a common index corresponding to the first query field, creating a combined index according to a leftmost matching principle and the more than two query fields;
and if the slow query statement has more than two query fields, the index directory of the relational database has a combined index corresponding to the first query field, and at least one query field does not exist in the index directory of the relational database, creating a combined index according to a leftmost matching principle and the more than two query fields.
Optionally, the number of fields of the combined index is less than or equal to 3.
According to another aspect of the embodiments of the present invention, there is provided an electronic device managed by a relational database, including:
one or more processors;
a storage device for storing one or more programs,
when executed by the one or more processors, cause the one or more processors to implement the methods of relational database management provided by the present invention.
According to a further aspect of embodiments of the present invention, there is provided a computer readable medium, on which a computer program is stored, which when executed by a processor, implements a method of relational database management provided by the present invention.
One embodiment of the above invention has the following advantages or benefits: because the technical means of obtaining the slow query statement from the slow log record of the relational database, creating the field index according to the query field of the slow query statement and updating the index directory of the relational database according to the field index are adopted, the slow log of the relational database can be analyzed to search the overtime query statement and optimize the index directory, the technical problem caused by lack of the field index can be solved, and the technical effects of reducing the query execution time of the query statement and improving the query efficiency can be achieved.
Further effects of the above-mentioned non-conventional alternatives will be described below in connection with the embodiments.
Drawings
The drawings are included to provide a better understanding of the invention and are not to be construed as unduly limiting the invention. Wherein:
FIG. 1 illustrates an exemplary system architecture diagram of a relational database management method or apparatus suitable for application to embodiments of the present invention;
FIG. 2 is a schematic diagram of the main flow of a method of relational database management according to an embodiment of the invention;
FIG. 3 is a schematic diagram of the main modules of an apparatus for relational database management according to an embodiment of the present invention;
fig. 4 is a schematic block diagram of a computer system suitable for use in implementing a terminal device or server of an embodiment of the invention.
Detailed Description
Exemplary embodiments of the present invention are described below with reference to the accompanying drawings, in which various details of embodiments of the invention are included to assist understanding, and which are to be considered as merely exemplary. Accordingly, those of ordinary skill in the art will recognize that various changes and modifications of the embodiments described herein can be made without departing from the scope and spirit of the invention. Also, descriptions of well-known functions and constructions are omitted in the following description for clarity and conciseness.
Fig. 1 shows an exemplary system architecture diagram of a relational database management method or a relational database management apparatus suitable for application to the embodiment of the present invention, and as shown in fig. 1, the exemplary system architecture of the relational database management method or the relational database management apparatus of the embodiment of the present invention includes:
as shown in fig. 1, the system architecture 100 may include terminal devices 101, 102, 103, a network 104, and a server 105. The network 104 serves as a medium for providing communication links between the terminal devices 101, 102, 103 and the server 105. Network 104 may include various connection types, such as wired, wireless communication links, or fiber optic cables, to name a few.
The user may use the terminal devices 101, 102, 103 to interact with the server 105 via the network 104 to receive or send messages or the like. The terminal devices 101, 102, 103 may have various communication client applications installed thereon, such as a shopping application, a web browser application, a search application, an instant messaging tool, a mailbox client, social platform software, and the like.
The terminal devices 101, 102, 103 may be various electronic devices having a display screen and supporting web browsing, including but not limited to smart phones, tablet computers, laptop portable computers, desktop computers, and the like.
The server 105 may be a server that provides various services, such as a background management server that supports shopping websites browsed by users using the terminal devices 101, 102, 103. The backend management server may analyze and otherwise process data such as the received product information query request, and feed back a processing result (for example, target push information, product information — just an example) to the terminal devices 101, 102, and 103.
It should be noted that the method for managing a relational database provided by the embodiment of the present invention is generally executed by the server 105, and accordingly, the apparatus for managing a relational database is generally disposed in the server 105.
It should be understood that the number of terminal devices, networks, and servers in fig. 1 is merely illustrative. There may be any number of terminal devices, networks, and servers, as desired for implementation.
Fig. 2 is a schematic diagram of a main flow of a method for relational database management according to an embodiment of the present invention, and as shown in fig. 2, the method for relational database management includes step S201, step S202, and step S203.
Step S201, obtaining a slow query statement from a slow log record of the relational database.
And starting a slow log recording parameter in the relational database, and recording in a log mode when the execution time of the query statement is longer, wherein the log is a log file (log file) of the slow query statement. The data source of the method of embodiments of the present invention comes from the log file.
The database server may periodically scan for slow log records of the relational database. In some alternative embodiments, retrieving the slow query statement from the slow log record of the relational database comprises: scanning the slow log of the relational database according to a preset scanning time interval, and filtering the query sentences of which the execution time is greater than or equal to an overtime threshold value in the scanning time interval to obtain the slow query sentences. The value of the timeout threshold may be selectively set according to the actual situation, and is not specifically limited herein. In further alternative embodiments, retrieving the slow query statement from the slow log record of the relational database comprises: scanning the slow log of the relational database according to a preset scanning time interval, and filtering query statements with execution time greater than or equal to an overtime threshold and overtime times greater than or equal to a preset query overtime time within the scanning time interval to obtain the slow query statements. The timeout refers to the execution time being equal to or greater than a timeout threshold.
Illustratively, a slow query rule is preset, and parameters of the rule comprise: query timeOut number total, timeOut threshold timeOut, and scan interval intervalTime for sql (Structured Query Language) statements. And filtering the log records of the slow queries through the set slow query rules. The database server scans the log file again every intervalTime duration after the last scanning execution, triggers rules when the execution time of a certain query sql is more than or equal to timeOut in a time interval and the timeOut times are more than or equal to total, temporarily stores sql sentences of the trigger rules, and the query sentences triggering slow query rules are slow query sentences.
And S202, creating a field index according to the query field of the slow query statement.
The field index is a structure for sorting values of one or more columns in the database table, and the field index can be used for accelerating the retrieval of data in the table and quickly accessing specific information in the database table. The creation mode of the field index can be selectively set according to the actual situation, for example, one or more query fields of the slow query statement are directly used as the field index. The type of the field index can also be selectively set, such as a primary key index (a special unique index, no null value is allowed), a common index (a basic index of mysql), a combined index (referring to indexes created on multiple fields), and the like.
Optionally, creating a field index according to the query field of the slow query statement includes: and judging whether the query field of the slow query statement is one or not and whether the query field of the slow query statement exists in an index directory of the relational database. And if the slow query statement only has one query field and the query field does not exist in an index directory of the relational database, taking the query field as the field index. And if the slow query statement has more than two query fields and each query field does not exist in the index directory of the relational database, creating a combined index according to a leftmost matching principle and the more than two query fields. The number of fields included in the combined index may be set as desired, for example, two, three, or more. Optionally, the number of fields contained in the field index does not exceed three at most. When each query field of the query statement does not exist in the index directory of the relational database, the field index is directly created, so that the query efficiency of the query statement in the relational database can be improved.
In some optional embodiments, indexing with the query field as the field index includes: judging whether the query field is an identity document (id) or not; if so, creating a main key index according to the query field to obtain the field index; if not, a common index is created according to the query field, and the field index is obtained. Illustratively, the sql statement automatically establishes a primary key index if there is only one field column1 and the name of the field is ID or ID; if only one field column1 exists and the name is not ID and ID, then automatically establishing a common index; if more than two fields, column1 and column2 … columns, n is a positive integer and represents the number of the query fields, the automatic establishment of the combined index is established. The combined index contains fields that conform to the leftmost matching principle (leftmost first, any consecutive index starting from the leftmost can be matched).
In further alternative embodiments, creating a field index from the query field of the slow query statement comprises: and if the slow query statement has more than two query fields, and the index directory of the relational database does not have a field index corresponding to the first query field or the index directory of the relational database has a common index corresponding to the first query field, creating a combined index according to a leftmost matching principle and the more than two query fields. And if the slow query statement has more than two query fields, the index directory of the relational database has a combined index corresponding to the first query field, and at least one query field does not exist in the index directory of the relational database, creating a combined index according to a leftmost matching principle and the more than two query fields.
Illustratively, the query fields of the sql statement are column1 and column2 …, where n is a positive integer and represents the number of query fields. If column1 has already built the common index and column2 has not built the index, then the combined index (column1, column2) is built and the common index of column1 is deleted. If column1 has already built a compound index, column2 has not built, and column1 is the leftmost field, then the second position to modify the compound index of column1 is column 2. If column1 has already built a compound index, column2 has not built, and column1 is not the leftmost field, a compound index is built (column1, column 2).
And step S203, updating the index catalog of the relational database according to the field index.
If the index is not established in the query sql statement, the server sequentially searches data with the length being integral multiple of the page on the disk under the condition that the table associated data size is large. When the data to be read is not in the memory, the disk will find the starting position of the data and successively read one or more pages backward to load the data into the memory, and if the expected data is still not available, the page is continuously read. Such sql read disk data sequentially without indexing consumes system resources and response time. If the database query timeout phenomenon occurs after the system is on line, development and maintenance personnel are difficult to find and repair quickly. If the index is searched, the index data page is searched, then the position of the target data is determined through binary search, and then the target data is directly loaded and returned through the target position, so that the resource consumption of the server is reduced. According to the embodiment of the invention, the overtime query statement can be searched and the index directory can be optimized by analyzing the slow log of the relational database, the corresponding table field index can be automatically identified and modified, the influence of the execution overtime of the online database on a service system is overcome, the query efficiency of a server is improved, the response time is reduced, and the user experience is improved; manual intervention is not needed, and the work of development and operator troubleshooting and repair is reduced.
The embodiment of the invention also provides a device for realizing the method. As shown in fig. 3, the apparatus 300 for relational database management includes:
the data acquisition module 301 acquires a slow query statement from a slow log record of the relational database;
an index creating module 302, which creates a field index according to the query field of the slow query statement;
and the index updating module 303 is configured to update the index directory of the relational database according to the field index.
Optionally, the data obtaining module obtains the slow query statement from a slow log record of the relational database, including:
scanning the slow log of the relational database according to a preset scanning time interval, and filtering the query sentences of which the execution time is greater than or equal to an overtime threshold value in the scanning time interval to obtain the slow query sentences.
Optionally, the data obtaining module obtains the slow query statement from a slow log record of the relational database, including:
scanning the slow log of the relational database according to a preset scanning time interval, and filtering query statements with execution time greater than or equal to an overtime threshold and overtime times greater than or equal to a preset query overtime time within the scanning time interval to obtain the slow query statements.
Optionally, the creating a field index according to the query field of the slow query statement by an index creating module includes:
judging whether the query field of the slow query statement is one or not and whether the query field of the slow query statement exists in an index directory of the relational database or not;
if the slow query statement only has one query field and the query field does not exist in an index directory of the relational database, taking the query field as the field index;
and if the slow query statement has more than two query fields and each query field does not exist in the index directory of the relational database, creating a combined index according to a leftmost matching principle and the more than two query fields.
Optionally, the index creating module takes the query field as the field index, and includes:
judging whether the query field is an identity or not; if so, creating a main key index according to the query field to obtain the field index; if not, a common index is created according to the query field, and the field index is obtained.
Optionally, the creating a field index according to the query field of the slow query statement by an index creating module includes:
if the slow query statement has more than two query fields, and the index directory of the relational database does not have a field index corresponding to a first query field or the index directory of the relational database has a common index corresponding to the first query field, creating a combined index according to a leftmost matching principle and the more than two query fields;
and if the slow query statement has more than two query fields, the index directory of the relational database has a combined index corresponding to the first query field, and at least one query field does not exist in the index directory of the relational database, creating a combined index according to a leftmost matching principle and the more than two query fields.
Optionally, the number of fields of the combined index is less than or equal to 3.
Fig. 4 is a schematic structural diagram of a computer system suitable for implementing a terminal device according to an embodiment of the present invention, and as shown in fig. 4, the computer system 400 of the terminal device according to the embodiment of the present invention includes:
a Central Processing Unit (CPU)401 is included, which can perform various appropriate actions and processes in accordance with a program stored in a Read Only Memory (ROM)402 or a program loaded from a storage section 408 into a Random Access Memory (RAM) 403. In the RAM403, various programs and data necessary for the operation of the system 400 are also stored. The CPU401, ROM402, and RAM403 are connected to each other via a bus 404. An input/output (I/O) interface 405 is also connected to bus 404.
The following components are connected to the I/O interface 405: an input section 406 including a keyboard, a mouse, and the like; an output section 407 including a display device such as a Cathode Ray Tube (CRT), a Liquid Crystal Display (LCD), and the like, and a speaker; a storage section 408 including a hard disk and the like; and a communication section 409 including a network interface card such as a LAN card, a modem, or the like. The communication section 409 performs communication processing via a network such as the internet. A driver 410 is also connected to the I/O interface 405 as needed. A removable medium 411 such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, or the like is mounted on the drive 410 as necessary, so that a computer program read out therefrom is mounted into the storage section 408 as necessary.
In particular, according to the embodiments of the present disclosure, the processes described above with reference to the flowcharts may be implemented as computer software programs. For example, embodiments of the present disclosure include a computer program product comprising a computer program embodied on a computer readable medium, the computer program comprising program code for performing the method illustrated in the flow chart. In such an embodiment, the computer program may be downloaded and installed from a network through the communication section 409, and/or installed from the removable medium 411. The computer program performs the above-described functions defined in the system of the present invention when executed by a Central Processing Unit (CPU) 401.
It should be noted that the computer readable medium shown in the present invention can be a computer readable signal medium or a computer readable storage medium or any combination of the two. A computer readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the foregoing. More specific examples of the computer readable storage medium may include, but are not limited to: 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 the present invention, 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. In the present invention, however, a computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated data signal may take many forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. 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, wire, fiber optic cable, RF, etc., or any suitable combination of the foregoing.
The flowchart and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods and computer program products according to various embodiments of the present invention. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams or flowchart illustration, and combinations of blocks in the block diagrams or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.
The modules described in the embodiments of the present invention may be implemented by software or hardware. The described modules may also be provided in a processor, which may be described as: a processor includes a data acquisition module, an index creation module, and an index update module. The names of these modules do not in some cases constitute a limitation on the module itself, for example, the data acquisition module may also be described as a "module that creates a field index from the query field of the slow query statement".
As another aspect, the present invention also provides a computer-readable medium that may be contained in the apparatus described in the above embodiments; or may be separate and not incorporated into the device. The computer readable medium carries one or more programs which, when executed by a device, cause the device to comprise: acquiring a slow query statement from a slow log record of a relational database; creating a field index according to the query field of the slow query statement; and updating the index directory of the relational database according to the field index.
According to the technical scheme of the embodiment of the invention, by analyzing the relational database slow logs, manual intervention is not needed, the overtime query sentences can be automatically identified, the index directory is optimized, the query execution time of the query sentences is reduced, and the query efficiency is improved.
The above-described embodiments should not be construed as limiting the scope of the invention. Those skilled in the art will appreciate that various modifications, combinations, sub-combinations, and substitutions can occur, depending on design requirements and other factors. Any modification, equivalent replacement, and improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.
Claims (10)
1. A method of database management, comprising:
acquiring a slow query statement from a slow log record of a relational database;
creating a field index according to the query field of the slow query statement;
and updating the index directory of the relational database according to the field index.
2. The method of claim 1, wherein obtaining slow query statements from slow log records of a relational database comprises:
scanning the slow log of the relational database according to a preset scanning time interval, and filtering the query sentences of which the execution time is greater than or equal to an overtime threshold value in the scanning time interval to obtain the slow query sentences.
3. The method of claim 1, wherein obtaining slow query statements from slow log records of a relational database comprises:
scanning the slow log of the relational database according to a preset scanning time interval, and filtering query statements with execution time greater than or equal to an overtime threshold and overtime times greater than or equal to a preset query overtime time within the scanning time interval to obtain the slow query statements.
4. The method of claim 1, wherein creating a field index from a query field of the slow query statement comprises:
judging whether the query field of the slow query statement is one or not and whether the query field of the slow query statement exists in an index directory of the relational database or not;
if the slow query statement only has one query field and the query field does not exist in an index directory of the relational database, taking the query field as the field index;
and if the slow query statement has more than two query fields and each query field does not exist in the index directory of the relational database, creating a combined index according to a leftmost matching principle and the more than two query fields.
5. The method of claim 4, wherein indexing the field with the query field comprises:
judging whether the query field is an identity or not; if so, creating a main key index according to the query field to obtain the field index; if not, a common index is created according to the query field, and the field index is obtained.
6. The method of claim 4, wherein creating a field index from a query field of the slow query statement comprises:
if the slow query statement has more than two query fields, and the index directory of the relational database does not have a field index corresponding to a first query field or the index directory of the relational database has a common index corresponding to the first query field, creating a combined index according to a leftmost matching principle and the more than two query fields;
and if the slow query statement has more than two query fields, the index directory of the relational database has a combined index corresponding to the first query field, and at least one query field does not exist in the index directory of the relational database, creating a combined index according to a leftmost matching principle and the more than two query fields.
7. An apparatus for database management, comprising:
the data acquisition module is used for acquiring slow query statements from slow log records of the relational database;
the index creating module is used for creating a field index according to the query field of the slow query statement;
and the index updating module updates the index catalog of the relational database according to the field index.
8. The apparatus of claim 7, wherein the index creation module creates a field index from a query field of the slow query statement, comprising:
judging whether the query field of the slow query statement is one or not and whether the query field of the slow query statement exists in an index directory of the relational database or not;
if the slow query statement only has one query field and the query field does not exist in an index directory of the relational database, taking the query field as the field index;
and if the slow query statement has more than two query fields and each query field does not exist in the index directory of the relational database, creating a combined index according to a leftmost matching principle and the more than two query fields.
9. An electronic device for relational database management, comprising:
one or more processors;
a storage device for storing one or more programs,
when executed by the one or more processors, cause the one or more processors to implement the method of any one of claims 1-6.
10. A computer-readable medium, on which a computer program is stored, which, when being executed by a processor, carries out the method according to any one of claims 1-6.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011340995.8A CN113760890A (en) | 2020-11-24 | 2020-11-24 | Relational database management method and apparatus |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011340995.8A CN113760890A (en) | 2020-11-24 | 2020-11-24 | Relational database management method and apparatus |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN113760890A true CN113760890A (en) | 2021-12-07 |
Family
ID=78786070
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202011340995.8A Pending CN113760890A (en) | 2020-11-24 | 2020-11-24 | Relational database management method and apparatus |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113760890A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN118132537A (en) * | 2024-01-29 | 2024-06-04 | 北京安锐卓越信息技术股份有限公司 | Method and device for solving problem of system blocking of social customer relationship management system |
Citations (10)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20080059441A1 (en) * | 2006-08-30 | 2008-03-06 | Lockheed Martin Corporation | System and method for enterprise-wide dashboard reporting |
| US20110078135A1 (en) * | 2009-09-28 | 2011-03-31 | Oracle International Corporation | Database index monitoring system |
| CN106959963A (en) * | 2016-01-12 | 2017-07-18 | 杭州海康威视数字技术股份有限公司 | A kind of data query method, apparatus and system |
| CN107016019A (en) * | 2015-10-23 | 2017-08-04 | 阿里巴巴集团控股有限公司 | Database index creation method and device |
| CN107239451A (en) * | 2016-03-28 | 2017-10-10 | 北京京东尚科信息技术有限公司 | Database index creation method and device |
| US20180322154A1 (en) * | 2017-05-05 | 2018-11-08 | Servicenow, Inc. | Database performance tuning framework |
| CN110245137A (en) * | 2019-05-07 | 2019-09-17 | 阿里巴巴集团控股有限公司 | A kind of processing method of index, device and equipment |
| CN110688354A (en) * | 2019-09-30 | 2020-01-14 | 深圳证券交易所 | Analysis method of slow log file in database, terminal and storage medium |
| CN111046040A (en) * | 2019-11-26 | 2020-04-21 | 北京达佳互联信息技术有限公司 | Method and device for determining index, electronic equipment and storage medium |
| CN111241059A (en) * | 2020-01-07 | 2020-06-05 | 广州虎牙科技有限公司 | Database optimization method and device based on database |
-
2020
- 2020-11-24 CN CN202011340995.8A patent/CN113760890A/en active Pending
Patent Citations (10)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20080059441A1 (en) * | 2006-08-30 | 2008-03-06 | Lockheed Martin Corporation | System and method for enterprise-wide dashboard reporting |
| US20110078135A1 (en) * | 2009-09-28 | 2011-03-31 | Oracle International Corporation | Database index monitoring system |
| CN107016019A (en) * | 2015-10-23 | 2017-08-04 | 阿里巴巴集团控股有限公司 | Database index creation method and device |
| CN106959963A (en) * | 2016-01-12 | 2017-07-18 | 杭州海康威视数字技术股份有限公司 | A kind of data query method, apparatus and system |
| CN107239451A (en) * | 2016-03-28 | 2017-10-10 | 北京京东尚科信息技术有限公司 | Database index creation method and device |
| US20180322154A1 (en) * | 2017-05-05 | 2018-11-08 | Servicenow, Inc. | Database performance tuning framework |
| CN110245137A (en) * | 2019-05-07 | 2019-09-17 | 阿里巴巴集团控股有限公司 | A kind of processing method of index, device and equipment |
| CN110688354A (en) * | 2019-09-30 | 2020-01-14 | 深圳证券交易所 | Analysis method of slow log file in database, terminal and storage medium |
| CN111046040A (en) * | 2019-11-26 | 2020-04-21 | 北京达佳互联信息技术有限公司 | Method and device for determining index, electronic equipment and storage medium |
| CN111241059A (en) * | 2020-01-07 | 2020-06-05 | 广州虎牙科技有限公司 | Database optimization method and device based on database |
Non-Patent Citations (5)
| Title |
|---|
| GRAYAN: "数据库_Mysql：慢查询的优化方法，添加索引的3种方式", pages 1 - 5, Retrieved from the Internet <URL:https://blog.csdn.net/weixin_43851310/article/details/88931063> * |
| 伍应树;赵志刚;李宪明;: "关系数据库基于索引查询的优化设计研究", 电脑编程技巧与维护, no. 17, 3 September 2016 (2016-09-03) * |
| 张士军;陆海伦;: "索引在MySQL查询优化中的应用", 计算机与数字工程, no. 01, 20 January 2007 (2007-01-20) * |
| 李田田: "基于JavaEE的企业通讯管理系统的设计与实现", 中国优秀硕士学位论文全文数据库, 15 April 2020 (2020-04-15), pages 138 - 168 * |
| 李素奇;: "关于SQL索引建立规则与优化的探讨", 科技展望, no. 19, 10 October 2014 (2014-10-10) * |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN118132537A (en) * | 2024-01-29 | 2024-06-04 | 北京安锐卓越信息技术股份有限公司 | Method and device for solving problem of system blocking of social customer relationship management system |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107506451B (en) | Abnormal information monitoring method and device for data interaction | |
| CN109614402B (en) | Multidimensional data query method and device | |
| CN114139040A (en) | Data storage and query method, device, equipment and readable storage medium | |
| CN112000734A (en) | Big data processing method and device | |
| CN110851419B (en) | Data migration method and device | |
| CN111753019B (en) | Data partitioning method and device applied to data warehouse | |
| CN112100168A (en) | Method and device for determining data association relationship | |
| CN113760890A (en) | Relational database management method and apparatus | |
| CN111858621B (en) | Method, apparatus, device and computer readable medium for monitoring business process | |
| CN113760966A (en) | Data processing method and device based on heterogeneous database system | |
| CN112817930A (en) | Data migration method and device | |
| CN113760600B (en) | Database backup method, database restoration method and related devices | |
| CN111723063A (en) | Method and device for processing offline log data | |
| CN113468529B (en) | Data searching method and device | |
| CN111459980A (en) | Monitoring data storage and query method and device | |
| CN113138974B (en) | Method and device for detecting database compliance | |
| CN113127416B (en) | Data query method and device | |
| CN113138943B (en) | Method and device for processing request | |
| CN112783914B (en) | Method and device for optimizing sentences | |
| CN114064693A (en) | Method, device, electronic equipment and computer readable medium for processing account data | |
| CN112988778B (en) | Method and device for processing database query script | |
| CN112988857B (en) | Service data processing method and device | |
| CN112783615B (en) | Data processing task cleaning method and device | |
| CN113535768A (en) | Production monitoring method and device | |
| CN113704242A (en) | Data processing method and device |
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 |