SQL optimization solution for millions of databases (1)

Editor's note: the goal of database optimization is to avoid disk I/O bottlenecks, reduce CPU utilization, and reduce resource competition. However, you must be careful not to optimize it blindly.

Related Articles: Database optimization design considerations, relational database performance optimization summary, engineers, developers, nothing to do with optimization.

Optimization solution 1-15

1. To optimize the query, try to avoid full table scanning. First, consider creating an index on the columns involved in where and order.

2. Try to avoid null value determination on the field in the where clause. Otherwise, the engine will discard the index and perform full table scanning, for example:

 
 

  
  
select id from t where num is null 
 
 

You can set the default value 0 on num to make sure that the num column in the table does not have a null value, and then query it like this:

 
 

  
  
select id from t where num=0  
 
 

3. Try to avoid using it in the where clause! = Or <> operator. Otherwise, the engine will discard the index for full table scanning.

4. Try to avoid using or in the where clause to connect to the condition. Otherwise, the engine will discard the index and perform full table scanning, for example:

 
 

  
  
select id from t where num=10 or num=20  
 
 

You can query it as follows:

 
 

  
  
select id from t where num=10  
  
  
union all 
  
  
select id from t where num=20  
 
 

5. Use in and not in with caution. Otherwise, a full table scan may occur, for example:

 
 

  
  
select id from t where num in(1,2,3)  
 
 

For continuous values, you can use between instead of in:

 
 

  
  
select id from t where num between 1 and 3  
 
 

6. The following query will also cause a full table scan:

 
 

  
  
select id from t where name like ‘%abc%’  
 
 

To improve efficiency, you can consider full-text search.

7. If a parameter is used in the where clause, a full table scan is performed. Because SQL parses local variables only at runtime, the optimizer cannot postpone the selection of the access plan to runtime; it must be selected at compilation. However, if an access plan is created during compilation, the value of the variable is still unknown and thus cannot be used as an input for index selection. The following statement performs a full table scan:

 
 

  
  
select id from t where num=@num 
 
 

You can change it to force query to use the index:

 
 

  
  
Select id from t with (index name) where num = @ num
 
 

. Avoid performing expression operations on fields in the where clause whenever possible. This will cause the engine to discard the use of indexes for full table scanning. For example:

 
 

  
  
select id from t where num/2=100 
 
 

Should be changed:

 
 

  
  
select id from t where num=100*2 
 
 

9. Avoid performing function operations on fields in the where clause as much as possible, which will cause the engine to stop using the index for full table scanning. For example:

 
 

  
  
Select id from t where substring (name, 1, 3) = 'abc'-name id starting with abc
  
  
Select id from t where datediff (day, createdate, '2017-11-30 ') = 0-'2017-11-30' generated id
 
 

Should be changed:

 
 

  
  
select id from t where name like ‘abc%’  
  
  
select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′ 
 
 

10. do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the where clause. Otherwise, the system may not be able to correctly use the index.

11. when using an index field as a condition, if the index is a composite index, you must use the first field in the index as the condition to ensure that the system uses the index, otherwise, the index will not be used, and the field order should be consistent with the index order as much as possible.

12. Do not write meaningless queries. If you need to generate an empty table structure:

 
 

  
  
select col1,col2 into #t from t where 1=0 
 
 

This type of code will not return any result set, but will consume system resources, should be changed to this:

 
 

  
  
create table #t(…) 
 
 

13. in many cases, replacing in with exists is a good choice:

 
 

  
  
select num from a where num in(select num from b) 
 
 

Replace the following statement:

 
 

  
  
select num from a where exists(select 1 from b where num=a.num) 
 
 

14. not all indexes are valid for queries. SQL queries are optimized based on the data in the table. When there is a large number of duplicate data in the index column, SQL queries may not use indexes, for example, if a table contains sex fields, male and female are almost half of each other, indexing sex does not play a role in query efficiency.

15. the more indexes, the better. Although the index can improve the efficiency of the select statement, it also reduces the efficiency of insert and update, because the insert or update statements may recreate the index, therefore, you need to carefully consider how to create an index, depending on the actual situation. It is recommended that the number of indexes in a table be no more than six. If there are too many indexes, consider whether the indexes on some columns that are not frequently used are necessary.