Database performance optimization methodTechnical Field
The invention belongs to the field of big data of embedded equipment, and particularly relates to a database performance optimization method.
Background
As a key ring of the intelligent transportation scheme, the traffic host mainly bears the task of receiving the front-end camera snapshot record, storing the local and uploading the platform. A road junction is accessed with 4 to 8 traffic cameras by a host, 3 to 5 traffic records are received in peak time, the data volume exceeds 20 ten thousand per day, and the total data volume exceeds ten thousand under the state of full magnetic disk by matching with a plurality of large-capacity magnetic disks. The host device is mostly realized by adopting an embedded scheme, resources such as cpu, memory and the like are limited, and the performance and stability of the magnetic disk are reduced under the condition of long-time use, and under the condition, the device cannot meet the requirement of large data storage. Similarly, the face nvr, the face server and other devices also have performance problems caused by overlarge face picture capturing amount, and the usability of users is affected.
Disclosure of Invention
Therefore, the invention aims to provide a database performance optimization method to support quick reading and writing of tens of millions of databases and improve data stability under the condition of limited hardware resources.
In order to achieve the above purpose, the technical scheme of the invention is realized as follows:
a database performance optimization method comprising the steps of:
s1, optimizing a database file, and splitting the file;
s2, optimizing query, unifying a client and a background read-write interface, providing a database module closing interface, and adopting a pre-compiling mode for query operation;
s3, optimizing file management, designating indexes by sentences, and eliminating invalid indexes;
s4, optimizing reading and writing, and adopting a WAL log mode, single-thread writing and multi-thread reading.
Further, the step S1 includes:
A1. splitting a database into a plurality of db files, naming the db files according to magnetic discs and serial numbers, and recording start and stop time of the files through a tab_cfg table;
A2. judging the current database by whole points, and creating a new database if the current database exceeds the limit of the number of records and the number of days;
A3. deleting the db file under the condition that the data deleting condition is met and no database is connected;
A4. closing the connection of the unoperated database within a certain time, so as to avoid the problem of excessive connection numbers;
A5. the main key of the database is adjusted, in order to ensure the unique equipment, each database file is allocated with an id, the database keys are self-increased by 0, and the (id < < 22) +keys are used as the external main key of the module;
A6. detecting the size of a database file, setting a 2G size illegal value, and deleting the reconstruction if the size of the database file exceeds the 2G size illegal value.
Further, step S2 includes:
B1. concurrent database read operations, the same thread registers a database handle once, and the same handle is not used in multiple threads;
B2. database read operation provides two interfaces, namely, sqlite3_get_table and sqlite 3_preparation;
B3. creating connection for the database in each disk inquiry time period, and respectively inquiring and combining inquiry results;
B4. each database user reading operation thread is independent, needed database connection is established, the database connection is always open in the same day, and the connection which is not operated in a certain time is closed, so that excessive connection numbers are avoided;
B5. a unified shutdown interface is provided for disk initialization and failure.
Further, the step S3 includes:
C1. defaulting the first available disk as a database master disk, storing a config database file, and transferring the config file to a memory without changing the master disk when formatting the master disk;
C2. judging the damage condition of the database by using the database file operation error code, and carrying out background repair by using a database backup or index reconstruction mode;
C3. and (5) performing damage inspection on the database in the early morning time period, and repairing.
C4. Adding a reconstruction index for snapshot record of a certain disk in a certain day, creating folders according to the day when saving a picture file, and supporting reconstruction according to disk, file type and time period conditions by using a kernel reconstruction index;
C5. and (4) retaining simple backup restoration logic of bk0, repairing the current database through bk0 and online files, and restoring the past data through bk 0.
Further, the step S4 includes the following steps:
D1. creating a database writing thread by each disk, and uniformly executing database writing operation;
D2. the low priority operation enters a sequence and stores a record file for power-off rewriting;
D3. taking a plurality of sentences from the queue, packaging the sentences into transactions for writing, and inserting the current transactions into the high-priority operation to ensure timeliness;
D4. and providing synchronous and asynchronous interfaces for writing operation, and meeting different scene requirements.
Compared with the prior art, the invention has the following advantages:
(1) The database performance optimization method disclosed by the invention has the advantages that the integrated method is zero, the its.db files are split according to the disk and the time period, the query speed is improved, and the damage probability and the repair cost of the db files are reduced.
(2) The database performance optimization method unifies the client and the background read-write interface, provides the database module closing interface, adopts the pre-compiling mode for query operation, and effectively controls the database operation quality.
(3) The database performance optimization method adopts single-thread writing and multi-thread reading, and avoids long-time writing transactions.
Drawings
The accompanying drawings, which are included to provide a further understanding of the invention and are incorporated in and constitute a part of this specification, illustrate embodiments of the invention and together with the description serve to explain the invention and do not constitute an undue limitation on the invention. In the drawings:
FIG. 1 is a schematic diagram of a database query flow according to an embodiment of the present invention;
FIG. 2 is a schematic diagram of a database write process according to an embodiment of the present invention;
FIG. 3 is a flowchart of a database file management process according to an embodiment of the present invention
Detailed Description
It should be noted that, without conflict, the embodiments of the present invention and features of the embodiments may be combined with each other.
In the description of the invention, it should be understood that the terms "center," "longitudinal," "transverse," "upper," "lower," "front," "rear," "left," "right," "vertical," "horizontal," "top," "bottom," "inner," "outer," and the like indicate orientations or positional relationships that are based on the orientation or positional relationships shown in the drawings, merely to facilitate describing the invention and simplify the description, and do not indicate or imply that the device or element being referred to must have a particular orientation, be configured and operate in a particular orientation, and therefore should not be construed as limiting the invention. Furthermore, the terms "first," "second," and the like, are used for descriptive purposes only and are not to be construed as indicating or implying a relative importance or implicitly indicating the number of technical features indicated. Thus, a feature defining "a first", "a second", etc. may explicitly or implicitly include one or more such feature. In the description of the invention, unless otherwise indicated, the meaning of "a plurality" is two or more.
In the description of the invention, it should be noted that, unless explicitly specified and limited otherwise, the terms "mounted," "connected," and "connected" are to be construed broadly, and may be, for example, fixedly connected, detachably connected, or integrally connected; can be mechanically or electrically connected; can be directly connected or indirectly connected through an intermediate medium, and can be communication between two elements. The specific meaning of the above terms in the creation of the present invention can be understood by those of ordinary skill in the art in a specific case.
The invention will be described in detail below with reference to the drawings in connection with embodiments.
As shown in fig. 1 to 3, a database performance optimization method includes the following steps:
s1, optimizing a database file, and splitting the file;
s2, optimizing query, unifying a client and a background read-write interface, providing a database module closing interface, and adopting a pre-compiling mode for query operation;
s3, optimizing file management, designating indexes by sentences, and eliminating invalid indexes;
s4, optimizing reading and writing, and adopting a WAL log mode, single-thread writing and multi-thread reading.
The step S1 includes:
A1. splitting a database into a plurality of db files, naming the db files according to magnetic discs and serial numbers, and recording start and stop time of the files through a tab_cfg table;
A2. judging the current database by whole points, and creating a new database if the current database exceeds the limit of the number of records and the number of days;
A3. deleting the db file under the condition that the data deleting condition is met and no database is connected;
A4. closing the connection of the unoperated database within a certain time, so as to avoid the problem of excessive connection numbers;
A5. the main key of the database is adjusted, in order to ensure the unique equipment, each database file is allocated with an id, the database keys are self-increased by 0, and the (id < < 22) +keys are used as the external main key of the module;
A6. detecting the size of a database file, setting a 2G size illegal value, and deleting the reconstruction if the size of the database file exceeds the 2G size illegal value.
As shown in fig. 1, step S2 includes:
B1. concurrent database read operations, the same thread registers a database handle once, and the same handle is not used in multiple threads;
B2. database read operation provides two interfaces, namely, sqlite3_get_table and sqlite 3_preparation;
B3. creating connection for the database in each disk inquiry time period, and respectively inquiring and combining inquiry results;
B4. each database user reading operation thread is independent, needed database connection is established, the database connection is always open in the same day, and the connection which is not operated in a certain time is closed, so that excessive connection numbers are avoided;
B5. a unified shutdown interface is provided for disk initialization and failure.
As shown in fig. 2, the step S3 includes:
C1. defaulting the first available disk as a database master disk, storing a config database file, and transferring the config file to a memory without changing the master disk when formatting the master disk;
C2. judging the damage condition of the database by using the database file operation error code, and carrying out background repair by using a database backup or index reconstruction mode;
C3. and (5) performing damage inspection on the database in the early morning time period, and repairing.
C4. Adding a reconstruction index for snapshot record of a certain disk in a certain day, creating folders according to the day when saving a picture file, and supporting reconstruction according to disk, file type and time period conditions by using a kernel reconstruction index;
C5. and (4) retaining simple backup restoration logic of bk0, repairing the current database through bk0 and online files, and restoring the past data through bk 0.
As shown in fig. 3, the step S4 includes the steps of:
D1. creating a database writing thread by each disk, and uniformly executing database writing operation;
D2. the low priority operation enters a sequence and stores a record file for power-off rewriting;
D3. taking a plurality of sentences from the queue, packaging the sentences into transactions for writing, and inserting the current transactions into the high-priority operation to ensure timeliness;
D4. providing a synchronous and asynchronous interface for writing operation, meeting the requirements of different scenes
The above embodiments are merely preferred embodiments of the present invention and are not intended to limit the present invention, and any modifications, equivalent substitutions, improvements, etc. within the spirit and principle of the present invention should be included in the protection scope of the present invention.