1. Optimize query , Full scan should be avoided as much as possible , The fields used to retrieve and sort data should be indexed , as where Clause for search ,order
by Clause for sorting , Therefore, the fields involved in these two clauses need to be indexed .
2. Should be avoided in where Negative operator in Clause , If not equal to （!= or <>）, Otherwise, the database engine will discard the index and scan the whole table .
3. Try to avoid where Use or in Clause （or） As connection condition , Otherwise, the database engine will discard the index and scan the whole table .
As follows SQL Statements can cause performance problems
select id,name,age from persons where name = 'Bill' or age > 30
Because of this SQL Statement used or, So the database engine will scan the whole table , To avoid full table scanning , You can put this SQL Change the statement to the following form .
select id,name,age from persons where name = 'Bill' union all select
id,name,agefrom persons where num = 20
4. Try to avoid where Use in Clause null Make a judgment , Otherwise, the database engine will discard the index and scan the whole table .
First look at the following SQL sentence ：
select id,name,age from persons where age is null
To avoid using null, Can be set age The default value for the field is 0, So you can go through the following SQL Statement achieves the same result .
select id,name,age from persons where age = 0
5. Try not to use like Retrieve data , Because it will also cause the database engine to abandon the use of indexes for full table scanning .
for example , Below SQL Statement execution is very inefficient ：
select id,name,age from persons where name like '%John%'
If you really want to do fuzzy query , Full text retrieval is available .
6. stay where Avoid using expressions in fields as much as possible in Clauses （ Include function operations , Arithmetic operations, etc ）, Otherwise, the database engine will discard the index and scan the whole table .
for example , Below SQL Poor performance of statement execution
select id,name,age from persons age / 2 > 12
Expression transformation should be used , Change to the following form ：
select id,name,age from persons age > 2 * 12
Or simply change it to the following form ：
select id,name,age from persons age > 24
7. Use should be avoided as much as possible in and not in, Otherwise, it will also result in a full table scan .
If the following writing is not recommended ：
select id, name,age from persons where age in (22,23,24)
If the value is continuous , Should use between, Instead of using in, If the values are discontinuous , Can be divided into multiple SQL, use union all Connection query results .
select id,name,age from persons where age between 22 and 24 select id,name,age
from persons where age = 22 union all select id,name,age from persons where age
= 26 union all select id,name,age from persons where age = 30
Try to avoid where Use parameters in Clause , Otherwise, it will also result in a full table scan . This is because the parameter needs to be in the SQL Replace at run time , and SQL optimization （ Using indexes is part of the optimization ） At compile time . So when the database engine retrieves the parameters , Because the specific meaning of the parameter is unknown , So it can't be optimized , Of course, indexes are not available .
Not indexed SQL sentence ：
select id,name,age from persons where name = @name
To use indexes , You can change to the following way to force index ：
select id,name,age from persons with(index(name_index)) where name = @name
among name_index Is based on name Index name on field .
9. Try not to execute meaningless queries , If the condition is completely false Queries for ：
select id,name,age into persons1 from persons where age < 0
This code will return an empty result set , And it will consume a lot of system resources , If you really want to build an empty table , It should be used directly create table sentence .
If you are using an index that matches the index , Only the 1 Only when fields are used as conditions can the database engine use the index , Otherwise, the compliant index will not be used . And try to keep the order of the fields in the same order as the index . for example ,name_index yes first_name and last_name Match index of field , Use the following SQL Statement will use the index .
select id,first_name,last_name from persons where first_name = 'Bill'
11. If you have to SQL Use in statement in, Then use exists replace in It's a good idea ：
select id,num from t where num in (select num from h)
You should use the following SQL Statement substitution ：
select id,num form t where exists(select 0 from h where num = t.num)
Index is not always valid , If the index column has a large amount of duplicate data , Then the database engine may not use the index . for example ,sex There are only two possible values for a field ：male and female, Maybe these two values are half of each other , It's like this sex There is no point in indexing fields .
Use numeric fields if you can . Because comparing numeric fields is much more efficient than character fields , This is because the value of the character type is compared , Compare letters one by one , And numerical values , Just compare a number . So if only the value of numerical information is included , Try to use fields of numeric type . for example ,age,salary etc .
Fixed length fields should be avoided as much as possible , as char,nchar. Using variable length fields is a great choice . Because the space occupied by variable length fields is allocated on demand , So it takes up less space . For queries , without doubt , Of course, the query efficiency of fields with small space is higher .
15. Try to return fields and records as needed , for example ：
select id,name,age from persons where age > 20
Try to use “*” Return all unnecessary fields , You don't need to look up all the records at once , As follows SQL The query efficiency of statements is very low when the data volume is large .
select * from persons
Index has advantages and disadvantages , Add index , Can improve select Implementation efficiency of , But the price is going on insert and update During operation , May reduce efficiency . Because insert and update The operation usually needs to rebuild the index . So in a table, the more indexes, the better . My suggestions are as follows ：
（1） If a table does most of the time select operation , More indexes do improve performance most of the time , But there's a premise , Just not frequently insert and update operation .
（2） Cannot have too many indexes in a table , Better not exceed 6 individual , Otherwise, it's better to optimize the database .
17. Updates should be avoided as much as possible clustered Index data column , because clustered
The order of index data columns is the physical storage order of table records , Once the value of this column changes, the order of the records in the whole table will be adjusted , It will cost a lot of resources . If the application system needs to be updated frequently clustered
Index data column , Consider whether the index should be set as clustered Indexes .
18. Try to avoid returning Dali data to the client , If the amount of data is too large , Need to change , Or return by page , If used MySQL In limit Clause now returns data .
19. Try to avoid using cursors , Because the efficiency of cursors is poor , If the data of cursor operation exceeds 1 Wanhang , Then we should adopt other plans .
20. Before using cursor based or temporary table methods , We should first find a solution based on data set to solve the problem , Dataset based methods are usually more efficient .
21. If a temporary table is used , Be sure to explicitly delete all temporary tables at the end of the stored procedure , Use first truncate table Clear data in table , then use drop
table Delete physical tables completely , This avoids long-term locking of system tables .
22. Avoid frequent creation and deletion of temporary tables , To reduce the consumption of system table resources .
23. 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 improve execution efficiency ; If the amount of data is small , To mitigate the resources of system tables , Should first create table, Then use insert insert data .
24. Set at the beginning of all stored procedures and triggers SET NOCOUNT ON , Set at end SET NOCOUNT OFF
. No need to send to client after each statement of stored procedure and trigger is executed DONE_IN_PROC news .
25. Avoid large transaction operations as much as possible , Improve system concurrency .
26. Try to insert multiple pieces of data at once , for example , Use the following SQL Statement performance will be very low ：
insert into persons(id,name,age) values('Bill',24) insert into
persons(id,name,age)values('Mike',26) insert into persons(id,name,age) values('
To improve performance , You can insert it all at once 3 Records .
insert into persons(id,name,age) values('Bill',24),('Mike',26),('John',20)
27. If you have to use like Fuzzy query , Do not prefix keywords %.
select id,name,age from persons where name like '%abc%'
If you prefix a keyword %, Then the query must go to full table query .
Positive example ：
select id,name,age from persons where name like 'abc%'
28. Try to use union all replace union
union and union
all The main difference is that the former needs two （ Or more ） Uniqueness filtering after result set merging , This involves sorting , Add a lot of cpu operation , Increase resource consumption and delay . So when we can confirm that duplicate result sets are not possible or we don't care about them , Try to use union
all instead of union.
29. Try to use equivalent connection
The equivalent connection is inner join, Also known as inline , and left join and right join Is external connection .
First look at the following SQL sentence
select a.id,a.name,b.id,b.name from a left join b on a.id = b.id select
a.id,a.name,b.id,b.namefrom a right join b on a.id = b.id select
a.id,a.name,b.id,b.namefrom a inner join b on a.id = b.id
above 3 strip SQL sentence , The first two use left connection and right connection respectively , And the last one uses internal connections , After actual operation , Use internal connected SQL The efficiency of statement execution is better than that of left join and right join . So on the premise of meeting the needs , Internal connections should be used whenever possible （ Equivalent connection ）.
30. Try to replace subqueries with outer joins
select id,name from a where exists (select id from b where id>=10 and
On top SQL In statement , The database engine will check the appearance first a Execute full table query , And then according to product_id Execute subqueries one by one , If the outer table （a surface ） There's a lot of data in , Query performance can be very poor . So we should SQL Change the statement to the following form ：
select id,name from a inner join b on A.product_id=b.product_id where b.id>=10