Common SQL statement optimization skills (1) and SQL statement optimization skills

Common SQL statement optimization skills (1) and SQL statement optimization skills
To improve the efficiency of SQL statement execution, the most common method is to create an index and avoid full table scan. We will organize some common SQL optimization techniques to avoid full table scanning. A simple optimization may increase your SQL Execution efficiency by several or even dozens of times.
 
1. Avoid using is null or is not null in the where clause to judge the field.

For example:

select id from table where name is null

In this query, even if we set an index for the name field, the query analyzer will not use it, so the query efficiency is lower. To avoid such a query, when designing the database, we try to set the default value for null fields. If we set the default value of the name field to 0, then we can query it like this:

 select id from table where name = 0

2. Avoid using it in the where clause! = Or <> operator.

For example:

 select name from table where id <> 0

When the database is being queried! = Or <> operators do not use indexes, but databases use indexes for <, <=, =,>,> =, between and. Therefore, the correct statement for the above query should be:

 select name from table where id < 0 union all select name from table where id > 0

Here, why didn't we use or to link the two conditions after where? This is the 3rd optimization skills we will talk about below.

 
3. Avoid using or in the where clause to link conditions.
 
For example:

 select id from table where name = 'C++' or name = 'C#'

In this case, we can write as follows:

 select id from table where name = 'C++' union all select id from table where name = 'C#'

4. Use less in or not in
 
Although the in condition will use an index and will not scan the entire table, other methods may be better in some specific situations. For example:

 select name from table where id in(1,2,3,4,5)

For such continuous values, we can use between and, for example:

 select name from table where id between 1 and 5

5. Pay attention to the use of wildcards in like.
 
The following statement will cause full table scanning and should be used as little as possible. For example:
 

select id from table where name like '%jayzai%'

Or

 select id from table where name like '%jayzai'

The execution efficiency of the following statement is much faster, because it uses an index:

 select id from table where name like 'jayzai%'

6. Avoid expression operations on fields in the where clause.
 
For example:

 select name from table where id/2 = 100

The correct statement should be:

 select name from table where id = 100*2

7. Avoid performing function operations on fields in the where clause.
For example:

 select id from table where substring(name,1,8) = 'jayzai'

Or

 select id from table where datediff(day,datefield,'2014-07-17') >= 0

These two statements all perform function processing on the fields, so that the query analyzer does not use the index. The correct method is as follows:

 select id from table where name like 'jayzai%'

Or

select id from table where datefield <= '2014-07-17'

That is to say, do not perform functions, arithmetic operations, or other expression operations on the left of = in the where clause.

 

8. in subqueries, replacing in with exists is a good choice.
 
For example:

 select name from table where id in (select id from b) 

If we replace this statement with the following statement:

select name from table a where exists (select 1 from b where id = a.id)

In this way, the query results are the same, but the query speed of the following statement is much faster.