MySQL 的执行原理
单表访问之索引合并(本质是主键索引的合并)
我们前边说过 MySQL 在一般情况下执行一个查询时最多只会用到单个二级 索引，但存在有特殊情况，在这些特殊情况下也可能在一个查询中使用到多个二 级索引，MySQL 中这种使用到多个索引来完成一次查询的执行方法称之为：索引 合并/index merge，具体的索引合并算法有下边三种。
Intersection 合并(交集合并)
Intersection 翻译过来的意思是交集。这里是说某个查询可以使用多个二级 索引，将从多个二级索引中查询到的结果取交集，比方说下边这个查询：
SELECT * FROM order_exp WHERE order_no = ‘a’ AND expire_time = ‘b’;
假设这个查询使用 Intersection 合并的方式执行的话，那这个过程就是这样 的：
从 idx_order_no 二级索引对应的 B+树中取出 order_no= 'a’的相关记录。
从 idx_insert_time 二级索引对应的 B+树中取出 insert_time= 'b’的相关记录。
二级索引的记录都是由索引列 + 主键构成的，所以我们可以计算出这两个 结果集中 id 值的交集。
按照上一步生成的 id 值列表进行回表操作，也就是从聚簇索引中把指定 id 值的完整用户记录取出来，返回给用户。(这个)也类似MRR: Mutil Range Read , 只不过多范围读取的不是在同一个索引中而是在不同的索引中读取到的主键索引)
为啥不直接使用 idx_order_no 或者 idx_insert_time 只根据某个搜索条件去读 取一个二级索引，然后回表后再过滤另外一个搜索条件呢？这里要分析一下两种 查询执行方式之间需要的成本代价。
只读取一个二级索引的成本：
按照某个搜索条件读取一个二级索引，根据从该二级索引得到的主键值进行 回表操作，然后再过滤其他的搜索条件
读取多个二级索引之后取交集成本：
按照不同的搜索条件分别读取不同的二级索引，将从多个二级索引得到的主 键值取交集，然后进行回表操作。
虽然读取多个二级索引比读取一个二级索引消耗性能，但是大部分情况下读 取二级索引的操作是顺序 I/O，而回表操作是随机 I/O，所以如果只读取一个二级 索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常 少，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更 高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。(减少回表)
MySQL 在某些特定的情况下才可能会使用到 Intersection 索引合并，哪些情 况呢？(有条件使用)
情况一：等值匹配
二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列 都必须等值匹配，不能出现只匹配部分列的情况。
SELECT * FROM order_exp WHERE order_no> ‘a’ AND insert_time = ‘a’ AND order_status = ‘b’ AND expire_time = ‘c’;
SELECT * FROM order_exp WHERE order_no = ‘a’ AND insert_time = ‘a’;
第一个查询是因为对 order_no 进行了范围匹配，第二个查询是因为联合索 引u_idx_day_status中的order_status和expire_time列并没有出现在搜索条件中， 所以这两个查询不能进行 Intersection 索引合并。
情况二：主键列可以是范围匹配
比方说下边这个查询可能用到主键和u_idx_day_status进行Intersection索引 合并的操作：
SELECT * FROM order_exp WHERE id > 100 AND insert_time = ‘a’;
对于 InnoDB 的二级索引来说，记录先是按照索引列进行排序，如果该二级 索引是一个联合索引，那么会按照联合索引中的各个列依次排序。而二级索引的用户记录是由索引列 + 主键构成的，二级索引列的值相同的记录可能会有好多 条，这些索引列的值相同的记录又是按照主键的值进行排序的。(可以)使用索引条件下推)
所以重点来了，之所以在二级索引列都是等值匹配的情况下才可能使用 Intersection 索引合并，是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。***(这句怎么理解: 其实我们联合索引是相对同一个前缀索引有序的, 我们主键也是相对联合索引的最后一个索引有序,主键索引的值也不是随便排序的***)
Intersection 索引合并会把从多个二级索引中查询出的主键值求交集，如果 从各个二级索引中查询的到的结果集本身就是已经按照主键排好序的，那么求交 集的过程就很容易(时间复杂度为O(n))。
按照有序的主键值去回表取记录有个专有名词，叫：Rowid Ordered Retrieval， 简称 ROR。
另外，不仅是多个二级索引之间可以采用 Intersection 索引合并，索引合并 也可以有聚簇索引参加，也就是我们上边写的情况二：在搜索条件中有主键的范 围匹配的情况下也可以使用 Intersection 索引合并索引合并。**为啥主键这就可以 范围匹配了？**还是得回到应用场景里：(因为主键索引值也在二级索引里面)
SELECT * FROM order_exp WHERE id > 100 AND insert_time = ‘a’;
假设这个查询可以采用 Intersection 索引合并，我们理所当然的以为这个查 询会分别按照 id > 100 这个条件从聚簇索引中获取一些记录，在通过 insert_time= 'a’这个条件从 idx_order_no 二级索引中获取一些记录，然后再求交集，其实这样 就把问题复杂化了，没必要从聚簇索引中获取一次记录。别忘了二级索引的记录 中都带有主键值的，所以可以在从 idx_order_no 中获取到的主键值上直接运用条 件 id > 100 过滤就行了，这样多简单。所以涉及主键的搜索条件只不过是为了从 别的二级索引得到的结果集中过滤记录罢了，是不是等值匹配不重要。
当然，上边说的情况一和情况二只是发生 Intersection 索引合并的必要条件， 不是充分条件。也就是说即使情况一、情况二成立，也不一定发生 Intersection 索引合并，这得看优化器的心情。优化器只有在单独根据搜索条件从某个二级索 引中获取的记录数太多，导致回表开销太大，而通过 Intersection 索引合并后需 要回表的记录数大大减少时才会使用 Intersection 索引合并。(最终还是要看成本)
Union 合并(并集合并)
我们在写查询语句时经常想把既符合某个搜索条件的记录取出来，也把符合 另外的某个搜索条件的记录取出来，我们说这些不同的搜索条件之间是 OR 关系。 有时候 OR 关系的不同搜索条件会使用到不同的索引，比方说这样：
SELECT * FROM order_exp WHERE order_no = ‘a’ OR expire_time = ‘b’
Intersection 是交集的意思，这适用于使用不同索引的搜索条件之间使用 AND 连接起来的情况；Union 是并集的意思，适用于使用不同索引的搜索条件之间使 用 OR 连接起来的情况。与 Intersection 索引合并类似，MySQL 在某些特定的情 况下才可能会使用到 Union 索引合并：
情况一：等值匹配
分析同 Intersection 合并
情况二：主键列可以是范围匹配
分析同 Intersection 合并
情况三：使用 Intersection 索引合并的搜索条件
就是搜索条件的某些部分使用 Intersection 索引合并的方式得到的主键集合 和其他方式得到的主键集合取交集，比方说这个查询：
SELECT * FROM order_exp WHERE insert_time = ‘a’ AND order_status = ‘b’ AND expire_time = ‘c’ OR (order_no = ‘a’ AND expire_time = ‘b’);
优化器可能采用这样的方式来执行这个查询：
-
先按照搜索条件 order_no = ‘a’ AND expire_time = 'b’从索引 idx_order_no 和 idx_expire_time 中使用 Intersection 索引合并的方式得到一个主键集合。
-
再按照搜索条件 insert_time = ‘a’ AND order_status = ‘b’ AND expire_time = ‘c’ 从联合索引 u_idx_day_status 中得到另一个主键集合。
-
采用 Union 索引合并的方式把上述两个主键集合取并集，然后进行回表操作， 将结果返回给用户。
当然，查询条件符合了这些情况也不一定就会采用 Union 索引合并，也得看 优化器的心情。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数 比较少，通过 Union 索引合并后进行访问的代价比全表扫描更小时才会使用 Union 索引合并。
Sort-Union 合并(对union的一种兼容处理)
Union 索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹 配的条件下才可能被用到，比方说下边这个查询就无法使用到 Union 索引合并：
SELECT * FROM order_exp WHERE order_no< ‘a’ OR expire_time> ‘z’
这是因为根据 order_no< 'a’从 idx_order_no 索引中获取的二级索引记录的主 键值不是排好序的，根据 expire_time> 'z’从 idx_expire_time 索引中获取的二级索 引记录的主键值也不是排好序的，但是 order_no< 'a’和 expire_time> ‘z’'这两个条 件又特别让我们动心，所以我们可以这样：
-
先根据 order_no< 'a’条件从 idx_order_no 二级索引中获取记录，并按照记录 的主键值进行排序
-
再根据 expire_time> 'z’条件从 idx_expire_time 二级索引中获取记录，并按照 记录的主键值进行排序
-
因为上述的两个二级索引主键值都是排好序的，剩下的操作和 Union 索引合 并方式就一样了。
上述这种先按照二级索引记录的主键值进行排序，之后按照 Union 索引合并 方式执行的方式称之为 Sort-Union 索引合并，很显然，这种 Sort-Union 索引合并 比单纯的 Union 索引合并多了一步对二级索引记录的主键值排序的过程。
联合索引替代 Intersection 索引合并(如果追求性能你就自己去建立联合索引)
SELECT * FROM order_exp WHERE order_no= ‘a’ And expire_time= ‘z’;
这个查询之所以可能使用 Intersection 索引合并的方式执行，还不是因为 idx_order_no 和 idx_expire_time 是两个单独的 B+树索引，要是把这两个列搞一个 联合索引，那直接使用这个联合索引就把事情搞定了，何必用啥索引合并呢，就 像这样：
ALTER TABLE order_exp drop index idx_order_no, idx_expire_time, ## 删除不需要或冗余的索引是一个很重要的素质
add index idx_order_no_expire_time(order_no, expire_time);
这样我们把 idx_order_no, idx_expire_time 都干掉，再添加一个联合索引 idx_order_no_expire_time，使用这个联合索引进行查询简直是又快又好，既不用 多读一棵 B+树，也不用合并结果
连接查询
搞数据库一个避不开的概念就是 Join，翻译成中文就是连接。使用的时候常常陷入下边两种误区：
误区一：业务至上，管他三七二十一，再复杂的查询也用在一个连接语句中 搞定。
误区二：敬而远之，上次慢查询就是因为使用了连接导致的，以后再也不敢 用了。
所以我们来学习一下连接的原理，才能在工作中用好 SQL 连接。
连接简介
连接的本质
为了方便讲述，我们建立两个简单的演示表并给它们写入数据：
CREATE TABLE e1 (m1 int, n1 char(1));
CREATE TABLE e2 (m2 int, n2 char(1));
INSERT INTO e1 VALUES(1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO e2 VALUES(2, 'b'), (3, 'c'), (4, 'd');
连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。
所以我们把 e1 和 e2 两个表连接起来的过程如下图所示：
这个过程看起来就是把e1表的记录和e2的记录连起来组成新的更大的记录， 所以这个查询过程称之为连接查询。连接查询的结果集中包含一个表中的每一条 记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为 笛卡尔积。因为表 e1 中有 3 条记录，表 e2 中也有 3 条记录，所以这两个表连接 之后的笛卡尔积就有 3×3=9 行记录。
在 MySQL 中，连接查询的语法很随意，只要在 FROM 语句后边跟多个表名 就好了，比如我们把 e1 表和 e2 表连接起来的查询语句可以写成这样：
SELECT * FROM e1, e2;
连接过程简介
我们可以连接任意数量张表，但是如果没有任何限制条件的话，这些表连接 起来产生的笛卡尔积可能是非常巨大的。比方说 3 个 100 行记录的表连接起来产 生的笛卡尔积就有 100×100×100=1000000 行数据！所以在连接的时候过滤掉特 定记录组合是有必要的，在连接查询中的过滤条件可以分成两种，比方说下边这 个查询语句：
SELECT * FROM e1, e2 WHERE e1.m1 > 1 AND e1.m1 = e2.m2 AND e2.n2 < ‘d’;
涉及单表的条件
比如 e1.m1 > 1 是只针对 e1 表的过滤条件，e2.n2 < 'd’是只针对 e2 表的过滤 条件。
涉及两表的条件
比如类似 e1.m1 = e2.m2、e1.n1 > e2.n2 等，这些条件中涉及到了两个表。
看一下携带过滤条件的连接查询的大致执行过程在这个查询中我们指明了 这三个过滤条件：
e1.m1 > 1
e1.m1 = e2.m2
e2.n2 < ‘d’
那么这个连接查询的大致执行过程如下：
**步骤一：首先确定第一个需要查询的表，这个表称之为驱动表。单表中执行 查询语句只需要选取代价最小的那种访问方法去执行单表查询语句就好了（就是 说从 const、ref、ref_or_null、range、index、all 等等这些执行方法中选取代价最 小的去执行查询）。
**此处假设使用 e1 作为驱动表，那么就需要到 e1 表中找满足 e1.m1 > 1 的记 录，因为表中的数据太少，我们也没在表上建立二级索引，所以此处查询 e1 表 的访问方法就设定为 all，也就是采用全表扫描的方式执行单表查询。
很明显，e1 表中符合 e1.m1 > 1 的记录有两条。
**步骤二：**针对上一步骤中从驱动表产生的结果集中的每一条记录，分别需要 到 e2 表中查找匹配的记录，所谓匹配的记录，指的是符合过滤条件的记录。因为是根据 e1 表中的记录去找 e2 表中的记录，所以 e2 表也可以被称之为被驱动 表。上一步骤从驱动表中得到了 2 条记录，所以需要查询 2 次 e2 表。此时涉及 两个表的列的过滤条件 e1.m1 = e2.m2 就派上用场了：
当 e1.m1 = 2 时，过滤条件 e1.m1 = e2.m2 就相当于 e2.m2 = 2，所以此时 e2 表相当于有了 e2.m2 = 2、e2.n2 < 'd’这两个过滤条件，然后到 e2 表中执行单表查 询。
当 e1.m1 = 3 时，过滤条件 e1.m1 = e2.m2 就相当于 e2.m2 = 3，所以此时 e2 表相当于有了 e2.m2 = 3、e2.n2 < 'd’这两个过滤条件，然后到 e2 表中执行单表查 询。
所以整个连接查询的执行过程就如下图所示：
也就是说整个连接查询最后的结果只有两条符合过滤条件的记录：
mysql> SELECT * FROM e1, e2 WHERE e1.m1 > 1 AND e1.m1 = e2.m2 AND e2.n2 < 'd';
+------+------+------+------+
| m1 | n1 | m2 | n2 |
+------+------+------+------+
| 2 | b | 2 | b |
| 3 | c | 3 | c |
+------+------+------+------+
2 rows in set (0.00 sec)
从上边两个步骤可以看出来，这个两表连接查询共需要查询 1 次 e1 表，2 次 e2 表。当然这是在特定的过滤条件下的结果，如果我们把 e1.m1 > 1 这个条件 去掉，那么从 e1 表中查出的记录就有 3 条，就需要查询 3 次 e2 表了。也就是说 在两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次。
内连接和外连接
为了大家更好理解后边内容 ，我们创建两个有现实意义的表 ，并插入一些数 据 ：
CREATE TABLE student (
number INT NOT NULL AUTO_ INCREMENT COMMENT '学号',
name VARCHAR(5) COMMENT '姓名',
major VARCHAR(30) COMMENT '专业',
PRIMARY KEY (number)
) Engine=InnoDB CHARSET=utf8 COMMENT '客户信息表';
CREATE TABLE score (
number INT COMMENT '学号',
subject VARCHAR(30) COMMENT '科目',
score TINYINT COMMENT '成绩',
PRIMARY KEY (number, subject)
) Engine=InnoDB CHARSET=utf8 COMMENT '客户成绩表';
mysql> SELECT * FROM student;
+----------+-------+-----------------+
| number | name | major |
+----------+-------+-----------------+
| 20200901 | aaa | 网络工程 |
| 20200902 | bbb | 计算机科学 |
| 20200903 | ccc | 计算机科学 |
| 20200904 | ddd | 软件工程 |
+----------+-------+-----------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM score;
+----------+-----------------------+-------+
| number | subject | score |
+----------+-----------------------+-------+
| 20200901 | 数据结构和算法 | 88 |
| 20200901 | 网络通信原理 | 90 |
| 20200902 | 数据结构和算法 | 95 |
| 20200902 | 网络通信原理 | 89 |
| 20200903 | 离散数学 | 70 |
+----------+-----------------------+-------+
5 rows in set (0.01 sec)
现在我们想把每个学生的考试成绩都查询出来就需要进行两表连接了（因为 score 中没有姓名信息 ，所以不能单纯只查询 score 表）。连接过程就是从 student 表中取出记录 ，在 score 表中查找 number 相同的成绩记录 ，所以过滤条件就是 student . number = socre . number ，整个查询语句就是这样 ：
mysql> SELECT s1 . number, s1 . name, s2 .subject, s2 .score FROM student AS s1, score AS s2 WHERE s1 . number = s2 . number;
+----------+-------+-----------------------+-------+
| number | name | subject | score |
+----------+-------+-----------------------+-------+
| 20200901 | aaa | 数据结构和算法 | 88 |
| 20200901 | aaa | 网络通信原理 | 90 |
| 20200902 | bbb | 数据结构和算法 | 95 |
| 20200902 | bbb | 网络通信原理 | 89 |
| 20200903 | ccc | 离散数学 | 70 |
+----------+-------+-----------------------+-------+
5 rows in set (0.00 sec)
从上述查询结果中我们可以看到 ，各个同学对应的各科成绩就都被查出来了 ，可 是有个问题 ， ddd 同学 ，也就是学号为 20200904 的同学因为某些原因没有参加 考试 ，所以在 score 表中没有对应的成绩记录
如果老师想查看所有同学的考试成绩 ， 即使是缺考的同学也应该展示出来 ， 但是到目前为止我们介绍的连接查询是无法完成这样的需求的 。我们稍微思考一 下这个需求 ，其本质是想 ：驱动表中的记录即使在被驱动表中没有匹配的记录 ， 也仍然需要加入到结果集 。为了解决这个问题 ，就有了内连接和外连接的概念
如果老师想查看所有同学的考试成绩 ， 即使是缺考的同学也应该展示出来 ， 但是到目前为止我们介绍的连接查询是无法完成这样的需求的 。我们稍微思考一 下这个需求 ，其本质是想 ：驱动表中的记录即使在被驱动表中没有匹配的记录 ， 也仍然需要加入到结果集 。为了解决这个问题 ，就有了内连接和外连接的概念
左外连接
右外连接
太过基础,连接省略
左（外）连接的语法
内连接的语法
略
连接的本质就是把各个连接表中的记录都取出来依次匹配的 组合加入结果集并返回给用户。不论哪个表作为驱动表，两表连接产生的笛卡尔 积肯定是一样的。而对于内连接来说，由于凡是不符合 ON 子句或 WHERE 子句 中的条件的记录都会被过滤掉，其实也就相当于从两表连接的笛卡尔积中把不符 合过滤条件的记录给踢出去，所以对于内连接来说，驱动表和被驱动表是可以互 换的，并不会影响最后的查询结果。
但是对于外连接来说，由于驱动表中的记录即使在被驱动表中找不到符合 ON 子句条件的记录时也要将其加入到结果集，所以此时驱动表和被驱动表的关 系就很重要了，也就是说左外连接和右外连接的驱动表和被驱动表不能轻易互换。
MySQL 对连接的执行
嵌套循环连接（Nested-Loop Join）
我们前边说过，对于两表连接来说，驱动表只会被访问一遍，但被驱动表却 要被访问到好多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中的 记录条数。
对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定 的，也就是说左（外）连接的驱动表就是左边的那个表，右（外）连接的驱动表 就是右边的那个表。
如果有 3 个表进行连接的话，那么首先两表连接得到的结果集就像是新的驱 动表，然后第三个表就成为了被驱动表，可以用伪代码表示一下这个过程就是这 样：
for each row in e1 { #此处表示遍历满足对 e1 单表查询结果集中的每一条 记录，N 条
for each row in e2 { #此处表示对于某条 e1 表的记录来说，遍历满足 对 e2 单表查询结果集中的每一条记录，M 条
for each row in t3 { #此处表示对于某条 e1 和 e2 表的记录组 合来说，对 t3 表进行单表查询，L 条
if row satisfies join conditions, send to client
}
}
}
这个过程就像是一个嵌套的循环，所以这种驱动表只访问一次，但被驱动表 却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录 条数的连接执行方式称之为嵌套循环连接（Nested-Loop Join），这是最简单， 也是最笨拙的一种连接查询算法，时间复杂度是 O（N*M*L）。
使用索引加快连接速度
我们知道在嵌套循环连接的步骤 2 中可能需要访问多次被驱动表，如果访问 被驱动表的方式都是全表扫描的话，那酸爽不敢想象！
但是查询 e2 表其实就相当于一次单表查询，我们可以利用索引来加快查询 速度。
SELECT * FROM e1, e2 WHERE e1.m1 > 1 AND e1.m1 = e2.m2 AND e2.n2 < 'd';
可以看到，原来的 e1.m1 = e2.m2 这个涉及两个表的过滤条件在针对 e2 表做 查询时关于 e1 表的条件就已经确定了，所以我们只需要单单优化对 e2 表的查询 了，上述两个对 e2 表的查询语句中利用到的列是 m2 和 n2 列，我们可以：
在 m2 列上建立索引，因为对 m2 列的条件是等值查找，比如 e2.m2 = 2、e2.m2 = 3 等，所以可能使用到 ref 的访问方法，假设使用 ref 的访问方法去执行对 e2 表的查询的话，需要回表之后再判断 e2.n2 < d 这个条件是否成立。
这里有一个比较特殊的情况，就是假设 m2 列是 e2 表的主键或者唯一二级 索引列，那么使用 e2.m2 = 常数值这样的条件从 e2 表中查找记录的过程的代价 就是常数级别的。我们知道在单表中使用主键值或者唯一二级索引列的值进行等 值查找的方式称之为 const，而 MySQL 把在连接查询中对被驱动表使用主键值或 者唯一二级索引列的值进行等值查找的查询执行方式称之为：eq_ref。
在 n2 列上建立索引，涉及到的条件是 e2.n2 < ‘d’，可能用到 range 的访问方 法，假设使用 range 的访问方法对 e2 表的查询的话，需要回表之后再判断在 m2 列上的条件是否成立。
假设 m2 和 n2 列上都存在索引的话，那么就需要从这两个里边儿挑一个代 价更低的去执行对 e2 表的查询。当然，建立了索引不一定使用索引，只有在二 级索引 + 回表的代价比全表扫描的代价更低时才会使用索引。
另外，有时候连接查询的查询列表和过滤条件中可能只涉及被驱动表的部分 列，而这些列都是某个索引的一部分，这种情况下即使不能使用 eq_ref、ref、 ref_or_null 或者 range 这些访问方法执行对被驱动表的查询的话，也可以使用索 引扫描，也就是 index(索引覆盖)的访问方法来查询被驱动表。
基于块的嵌套循环连接（Block Nested-Loop Join）
扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中 比较匹配条件是否满足。ssssssss
现实生活中的表成千上万条记录都是少的，几百万、几千万甚至几亿条记录 的表到处都是。内存里可能并不能完全存放的下表中所有的记录，所以在扫描表 前边记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存 不足，所以需要把前边的记录从内存中释放掉。
而采用嵌套循环连接算法的两表连接过程中，被驱动表可是要被访问好多次 的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于 要从磁盘上读好几次这个表，这个 I/O 代价就非常大了，所以我们得想办法：尽 量减少访问被驱动表的次数。
当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加 载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之 后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把 被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录， 就得把被驱动表从磁盘上加载到内存中多少次。
所以我们可不可以在把被驱动表的记录加载到内存的时候，一次性和多条驱 动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。 所以 MySQL 提出了一个 join buffer 的概念，join buffer 就是执行连接查询前申请 的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个 join buffer 中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和 join buffer 中的多 条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著 减少被驱动表的 I/O 代价。使用 join buffer 的过程如下图所示：
最最好的情况是 join buffer 足够大，能容纳驱动表结果集中的所有记录。
这种加入了 join buffer 的嵌套循环连接算法称之为基于块的嵌套连接（Block Nested-Loop Join）算法。
这个 join buffer 的大小是可以通过启动参数或者系统变量 join_buffer_size 进 行配置，默认大小为 262144 字节（也就是 256KB），最小可以设置为 128 字节。
mysql> show variables like 'join_buffer_size' ;
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.10 sec)
当然，对于优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引， 如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大 join_buffer_size 的值来对连接查询进行优化。
另外需要注意的是，驱动表的记录并不是所有列都会被放到 join buffer 中， 只有查询列表中的列和过滤条件中的列才会被放到 join buffer 中，所以再次提醒 我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了， 这样还可以在 join buffer 中放置更多的记录。(所以使用*不仅会导致cpu,内存,网络不必要的开销,还会导致join buffer可放入的数据量更少,导致缓存使用效率降低)
MySQL 的查询成本
什么是成本
MySQL 执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者 说代价最低的那种方案去真正的执行查询。不过我们之前对成本的描述是非常模 糊的，其实在 MySQL 中一条查询语句的执行成本是由下边这两个方面组成的：
I/O 成本
我们的表经常使用的 MyISAM、InnoDB 存储引擎都是将数据和索引都存储到 磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然 后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为 I/O 成本。
CPU 成本
读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作 损耗的时间称之为 CPU 成本。
对于 InnoDB 存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL 规定读取一个页面花费的成本默认是 1.0，读取以及检测一条记录是否符合搜索 条件的成本默认是 0.2。1.0、0.2 这些数字称之为成本常数，这两个成本常数我 们最常用到，当然还有其他的成本常数
注意，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是 0.2。
单表查询的成本
基于成本的优化步骤实战
在一条单表查询语句真正执行之前，MySQL 的查询优化器会找出执行该语句 所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是 所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询，这个过程 总结一下就是这样：
1、根据搜索条件，找出所有可能使用的索引
2、计算全表扫描的代价
3、计算使用不同索引执行查询的代价
4、对比各种执行方案的代价，找出成本最低的那一个
下边我们就以一个实例来分析一下这些步骤，单表查询语句如下：
mysql> show create table order_exp\G
*************************** 1. row ***************************
Table: order_exp
Create Table: CREATE TABLE `order_exp` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
`order_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的编号',
`order_note` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的说明',
`insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
`expire_duration` bigint NOT NULL COMMENT '订单的过期时长，单位秒',
`expire_time` datetime NOT NULL COMMENT '订单的过期时间',
`order_status` smallint NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
KEY `idx_order_no` (`order_no`) USING BTREE,
KEY `idx_expire_time` (`expire_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10819 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
SELECT * FROM
order_exp
WHERE
order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S')
AND expire_time> '2021-03-22 18:28:28'
AND expire_time<= '2021-03-22 18:35:09'
AND insert_time> expire_time
AND order_note LIKE '%7 排 1%'
AND order_status = 0;
-
根据搜索条件，找出所有可能使用的索引
我们前边说过，对于 B+树索引来说，只要索引列和常数使用=、<=>、IN、 NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写 成<>）或者 LIKE 操作符连接起来，就可以产生一个所谓的范围区间（LIKE 匹配字 符串前缀也行），MySQL 把一个查询中可能使用到的索引称之为 possible keys。
我们分析一下上边查询中涉及到的几个搜索条件：
order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’) ，这个搜索条件可以使用二 级索引 idx_order_no。
expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’， 这个搜索条件可以使用二级索引
idx_expire_time。
insert_time> expire_time，这个搜索条件的索引列由于没有和常数比较，所 以并不能使用到索引。
order_note LIKE ‘%hello%’，order_note 即使有索引，但是通过 LIKE 操作符和 以通配符开头的字符串做比较，不可以适用索引。
order_status = 0，由于该列上只有联合索引，而且不符合最左前缀原则，所 以不会用到索引。
综上所述，上边的查询语句可能用到的索引，也就是 possible keys 只有 idx_order_no,idx_expire_time。
-
计算全表扫描的代价
对于 InnoDB 存储引擎来说，全表扫描的意思就是把聚簇索引中的记录都依 次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集，所以需 要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。由 于查询成本=I/O 成本+CPU 成本，所以计算全表扫描的代价需要两个信息：
聚簇索引占用的页面数
该表中的记录数
这两个信息从哪来呢？MySQL 为每个表维护了一系列的统计信息，关于这些 统计信息是如何收集起来的我们放在后边再说，现在看看怎么查看这些统计信息。
MySQL 给我们提供了 SHOW TABLE STATUS 语句来查看表的统计信息，如果 要看指定的某个表的统计信息，在该语句后加对应的 LIKE 语句就好了，比方说 我们要查看 order_exp 这个表的统计信息可以这么写：
mysql> SHOW TABLE STATUS LIKE 'order_exp'\G *************************** 1. row *************************** Name: order_exp Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 10612 ##总行数 Avg_row_length: 149 Data_length: 1589248 ## 数据大小 Max_data_length: 0 Index_length: 1212416 Data_free: 4194304 Auto_increment: 10819 Create_time: 2021-12-10 19:55:37 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: row_format=DYNAMIC Comment: 1 row in set (0.01 sec)出现了很多统计选项，但我们目前只需要两个：
Rows
本选项表示表中的记录条数。对于使用 MyISAM 存储引擎的表来说，该值是 准确的，对于使用 InnoDB 存储引擎的表来说，该值是一个估计值。从查询结果 我们也可以看出来，由于我们的 order_exp 表是使用 InnoDB 存储引擎的，所以 虽然实际上表中有 10567 条记录，但是 SHOW TABLE STATUS 显示的 Rows 值只有 10612条记录。(这个估值可能比实际值大也可能小,相等概率不知道有没有)
Data_length
本选项表示表占用的存储空间字节数。使用 MyISAM 存储引擎的表来说，该 值就是数据文件的大小，对于使用 InnoDB 存储引擎的表来说，该值就相当于聚 簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：
Data_length = 聚簇索引的页面数量 x 每个页面的大小
我们的 order_exp 使用默认 16KB 的页面大小，而上边查询结果显示 Data_length 的值是 1589248，所以我们可以反向来推导出聚簇索引的页面数量：
聚簇索引的页面数量 = 1589248 ÷ 16 ÷ 1024 = 97
我们现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值，所 以就可以计算全表扫描成本了
I/O 成本
97 * 1.0 + 1.1 = 98.1 ,97 指的是聚簇索引占用的页面数，1.0 指的是加载一个页面的成本常数，后 边的 1.1 是一个微调值。
TIPS*：*MySQL 在真实计算成本时会进行一些微调，这些微调的值是直接硬编
码到代码里的，没有注释而且这些微调的值十分的小，并不影响我们分析。
CPU 成本
10612 * 0.2 + 1.0 = 2123.4 ,10350 指的是统计数据中表的记录数，对于 InnoDB 存储引擎来说是一个估 计值，0.2 指的是访问一条记录所需的成本常数，后边的 1.0 是一个微调值。
总成本：
98.1 + 2123.4 = 2220.5
TIPS：我们前边说过表中的记录其实都存储在聚簇索引对应 B+树的叶子节点 中，所以只要我们通过根节点获得了最左边的叶子节点，就可以沿着叶子节点组 成的双向链表把所有记录都查看一遍。
也就是说全表扫描这个过程其实有的 B+树非叶子节点是不需要访问的，但 是 MySQL 在计算全表扫描成本时直接使用聚簇索引占用的页面数作为计算 I/O 成本的依据，是不区分非叶子节点和叶子节点的。
-
计算使用不同索引执行查询的代价
从第 1 步分析我们得到，上述查询可能使用到 idx_order_no，idx_expire_time 这两个索引，我们需要分别分析单独使用这些索引执行查询的成本，最后还要分 析是否可能使用到索引合并。这里需要提一点的是，MySQL 查询优化器先分析使 用唯一二级索引的成本，再分析使用普通索引的成本，我们这里两个索引都是普 通索引，先算哪个都可以。我们也先分析 idx_expire_time 的成本，然后再看使用 idx_order_no 的成本。
使用 idx_expire_time 执行查询的成本分析
idx_expire_time 对应的搜索条件是：expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’ ，也就是说对应的范围区间就是： (‘2021-03-22 18:28:28’ , ‘2021-03-22 18:35:09’ )。
使用 idx_expire_time 搜索会使用用二级索引 + 回表方式的查询，MySQL 计 算这种查询的成本依赖两个方面的数据：
1、范围区间数量
不论某个范围区间的二级索引到底占用了多少页面，查询优化器认为读取索 引的一个范围区间的 I/O 成本和读取一个页面是相同的(全部扫描更具数据大小计算的)。本例中使用 idx_expire_time 的范围区间只有一个，所以相当于访问这个范围区间的二级索引 付出的 I/O 成本就是：1 x 1.0 = 1.0
2、需要回表的记录数
优化器需要计算二级索引的某个范围区间到底包含多少条记录，对于本例来 说就是要计算 idx_expire_time 在(‘2021-03-22 18:28:28’ ，‘2021-03-22 18:35:09’) 这个范围区间中包含多少二级索引记录，计算过程是这样的：
步骤 1：先根据 expire_time> ‘2021-03-22 18:28:28’这个条件访问一下 idx_expire_time 对应的 B+树索引，找到满足 expire_time> ‘2021-03-22 18:28:28’ 这个条件的第一条记录，我们把这条记录称之为区间最左记录。我们前头说过在 B+数树中定位一条记录的过程是很快的，是常数级别的，所以这个过程的性能消 耗是可以忽略不计的。
步骤 2：然后再根据 expire_time<= ‘2021-03-22 18:35:09’这个条件继续从 idx_expire_time 对应的 B+树索引中找出最后一条满足这个条件的记录，我们把 这条记录称之为区间最右记录，这个过程的性能消耗也可以忽略不计的。
步骤 3：如果区间最左记录和区间最右记录相隔不太远（在 MySQL 5.7 这个 版本里，只要相隔不大于 10 个页面即可），那就可以精确统计出满足 expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’条件的二级索引记 录条数。否则只沿着区间最左记录向右读 10 个页面，计算平均每个页面中包含 多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量 就可以了。那么问题又来了，**怎么估计区间最左记录和区间最右记录之间有多少 个页面呢？**解决这个问题还得回到 B+树索引的结构中来。
我们假设区间最左记录在页 b 中，区间最右记录在页 c 中，那么我们想计算 区间最左记录和区间最右记录之间的页面数量就相当于计算页b和页 c 之间有多 少页面，而它们父节点中记录的每一条目录项记录都对应一个数据页，所以计算 页 b 和页 c 之间有多少页面就相当于计算它们父节点（也就是页 a）中对应的目 录项记录之间隔着几条记录。在一个页面中统计两条记录之间有几条记录的成本 就很小了。
不过还有问题，如果页 b 和页 c 之间的页面实在太多，以至于页 b 和页 c 对 应的目录项记录都不在一个父页面中怎么办？既然是树，那就继续递归，之前我 们说过一个 B+树有 4 层高已经很了不得了，所以这个统计过程也不是很耗费性 能。
知道了如何统计二级索引某个范围区间的记录数之后，就需要回到现实问题 中来，MySQL 根据上述算法测得 idx_expire_time 在区间(‘2021-03-22 18:28:28’ ， ‘2021-03-22 18:35:09’)之间大约有 39 条记录
mysql> explain SELECT * FROM order_exp WHERE expire_time> '2021-03-22 18:28:28' AND expire_time<= '2021-03-22 18:35:09'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: order_exp partitions: NULL type: range possible_keys: idx_expire_time key: idx_expire_time key_len: 5 ref: NULL rows: 39 ## 直接使用执行计划帮我们算,我们只是为了学习原理 filtered: 100.00 Extra: Using index condition 1 row in set, 1 warning (0.00 sec) 读取这 39 条二级索引记录需要付出的 CPU 成本就是：
39 * 0.2 + 0.01 = 7.81 , 其中 39 是需要读取的二级索引记录条数，0.2 是读取一条记录成本常数，0.01 是微调。
在通过二级索引获取到记录之后，还需要干两件事儿：
1、根据这些记录里的主键值到聚簇索引中做回表操作
MySQL 评估回表操作的 I/O 成本依旧很简单粗暴，他们认为每次回表操作都 相当于访问一个页面，也就是说二级索引范围区间有多少记录，就需要进行多少 次回表操作，也就是需要进行多少次页面 I/O。我们上边统计了使用 idx_expire_time 二级索引执行查询时，预计有 39 条二级索引记录需要进行回表 操作，所以回表操作带来的 I/O 成本就是：
39 x 1.0 = 39 .0 , 其中 39 是预计的二级索引记录数，1.0 是一个页面的 I/O 成本常数。
2、回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立
回表操作的本质就是通过二级索引记录的主键值到聚簇索引中找到完整的 用户记录，然后再检测除 expire_time> ‘2021-03-22 18:28:28’ AND expire_time< '2021-03-22 18:35:09’这个搜索条件以外的搜索条件是否成立。
因为我们通过范围区间获取到二级索引记录共 39 条，也就对应着聚簇索引 中 39 条完整的用户记录，读取并检测这些完整的用户记录是否符合其余的搜索 条件的 CPU 成本如下：
39 x 0.2 =7.8, 其中 39 是待检测记录的条数，0.2 是检测一条记录是否符合给定的搜索条 件的成本常数。(和全表比较时候这部分成本会去掉)
所以本例中使用 idx_expire_time 执行查询的成本就如下所示：
***I/O 成本：***1.0 + 39 x 1.0 = 40 .0 (范围区间的数量 + 预估的二级索引记录条数)
***CPU 成本：***39 x 0.2 + 0.01 + 39 x 0.2 = 15.61 （读取二级索引记录的成本 + 读取并检测 回表后聚簇索引记录的成本）
综上所述，使用 idx_expire_time 执行查询的总成本就是：
40 .0 + 15.61 = 55.61
使用 idx_order_no 执行查询的成本分析
idx_order_no 对应的搜索条件是：order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’)，也就是说相当于 3 个单点区间。
具体过程可以自己复习计算下
我的数据获得值是 84.21
是否有可能使用索引合并（Index Merge）
本例中有关 order_no 和 expire_time 的搜索条件是使用 AND 连接起来的，而 对于 idx_order_no 和 idx_expire_time 都是范围查询，也就是说查找到的二级索引 记录并不是按照主键值进行排序的，并不满足使用 Intersection 索引合并的条件， 所以并不会使用索引合并。而且 MySQL 查询优化器计算索引合并成本的算法也 比较麻烦
-
对比各种方案，找出成本最低的那一个
全表扫描的成本：2148.7
使用 idx_expire_time 的成本：55.61
使用 idx_order_no 的成本：84.21
很显然，使用 idx_expire_time 的成本最低，所以当然选择 idx_expire_time 来执行查询。
mysql> explain SELECT * FROM order_exp WHERE order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S') AND expire_time> '2021-03-22 18:28:28' AND expire_time<= '2021-03-22 18:35:09' AND insert_time> expire_time AND order_note LIKE '%7 排 1%' AND order_status = 0\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: order_exp partitions: NULL type: range possible_keys: idx_order_no,idx_expire_time key: idx_expire_time ## 使用这个索引 key_len: 5 ref: NULL rows: 39 filtered: 0.13 Extra: Using index condition; Using where 1 row in set, 1 warning (0.00 sec) 请注意：1、MySQL 的源码中对成本的计算实际要更复杂，但是基本思想和 算法是没错的。
2、在 MySQL 的实际计算中，在和全文扫描比较成本时，使用索引的成本会去除读取并检测回表后聚簇索引记录的成本，也就是说，我们通过 MySQL 看到 的成本将会是：idx_expire_time 为 47.81(55.61-7.8)，idx_order_no 为 72.61(84.21-11.6)。但是 MySQL 比较完成本后，会再计算一次使用索引的成本，此时就会加上去除读取并检测回表后聚簇索引记录的成本，也就是我们计算出来 的值。
基于索引统计数据的成本计算
index dive
有时候使用索引执行查询时会有许多单点区间，比如使用 IN 语句就很容易 产生非常多的单点区间，比如下边这个查询（下边查询语句中的…表示还有很多 参数）：
SELECT * FROM order_exp WHERE order_no IN ('aa1', 'aa2', 'aa3', ... , 'zzz');
很显然，这个查询可能使用到的索引就是 idx_order_no，由于这个索引并不 是唯一二级索引，所以并不能确定一个单点区间对应的二级索引记录的条数有多 少，需要我们去计算。就是先获取索引对应的 B+树的区间最左记录和区间最右 记录，然后再计算这两条记录之间有多少记录（记录条数少的时候可以做到精确 计算，多的时候只能估算）。MySQL 把这种通过直接访问索引对应的 B+树来计 算某个范围区间对应的索引记录条数的方式称之为 index dive。
有零星几个单点区间的话，使用 index dive 的方式去计算这些单点区间对应 的记录数也不是什么问题，如果 IN 语句里 20000 个参数怎么办？
这就意味着 MySQL 的查询优化器为了计算这些单点区间对应的索引记录条 数，要进行 20000 次 index dive 操作，这性能损耗就很大，搞不好计算这些单点 区间对应的索引记录条数的成本比直接全表扫描的成本都大了。MySQL 考虑到了 这种情况，所以提供了一个系统变量 eq_range_index_dive_limit，我们看一下在 MySQL 5.7.21 中这个系统变量的默认值：
mysql> show variables like '%dive%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 200 |
+---------------------------+-------+
1 row in set (0.00 sec)
也就是说如果我们的 IN 语句中的参数个数小于 200 个的话，将使用 index dive 的方式计算各个单点区间对应的记录条数，如果大于或等于 200 个的话，可 就不能使用 index dive 了，要使用所谓的索引统计数据来进行估算。怎么个估算 法？
像会为每个表维护一份统计数据一样，MySQL 也会为表中的每一个索引维护 一份统计数据，查看某个表中索引的统计数据可以使用 SHOW INDEX FROM 表名 的语法，比如我们查看一下 order_exp 的各个索引的统计数据可以这么写：
mysql> show index from order_exp\G
*************************** 1. row ***************************
Table: order_exp ## 索引所属表的名称
Non_unique: 0 ## 索引列的值是否是唯一的，聚簇索引和唯一二级索引的该列 值为 0，普通二级索引该列值为 1。
Key_name: PRIMARY ## 索引的名称。
Seq_in_index: 1 ## 索引列在索引中的位置，从 1 开始计数。比如对于联合索引 u_idx_day_status，来说，`insert_time`, `order_status`, `expire_time`对应的位置分 别是 1、2、3。
Column_name: id ## 索引列的名称
Collation: A ## 索引列中的值是按照何种排序方式存放的，值为 A 时代表升序存 放，为 NULL 时代表降序存放。
Cardinality: 10612 ## 索引列中不重复值的数量。
Sub_part: NULL ## 对于存储字符串或者字节串的列来说，有时候我们只想对这些串 的前 n 个字符或字节建立索引，这个属性表示的就是那个 n 值。如果对完整的列 建立索引的话，该属性的值就是 NULL。
Packed: NULL ## 索引列如何被压缩，NULL 值表示未被压缩。这个属性我们暂时不了 解，可以先忽略掉。
Null: ## 该索引列是否允许存储 NULL 值。
Index_type: BTREE ## 使用索引的类型，我们最常见的就是 BTREE，其实也就是 B+树索 引。
Comment: ## 索引列注释信息。
Index_comment: ## 索引注释信息。
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Cardinality 属性，Cardinality 直译过来就是基数的意思，表示索引列中不重 复值的个数。比如对于一个一万行记录的表来说，某个索引列的 Cardinality 属性 是 10000，那意味着该列中没有重复的值，如果 Cardinality 属性是 1 的话，就意 味着该列的值全部是重复的。不过需要注意的是，对于 InnoDB 存储引擎来说， 使用 SHOW INDEX 语句展示出来的某个索引列的 Cardinality 属性是一个估计值， 并不是精确的。
前边说道，当 IN 语句中的参数个数大于或等于系统变量 eq_range_index_dive_limit 的值的话，就不会使用 index dive 的方式计算各个单点 区间对应的索引记录条数，而是使用索引统计数据，这里所指的索引统计数据指 的是这两个值：
使用 SHOW TABLE STATUS 展示出的 Rows 值，也就是一个表中有多少条记录。
使用 SHOW INDEX 语句展示出的 Cardinality 属性。
结合上一个 Rows 统计数据，我们可以针对索引列，计算出平均一个值重复 多少次。
一个值的重复次数 ≈ Rows ÷ Cardinality
以 order_exp 表的 idx_order_no 索引为例，它的 Rows 值是 10350，它对应 的 Cardinality 值是 10220，所以我们可以计算 order_no 列平均单个值的重复次数 就是：
10350÷ 10220≈ 1.012（条）
此时再看上边那条查询语句：
SELECT * FROM order_exp WHERE order_no IN (‘aa1’, ‘aa2’, ‘aa3’, … , ‘zzz’);
假设 IN 语句中有 20000 个参数的话，就直接使用统计数据来估算这些参数 需要单点区间对应的记录条数了，每个参数大约对应 1.012 条记录，所以总共需 要回表的记录数就是：
使用统计数据来计算单点区间对应的索引记录条数比 index dive 的方式简单， 但是它的致命弱点就是：不精确！。使用统计数据算出来的查询成本与实际所需 的成本可能相差非常大。
大家需要注意一下，在 MySQL 5.7.3 以及之前的版本中，eq_range_index_dive_limit 的默认值为 10，**之后的版本默认值为 200。**所以如果 大家采用的是 5.7.3 以及之前的版本的话，很容易采用索引统计数据而不是 index dive 的方式来计算查询成本。当你的查询中使用到了 IN 查询，但是却实际没有 用到索引，就应该考虑一下是不是由于 eq_range_index_dive_limit 值太小导致的。
再深入 Explain
EXPLAIN 输出成本
前面我们已经对 MySQL 查询优化器如何计算成本有了比较深刻的了解。但 是 EXPLAIN 语句输出中缺少了一个衡量执行计划好坏的重要属性 —— 成本。不 过 MySQL 已经为我们提供了一种查看某个执行计划花费的成本的方式：
在 EXPLAIN 单词和真正的查询语句中间加上 FORMAT=JSON。
这样我们就可以得到一个 json 格式的执行计划，里边包含该计划花费的成 本，比如这样：
mysql> explain format=json SELECT * FROM order_exp WHERE order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S') AND expire_time> '2021-03-22 18:28:28' AND expire_time<= '2021-03-22 18:35:09' AND insert_time> expire_time AND order_note LIKE '%7 排 1%' AND order_status = 0\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1, # 整个查询语句只有 1 个 SELECT 关键字，该关键字对应的 id 号为 1
"cost_info": {
"query_cost": "17.81" # 整个查询的执行成本预计为 17.81
},
"table": {
"table_name": "order_exp",
"access_type": "range",
"possible_keys": [
"idx_order_no",
"idx_expire_time"
],
"key": "idx_expire_time",
"used_key_parts": [
"expire_time"
],
"key_length": "5",
"rows_examined_per_scan": 39,
"rows_produced_per_join": 0,
"filtered": "0.13",
"index_condition": "((`len`.`order_exp`.`expire_time` > TIMESTAMP'2021-03-22 18:28:28') and (`len`.`order_exp`.`expire_time` <= TIMESTAMP'2021-03-22 18:35:09'))",
"cost_info": {
"read_cost": "17.80",
"eval_cost": "0.01",
"prefix_cost": "17.81", #单独查询表的成本，也就是：read_cost + eval_cost
"data_read_per_join": "24" #和连接查询相关的数据量，单位字节，这里无用
},
"used_columns": [
"id",
"order_no",
"order_note",
"insert_time",
"expire_duration",
"expire_time",
"order_status"
],
"attached_condition": "((`len`.`order_exp`.`order_status` = 0) and (`len`.`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and (`len`.`order_exp`.`insert_time` > `len`.`order_exp`.`expire_time`) and (`len`.`order_exp`.`order_note` like '%7 排 1%'))"
}
}
}
1 row in set, 1 warning (0.00 sec)
Optimizer Trace
对于 MySQL5.6 之前的版本来说，只能通过 EXPLAIN 语句查看到最后优化器 决定使用的执行计划，**却无法知道它为什么做这个决策。**我们可能有这样的疑问： “我就觉得使用其他的执行方案比 EXPLAIN 输出的这种方案强，凭什么优化器做 的决定和我想的不一样呢？为什么 MySQL 一定要全文扫描，不用索引呢？”
在 MySQL 5.6 以及之后的版本中，MySQL 提出了一个 optimizer trace 的功能， 这个功能可以让我们方便的查看优化器生成执行计划的整个过程，这个功能的开 启与关闭由系统变量 optimizer_trace 决定：
mysql> SHOW VARIABLES LIKE 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name | Value |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+
1 row in set (0.00 sec)
可以看到 enabled 值为 off，表明这个功能默认是关闭的。one_line 的值是控制输 出格式的，如果为 on 那么所有输出都将在一行中展示，我们就保持其默认值为 off。
如果想打开这个功能，必须首先把 enabled 的值改为 on，就像这样：
mysql> SET optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'optimizer_trace';
+-----------------+-------------------------+
| Variable_name | Value |
+-----------------+-------------------------+
| optimizer_trace | enabled=on,one_line=off |
+-----------------+-------------------------+
1 row in set (0.00 sec)
然后我们就可以输入我们想要查看优化过程的查询语句，当该查询语句执行 完成后，就可以到 information_schema 数据库下的 OPTIMIZER_TRACE 表中查看完 整的优化过程。这个 OPTIMIZER_TRACE 表有 4 个列，分别是：
mysql> show create table optimizer_trace\G
*************************** 1. row ***************************
Table: OPTIMIZER_TRACE
Create Table: CREATE TEMPORARY TABLE `OPTIMIZER_TRACE` (
`QUERY` longtext NOT NULL, ## 表示我们的查询语句。
`TRACE` longtext NOT NULL, ## 表示优化过程的 JSON 格式文本。
`MISSING_BYTES_BEYOND_MAX_MEM_SIZE` int NOT NULL DEFAULT '0', ## 由于优化过程可能会输出很多， 如果超过某个限制时，多余的文本将不会被显示，这个字段展示了被忽略的文本 字节数。
`INSUFFICIENT_PRIVILEGES` tinyint NOT NULL DEFAULT '0' ## 表示是否没有权限查看优化过程，默认值是 0， 只有某些特殊情况下才会是 1，我们暂时不关心这个字段的值。
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)
当停止查看语句的优化过程时，把 optimizer trace 功能关闭
SET optimizer_trace="enabled=off";
注意：开启 trace 会影响 mysql 性能，所以只能临时分析 sql 使用，用完之 后立即关闭 。
现在我们有一个搜索条件比较多的查询语句，它的执行计划如下：
explain SELECT *
FROM order_exp
WHERE order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S') AND
expire_time> '2021-03-22 18:28:28' AND
insert_time> '2021-03-22 18:35:09' AND
order_note LIKE '%7 排 1%';
可以看到该查询可能使用到的索引有 3 个
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: order_exp
partitions: NULL
type: range
possible_keys: u_idx_day_status,idx_order_no,idx_expire_time
key: idx_order_no
key_len: 152
ref: NULL
rows: 58
filtered: 2.78
Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)
u_idx_day_status,idx_order_no,idx_expire_time，那么为什么优化器最终选择了 idx_order_no 而不选择其他的索引或者直接全表扫描呢？这时候就可以通过 otpimzer trace 功能来查看优化器的具体工作过程：
## 执行一次sql
SELECT * FROM order_exp WHERE order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S') AND expire_time> '2021-03-22 18:28:28' AND insert_time> '2021-03-22 18:35:09' AND order_note LIKE '%7 排 1%';
SELECT * FROM information_schema.OPTIMIZER_TRACE\G ## 如果太多,可以加上条件
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
// 查询端语句
QUERY: SELECT * FROM order_exp WHERE order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S') AND expire_time> '2021-03-22 18:28:28' AND insert_time> '2021-03-22 18:35:09' AND order_note LIKE '%7 排 1%'
// 优化过程
TRACE: {
"steps": [
{
"join_preparation": { // 准备阶段
"select#": 1,
"steps": [
{
"IN_uses_bisection": true
},
{ // 类似于查询优化器将查询语句重写后的语句, 也是我们执行执行计划后的warnings类容
"expanded_query": "/* select#1 */ select `order_exp`.`id` AS `id`,`order_exp`.`order_no` AS `order_no`,`order_exp`.`order_note` AS `order_note`,`order_exp`.`insert_time` AS `insert_time`,`order_exp`.`expire_duration` AS `expire_duration`,`order_exp`.`expire_time` AS `expire_time`,`order_exp`.`order_status` AS `order_status` from `order_exp` where ((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and (`order_exp`.`expire_time` > '2021-03-22 18:28:28') and (`order_exp`.`insert_time` > '2021-03-22 18:35:09') and (`order_exp`.`order_note` like '%7 排 1%'))"
}
]
}
},
{
"join_optimization": { // 优化阶段
"select#": 1,
"steps": [
{
"condition_processing": { // 处理搜索条件
"condition": "WHERE",
"original_condition": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and (`order_exp`.`expire_time` > '2021-03-22 18:28:28') and (`order_exp`.`insert_time` > '2021-03-22 18:35:09') and (`order_exp`.`order_note` like '%7 排 1%'))", // 原始搜索条件
"steps": [
{
"transformation": "equality_propagation", // 等值传递转换
"resulting_condition": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and (`order_exp`.`expire_time` > '2021-03-22 18:28:28') and (`order_exp`.`insert_time` > '2021-03-22 18:35:09') and (`order_exp`.`order_note` like '%7 排 1%'))"
},
{
"transformation": "constant_propagation", // 常量传递转换
"resulting_condition": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and (`order_exp`.`expire_time` > '2021-03-22 18:28:28') and (`order_exp`.`insert_time` > '2021-03-22 18:35:09') and (`order_exp`.`order_note` like '%7 排 1%'))"
},
{
"transformation": "trivial_condition_removal", // 移除没有用的条件
"resulting_condition": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and (`order_exp`.`expire_time` > TIMESTAMP'2021-03-22 18:28:28') and (`order_exp`.`insert_time` > TIMESTAMP'2021-03-22 18:35:09') and (`order_exp`.`order_note` like '%7 排 1%'))"
}
]
}
},
{
"substitute_generated_columns": { // 替换虚拟生成列
}
},
{
"table_dependencies": [ // 表的依赖信息
{
"table": "`order_exp`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
]
},
{
"rows_estimation": [ // 预估不同单表访问方法的访问成本
{
"table": "`order_exp`",
"range_analysis": {
"table_scan": { // 全表扫描的行数以及成本
"rows": 10612,
"cost": 1087.55
},
"potential_range_indexes": [ // 分析可能使用的索引
{
"index": "PRIMARY", // 主键
"usable": false,
"cause": "not_applicable" // 主键不可用
},
{
"index": "u_idx_day_status", // u_idx_day_status 肯能被使用
"usable": true,
"key_parts": [
"insert_time",
"order_status",
"expire_time"
]
},
{
"index": "idx_order_no", // idx_order_no 可能被使用
"usable": true,
"key_parts": [
"order_no",
"id"
]
},
{
"index": "idx_expire_time", // idx_expire_time 可能被使用
"usable": true,
"key_parts": [
"expire_time",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "u_idx_day_status",
"usable": false,
"cause": "query_references_nonkey_column"
},
{
"index": "idx_order_no",
"usable": false,
"cause": "query_references_nonkey_column"
},
{
"index": "idx_expire_time",
"usable": false,
"cause": "query_references_nonkey_column"
}
]
},
"analyzing_range_alternatives": { //分析各种可能使用的索引的成本
"range_scan_alternatives": [
{
"index": "u_idx_day_status", // 使用 u_idx_day_status 的成本分析
"ranges": [ // 使用 u_idx_day_status 的范围区间
"0x99a92d28c9 < insert_time"
],
"index_dives_for_eq_ranges": true, // 是否使用 index dive
"rowid_ordered": false, // 使用该索引获取的记录是否按照主键排序
"using_mrr": false, // 是否使用 mrr
"index_only": false, // 是否是索引覆盖访问
"rows": 5306, // 使用该索引获取的记录条数
"cost": 1857.36, // 使用该索引的成本
"chosen": false, // 是否选择该索引
"cause": "cost" // 因为成本太大所以不选择该索引
},
{
"index": "idx_order_no",
"ranges": [
"DD00_10S <= order_no <= DD00_10S",
"DD00_6S <= order_no <= DD00_6S",
"DD00_9S <= order_no <= DD00_9S"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 58,
"cost": 21.06,
"chosen": true
},
{
"index": "idx_expire_time",
"ranges": [
"0x99a92d271c < expire_time"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 5306,
"cost": 1857.36,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": { // 分析使用索引合并的成本
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": { // 对于上述单表查询 s1 最优的访问方法
"range_access_plan": {
"type": "range_scan",
"index": "idx_order_no",
"rows": 58,
"ranges": [
"DD00_10S <= order_no <= DD00_10S",
"DD00_6S <= order_no <= DD00_6S",
"DD00_9S <= order_no <= DD00_9S"
]
},
"rows_for_plan": 58,
"cost_for_plan": 21.06,
"chosen": true
}
}
}
]
},
{
// 分析各种可能的执行计划,负责对比各可行计划的开销，并选择相对最优的执行计划。
//（对多表查询这可能有很多种不同的方案，单表查询的方案上边已经分析过了，直接选取 idx_order_no 即可）
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`order_exp`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 58,
"access_type": "range",
"range_details": {
"used_index": "idx_order_no"
},
"resulting_rows": 58,
"cost": 26.86,
"chosen": true // 选中
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 58,
"cost_for_plan": 26.86,
"chosen": true
}
]
},
{
// 尝试给查询添加一些其他的查询条件,增加主要是为了便于 ICP（索引条件下推）
"attaching_conditions_to_tables": {
"original_condition": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and (`order_exp`.`expire_time` > TIMESTAMP'2021-03-22 18:28:28') and (`order_exp`.`insert_time` > TIMESTAMP'2021-03-22 18:35:09') and (`order_exp`.`order_note` like '%7 排 1%'))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`order_exp`",
"attached": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and (`order_exp`.`expire_time` > TIMESTAMP'2021-03-22 18:28:28') and (`order_exp`.`insert_time` > TIMESTAMP'2021-03-22 18:35:09') and (`order_exp`.`order_note` like '%7 排 1%'))"
}
]
}
},
{
"finalizing_table_conditions": [
{
"table": "`order_exp`",
"original_table_condition": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and (`order_exp`.`expire_time` > TIMESTAMP'2021-03-22 18:28:28') and (`order_exp`.`insert_time` > TIMESTAMP'2021-03-22 18:35:09') and (`order_exp`.`order_note` like '%7 排 1%'))",
"final_table_condition ": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and (`order_exp`.`expire_time` > TIMESTAMP'2021-03-22 18:28:28') and (`order_exp`.`insert_time` > TIMESTAMP'2021-03-22 18:35:09') and (`order_exp`.`order_note` like '%7 排 1%'))"
}
]
},
{
"refine_plan": [ // 再稍稍的改进一下执行计划, refine:v. 精炼，提纯；改进，改善；使……文雅
{
"table": "`order_exp`",
"pushed_index_condition": "(`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S'))",
"table_condition_attached": "((`order_exp`.`expire_time` > TIMESTAMP'2021-03-22 18:28:28') and (`order_exp`.`insert_time` > TIMESTAMP'2021-03-22 18:35:09') and (`order_exp`.`order_note` like '%7 排 1%'))"
}
]
}
]
}
},
{
"join_execution": { // execute 阶段
"select#": 1,
"steps": [
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 // # 因优化过程文本太多而丢弃的文本字节大小，值为 0 时表示并没有丢弃
INSUFFICIENT_PRIVILEGES: 0 // # 权限字段
1 row in set (0.00 sec)
优化过程大致分为了三个阶段：
- prepare 阶段
- optimize 阶段
- execute 阶段
我们所说的基于成本的优化主要集中在 optimize 阶段，对于单表查询来说， 我们主要关注 optimize 阶段的"rows_estimation"这个过程，这个过程深入分析了 对单表查询的各种执行方案的成本；
对于多表连接查询来说，我们更多需要关注"considered_execution_plans"这 个过程，这个过程里会写明各种不同的连接方式所对应的成本。反正优化器最终 会选择成本最低的那种方案来作为最终的执行计划，也就是我们使用 EXPLAIN 语 句所展现出的那种方案。
如果对使用 EXPLAIN 语句展示出的对某个查询的执行计划很不理解，就可以 尝试使用 optimizer trace 功能来详细了解每一种执行方案对应的成本。
连接查询的成本
Condition filtering 介绍
连接查询至少是要有两个表的，课程的讲述中可能使用 order_exp 表的派生 表 s1、s2 和 order_exp2。
我们前边说过，MySQL 中连接查询采用的是嵌套循环连接算法，驱动表会被 访问一次，被驱动表可能会被访问多次，所以对于两表连接查询来说，它的查询 成本由下边两个部分构成：
单次查询驱动表的成本
多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集 中有多少条记录）
对驱动表进行查询后得到的记录条数称之为驱动表的扇出（英文名：fanout）。 很显然驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成 本也就越低。当查询优化器想计算整个连接查询所使用的成本时，就需要计算出 驱动表的扇出值，有的时候扇出值的计算是很容易的，比如下边这两个查询：
查询一：
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2;
假设使用 s1 表作为驱动表，很显然对驱动表的单表查询只能使用全表扫描 的方式执行，驱动表的扇出值也很明确，那就是驱动表中有多少记录，扇出值就是多少。统计数据中s1表的记录行数是10573，也就是说优化器就直接会把10573 当作在 s1 表的扇出值。
查询二：
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE s1.expire_time> '2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22 18:35:09';
仍然假设 s1 表是驱动表的话，很显然对驱动表的单表查询可以使用 idx_expire_time 索引执行查询。此时范围区间( ‘2021-03-22 18:28:28’, ‘2021-03-22 18:35:09’)中有多少条记录，那么扇出值就是多少。
但是有的时候扇出值的计算就变得很棘手，比方说下边几个查询
查询三：
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE s1.order_note > ‘xyz’;
本查询和查询一类似，只不过对于驱动表 s1 多了一个 order_note > 'xyz’的搜 索条件。查询优化器又不会真正的去执行查询，所以它只能猜这 10573 记录里有 多少条记录满足 order_note > 'xyz’条件。
查询四：
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE s1.expire_time> '2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22 18:35:09' AND s1.order_note > 'xyz';
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: s1
partitions: NULL
type: range
possible_keys: idx_expire_time
key: idx_expire_time
key_len: 5
ref: NULL
rows: 39
filtered: 33.33
Extra: Using index condition; Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: s2
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10612
filtered: 100.00
Extra: Using join buffer (hash join)
2 rows in set, 1 warning (0.00 sec)
本查询和查询二类似，只不过对于驱动表 s1 也多了一个 order_note > ‘xyz’ 的搜索条件。不过因为本查询可以使用 idx_expire_time 索引，所以只需要从符合 二级索引范围区间的记录中猜有多少条记录符合 order_note > 'xyz’条件，也就是 只需要猜在 39 条记录中有多少符合 order_note > 'xyz’条件。
查询五：
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE s1.expire_time> '2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22 18:35:09' AND s1.order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S') AND s1.order_note > 'xyz';
本查询和查询四类似，不过在驱动表 s1 选取 idx_expire_time 索引执行查询 后，优化器需要从符合二级索引范围区间的记录中猜有多少条记录符合下边两个 条件：
order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’)
order_note > ‘xyz’
也就是优化器需要猜在 39 条记录中有多少符合上述两个条件的。
说了这么多，其实就是想表达在这两种情况下计算驱动表扇出值时需要靠猜：
如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需 要猜满足搜索条件的记录到底有多少条。
如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜满 足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。
MySQL 把这个猜的过程称之为 condition filtering。当然，这个过程可能 会使用到索引，也可能使用到统计数据，也可能就是 MySQL 单纯的瞎猜，整个 评估过程非常复杂，所以我们不去细讲。
在 MySQL 5.7 之前的版本中，查询优化器在计算驱动表扇出时，如果是使用 全表扫描的话，就直接使用表中记录的数量作为扇出值，如果使用索引的话，就 直接使用满足范围条件的索引记录条数作为扇出值。
在 MySQL 5.7 中，MySQL 引入了这个 condition filtering 的功能，就是还要猜 一猜剩余的那些搜索条件能把驱动表中的记录再过滤多少条，其实本质上就是为 了让成本估算更精确。 我们所说的纯粹瞎猜其实是很不严谨的，MySQL 称之为 启发式规则。
两表连接的成本分析
连接查询的成本计算公式是这样的：
连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被 驱动表的成本
对于左（外）连接和右（外）连接查询来说，它们的驱动表是固定的，所以 想要得到最优的查询方案只需要分别为驱动表和被驱动表选择成本最低的访问 方法。
可是对于内连接来说，驱动表和被驱动表的位置是可以互换的，所以需要考 虑两个方面的问题：
不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的 表连接顺序。然后分别为驱动表和被驱动表选择成本最低的访问方法。
很显然，计算内连接查询成本的方式更麻烦一些，下边我们就以内连接为例 来看看如何计算出最优的连接查询方案。当然在某些情况下，左（外）连接和右 （外）连接查询在某些特殊情况下可以被优化为内连接查询。
我们来看看内连接，比如对于下边这个查询来说：
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 ON s1.order_no= s2.order_note WHERE s1.expire_time> '2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22 18:35:09' AND s2.expire_time> '2021-03-22 18:35:09' AND s2.expire_time<= '2021-03-22 18:35:59';
可以选择的连接顺序有两种：
s1 连接 s2，也就是 s1 作为驱动表，s2 作为被驱动表。
s2 连接 s1，也就是 s2 作为驱动表，s1 作为被驱动表。
查询优化器需要分别考虑这两种情况下的最优查询成本，然后选取那个成本 更低的连接顺序以及该连接顺序下各个表的最优访问方法作为最终的查询计划。 我们定性的分析一下，不像分析单表查询那样定量的分析了：
使用 s1 作为驱动表的情况
分析对于驱动表的成本最低的执行方案，首先看一下涉及 s1 表单表的搜索 条件有哪些：
s1.expire_time> ‘2021-03-22 18:28:28’ AND s1.expire_time<= ‘2021-03-22 18:35:09’
所以这个查询可能使用到 idx_expire_time 索引，从全表扫描和使用 idx_expire_time 这两个方案中选出成本最低的那个，很显然使用 idx_expire_time 执行查询的成本更低些。
然后分析对于被驱动表的成本最低的执行方案，此时涉及被驱动表 s2 的搜 索条件就是：
1、s2.order_note = 常数（这是因为对驱动表 s1 结果集中的每一条记录，都 需要进行一次被驱动表 s2 的访问，此时那些涉及两表的条件现在相当于只涉及 被驱动表 s2 了。）
2、s2.expire_time> ‘2021-03-22 18:35:09’ AND s2.expire_time<= '2021-03-22 18:35:59
很显然，第一个条件由于 order_note 没有用到索引，所以并没有什么用， 此时访问 s2 表时可用的方案也是全表扫描和使用 idx_expire_time 两种，假设使 用 idx_expire_time 的成本更小。
所以此时使用 s1 作为驱动表时的总成本就是（暂时不考虑使用 join buffer 对成本的影响）：
使用 idx_expire_time 访问 s1 的成本 + s1 的扇出 × 使用 idx_expire_time 访 问 s2 的成本
使用 s2 作为驱动表的情况
分析对于驱动表的成本最低的执行方案
首先看一下涉及 s2 表单表的搜索条件有哪些：
s2.expire_time> ‘2021-03-22 18:35:09’ AND s2.expire_time<= ‘2021-03-22 18:35:59’
所以这个查询可能使用到 idx_expire_time 索引，从全表扫描和使用 idx_expire_time 这两个方案中选出成本最低的那个，假设使用 idx_expire_time 执 行查询的成本更低些。
然后分析对于被驱动表的成本最低的执行方案
此时涉及被驱动表 s1 的搜索条件就是：
1、s1.order_no = 常数
2、s1.expire_time> ‘2021-03-22 18:28:28’ AND s1.expire_time<= ‘2021-03-22 18:35:09’
这时就很有趣了，使用 idx_order_no 可以进行 ref 方式的访问，使用 idx_expire_time 可以使用 range 方式的访问。
那么优化器需要从全表扫描、使用 idx_order_no、使用 idx_expire_time 这几 个方案里选出一个成本最低的方案。
这里有个问题，因为 idx_expire_time 的范围区间是确定的，怎么计算使用 idx_expire_time 的成本我们上边已经说过了，可是在没有真正执行查询前， s1.order_no = 常数中的常数值我们是不知道的，怎么衡量使用 idx_order_no 执 行查询的成本呢？其实很简单，直接使用我们前面说过的索引统计数据就好了 （就是索引列平均一个值重复多少次）。一般情况下，ref 的访问方式要比 range 成本更低，这里假设使用 idx_order_no 进行对 s1 的访问。
所以此时使用 s2 作为驱动表时的总成本就是：
使用 idx_expire_time 访问 s2 的成本 + s2 的扇出 × 使用 idx_order_no 访问 s1 的成本
最后优化器会比较这两种方式的最优访问成本，选取那个成本更低的连接顺 序去真正的执行查询。从上边的计算过程也可以看出来，一般来讲，连接查询成 本占大头的其实是驱动表扇出数 x 单次访问被驱动表的成本，所以我们的优化 重点其实是下边这两个部分：
-
尽量减少驱动表的扇出
-
对被驱动表的访问成本尽量低
这一点对于我们实际书写连接查询语句时十分有用，我们需要尽量在被驱动 表的连接列上建立索引，这样就可以使用 ref 访问方法来降低访问被驱动表的成 本了。如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这 样就可以把访问被驱动表的成本降到更低了。(等值索引至少是ref, 再好是eq_ref和const,eq_ref和const都是等值条件,但是const的等值是常数,eq_ref是变量或范围, index dive(基于索引统计的成本计算参数,in多于eq_range_index_dive_limit,将会使用估算方式计算成本))
EXPLAIN 输出连接成本
连接查询在输出成本时和单表查询稍有不同，如下：
explain format=json SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 ON s1.order_no= s2.order_note WHERE s1.expire_time> '2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22 18:35:09' AND s2.expire_time> '2021-03-22 18:35:09' AND s2.expire_time<= '2021-03-22 18:35:59'\G
EXPLAIN: {
"query_block": {
"select_id": 1, // 整个查询语句只有 1 个 SELECT 关键字，该关键字对应的 id 号为 1
"cost_info": {
"query_cost": "840.51" // 整个查询的执行成本
},
"nested_loop": [ // 几个表之间采用嵌套循环连接算法执行
{
"table": {
"table_name": "s2", // s2 表是驱动表
"access_type": "range", // 访问方法为 range
"possible_keys": [
"idx_expire_time"
],
"key": "idx_expire_time",
"used_key_parts": [
"expire_time"
],
"key_length": "5",
"rows_examined_per_scan": 321, // 查询 s2 表大致需要扫描 321 条记录
"rows_produced_per_join": 321, // 驱动表 s2 的扇出是 321
"filtered": "100.00", // condition filtering 代表的百分比
"index_condition": "((`mysqladv`.`s2`.`expire_time` > '2021-03-22 18:35:09') and (`mysqladv`.`s2`.`expire_time` <= '2021-03-22 18:35:59'))",
"cost_info": {
"read_cost": "386.21",
"eval_cost": "64.20",
"prefix_cost": "450.41", // 查询 s1 表总共的成本，read_cost + eval_cost
"data_read_per_join": "152K" // 读取的数据量
},"used_columns": [
"id",
"order_no",
"order_note",
"insert_time",
"expire_duration",
"expire_time",
"order_status"]
}
}, {
"table": {
"table_name": "s1", // s1 表是被驱动表
"access_type": "ref",
"possible_keys": [
"idx_order_no",
"idx_expire_time"
],
"key": "idx_order_no",
"used_key_parts": [
"order_no"
],
"key_length": "152",
"ref": [
"mysqladv.s2.order_note"
],
"rows_examined_per_scan": 1, // 查询一次 s1 表大致需要扫描 1 条记录
"rows_produced_per_join": 16, // 被驱动表 s2 的扇出是 16（由于没有多余的 表进行连接，所以这个值无用）
"filtered": "4.94", // condition filtering 代表的百分比
"index_condition": "(`mysqladv`.`s1`.`order_no` = `mysqladv`.`s2`.`order_note`)",
"cost_info": {
"read_cost": "325.08",
"eval_cost": "3.21",
"prefix_cost": "840.51", // 单次查询 s2、多次查询 s1 表总共的成本
"data_read_per_join": "7K"
},
"used_columns": [
"id",
"order_no",
"order_note",
"insert_time",
"expire_duration",
"expire_time",
"order_status"
],
"attached_condition": "((`mysqladv`.`s1`.`expire_time` > '2021-03-22 18:28:28') and (`mysqladv`.`s1`.`expire_time` <= '2021-03-22 18:35:09'))"
}
}
]
}
}
我们使用//后边跟随注释的形式为大家解释了EXPLAIN FORMAT=JSON语句的 输出内容，s2 表的"cost_info"中 prefix_cost 就是单独查询 s2 表的成本。
对于 s1 表的"cost_info"中，由于 s1 表是被驱动表，所以可能被读取多次， 这里的 read_cost 和 eval_cost 是访问多次 s2 表后累加起来的值，而 s1 表中的 prefix_cost 的值代表的是整个连接查询预计的成本。
看完了上面的执行计划的输出，可能大家有疑惑，驱动表 S2 的查询成本为 450.41，总查询成本为 840.51，也就是说对被驱动表 S1 的查询成本也就是 390 左右，看起来用 S1 做驱动表好像更省一点。真的这样吗？我们把 SQL 语句改造 一下，将 INNER JOIN 替换为 STRAIGHT_JOIN：
explain format=json SELECT * FROM order_exp AS s1 STRAIGHT_JOIN order_exp2 AS s2 ON s1.order_no= s2.order_note WHERE s1.expire_time> '2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22 18:35:09' AND s2.expire_time> '2021-03-22 18:35:09' AND s2.expire_time<= '2021-03-22 18:35:59'\G
大家可以自行看看所需要的成本是多少，并自行解释一下原因。
多表连接的成本分析
首先要考虑一下多表连接时可能产生出多少种连接顺序：
对于两表连接，比如表 A 和表 B 连接
只有 AB、BA 这两种连接顺序。其实相当于 2 × 1 = 2 种连接顺序。
对于三表连接，比如表 A、表 B、表 C 进行连接
有 ABC、ACB、BAC、BCA、CAB、CBA 这么 6 种连接顺序。其实相当于 3 × 2 × 1 = 6 种连接顺序。(组合)
对于 n 表连接的话，则有 n × (n-1) × (n-2) × ··· × 1 种连接顺序， 就是 n 的阶乘种连接顺序，也就是 n!。
有 n 个表进行连接，MySQL 查询优化器要每一种连接顺序的成本都计算一 遍么？那可是 n!种连接顺序呀。其实真的是要都算一遍，不过 MySQL 用了很多 办法减少计算非常多种连接顺序的成本的方法：
提前结束某种顺序的成本评估
MySQL 在计算各种链接顺序的成本之前，会维护一个全局的变量，这个变量 表示当前最小的连接查询成本。如果在分析某个连接顺序的成本时，该成本已经 超过当前最小的连接查询成本，那就压根儿不对该连接顺序继续往下分析了。比 方说 A、B、C 三个表进行连接，已经得到连接顺序 ABC 是当前的最小连接成本，比方说 10.0，在计算连接顺序 BCA 时，发现 B 和 C 的连接成本就已经大于 10.0 时，就不再继续往后分析 BCA 这个连接顺序的成本了。
系统变量 optimizer_search_depth
为了防止无穷无尽的分析各种连接顺序的成本，MySQL 提出了 optimizer_search_depth 系统变量，如果连接表的个数小于该值，那么就继续穷 举分析每一种连接顺序的成本，否则只对与 optimizer_search_depth 值相同数量 的表进行穷举分析。很显然，该值越大，成本分析的越精确，越容易得到好的执 行计划，但是消耗的时间也就越长，否则得到不是很好的执行计划，但可以省掉 很多分析连接成本的时间。
根据某些规则压根儿就不考虑某些连接顺序
即使是有上边两条规则的限制，但是分析多个表不同连接顺序成本花费的时 间还是会很长，所以 MySQL 干脆提出了一些所谓的启发式规则（就是根据以往 经验指定的一些规则），凡是不满足这些规则的连接顺序压根儿就不分析，这样 可以极大的减少需要分析的连接顺序的数量，但是也可能造成错失最优的执行计 划。他们提供了一个系统变量 optimizer_prune_level 来控制到底是不是用这些启 发式规则。
调节成本常数
我们前边已经介绍了两个成本常数：
读取一个页面花费的成本默认是 1.0
检测一条记录是否符合搜索条件的成本默认是 0.2
其实除了这两个成本常数，MySQL 还支持很多，它们被存储到了 MySQL 数据库的两个表中：
mysql> SHOW TABLES FROM mysql LIKE '%cost%';
+--------------------------+
| Tables_in_mysql (%cost%) |
+--------------------------+
| engine_cost |
| server_cost |
+--------------------------+
2 rows in set (0.00 sec)
因为一条语句的执行其实是分为两层的：server 层、存储引擎层。
在 server 层进行连接管理、查询缓存、语法解析、查询优化等操作，在存储 引擎层执行具体的数据存取操作。也就是说一条语句在 server 层中执行的成本是 和它操作的表使用的存储引擎是没关系的，所以关于这些操作对应的成本常数就 存储在了 server_cost 表中，而依赖于存储引擎的一些操作对应的成本常数就存 储在了 engine_cost 表中。
mysql.server_cost 表
server_cost 表中在 server 层进行的一些操作对应的成本常数，具体内容如下：
SELECT * FROM mysql.server_cost;
mysql> SELECT * FROM mysql.server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name | cost_value | last_update | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost | NULL | 2021-12-07 06:11:04 | NULL | 20 |
| disk_temptable_row_cost | NULL | 2021-12-07 06:11:04 | NULL | 0.5 |
| key_compare_cost | NULL | 2021-12-07 06:11:04 | NULL | 0.05 |
| memory_temptable_create_cost | NULL | 2021-12-07 06:11:04 | NULL | 1 |
| memory_temptable_row_cost | NULL | 2021-12-07 06:11:04 | NULL | 0.1 |
| row_evaluate_cost | NULL | 2021-12-07 06:11:04 | NULL | 0.1 |
+------------------------------+------------+---------------------+---------+---------------+
6 rows in set (0.00 sec)
cost_name 表示成本常数的名称。
cost_value 表示成本常数对应的值。如果该列的值为 NULL 的话，意味着对应的成本常 数会采用默认值。
last_update 表示最后更新记录的时间。
comment 注释。
从 server_cost 中的内容可以看出来，目前在 server 层的一些操作对应的成 本常数有以下几种：
disk_temptable_create_cost 默认值 20.0(mysql5.7是40,mysql8是20) 创建基于磁盘的临时表的成本，如 果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。
disk_temptable_row_cost 默认值 0.5(mysql5.7是1.0,mysql8是0.5) 向基于磁盘的临时表写入或读取一 条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。
key_compare_cost 0.1 两条记录做比较操作的成本，多用在排序操作上， 如果增大这个值的话会提升 filesort 的成本，让优化器可能更倾向于使用索引完 成排序而不是 filesort。
memory_temptable_create_cost 默认值 2.0 创建基于内存的临时表的成本， 如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。
memory_temptable_row_cost 默认值 0.2 向基于内存的临时表写入或读 取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临 时表。
row_evaluate_cost 默认值 0.2 这个就是我们之前一直使用的检测一条记 录是否符合搜索条件的成本，增大这个值可能让优化器更倾向于使用索引而不是 直接全表扫描。
MySQL 在执行诸如 DISTINCT 查询、分组查询、Union 查询以及某些特殊条件 下的排序查询都可能在内部先创建一个临时表，使用这个临时表来辅助完成查询 （比如对于 DISTINCT 查询可以建一个带有 UNIQUE 索引的临时表，直接把需要去 重的记录插入到这个临时表中，插入完成之后的记录就是结果集了）。在数据量 大的情况下可能创建基于磁盘的临时表，也就是为该临时表使用MyISAM、InnoDB等存储引擎，在数据量不大时可能创建基于内存的临时表，也就是使用 Memory 存储引擎。大家可以看到，创建临时表和对这个临时表进行写入和读取的操作代 价还是很高的就行了。
这些成本常数在 server_cost 中的初始值都是 NULL，意味着优化器会使用它 们的默认值来计算某个操作的成本，如果我们想修改某个成本常数的值的话，需 要做两个步骤：
对我们感兴趣的成本常数做 update 更新操作，然后使用下边语句即可： FLUSH OPTIMIZER_COSTS;
当然，在你修改完某个成本常数后想把它们再改回默认值的话，可以直接把 cost_value 的值设置为 NULL，再使用 FLUSH OPTIMIZER_COSTS 语句让系统重新加 载。
mysql.engine_cost 表
engine_cost 表表中在存储引擎层进行的一些操作对应的成本常数，具体内 容如下：
SELECT * FROM mysql.engine_cost;
mysql> SELECT * FROM mysql.engine_cost\G
*************************** 1. row ***************************
engine_name: default
device_type: 0
cost_name: io_block_read_cost
cost_value: NULL
last_update: 2021-12-07 06:11:04
comment: NULL
default_value: 1
*************************** 2. row ***************************
engine_name: default
device_type: 0
cost_name: memory_block_read_cost
cost_value: NULL
last_update: 2021-12-07 06:11:04
comment: NULL
default_value: 0.25
2 rows in set (0.00 sec)
与 server_cost 相比，engine_cost 多了两个列
engine_name 列:指成本常数适用的存储引擎名称。如果该值为 default，意味着对应的成本 常数适用于所有的存储引擎。
device_type 列: 指存储引擎使用的设备类型，这主要是为了区分常规的机械硬盘和固态硬盘， 不过在 MySQL 5.7.X 这个版本中并没有对机械硬盘的成本和固态硬盘的成本作区 分，所以该值默认是 0。
我们从 engine_cost 表中的内容可以看出来，目前支持的存储引擎成本常数 只有两个：
io_block_read_cost 默认值 1.0 从磁盘上读取一个块对应的成本。请注意我 使用的是块，而不是页这个词。对于 InnoDB 存储引擎来说，一个页就是一个块， 不过对于 MyISAM 存储引擎来说，默认是以 4096 字节作为一个块的。增大这个 值会加重 I/O 成本，可能让优化器更倾向于选择使用索引执行查询而不是执行全 表扫描。
memory_block_read_cost 默认值 1.0 与上一个参数类似，只不过衡量的 是从内存中读取一个块对应的成本。
怎么从内存中和从磁盘上读取一个块的默认成本是一样的？这主要是因为 在 MySQL 目前的实现中，并不能准确预测某个查询需要访问的块中有哪些块已经加载到内存中，有哪些块还停留在磁盘上，所以 MySQL 简单的认为不管这个 块有没有加载到内存中，使用的成本都是 1.0。
与更新 server_cost 表中的记录一样，我们也可以通过更新 engine_cost 表中 的记录来更改关于存储引擎的成本常数，做法一样。
InnoDB 中的统计数据
我们前边唠叨查询成本的时候经常用到一些统计数据，比如通过 SHOW TABLE STATUS 可以看到关于表的统计数据，通过 SHOW INDEX 可以看到关于索引 的统计数据，那么这些统计数据是怎么来的呢？它们是以什么方式收集的呢？
统计数据存储方式
InnoDB 提供了两种存储统计数据的方式：
永久性的统计数据，这种统计数据存储在磁盘上，也就是服务器重启之后这 些统计数据还在。
非永久性的统计数据，这种统计数据存储在内存中，当服务器关闭时这些这 些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景下才会重 新收集这些统计数据。
MySQL 给我们提供了系统变量 innodb_stats_persistent 来控制到底采用哪种 方式去存储统计数据。在 MySQL 5.6.6 之前，innodb_stats_persistent 的值默认是 OFF，也就是说 InnoDB 的统计数据默认是存储到内存的，之后的版本中 innodb_stats_persistent 的值默认是 ON，也就是统计数据默认被存储到磁盘中。
mysql> SHOW VARIABLES LIKE 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON |
+-------------------------+-------+
1 row in set (0.01 sec)
不过最近的 MySQL 版本都基本不用基于内存的非永久性统计数据了，所以 我们也就不深入研究。
不过 InnoDB 默认是以表为单位来收集和存储统计数据的，也就是说我们可 以把某些表的统计数据（以及该表的索引统计数据）存储在磁盘上，把另一些表 的统计数据存储在内存中。怎么做到的呢？我们可以在创建和修改表的时候通过 指定 STATS_PERSISTENT 属性来指明该表的统计数据存储方式：
CREATE TABLE 表名 (…) Engine=InnoDB, STATS_PERSISTENT = (1|0);
ALTER TABLE 表名 Engine=InnoDB, STATS_PERSISTENT = (1|0);
当 STATS_PERSISTENT=1 时，表明我们想把该表的统计数据永久的存储到磁 盘上，当 STATS_PERSISTENT=0 时，表明我们想把该表的统计数据临时的存储到 内存中。如果我们在创建表时未指定 STATS_PERSISTENT 属性，那默认采用系统 变量 innodb_stats_persistent 的值作为该属性的值。
基于磁盘的永久性统计数据
当我们选择把某个表以及该表索引的统计数据存放到磁盘上时，实际上是把 这些统计数据存储到了两个表里：
mysql> SHOW TABLES FROM mysql LIKE 'innodb%';
+---------------------------+
| Tables_in_mysql (innodb%) |
+---------------------------+
| innodb_index_stats |
| innodb_table_stats |
+---------------------------+
2 rows in set (0.00 sec)
可以看到，这两个表都位于 mysql 系统数据库下边，其中：
innodb_table_stats 存储了关于表的统计数据，每一条记录对应着一个表的统 计数据。
innodb_index_stats 存储了关于索引的统计数据，每一条记录对应着一个索 引的一个统计项的统计数据。
innodb_table_stats
直接看一下这个 innodb_table_stats 表中的各个列都是干嘛的：
mysql> desc mysql.innodb_table_stats\G
*************************** 1. row ***************************
Field: database_name 数据库名
Type: varchar(64)
Null: NO
Key: PRI
Default: NULL
Extra:
*************************** 2. row ***************************
Field: table_name 表名
Type: varchar(199)
Null: NO
Key: PRI
Default: NULL
Extra:
*************************** 3. row ***************************
Field: last_update 本条记录最后更新时间
Type: timestamp
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra: DEFAULT_GENERATED on update CURRENT_TIMESTAMP
*************************** 4. row ***************************
Field: n_rows 表中记录的条数
Type: bigint unsigned
Null: NO
Key:
Default: NULL
Extra:
*************************** 5. row ***************************
Field: clustered_index_size 表的聚簇索引占用的页面数量
Type: bigint unsigned
Null: NO
Key:
Default: NULL
Extra:
*************************** 6. row ***************************
Field: sum_of_other_index_sizes 表的其他索引占用的页面数量
Type: bigint unsigned
Null: NO
Key:
Default: NULL
Extra:
6 rows in set (0.00 sec)
我们直接看一下这个表里的内容：
几个重要统计信息项的值如下：
n_rows 的值是 10350，表明 order_exp 表中大约有 10350 条记录，注意这个数据是估计值。
clustered_index_size 的值是 97，表明 order_exp 表的聚簇索引占用 97 个页面，这个值是也是一个估计值。
sum_of_other_index_sizes 的值是 81，表明 order_exp 表的其他索引一共占用81 个页面，这个值是也是一个估计值。
n_rows 统计项的收集
InnoDB 统计一个表中有多少行记录是这样的：
按照一定算法（并不是纯粹随机的）选取几个叶子节点页面，计算每个页面中主键值记录数量，然后计算平均一个页面中主键值的记录数量乘以全部叶子节点的数量就算是该表的 n_rows 值。
可以看出来这个 n_rows 值精确与否取决于统计时采样的页面数量，MySQL用名为 innodb_stats_persistent_sample_pages 的系统变量来控制使用永久性的统计数据时，计算统计数据时采样的页面数量。该值设置的越大，统计出的 n_rows值越精确，但是统计耗时也就最久；该值设置的越小，统计出的 n_rows 值越不精确，但是统计耗时特别少。所以在实际使用是需要我们去权衡利弊，该系统变量的默认值是 20。
InnoDB 默认是以表为单位来收集和存储统计数据的，我们也可以单独设置某个表的采样页面的数量，设置方式就是在创建或修改表的时候通过指定STATS_SAMPLE_PAGES 属性来指明该表的统计数据存储方式：
CREATE TABLE 表名 (…) Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;
ALTER TABLE 表名 Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;
如果我们在创建表的语句中并没有指定 STATS_SAMPLE_PAGES 属性的话，将默认使用系统变量 innodb_stats_persistent_sample_pages 的值作为该属性的值。clustered_index_size 和 sum_of_other_index_sizes 统计项的收集牵涉到很具体的 InnoDB 表空间的知识和存储页面数据的细节，我们就不深入讲解了。
innodb_index_stats
直接看一下这个 innodb_index_stats 表中的各个列都是干嘛的：
desc mysql.innodb_index_stats;
字段名 描述
database_name 数据库名
table_name表名
index_name 索引名
last_update 本条记录最后更新时间
stat_name 统计项的名称
stat_value 对应的统计项的值
sample_size为生成统计数据而采样的页面数量
stat_description对应的统计项的描述
innodb_index_stats 表的每条记录代表着一个索引的一个统计项。可能这会大家有些懵逼这个统计项到底指什么，别着急，我们直接看一下关于 order_exp表的索引统计数据都有些什么：
mysql> SELECT * FROM mysql.innodb_index_stats WHERE table_name = ‘order_exp’;
先查看 index_name 列，这个列说明该记录是哪个索引的统计信息，从结果中我们可以看出来，PRIMARY 索引（也就是主键）占了 3 条记录idx_expire_time索引占了 6 条记录。
针对 index_name 列相同的记录，stat_name 表示针对该索引的统计项名称，stat_value 展示的是该索引在该统计项上的值，stat_description 指的是来描述该统计项的含义的。我们来具体看一下一个索引都有哪些统计项：
n_leaf_pages：表示该索引的叶子节点占用多少页面。
size：表示该索引共占用多少页面。
n_diff_pfxNN：表示对应的索引列不重复的值有多少。其中的 NN 长得有点儿怪呀，啥意思呢？
其实 NN 可以被替换为 01、02、03… 这样的数字。比如对于 u_idx_day_status来说：
n_diff_pfx01 表示的是统计 insert_time 这单单一个列不重复的值有多少。
n_diff_pfx02 表示的是统计 insert_time,order_status 这两个列组合起来不重复的值有多少。
n_diff_pfx03 表示的是统计 insert_time,order_status,expire_time 这三个列组合起来不重复的值有多少。
n_diff_pfx04 表示的是统计 key_pare1、key_pare2、expire_time、id 这四个列组合起来不重复的值有多少。对于普通的二级索引，并不能保证它的索引列值是唯一的，比如对于
idx_order_no 来说，key1 列就可能有很多值重复的记录。此时只有在索引列上加上主键值才可以区分两条索引列值都一样的二级索引记录。对于主键和唯一二级索引则没有这个问题，它们本身就可以保证索引列值的
不重复，所以也不需要再统计一遍在索引列后加上主键值的不重复值有多少。比如 u_idx_day_statu 和 idx_order_no。
在计算某些索引列中包含多少不重复值时，需要对一些叶子节点页面进行采样，sample_size 列就表明了采样的页面数量是多少。对于有多个列的联合索引来说，采样的页面数量是：
innodb_stats_persistent_sample_pages × 索引列的个数。
当需要采样的页面数量大于该索引的叶子节点数量的话，就直接采用全表扫 描来统计索引列的不重复值数量了。所以大家可以在查询结果中看到不同索引对 应的 size 列的值可能是不同的。
定期更新统计数据
随着我们不断的对表进行增删改操作，表中的数据也一直在变化， innodb_table_stats 和 innodb_index_stats 表里的统计数据也在变化。MySQL 提供 了如下两种更新统计数据的方式：
开启 innodb_stats_auto_recalc。
系统变量 innodb_stats_auto_recalc 决定着服务器是否自动重新计算统计数 据，它的默认值是 ON，也就是该功能默认是开启的。每个表都维护了一个变量， 该变量记录着对该表进行增删改的记录条数，如果发生变动的记录数量超过了表 大小的 10%，并且自动重新计算统计数据的功能是打开的，那么服务器会重新进 行一次统计数据的计算，并且更新 innodb_table_stats 和 innodb_index_stats 表。 不过自动重新计算统计数据的过程是异步发生的，也就是即使表中变动的记录数超过了 10%，自动重新计算统计数据也不会立即发生，可能会延迟几秒才会进行 计算。
再一次强调，InnoDB 默认是以表为单位来收集和存储统计数据的，我们也 可以单独为某个表设置是否自动重新计算统计数的属性，设置方式就是在创建或 修改表的时候通过指定 STATS_AUTO_RECALC 属性来指明该表的统计数据存储方 式：
CREATE TABLE 表名 (…) Engine=InnoDB, STATS_AUTO_RECALC = (1|0);
ALTER TABLE 表名 Engine=InnoDB, STATS_AUTO_RECALC = (1|0);
当 STATS_AUTO_RECALC=1 时，表明我们想让该表自动重新计算统计数据， 当 STATS_AUTO_RECALC=0 时，表明不想让该表自动重新计算统计数据。如果我 们在创建表时未指定 STATS_AUTO_RECALC 属性，那默认采用系统变量 innodb_stats_auto_recalc 的值作为该属性的值。
手动调用 ANALYZE TABLE 语句来更新统计信息
如果 innodb_stats_auto_recalc 系统变量的值为 OFF 的话，我们也可以手动 调用 ANALYZE TABLE 语句来重新计算统计数据，比如我们可以这样更新关于 order_exp 表的统计数据：
mysql> ANALYZE TABLE order_exp;
ANALYZE TABLE 语句会立即重新计算统计数据，也就是这个过程是同步的，在表 中索引多或者采样页面特别多时这个过程可能会特别慢最好在业务不是很繁忙 的时候再运行。
手动更新 innodb_table_stats 和 innodb_index_stats 表
其实 innodb_table_stats 和 FLUSH TABLE order_exp; 表就相当于一个普通的表一样， 我们能对它们做增删改查操作。这也就意味着我们可以手动更新某个表或者索引 的统计数据。比如说我们想把 order_exp 表关于行数的统计数据更改一下可以这 么做：
步骤一：更新 innodb_table_stats 表。
步骤二：让 MySQL 查询优化器重新加载我们更改过的数据。
更新完 innodb_table_stats 只是单纯的修改了一个表的数据，需要让 MySQL 查询优化器重新加载我们更改过的数据，运行下边的命令就可以了：
FLUSH TABLE order_exp;
5343
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
