CN113282607A - Optimized query method for database - Google Patents
Optimized query method for database Download PDFInfo
- Publication number
- CN113282607A CN113282607A CN202110613507.4A CN202110613507A CN113282607A CN 113282607 A CN113282607 A CN 113282607A CN 202110613507 A CN202110613507 A CN 202110613507A CN 113282607 A CN113282607 A CN 113282607A
- Authority
- CN
- China
- Prior art keywords
- data
- column
- index
- data table
- optimized
- 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
- 238000000034 method Methods 0.000 title claims abstract description 28
- 230000004048 modification Effects 0.000 claims description 4
- 238000012986 modification Methods 0.000 claims description 4
- 238000001914 filtration Methods 0.000 claims description 3
- 238000012163 sequencing technique Methods 0.000 claims description 3
- 230000009286 beneficial effect Effects 0.000 description 1
- 230000007547 defect Effects 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 238000005457 optimization Methods 0.000 description 1
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/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/242—Query formulation
- G06F16/2433—Query languages
Abstract
The invention discloses an optimized query method of a database, which relates to the technical field of data query, and the implementation content comprises two parts of optimized data processing and optimized query statement; in the optimized data processing section, the operations performed include: (1.1) deleting duplicate records in the database; (1.2) setting a primary key in the data table; in the optimized query statement section, the operations performed include: (2.1) arranging the data tables in descending order according to the data quantity, and connecting two adjacent data tables by using inner join; (2.2) performing alias operation on the data tables before connecting the plurality of data tables; (2.3) avoiding use of query-all in Select clauses; (2.4) setting clauses which can be replaced in the data table; and (2.5) establishing an index in the data table. The invention can improve the query sequence of the data table, reduce the scanning times of the unrelated data, reduce the occupied resources in the data query process and realize the quick query of the data.
Description
Technical Field
The invention relates to the technical field of data query, in particular to an optimized query method of a database.
Background
The index is a data structure for helping to efficiently acquire data, and can sequence one or more columns of values in a database table, the index can be used for quickly accessing specific information in the database table, the positions of the columns needing to be retrieved are preferentially recorded when a large-data-volume data table is queried, so that all data in the data table is prevented from being scanned, and the speed of querying the data in the database is improved.
In a large project, the amount of data to be processed by a business system is huge, SQL bears huge operating pressure, and the performance of the SQL has huge influence on the performance of the whole business system. Based on the method, an optimized query method of the database is designed and developed, so that the data can be rapidly queried through optimized data processing and optimized query sentences.
Disclosure of Invention
Aiming at the requirements and the defects of the prior art development, the invention provides an optimized query method of a database.
The invention discloses an optimized query method of a database, which adopts the following technical scheme for solving the technical problems:
the optimized query method for the database includes two parts of optimized data processing and optimized query statement;
in the optimized data processing section, the operations performed include: (1.1) deleting duplicate records in the database; (1.2) setting a primary key in the data table;
in the optimized query statement section, the operations performed include: (2.1) arranging the data tables in descending order according to the data quantity, and connecting two adjacent data tables by using inner join; (2.2) performing alias operation on the data tables before connecting the plurality of data tables; (2.3) avoiding use of query-all in Select clauses; (2.4) setting clauses which can be replaced in the data table; and (2.5) establishing an index in the data table.
Further, in the optimized data processing section, the operations performed further include: (1.3) for fields containing only numeric types, numeric types are used and fields containing characters use varchar/nvarchar instead of char/nchar.
Further, when the optimized data processing part is executed (1.1) and the duplicate records in the database are deleted, truncate is used to replace delete, when truncate is used, the rollback section does not store any information which can be replied, and the deleted data cannot be recovered.
Further, in the optimized query statement part, the (2.2) is executed, and after the data table is subjected to the alias operation, the alias of the data table is used, and the alias of the data table is prefixed on each column needing to be queried.
Further, in the optimized query statement part, executing (2.4) a clause which can be replaced in the setting data table, wherein the clause comprises:
replacing the having clause with the where clause: the method comprises the following steps that a change clause is used for searching all records and then filtering a result set, and the change clause is also used for carrying out sequencing operation and total operation on the results;
EXISTS is used for replacing IN, and NOT EXISTS is used for replacing NOT IN;
replace the UNION operator with UNION ALL: the UNION operator will filter out duplicate records after the data table join, and the UNION ALL operation returns simply by merging the results.
Further, in the optimized query statement section, the principle of establishing an index in the data table is performed (2.5) and includes:
(a) setting a maximum column threshold, and establishing an index of a certain column of data when the daily query frequency of the column of data in the data table exceeds the maximum column threshold;
(b) setting a column minimum threshold, and establishing an index of a certain column of data when the modification times of the column of data in the data table is lower than the column minimum threshold;
(c) when the type of a certain row of data is different from the types of the previous row of data and the next row of data in the data table, establishing an index of the row of data;
(d) setting a field threshold value, and establishing an index of a field when the text length of the field is smaller than the field threshold value;
(e) and aiming at the intermediate data tables connected with other data tables, establishing indexes on the fields of the intermediate data tables connected with other data tables.
Further, in the optimized query statement section, the principle of indexing in the data table is performed (2.5), and the principle further includes:
(f) forbidding to use% xx in the established index, and meanwhile, replacing% xx with select t from t where reverse (f) like reverse ('% abc');
(g) in the where clause, use is prohibited! A ═ and < > operator;
(h) the use of is null and is not null in the index column is prohibited to determine whether or not the index column is empty.
Preferably, the number of indexes in one data table does not exceed 6;
aiming at the principles (a), (b) and (d) for establishing the index, when the multi-column data of the data table meet the same principle, establishing the index on the column with the maximum difference with the set threshold value;
aiming at the principle (c) of establishing the index, when the data of multiple columns of the data table meet the same principle, if the type of data of a certain column is different from the types of the data of the upper M columns and the data of the lower N columns, the column corresponding to the maximum M + N is selected, and the index is established on the column.
Compared with the prior art, the optimized query method of the database has the following beneficial effects:
by optimizing the data processing and optimizing the query statement, the query sequence of the data table can be improved, the scanning times of unrelated data are reduced, the resources occupied in the data query process are reduced, and the rapid query of the data is realized.
Detailed Description
In order to make the technical scheme, the technical problems to be solved and the technical effects of the present invention more clearly apparent, the following technical scheme of the present invention is clearly and completely described with reference to the specific embodiments.
The first embodiment is as follows:
the embodiment provides an optimized query method for a database, and the implementation content of the optimized query method comprises two parts, namely optimized data processing and optimized query statement.
In the optimized data processing section, the operations performed include:
and (1.1) deleting the repeated records in the database. When using truncate, the rollback segment no longer stores any information that can be replied to, and the deleted data cannot be recovered.
(1.2) setting a primary key in the data table.
(1.3) for fields containing only numeric types, numeric types are used and fields containing characters use varchar/nvarchar instead of char/nchar.
(II) in the optimization query statement part, the executed operation comprises the following steps:
(2.1) arranging the data tables in descending order of data amount, and connecting two adjacent data tables by using inner join.
And (2.2) performing alias operation on the data tables before connecting the plurality of data tables. And after carrying out alias operation on the data table, using the alias of the data table, and prefixing the alias of the data table on each column needing to be queried.
(2.3) avoid using query-all in Select clauses.
(2.4) setting clauses which can be replaced in the data table, which specifically includes:
replacing the having clause with the where clause: the method comprises the following steps that a change clause is used for searching all records and then filtering a result set, and the change clause is also used for carrying out sequencing operation and total operation on the results;
EXISTS is used for replacing IN, and NOT EXISTS is used for replacing NOT IN;
replace the UNION operator with UNION ALL: the UNION operator will filter out duplicate records after the data table join, and the UNION ALL operation returns simply by merging the results.
(2.5) establishing an index in the data table, wherein the principle of establishing the index comprises the following steps:
(a) setting a maximum column threshold, and establishing an index of a certain column of data when the daily query frequency of the column of data in the data table exceeds the maximum column threshold;
(b) setting a column minimum threshold, and establishing an index of a certain column of data when the modification times of the column of data in the data table is lower than the column minimum threshold;
(c) when the type of a certain row of data is different from the types of the previous row of data and the next row of data in the data table, establishing an index of the row of data;
(d) setting a field threshold value, and establishing an index of a field when the text length of the field is smaller than the field threshold value;
(e) aiming at the intermediate data tables connected with other data tables, establishing indexes on fields of the intermediate data tables connected with other data tables;
(f) forbidding to use% xx in the established index, and meanwhile, replacing% xx with select t from t where reverse (f) like reverse ('% abc');
(g) in the where clause, use is prohibited! A ═ and < > operator;
(h) the use of is null and is not null in the index column is prohibited to determine whether or not the index column is empty.
It should be added that, in order to avoid a large number of indexes in one data table, the number of indexes in one data table is set to be not more than 6. At this time, the process of the present invention,
aiming at the principles (a), (b) and (d) for establishing the index, when the multi-column data of the data table meet the same principle, establishing the index on the column with the maximum difference with the set threshold value;
aiming at the principle (c) of establishing the index, when the data of multiple columns of the data table meet the same principle, if the type of data of a certain column is different from the types of the data of the upper M columns and the data of the lower N columns, the column corresponding to the maximum M + N is selected, and the index is established on the column.
In summary, by optimizing data processing and query statements, the query sequence of the data table can be improved, the scanning times of unrelated data are reduced, resources occupied in the data query process are reduced, and the data is rapidly queried.
The principles and embodiments of the present invention have been described in detail using specific examples, which are provided only to aid in understanding the core technical content of the present invention. Based on the above embodiments of the present invention, those skilled in the art of data query technology should make any improvements and modifications to the present invention without departing from the principle of the present invention, and therefore, the present invention should fall into the protection scope of the present invention.
Claims (8)
1. The optimized query method of the database is characterized in that the implementation content comprises two parts of optimized data processing and optimized query statements;
in the optimized data processing section, the operations performed include: (1.1) deleting duplicate records in the database; (1.2) setting a primary key in the data table;
in the optimized query statement section, the operations performed include: (2.1) arranging the data tables in descending order according to the data quantity, and connecting two adjacent data tables by using inner join; (2.2) performing alias operation on the data tables before connecting the plurality of data tables; (2.3) avoiding use of query-all in Select clauses; (2.4) setting clauses which can be replaced in the data table; and (2.5) establishing an index in the data table.
2. The method of claim 1, wherein in the optimized data processing portion, the operations further comprise: (1.3) for fields containing only numeric types, numeric types are used and fields containing characters use varchar/nvarchar instead of char/nchar.
3. The method according to claim 1, wherein in the optimized data processing part, when (1.1) the duplicate records in the database are deleted, a truncate is used instead of a delete, when a truncate is used, the rollback segment no longer stores any information that can be replied, and the deleted data cannot be recovered.
4. The optimized query method for database according to claim 1, wherein in the optimized query statement section, performing (2.2), performing alias operation on the data table, using the alias of the data table, and prefixing the alias of the data table to each column to be queried.
5. The optimized query method for database according to claim 1, wherein in the optimized query statement section, executing (2.4) the clauses that can be replaced in the data table are set, and the method comprises:
replacing the having clause with the where clause: the method comprises the following steps that a change clause is used for searching all records and then filtering a result set, and the change clause is also used for carrying out sequencing operation and total operation on the results;
EXISTS is used for replacing IN, and NOT EXISTS is used for replacing NOT IN;
replace the UNION operator with UNION ALL: the UNION operator will filter out duplicate records after the data table join, and the UNION ALL operation returns simply by merging the results.
6. The optimized query method for database according to claim 1, wherein in the optimized query statement section, performing (2.5), the principle of building an index in the data table comprises:
(a) setting a maximum column threshold, and establishing an index of a certain column of data when the daily query frequency of the column of data in the data table exceeds the maximum column threshold;
(b) setting a column minimum threshold, and establishing an index of a certain column of data when the modification times of the column of data in the data table is lower than the column minimum threshold;
(c) when the type of a certain row of data is different from the types of the previous row of data and the next row of data in the data table, establishing an index of the row of data;
(d) setting a field threshold value, and establishing an index of a field when the text length of the field is smaller than the field threshold value;
(e) and aiming at the intermediate data tables connected with other data tables, establishing indexes on the fields of the intermediate data tables connected with other data tables.
7. The optimized query method for database according to claim 6, wherein in the optimized query statement section, performing (2.5), the principle of building an index in the data table further comprises:
(f) forbidding to use% xx in the established index, and meanwhile, replacing% xx with select t from t where reverse (f) like reverse ('% abc');
(g) in the where clause, use is prohibited! A ═ and < > operator;
(h) the use of is null and is not null in the index column is prohibited to determine whether or not the index column is empty.
8. The method of claim 7, wherein the number of indexes in a data table is not more than 6;
aiming at the principles (a), (b) and (d) for establishing the index, when the multi-column data of the data table meet the same principle, establishing the index on the column with the maximum difference with the set threshold value;
aiming at the principle (c) of establishing the index, when the data of multiple columns of the data table meet the same principle, if the type of data of a certain column is different from the types of the data of the upper M columns and the data of the lower N columns, the column corresponding to the maximum M + N is selected, and the index is established on the column.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110613507.4A CN113282607A (en) | 2021-06-02 | 2021-06-02 | Optimized query method for database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110613507.4A CN113282607A (en) | 2021-06-02 | 2021-06-02 | Optimized query method for database |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN113282607A true CN113282607A (en) | 2021-08-20 |
Family
ID=77283137
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110613507.4A Pending CN113282607A (en) | 2021-06-02 | 2021-06-02 | Optimized query method for database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113282607A (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113688142A (en) * | 2021-10-25 | 2021-11-23 | 北京金山云网络技术有限公司 | Index management method, device, storage medium and electronic equipment |
| CN114490724A (en) * | 2022-04-15 | 2022-05-13 | 北京奥星贝斯科技有限公司 | Method and device for processing database query statement |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101963993A (en) * | 2010-10-21 | 2011-02-02 | 江苏科技大学 | Method for fast searching database sheet table record |
| CN106919678A (en) * | 2017-02-27 | 2017-07-04 | 武汉珞佳伟业科技有限公司 | A kind of database inquiry optimization system and method |
| CN107315790A (en) * | 2017-06-14 | 2017-11-03 | 腾讯科技（深圳）有限公司 | A kind of optimization method and device of irrelevant subquery |
-
2021
- 2021-06-02 CN CN202110613507.4A patent/CN113282607A/en active Pending
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101963993A (en) * | 2010-10-21 | 2011-02-02 | 江苏科技大学 | Method for fast searching database sheet table record |
| CN106919678A (en) * | 2017-02-27 | 2017-07-04 | 武汉珞佳伟业科技有限公司 | A kind of database inquiry optimization system and method |
| CN107315790A (en) * | 2017-06-14 | 2017-11-03 | 腾讯科技（深圳）有限公司 | A kind of optimization method and device of irrelevant subquery |
Non-Patent Citations (1)
| Title |
|---|
| 创客诚品等, 北京希望电子出版社 * |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113688142A (en) * | 2021-10-25 | 2021-11-23 | 北京金山云网络技术有限公司 | Index management method, device, storage medium and electronic equipment |
| CN114490724A (en) * | 2022-04-15 | 2022-05-13 | 北京奥星贝斯科技有限公司 | Method and device for processing database query statement |
| CN114490724B (en) * | 2022-04-15 | 2022-06-14 | 北京奥星贝斯科技有限公司 | Method and device for processing database query statement |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN103761318B (en) | A kind of method and system of relationship type synchronization of data in heterogeneous database | |
| CN102521303B (en) | A kind of single-table multi-column sequence storage method for a column database | |
| AU2005203240B2 (en) | Phrase identification in an information retrieval system | |
| US5974408A (en) | Method and apparatus for executing a query that specifies a sort plus operation | |
| US6266660B1 (en) | Secondary index search | |
| CN113282607A (en) | Optimized query method for database | |
| US6778985B1 (en) | Implementing descending indexes with a descend function | |
| CN103177120B (en) | A kind of XPath query pattern tree matching method based on index | |
| CN102495906A (en) | Incremental data migration method capable of realizing breakpoint transmission | |
| CN109325032B (en) | Index data storage and retrieval method, device and storage medium | |
| KR101549220B1 (en) | Method and System for Managing Database, and Tree Structure for Database | |
| CN110928882B (en) | Memory database indexing method and system based on improved red black tree | |
| CN112231321A (en) | Oracle secondary index and index real-time synchronization method | |
| US8438173B2 (en) | Indexing and querying data stores using concatenated terms | |
| CN103902592A (en) | Method and system for realizing analytic functions based on MapReduce | |
| CN105373605A (en) | Batch storage method and system for data files | |
| CN112905642A (en) | Method for storing IEC61850 report data into relational database based on CSV mapping file | |
| US7165072B2 (en) | Method for merging information from effective dated base tables | |
| CN111190903A (en) | Btree block indexing technology for disaster recovery client | |
| Stockinger et al. | Using bitmap index for joint queries on structured and text data | |
| US20230139988A1 (en) | Efficient scan through comprehensive bitmap-index over columnar storage format | |
| KR102013839B1 (en) | Method and System for Managing Database, and Tree Structure for Database | |
| KR102351846B1 (en) | Query Optimization Method using Index Merge on Distributed Database | |
| CN110674142A (en) | Oracle database index optimization method | |
| CN114443625A (en) | Database 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 | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20210820 |
|
| RJ01 | Rejection of invention patent application after publication |