MySQL学习笔记3——索引

比较常见的索引模型有三种，哈希表，有序数组，搜索树。

哈希表

为了处理多个key经过哈希函数映射到同一个数组位置的问题，解决方法一般是拉出一个链表

如上图，维护一个身份证信息-姓名的表，根据身份证号查找名字。
可以看见2和4两个虽然key值映射到的都是N，因此由此拉出一个链表，按顺序遍历这个链表，直到找到对应值

问题在于，key值并不是递增的，只是单纯的往链表后追加，非有序带来的问题就是查询速度慢
所以，哈希表适用于只有等值查询（筛选出一个字段等于特定值的所有记录）的场景

有序数组

假设是有序排列的，那么查找自然很方便，用二分法即可查询
（见我们之前说的kafka消息查询方式添加链接描述）

但是问题在于，插入与删除的代价太大，只适用于做静态存储
比如保存2020年某个城市的数据，这个数据相较于2022年来说，已经不会再变化了，没有增删的需求。

N叉搜索树

我们按照左小右大的特点，存放数据。查询某个节点的时间复杂度是log（n），且我们需要保持二叉搜索树是二叉平衡树。不断调整位置的时间复杂度同上。

数据量大了之后，肯定不能用二叉树，而是用N叉树。

以InnoDB的一个整数字段索引为例，这个N差不多是1200。这棵树高是4的时候，就可以存1200的3次方个值，这已经17亿了。考虑到树根的数据块总是在内存中的，一个10亿行的表上一个整数字段的索引，查找一个值最多只需要访问3次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

在MySQL中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样。而即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

InnoDB索引模型——B+树

在该模型中，表根据主键顺序以索引形式存放，存储方式为索引组织表。
而数据存放在B+树中。**每一个索引在InnoDB中对应一颗B+树，B+树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。
**

假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。表中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=100;是主键查询，只搜索ID这颗B+树即可。
• 而如果是select * from T where k=1;则是二级索引查询，需要先查K索引树，找到对应的主键为100，再去进行主键查询ID，这个过程就叫做回表

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

B+树索引的维护

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果插入新的行ID值为700，则只需要在R5的记录后面插入一个新记录。如果新插入的ID值为400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。

而更糟的情况是，如果R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。

当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

自增主键的使用
自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOTNULL PRIMARY KEY AUTO_INCREMENT。

插入新记录的时候可以不指定ID的值，系统会获取当前ID最大值加1作为下一条记录的ID值。

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

假设刚才的身份证信息表，是否应该用身份证号做主键？
由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约20个字节，而如果用整型做主键，则只要4个字节，如果是长整型（bigint）则是8个字节。

主键长度越小，普通索引（二级索引）叶子节点（主键的值）就越小，则普通索引占用空间越小
所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

什么情况适合直接用业务字段做主键？ KV场景。

案例——如何避免回表过程

执行 select *fromTwhere k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？

1. 在k索引上得先找到k=3的记录，获取其主键300.
2. 回到ID索引找到主键ID=300对应R3。
3. 同上找到k=5,500,R4.至此找到两个端点
4. 回到K索引找下一个值k=6，不在3 5之间，直接返回

在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？

覆盖索引

上面的案例要找*
索引我们找到ID后要回表去找Rx。
但是如果我们select的是ID。此时直接找索引K，即可找到ID，不需要回表，该索引已经覆盖了我们的查询要求，称为覆盖索引。 ·

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

还是最上面身份证的问题，我们是否有必要建立一个 身份证号-姓名 的联合索引？
我们已经直到身份证号可以唯一确定一个市民，看起来已经够了。如果有一个高频请求，根据身份证号查姓名，该联合索引才有意义，因为这样可以用覆盖索引来避免回表，减少语句执行时间

最左前缀

B+树这种索引结构，可以利用索引的“最左前缀”，来定位记录。
我们用（name，age）这个联合索引来分析。

索引项按照索引定义里出现的字段顺序排序，因此可以查找到某个姓张的人ID是多少，一旦定位到张三，往后数一定能找到。

只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

在建立联合索引的时候，如何安排索引内的字段顺序？

原则1：索引的复用能力。因为可以支持最左前缀，所以当已经有了(a,b)这个联合索引后，一般就不需要单独在a上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

原则2：空间
比如查询条件里面只有b的语句，是无法使用(a,b)这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护(a,b)、(b) 这两个索引。所以，如果有name,age的需求，name字段显然大于age，如果需要单独查age，则需要（name,age）和(age)两个索引

索引下推

上一段我们说到满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录。这时，你可能要问，那些不符合最左前缀的部分，会怎么样呢？

我们还是以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是10岁的所有男孩”。那么，SQL语句是这么写的：

select * from tuser where name like '张%' and age=10 and ismale=1;

根据最左前缀的规则，我们先找到了第一个姓张的人，ID3。
然后我们需要判度其他条件是否满足，
MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

我们来看假如没有索引下推：

我们查到这些姓张的人之后，获取其ID，再回表到主键索引中查他的age。可以看见需要回表4次。

而有了索引下推，只需要回表2次。在（name，age）联合索引内部就判度age是否符合，不符合直接跳过，就不用回表

如何选择普通索引和唯一索引

对于一个普通索引，查找到第一个满足条件的记录后，需要继续往后找下一个记录，直到其不满足条件
而唯一索引只要找到就是唯一的。
但是两者性能差距微乎其微

为什么？
InnoDB读数据并不是按行读的，而是按页为单位读。将一页整体读入内存，默认页大小是16KB。
所以普通索引多出的往下一次找，只是指针移动一次，所以性能上并没有差别。

change buffer：
如果更新操作更新的这一页恰好在内存中，那么InnoDB可以直接更新，如果不在内存中，就缓存这些操作到change buffer中，直到下次从磁盘把这一页读到内存中再执行操作（这个操作叫Merge）。
需要说明的是，这个缓存是可持久化的数据，会被拷贝到磁盘上

什么条件使用change buffer：

• 对于唯一索引，既然要保证唯一，必须要判度插入的内容是否唯一，那就要把数据页读进内存，既然已经读进内存了，没必要使用change buffer。
• 只有普通索引才能用change buffer

所以对于一个插入操作

• 如果要更新的数据在内存中，唯一索引只多了判断索引是否冲突这一步，性能没有影响。
• 而如果不在，唯一索引需要从磁盘中读数据页，这个过程IO成本高，而普通索引同上面情况都只需要记在change buffer里，没有磁盘IO。

总结：
写多读少，用change buffer效果好，如账单，日志
更新多，更新完需要立即查询，change buffer会起到副作用，因为需要额外merge
在实际使用中，你会发现，普通索引和change buffer的配合使用，对于数据量大的表的更新优化还是很明显的。

区别change buffer 和redo log

对于一个操作：

insert into t(id,k) values(id1,k1),(id2,k2)

加入k1在内存中（即在buffer pool），k2不在。

1. page1 在内存中，直接更新内存
2. page2不在，在内存的change buffer区域记下操作
3. 而后把上述两个操作都记录到redo log中

如果在更新完不久就有了读操作，

• 此时如果读page1直接从内存返回，所以WAL（write ahead log）并不一定要读盘 等redo log更新了数据才能返回，虽然磁盘中的数据还没有被更新，但是此时内存里的数据已经是最新的了。
• 如果读page2 ，需要从磁盘里读进内存，然后执行change buffer里对page 2的操作，生成最后正确的版本返回。这里可以体会到我们之前说的直到下一次读取该页，才把该页从磁盘中读入内存，否则只要记在change buffer里就行

redo log 和change buffer的区别：
redo log主要节省随机写磁盘的IO消耗（有了redo log就可以按log顺序写）
change buffer主要节省随机读磁盘的IO消耗