1. Optimize queries and avoid them as much as possible For a full table scan, you should first consider establishing an index on the columns involved in where and order by.
2. Try to avoid the where clause The null value of the field is judged in the field, otherwise it will cause the engine to give up using the index and perform a full table scan, such as: select id from t where num is null, you can set the default value of 0 on num to ensure that the num column in the table does not have a null value, and then Query like this: select id from t where num=0
3. Try to avoid using the != or <> operator in the where clause, otherwise the engine will give up using the index and perform a full table scan.
4. Try to avoid using it in the where clause or to join the condition, otherwise it will cause the engine to give up using the index and perform a full table scan, such as: select id from t where num=10 or num=20, you can query 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 cause Full table scan, such as: select id from t where num in(1,2,3) For continuous values, don’t use in if you can use between: select id from t where num between 1 and 3
6. The following query will also lead to full Table scan: select id from t where name like’%李%’ To improve efficiency, you can consider full-text search.
7. If you use parameters in the where clause, it will also cause a full table scan. Because SQL only parses local variables at runtime, the optimizer cannot defer the choice of access plan until runtime; it must choose at compile time. However, if the access plan is established at compile time, the value of the variable is still unknown and therefore cannot be used as an input item for index selection. For example, the following statement will perform a full table scan: select id from t where [email protected] can be changed to force the query to use an index: select id from t with(index (index name)) where [email protected]
8. Try to avoid performing expression operations on fields in the where clause, which will cause the engine to abandon the use of indexes and perform full table scans. Such as: select id from t where num/2=100 should be changed to: select id from t where num=100*2
9. Should try to avoid the field in the where clause Perform function operations, 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’, the id whose name starts with abc should be changed to:
select id from t where name like’abc%’
10. Do not use the “in the where clause =” Perform functions, arithmetic operations or other expression operations on the left, otherwise the system may not be able to use the index correctly.
11. When using index fields as a condition, if the index is a compound index, then the first field in the index must be used as Conditions can 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 some without Meaningful query, if you need to generate an empty table structure: select col1, col2 into #t from t where 1=0
This kind of code will not return any result set, but will consume system resources, should Change it to this:
create table #t(…)
13. In many cases, using exists instead of in is a good choice: select num from a where num in(select num from b)
Use the following Statement replacement:
select num from a where exists(select 1 from b where num=a.num)