【MySQL】SQL优化

目录

一.EXPLAIN执行计划

1.用法

2.explain字段

二.WHERE子句优化

三.范围优化

1.单部索引范围访问

2.多部索引范围访问

四.索引合并与索引下推

1.索引合并

2.索引下推

六.IS NULL优化

七.ORDER BY优化

1.可以使用索引来解析ORDER BY

2.不使用索引来处理ORDER BY，但可以使用索引作为where条件

八.聚合查询优化

1.GROUP BY优化

2.DISTINCT优化

九.索引失效

十.索引使用原则

一.EXPLAIN执行计划

explain可以分析SQL语句的执行情况，以便我们优化SQL语句。注意，explain并不是真正的执行SQL语句，只是分析SQL语句。

1.用法

直接在explain后加上要分析的SQL语句

explain SQL语句

2.explain字段

下面简洁说明一下explain字段：

列名	说明
id	select标识符，查询语句中select的序号。如果查询语句中包含子查询或合并查询，则每个查询的编号依次递增
select_type	select类型
table	查询的表
partitions	查询的分区，对于非分区表，值为NULL
type	JOIN类型
possible_keys	可能选择的索引
key	实际选择的索引
key_len	索引的字节长度，如果key为NULL，那么长度也为NULL
ref	在查询过程中哪些列或常量与key列中指定的索引进行比较
rows	估算要检查的行数，数据越小越好
filtered	按条件筛选行的百分比，数据越大越好
Extra	附加信息

下面对部分字段进行详细介绍：

1）select_type 列

select类型：

select_type值	说明
SIMPLE	简单查询，没有子查询或合并查询
PRIMARY	外层查询
UNION	UNION中的第二个及之后的查询语句
UNION RESULT	UNION的结果
SUBQUERY	子查询中
INSERT	insert语句
UPDATE	update语句
DELETE	delete语句

2）table 列

查询时数据行所在表的名称

<union M,N>：表示合并查询用的表，M和N是id列的值

<subquery N>：表示子查询用的表，N是id列的值

3）type 列

展示表是如何连接的，性能排序如下：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >
unique_subquery > index_subquery > range > index > ALL

类型	说明
system	表中只有一行数据，不用任何扫描，性能极高，是const类型的特殊情况，一般是在MyISAM存储引擎中
const	当查询中的条件通过主键索引或唯⼀索引与常量进行比较时，结果最多有⼀个匹配的行，查询性能极高，且只会返回一行数据
eq_ref	唯一非空索引列，应用于多表连接的场景，表关联条件是主键索引或唯⼀非空索引时使用等号 ( = ) 进行索引列的比较，每行只匹配⼀条记录
ref	非空但不唯一，SQL语句中使用了普通索引，返回的结果可能是多行组成的结果集
fulltext	使用全文索引
ref_or_null	不唯一，索引列必须是可以为空的列
index_merge	在查询中使用了多个索引，OR 两边必须是单独索引，最终通过不同索引检索数据，然后对结果集进行合并，Key_len显示最长的索引长度
unique_subquery	子查询中返回的是外层表的主键索引或唯一索引
index_subquery	子查询中返回的是外层表的普通索引
range	使⽤索引列进行范围查询，当使用<>、>、>=、<、<=、is NULL、<=>、BETWEEN、LIKE或IN()操作符，索引列与常量进行比较
index	扫描整个索引树而不扫描整个表，比如只使用索引排序而不使用条件查询
ALL	最差的情况，表示MySQL必须对全表进行逐行扫描才可以以找到匹配行，遇到这种情况通常建议在查询的列上加索引来避免全表扫描

我们在实际优化SQL的时候，要尽量避免最后三个，最后三个效率低。

4）Extra 列

类型	说明
Using temporary	当使用非索引列进行分组时，会用临时表进行排序，优化时可以考虑为分组的列加索引
Using filesort	当使用非索引列进行排序时会用到为文件内排序，优化时可以考虑为排序的列加索引
Using where	当使用非索引列进行检索数据，且进行了全表扫描
Using index	发生索引覆盖时显示using index，表示这是⼀个高效查询

什么是索引覆盖？

简单来说就是：要使用的索引中包含要查询列，这时直接返回索引中列的值，而不用回表查询。

当创建普通索引时，生成对应的索引树。普通索引的索引树的叶子结点保存的是索引列的值和主键值。当查询的结果列在索引树中可以找到，那么就直接从索引树中返回结果，这就是索引覆盖。

什么是回表查询？

当使用索引检索数据时，查询的列不只包含索引列，这时需要通过索引中记录的主键值到主表中进行查询，这个现象叫做回表查询。

二.WHERE子句优化

不仅适用于select，update、delete也可以。

1.删除不必要的括号

-- 优化前
((a AND b) AND c OR (((a AND b) AND (c AND d))))
-- 优化后
(a AND b AND c) OR (a AND b AND c AND d)

2.常量合并

-- 优化前
(a<b AND b=c) AND a=5
-- 优化后
b>5 AND b=c AND a=5

3.常量条件去除

-- 优化前
(b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6)
-- 优化后
b=5 OR b=6

4.删除无效或超出范围的值

-- 表结构如下，有⼀个TINYINT 类型的列 (1byte)
CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);
-- 优化前
SELECT * FROM t WHERE c < 256;
-- 优化后
SELECT * FROM t WHERE 1;

5.优先查询常量表

什么是常量表？

MySQL中常量表分为两种类型：

1）System表：只包含一行数据的表。

2）Const表：通过where语句中的限制条件筛选后只包含一行数据的表。这个条件通常是某列等于要给常量值，该列上通常有索引，可能是主键或唯一键。MySQL可以通过索引快速定位该行。

-- 单表中⽤主键查询
SELECT * FROM t WHERE primary_key=1;
-- 多表中⽤主键查询
SELECT * FROM t1,t2
WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

注意：在表连接查询中，读取任何其他表之前先读取所有常量表。

6.高效查询示例

-- 统计所有⾏数
SELECT COUNT(*) FROM tbl_name;-- 聚合函数中使⽤索引列
SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;-- 复合索引时，where条件使⽤索引列1，聚合函数中使⽤其他索引列
SELECT MAX(key_part2) FROM tbl_name
WHERE key_part1=constant;-- 排序时使⽤索引列、复合索引时，按索引列的顺序排序
SELECT ... FROM tbl_name
ORDER BY key_part1,key_part2,... LIMIT 10;-- 排序时使⽤索引列，复合索引时，按索引列的顺序排序，可以为不同的列指定排序规则
SELECT ... FROM tbl_name
ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

三.范围优化

在范围查询的列上建立索引可以有效的提高查询效率。注意，这里的范围不是特指一个区间。

1.单部索引范围访问

范围条件定义如下：

1）对于BTREE和HASH索引使用=、<=>、IN()操作符时，索引部分与常量值的比较是一个范围条件；

2）对于BTREE索引当使用>、<、>=、<=、BETWEEN、<>操作符时，索引部分与常量值的比较是一个范围条件；

3）LIKE的参数是一个不以通配符开头的常量字符串也是一个范围条件；

4）对于所有索引类型，多个范围条件用OR或AND组合形成一个范围条件。

注：常量值是查询字符串中的常量，表连接中const表或system表中的列，或由上述类型组成的表达式。

2.多部索引范围访问

多部索引范围访问是单部索引范围条件的扩展，使用复合索引中的多个索引列进行范围查询。

四.索引合并与索引下推

1.索引合并

多条件的查询语句中where查询通常会包含多个条件判断，以AND或OR操作进行连接。在对⼀个表进行查询时最多只能利用该表上的⼀个索引，其他条件需要在回表查询时进行判断（不考虑覆盖索引的情况）。当回表的记录数很多时，需要进行大量的随机IO，这可能导致查询性能下降。

索引合并是通过对⼀个表同时使用多个索引进行条件扫描，并将满足条件的多个主键集合取交集或 并集后再进行回表，可以提升查询效率。 索引合并可以检索多个范围扫描的数据行，并将结果合并为⼀个。索引合并仅合并单个表中的索引扫描，而不能跨表扫描。合并的结果是多个索引扫描的并集、交集或交集的并集。

在explain输出中索引合并在type列中显示为index_merge。具体算法有以下三种：

1）交叉访问算法

适用于将⼀个WHERE子句中的条件转换为多个不同索引的范围条件并结合AND⼀起使用。

2）联合访问算法

2.索引下推

当使用索引进行范围或不以%开头的模糊查询时，先过滤出⼀个范围，如果没有索引下推则需要 根据主键回表后再判断其他的where条件。如果使用复合索引且其他的条件可以用复合索引中的列判断，则在存储引擎层面就进行过滤，而不用回表查询后再过滤，最终会减少回表的次数，提升效率。

六.IS NULL优化

MySQL可以对列名 IS NULL与列名 = 常量值相同的优化。

建议在建表的时候为非空的列指定NOT NULL约束。

如果where子句对NOT NULL的列添加列名 IS NULL条件，那么表达式将被优化掉：

-- 主键不可能为空，执⾏计划中显⽰ Impossible WHERE 表⽰当前条件永远不可能为真
select * from t1 where id is null;
-- ⾮空列不可能为空，执⾏计划中显⽰ Impossible WHERE
select * from t1 where a is null;
-- 条件组合中存在is null 也会被优化掉
select * from t1 where a = 'aaa' or a is null;

七.ORDER BY优化

MySQL可以使用索引进行ORDER BY排序，不能使用索引的时候，会通过filesort来处理结果的排序操作，所以要尽量使用索引。

1.可以使用索引来解析ORDER BY

联合索引是(key_part1, key_part2)

1）排序时直接使用索引列

SELECT * FROM t1 ORDER BY key_part1, key_part2;

2）key_part1是常数，通过索引访问的所有行都按照key_part2排序

SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;

3）ORDER BY对索引列进行升序或降序排序

SELECT * FROM t1 ORDER BY key_part1 ASC, key_part2 ASC;

4）key_part1与常量比较，并使用key_part1排序

SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC;

2.不使用索引来处理ORDER BY，但可以使用索引作为where条件

1）对不同的索引使用ORDER BY

-- key1, key2是两个不同的普通索引
SELECT * FROM t1 ORDER BY key1, key2;

2）不符合最左前缀匹配原则

SELECT * FROM t1 ORDER BY key1_part1, key1_part3;

3）WHERE中的索引与ORDER BY的索引不一致

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

4）ORDER BY中对索引进行转换和运算

SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;

八.聚合查询优化

1.GROUP BY优化

在执行分组查询时，会先把符合where条件的结果保存在一个新创建的临时表中，临时表中每个分组字段的所有行都是连续的，然后再分离每个组并用聚合函数。由于索引本身就是连续的，因此可以使用使用来避免创建临时表。

以下分组查询可以用到索引：

联合索引是(c1,c2)

1）分组时使用索引列并遵循最左前缀匹配原则

SELECT c1, c2 FROM t2 GROUP BY c1, c2;

2）对于同一个索引使用MAX()或MIN()

SELECT c1, MIN(c2), MAX(c2) FROM t2 GROUP BY c1;

3）如果使用其他聚合函数，必须加DISTINCT

-- 如果使⽤其他聚合函数，必须加DISTINCT
SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t2;
-- 对两个列进⾏COUNT()，必须加DISTINCT，否则会报语法错误
SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2,c1) FROM t2;

2.DISTINCT优化

distinct子句查询可以理解成分组查询的特殊情况，参考分组查询优化。

九.索引失效

1.违反最左前缀匹配原则

举个例子：

联合索引(a, b, c)where a=1 and b=2  # 走联合索引where b=2 and a=1  # 走联合索引, 不会因为where后的字段顺序就失效where b=2 and c=1  # 不会走联合索引, 因为a字段在索引最左侧, where中没有awhere c=1 and a=2  # 会走联合索引关于a的部分

2.where中有or，or的两边的列有不是索引列

举个例子：

select * from index_demo where id = '1020000' or age = 20
# id是索引列，但是age不是

3.复合索引中第一个使用范围查询的条件，或不以%开头的模糊查询之后的列不使用索引

举个例子：

select * from index_demo where age < 18 and class_id > 4

4.模糊查询以%开头

举个例子：

select * from index_demo where name like '%user_1022000'

5.隐式转换，比如字符串没有加引号

select * from index_demo where sn = 1020000
# sn是varchar类型的

6.where子句中有表达式或函数

# 表达式
select * from index_demo where id + 1 = 1000020 # 不走索引
select * from index_demo where id = 1000020 + 1 # 走索引
# 函数
select * from index_demo where length(name) = 11 # 不走索引

7.使用<>，not in

select * from index_demo where name <> 'user1020000'

8.MySQL判断全表扫描比索引快时不使用索引，常发生在数据量极少的表中，或是过滤条件效果不佳时

十.索引使用原则

1.每张表必须有主键，推荐BIGINT类型且自增；

2.DISTINCT，ORDER BY，GROUP BY，JOIN条件，WHERE条件的列加索引；

3.对频繁进行数据操作的表，不要建立太多索引，因为维护索引也需要很大的成本；

4.使用复合索引时遵守最左前缀原则；

5.多表JOIN有确定条件时WHERE class_id = 1，可以分成多个单表查询，然后在程序中合并；

6.避免在重复值太多（区分度低）的列上建立索引，比如大部分都是相同的值，查了索引之后还会回表，那么还不如全表扫描；

7.使用指定的索引use index(index_name)，force index(index_name)；

8.创建索引时可以指定ASC DESC，create index index_name(col desc, col1 asc)；

9.创建⼀个复合索引(a, b, b)，相当于同时创建了(a)，(a,b)，(a,b,c)；

10.创建索引之前，确保当前数据库实例没有未提交的大事务，防止数据锁死；

11.对不常使用的索引进行清理，删除无用的索引，避免对执行计划造成负面影响。