tslilove
2022/10/23阅读:42主题:山吹
MySQL常见优化总结
导语
大家好,我是亮哥,本期给大家总结一下SQL语句优化方法,以下简称SQL优化。为什么要优化?想必同学们能看到这边文章,并且点进来进行浏览,肯定是有了解SQL优化的需求的。在我们日常工作中,可能会遇到各种各样的需求,以及复杂场景,那么我们所写的SQL语句执行的效率逐渐变慢了,或者我们根本不知道在如何写出优雅的SQL语句而导致SQL执行的效率不高,这是时常有的。今天给大家总结常用优化方法,帮助大家提升SQL语句的执行效率(注:具体需实践验证)。
优化1
-
使用LIKE关键字的查询语句
避坑:%字符不能在第一个位置
例如:
WHERE city LIKE '%城市'
是因为最左边是需要匹配任意字符,从而进行全表扫描,导致索引失效,所以要避免%在开头的情况。
优化2
-
使用多列索引查询语句
避坑:查询条件中至少需包含组合索引的第一个字段
例如:组合索引(a,b,c)
-- 优化前
WHERE b=2 AND c=3
-- 优化后
WHERE a=1 AND b=2 AND c=3
WHERE a=1 AND c=3 AND b=2
WHERE a=1 AND c=3
是因为查询条件中没有包含组合索引的第一个索引,不管如何,你的查询条件中至少需要索引a
优化3
-
使用OR关键字的查询语句
避坑:OR前后需使用索引字段
例如:表里有索引字段a,索引字段b,普通字段c
-- 优化前
WHERE b=2 OR c=3
-- 优化后
WHERE a=2 OR b=3
是因为查询条件中OR的前后,不管如何,你的查询条件中至少需要索引a,否则将导致索引失效;
其次我们可以利用UNION ALL来代替OR
SELECT 字段1,... FROM table WHERE a=2
UNION ALL
SELECT 字段1,... FROM table WHERE b=3
优化4
-
使用子查询的时候
避坑:改子查询为连接查询
例如:
-- 优化前
SELECT col1,... FROM table1 WHERE col2 IN (SELECT col2 FROM table2) a
-- 优化后
SELECT col1,... FROM table1 as t1 INNER JOIN table2 as t2 ON t1.id = t2.id
优化5
-
使用谓词IN的时候
避坑:当子查询返回的结果集比较大时,应使用关联查询或者EXISTS来代替IN
例如:
-- 优化前
SELECT col1,... FROM table1 WHERE col2 IN (SELECT col2 FROM table2 WHERE col2>1) a
-- 优化后1
SELECT col1,... FROM table1 as t1 INNER JOIN table2 as t2 ON t1.id = t2.id WHERE t2.col2>1
-- 优化后2
SELECT col1,... FROM table1 as t1 EXISTS(SELECT 1 FROM table2 as t2 WHERE t1.id = t2.id AND t2.col2>1)
优化6
-
在WHERE条件中使用<>或者!=和NOT IN符号
避坑:尽量减少使用不等号来作为过滤条件
使用不等号会索引失效,从而导致全表扫描
优化7
-
在索引字段上进行计算
避坑:尽量避免在索引上进行计算
例如索引字段col1
SELECT * FROM table WHERE col1*2 > 100
-- 或者
SELECT * FROM table WHERE INSTR(col1,'好')>0
例如上面这样,我们在WHERE条件的中的运算符左侧进行运算,这样数据库引擎是不允许这样做的,从而导致使用不了索引
优化8
-
在使用排序的时候
避坑:避免排序
例如如下运算会进行排序
以下总结来着SQL进阶教程
GROUP BY子句
ORDER BY子句
聚合函数(SUM、COUNT、AVG、MAX、MIN)
DISTINCT
集合运算(UNION、INTERSECT、EXCEPT)
窗口函数(RANK、ROW_NUMBER、DENSE_RANK等)
tips:
-
在使用UNION的时候,如果没有特别要求,尽量使用 UNION ALL -
在使用DISTINCT去重时,可使用EXISTS代替 -
在使用极值函数时,尽可能利用索引字段,避免全表扫码 -
能写在WHERE子句里的条件不要写在HAVING子句里 -
在GROUP BY子句和ORDER BY子句中使用索性,来提升查询数据

作者介绍