MySQL的索引、EXPLAIN、日志文件学习笔记

主要是索引内容的整合，所以写得不是很详细，但收集了很多大佬写得很不错的文章，大家根据需要进一步进行学习。

索引（INDEX） 是一种排序好的数据结构，使得查询速度变快。索引就像书的目录，而普通查询时一页一页找。常见的索引结构有: B 树， B+树 和 Hash、红黑树。MySQL的InnoDB和MyIsam的索引都使用B+树结构。
索引需要文件存储保存，且对有索引的数据增删改时需要同时对索引进行修改。对于数据量不大的表，索引用处不大。

B树与B+树

推荐看看这篇文章：https://mp.weixin.qq.com/s?__biz=MzUxODAzNDg4NQ==&mid=2247502168&idx=1&sn=ff63afcea1e8835fca3fe7a97e6922b4&chksm=f98d8df2cefa04e470a65a823256659128e84088e7d249bb0800f8d2a74ef59bf1c411b57110&scene=21

其他结构的劣势：

• Hash表：通过计算hash值进行定位，hash冲突时使用链表连接，等值查询效率很高，但因为是无序，无法做到范围查询。

• 二叉查找树（BST）：有排序的树，但如果出现斜树则会影响查询效率。

• AVL树（平衡二叉查找树、高度平衡二叉树）：通过旋转解决了二叉查找树斜树的问题。但因为需要频繁旋转，也导致会频繁进行IO操作。

• 红黑树：虽然红黑树对高度不如AVL树要求高，但也需要频繁进行旋转，也导致会频繁进行IO操作。

• B树（B Tree），又称B-树、多路平衡查找树。对比其他树来说，B树因为多叉路结构，存储的数据变多了，而高度反而更低了。树高度低也代表了查到叶子节点的次数少，相当于磁盘IO的次数少。磁盘IO频率低，这也符合数据库对于IO频率的要求，所以大多数数据库都是使用B树或者其变形的B+树结构。
  

• B+树：B树的变体，叶子节点是连接起来的，也提高了范围查询时的速度。

  • InnoDB：使用B+树，主键索引为聚簇索引（叶子节点value保存完整数据）。
  • MyISAM：使用B+树，主键索引为非聚簇索引（叶子节点value只保存数据地址）。
    

• B*树：在B+树的变形，在B+树的基础上非叶子节点增加指向兄弟节点的指针。
  

聚簇索引与非聚簇索引

聚簇索引（聚集索引（Clustered Index））：索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 使用B+树，主键索引为聚簇索引。
非聚簇索引（非聚集索引(Non-Clustered Index)）：索引结构和数据分开存放的索引，并不是一种单独的索引类型。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

主键索引(Primary Key)

在MySQL的InnbDB表中，会对主键（PRIMARY KEY）列默认创建索引，即主键索引。索引的B+树上，每个节点的key存储的都为主键值，非叶子节点无value，叶子节点保存所有字段数据，即InnoDB的主键索引为聚簇索引（聚集索引）。主键索引如果未到达叶子节点就找到对应的主键值且select只有主键列，即可直接返回，否则一直查到叶子节点查询其他字段在返回结果。
当表中没主键时：

• 会使用表中设置了唯一约束（INIQUE KEY）且 非空（NOT NULL） 列作为聚簇索引。
• 如果也没有非空的唯一索引列，则会**自动生成隐藏列（自增）**作为聚簇索引。（隐藏列要保证自增唯一，所以并发时会导致锁竞争影响性能）

所以，InnoDB表必须设置主键或者非空的唯一约束。

推荐看看这篇文章：https://blog.csdn.net/baidu_36124158/article/details/115125321

二级索引

推荐看下这篇文章：https://mp.weixin.qq.com/s/8qemhRg5MgXs1So5YCv0fQ

根据非主键列创建的索引就叫做二级索引，因为MySQL会默认对主键创建主键索引，所以二级索引也无需和主键索引组成联合索引。二级索引的B+树上，每个节点的key存储的是索引列上的值，非叶子节点无value，叶子节点保存主键值。通过主键值再回表查询其他字段的信息，所以二级索引也称为辅助索引。另外如果索引列允许非空，需要对NULL进行特殊处理而会影响性能（尽量保证创建索引的列是非空的）。

• 回表：根据主键值，再去主键索引的B+树里找对应的数据，然后再返回，这个过程就叫做回表。正常情况下二级索引需要查询两次索引（自己的索引和主键索引）。因为索引是以页作为单位进行存储的，如果数据都在同页上那么只需要回表一次，通过主键索引叶子节点的连接就能找到所有数据。

• 普通索引：普通的列（不是主键也未加约束）创建的索引。

• 唯一索引：加了唯一约束（INIQUE KEY）的列创建的索引。

• 前缀索引：索引B+树存储的节点变少节省了存储空间，根据前缀查到叶子节点后遍历往后找。

  -- 普通索引（整个字符串），索引字段为user_name整个字符串
  alter table SUser add index index1(user_name);
  -- 前缀索引，6表示索引字段为user_name的前6位
  alter table SUser add index index2(user_name(6));
  

• 全文索引：用于大文本字段（例如TEXT类型）中查找部分片段内容，与like类似但快很多倍。数据少无高并发高吞吐的场景下可以使用，其他场景都有其他更好的解决方案，如使用ElasticSearch。（很少用到）

  推荐看看这篇文章：https://blog.csdn.net/Princeliu999/article/details/128539216

• 函数索引：索引列就是函数或计算的表达式，生成时会保存计算后的值。

  CREATE INDEX idx2 ON t1 ((SUBSTRING(merchant_name, 2, 4)));
  CREATE INDEX idx2 ON t1 ((unit_price * goods_num));
  

• 联合索引：由多个非主键列创建的索引。按创建索引时字段的排序顺序在B+树里进行排序（先按照第一列排序，然后再按照第二列，…），这也是为什么有最左匹配原则的原因。正常情况下，如果查询条件中没第一列，那这个索引直接无效了，因为是先按照第一个进行排序的。（代入平时MySQL的多字段排序查询（ORDER BY）就可以理解了）

  -- 例如现有 user_name和gender创建的联合索引
  -- 1和2会使用到联合索引，但正常情况下3就不会使用到联合索引（未遵守最左匹配原则）
  SELECT id,user_name,gender,age FROM t_person WHERE user_name='小楊同学' and gender=1;
  SELECT id,user_name,gender,age FROM t_person WHERE user_name='小楊同学';
  SELECT id,user_name,gender,age FROM t_person WHERE gender=1;
  

• 覆盖索引：而如果二级索引节点上的key值就能满足查询的结果（select的字段），则无需回表。发生这种情况（只查一个B+树）时，就可以称这个二级索引为覆盖索引。

  -- 例如现有 user_name和gender创建的联合索引，下面几种情况都可称为覆盖索引
  SELECT user_name,gender FROM t_person WHERE user_name='小楊同学' and gender=1;
  SELECT gender FROM t_person WHERE user_name='小楊同学';
  SELECT user_name FROM t_person WHERE user_name='小楊同学' and gender=1;
  

• 索引跳跃扫描：MySQL8中新增了索引跳跃扫描的功能，当联合索引的其中一列值较少时，即使WHERE中版本号这一列，它后续的列也能使用到联合索引。

  -- 例如现有 user_name和gender创建的联合索引
  -- 如果user_name值很少（很多重复的），gender也能使用到联合索引（未遵守最左匹配原则）
  SELECT id,user_name,gender,age FROM t_person WHERE gender=1;
  

• 索引下推：简称ICP，用于优化数据的查询，在二级索引查找数据时通过过滤掉更多不符合的数据，减少回表次数。

  -- 例如现有 user_name和gender、age创建的联合索引
  -- user_name为小楊同学有两条:（gender=1、age=100）、（gender=1、age=10）
  SELECT id,user_name,gender,age FROM t_person WHERE user_name='小楊同学' AND age=100;
  -- 因为未完全遵守最左匹配原则age未使用到索引
  -- 如果没有索引下推，那么在二级索引查询时会同时查到两条数据，需要回表两次
  -- 但回表查询查询age=10不符合条件，不返回，最终最返回age=100一条数据
  -- 而有了索引下推，就可以在二级索引查完数据后，因为age也是联合索引列，会对数据进行一次过滤
  -- 最终回表就只有age=100这一条数据回表，而age=10不会回表
  

• MRR（Disk-Sweep Multi-Range Read，多范围读取)：针对于辅助索引的回表查询，减少离散IO，并且将随机IO转换为顺序IO，从而提高查询效率。因为索引是以页为单位的，如果回表时数据不在同页里，就会多次回表，直到找到所有数据在不同页上位置。原理是二级索引找到主键值后，先对主键值进行排序，然后在回表查询，就可以减少回表的次数。

  推荐看这篇文章：https://blog.csdn.net/zhenghuishengq/article/details/128273593

索引失效场景

• 使用 联合索引 但未遵守 最左匹配原则（注意：MySQL8会进行索引跳跃扫描，不遵守最左匹配原则也能使用到索引，但尽量避免）

• 索引列使用函数改变了值，与索引记录的值不一致（MySQL8函数索引）

• 索引列进行了计算改变了值，与索引记录的值不一致

  -- id为主键，stock有索引
  EXPLAIN SELECT id,stock FROM t_book WHERE stock = 6 - 1;	-- type为ref
  EXPLAIN SELECT id,stock FROM t_book WHERE stock - 1 = 5; -- type为ALL
  

• LIKE查询用%开头

  EXPLAIN SELECT id FROM t_book WHERE name like '%Java%'; -- type为ALL
  -- 根据搜索的字符串可以确定一个查询的范围，所以是range
  EXPLAIN SELECT id FROM t_book WHERE name like 'Java%';	-- type为range
  

• 使用</<=/>/>=/BETWEEN/LIKE只有后缀查询到的数据太多

• 使用OR的左右列无索引（如果OR的左右列是同个联合索引，同样会失效）

  -- id为主键，stock有索引，author无索引，price和sales为联合索引（为了测试建的）
  EXPLAIN SELECT * FROM t_book WHERE id = 1 OR stock = 6; -- type为index_merge
  EXPLAIN SELECT id FROM t_book WHERE id = 1 OR author = 'aaa'; -- type为ALL
  EXPLAIN SELECT id FROM t_book WHERE price = 20.00 OR sales = 14; -- type为ALL
  

• IN/NOT IN取值范围大（如果IN/NOT IN重复值比较多导致查询的结果很多时）

• IS NOT NULL/!= 或 <>

• WHERE查询的值类型与列类型不一致（隐式转换，例如name类型为VARCHAR，但使用WHERE name=111进行查询）

  关于隐式转换可以看看这篇文章：https://javaguide.cn/database/mysql/index-invalidation-caused-by-implicit-conversion.html

其他

• 使用SELECT *进行查询（）：并不会导致索引失效，而是范围查询过大会导致索引失效。有些情况下可以覆盖索引无需回表但使用*就导致一定会回表。

  -- id为主键
  EXPLAIN SELECT * FROM t_book WHERE id = 10;	-- type为const
  EXPLAIN SELECT * FROM t_book WHERE id >= 10;	-- type为range
  -- stock加了普通索引
  EXPLAIN SELECT * FROM t_book WHERE id stock = 6;	-- type为ref
  explain SELECT * FROM t_book where stock > 100;	-- type为range
  explain SELECT * FROM t_book where stock >= 100;	-- type为range
  -- 范围太大导致失效（查询到的数据太多）
  explain SELECT * FROM t_book where stock > 6;	-- type为ALL
  explain SELECT * FROM t_book where stock >= 6;	-- type为ALL
  

索引建议

• 使用不为NULL的字段建索引
• 被 频繁查询/作为查询条件/用于排序/join连接 的字段建索引
• 被频繁更新的字段不应该建索引
• 限制每张表索引的数量（不超过5条）
• 避免同表中索引的字段相同
• 创建联合索引而非单列索引
• 对字符串类型的字段使用前缀索引
• 删除长期未使用的索引

EXPLAIN

使用EXPLAIN命令可以查看SQL语句的执行计划，从而可以针对SQL语句中存在的问题进行优化。

EXPLAIN SELECT ...; -- 在SELECT语句前加上EXPLAIN

字段名	含义
id	select查询的序列号
select_type	select的查询类型
table	操作的表名
partitions	匹配的分区，未分区的表该值为空
type	表的访问方法
possible_keys	可能用到的索引（不太重要）
key	实际用到的索引
key_len	索引的长度
ref	当使用索引等值查询时，与索引作比较的列或常量
rows	预计查到的行数
filtered	按表条件过滤后，留存的记录数的百分比
Extra	附加信息

• id：select查询的序列号，id从大到小执行，id相同从上往下。
• select_type：select的查询类型
  • SIMPLE：简单查询，不包含 UNION 或者子查询。
  • **PRIMARY：**查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY。
  • **SUBQUERY：**子查询中的第一个 SELECT。
  • **UNION：**在 UNION 语句中，UNION 之后出现的 SELECT。
  • **DERIVED：**在 FROM 中出现的子查询将被标记为 DERIVED。
  • **UNION RESULT：**UNION 查询的结果。
• table：操作的表名，是对哪张表进行查询。
• type：所有值的顺序从最优到最差排序为：system > const > eq_ref > ref（最推荐达到） > index_merge > range（至少达到） > index > ALL（避免）。见的几种类型具体含义如下：
  • system：表里只有一行记录，const 的一种特例，忽略不计平时不会出现。
  • const：对主键(primary key)或唯一索引(unique)进行查询且where只匹配到一行，limit 1不属于这个类型（limit是通过过滤留存）。
  • eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录满足查询条件。
  • ref（最推荐达到）：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
  • index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。
  • range（至少达到）：对索引列进行范围查询（between、>、<、in等）， key 字段可以查看使用到的索引。
  • index：全表扫描，查询遍历了整棵索引树，索引在内存中所以速度比ALL快一些。
  • ALL（避免）：全表扫描，从磁盘中读取。
• **possible_keys：**列出包含select中字段可能用到的索引（不一定使用到）。（不太重要）
• key：实际中使用的索引，如为NULL，则表示未使用索引。
• key_len：索引字段的最大可能字节数长度（并非实际使用长度），不影响结果的情况下越短越好。
• ref：被用于查找索引列上的列或常量。
• rows：预计查到的行数。越少越好。
• filtered：经过搜索条件过滤后剩余记录条数的百分比。
• Extra（重要）：查询的额外信息，几十个值，需要优化的值如下：
  • Using filesort：速度很慢的外部排序（文件排序），没有用到表内索引进行排序（索引排序）。（需要优化）
  • Using temporary：需要创建临时表存储查询结果，常见于 ORDER BY 和 GROUP BY。（需要优化）

日志文件

binlog（归档日志）

表数据发生修改时都会记录修改SQL语句按修改顺序写入到binlog文件里。数据备份、主备、主主、主从都是靠binlog来保证数据的一致性。如果SQL里包含函数（如NOW()）时，会把函数修改成当时的结果数据。事务提交时才进行记录。

• 数据备份/主备/主从：A数据库写binlog，B数据库读binlog同步数据。
• 主主：A数据库写binlog并读binlog其他新数据，B数据库同样。

系统给每个事务分配一块内存作为binlog缓存区（binlog cache），事务提交时把缓冲区里的修改记录同步到binlog文件中，一个事务的操作不能拆分。binlog_cache_size参数控制缓冲区大小，如果事务修改记录大于这个大小，修改记录会暂存到磁盘（Swap）中。

redo log（重做日志）

InnoDB特有的日志，当MySQL崩溃或宕机了，重启时通过redo log就可以对数据进行恢复，保证数据的持久性和完整性。redo log文件有4个，每个都为1G大小，循环记录（满了就换下一个）。MySQL奔溃宕机时恢复记录。事务进行时就不断的写入记录。
缓存池（Buffer pool）：对查询的数据进行缓存，减少云盘IO频率。因为MySQL的数据是以页做单位进行存储的，所以当查询数据时，会先到缓存池里找有无对应数据，有直接返回，没有再去硬盘中找到对应数据的一整页放到缓存池里，然后再返回查询的数据。更新数据时同样，先查然后在缓存池中直接修改数据。
重做日志缓存（redo log buffer）：当缓存池中有数据被修改后，记录数据修改的操作，然后修改记录刷盘（刷新同步）到redo log文件中。
刷盘时机（以下几种情况都会进行刷盘）：

1. 事务提交（默认，可修改）
2. 重做日志缓存（redo log buffer）空间不足时：记录的redo log日志超过总空间的一半
3. 事务日志缓冲区已满
4. Checkpoint（检查点）：InnoDB定期检查，刷盘
5. 后台刷新线程：InnoDB后台线程周期性（1秒）刷盘
6. 正常关闭MySQL

两阶段提交：因为redo log是事务进行就不同写入，需要有个机制判断MySQL崩溃时，数据是否需要回滚。

• prepare（事务未提交）：事务进行中时，记录到redo log文件里的数据状态为prepare，代表事务未提交。当MySQL崩溃恢复时，发现记录的数据为prepare状态，则回滚该事务。
• commit（事务已提交）：当事务提交后，先写入binlog文件，然后再把redo log记录的状态修改为commit。如果在事务提交后MySQL崩溃了，恢复时，判断对应的事务有无binlog记录，如果有，修改redo log记录状态为commit；如果没有，则回滚事务。

undo log（回滚日志）

这部分内容，可以看看我前面写的这篇文章中关于MVCC的内容：https://blog.csdn.net/qq_36903261/article/details/136079124?spm=1001.2014.3001.5501