mysql（九）explain+案例分析

一、explain

一、执行计划对应信息

例如explain  select *  from table  ，使用explain就是在查询语句前面加上explain命令即可。然后我们来看看这句sql的执行计划是怎样的？

这里看到的id、select_type、table、partitions、type、possible_keys、key、key_len、ref、等东西就是执行计划中包含的内容。

（1）id：每个select语句都对应一个id，例如上面的只有一个select所以就对应一个id（如果子查询有多个select那就有多个id）。（也可以说每条执行计划对应一个唯一id）

（2）select_type：即一条执行计划对应的查询是什么样的查询类型（例如上面的simple代表简单的查询类型）

（3）table：代表查询哪个表。

（4）partitions：表分区   ？？？

（5）type：即访问方式，就是前面说的const、ref、range等。分别代表聚集索引、二级索引等

（6）possible_keys: 列出可以访问的索引列表

（7）key：最后该执行计划选择哪个索引进行检索的

（8）key_len：使用的索引的长度

（9）ref：

（10）rows：预估可以读取的数据条数。

（11）filtered：经过条件过滤后得到的数据条数占总的条数比

（12）extra：一些额外的信息

二、

1、explain  select  *  from  t1

其中查询是simple简单查询，查的是t1这张表，执行计划的走索引类型是All即全表扫描。rows其中预估可以查询出3457条数据。filtered为100表示所有数据都被查出来了。

2、explain  select *  from  t1  join  t2

多表联查，因为只有一个select关键字（两条执行计划对应一个select语句），所以只有一个id。此时先访问t1表，使用All全表扫描，查到3457条数据。然后再对t2表进行All查询，查询出所有的4568条数据。extra中写了nested Loop即代表嵌套循环的访问方式（联表的嵌套循环检索前面讲过）。

3、explain select  *  from t1  where x1  in  (select  x1  from  t2)  or  x3 = 'xxxx';

此时一条执行计划对应一个select，所以有两个id。

第一条执行计划中：primary表示是主查询，采用All的方式扫描。其中有where  x3 = 'xxxx'，所以存在index_x3这个索引。而采用ALL表示最后没用这个索引。

第二条执行计划中：subquery表示子查询，存在index_x1索引，此时使用的是index方式，表示对二级索引树的叶子节点进行了全扫描。

（先执行子查询，再执行主查询）

4、explain  select  *  from  t1  union  select  *  from  t2；（union合并两个结果集，即并集且去重）

第一二条执行计划都是比较简单的。此时需要注意，这里多出来了一条执行计划。第三条执行计划干的就是去重的活(第二条执行计划已经进行了合并的操作了)。其中<union 1，2>这是将两个结果集合并后（未去重）放进了一个临时表，从extra的using temporary可以看出这就是使用了临时表。select_type为union result表示基于临时表进行去重（如果你使用了union all，就不用第三条执行计划进行去重了）。

5、explain  select  *  from t1  where  x1  in  （select  x1  from  t2  where x1 = ‘xxx’  union  select  x1  from  t1  where x1 = ‘xxx’）；

这里有三个select。四个执行计划

第一个执行计划：比较简单，进行全表扫描。

第二个执行计划：使用index_x1进行二级索引的方式ref，select_type是dependent  subquery。

第三个执行计划：也是走ref方式，select_type是dependent  union。

第四个执行计划：去重的工作，select_type是 union result

6、explain  select  *  from （select  x1，count（*）as cnt  from  t1  group  by x1） as _t1  where  cnt >10;

两条执行计划：
第一条：其中table为derived2，表示针对子查询执行后的结果集被物化到一个内部临时表。

第二条：select_type  为derived，也是说子查询结果集物化到临时表。

7、explain  select  *  from  t1  inner  join  t2  on  t1.id = t2.id

第一条执行计划：全表扫描，此时看出t1是驱动表，而这个驱动表没有where 筛选条件， t1.id = t2.id是联接条件，不是t1的where筛选条件。所以此时要进行全表扫描。

第二条：此时t2是被驱动表，我们之前已经说过，根据驱动表获取一波数据，然后各个数据对被驱动表进行循环嵌套筛选。所以此时被驱动表其实是循环的进行等值匹配，此时被驱动表走主键进行等值匹配时，其方式被称为eq_ref。

之前说过如果基于某个二级索引进行正常的等值匹配，此时type是ref，如果基于二级索引查询时允许值为null，那么查询方式就是ref_or_null。

另外一些特殊场景下，单表查询时可能基于多个索引树分别筛选数据后进行合并，此时查询方式就会是index_merge。

range基于二级索引的范围查找，index全扫描二级索引树的叶子节点。

8、explain  select  *  from  t1  where  x1 = ‘xxx‘；

上面主要对type、select_type举例进行说明。这里对possible_keys等几个进行说明

possible_keys就是这条查询潜在的可能用到的查询。ref就是当你查询方式是索引等值匹配时，等值的那个值是常量还是其他字段还是什么。

这里比较简单就不分析了。其中因为是等值匹配且x1对应的值是常量’xxx‘，所以此时ref是const。

最后简单说下rows和diltered

explain  select  *  from  t1  where  x1>'xxx'  and  x2 = 'xxx'; 此时x1是索引字段，x2不是

可以看出筛选出1987条数据，占总数的13%。

9、explain  select  x1  from  t1  where  x1 = ’xxx‘；

这里讲讲extra，这个代表的信息非常多，只能举部分例子进行说明。

此时看extra信息是using index，即代表这个时候使用的是index，全扫描二级索引树，此时没有进行回表操作。

如果此时sql： select  *  from  t1  where  x1> 'xxx'  and  x1  like  '%xxx'

此时会进行正常的二级索引index_x1进行查找，然后找出来的结果进行模糊查询。这个时候的extra就会被显示为using  index  condition。

还有一种常见的情况就是extra表示为using  where。这个代表全表扫描。（或者是你有部分使用索引，还有部分条件字段不是用索引）

例如：explain  select  *  from  t1  where   x1 = ’xxx‘  and  x2 = ’xxx‘；  （x1是索引字段，x2不是）

此x1索引进行查找，看到ref对应的是const则表示x1是常量匹配。然后看到extra是using where，则表示在x1索引筛选出来后，又根据非索引字段x2进行全表扫描。

还有一种情况：利用join  buffer的内存技术来提升关联的性能。

例如:  explain  select  *  from  t1  inner  join  t2  on  t1.x2 = t2.x2

其中先对t1驱动表进行全表扫描，然后根据获取的数据对被驱动表进行嵌套循环查询。此时就会用到join  buffer 技术在内存中做一些特殊的优化，减少t2表的全表扫描次数。

还有一种是using  filesort，即在内存或者磁盘中对数据进行排序等操作。

例如：explain  select  *  from  t1  order  by  x2  limit  10。 （其中x2没索引）

此时基于x2来排序，因为这个字段不是索引，所以不能直接的去查找有序数据。此时就全表扫描将所有数据存到一个临时的磁盘文件中，然后进行排序，排序完后再进行limit操作。因为将表的所有数据放在磁盘文件中操作，此时性能会很差。

还有一种情况是using  temporary。即使用临时表。

当使用一些group by、union、distinct时，如果没法直接利用索引来进行分组聚合（没有索引字段使用），那么他就直接基于临时表来完成，此时也会由大量的磁盘操作，性能其实也是很低的。

例如：explain  select  x2 ，count(*)  as  amount  from  t1  group by x2;  (此时x2不是索引字段)

则这里会对全表数据放到临时表中做大量的磁盘文件操作，进行分组、聚合等操作，这个过程是相当耗时的。

（所以一般要对这些字段进行设立索引）

二、具体案例分析

一、千万级用户场景下的运营系统的sql调优

这个系统有专门通过各种条件筛选出大量的用户，接着对这些用户推送各种不同消息。

该系统注册用户是千万级别，用户日活跃度是百万级别的，而且如果没有进行分库分表的话，那么一个数据库的用户表单表就是千万的用户数据。此时我们对该系统的筛选用户的sql做一个简化，大概如下面：

select  id ，name  from  users  where  id  in  （select  user_id from  users_extent_info  where  latest_login_time < xxxx）

上面的sql大概就是存储用户数据的表会分为两张表，一个表（users）用来存储用户的核心数据（name、手机号等待），一张（users_extent_info）用来存储用户的一些拓展信息（爱好、住址等）。而这个sql就是用来查询最近登录时间小于某个时间点的用户。然后外层根据in查询子查询的结果集。此时这个sql可能查出几万，几十万的数据。所以一般在执行这类sql前，都会先跑一个count聚合函数看看最终会查出几条。

如果count出来发现数据比较多，就小批量分批次的读取数据（用limit）。但这里你会发现跑个count的sql此时也会花费几十秒才能跑出来。那么我们现在就来优化下这个count的sql语句。

explain  select  count（id） from  users  where  id  in  （select  user_id from  users_extent_info  where  latest_login_time < xxxx）；  （这里优化count基本就等于优化上面的筛选数据的sql）

此时有三条执行计划，两个select。首先我们先来针对子查询。

第三条：针对的table是uers_extent_info进行范围查找（range），其中使用了idx_login_time这个索引。查出4561条数据

               其中select_type是materialized类型，表明了这里把子查询的结果集进行物化成一个临时表。该临时表将这些数据临时的放在磁盘文件中（这个过程就很慢），

第二条：针对users表进行全表扫描得到49651条数据，此处使用了join buffer技术。

第一条：对子查询得到的临时物化表（subquery2）进行全表扫描，此时要将users表的每条数据都跟临时表的数据进行join操作，所以需要对临时表的数据进行一次全扫描。

而这里又要回到第二条的filtered中是10%，这个表示在users表和临时表进行join后筛选出49651的10%。也就是最终这条sql筛选出4千多条。

这个时候我们需要分析为什么这条sql执行时间这么长？
可以看到子查询的结果做了一次物化临时表落地磁盘了。还有对users表查询时是all全表扫描，然后每条users表的数据循环到临时表中做全表扫描匹配出相应的值。

所以我们这个时候就得让对users表的全表扫描用上索引。此时你是否觉得根据之前使用索引的规则，这里id已经是主键了，但还是没用到索引，那能怎么办？

此时我们可以执行下show  warnings命令。会显示如下内容：

这里可以大概的猜到，生成执行计划时，自动将一个in子句优化成基于semi  join来进行in+子查询的操作。即将users表的每条数据都去跟临时表的所有数据进行匹配。这就是semi join的意思。

但在这里的semi  join导致users这张大表是驱动表，而此时临时表这张小表为被驱动表。所以解决方法就是关闭掉semi join变为原先的in操作，此时mysql就会修改临时表为驱动表，而user为被驱动表，此时被驱动表就可以用到索引了。

（驱动表和被驱动表的选择在上一篇中有讲到，一般都会优化为小表驱动大表，因为小表为驱动表时，驱动表是需要进行全表扫描的，被驱动表有索引时可以进行索引检索。）而这个时候mysql优化错了，semi  join会将左边的定位驱动表，此时是大表且不能使用索引所以性能边慢了很多。

那么怎么关闭掉semi join呢？

查询了下MySQL对什么样的子查询支持使用半连接进行优化，其中有一条是子查询位于WHERE/JOIN-ON子句中，且首层不存在OR/NOT操作（即首层的条件子句中只能是AND操作符连接的表达式。如果与OR操作在同层的子查询不可以被半连接优化，但可以被“物化策略”优化）。

总结：这里主要解决mysq错误的将in优化为semi  join  导致驱动表和被驱动表的选择不适当，最后导致两张表的全表扫描从而导致的性能低下。

二、亿级数据的商品系统的sql调优

这个案例也是因为mysql在多个可选索引下，选择了不太合适的索引，而没有使用更合适性能更高的索引。

生产过程中通过监控发现每分钟的慢查询超过10w+，就是商品系统的大量查询变成了慢查询，那么这些慢查询大概是什么语句呢？这里做了一些简化：
select  *  from  products  where  category =’xx‘  and  sub_category='xxx'  order  by id  desc  limit  xx,xx

这是一个很平常的sql，就是根据据商品的品类以及子类进行筛选，然后按照id倒序排序，最后进行分页。

这个时候发现这个sql竟然需要跑几十秒，导致数据库的连接资源被全部打满，难怪此时一大堆查询说查询数据库超时。

这里先说一下，这个表是有对条件字段设立索引index_cate(category,sub_category)，按照逻辑这个sql也是会用到这个索引的。那此时即使是亿级的数据，执行时间最多也应该是1、2秒的。为什么会执行了几十秒？
此时我们进行explain一下，发现possible_keys里有我们的index_category，但实际用的key不是这个索引，而是primary聚集索引。而且extra中写道using where，说明此时有一些条件是没用到索引的。

所以此时我们就得将其改为使用index_cate这个索引，此时就可以使用force_index语法，如下：
select  * from  products  forces index(index_cate)  where   category =’xx‘  and  sub_category='xxx'  order  by id  desc  limit  xx,xx;

这个时候就会走index_cate这个索引了。

虽然我们解决了这个问题，可是还遗留了一些问题，例如：

这几个问题对于下一个案例的分析很重要，下一个案例的优化就是根据这个原理优化的

三、亿级评论系统的sql调优

 

四、千万级数据删除导致的慢查询优化

aa