MySQL索引执⾏计划与SQL优化

无标签
MySQL
发布日期:   2022-07-13
更新日期:   2022-07-13
文章字数:   6.2k
阅读时长:   22 分

MySQL索引执⾏计划与SQL优化

1. MySQL多表join的实现⽅式

1.1基本概念：

  • 驱动表与被驱动表：在多表join查询时，先从⼀个表中查询出来⼀批数据，再根据这批数据去另外⼀个表中进⾏关联查询或者筛选，那第⼀个表就是驱动表，第⼆个表就是被驱动表；

  • join类型：

    • inner join（内连接）：两个表中的数据必须完全关联，也就是A表中的数据在B表中必须也存在，才能查询出来；

    • 内连接的驱动表和被驱动表的位置是可以相互转换的；（也就是可以由MySQL来进⾏优化改写的；⽽左/右连接的是固定的）

    • left join（左连接）：返回所有左表中的数据，当右表中不存在左表关联的数据时，以NULL来进⾏填充；

    • right join（右连接）：返回所有右表中的数据，当左表中不存在右表关联的数据时，以NULL来进⾏填充；

    • 上⾯的⼏种连接⽅式，驱动表和被驱动表是由MYSQL执⾏的时候去选择的；

    • straight_join：straight_join的表现完全与inner join⼀致，但是它限制了表的顺序，也就是说载⼊表的顺序遵循从左往右；

      • 也就是说straight_join可以指定驱动表与被驱动表的顺序；
      • 如果遇到两个表的join，各⾃数据量⽐较⼤，但是关联的字段本来是有索引却没有⾛索引，然后查询⾮常慢的情况可以看看是不是MySQL⾃⼰给你调整了表的驱动顺序，然后没有使⽤到索引，并且还有⼀些额外的排序等操作；这个时候可以使⽤ straight_join 来优化试试；

    • 循环嵌套查询（Nested-LoopJoin）：在MySQL中，多表join查询是通过循环嵌套查询来实现的；示例：

      select * from t1 straight_join t2 on t1.a = t2.b;

    • 假设 t1表有100条数据，2表有1000条数据

    • 这个语句没有where条件，那它就会先从t1表中取出所有的数据（100⾏），然后对这100⾏数据进⾏循环遍历，每次取⼀条数据，⽤它的字段a的值到t2表中扫描所有⾏（1000⾏），找到t2表中满⾜条件t1.a=t2.b的⾏；再跟t1表中的记录合并放到结果集；

      • 如果还有t3表，则再对t2表中取出来的所有记录进⾏循环，再使⽤每⼀条数据去t3中查询满⾜条件的记录；

      • 伪代码

        t1Rows = queryFromT1();
for t1Row in t1Rows {
    t2Rows = queryFromT2(t1Row.a);
    for t2Row in t2Rows {
        t3Rows = queryFromT3(t2Row.xx);
        // 将 t1Row、t2Row、t3Row 组合成为结果集
   }
}

      • 那知道了MySQL是循环嵌套查询的，我们再回过头来看看，驱动表与被驱动表应该怎么选择更好呢？

        • 因为得拿驱动表中查出来的结果，全部遍历之后去被驱动表中查询；
        • 驱动表中的数量越少越好，也就是说使⽤⼩表作为驱动表

1.2 Join 查询类型

下⾯来看看MySQL中的不同情况下join查询是怎么实现的，后⾯的join查询都使⽤这个作为示例：

select * from t1 straight_join t2 on t1.a = t2.b;
  • 假设t1表有100条数据，t2表有1000条数据；
  • 并且⾥⾯的数据是⼀⼀对应的，即t1表和t2表关联的数据⼀定存在，且只存在⼀条；

1.2.1 Index Nested-Loop Join（NLJ）索引嵌套循环连接

如果t2表中的字段b有索引，此时这个语句的执⾏过程为：

  • 对t1表做全表扫描，这个过程扫描出来了100⾏数据；
  • 遍历这100⾏数据，对于每⾏数据，都根据字段a到t2表中去查找满⾜t2.b=a的记录⾏；
  • 因为t2表中的字段b是有索引的，所以这个查找就是⾛索引查找，速度很快；
  • 但是执⾏的是select*，所以根据字段b索引查找之后，还要到t2的主键索引中回表得到全部的数据信息；
  • 最终从t2表中查找到对应的记录⾏，加⼊结果集；

也就是说，当驱动表的关联字段有索引时，会⾛索引查找，速度很快；

并且因为数据是⼀⼀对应的，且⾛了索引查找，也只会扫描⼀⾏记录，则这个查询⼀共扫描了：100+100=200⾏记录；

1.2.2 Simple Nested-Loop Join 单嵌套循环连接

  • 如果t2表中的字段b没有索引，当从t1表中扫描出100⾏数据，并遍历每⾏数据，到t2表中去查找满⾜t2.b=a的记录⾏时，就得做全表扫描了，也就是说会扫描1000⾏数据；
  • 这种情况下，这个查询⼀共会扫描：100*1000=100000⾏记录；
  • 这样扫描太笨重了，速度也太慢了，所以MySQL肯定不会采取性能这么差的查询⽅式，⽽是会采⽤BlockNested-LoopJoin的⽅式；

1.2.3 Block Nested-Loop Join（BNL）缓存块嵌套循环连接

MySQL的线程中开辟了⼀块叫做join_buffer的内存区域；

在t2表中的字段b没有索引的情况下，MySQL采⽤的BlockNested-Loop Join优化算法的执⾏流程为：

  • 把t1表中的所有数据读⼊线程内存join_buffer中，由于我们这个语句中写的是select*，因此是把整个t1表所有字段放⼊了内存；
  • 扫描表t2，将表t2中的每⼀⾏取出来，到join_buffer中去做⽐较，如果满⾜t1.a=t2.b时，就放⼊结果集：

BNL

PS:

  • 在这个过程中，对于t1表和t2表都做了⼀次全表扫描，因此总的扫描⾏数为100+1000=1100；

  • 但是join_buffer中的数据是⽆序的，所以对于t2表中取出的每⼀条数据，都要进⾏100次判断，因此在内存中做的判断次数为100*1000=100000；

  • 在前⾯使⽤SimpleNested-LoopJoin算法进⾏查询，扫描⾏数也是10万⾏；因此，从时间复杂度上来说，这两个算法是⼀样的；

  • 但是，BlockNested-LoopJoin算法的这10万次判断是内存操作，⽽SimpleNested-LoopJoin算法是在磁盘上的扫描操作；所以速度上会快很多，性能也更好；

这⾥还有⼀个问题，join_buffer的⼤⼩是有限的（由参数join_buffer_size设定，默认值是256k）；

如果join_buffer中放不下t1表中的全部数据怎么办呢？

解决⽅式为分段放：假设t1表中有10000⾏数据，join_buffer中只能放1000条：

  • 每次从t1表中读取1000条放⼊join_buffer，然后扫描t2表中所有数据到join_buffer中去作对比；
  • 然后清空join_buffer，再次从t1中读取1000条放进⼊，然后再扫描t2表中的数据去对⽐；
  • 这样⼀次⼀次的分段将t1表中的数据加载到join_buffer，直到得到最后的结果集；

这个流程，叫做分段，也叫做分块，也就是分块放⼊join_buffer，也就是算法名称中 “Block”的由来；

Block Nested-Loop Join（BNL）的弊端：

  • 这⾥当join_buffer_size较⼩，或者t1表中数据量较⼤时，需要分块地多次放到join_buffer中，然后每次都要扫描t2表中的数据来做对⽐；
  • 在最开始我们将BufferPool的时候讲到了，优化后的LRU链表变为了冷热数据分离的LRU链表，先加载进来的数据⻚放在冷数据区，这个数据⻚在1s之后再次被访问过，才放到热数据区；
  • 所以针对于这⾥的t2表，不就是会多次扫描它，如果整个过程执⾏的时间超过了1s，不就会把t2表所在的数据⻚，给移到LRU链表的热数据区去了；
  • 如果这个t2表是⼀个很⼤的表，那可能就会将LRU链表中热数据区⾥⾯原本的热数据，给全部淘汰掉；
  • 并且join语句还在⼀直循环读取磁盘和加载数据⻚，也就是会⼀直往冷数据区加载进来数据⻚，满了之后也会淘汰冷数据区；
    • 则此时还会影响业务正常访问的数据⻚，没有机会进⼊到热数据区了；
    • 因为它⼀加载进去冷数据区，还没等1s后再次被访问呢，就被join语句加载到冷数据区的给淘汰了；
  • 所以，⼤表join不⽌会影响IO性能，还会影响BufferPool中的缓存命中率的性能；○为了减少这种影响，可以考虑增⼤join_buffer_size，减少对被驱动表的扫描次数；

当然，为了真正减少这些影响，我们最好不要出现BNL，⽽是对被驱动表的关联字段加上索引；

1.2.4 Multi-Range Read（MRR索引多范围查找）（优化⼿段）

这个Multi-RangeRead主要是⼀种优化的⼿段，它的⽬的是尽量将读磁盘的随机IO转化为顺序IO；

#字段a有索引
select * from t1 where a>=1 and a<=100;

我们前⾯说过回表的概念，这⾥是select *，并且使⽤的是字段a的索引，所以在a索引中查询出满⾜a>=1 and a<=100的记录之后，需要回表到主键索引中查询出所有的数据信息；

那这⾥的回表，是怎么回的呢？是将这些记录⾏⼀⾏⼀⾏的回表？还是量地回表查询呢？

  • 主键索引⻚是⼀棵B+树，在这颗树上，每次也只能根据⼀个主键id进⾏查找数据；所以回表肯定是⼀⾏⼀⾏的回的；

回表

但是⼀般来说，字段c和主键id是没有什么顺序关系的，当你使⽤索引c查询出来的⼀批有序的数据，它们对应的主键id基本都是⽆序的；

此时去做回表的话，那不就是⽤⼀批⽆序的主键id去主键索引中进⾏查找，也就出现了随机访问，随机IO，性能就会很低了；

优化思路：

因为主键索引中的数据都是按照主键id递增的顺序存储的，当我们按照主键id递增的顺序进⾏查询时，就接近为顺序IO了，也就能极⼤程度的提升性能了；基于这个优化思路，MMR优化后的语句执⾏流程为：

  • 根据索引a查询出来满⾜a>=1 and a<=100条件的记录，并将这些记录的id值放⼊到read_rnd_buffer（也是⼀个内存缓冲区）中；
  • 在内存中对read_rnd_buffer中的所有id进⾏⼀个递增的排序；
  • 然后将排序之后的id数组，再到主键索引中进⾏回表查找，得到所有数据信息后放⼊结果集；这个时候，去主键索引中回表的所有ID，都是递增的，也就能实现顺序读取，转变为了顺序IO，提升了性能。

MMR

跟join_buffer⼀样，read_rnd_buffer⼤⼩受参数read_rnd_buffer_size控制，当read_rnd_buffer放满时，也是分段去多次这样执⾏即可；

另外，现在MySQL的优化器中在判断执⾏成本时，倾向于不使⽤MRR，因此MRR不是都会⽤到的；如果你想要稳定地使⽤MMR，需要设置setoptimizer_switch=”mrr_cost_based=off”，把mrr_cost_based设置为off，就是固定使⽤MRR了；

1.2.5 Batched Key Access（BKA）批量索引键值访问

这个Batched Key Access，是对Index Nested-Loop Join（NLJ）的优化；

在前⾯讲的IndexNested-LoopJoin（NLJ）中，

  • 从t1表中每次取⼀⾏记录，通过字段a到t2表中去查找满⾜t2.b=a的记录⾏；
  • 然后再将满⾜的记录⾏到t2表主键索引中去做回表；
  • 每次t1遍历⼀条数据，到t2中匹配到⼀个值，然后就要做⼀次回表；
  • 这样⼀⾏⼀⾏地去做回表，MMR的优化就⽤不上了；所以要思考能否实现⼀次性多传⼀些值到t2表中进⾏扫描匹配呢？这样的话，批量的数据应该就能使⽤上MMR了；实现⽅案：
    • 将t1表中的数据取⼀部分出来，先放到⼀个临时内存，这个内存其实就是join_buffer；
      • join_buffer在BNL中是暂存驱动表⾥⾯的数据；
      • 但是在NLJ中是没有使⽤的，所以这⾥就使⽤来存储⼀批次的要传到t2表的数据；
    • 再将这个join_buffer中的⼀批数据传到t2表中，在t2表中等于是⼀批次地去查找到满⾜t2.b=a的多个数据⾏，此时在t2表中也就可以⽤上MRR的优化了；

BKA

同样，如果要使⽤BKA优化算法的话，也需要设置：

set optimizer_switch=’mrr=on,mrr_cost_based=off,batched_key_access=on’;

前⾯两个参数是启⽤MRR，因为 BKA算法的优化要依赖于MRR；后⾯⼀个参数是启BKA；

2. 执⾏计划

我们InnoDB引擎有⼀个东⻄：查询优化器，这个查询优化器的作⽤就是对于每个SQL都会去⽣成⼀个执⾏计划；

这个执⾏计划也就是：

对于⼀个SQL语句（不管简单还是复杂），在MySQL底层的磁盘上有⼤量数据⻚，有主键索引和⼀堆普通索引；那么如何去使⽤索引、如果去确定查表的顺序、如何进⾏筛选、如何进⾏排序分组等；

SQL调优：

通过分析执⾏计划，就能知道你的SQL真正是怎么执⾏的了，然后也就能根据实际情况去想各种办法改写你的SQL语句，改良索引设计，进⽽优化SQL语句的执⾏计划，最终提升执⾏速度；

MySQL针对⼀个SQL语句可以有多种执⾏的⽅案，那最终是通过什么来得出⼀个最佳的执⾏计划呢？

  • 基于成本来选择执⾏计算
  • 基于规则来优化执⾏计划

2.1 基于成本选择执⾏计划

这个基于成本选择执⾏计划，其实就是去评估各种⽅案的成本，然后选择⼀个成本最低的执⾏计划，来得到最佳的查询速度；

MySQL需要评估的成本主要是两种：IO成本和CPU成本；

为什么是这两种？我们来想⼀下MySQL要执⾏⼀个SQL语句：

  • ⾸先，这些数据是在磁盘中的，那就需要把磁盘中的数据⻚给读取出来，加载到bufferpool中，这个从磁盘读取数据⻚的成本就是IO成本；
    • MySQL从磁盘读取数据都是⼀⻚⼀⻚的读取，⼀般约定读取⼀⻚的成本为1；
  • 另外，当你拿到数据之后，可能需要对数据做⼀些运算，⽐如：验证是否符合筛选条件、或者做⼀些排序、分组之类的事情；
    • 这些事情都是由CPU来做的，于是就会消费CPU资源，也就是CPU成本；
    • ⼀般约定读取或者检测⼀条数据是否符合条件的成本为0.2（这个在MySQL8.0版本已经改为了0.1）；

这个1和0.2都是MySQL⾃定义的⼀个成本常数，你也可以看做⼀个具体的计量单位，不然它的成本也没法被计算出来；

那我们来看看在你的MySQL中，如何查看这些成本常数：

  • server层

    SELECT * FROM mysql.server_cost;

    server层

    • 这⾥我们要关注的是最后这个row_evaluate_cost，即上⾯说的读取或者检测⼀条数据是否符合条件的成本为0.2；

  • engine层

    SELECT * FROM mysql.engine_cost;

    engine

    • 这⾥我们要关注的是io_block_read_cost，即上⾯说的从磁盘中读取⼀个数据⻚的成本为 1；

    知道了成本的概念之后，我们再来看看MySQL具体是怎么计算这些成本的；

2.1.1 全表扫描的成本计算

⾸先我们来看下这个SQL语句： 

#表t中的x1字段是没有索引的，所以这⾥要⾛的就是全表扫描；
select * from t where x1 = xxx;

全表扫描：

去扫描主键索引的所有叶⼦节点的数据⻚（因为主键索引的叶⼦节点中是存放了⽤户记录的所有数据信息的）

那全表扫描的执⾏成本怎么计算的呢？

  • ⾸先需要把磁盘中的主键索引的叶⼦节点的数据⻚，都⼀⻚⼀⻚的加载到bufferpool中进⾏⽐较，那么有多少数据⻚就会消耗多少IO成本；
  • 然后再在bufferpool（内存）中对⾥⾯的每⼀条数据，都判断是否符合查询条件，那么有多少条数据就会消耗多少CPU成本；对于全表扫描，有个简单⽅法可以去看看它的执⾏成本；
  • 有⼀个命令为：show table status like’表名’;
  • 这个命令可以拿到你的表的统计信息，当你在对表进⾏增删改查的时候，MySQL会⾃动给你维护这个表的⼀些这些统计信息；

show table status

  • 这⾥有两个字段：

    • Rows：表示表中的记录⾏数；
      • 但是要注意，这⾥的Rows是⼀个估算值，不是真实值；如果你要得出真实值，那不得在执⾏得到执⾏计划的时候还要去计算或者执⾏⼀个selectcount(1)？正常来说不可能这样的对吧；
      • 因为InnoDB引擎中没有地⽅记录⼀个表中到底有多少⾏数据；
    • Data_length：表示表的主键索引的字节数⼤⼩；即Data_length/1024/16=数据⻚数；

  • 因此，这个全表扫描的执⾏成本为：

    • 数据⻚数1 + 记录⾏数 * 0.2 = (27449622528/1024/16) * 1 + 28748912 0.2 = 7425174；

2.1.2 主键索引的成本计算

在⽹上没有找到真正讲主键索引的成本计算的，都只有讲普通索引的成本计算的；但是我认为这个计算⽅式应该是差不多的，所以按照普通索引的计算⽅式应该是没问题的；

对于这种索引查询的⽅式，MySQL的设计是：

  • 不去管到底占⽤了多少的数据⻚，⽽是⽐较粗暴的认为读取索引的⼀个范围区间的IO成本就等于读取⼀个⻚⾯的成本；例如：where id > 10：
  • 这就是⼀个区间，成本为1；（这⾥应该对于whereid=10这种等值查询，也就是成本为1）
  • where id > 10 and id < 20;：这就是两个区间，成本为2；
  • 另外，就是去⽐较有多少条数据满⾜筛选条件：如果是主键id的等值查询，表中也只有⼀个唯⼀的id，这种情况成本为1+0.2；
  • 如果是主键id的范围查询，就需要在定位到第⼀条之后，往后去⽐较还有多少满⾜范围的；如果有n条，则为0.2*n；这种情况成本为1+0.2n；

这⾥使⽤这个语句可以查出：

EXPLAIN format=json
SELECT * FROM `server` WHERE id = 1;

主键索引成本

这⾥的 query_cost 就是执⾏这个查询语句的⼤概成本了

当改为范围查询时：

EXPLAIN format=json
SELECT * FROM `server` WHERE id > 1;

query_cost = 7425174

2.1.3 普通索引的成本计算

⾄于普通索引，它的成本消耗的计算⽅式，跟前⾯的主键索引⼀样：

  • 按照读取索引的⼀个范围区间的IO成本就等于读取⼀个⻚⾯的成本；
  • 然后⽐较有多少条数据满⾜筛选条件；也就是假设为⼀个范围，那它的成本就是1+0.2n；
  • 但是对于普通索引，它的B+树叶⼦节点数据⻚中只存储索引项+主键Id，所以需要回表；
  • 对于回表操作的成本计算，MySQL也⽐较粗暴的认为，⼀条数据要回表就得回表到主键索引上去查⼀个数据⻚；
  • 上⾯普通索引查询出来有n条数据，则回表的IO成本为：n*1；
  • 回表到主键索引中，主键id是唯⼀的；则CPU成本应该是每条0.2，⼀共有n条，则为0.2n；

所以最终，普通索引的成本为：(1+0.2n)+(n+0.2n)；

2.1.4 多表join的成本计算

多表join的成本计算更为复杂，我想我们也没有必要去真正了解清楚这些，有兴趣的可以⾃⼰下去研究研究；

⼤概计算⽅式为：

  • 先根据全表扫描或索引扫描查询驱动表，得到驱动表的成本；（假设此时查询到了n条数据）
  • 由于join查询是循环嵌套查询，也就是驱动表中的每条数据，都会到被驱动表中进⾏查询；
  • 那再对于每条数据，根据全表扫描或索引扫描查询被驱动表，得到被驱动表的成本；

则最终的成本为：驱动表成本+n*被驱动表成本

2.2 基于规则优化执⾏计划

前⾯我们讨论的是基于成本的计算来选择⼀个执⾏计划，也就是在⼀个SQL语句可能有多种执⾏计划可供选择，然后计算成本之和选择最低的那种；

在此之外，MySQL还会基于⼀些规则去优化或者说改写执⾏计划；因为它可能觉得你写的SQL语句并不好，直接按照你的SQL语句⽣成的执⾏计划的效率不⾼，可能就需要主动帮你改写⼀下（也叫查询重写）；查询重写会按照⼀定的规则：

  • 去掉不必要的括号：
  select * from tb where ((a = 5 AND b = c) OR ((a > c) AND (c < 5))) -> (a = 5 and b = c) OR (a > c AND c < 5)
  • 将语句中没有必要的，多余的括号给去掉，使得语句更为简洁、阅读性更好；

  • 常量替换：

    • 例如你的SQL中有： i = 5 and j > i 这种语句的话，这⾥的i已经是常量5了，所以会直接被替换成：i = 5 and j > 5 ；

  • 等值替换：

    • 跟常量替换类似，你的SQL中有： a = b and b = c and c = 5，全都是⼀个常量，也就会被直接替换成：a = 5 and b = 5 and c = 5 ；

  • 表达式计算：

    • 在SQL中如果在表达式中只包含常量的话，它的值就会被直接先计算出来；如： a = 5 + 1，会被直接替换为： a = 6 ；

  • 外连接消除：

    • 内连接是需要两个表中都存在对应的记录才能进⾏匹配上加⼊结果集中；
    • ⽽外连接是以驱动表为主，如果被驱动表中的记录⽆法匹配上，则相关字段⽤NULL来填充之后加⼊结果集中；那么，如果存在这样的情况：在外连接的语句中，被驱动表中的相关字段不可能为NULL时，那么此时的外连接可以转换为内连接；
      • 好处在于，优化器可以两者转换来评估不同连接顺序的成本（内连接的驱动表和被驱动表是可以调换顺序的）；
      • 这种情况也成为“空值拒绝”；那哪些情况被驱动表中的字段不可能为NULL呢？例如：
    SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.n2 IS NOT NULL;
SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;
#其实这两种情况，就都跟 内连接 没有什么区别了

  • ⼦查询优化：

    • 为了更快的执⾏⼦查询，MySQL想了⾮常⾮常多的⽅法来进⾏优化；这⾥就⽤⼀个示例来说明，MySQL是怎么通过 ⼦查询优化 来 优化执⾏计划的；例如这样⼀个SQL语句：

      select * from t1 where x1 in (select x2 from t2 where x3 = xxx);

    • 这个语句可能是按照如下⽅式执⾏：

      • 将外层查询和内层查询当做两个独⽴的单表查询来对待；
      • 先执⾏内层查询，得出所有满⾜x3 = xxx的结果，并放⼊内存中；
      • 再执⾏外层查询，select * from t1 where x1 in (xx, xx, xx, ……)

    • 但是这样可能会存在⼀些问题，如果外层查询得出的结果集太多时：

      • 内存可能放不下
      • in (xx, xx, xx, ……) 时可能⽆法使⽤到索引，进⾏全表扫描
      • 检测⼀条记录是否符合 in 中的参数匹配花费的时间太⻓；导致整体执⾏的时间太⻓

    • 因此，MySQL并没有将内层查询的结果集当做外层查询的参数这样设计；⽽是将内层查询的结果集给写⼊了⼀个临时表中（也叫做物化表）；这个临时表的写⼊过程为

      • 这个临时表的列，也就是⼦查询结果集中的列；
      • 写⼊临时表时会被去重（可以让临时表变得更⼩）；
      • ⼀般情况下⼦查询的结果集不会太⼤，所以⼀般临时表使⽤基于内存的Memory存储引擎，并创建hash索引；
      • 如果这个结果集太⼤了（有⼀个系统参数tmp_table_size），如果超过了则会使⽤InnoDB存储引擎，并创建B+树索引；

    • 这样，创建了⼀个带有索引的临时表，那也就是两个表之间进⾏join了，也就是将⼦查询给优化为了连接查询，就可以⽤到连接查询的⼀些优化或者改写了；

3. explain

上⾯讨论了MySQL会基于成本和基于规则来选择出⼀个最优的执⾏计划，这个执⾏计划也就是MySQL会怎样去执⾏你的SQL语句，例如：多表连接的顺序、怎么样去访问每个表、使⽤哪个索引（或不使⽤索引）等；

这些东⻄都是MySQL内部的执⾏，那我们怎么知道MySQL具体是怎么执⾏的呢？

为此，MySQL提供了explain关键字，⽤它也就可以让我们查看每个语句的具体执⾏计划；⽤法就是在你的SQL语句之前，直接加⼀个explain就⾏了；然后explain会给你输出⼀个列表，这个列表也就是MySQL对于你这条SQL语句的的执⾏计划，所以我们需要去看懂这个列表中每⼀列的含义，然后在看懂之后去思考怎么改写⾃⼰的SQL语句，使得执⾏起来更快；这其实就是SQL优化了；

我们先⼤概来看看explain中每⼀列的含义吧，后⾯再⼀个⼀个的详细解释：

explain含义

先创建两个示例表 t1， t2,并插入一些数据

CREATE TABLE t1 (
 id INT NOT NULL AUTO_INCREMENT,
 key1 VARCHAR (100),
 key2 INT,
 key3 VARCHAR (100),
 common_field VARCHAR (100),
 PRIMARY KEY (id),
 KEY idx_key1 (key1),
 UNIQUE KEY uk_key2 (key2),
 KEY idx_key1_key2_key3 (key1, key2, key3) ) ENGINE = INNODB;

3.1 id

⼀个select对应⼀个id，也就是⼀个简单的查询语句；但是在⼀些例如⼦查询、UNION⼦句的情况下，就会包含两个select语句，此时可能会有两个id；这个id也代表了SQL语句的执⾏顺序：id相同时，从上往下执⾏；id不同时，id越⼤的先执⾏；

3.2 select_type

每个select代表的⼀个⼩查询，都会对应⼀个select_type，这个select_type就表示了这个⼩查询在整个查询中扮演的⻆⾊；

  • SIMPLE：简单的查询、不包含⼦查询UNION查询等的查询都是SIMPLE类型；当然连接查询也是SIMPLE；
  • PRIMARY：对于包含了⼦查询、UNION查询这些的多个查询来说，整个⼤查询是由⼏个⼩查询组成的，这⾥⾯最外层⼤查询的select_type就会是PRIMARY；
  • UNION：对于包含 UNION查询的⼤查询来说，内层的⼩查询的select_type就是UNION；
  • UNIONRESULT：UNION之后要去重，MySQL使⽤了⼀个临时表来去重，这个临时表的select_type就是UNIONRESULT；
  • SUBQUERY：当⼦查询不能转换为连接查询时，MySQL会将⼦查询给物化，这个物化的临时表的select_type为SUBQUERY；
  • 还剩下⼀些其他的类型，我认为没有必要去了解了；

3.3 table

每个select语句对应需要去查询哪个单表

3.4 partitions

基本没有⽤到和关注过

3.5 type

这个应该算是explain中最重要的列之⼀了；explain中每⼀⾏代表了MySQL对某个表的查询⽅式，type列就表示如何去查询这个表，即查询类型、关联类型等；

先总结说⼀下 type有哪些类型：system、const、eq_ref、ref、ref_or_null、range、index_merge、index、ALL 等；

  • system：当表中只有⼀条记录时，并且依赖的存储依赖是使⽤的精确统计的话（如MyIsam、Memory，InnoDB不是精确统计），type就为system；
  • const：单词意思是常量嘛，也就是只有⼀个匹配⾏；那也就是⽤于主键索引或唯⼀索引的查询；
  • eq_ref：跟const类似，但是是在执⾏连接查询时，被驱动表是通过主键索引或唯⼀索引来进⾏关联的，也就是说这样是等值匹配，也只会有⼀条记录；
  • ref：⽤于普通索引的等值查询；或者连接查询中，被驱动表使⽤普通索引来关联；（也就是⽤上普通索引）
  • ref_or_null：对于普通索引进⾏等值查询时，这个索引列的值也可以是NULL时，对于这个表的访问⽅式；
  • range：⽤于主键索引或普通索引的范围查询，或普通索引的in查询；
  • index_merge：⼀般情况下，只会为单个索引⽣成扫描区间来进⾏访问；但是在某些场景下，MySQL可能个会使⽤两个索引来提取数据，也就是使⽤索引合并来对某个表进⾏查询；
  • index：要查询的字段都能被索引给覆盖（也就是我们说的覆盖索引），但是⼜不能完全⾛这个索引的树搜索（没法使⽤到B+树的查询进⾏定位）；这⾥⼤家可能会有点奇怪，是什么意思呢；
    • 这种情况，也就是你不能使⽤这个索引来直接定位，但是⼜的确使⽤了这个索引，怎么使⽤的呢？
      • 也就是去扫描这个索引的B+树结构的所有叶⼦节点（也就是数据⻚）；
    • 为什么要去扫描这个索引的叶⼦节点，⽽不去做全表扫描呢？
      • 因为这⾥是覆盖索引，这个索引中已经有了所有需要的信息；并且这个普通索引中内容要⽐主键索引的内容少，扫描起来更快；
      • 所以就直接扫描这个索引的叶⼦节点，⽽不去做全表扫描；
    • 其实这种情况，可能性能也是⾮常慢的；因为它并没⽤使⽤到树搜索；
    • 什么情况才会是这样呢？
      • ⽐如⼀个联合索引中，前⾯的列使⽤了范围查询、跳过了等，使得后⾯的列没法再按照索引进⾏搜索了；但是查询的列都在这个联合索引中，属于覆盖索引，可以不去回表；
  • ALL：这个没什么好说的了，就是全表扫描；但是要记住的是全表扫描扫的是主键索引的叶⼦节点中数据⻚的全部记录⾏，挨着挨着扫就完了；我们前⾯说过这⾥怎么扫描的；

3.6 possible_keys和key

possible_keys 表示在执⾏某个查询语句中，对于某个单表查询时，可能会⽤到的索引有哪些；

key 就表示实际上真正⽤到了哪个索引；

有⼀点要注意的是，possible_keys 中的列不是越多越好，因为这⾥⾯的数量越多，就代表MySQL需要 去计算成本和⽐较成本时的开销也就越⻓；

所以，在可能的情况下，尽量删除⼀些多于的索引；

3.7 key_len

MySQL在决定使⽤哪个索引的时候，会有对应的扫描区间，以及形成这个扫描区间的边界条件；这个key_len的组成也就是扫描区间的边界条件；

计算⽅式：索引列的实际数据最多占⽤存储空间的⻓度：

  • INT类型:实际最多占⽤就是4字节；（INT其实不论存什么都是4字节）

  • 变⻓类型VARCHAR(100)，最多占⽤字节数为：

    • 假设对于utf8字符集下⼀个字符最多占⽤的字节数*最多可以存储的字节数；这⾥也就是100 X 3=300；

  • 如果索引列可以存储NULL值，则再在上⾯key_len计算值上加1字节；

  • 对于使⽤了变⻓类型的索引列，都会有2字节的空间来存储实际数据占⽤的存储空间⻓度，则再在上⾯key_len计算值上加1字节；

    key_len

    • key1 是 varchar(100)，则 3 *100 = 300；
    • key1 允许为 NULL，则再加 1 为 301；
    • key1 是变⻓字段，则再加 2 为 303；

  • 对于联合索引⽽⾔

    key_len

  • 这⾥的 611，也就是：

    • key1, key3 都是 303；
    • key2 为 INT，也允许为 NULL ，则为 5；
    • 那最终结果为 303 + 303 + 5 = 611；

3.8 ref

ref表示跟索引进⾏匹配的是什么，是常量，还是列，还是什么；

3.9 rows

  • 当决定使⽤全表扫描的⽅式对某个表进⾏查询时，rows就表示整个表的评估⾏数；
  • 当决定使⽤索引的⽅式对某个表进⾏查询时，rows就表示要扫描的索引中的⾏数；

3.10 filtered

filtered

  • 这⾥使⽤idx_key1索引进⾏查询，从rows列可以看出评估出来满⾜条件的有15条记录；
  • filtered中的10表示这15条记录中，有多少条满⾜条件common_field=1000；
  • 这⾥是10，也就是10%，即只有1,2条满⾜条件的意思；

3.11 Extra

Extra列是⽤来说明⼀些额外信息的，我们可以通过这个Extra列来更精确地理解MySQL到底如何执⾏给定的查询语句；

Extra可能显示的额外信息⾮常⾮常多，我们这⾥就只介绍⼏个平时最常⻅的即可；

  • Using index：使⽤了覆盖索引，要查询的列都可以从索引中返回，也就不⽤再回表去查了；

  • Using index condition：索引下推

    • 在联合索引中，只能使⽤到索引的前⾯字段，因为⼀些模糊查询、范围查询等35导致不能⽤到后⾯字段了；

    • 但是这个查询⼜是需要回表的，所以先在存储引擎层利⽤联合索引中后⾯的字段，对于满⾜前⾯字段的查询结果进⾏过滤，过滤之后再去回表；这个就是索引下推；

      • explain SELECT * FROM t1 WHERE key1 LIKE 'c309%' AND key2 > 1000;

      • 对于key1可以⽤上索引，但是是范围查询了，就会导致key2⽤不上索引；

  • Usingwhere：上⾯说到的索引下推，是因为剩余部分的条件可以在索引中进⾏过滤，也就是可以下推到存储引擎层进⾏过滤；

    • 但是查询条件中可能还存储⼀些不在索引中的条件，对于这种条件的过滤，就只有在MySQL的服务器层进⾏过滤了；这种情况也就是Usingwhere；
    • 最简单的⼀个没有索引的查询，也就是使⽤的 Using where，因为它需要在服务器层进⾏过滤；

  • Using join buffer 连接查询中，被驱动表的关联字段没法使⽤上索引时，就会显示Using join buffer

  • Using filesort

    • 仅仅表示没有使用索引的排序，事实上filesort这个名字很糟糕，并不意味着在硬盘上排序，filesort与文件无关。因此消除Using filesort的方法就是让查询sql的排序走索引

  • Using temporary

    • 表示由于排序没有走索引、使用union、子查询)连接查询、使用某些视图等原因，因此创建了一个内部临时表。注意这里的临时表可能是内存上的临时表，也有可能是硬盘上的临时表，理所当然基于内存的临时表的时间消耗肯定要比基于硬盘的临时表的实际消耗小。
无标签
 上一篇
MySQL ⽇志（Redolog Undolog Binlog） MySQL ⽇志（Redolog Undolog Binlog）
MySQL ⽇志（Redolog Undolog Binlog）1. Redolog在前⾯我们对Redolog下过⼀个定义：它是崩溃⽇志，⽤来⽀持崩溃恢复的；为什么需要它呢？ 因为MySQL为了提⾼⾃⼰的性能，避免⼤量的磁盘随机IO的发
2022-07-19 MySQL
下一篇 
MySQL索引应⽤介绍 MySQL索引应⽤介绍
MySQL索引应⽤介绍1. 索引分类MyIsam 存储引擎中索引的存储格式 在MyIsam中，也是通过B+树作为索引的数据结构，但是跟InnoDB不同的是，MyIsam的索引⽂件和数据⽂件是分开的，使⽤.MYI来表示索引⽂件，使⽤.MYD来
2022-07-08 MySQL
  目录