tslilove

V1

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:

  1. 在使用UNION的时候,如果没有特别要求,尽量使用 UNION ALL
  2. 在使用DISTINCT去重时,可使用EXISTS代替
  3. 在使用极值函数时,尽可能利用索引字段,避免全表扫码
  4. 能写在WHERE子句里的条件不要写在HAVING子句里
  5. 在GROUP BY子句和ORDER BY子句中使用索性,来提升查询数据

分类:

后端

标签:

数据库

作者介绍

tslilove
V1