1. Rational use of index

Index is an important data structure in database , Its basic purpose is to improve the query efficiency . Now most database products adopt IBM First proposed ISAM index structure . Use the index just right , Its use principle is as follows :

◆ Connect frequently , However, there is no index on the column specified as a foreign key , Fields that are not frequently joined are automatically indexed by the optimizer .

◆ To sort or group frequently ( Immediately group by or order by operation ) Index on the column of .

◆ In the conditional expression often used in the different values of the column to establish a search , Do not index columns with fewer values . For example, in the employee table “ Gender ” There are only “ male ” And “ female ” Two different values , So there's no need to index . If the index is built, it will not improve the query efficiency , It will seriously slow down the update speed .

◆ If there are more than one column to sort , Composite indexes can be built on these columns (compound index).

◆ Using system tools . as Informix The database has one tbcheck tool , You can check on suspicious indexes . On some database servers , The index may fail or the read efficiency may be reduced due to frequent operations , If a query using an index slows down , You can try it tbcheck The tool checks the integrity of the index , Repair if necessary . in addition , When the database table updates a large amount of data , Deleting and rebuilding indexes can improve query speed .

2. Avoid or simplify sorting

Repetitive sorting of large tables should be simplified or avoided . When the index can be used to automatically generate output in the appropriate order , The optimizer avoids the sorting step . Here are some factors :

◆ One or more columns to be sorted are not included in the index ;

◆group by or order by The order of the columns in the clause is not the same as the order of the index ;

◆ Sorted columns come from different tables .

To avoid unnecessary sorting , It is necessary to add indexes correctly , Merge database tables reasonably ( Although sometimes it may affect the normalization of tables , But compared with the improvement of efficiency, it is worth it ). If sorting is unavoidable , Then try to simplify it , Such as narrowing the range of sorted columns .

3. Eliminating sequential access to large table row data

In nested queries , Sequential access to tables can have a fatal impact on query efficiency . For example, sequential access strategy is adopted , A nest 3 Layer query , If every layer queries 1000 That's ok , Then this query is to query 10 Billion data . The main way to avoid this is to index the joined columns . for example , Two tables : Student list ( Student number , full name , Age ……) And course schedule ( Student number , Course number , achievement ). If two tables are to be joined , It's going to be in “ Student number ” Index this join field .

Union can also be used to avoid sequential access . Although there are indexes on all the check columns , But some forms of where Clause forces the optimizer to use sequential access . The following query forces the orders Table execution sequence operation :

SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR

Although in the customer_num and order_num Index built on , However, in the above statement, the optimizer still uses the sequential access path to scan the entire table . Because this statement is retrieving a collection of detached rows , So it should be changed to the following statement :

SELECT * FROM orders WHERE customer_num=104 AND order_num>1001 UNION SELECT *
FROM orders WHERE order_num=1008 

This allows the query to be processed using the index path .

4. Avoid related subqueries

The label of a column is in both the main query and the where Appears in the query in clause , It is likely that when the column values in the main query change , Subquery must be queried again . More levels of query nesting , The lower the efficiency , Therefore, subqueries should be avoided as much as possible . If subqueries are unavoidable , Filter out as many rows as possible in the subquery .

5. Avoiding difficult normal expressions

MATCHES and LIKE Keywords support wildcard matching , Technically, it's called a normal expression . But this matching is particularly time-consuming . for example :

SELECT * FROM customer WHERE zipcode LIKE “98_ _ _” 

Even in zipcode Field is indexed , In this case, sequential scanning is also used . If you change the sentence to SELECT * FROM customer WHERE
zipcode >“98000”, The index is used to query when the query is executed , Obviously, it will greatly increase the speed .

in addition , Also avoid non starting substrings . For example, statements :SELECT * FROM customer WHERE
zipcode[2,3]>“80”, stay where A non starting substring is used in clause , Therefore, the statement does not use an index .

6. Using temporary tables to speed up queries

Sort a subset of the table and create a temporary table , Sometimes it can speed up the query . It helps to avoid multiple sort operations , And there are other ways to simplify the work of the optimizer . for example :

SELECT cust.name,rcvbles.balance,……other columns FROM cust,rcvbles WHERE
cust.customer_id = rcvlbes.customer_id AND rcvblls.balance>0 AND
cust.postcode>“98000” ORDER BY cust.name 

If the query is to be executed more than once, but not more than once , You can find all outstanding customers and put them in a temporary file , And sort by customer's name :

SELECT cust.name,rcvbles.balance,……other columns FROM cust,rcvbles WHERE
cust.customer_id = rcvlbes.customer_id AND rcvblls.balance>0 ORDER BY cust.name
INTO TEMP cust_with_balance 


Then query in the temporary table in the following way :

SELECT * FROM cust_with_balance WHERE postcode>“98000” 

There are fewer rows in the temporary table than in the main table , And the physical order is the order required , Reduced disk I/O, Therefore, the query workload can be greatly reduced .

be careful : Changes to the main table will not be reflected after the temporary table is created . In the case of frequent data changes in the main table , Be careful not to lose data .

7. Replacing non sequential access with sorting

Non sequential disk access is the slowest operation , This is reflected in the back and forth movement of the disk access arm .SQL The statement hides this situation , This makes it easy to write queries that require access to a large number of non sequential pages when writing applications .
Sometimes , Using the sorting ability of the database to replace the non sequential access can improve the query .

©2019-2020 Toolsou All rights reserved,
QQ Login interface implementation code implementation mysql Addition, deletion, modification and query of database JAVA Experiment 4 set and functional programming experiment about String How to create objects VHDL——4 choose 1 Data selector C language ( Guess numbers games ) Blue Bridge Cup MCU advanced module --NE555 I don't memorize eight part essays , You go to the interview ? Hill sorting of sorting algorithm ——c++ realization python What does built-in function mean _python What are the built-in functions