基于explain分析进行SQL优化（实例分析）

背景

最近遇到一个问题，实现相同功能的几个 sql 不知道哪个最优。当然直接测试可以得到最终结论，但是还是不知道为啥慢，为啥快，还可以怎么样再进行优化？这些问题没有解决。下面通过一个实例来介绍下 sql 优化神器 Explain，它可以辅助进行 sql 优化。

案例

存在两张表，第一张表是item表（10w+），存储了人力资源分配单据的概要信息（存储了id、company_id、sn），第二张表是detail(1000w+)表，存储了人力资源分配单据的详细信息（存储了id、company_id、sn和person_id）

item表存在company_id+sn的联合索引，detail表存在company_id+sn+person_id的联合索引

查询需求：在某公司的概要页（例如company_id = 123的公司），需要支持根据单据中是否存在某个人或某些人（当搜索两个人时，只要有一个人在该单据中，该单据就会被展示出来）进行查询。

下面有3条 SQL， 选出最优的那一条。

sql_1：

SELECT * FROM item WHERE company_id = 123 AND EXISTS (SELECT 1 FROM  detail 
 
          WHERE detail.company_id = item.company_id 
 
          AND detail.sn=item.sn
 
          AND detail.person_id in (P10001, P10002));

sql_2:

SELECT * FROM item WHERE company_id = 123 AND id IN(select distinct item.id from item
 
                LEFT JOIN detail
 
                ON item.company_id = detail.company_id
 
                AND item.sn = detail.sn
 
                WHERE
 
                item.company_id= 123
 
                AND detail.person_id in (123, 321));

sql_3:

SELECT * FROM item WHERE company_id = 123 AND sn IN (
 
            SELECT DISTINCT sn FROM detail
 
            WHERE detail.company_id = 123
 
            AND detail.person_id in (123, 321));

explain分析

结论是 方案1 最优，在有索引加持下，总体来说都差不多，耗时： 方案1 < 方案3 < 方案2。具体分析过程如下：

explain 分析的使用方法很简单，就是在SQL前面加 explain 就行了，比如 explain select…from…

分别对上述SQL加explain 跑一下，得到如下结果：

这里主要关注几个点：type（访问类型），key（引用索引）， extra（附加信息）, rows(扫描行数)

1、首先分析 访问类型（type）
这里补充一下知识点：
ref：表示索引访问，会将索引跟参考值比较（如这里的 123），找到所有符合条件的行；
eq_ref: 同上，只是最多会返回一行
其它：all （全表扫描）， index（扫描全索引），range（扫描固定范围 内的索引），const、system （可以理解成常量）
它们的执行耗时是： all > index > range > ref > eq_ref > const、 system (当然也不完全是，还要根据具体场景，有时候 index > all)

key字段表示的是 该查询使用的索引。

从访问类型（type）上看都差不多，但是 方案2 中间还有一个 eq_ref 操作，猜测可能会 有一点额外耗时，但是相差不大。其他访问都使用了索引 index0。

2、从extra 来分析
这里再补充下知识点：
useing where： 该查询使用了where 条件
useing index：只使用索引数据，不需要回表查询
useing temporary：使用临时表，如 distinct、order by 等，是一个常见的优化点。

从这点来看的话， 方案1 优于 方案3，因为方案3 使用到了 临时表（useing temporary）

一般还要关注一个指标 rows，表示为了找到满足条件的行，估计需要扫描多少行，这里都一样，就不分析了。

理论上得出的结论是： 方案1 最优，其次是 方案3，最后是 方案2

实际测试

测试环境： win10 + mysql 5.5（mysql 关闭内存）
数据量：item 表 19万， detail表 5450万 数据，company_id = ‘123’ 在两张表中分别是 10 w、5000w 行

分别把 3 条sql 跑 10次，取平均值，结果如下：
方案1： 27ms，
方案2：35ms，
方案3： 31ms
跟explain分析结果基本符合

综上：方案1最优。

案例 参考自博文：https://ask.csdn.net/questions/7651470?weChatOA=