《MySQL实战45讲》笔记-索引相关《MySQL实战45讲-学习笔记》InnoDB索引相关索引模型、索引概念、优化技

索引的作用

提高数据查询的效率，就像书的目录；

索引的常见模型

哈希表、有序数组、搜索树

假设维护了一个身份证信息和姓名的表，需要根据身份证号查找对应的名字。基于上述场景需求，分别用哈希表、有序数组、搜索树三种索引模型来示意。

（一）哈希表

哈希表也叫散列表，是根据关键字和值（Key-Value）直接进行访问的数据结构。

哈希表的关键思想是使用哈希函数，将键 key 和值 value 映射到对应表的某个哈希槽中。

不同的关键字通过同一个哈希函数可能得到同一哈希地址，即 key1 ≠ key2，而 Hash(key1) = Hash(key2)，这种现象称为哈希冲突。

哈希冲突的处理方式：

开放地址法：将哈希表中的「空地址」向处理冲突开放。当哈希表未满时，处理冲突时需要尝试另外的单元，直到找到空的单元为止；
链地址法：将具有相同哈希地址的元素（或记录）存储在同一个线性链表中（常用）

了解了什么是哈希表，以及哈希冲突处理等概念，就很容易理解 "根据身份证号查找对应的名字" 场景的哈希索引结构了：

对身份证号（key）做哈希函数计算，取余存入对应的哈希槽位置；
哈希槽相同的key就会产生哈希碰撞（哈希冲突），碰撞后就会挂在同一链表上，即：链地址法处理哈希冲突；
查找时先通过key找到哈希槽，再遍历链表找到对应key匹配的值（链表无序）；

链表元素是无序的，优点是链表元素往后追加速度快，缺点是链表无序做区间查询速度慢。

综上，哈希索引适用于 只有等值查询 的场景

（二）有序数组

有序数组是一种特殊数组，里面的元素按一定顺序排列：

因为数组"有序"，相较于哈希表，对于范围查询场景有优势；
删除和插入场景，需要挪动其他记录来保证数组有序，成本较高；

综上，有序数组适用于 静态存储引擎（只查不更新）、适用于 等值查询和范围查询 的场景。

"根据身份证号查找对应的名字" 场景的有序数组结构如下：

（三）搜索树

搜索树通常分为二叉搜索树（二叉树）和多路搜索树（N 叉树）。

"根据身份证号查找对应的名字" 场景的二叉树结构如下：

InnoDB 的索引模型（B+树索引）

InnoDB 存储引擎使用 B+树索引模型。

（一）索引组织表

在 InnoDB 中，表是根据主键顺序以索引的形式存放的，这种存储方式的表称为 索引组织表。

（二）B+树索引类型

主键索引（聚簇索引）
非主键索引（非聚簇索引/二级索引）
- 普通索引/非唯一索引
- 唯一索引

主键索引是一种特殊的唯一索引

（三）B+树索引的维护

B+树为了维护索引的有序性，需要页分裂和页合并等索引维护。如下案例详细说明什么是页分裂、页合并？


mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

1、页分裂

假设需要新增一条记录：

如果新增记录的ID值为700，只需要在R5的记录后面插入一个新记录；
如果新增记录的ID值为400，则需要逻辑挪动后面的数据，空出位置：
- 如果R5所在的数据页未满，挪数据空位置即可；
- 如果R5所在的数据页已经满了，需要申请一个新的数据页，挪出部分数据过去，这个过程就是页分裂。

页分裂操作不仅影响性能，还影响数据页的利用率。原先放在一个页的数据现在分裂到两个页，整体空间利用率降低了大约50%

2、页合并

当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并，也就是页合并。

合并的过程可以理解为分裂过程的逆过程。

（四）二级索引SQL查询流程

如下初始化表数据：

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

select * from T where k between 3 and 5语句执行流程：

在 k 索引树上找到 k=3 的记录，取得 ID = 300；再到 ID 索引树查到 ID=300 对应的 R3；
在 k 索引树取下一个值 k=5，取得 ID=500；再回到 ID 索引树查到 ID=500 对应的 R4；
在 k 索引树取下一个值 k=6，不满足条件，循环结束。综上，这个查询过程读了 k 索引树的 3 条记录，回表了2次。

上例描述的查询过程根据二级索引的ID值逐个回表，实际上MySQL5.6有个新特性：MRR（Multi-Range Read），是优化器以空间换时间，把随机 IO 转化为顺序 IO，以降低查询过程中IO开销。

在不使用 MRR 时，优化器需要根据二级索引返回的记录来进行逐个"回表",这个过程一般会有较多的随机IO, 使用MRR时，SQL语句的执行过程是这样的：

1）根据where条件中二级索引，获取二级索引和主键的集合，并将结果集放在buffer里（read_md_buffer_size），直到扫描完成或buffer满了；

2）对buffer中结果集按照主键排序；

3）按排序后的结果集，访问主键索引数据，此时是顺序IO；

MySQL查询优化

（一）索引下推（ICP）

索引下推（Index Condition Pushdown,简称ICP）是5.6的新特性，能减少回表查询次数，提升检索效率。

ICP只对二级索引使用，主键索引用不到。

数据库对where中过滤条件的处理，根据索引使用情况分成了三种：

index key: 用来确定SQL查询在索引中的连续范围的查询条件；
index filter: 在确定的索引范围内、可使用索引进行过滤的查询条件；
table filter: 无法通过索引过滤、只能访问table进行过滤的查询条件；

在 MySQL5.6 之前：并不区分 Index Filter 与 Table Filter ，统统将索引范围内的索引记录，回表读取完整记录，然后返回给MySQL Server层进行过滤。

MySQL 5.6开始：Index Filter 与 Table Filter 分离，Index Filter下降到InnoDB的索引层面进行过滤（即: ICP特性），减少了回表与返回MySQL Server层的记录交互开销，提高了SQL的执行效率。

ICP（索引下推）就是对 index filter 技术的实现。

之所以叫索引“下推”，是因为MySQL的架构分为server层和引擎层两层，ICP特性将原来在server层进行的table filter中可以进行index filter的部分，在引擎层面使用index filter进行处理，不再需要回表进行table filter。

（二）二级索引合并回表（MRR）

MySQL5.6中引入了MRR，用来优化二级索引回表的情况。

MRR 仅仅针对 二级索引的范围扫描 和 使用二级索引进行 join 的情况

MRR的原理是：将多个需要回表的二级索引根据主键进行排序，然后一起回表，将原来的回表时进行的随机IO，转变成顺序IO

MRR 的优势是将多个随机IO转换成较少数量的顺序IO。所以对于 SSD 来说价值还是有的，但是相比机械磁盘来说意义小一些。

（三）索引合并（Index Merge）

使用多个索引来完成一次查询的执行方法称为 索引合并（Index Merge）, 从5.1开始就引入了index merge优化技术。

如果where条件中涉及多个索引字段，且它们之间进行AND或OR，此时就可能使用到index merge

index merge只能合并同一个表的索引扫描，不能合并跨表扫描，合并可能生成基础扫描结果的"并集"、"交集" 或 "交集的并集"。

index merge算法根据合并算法的不同分成了三种（explain执行计划可看）：

intersect（index intersect merge）: 多个条件之间进行的是 AND 运算;
union（index union merge）: 多个条件之间进行的是 OR 运算;
sort_union（index sort_union merge）: 多个条件扫描进行 OR 运算，但是不符合union算法的，此时可能会使用sort_union算法;

（四）写缓冲（change buffer）

change buffer对普通索引的更新过程有加速作用: 对于数据页未加载在内存的普通索引更新操作，会先将更新操作记录在 change buffer 中，这样就不需要从磁盘读入这个数据页，减少了 IO 操作，提高了性能。

唯一索引用不到change buffer，因为唯一索引所有的更新操作前都需要加载相关数据页到内存，判断是否违反唯一性约束。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

将change buffer的操作应用到原数据页，得到最新结果的过程称为 merge，触发策略如下：

等下次查询需要加载这个数据页到内存时，触发merge操作；
系统后台线程定期merge；
数据库正常shutdown过程中执行merge；

事务提交时会把change buffer的操作也记录到redo log里，因此服务崩溃恢复也不会丢失change buffer和数据。

问题讨论

1、基于主键索引和二级索引的查询有什么区别？

二级索引查询可能需要回表。

根据二级索引找到ID，再通过ID到主键索引树上找其他数据，这个过程叫回表。

2、页分裂有什么影响？

性能有影响：SQL语句若若触发页分裂，需要申请一个新数据页，再挪部分数据到新数据页上，这个过程对SQL性能有影响；
数据页利用率有影响：原本放一个数据页的数据，分裂到两个数据页，整体空间利用率降低50%

自增主键可防止主键索引的页分裂：自增主键是递增插入的场景，每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

二级索引的页分裂不可避免。

3、页合并有什么影响？

4、哪些场景推荐使用自增主键？哪些场景不推荐？

使用自增主键的好处：

性能方面：自增主键保证有序插入，可以防止主键索引的页分裂；
存储方面：主键长度越小，二级索引的叶子节点越小，二级索引占用的空间也就越小；

推荐使用自增主键的场景：

非KV场景推荐使用自增主键

不推荐使用自增主键的场景：

KV场景不推荐使用自增主键，即：只有一个唯一索引的表

5、InnDB存储引擎为什么选择B+树索引模型？

B+树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。

6、MySQL/InnoDB在减少IO开销方面的设计？

InnoDB采用B+树索引模型，层高较低，可减少单次查询的磁盘IO次数；
MySQL 5.6引入的MRR特性，把随机IO转换为顺序IO，降低查询过程的IO开销；
MySQL 5.6引入的索引下推（ICP）特性，减少回表次数；
覆盖索引、前缀索引等

7、比较`in`、`between .. and..` 和 `>=..<=` 三者差异，推荐哪种？

举例：

1、select * from T where k in(1,2,3,4,5) 
2、select * from T where k between 1 and 5
3、select * from T where k >=1 and k <=5

分析：

in(1,2,3,4,5) 内部的数字是枚举的，是否连续、是否有序未知，需要一个一个去看，等价多个union all等值查询，即：需要树搜索5次、回表5次；
between 1 and 5 是一个已知的范围查询，只需定位第一个值，后面通过指针遍历就行了，即：只需要树搜索1次、回表5次；
k >=1 and k <=5 效果同 between 1 and 5 一样

between..and..相当于>=..<=的一种语法糖，mysql内部会翻译成>=..<=

8、`alter table T engine=InnoDB` 重建表原理

创建一个临时表，扫描主键索引得到行数据，根据数据页生成B+树,存储到临时文件中(此时页中数据变的紧凑,不会存在空洞)；
新数据写入原表的同时记录到redo log中（保证拷贝过程数据不丢失）；
临时文件生成完成后，再将redo log操作到临时文件中；
临时文件替换为原表的数据文件；

整个过程是online，获取MDL写锁 --> 降级为MDL读锁 --> DDL --> 升级为MDL写锁 --> 释放MDL锁

9、假设where条件里有2个索引字段同时等值查询，优化器如何选择？

假设有index_a(a)、index_b(b)两个单索引，查询语句select * from T where a=.. and b=.. 优化器阶段会选择哪个索引，是否会两个索引都走？

三种可能：

只选index_a索引, 然后用b列过滤；
只选index_b索引, 然后用a列过滤；
同时选index_a和index_b，然后两个单独跑出来的结果取交集；

优化器选择的策略是数据分布，第三种同时选2个索引的情况是 index merge 特性

10、InnoDB中主键索引和唯一索引的区别

主键索引是特殊的唯一索引，唯一索引不一定是主键索引
主键不允许为空值，唯一索引列允许为空；
一个表只有一个主键索引，但可以有多个唯一索引；
主键可以被其他表引用为外键，唯一索引列不可以；
主键索引就是表数据（索引组织表），而唯一索引是表的容于数据结构；

11、InnoDB中普通索引和唯一索引的性能差异

查询操作：

普通索引：先查到满足条件的第一个记录，然后查找下一个，直到不满足条件；
唯一索引：只要查到满足条件的第一个记录，就会停止继续检索；

InnoDB是以页为单位从磁盘加载数据到内存的，因此对于普通索引，多做的一次查找和判断一般在内存中操作，性能消耗可以忽略。

更新操作：

普通索引：
- 若记录在内存中：直接更新记录；
- 若记录不在内存中：更新操作记在 change buffer 中即可；
唯一索引：
- 若记录在内存中：判断唯一性冲突 + 更新记录；
- 若记录不在内存中：加载数据页到内存 + 判断唯一性冲突 + 更新记录；

12、MySQL优化器为什么会选错索引？如何处理？

选择哪个索引是由Server层的优化器决定的，优化器选择索引的目的: 找到一个最优的执行方案，并用最小的代价去执行语句。

"代价"因素包括：扫描行、是否使用临时表、是否排序、是否回表等等。

知道了影响优化器决策的因素，也就知道了优化器选错索引的原因：

索引基数相关的统计信息不准（show index命令、cardinality列值），导致扫描行预估偏差
有临时表、排序、回表等其他成本干预

选错索引的处理方式：

force index方式强制选择一个索引
修改SQL，引导优化器使用期望的索引
新建更合适的索引
删掉误用的不必要索引

13、MySQL如何统计索引基数？为什么会统计不准？

通过show index from T语句，显示的Cardinality列值，就是索引列的唯一值的个数，如果是复合索引就是唯一组合的个数，即：索引基数

MySQL通过采样统计方式统计索引基数，而非把整张表取出来一行行统计（这样虽然结果精确但是代价太高），统计信息很容易不准。

采样统计策略：

选取N个数据页，统计每个数据页上不同值的个数，然后得到平均每个数据页上不同值的个数，再乘以这个索引的页面数，就得到这个索引的基数。
当数据变更的行数超过1/M时，会自动触发重新做一次索引统计。

这里的N、M值和innodb_stats_persistent 参数有关：

innodb_stats_persistent=on：统计信息会持久化存储，默认的 N 是 20，M 是 10

innodb_stats_persistent=off：统计信息只存储在内存中，默认的 N 是 8，M 是 16

参考链接：

time.geekbang.org/column/arti… blog.csdn.net/b1303110335… www.bbsmax.com/A/kPzO8qoJx…

《MySQL实战45讲》笔记-索引相关