吹吹牛

前几天老大问我怎么进行sql优化的，我回答了新建索引。哈哈哈，然后老大就出去找棍子了，进来之后跟我说你知道门在哪边吧，自己出去还是我请你出去？

 

然后被迫出去挨打，回来之后老大说去看下什么是Explain，然后交一份5000字检讨上来。。。。。。

以下内容以MySQL 8.0进行描述

2、基础内容

既然想优化sql，那么新建索引也确实没错，只不过不能看见一个字段就建一个索引，这样就确实容易挨打，那先说下新建索引到底mysql做了什么操作： 首先InnoDB的索引模型是B+树，在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。而每新建一个索引，就在InnoDB里面对应一棵B+树。假设，我们有一个主键列为id的表，表中有字段k、name，并且在k上有索引。

create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;
复制代码

往表中插入三条数据：

INSERT INTO T (id, k) VALUES (100, 1);
INSERT INTO T (id, k) VALUES (200, 2);
INSERT INTO T (id, k) VALUES (300, 3);
INSERT INTO T (id, k) VALUES (500, 5);
INSERT INTO T (id, k) VALUES (600, 6);

两棵树的示例示意图如下：

 

从图中可以看出，每新建一个索引，就新加了一颗B+树，而索引又分为主键索引和非主键索引： 主键索引:主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。 非主键索引:非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。

那么现在执行一条查询语句：

select * from T where id=500;

这条语句只需要搜索id这棵B+树，然后就会返回叶子节点的所有数据。

下一条查询语句：

select * from T where k=5

这条语句首先需要去索引k的这颗B+树，然后查到了k=5对应的主键id是500，在通过这个主键id再去id索引树查询一次然后返回数据，这个来回的过程就叫做回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

当然，我也知道，在实际开发中，很少能用到主键查询的，因为主键一般不存在于业务流程中，那么再来看下下面这个语句：

select id from T where k=5

这条语句和上面的语句最大的区别就是上面语句查询的是所有字段，而下面这个语句查询的只有id，而k字段索引树叶子节点保存的就是id值，就可以直接返回，不用再回表查询id索引树了，这就叫做覆盖索引。

  • 由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

基于上面覆盖索引的说明，再来看一个例子：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

建表语句中可以看到，有个主键索引id，有两个二级索引id_card和name_age，而name_age是由两个字段组成的索引(也可以是多个字段)，叫做组合索引。假设现在要执行如下语句：

select id from tuser where name like ‘张%’;

上面这条查询语句，虽然没有在name字段添加单独索引，但是它有个组合索引，所以上面这条语句也是能用到索引的：

 

可以看到，索引项是按照索引定义里面出现的字段顺序排序的，上面这条语句可以用上这个组合索引，查找到第一个符合条件的记录是ID3，然后向后遍历，直到不满足条件为止。

但是如果组合索引定义为name_age (age,name)这样的话，上面这条查询语句就用不上这个组合索引了，这就叫做最左前缀原则。

  • 单表索引不能过多，业界有个不成文的规定，单表字段不超过20个，索引不超过5个，因为随着数据量的增加，过多的索引会占据很多物理空间。

当然，这些都是些基础sql，遇到一些比较复杂的sql语句，怎么优化呢，这就要用到Explain执行计划了，先来看下举例吧(这条sql语句很复杂，是现在公司用到的，所以打个码，不好意思哈)：

 

重点在这里：

 

通过Explain关键字，可以看出来每个查询扫描的行数，用到了哪个索引等等，下面文章，会讲每个字段的意思，然后看下如何优化。。。。。。

3、Explain执行计划详解

就拿上面的图片来说，Explain主要通过以下字段来显示有关优化器的期望如何与实际执行相匹配的时间以及基于迭代器的其他信息：

 

id

id是执行顺序，就是每条语句的执行优先级，有可能相同(这种情况就由优化器决定)，也有可能不同(id值越大优先级越高，越先被执行)。

select_type

表示 select 查询的类型，主要是用于区分各种复杂的查询，像普通查询、联合查询、子查询等，值主要有以下几点：

SIMPLE：表示最简单的 select 查询语句，也就是在查询中不包含子查询或者union取并集等操作。

PRIMARY：当查询语句中包含任何复杂的子部分，最外层查询则被标记为PRIMARY。

SUBQUERY：当 select 或 where 列表中包含了子查询，该子查询被标记为：SUBQUERY 。

DERIVED：表示包含在from子句中的子查询的select，from列表中包含的子查询会被标记为derived 。

UNION：如果union后边又出现的select语句，则会被标记为union；若union包含在from子句的子查询中，外层select 将被标记为derived。

UNION RESULT：代表从union的临时表中读取数据，而table列的<union1,4>表示用第一个和第四个select的结果进行union操作。

table

查询的表名，并不一定是真实存在的表，如果给表指定了别名，则显示别名，也可能为临时表。(只是个表名，就注释一下)

partitions

查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表名中的分区情况。

type

查询使用了何种类型，它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system： 当表仅有一行记录时(系统表)，数据量很少，往往不需要进行磁盘IO，速度非常快。

const：表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量(const)值。这类扫描效率极高，返回数据量少，速度非常快。

eq_ref：查询时命中主键primary key 或者 unique key索引。

ref：区别于eq_ref ，ref表示使用非唯一性索引，会找到很多个符合条件的行。

ref_or_null：这种连接类型类似于 ref，区别在于 MySQL会额外搜索包含NULL值的行。

index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。

unique_subquery：只是用来完全替换子查询的索引查找函数效率更高了。

index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。

range：使用索引选择行，仅检索给定范围内的行。简单点说就是针对一个有索引的字段，给定范围检索数据。在where语句中使用 bettween...and、<、>、<=、in 等条件查询 type 都是 range。

index：Index 与ALL 其实都是读全表，区别在于index是遍历索引树读取，而ALL是从硬盘中读取。

ALL：将遍历全表以找到匹配的行，性能最差。

possible_keys

表示在MySQL中通过哪些索引，能让我们在表中找到想要的记录，一旦查询涉及到的某个字段上存在索引，则索引将被列出，但这个索引并不一定是最终查询数据时所被用到的索引。((有时候，你虽然给相应字段建立了索引，但是优化器并不一定会按这个索引来执行，这时候就需要你以force index来指定))

key

区别于possible_keys，key是查询中实际使用到的索引，若没有使用索引，显示为NULL。

key_len

key_len：表示查询用到的索引长度（字节数），原则上长度越短越好。

单列索引就将整个索引长度算进去，多列索引，不是所有列都能用到，需要计算查询中实际用到的列。

ref

ref：常见的有：const，func，null，字段名。

当使用常量等值查询，显示const。当关联查询时，会显示相应关联表的关联字段。如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为func。其他情况都显示为null。

rows

rows：以表的统计信息和索引使用情况，估算要找到我们所需的记录，需要读取的行数。

这是评估SQL性能的一个比较重要的数据，mysql需要扫描的行数，很直观的显示SQL性能的好坏，一般情况下rows值越小越好。

filtered

filtered 这个是一个百分比的值。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。(MySQL.5.7后，默认explain直接显示partitions和filtered的信息)。

Extra

这个列会显示很多不适合在其他列中显示的信息，Explain 中的很多额外的信息会在这个字段显示：

  • Using index：我们在相应的 select 操作中使用了覆盖索引，通俗一点讲就是查询的列被索引覆盖，使用到覆盖索引查询速度会非常快，SQl优化中理想的状态。 
EXPLAIN SELECT id FROM s_goods;

 

  • Using where：查询时未找到可用的索引，进而通过where条件过滤获取所需数据，但要注意的是并不是所有带where语句的查询都会显示Using where。 
EXPLAIN SELECT * FROM s_goods WHERE cat_id = 1;

 

  • Using temporary：表示查询后结果需要使用临时表来存储，一般在排序或者分组查询时用到。 
EXPLAIN SELECT shop_id FROM s_goods WHERE cat_id IN (1,2,3,4) GROUP BY shop_id;

 

  • Using filesort:表示无法利用索引完成的排序操作，也就是ORDER BY的字段没有索引，通常这样的SQL都是需要优化的。 
EXPLAIN SELECT * FROM s_goods ORDER BY shop_id;

 

  • Using join buffer：关联字段没有用到索引的话，会显示这个。 
EXPLAIN SELECT * FROM s_goods a LEFT JOIN s_goods_cats b ON a.shop_id = b.parent_id;

 

  • Impossible where：表示在我们用不太正确的where语句，导致没有符合条件的行。 
EXPLAIN SELECT * FROM s_goods WHERE 1 <> 1;

 

  • No tables used：我们的查询语句中没有FROM子句，或者有 FROM DUAL子句。 
EXPLAIN SELECT NOW();

 

作者：少年起而行之

链接：https://juejin.im/post/5ee894a05188253683551567