索引 - MySQL查询优化 - 《MySql》

1 MySQL 查询执行过程
2 MySQL 常见的导致慢查询的原因
- 2.1 偶尔很慢
  - 2.3.1 数据库在刷新脏页
  - 2.3.2 拿不到锁
- 2.2 一直很慢
  - 2.2.1 常见到导致索引失效的查询
  - 2.2.2 数据库选错了索引
3 索引
4 Explain

1 MySQL 查询执行过程

1.1 先上图

mysql执行.png

1.2 Server层和存储引擎层

第一层，MySql Server层：连接器，查询缓存，解析器，优化器，执行器等
第二层，存储引擎层，负责数据存储和提取

1.3 查询过程概述

连接器，连接数据库，此时接待你的就是连接器。连接器负责跟客户端建立连接，获取权限，维持和关联连接。
查询缓存 MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果会以key-value对的形式，直接缓存在内存中，key 是查询语句，value是查询的结果。
解析和预处理 MySQL的解析器会根据查询语句，构造出一个解析树，主要用于根据语法规则来验证语句是否正确，比如SQL的关键字是否正确，关键字的顺序是否正确。而预处理器主要是进一步校验，比如表名，字段名是否正确等
查询优化查询优化器将解析树转化为查询计划，一般情况下，一条查询可以有很多种执行方式，最终返回相同的结果，优化器就是根据成本找到这其中最优的执行计划。例如：表有多个索引时候，决定使用那个索引，多表关联（join）的时候，决定各个表的连接顺序。
执行执行计划调用查询执行引擎，而查询引擎通过一系列API接口查询到数据。
得到数据之后，在返回给客户端的同时，会将数据存在查询缓存中

2 MySQL 常见的导致慢查询的原因

2.1 偶尔很慢
比如数据库在刷脏页或者锁

2.3.1 数据库在刷新脏页

当我们要往数据库插入一条数据、或者要更新一条数据的时候，我们知道数据库会在内存中把对应字段的数据更新了，但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，等到空闲的时候，在通过 redo log 里的日记把最新的数据同步到磁盘中去。

不过，redo log 里的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候 redo log 很快就会被写<br />      满了， 这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身心来把数据同步<br />      到 磁盘中去的，而这个时候，**就会导致我们平时正常的SQL语句突然执行的很慢**，所以说，数据库在在同步<br />      数据到磁盘的时候，就有可能导致我们的SQL语句执行的很慢了

2.3.2 拿不到锁

我们要执行的这条语句，刚好这条语句涉及到的表，别人在用，并且加锁了，我们拿不到锁，只能慢慢等待别人释放锁了。或者，表没有加锁，但要使用到的某个一行被加锁了。

2.2 一直很慢
这块是优化的核心
没有用上索引：字段没用索引，错误操作导致无法用索引
数据库选错了索引

2.2.1 常见到导致索引失效的查询
like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效
组合索引，不是使用第一列索引，索引失效（最左原则）
or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。(在大食堂项目上我就犯过这样的错误)
在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可
在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。优化方法： key<>0 改为 key>0 or key<0
对索引字段进行计算操作、字段上使用函数
当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效（例如：回表）
2.2.2 数据库选错了索引
例如：
select from t where 100 < c and c < 100000;
主键索引和非主键索引是有区别的，主键索引存放的值是整行字段的数据，而非主键索引上存放的值不是整行字段的数据，而且存放*主键字段的值。

如果走 c 这个字段的索引的话，最后会查询到对应主键的值，然后，再根据主键的值走主键索引，查询到整行数据返回。

就算你在 c 字段上有索引，系统也并不一定会走 c 这个字段上的索引，而是有可能会直接扫描扫描全表，找出所有符合 100 < c and c < 100000 的数据

为什么会这样呢？
其实是这样的，系统在执行这条语句的时候，会进行预测：究竟是走 c 索引扫描的行数少，还是直接扫描全表扫描的行数少呢？显然，扫描行数越少当然越好了，因为扫描行数越少，意味着I/O操作的次数越少。
如果是扫描全表的话，那么扫描的次数就是这个表的总行数了，假设为 n；而如果走索引 c 的话，我们通过索引 c 找到主键之后，还得再通过主键索引来找我们整行的数据，也就是说，需要走两次索引。而且，我们也不知道符合 100 c < and c < 10000 这个条件的数据有多少行，万一这个表是全部数据都符合呢？这个时候意味着，走 c 索引不仅扫描的行数是 n，同时还得每行数据走两次索引。（回表）

所以呢，系统是有可能走全表扫描而不走索引的。那系统是怎么判断呢？

判断来源于系统的预测，也就是说，如果要走 c 字段索引的话，系统会预测走 c 字段索引大概需要扫描多少行。如果预测到要扫描的行数很多，它可能就不走索引而直接扫描全表了。
那么问题来了，系统是怎么预测判断的呢？这里我给你讲下系统是怎么判断的吧，虽然这个时候我已经写到脖子有点酸了。
系统是通过索引的区分度来判断的，一个索引上不同的值越多，意味着出现相同数值的索引越少，意味着索引的区分度越高。我们也把区分度称之为基数，即区分度越高，基数越大。所以呢，基数越大，意味着符合 100 < c and c < 10000 这个条件的行数越少。
所以呢，一个索引的基数越大，意味着走索引查询越有优势。

那么问题来了，怎么知道这个索引的基数呢？

系统当然是不会遍历全部来获得一个索引的基数的，代价太大了，索引系统是通过遍历部分数据，也就是通过采样的方式，来预测索引的基数的。
扯了这么多，重点的来了，居然是采样，那就有可能出现失误的情况，也就是说，c 这个索引的基数实际上是很大的，但是采样的时候，却很不幸，把这个索引的基数预测成很小。例如你采样的那一部分数据刚好基数很小，然后就误以为索引的基数很小。然后就呵呵，系统就不走 c 索引了，直接走全部扫描了。
所以呢，说了这么多，得出结论：由于统计的失误，导致系统没有走索引，而是走了全表扫描，而这，也是导致我们 SQL 语句执行的很慢的原因。

系统判断是否走索引，扫描行数的预测其实只是原因之一，这条查询语句是否需要使用使用临时表、是否需要排序等也是会影响系统的选择的

另外，我们可以通过强制走索引的方式来查询

select * from t force index(a) where c < 100 and c < 100000;

还可以通 show index from t 来查询索引的基数和实际是否符合，如果和实际很不符合的话，我们可以重新来统计索引的基数，可以用这条命令 analyze table t 来重新统计分析。

table 表名称
Non_unique 如果索引不能包括重复值则为0，如果可以则为1。也就是平时所说的唯一索引
Key_name 索引名称
Seq_in_index 索引中的列序列号, 复合索引中索引序列代表了先后顺序
Column_name 索引的列名
Collation 列以什么方式存储在索引中, 有值’A’（升序）或者NULL（无分类）
cardinality 基数的意思。表示索引中唯一值的数目的估计值通过运行analyze table xxx_table;或者 myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。我们知道某个字段的重复值越少越适合建索引，所以我们一般都是根据Cardinality来判断索引是否具有高选择性，如果这个值非常小，那就需要重新评估这个字段是否适合建立索引。
Sub_part 前置索引的意思，如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。压缩一般包括压缩传输协议、压缩列解决方案和压缩表解决方案
Null 如果列含有NULL，则含有YES。
Index_type 索引类型
Comment Index_comment 注释的意思

3 索引
索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

3.1 优缺点

3.1.1 优点
大大加快数据的查询速度
唯一索引可以保证数据库表每一行的唯一性
加速表连接时间

3.1.2 缺点

创建、维护索引要耗费时间，所以，索引数量不能过多
索引是一种数据结构，会占据磁盘空间
对表进行更新操作时，索引也要动态维护，降低了维护速度

3.2 索引的常见模型

从数据结构来分，它们分别是哈希表，有序数组和搜索树

3.2.1 哈希表

哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的值即 key，就可
以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换
算成一个确定的位置，然后把 value 放在数组的这个位置。多个 key 值经过哈希函数的换算，
会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。
例如：你现在维护着一个身份证信息和姓名的表，需要根据身份证号查找对应的名字

图中，User2 和 User4 根据身份证号算出来的值都是 N，但没关系，后面还跟了一个链表。假
设，这时候你要查 ID_card_n2 对应的名字是什么，处理步骤就是：首先，将 ID_card_n2 通过
哈希函数算出 N；然后，按顺序遍历，找到 User2。
需要注意的是，图中四个 ID_card_n 的值并不是递增的，这样做的好处是增加新的 User 时速度
会很快，只需要往后追加。但缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢
的。哈希表这种结构适用于只有等值查询的场景

3.2.2 有序数组

有序数组在等值查询和范围查询场景中性能非常优秀。还是上面身份证号查名字的例子，如果使用有序数组来实现的话，示意图如下：

这里我们假设身份证号没有重复，这个数组就是按照身份证号递增的顺序保存的。这时候如果你
要查 ID_card_n2 对应的名字，用二分法就可以快速得到，这个时间复杂度是 O(log(N))。
(推荐一篇简单时间复杂度的文章:https://baijiahao.baidu.com/s?id=1660652875492472954&wfr=spider&for=pc)
同时很显然，这个索引结构支持范围查询。你要查身份证号在 [ID_card_X, ID_card_Y] 区间的
User，可以先用二分法找到 ID_card_X（如果不存在 ID_card_X，就找到大于 ID_card_X 的第
一个 User），然后向右遍历，直到查到第一个大于 ID_card_Y 的身份证号，退出循环。
缺点：如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦
了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。
所以，有序数组索引只适用于静态存储引擎。

3.2.3 搜索树

二叉搜索树也是课本里的经典数据结构了。还是上面根据身份证号查名字的例子，如果我们用二
叉搜索树来实现的话，示意图如下所示：
图片.png
二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。这样如果你要查
ID_card_n2 的话，按照图中的搜索顺序就是按照 UserA -> UserC -> UserF -> User2 这个路
径得到。这个时间复杂度是 O(log(N))。
当然为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保
证，更新的时间复杂度也是 O(log(N))。
树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右
递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因
是，索引不止存在内存中，还要写到磁盘上。
你可以想象一下一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据
块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于
一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间，
这个查询可真够慢的。
为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该
使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。
以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以
存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行
的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很
大概率在内存中，那么访问磁盘的平均次数就更少了。
N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中
了。
不管是哈希还是有序数组，或者 N 叉树，它们都是不断迭代、不断优化的产物或者解决方案。

3.3 InnoDB的索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。
InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。
每一个索引在 InnoDB 里面对应一棵 B+ 树。

3.3.1 聚簇索引和非聚簇索引

聚簇索引，也叫聚集索引，就是按照每张表的主键构造一颗B+树，同时叶子结点中存放的就是整张表的行记录数据，简单点说，就是我们常说的主键索引。
非聚簇索引，也叫非聚集索引，二级索引。这种索引是将数据与索引分开存储。

例如：
图片.png
表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示
例示意图如下。

根据上面的索引结构说明，我们来讨论一个问题：基于主键索引和普通索引的查询有什么区别？

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+

树；

如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，

得到 ID 的值为 500，再到 ID 索引树搜索一次。
在这个过程中，回到主键索引树搜索的过程，我们称为回表

主键索引自增
B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果插
入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为
400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。
而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新
的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。
除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，
整体空间利用率降低大约 50%。
当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合
并的过程，可以认为是分裂过程的逆过程。

自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条
新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

由于每个非主键索引的叶子节点上都是主键的值，显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

3.3.2 覆盖索引

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而
ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查
询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能
优化手段。

3.3.3 联合索引-最左原则

为了直观地说明这个概念，我们用（name，age）这个联合索引来分析。
图片.png
可以看到，索引项是按照索引定义里面出现的字段顺序排序的。
当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所
有需要的结果。
如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是”where name like
‘张 %’”。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍
历，直到不满足条件为止。
可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左
前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

3.3.4 MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
MyISAM的索引方式也叫做“非聚集”的

4 Explain

4.1 先上图

4.2 explain的列


******************************************************
           id: 1
  select_type: SIMPLE
        table: subject
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
******************************************************
1. id  //select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
2. select_type //查询类型
3. table //正在访问哪个表
4. partitions //匹配的分区
5. type //访问的类型
6. possible_keys //显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
7. key //实际使用到的索引，如果为NULL，则没有使用索引
8. key_len //表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
9. ref //显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
10.rows //根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
11.filtered //查询的表行占表的百分比
12.Extra //包含不适合在其它列中显示但十分重要的额外信息

4.2.1 id select查询的序列号

1. id 相同

执行顺序从上至下
例子：
explain select subject.* from subject,student_score,teacher 
where subject.id = student_id and subject.teacher_id = teacher.id;
读取顺序：subject > teacher > student_score

2. id 不同

如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行
例子：
explain select score.* from student_score as score 
where subject_id = (select id from subject 
where teacher_id = (select id from teacher where id = 2));
读取顺序：teacher > subject > student_score

3. id 相同又不同

id如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
例子：
explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id
 -> union 
 -> select subject.* from subject right join teacher on subject.teacher_id = teacher.id;
 读取顺序：2.teacher > 2.subject > 1.subject > 1.teacher

4.2.2 select_type 查询类型

1. SIMPLE

简单查询，不包含子查询或Union查询
例子：
explain select subject.* from subject,student_score,teacher where subject.id = student_id and 
subject.teacher_id = teacher.id;

2. PRIMARY

查询中若包含union或者子查询,最外层查询则被标记为主查询
例子：
explain select score.* from student_score as score 
where subject_id = 
(select id from subject 
where teacher_id = (select id from teacher where id = 2));

3. SUBQUERY

在select或where中包含子查询
explain select out_trade_no, 
(select  service from wbs_mch_orders  where out_trade_no='SW202003231116369037') service 
from wbs_mch_orders where out_trade_no='SW202003231116369037';

4.3 例子

SELECT
    d.channel_subsidy,
    d.trade_no,
    d.provider_subsidy,
    s.mch_name,
    s.mid_src,
    d.service,
    d.alleys,
    d.trade_type,
    d.pay_amount,
    d.service_charge,
    d.alleys_charge,
    d.cost_charge,
    d.settlement_charge,
    d.create_time,
    d.settle_status,
    d.out_trade_no,
    d.out_order_no,
    s.domain_auth,
    d.store_id,
    d.deposit_other_account,
    d.out_trade_amount,
    d.out_order_amount,
    t.NAME AS store_name,
    h.web_name,
    a.user_name AS agent_name 
FROM
    wbs_mch_detailed_orders d
    JOIN wbs_mch_seller s ON d.mid = s.id
    JOIN wbs_mch_store t ON d.store_id = t.id
    JOIN wbs_domain_auth h ON h.web_authcode = d.domain_auth
    JOIN wbs_mch_agent a ON s.agent_id = a.id 
WHERE
    d.channel_subsidy IS NOT NULL 
ORDER BY
    d.id DESC 
    LIMIT 0,
    15;

SELECT
    d.channel_subsidy,
    d.trade_no,
    d.provider_subsidy,
    s.mch_name,
    s.mid_src,
    d.service,
    d.alleys,
    d.trade_type,
    d.pay_amount,
    d.service_charge,
    d.alleys_charge,
    d.cost_charge,
    d.settlement_charge,
    d.create_time,
    d.settle_status,
    d.out_trade_no,
    d.out_order_no,
    s.domain_auth,
    d.store_id,
    d.deposit_other_account,
    d.out_trade_amount,
    d.out_order_amount,
    t.NAME AS store_name,
    h.web_name,
    a.user_name AS agent_name 
FROM
    wbs_mch_detailed_orders d
    JOIN wbs_mch_seller s ON d.mid = s.id
    JOIN wbs_mch_store t ON d.store_id = t.id
    JOIN wbs_domain_auth h ON h.id = t.domain_id
    JOIN wbs_mch_agent a ON s.agent_id = a.id 
WHERE
    1 = 1 
ORDER BY
    d.id DESC 
    LIMIT 0,
    15;

MySQL查询优化