MySQL(三):索引的原理

大纲内容

• 索引的数据结构
• 主键索引，普通索引，唯一索引的区别
• 联合索引
• 前缀索引

读书才能够系统的学习，听别人偶尔的两句断章取义的结论，远远不能够支撑设计一套系统。:
博主建立了一个群, 有兴趣的可以一起一起分享问题, 解决问题, 分享面试题, 内推。

索引的好处：比如一本书的目录，如果存在目录，找某个章节是很快的，如果不存在目录，即一页一页的找对应的章节，此处目录比做索引。

索引的数据结构

哈希表

以key-value的形式存储数据。将key(新增/更新数据)进行hash算法运算，计算出哈希表的索引下标，如果索引下标为空，则直接插入，作为链表的头结点，如果索引下标有值，说明有哈希冲突，则通过尾插法插入结点。

查询操作：先通过hash算法获取哈希表的索引下标，如果索引下标存在，则按链表的顺序从中查询，对索引的key进行依次hash计算就可以定位到数据存储的位置。

缺点：哈希表只能做等值查询，链表中无法判断是否有序，用哈希表做范围查找的效率很慢。

---

有序数组

优点：查询用二分查找，时间复杂度为log(n)，适用于静态存储，不会有新增的操作。

缺点：进行插入时，如果是插入中间元素，会挪动后续的所有数组元素。

---

B+树

索引结构为B+树，它是排好序的数据结构。主键索引中，非叶子结点存储的是索引值，叶子结点存储的是行数据，普通索引中，叶子结点存储的是索引值和主键ID，同时叶子结点间是链表组成。

---

主键索引和普通索引的区别是什么?

非叶子节点：主键索引和普通索引存储的都是索引值。
叶子结点：主键索引存储的是行数据，而普通索引存储的是普通索引值+主键ID，如果用普通索引值进行搜索，会触发回表操作，即多访问一次索引树的次数。如果想减少回表操作，将select * 改为普通索引树中存储的字段即可。

假设普通索引name, 固普通索引树上叶子结点存储的是name值+主键ID

将select * 改为 -->select id，name 可减少回表次数，只要普通索引树上存在的字段查询可减少回表，不存在的字段才需要回表。
如果想拿到更多的字段，同时想减少回表操作，可用联合索引，因联合索引上存储的更多的索引值+主键ID。

---

为什么建议使用自增主键，同时要用整型数据类型?

在MySQL中，char占用3n个字节，varchar占用3n+2个字节，bigint占用8字节，tinyint占用1字节，如果用字符串数据类型，在普通索引树中存储的数量肯定比整型存储的数量要少很多，在内存维度上，用整型数据类型更划算。因为B+树是帮我们排序好的数据结构，如果非自增的数据类型，MySQL会帮我们手动排序，同时会造成页分裂，页分裂会带来拷贝数据集的成本。

• 如果没有设置主键索引，MySQL会帮我们找到第一个普通索引为主键索引。
• 如果没有设置主键索引，并且没有普通索引，MySQL会帮我们设置一个隐式主键索引。

---

主键索引，普通索引，唯一索引的区别

假设有一张用户表user，其中有身份证字段id_card，假设业务给我们做了唯一性校验，当前SQL该如何加索引?

SQL语句：
select * from user where id_card="422112121321322321xx";

查询

主键索引

 不适合，当前id_card是字符串，我们推荐使用整型数据类型，同时支持自增主键，
 字符串varchar占用3n+2个字节，而整型bigint占用8个字节，站在内存维度，
 由于普通索引树中存储的是当前索引值+主键ID，固可以在普通索引树上存储更多的节点。
 B+树的索引是帮我们排好序的数据结构，由于是字符串，固MySQL还要帮我们排序字符串，
 会带来页分裂导致数据集拷贝的风险，额外带来了性能消耗，固主键索引适合是整型且自增的类型。

普通索引

  适合，普通索引叶子节点存储的是当前索引值和主键ID，通过id_card的索引值，
  树的根节点通过二分查找，找到对应的叶子结点，如果找到符合条件的索引值，
  还是会继续往后查询，当遍历到第一个不为当前索引值的节点时，遍历结束，
  将当前值所在的对应的数据页加载进内存中。

特殊场景。(InnoDB在读取磁盘数据文件时，都是以数据页为单位读取到内存中)。

唯一索引

适合，唯一索引有唯一性约束，当遍历到第一个符合当前索引值时，查询便结束，
不会往下记录直至第一个不为当前索引值。

针对于查询而言，普通索引和唯一索引的区别：普通索引会找到第一个不为当前索引值的数据为止，而唯一索引因为有唯一性校验，找到即结束。

---

新增/更新

唯一索引

假设数据页不在内存中，涉及到唯一索引字段的新增/更新，比如name为唯一索引，name=“peter”，
InnoDB会先去磁盘数据文件中找到当前值可能对应的数据页中，并把数据页加载进内存，
在内存中判断是否存在peter字段，如果不存在，则可以插入，如果存在，则抛出异常。

假设数据页在内存中，则判断是否存在冲突，如果没有冲突，则插入。

• 由于唯一性校验，唯一索引进行了一次磁盘IO，把数据页加载进内存中。

普通索引

假设数据页不在内存中，开启change buffer，可以减少磁盘的读操作
将当前新增/更新操作记录到change buffer中。

假设数据页在内存中，则直接新增/更新到对应的位置中。

针对于新增/更新而言，假设数据页不在内存中，唯一索引会去磁盘文件中加载对应的数据页，进行唯一性校验，而普通索引会因为有change buffer的开启带来减少一次磁盘的读操作，将更新/新增记录先记录至change buffer中。

---

什么是change buffer?

change buffer：只针对于普通索引有效，同时数据页不在内存中。

• 如果数据页在内存中，若是唯一索引，则直接进行唯一性校验，若不存在冲突，则直接DML操作，若是普通索引，则直接DML操作。
• 如果数据页不在内存中，针对于普通索引而言，会把对应的新增/更新操作都记录到change buffer中去，目的是为了减少磁盘读操作，提高操作效率，因为读取磁盘是最消耗时间成本之一。

可以理解：change buffer就是一个伪数据页。

change buffer的适用场景

写多读少的场景，涉及到读，还是会去查询磁盘中对应的数据页，加载进内存中，与伪数据页(change buffer)进行对比，会造成merge操作，将最新结果集返回给执行器。如果在写少读多的场景，涉及到读还是将查询到的数据页加载进内存中，无法发挥change buffer的优势，同时还带来维护成本，change buffer的优点就是为了在写操作时避免去读磁盘操作。

存储在change buffer中的文件，何时同步进磁盘?
在查询时，会将老数据页与新数据页进行merge操作，同步进磁盘中，在系统空闲时，会同步进磁盘中，在数据库正常关闭时，会同步进磁盘中。

个人理解：这个同步进磁盘中，都是同步进redo log文件中，最终redo log会flush进磁盘数据文件中，因为没有看过源码, 我初步猜想，redolog是避免了频繁写磁盘，而change buffer是避免了频繁读磁盘，redolog中存储的数据页和change buffer，change buffer中存储不在内存中的数据页，都是未同步进磁盘的脏页数据，而change buffer页属于一种伪数据页，最终数据都是被redolog给flush进磁盘数据文件中。

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

站在k是普通索引的维度来分析，k1所在的数据页D1在内存中，k2所在的数据页D2不在内存中。

1：因为k1所在的数据页D1在内存中，固直接在D1中按从值的小到大，顺序插入。

2：k2的数据页不在内存中，此时又开启了change buffer，会减少磁盘读操作，将新增操作先记录到伪D2中，此时内存中有一个changebuffer，有一个真数据页。

3：将上述一致同步进redolog中，最终事务提交，等系统空闲时同步进数据磁盘中。

结论：同步进change buffer中的数据，通过redo log日志来写入磁盘。
此时 select * from user where k in (k1，k2)
只有真的在查询D2数据页时，才会将数据页加载进内存中，执行引擎每次读取磁盘，都是以数据页为单位加载进内存中。
1：读D1的时候，由于D1在内存中，所以直接从内存中返回。
2：读D2的时候，由于内存中存储的是伪数据页change buffer，此时读磁盘时，会拿原数据页和伪数据页进行merge，返回最新数据给执行器。

change buffer和redolog有什么关联吗?

redolog中包括了change buffer和数据页，redolog是为了避免频繁写磁盘，而change buffer有利于普通索引的修改/新增，目的是为了避免频繁读磁盘。

如果没有change buffer, 正常的流程如下

1：先读取该值所在的数据页，加载进内存中，在内存中找到对应的行，进行修改操作，同时生成undolog日志版本链。

2：再将修改的操作写进redolog中，当前redolog处于prepare状态，再写入binlog中，再修改redolog状态为commit状态。

3：等空闲的时候，内存中的数据同步进数据磁盘文件中，同步checkPoint redolog文件。

什么时候用change buffer?

对于唯一索引，所有的更新操作都要先判断是否违反唯一性约束，比如要插入一条name =“peter”，当前name是唯一索引，就要判断name="peter"是否已经存在，所以查询之前，必须要磁盘IO一次加载数据页进内存中，既然存在数据页，还要change buffer有什么用?

对于普通索引，change buffer能避免频繁读操作，change buffer用的是buffer bool的内存，一般设置为buffer pool的40%。

---

联合索引

假设当前联合索引：
KEY 'idx_name_age_position'('name','age','position')    姓名-年龄-环境
等同于建立了（‘name’），（‘name’，‘age’），（‘name’，‘age’，‘position’）三个索引。

有人可能会问: 为什么不直接建立三个索引(‘name’),(‘age’),(‘position’)三个索引呢？维护索引也是需要成本的，站在数量维度分析，能维护一个索引，为什么要维护三个索引，同时联合索引存在更大几率较少回表，可选的字段更多。

联合索引的最左前缀原则

可以是联合索引的最左的N个字段, 也可以是首个name索引的最左N个字符。

• 必须遵循最左边的顺序索引元素。比如顺序name，age，position。当然优化器可以帮我们解决排序问题，当用到age，name时，MySQL能帮我们排序称name，age两个索引，当用name，position，中间断开了，不遵循最左前缀原则，只能用到name索引。
• 建立联合索引时，要考虑到复用能力，能用联合索引就用联合索引，若必须要用age搜索，则单独给age加一个索引字段。
• 在覆盖索引中，最左边第一个索引元素一定能保证当前索引树中是有序的，如果不遵循最左前缀原则，索引是会失效的。若优化器能顺序优化对应的字段，比如age，name，优化成name，age，也是可以走索引的。

如果没有遵循最左前缀原则的数据, 会怎么处理呢?

select * from user  where name like "peter%" and age=10 and position="dev";

当联合索引的第一个字段用了范围查询，即只会走第一个索引。

在无索引下推的版本，会查询到name=peter1，peter2，peter3然后一条一条的回表，在MySQL5.6版本之前，都是根据name like "peter%"条件一条一条的回表查询，然后判断对比，此时回表了三次。

索引下推：在索引遍历中，先对索引中所有的字段先进行判断，过滤掉不符合条件的数据，然后再进行回表，目的是为了减少回表的次数，此时回表了1次。

为什么like有索引下推，而范围查询没有?

个人理解：like已经帮我们的范围给缩小了，而MySQL会觉得范围查询的数据集太大了。

---

前缀索引

如果没有加索引，MySQL会走全表扫描，MySQL支持给字符串加前缀索引。

默认创建的字符串索引是没有指定长度的，默认包含整个字符串。

alter table user add index idx(name)
-- 若是peter，则索引树上的值是peter

alter table user add index  idx(name(3))
-- 若是pter，则索引树上的值是pet

优点：字符串索引使用索引前缀，可以在索引树上存储更多的数据，因为前缀索引占用内存会相当于小，但感觉没有什么实际用处。

缺点：增加回表的次数，使用前缀索引无法用到覆盖索引对查询的优化功能。