Introduction to SQL statement optimization strategies

1. Avoid full table scan

To optimize the query, try to avoid full table scan. First, consider where and order by Create an index on the involved columns.

2. Avoid judging null values

You should try to avoid judging null values ​​for fields in the where clause, otherwise the engine will give up using the index and perform the entire table Scan, such as:

select id from t where num is null

You can set the default value 0 on num, ensure that there is no null value in the num column in the table, and then query like this:

select id from t where num=0

(free learning video tutorial recommendation: mysql video tutorial)

3. Avoid unequal value judgment

You should try to avoid using it in the where clause != or <> operator, otherwise the engine will give up using the index and perform a full table scan.

4. Avoid using or logic

You should try to avoid using or in the where clause to connect conditions, otherwise it will cause The engine gives up using the index and performs 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. Use in and not in logic with caution

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

select id from t1 where num in(select id from t2 where id > 10)

At this time, the outer query will scan the entire table without using the index. It can be modified to:

select id from t1,(select id from t1 where id > 10)t2 where t1.id = t2.id

At this time, the index is used, which can significantly improve query efficiency.

6. Pay attention to fuzzy query

The following query will also cause a full table scan:

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

If fuzzy query is a necessary condition, you can use it Select id from t where name like 'abc%' to implement fuzzy query, in which case the index will be used. If header matching is necessary logic, it is recommended to use a full-text search engine (Elastic search, Lucene, Solr, etc.).

7. Avoid field calculations in query conditions

You should try to avoid expression operations on fields in the where clause, which will cause the engine to give up using the index. 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. Avoid performing function operations on fields in query conditions

Should try to avoid using the where clause Function operations are performed on fields in the field, 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. Pay attention to the

on the left side of the WHERE clause "=" and do not use it in the where clause. Do not perform functions, arithmetic operations or other expression operations on the left side of "=" in the sentence, otherwise the system may not be able to use the index correctly.

10. Use of composite index

When using an index field as a condition, if the index is a composite index, the first field in the index must be used Only when used as a condition can the system use 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 define unobjectionable queries.

Do not write 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 anything. The result set, but it will consume system resources, should be changed to this:

create table #t(...)

12. exists

In many cases, it is a good idea to use exists instead of in. Select:

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

Replace with the following statement:

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

13. The index may also fail

并不是所有索引对查询都有效，SQL 是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL 查询可能不会去利用索引，如一表中有字段sex，male、female 几乎各一半，那么即使在sex 上建了索引也对查询效率起不了作用。

14、表格字段类型选择

尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。尽可能的使用varchar 代替char ，因为首先可变长度字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

15、查询语法中的字段

任何地方都不要使用select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

16、索引无关优化

不使用*、尽量不使用union，union all 等关键字、尽量不使用or 关键字、尽量使用等值判断。表连接建议不超过5 个。如果超过5 个，则考虑表格的设计。（互联网应用中）表连接方式使用外联优于内联。外连接有基础数据存在。

如：A left join B,基础数据是A。A inner join B，没有基础数据的，先使用笛卡尔积完成全连接，在根据连接条件得到内连接结果集。

大数据量级的表格做分页查询时，如果页码数量过大，则使用子查询配合完成分页逻辑。

Select * from table limit 1000000, 10
Select * from table where id in (select pk from table limit100000, 10)

相关文章教程推荐：mysql教程

The above is the detailed content of Introduction to SQL statement optimization strategies. For more information, please follow other related articles on the PHP Chinese website!