One . Table design optimization
a. Field type optimization
Generally, the tables with integer values are below 10 million , String based tables are less than five million . And in fact, a lot of the time MySQL The performance of single table still has a lot of room for optimization , It can even support more than ten million levels of data .
Use numeric type fields whenever possible , If the field only contains numerical information, try not to design as character type , This will reduce query and join performance , Increase costs , Because if it's a string , The database compares the characters of each string one by one , And the number type only needs to be compared once
2. Use as much as possible tinyint, smallint, mediumint As an integer type instead of int
3. Use as much as possible varchar/nvarchar replace char/nchar, And the length only allocates the space needed
4. Use enumerations or integers instead of string types
5. Use as much as possible TIMESTAMP Instead of DATETIME
6. Don't have too many fields in a single table , It is suggested that 20 within
7. Avoid use NULL field , Query optimization is difficult and takes up extra index space
b. Index optimization
1. Consider where and order by and join Index on the column involved , Ability to optimize queries , This avoids full table scanning
2. Don't be in where For the field in the condition null The judgment of , Otherwise, it will result in abandoning the index and performing a full table scan ( Such as : select id
from a where id != null), So try not to insert when designing tables null value , It's using 0 Or other values
When a column has a large number of duplicate data , as sex In the gender field, half are male and half female , Even if you index this field , The index may not be valid , because sql Query optimization is based on the data in the table
If there are too many indexes, the query efficiency can be improved , however insert and update The index may be rebuilt , Insertion and update efficiency will be reduced , Generally, the index number of a table should not exceed 6 individual
Try to avoid updating the data of index columns , Because the order of index data columns is the physical storage order of table records , Once the data value is changed, the order of the whole table record will be adjusted , Cost a lot of resources , So when a column needs to be updated frequently and queried frequently , You need to comprehensively measure whether to index the column
c. Temporary table optimization
1. Try to use table variables instead of temporary tables . If the table variable contains a large amount of data , Note that the index is very limited ( Primary key index only )
2. Avoid frequent creation and deletion of temporary tables , To reduce the consumption of system table resources .
Temporary tables are not unusable , Using them properly can make some routines more efficient , for example , When a dataset in a large or common table needs to be referenced repeatedly . however , For one-time events , It's better to export tables .
4. When creating a new temporary table , If a large amount of data is inserted at one time , Then you can use select into replace create table, Avoid creating a lot of
log , To increase speed ; If the amount of data is small , To ease the resources of the system table , First of all create table, then insert.
5. If temporary tables are used , Be sure to explicitly delete all temporary tables at the end of the stored procedure , before truncate table , then drop
table , This can avoid long-term locking of system tables .
6. Try to avoid cursors , Because cursors are inefficient , If the data of cursor operation exceeds 1 Wanxing , Then we should consider rewriting .
7. Before using cursor based or temporary table methods , You should first look for a set based solution to solve the problem , The set based approach is usually more effective .
Two .sql optimization
a.where Statement optimization
1. The following are conditional queries that result in a full table scan due to abandoning the index , It should be avoided
1.1 stay where Clause != or <> Operator
1.2 stay where Clause != or <> Operator
1.3 stay where Clause or To connect conditions , We can use it union Instead or
1.4 stay where Clause in and not in, For continuous data , We can use it between
1.5 stay where Clause "=" On the left side of the , Arithmetic operation or other expression operation , as :select id from t where
num/2= 1; select id from t where sum(num,1)= 2
1.6 stay where Fuzzy query in Clause like after , Use left fuzzy matching or full fuzzy matching , as "%xxx"
1.7 stay where Use parameters in Clause
2. Efficiency optimization
2.1 stay where Clause ,or It can be rewritten as in, or The efficiency of n level ,in The efficiency of log(n) level ,in It is suggested that the number of 200
2.2 In the case of select num from a where num in(select num from b)
Such a query statement , If b Table comparison a Watch big , You can use the exists replace in
b. Other query optimization
1. Never use it select * from t, It should be replaced by specific fields "*"
2. sql Be as simple as possible : One sql Only in one cpu operation , So big statements should be broken down into small ones , Reduce lock time ( A big one sql It can block the entire library )
3. Less functions and triggers , The operation of data should be implemented in the application program as much as possible
4. Try not to do a full table search , It's about using limit And the number of pages should not be too large
5. When there is only one query result , Recommended “limit 1”,“limit 1” Full table scanning can be avoided , If you find the corresponding result, the scan will not continue
6. Use ascending order as much as possible
7. Innodb On use select count(*), because Innodb Statistics are stored ;
8. The highly selective fields of the composite index come first
9. Avoid returning large amounts of data , If the amount of data is too large , Whether the demand is reasonable should be considered
10. Multiple queries for the same result set , keep SQL Sentence consistency , This makes full use of the query buffer
c. affair sql optimization
1. Try to avoid big business SQL, Big business SQL It will affect the concurrent performance of database and master-slave synchronization ;
2. To delete all records in the table, please use truncate, Don't use it delete
3. Try not to use stored procedures