Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what database query performance needs to pay attention to". In daily operation, I believe many people have doubts about what problems database query performance needs to pay attention to. Xiaobian consulted all kinds of information and sorted out simple and easy operation methods. I hope to help you answer the doubts of "what database query performance needs to pay attention to"! Next, please follow the small series to learn together!

1. To optimize queries, avoid full table scans as much as possible, and first consider indexing where and order by columns.

2. Try to avoid making null value judgments on fields in where clauses, otherwise the engine will abandon using indexes and perform full table scans, such as:

select id from t where num is null

You can set a default value of 0 on num, make sure there is no null value in num column in the table, and then query like this:

select id from t where num=0

3. Avoid using!= in where clauses Or operator, otherwise the engine forgoes index use and performs a full table scan.

4. Avoid using or in the where clause to join conditions, otherwise it will cause the engine to abandon the use of indexes and perform a full table scan, such as:

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

You can query:

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 full table scanning, such as:

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 result in a full table scan:

select id from t where name like '%abc%'

To improve efficiency, consider full-text search.

7. Using parameters in the where clause also results in a full table scan. Because SQL resolves local variables only at runtime, the optimizer cannot defer access plan selection to runtime; it must make the selection at compile time. However, if the access plan is established at compile time, the value of the variable is unknown and therefore cannot be selected as an input for the index. For example, the following statement will perform a full table scan:

select id from t where num=@num

You can force queries to use indexes instead:

select id from t with(index) where num=@num

8. Expression operations on fields in where clauses should be avoided as much as possible, as this will cause the engine to forgo indexes in favor of a full table scan. For example:

select id from t where num/2=100

should read

select id from t where num=100*2

9. Function operations on fields in where clauses should be avoided as much as possible, which will cause the engine to forgo indexes in favor of a full table scan. For example:

select id from t where substring(name,1,3)='abc'--name id starting with abc

select id from t where datediff(day, created,'2005-11- 30')=0--'2005-11-30'id generated

should read

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30' and createdate

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.