Several methods of sql optimization

1. To optimize the query, try to avoid full table scans. First, consider creating indexes on the columns involved in where and order by.

2. Try to avoid making null value judgments on fields in the where clause, otherwise the engine will give up using the index and perform a full table scan, such as:

select id from t where num is null

can be set on num The default value is 0. Make sure there is no null value in the num column in the table, and then query like this:

select id from t where num=0

3. Try to avoid using != or operators in the where clause, otherwise it will The engine gives up using the index and performs a full table scan.

4. Try to avoid using or in the where clause to connect conditions, otherwise the engine will give up using the index and perform a full table scan, such as:

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

Can be queried like this :

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

5.in and not in should also be used with caution, otherwise it will lead to a full table scan, such as:

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

For continuous values, if you can use between, do not use 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%'

7. Try to avoid expression operations on fields in the where clause , which will cause the engine to give up using the index and perform a full table scan. For example:

select id from t where num/2=100

should be changed to:

select id from t where num=100*2

8. Try to avoid performing functional operations on fields in the where clause, which will cause the engine to give up using the index and Perform a full table scan. For example:

select id from t where substring(name,1,3)='abc'--name以abc开头的id

should be changed to:

select id from t where name like 'abc%'

9. 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 use the index correctly.

10. When using an index field as a condition, if the index is a composite index, then the first field in the index must be used 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.

11. Do not write some meaningless queries. For example, 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 it will consume system resources and should be changed. Like this:

create table #t(...)

The above is the detailed content of Several methods of sql optimization. For more information, please follow other related articles on the PHP Chinese website!