mysq DBA（三）调优：sql执行计划

一、索引

主键索引：每个表只能有一个

唯一索引：字段的值是唯一的索引

普通索引：普通字段的索引。

索引底层是B+树结构的索引，mysql会对索引字段的值进行排序，排序完只会查询的速度才会很快。

索引定义：一张表上可以有多个索引，指定后会对索引字段进行排序，排序的目的即为了快速的查询。

B+树的缺点：插入时要对索引字段进行排序维护，维护代价比较大。

---

1、B+Tree

 mysql对数据储存是以页/块为基础的，其中叶子节点是有序的，且叶子节点之间是双向链表。

B+树进行查找后，只能找到数据对应的页（16k）。页内的数据也是排序好的数据。

排序好的数据查询方式都是用二分法进行快速查找。

这些排序都是逻辑上的排序（即物理地址可能不是有序的，用指针形成有序）

---

如果对b+树进行插入，先二分查找看要插入哪个页，如果页有空闲进行插入，页满了则进行split操作。

---

创建索引：
例子：创建唯一索引：

 创建普通索引：

删除索引：

---

创建索引时，如果表的数据量比较大，此时需要调整一个参数，不然可能会出现创建索引失败的情况。

 在创建索引的5分钟内，所有对于该表的操作都会记录到这个日志中（属于内存的，默认128M），如果该日志大小满了，再针对该表的操作就会报错，此时创建索引也会失败。

所以在创建索引的时候建议把这个调大一点。（可以在配置文件中配置）

创建索引的过程是会锁表的。

在主从架构中，这样创建索引会存在主从延时，所以可以用一些工具去在线创建索引，可以尽量减少延迟。

---

索引组织表（聚集索引）：对于主键索引，其叶子节点存放了整行数据。而辅助索引树的叶子节点只存放主键值（键值+指针）。（innodb就是聚集索引类型）

回表：就是根据辅助或其他非主键索引树查找到叶子节点后，拿到主键再在主键索引树中再查询一次。

非聚集索引：叶子节点存放的是磁盘文件指针，没有回表操作。

各自的优缺点：

注意：虽然说聚集索引的叶子节点存储的是整行数据，但是其实是叶子节点将主键+主键对应的页号+磁盘中对应页数据统称为叶子节点的数据，所以叶子节点还是需要根据页号快速的去磁盘中获取相应页数据（页数据也是有序的，所以可以二分快速查找）加载到buffer pool（mysql内存）中进行使用。其过程还是需要io操作。

（索引树本身也是磁盘中的）

 （聚集索引是叶子节点+数据存放在一起，非聚集索引是分开存的，最后还是要把相应的页读到bp中的）

---

每个页（16k）中有一个填充因子的概念，默认大小一般是16分之一。

创建索引时，可以指定一个填充因子，以便在索引的每个叶级页上留出额外的间隙和保留一定百分比的空间，供将来表的数据存储容量进行扩充和减少页拆分的可能性。填充因子的值是从   0   到   100   的百分比数值，指定在创建索引后对数据页的填充比例。值为   100   时表示页将填满，所留出的存储空间量最小。只有当不会对数据进行更改时（例如，在只读表中）才会使用此设置。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中对数据页进行拆分的需要，但需要更多的存储空间。当表中数据会发生更改时，这种设置更为适当。

100即1k。最小值10。一般不用调这个参数了。

https://www.cnblogs.com/chinahbzm/articles/1041405.html

---

高/低选择性：

 存放索引信息的表：information_schema.statistics表中

 索引建立的字段一般是在大量的数据中查找少量数据，如果存在性别这种一般数据对分的字段，建立索引是没有大必要的。

这个可以用一个cardinality来表示，我们可以查看一张表的主键字段的cardinality是多少：

 然后查看这张表的数据行数：发现cardinality和行数一样，因为该主键唯一。

 注意：这两个值不是一定相等的，因为是采用采样的方法，所以这个值是预估的。

如果cardinality/table_rows的值接近1的话表示选择性，则比较建议建索引，如果是比较小则不太建议建索引。

---

组合索引：多个字段组合起来的索引。

和之前单个字段索引是类似的，不过其排序是基根据最左原则进行排序的。

即如果组合索引是a、b、c。该索引排序方式是先排序a，再排序b，最后对c进行排序。

所以要使用后面b索引需要在a排好序的情况下才能用到索引，要使用c的索引，要在a、b已经排好序的情况下。否则不会使用索引。

如果现在某个语句例如：a =？and b =？and c=？，此时abc、bca、cab等等索引排列都行，此时一般可以将选择度高的字段排在前面。

对于组合索引，再去查看索引信息时，：其中SEQ_IN_INDEX的值有1，表示是组合索引的第一个字段。

---

注意：如果现在有a、b两个个字段组成一个组合索引。此时c是主键。

那么此时ab索引树的叶子节点存的数据就会是（key，pk）且根据这个进行排序（key对应a、b，pk是主键c）。所以此时叶子节点的排序是根据abc进行排序的。

又因为叶子节点有主键的值，所以有些索引又称覆盖索引，即如果现在要查a、b、c三个字段的数据，组合索引树（二级索引/辅助索引）的叶子节点已经有了所有的数据，此时不用进行回表操作。

这个例子也是索引覆盖（不用进行回表）。 但是这个sql最优的方法还是对buy_date添加单个索引。

---

在mysql中，如果对于a、b、c组合索引，都是对于（a asc，b asc，c asc）进行排序的。

如果此时突然我们where a= ？order by b desc，c 此时中间的字段排序和索引正常排序的结果相反了，此时就不会用都索引了。

 但是如果此时是where a =？ order by b desc，c desc。此时就会用到索引，mysql会将b、c正序的结果优化为逆序结果输出。

那如果是where a =？ order by b ，c desc。此时是不会用到索引的。

总结：在mysql5.7中，如果排序的字段中要使用索引必须是同一个方向，例如都是正序或者逆序。这样才可以用到索引。在5.7中如果改变索引的排序是无法生效的，例如b索引排序改为desc是会被mysql忽略的，最后还是以默认的asc进行排序。

在mysql8.0才可以建立desc的排序索引。

那么5.7怎么解决降序desc的问题？

——此时就可以用5.7新增的功能：函数索引

---

函数索引：

先创建一个虚拟字段（不是真实存在的，调用的时候执行相应的函数逻辑）

 此时我们再建立这个虚拟字段的索引（索引是真实存在的）。

这就是函数索引。就像上面5.7的降序则可以用这个实现。

https://www.cnblogs.com/lynn919/p/10875081.html

MySQL函数索引及优化 - 耿小厨 - 博客园

---

索引倾斜：
例如现在有字段a，其值只要0、1、2。但是其中a的数据大部分是2，而小部分是0、1。

这样子如果查询的是0、1则使用索引比较有效。这就是索引倾斜。

可以使用force index（索引名） 让sql强制使用某个索引。

 sql语句可以使用多个索引。看优化器自己去判断。

二、join算法

之前讲的是join的语法。

一、关联算法

1、简单关联算法

如果a表有3条记录，b表有4条记录。那么此时简单关联的结果就是a表的3条记录分别去扫描b的4条记录。最后扫描成本是3*4=12。

2、索引关联算法

即如果where a.x=b.y 。y的字段又是索引字段，此时x就会去y的索引树进行扫描。这就大大的缩短了扫描成本。扫描成本O(n)。（n表示扫描内表次数）

其中a表去扫描的被称为驱动表/外表，而被扫描的b表被称为内表。

一般为了减少扫描内表的次数，驱动表要小于内表，这样扫描成本n会较低，扫描成本也会降低。

注意：内表为了减少每次扫描的时间，一般需要建立索引，如果是外表建索引而内表不建索引，优化器可能会将原本有索引的外表变为内表。

3、基于块的切割查询算法

优化简单关联算法，减少内部表被扫描的次数。

加了一个join buffer的内存，用空间换时间。将各自的列数据放到join buffer中，然后根据列数据进行比较，而不是之前的根据行数据进行比较。

如果现在有a、b表，a有1、2、3；b表有1、2、3、4

此时进行简单关联算法：外表a要扫描一次，内表b要被扫描3次（a表的每行数据都要进来扫描1次），总共的比较次数12次（行数据比较次数，也就是扫描成本）。

此时进行块内存关联算法：外表a要扫描1次，内表b要扫描一次（扫描一次后之间进去join buffer），总共比较次数12次。此时比较次数虽然没有减下来，但是减少了b表的扫描次数。

如果内表的数据比较大，此时join buffer就需要适当的调整join_buffer_size的大小（默认256k）。不过因为这个算法是在没用到索引时mysql去使用的join算法，所以如果sql已经使用了索引，此时调整join buffer的大小是没有效果的。

---

对于一些二级索引，有时候走索引的性能可能低于不走索引的性能，因为回表和索引树的io可能会占其中的较大性能消耗。

 所以对于这样的情况，可以使用索引覆盖去掉回表的操作，但如果是要select *此时在5.7中也可以这么写：

 在select后面加锁/*+MRR（表名）*/

MRR也是利用空间换时间的概念：MRR 通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。
例如现在有二级索引y，此时主键索引是x。那么y索引树的叶子节点存放的（key，pk），其中key为y值是有序的，而pk存的是x值是无序的。假如现在有pk为100、1、98这三个值要进行回表操作，那么没使用MRR的情况下会先用100通过x索引树最后磁盘io获取到相应数据，然后再来了一个1的，此时发现这条数据跟上一条数据，在物理存储位置上，离的贼远！

咋办，没办法，只能让磁盘和磁头一起做机械运动，去给你读取这条数据。这样1的数据花了较大的消耗随机读到了这个数据。相同的道理这个时候又来一个99的，和1相差也很远，此时又消耗了不少性能。这样这三个数据读下来花费了大量io消耗。

此时如果使用MRR，此时会申请一个内存空间，然后会将第一次对y索引树得到的主键值先进行排序，即将100、1、99排序成1、99、100。然后再进行磁盘的读取，这就符合了一定的顺序读了，先读1再读99最后读100，这样就大大的减少了磁盘IO的消耗。

MySQL 的 MRR 到底是什么？ - 知乎

---

hash join （8.0开始支持）

将R表（驱动表）先将列放进join buffer 然后再放进内存的hash table，扫描S表（内表）然后放进hash table中（s表的数据进入hash table会比较该hash table是否有匹配的值）。

这样驱动表需要扫描一次、内表需要扫描一次，（如果R表3行，s表4行）此时比较次数为4次。

MySQL8.0 新特性 Hash Join - 天士梦 - 博客园

这样hash join的算法就会降低比较次数。而且hash join没有回表的问题，且hash join可以用来做并发。

---

BKA join算法

这个算法是基于MRR接口实现的，也是将驱动表的列数据存放到join buffer中，然后将相应的键值（key，pk）传给mrr接口，mrr对pk进行排序，然后再和被驱动表进行比较

BKA mysql中默认不会使用。需要手动启动。

MySQL联接查询算法（NLJ、BNL、BKA、HashJoin）_joenqc的博客-CSDN博客

三、explain

如果有表a、b，此时mysql会优先选择数据较大、有用到索引的表为内表（当然也不是一定，因为mysql会根据数据各种情况计算成本的），数据量小、没索引的为外表。

explain用来显示sql语句的执行计划。

explain的结果可以用表格的形式展示出来，也可以用json的格式展示出来。

json格式例如：这样就可以看到每个语句的执行成本之类的信息。

 表格格式的：各个字段信息的定义： 

其中select_type、type、key、ref、extra是比较重要的指标。

---

执行计划 ：

  此时有三条结果，那么此时要从哪里开始看？

id相同的从上往下看，id不同的从下往上看（大部分场景这样看，也有小部分不是这个顺序）。所以我们先从id=2的那条开始看起。

id=2：该条记录是对lineitem表进行查询，其类型（type）是范围查询，用到了i_l_shipdate索引，预估行数137872，过滤出来的结果是100%，（extra）这条查询过程中用到了索引。（）

此时有两个id=1的（一般id相等表示的是关联join），join关联一般就有外表和内表，一般第一个表示的是外表（即part），第二个表示的是内表（即<subquery2>，这个表就是我们上一条语句id=2得到的那张表）(这里就是part和subquery2进行关联)，我们此时从上往下看，此时我们结合两条结果看id=1的执行计划：

id=1：此时对part进行全部扫描（all），然后根据dbt3.part.p_partkey字段和subquery2表进行关联，整个id=1的执行计划中会使用到排序（filesort）和使用where条件筛选。其中type=eq_ref表示该关联是根据唯一索引进行关联的。

这里使用了auto key的一个索引，此时需要回到id=2的结果，其中select_type是materialized

类型，这个表示产生的结果会生成一张表（物化为临时表，存在内存中），并且会给这张表创建一个唯一索引（因为id=1要进行关联查询，id=2的结果要作为内表，所以mysql会为他上索引）。所以id=1的auto key就是subquery2表的唯一索引。而且id=1的第二条中type为eq_ref也表示其关联是通过唯一索引进行关联的。

此时是否会发现一个问题？
——之前我们说外表的数据一般要比内表大，但这里外表的数据有19万多，明显比内表的大，为什么还用它做外表？——这里一般是mysql最后计算的成本可能选择这样做成本会低一点。（一般的话理论上还是外表要大于内表）

这里为什么会将查询出来的结果物化为一张临时表？
——1、in后面的结果在mysql中是要进行去重的，所以他要进行物化临时表

——2、因为在执行计划中这个结果被指定为内表，一般内表为了减少关联成本，需要建索引，所以要对其物化为临时表。

mysql会根据执行计划的需要看是否需要进行物化。

---

此时我们把sql的时间范围缩小为7天：

再次执行后查看explain结果：

 此时subquery2变成了外表（id相同上面的为外表），所以说mysql并不是说一定按理论去走执行计划，而是会根据实际计算出来的最优解去走。

---

  

 此时查看其执行计划：

此时 可以看id=1、2都是简单的全部扫描，然后在最后id为空那条结果中，select_type是union result（表示将前面的结果集进行合并），此时再看id为空的extra中using temporary表示使用了临时表。

这里为什么也使用了临时表？
——因为在mysql中union要对结果进行去重，所以需要创建一个临时表。

（一般需要对查询结果进行去重、加索引等等操作都要用到临时表）

---

 这个例子中id=2的select_type为关联子查询，即这个执行计划是要依赖上面id=1的执行计划，所以这个例子就和我们之前说的id不同从下往上看的不同，这个时候就得从上往下看（下面的子查询依赖于上面的查询，所以要先看上面的子查询）。

---

我们来看下type输出：

 这里的类型中越往后，表示的运行代价越大。

注意这里的index不是说使用了索引进行查询，而是使用索引进行扫描（索引全扫描，即扫描整个索引叶子节点），所以这个成本还是很大的。

extra：

 这里出现了filesort、join buffer、mrr、temporary可以看下这四个相应的参数用不用进行调整。

四、索引的补充

1、全文索引

 即对关键字进行分词，然后利用这些分词在mysql中查询。不过全文索引一般都用es等。

MySQL 之全文索引_潜心做事的博客-CSDN博客_全文索引

2、地理空间索引（一般也不推荐用这个实现GIS，性能不是很高，可以用mongdb、redis等实现）

Mysql空间索引 - 简书