sql分析和优化

SQL的快慢有些时候决定着系统的性能，作为一名 SQL BOY，SQL 优化已经成了必须掌握的技能，今天我们就来学习如何分析 SQL ，SQL 优化的案例以及一些 SQL 优化经验。

一. 数据准备
创建表 t1 和 t2 ：

CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int DEFAULT NULL,
  `b` varchar(16) DEFAULT NULL,
  `c` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`),
  KEY `idx_b_a` (`b`,`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `t2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int DEFAULT NULL,
  `b` varchar(16) DEFAULT NULL,
  `c` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_a` (`a`),
  KEY `idx_b_c` (`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

sql 脚本循环插入数据：

delimiter ;;
create procedure loop_insert_t1()
begin
  declare i int;
  set i=1;
  while(i<=5000000) do
    insert into t1 (a, b) values(i, 5000000 - i);
    set i=i+1;
  end while;
end;;
delimiter ;
 
call loop_insert_t1();
 
delimiter ;;
create procedure loop_insert_t2()
begin
  declare i int;
  set i=1;
  while(i<=50000) do
    insert into t2 (a, b, c) values(i, 50000 - i, 10000 + i);
    set i=i+1;
  end while;
end;;
delimiter ;
 
call loop_insert_t2();

二. SQL 分析
explain 执行计划分析
explain 用于分析 sql 查询语句的执行计划，包括执行顺序、是否走索引、用到了哪些优化等，所以 explain 是用于 sql 优化的基础工具，我们先来分析下 explain 工具。

explain 的使用非常简单，直接加在查询语句的前面运行即可：

explain select * from t1 limit 1;

我们来解析下各个字段的含义。

id
id 列是 select 的标识符，有几个 select 就有几个执行计划，id 越大的优先级越高（越先执行），如果 id 一样，则从上往下执行。

select_type
select_type 表示对应查询语句的查询类型，种类较多，官网上的介绍：

table
table 表示执行计划访问的哪张表，或聚合哪几个执行计划的结果集（最新版本已不展示）。

partitions
partitions表示查询记录所在的分区，如果是非分区表显示 NULL。

type
type 列表示关联类型或访问类型，是进行 sql 分析和优化的关键字段。

性能由好到差分别是：system > const > eq_ref > ref > index_merge > range > index > ALL。

system
该类型代表查询的是系统表且只有一行记录，可以看作是 const 类型的特例，在我们日常 sql 中不会出现。

const
const 代表该表最多只有一行匹配，出现在使用主键索引或唯一索引和常量比较的情况，例如：

– 字段 id 为主键

explain select * from t1 where id = 320001;

– 字段 a 为唯一索引

explain select * from t2 where a = 111;

eq_ref
eq_ref 出现在关联查询时使用的索引是主键索引或唯一索引（不为null）的情况下，例如：

explain select * from t1 join t2 on t1.id=t2.id;

ref
ref 出现在使用普通索引等值查询或者唯一联合索引的部分前缀等值查询的情况，例如：

– 字段 a 是普通索引

explain select * from t1 where a = 111;

index_merge
index_merge 出现在使用 or 的字段是不同的索引，MySQL 分别走索引树将结果聚合返回，例如：

– a, b 都为索引

explain select * from t2 where a = 123 or b = "1234";

range
range 代表通过索引进行范围查询，range 出现情况比较多：当对索引字段使用 <、>、<=、>=、between、is null、in 条件内元素个数大于 1、模糊查询走索引（满足前缀匹配），例如：

explain select * from t1 where id > 111;

一般在开发中我们 sql 的 type 等级至少要到 range。

index 和 ALL
当 sql 的 type 等级为 index 或 ALL 时，我们就需要进行优化了，二者都可以算全表扫描，唯一的区别是 index 检索的是整个非聚簇索引树（非主键索引树），ALL 检索的是整个聚簇索引树（主键索引树），所以 index 的性能要比 ALL 好一些，因为非聚簇索引树的叶子结点存的是主键id，而聚簇索引叶子结点存的是整条记录，所以优化器会选择小的索引树进行检索速度会快一点。

当没有条件字段非索引字段或索引失效（下面分析）或进行全表扫描。

index 等级例如：

explain select a from t1;    

ALL 等级例如：

– 字段 c 非索引字段

explain select * from t1 where c = "111";

possiable_keys
possiable_keys 代表该条语句可以走哪些索引，如果为 null 则代表无可用索引；语句最终通过哪个索引检索是在 key 字段进行展示。如果 where 条件有多个且都是索引，possiable_keys 就会展示这些索引名称， 例如：

– 字段 a 和 字段 b 都为普通索引

explain select * from t1 where a = 123 and b = "2222";

key
key 列代表 MySQL 最终决定使用的索引。key 为 null 代表没有使用索引，如果想强制使用或不使用某个索引可以通过 force index 或 ignore index 实现。

key_len
key_len 表示使用的索引长度，如果 key 为 null，那么该字段也为 null。

key_len计算规则如下： 字符串 char(n)：n字节长度 varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2 数值类型 tinyint：1字节 smallint：2字节 int：4字节 bigint：8字节　　 时间类型　 date：3字节 timestamp：4字节 datetime：8字节

ref
ref 显示的那些列或常量与索引进行比较。

rows
rows 表示预计扫描的行数，官方给出的误差比例为 40% 。

filtered
filtered 代表按照条件过滤后剩余数据占总数据的预估百分比；如果是100% ，则代表没有过滤。

Extra
Extra 列会输出一些额外信息，包括该语句用到了哪些优化。Extra 列的信息较多，我们挑一些重点介绍下：

Using index
Using index 代表语句用到了覆盖索引的优化；覆盖索引就是查询的列通过索引树就可以返回，不需要额外的回表操作；是性能高的体现，例如：

– 字段 a 是普通索引

explain select id from t1 where a = 111;

a 是普通索引，也就是非聚簇索引，叶子结点存储的 id，所以可以直接将 id 返回而不需要回表。

Using index condition
Using index condition 代表语句用到了索引下推的优化；索引下推一般出现在联合索引中，当对联合索引进行检索时且 where 的条件是联合索引中的字段，可以直接通过索引树完成过滤省去回表过滤的操作，例如：

– 联合索引 (b,a)

explain select * from t1 where b = "21313" and a != 123;

b，a 两个字段构成联合索引，通过 b = “21313” 在联合索引树上快速检索，针对匹配到的值直接判断 a 是否满足不等于 123，不满足就过滤。

Using where
一般没有用到索引的条件查询会展示该信息，出现该信息且 type 为 ALL 则需要进行优化（对条件字段加索引），例如：

– 字段 c 非索引字段

explain select * from t1 where c = "123";

Using temporary
Using temporary 代表需要创建一个临时表来保存结果，一般出现在对非索引字段使用分组查询（group by）或去重查询（distinct），例如：

– 字段 c 非索引

explain select c from t1 group by c;


使用临时表的性能会比较差，我们对 c 加索引即可优化该情况。

Using filesort
Using filesort 代表语句查询的数据需要单独进行排序，而不是通过索引树完成，所以该情况通常出现进行排序的字段为非索引字段，例如：

-- 字段 c 非索引 

explain select * from t1 order by c limit 1000;

排序的逻辑和优化我们下面详细讲。

三. 索引失效的场景及原因
针对慢 SQL 进行优化最常见的手段就是对条件查询、排序、分组的字段加索引。

表 t1 有 500 万的数据，我们看下有无索引的耗时对比：

-- 无索引，耗时：3600 ms 

select * from t1 where c = “123”;

-- 有索引，耗时：0.6 ms 

select * from t1 where b = “123”;

差别还是很明显的，我们来分析下没有索引和有索引 MySQL 分别是如何进行数据查找的。

在分析之前，先回顾下 MySQL 中 innodb 表的实现方式（《MySQL索引与优化》）：

innodb 表是索引组织表，由聚簇索引树和非聚簇索引树组成；

聚簇索引和非聚簇索引的区别是：聚簇索引的叶子结点存储的整条记录，非聚簇索引的叶子结点只存储主键的值；

这些索引树是以 B+ 树的结构存储在磁盘上的，B+ 树可以看作是一个平衡多叉搜索树，非叶子结点存储索引，叶子结点存储数据；

MySQL 索引树的叶子节点是一个数据页（16kb），数据页上通常会有多条记录，数据页是 MySQL 的基本操作单元，一次读取会把整个的数据页都加载到内存，叶子节点（数据页）之间通过指针相连组成双向链表。

聚簇索引（主键索引）的结构图：



非聚簇索引（非主键索引）的结构图：



有了这些理论知识，我们来分析下有无索引的查找是如何工作的。

无索引：没有索引只能到聚簇索引树上叶子节点组成的链表从头开始遍历，每经过一个数据页都是一次磁盘IO（假设buffer pool中没有该数据页），最终需要扫描整张表才能完成查找。

有索引：

select * from t1 where b = “123”;

就看上面的语句：

1. 假设 b 是主键，直接从聚簇索引树的根节点以 O(logN) 的时间复杂度快速查找到记录所在的数据页，并将数据页上符合条件的记录直接返回。

2. 假设 b 是普通索引，首先从 b 的非聚簇索引树上以 O(logN) 的时间复杂度快速查找到对应数据页，并将数据页上符合条件的主键值取出，到聚簇索引树上根据主键查找记录，将结果返回。

所以，我们需要让语句走索引扫描而不是进行全表扫描。

索引失效的情况
虽然加了索引，但是也有很多情况会让索引失效。但是所有索引失效的原因基本上都是没有利用到 B+ 树快速存取的优势；要想防止慢 SQL，就需要避免索引失效，我们来看下索引失效有哪些情况。

1. 条件字段进行函数或计算操作
当我们对条件字段进行函数或计算操作时会导致索引失效，例如：

-- b 是索引 

explain select * from t1 where length(b) = 3;

字段 b 是有索引的，我们用 explain 执行一下看看执行计划：



看到 type 是 ALL：进行全表扫描，key 也是 NULL，所以没有走索引。

为什么对字段进行函数或计算操作就会导致索引失效？

其实原因很简单，B+ 树的快速定位的能力来源于兄弟节点的有序性，所以当对字段进行函数操作时，有可能会破坏索引的有序性，无法通过 B+ 树进行快速定位，因此优化器就会放弃走树搜索。

需要强调一点，这里说的索引失效并不是说不走索引，而是不走树搜索，例如下面这种情况还是会走 b 字段的索引树：

explain select id from t1 where length(b) = 3;

就是我们在 explain 分析那块讲的 type 为 index 的情况：在能在索引树上直接拿到结果的前提下选择更小的索引树（当然也是扫描索引树所有叶子节点）。

2. 隐式类型转换
当我们使用字符串类型的字段和 int 整型进行比较时索引会失效，例如：

-- 字段 b 为 varchar 类型并且是索引 

explain select * from t1 where b = 123;

explain 执行计划中的 type 为 ALL 代表全表扫描，possible_keys 列显示可能会走 idx_b_a 这个索引，但是 key 列为 NULL，所以优化器放弃执行树搜索而进行全表扫描。

其实出现这种情况和函数操作的原因一样，都是对字段进行函数操作导致无法快速定位；在 MySQL 底层，如果字符串和整型比较，是会先把字符串转换为整型再继续比较，那么这个语句在 MySQL 里就会转换成：

-- CAST(b AS signed int) 代表将 b 转换为 int 型 
select * from t1 where CAST(b AS signed int) = 123;
那如果把整型类型的字段和字符串进行匹配会导致索引失效吗？

-- a 为 int 型且是索引 

explain select * from t1 where a = “123456”;

发现是走索引的，其实我们上面说的原则就可以解释了：如果字符串和整型比较，是会先把字符串转换为整型再继续比较；上面的情况是对值进行函数操作而不是字段，对值操作完后是可以在树上快速搜索的。

3. 隐式字符编码转换
当进行关联查询时两个字段的编码类型不一致，也会导致索引失效；比如一个utf8，另一个是utf8mb4，如果进行关联查询，也会导致索引的失效，原因和上面基本一样，需要将字符类型转换成匹配的类型再继续搜索。

4. 联合索引不满足最左前缀原则
当使用联合索引时，必须满足最左前缀原则，否则索引会失效，例如：

-- 联合索引 (b, c) 

explain select * from t2 where c = ‘123’

联合索引的 B+ 树结构是按照定义时字段顺序排序的，所以 where 条件里需要出现联合索引中最左边 N 个字段才可以走索引，这就是最左前缀原则。

5. 模糊查询以%开头的
当使用模糊查询时以 % 开头会造成索引失效，这种情况和第四个一样是没有满足最左前缀原则，例如：

-- b 为索引 

explain select * from t1 where b like ‘%123’;

当我们的语句满足最左前缀就可以走索引树快速检索，例如：

explain select * from t1 where b like ‘123%’;

6. or 一个非索引字段
当使用 or 的条件查询语句其中有个字段非索引时会导致所有索引失效，例如：

-- a 为索引，c 非索引 

explain select * from t1 where a = 123 or c = “1234”;

这种情况，优化器认为再走索引的收益不大，因为有个字段非索引，一定要全表扫描，所以索引会失效。

8. MySQL 优化器不选择索引
还有一些情况需要具体情况具体分析，优化器会判断走索引的收益来决定是否走索引：

(1) 索引条件不等于判断（不一定）

(2) 字段允许为空时，条件为空（不一定）

(3) 走全表比索引更快

ps：索引的一些优化可以看《MySQL索引与优化》

四. SQL 优化实战
count 的选择
先说结论，性能排行：count(*) ≈ count(1) > count(主键id) > count(非空字段) > count(普通字段)

InnoDB 引擎计算 count 时需要把数据一行一行读出来然后累加，隔壁的 MyISAM 是直接存总数在引擎里，需要直接返回就可以了，那为什么 InnoDB 不存储 count 总数呢？

其实有两个原因：

1. 为了适配 MVCC 多版本并发控制（具体介绍可以看下《MySQL事务及MVCC》），因为每一行记录都需要判断对当前会话是否可见；

2. 业务中大部分都是根据条件去查 count 总数，如果是条件查询的 count 就不起作用。

我们来分析下为什么不同的 count(?) 性能不同。
count() 其实是一个聚合函数，代表的是括号里字段不为 null 的数量。

count(id)：MySQL 会遍历整张表把每一行的 id 取出来返回给 server 层。server 层拿到 id 后认为不可能为空，按行累加。

count(1)：MySQL 遍历整张表但是不取值，server 层直接按行累加。

count(字段)：

        1. 如果字段非空，一行行从记录里读取出来后无需判断是否为 null 累加就可以了；

        2. 如果字段可以为空，一行行从记录里读取出来后，需要再判断下是否为 null，不为 null 再累加。

count(*)：MySQL 对 count(*) 专门进行优化，不需要取值直接累加，和 count(1) 一样的效果。

综上所述，count(1) 和 count(*) 的性能最好，所以以后无脑使用 count(*) 进行总数查询就可以了。

分页优化案例
我们在业务开发中，为了提高查询性能会使用分页的手段，例如：

-- c 非索引，耗时 4260 ms 

explain select * from t1 where a > 22222 order by c limit 0, 20;

发现耗时有点大，我们 explain 看下执行计划：



看到 Extra 信息里有：Using filesort 使用外部排序，并且 key 列为空没有走索引排序。

我们来了解下 Using filesort 的工作流程：

MySQL 会给每个线程分配一块内存专门用来排序，称为 sort_buffer，如果需要排序会把所有符合条件的数据都放进 sort_buffer 里面，然后根据需要排序的字段进行快速排序，当 sort_buffer 放不下时还会启用外部临时文件，进行多路归并排序，性能肯定会很差。

我们将排序字段 c 换成索引字段 b 试试看：

-- b 为索引，耗时 0.6 ms 
select * from t1 where a > 22222 order by b limit 0, 20;
提升巨大，我们再 explain 看下还是否需要走外部排序：



通过索引直接排序而不是外部排序。

我们把分页的深度加大执行看看：

-- 耗时 10900 ms 
select * from t1 where a > 22222 order by b limit 400000, 20;
随着分页深度的加大，性能也下降的很厉害，什么原因导致的？

我们再 explain 看下啥情况：



发现 key 为 NULL 并且 Extra 又出现了 Using filesort，又不走索引排序了！

limit a, b 分页的逻辑是这样的：取出 a + b 条记录，舍弃前 a 条记录。由于查询数量过多（回表次数也增多），优化器选择放弃索引走全表扫描。

针对这种深度分页该如何优化呢？
1. 强制索引
既然优化器没走索引，我们用 force index 强制走索引试试：

-- 耗时 1370 ms 
select * from t1 
force index (idx_b_a) 
where a > 22222 order by b limit 400000, 20;
强制走索引后提升还是很明显的，但是还是有点慢。

2. 子查询
我们再来分析一下分页的耗时点：limit a, b 分页的逻辑是这样的：取出 a + b 条记录，舍弃前 a 条记录；故还是需要取出完整的 400020 条数据，需要读取大量数据页面，是非常消耗 IO 资源的；我们从这点出发，通过子查询，在子查询里进行条件查询和分页，这样只会取出 400020 个 id 字段（比整条记录小很多），最后根据 id 查询：

-- 耗时 66.5 ms 

select * from t1
join
(select id from t1 where a > 22222 order by b limit 400000, 20) t
using(id);

效果太明显了！耗时直接降到 70 ms 以内！

3.  业务优化
除了以上的两种方式，其实也可以从业务的角度去优化深度分页的问题：

偏移 id：比如在手机 app 端的站内信消息都是往下滑而没有分页选项，这种的实现逻辑就是：只允许翻下一页，翻页时将当前页最后一个 id 传给服务端，服务端将 id 当作条件过滤后直接 limit 就可以非常快的查出结果。

控制分页深度 + 数据冷热分离：在页面上控制分页的深度，比如 100 页，如果想要查更久的数据可以通过 TiDB 、Hive（冷热分离）来查询。

五. 其他优化经验
1. 大事务分批执行：比如定时删除七天的数据，一次性删除过多会占用连接和数据库线程资源而且大事物会增加主从延迟，可以加 limit 每次删除一批数据。

2. 适当合批：比如插入一批数据，使用批量插入而不是 for 循环一个一个插入。

3. 尽量不使用 select *：尽量需要啥就查啥，因为查的数据都是会占用带宽、网络 IO 和磁盘 IO 资源，但不用 * 代码耦合度（增加新字段要改代码 SQL）又会增加，需要自己权衡。

4. 更新时 where 条件一定得是索引，否则会锁表。

5. 业务中避免超过三张表的 join 查询，可以通过适当冗余字段减少 join 查询。

6. 多个条件查询的可以建联合索引，占用磁盘空间更小，且可以利用到覆盖索引和索引下推的优化。

7. 必要的时候进行冷热分离、读写分离、分库分表。

如果觉得文章不错可以点个赞和关注！

参考：

《MySQL实战45讲》 作者：林晓斌

https://juejin.cn/post/7101581835527782414#comment

https://mp.weixin.qq.com/s/klrORFnM8hYLuyVWPha6EQ

https://juejin.cn/post/6985478936683610149

https://juejin.cn/post/7074030240904773645#comment

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information