mysql-explain执行计划解析
1.explain 字段
2.字段解释
2.1 id:表示查询中执行select子句或操作表的顺序
(1) id相同：执行顺序由上至下
(2) id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
(3) id相同又不同（两种情况同时存在）：id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
2.2 select_type:查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询
(1) SIMPLE：简单的select查询，查询中不包含子查询或者union
(2) PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary
(3) SUBQUERY：在select 或 where列表中包含了子查询
(4) DERIVED：在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在临时表里
(5) UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
(6) UNION RESULT：从union表获取结果的select
2.3 table:
2.4 type: 查询类型
system>const>eq_ref>ref> fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >range>index>ALL
一般来说，好的sql查询至少达到range级别，最好能达到ref
（1）system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计。
（2）const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const。
（3）eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。简单来说，就是多表连接中使用primary key或者 unique key作为关联条件。
（4）ref：出现该连接类型的条件是： 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。
（5）range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。
（6）index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，应为索引文件通常比数据文件小。
详细见下面：（1）聚簇索引和非聚簇索引以及回表
（7）ALL：Full Table Scan，遍历全表以找到匹配的行
2.5 possible_keys
查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用
2.6 key实际使用的索引，如果为NULL，则没有使用索引。
2.7 key_len
表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的
2.8 ref
显示索引的那一列被使用了，如果可能，是一个常量const。
2.9 rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
详情参考：explain 中的rows 是怎么算出来的？
2.10 Extra
不适合在其他字段中显示，但是十分重要的额外信息
（1）Using filesort
mysql对数据使用一个外部的文件排序，而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作成为“文件排序”。
created_time 没有索引，但是要在该字段上进行排序。
（2）Using temporary：
使用临时表保存中间结果，也就是说mysql在对查询结果排序时使用了临时表，常见于order by 和 group by
详见order by 的工作原理
（3）Using index：
表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高。
（4）Using where 一般发生在不能走索引扫描的情况下或者走索引扫描，但是有些查询条件不在索引当中的情况下。(在查找使用索引的情况下，需要回表去查询所需的数据)
（4）Using where Using index：查询的列被索引覆盖，并且 WHERE 筛选条件是索引列之一，但并不是索引的前导列，意味着无法直接通过索引查找来查询到符合条件的数据。
（5）Using index condition：详见索引下推。
性能从 using index ->Using where Using index -> using index condition -> using where -> using temporary -> using filesort 依次递减
order by 的工作原理
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`city` varchar(16) NOT NULL,
`name` varchar(16) NOT NULL,
`age` int(4) NOT NULL,
`addr` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `city` (`city`)
)
select city,name,age from t where city='杭州' order by name limit 1000 ;
通常情况下，这个语句执行流程如下所示 ：
MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。
（1）初始化 sort_buffer，确定放入 name、city、age 这三个字段；
（2）从索引 city 找到第一个满足 city='杭州’条件的主键 ID;
（3）到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
（4）从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
对 sort_buffer 中的数据按照字段 name 做快速排序；按照排序结果取前 1000 行返回给客户端。
sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。
但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。
内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。可以这么简单理解，MySQL 将需要排序的数据分成 12 份，
每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件。
那么，如果 MySQL 认为排序的单行长度太大会怎么做呢？
max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。
它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。
city、name、age 这三个字段的定义总长度是 36，我把 max_length_for_sort_data 设置为 16，
我们再来看看计算过程有什么改变。新的算法放入 sort_buffer 的字段，只有要排序的列（即 name 字段）和主键 id。
但这时，排序的结果就因为少了 city 和 age 字段的值，不能直接返回了，
整个执行流程就变成如下所示的样子：
（1）初始化 sort_buffer，确定放入两个字段，即 name 和 id；
（2）从索引 city 找到第一个满足 city='杭州’条件的主键 id；
（3）到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
（4）从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到不满足 city='杭州’条件为止；
对 sort_buffer 中的数据按照字段 name 进行排序；
遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。
全字段排序 VS rowid 排序我们来分析一下，从这两个执行流程里，还能得出什么结论。
（1）如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。
（2）如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。
这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。
对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。
是不是所有的 order by 都需要排序操作呢？如果不排序就能得到正确的结果，那对系统的消耗会小很多，语句的执行时间也会变得更短。
其实，并不是所有的 order by 语句，都需要排序操作的。从上面分析的执行过程，我们可以看到，MySQL 之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。
explain 中的rows 是怎么算出来的？
我们都知道，选择索引是优化器的工作。
而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。
当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
如果其他因素都不考虑，仅考虑扫描行数的话
基数：一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数就叫基数。
采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，
就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，
会自动触发重新做一次索引统计。在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：
设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。设置为 off 的时候，表示统计信息只存储在内存中。
这时，默认的 N 是 8，M 是 16。由于是采样统计，所以不管 N 是 20 还是 8，这个基数都是很容易不准的。
所以在实践中，如果你发现 explain 的结果预估的 rows 值跟实际情况差距比较大，可以采用这个方法来处理。analyze table t 命令。
补充：
（1）聚簇索引和非聚簇索引以及回表
mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;
表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下。
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。
根据上面的索引结构说明，我们来讨论一个问题：基于主键索引和普通索引的查询有什么区别？
如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。
select id from T where k = 5
例子：身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了。
而再建立一个（身份证号、姓名）的联合索引，是不是浪费空间？
如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。
它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。
（2）索引下推
联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：
mysql> select * from tuser where name like '张%' and age=10 and ismale=1;
你已经知道了前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID，然后进行回表操作。
MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，
只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。
CREATE TABLE `t1` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL, `plan_id` int(11) NOT NULL DEFAULT '0' , PRIMARY KEY (`id`),
KEY `userid` (`user_id`) USING BTREE, KEY `idx_planid` (`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
CREATE TABLE `t3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`status` int(4) NOT NULL DEFAULT '0',
`ootime` varchar(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_xxoo` (`status`,`ootime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
t1 和 t3 表的字符集不一样
sql 执行计划如下：
explain
SELECT t1.id, t1.user_id
FROM t1, t3
WHERE t1.plan_id = t3.id
AND t3.ootime < UNIX_TIMESTAMP('2022-01-18')
+----+-------------+-------+-------+---------------+--------------+---------+--------------+-------+----------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+--------------+-------+----------------------------------------+
| 1 | SIMPLE | t3 | index | PRIMARY | idx_xxoo | 51 | NULL | 39106 | Using where; Using index |
| 1 | SIMPLE | t1 | ref | idx_planid | idx_planid | 4 | t3.id | 401 | Using join buffer (Batched Key Access) |
+----+-------------+-------+-------+---------------+--------------+---------+--------------+-------+----------------------------------------+
使用索引应该注意的问题
1.对于较长的字符串加索引，尽量使用前缀索引，控制索引的大小。例如身份证号码
2.select的时候，如无必要，不要select不需要的字段
仅select索引中的字段时，但查索引即可，会极大的提高查询速度，不用进行二次io查询（根据索引id查询记录的其他信息）
另，在传输时，少量的字段效率也会更高。（覆盖索引）
3.排序（order by，group by）都是很昂贵的操作，因为索引就是有序的，如果排序字段恰好可以使用索引，排序基本无额外影响。
4.对于字符串索引，在写sql时，一定注意a='text'才能走索引。a=text 不会走索引。
5.IN 要慎用，元素过多会导致全表扫描。
6.在SQL语句中，禁止使用前缀是%的like。
7.对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
加锁逻辑
在可重复读（RR）隔离级别下，会有next-key lock(gap lock + 行锁) 是为了解决幻读问题。
但是RR 隔离级别下会容易产生死锁。
我总结的加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。
原则 1：加锁的基本原则是只有索引的字段才会涉及到锁的概念，加锁的单位是 next-key lock。next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
CREATE TABLE `tt` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
案例一：等值查询间隙锁
图 1 等值查询的间隙锁由于表 t 中没有 id=7 的记录，所以用我们上面提到的加锁规则判断一下的话：
根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；同时根据优化 2，这是一个等值查询 (id=7)，而 id=10 不满足查询条件，
next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。所以，session B 要往这个间隙里面插入 id=8 的记录会被锁住，但是 session C 修改 id=10 这行是可以的。
案例二：非唯一索引等值锁
图 2 只加在非唯一索引上的锁看到这个例子，你是不是有一种“该锁的不锁，不该锁的乱锁”的感觉？
我们来分析一下吧。这里 session A 要给索引 c 上 c=5 的这一行加上读锁。
根据原则 1，加锁单位是 next-key lock，因此会给 (0,5]加上 next-key lock。要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃。
根据原则 2，访问到的都要加锁，因此要给 (5,10]加 next-key lock。但是同时这个符合优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。
根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。
但 session C 要插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住。需要注意，在这个例子中，lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。
执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。
案例三：主键索引范围锁第三个例子是关于范围查询的。举例之前，你可以先思考一下这个问题：对于我们这个表 t，下面这两条查询语句，加锁范围相同吗？
mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;
现在我们就用前面提到的加锁规则，来分析一下 session A 会加什么锁呢？开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。
根据优化 1， 主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。
所以，session A 这时候锁的范围就是主键索引上，行锁 id=10 和 next-key lock(10,15]。这样，session B 和 session C 的结果你就能理解了。
这里你需要注意一点，首次 session A 定位查找 id=10 的行的时候，是当做等值查询来判断的，而向右扫描到 id=15 的时候，用的是范围查询判断。
