MySQL：索引

索引的定义以及好处

MySQL的索引是一种数据结构，类似于书籍的目录，它可以帮助数据库高效的查询和更新数据。

如果查询的时候，没有用到索引就会全表扫描

如果用到了索引，查询的时候，可以基于二分查找算法，通过索引快速定位到目标

MySQL索引的数据结构采用的是B+树，搜索复杂度为O(logdN)，d表示节点允许的最大子节点个数为 d 个。

MySQL中的索引是怎么实现的？为什么要采用B+树

1. 如果使用HASH算法，时间复杂度是O(1)，非常快，但是不支持范围查找，不采用
2. 二叉搜索树，中序遍历是一个有序数组，但是在最坏的情况下——单边树，时间复杂度是O(N),并且节点个数过多是无法保证树高，在检索数据是，访问某个节点的子节点都会发生一次磁盘的IO，，频繁的进行IO，性能低
3. N叉树，解决了树高的问题，提升了效率，但是还不够好，因为非叶子节点存储了数据和索引
4. B+树，常用与数据库和文件系统的平衡查找树，特点是非叶子节点仅存放索引，不存储数据，所有叶子节点都是真实数据，叶子节点通过引用构成一个有序链表，都在同一层，插入与修改有较稳定的时间复杂度

B树和B+树的区别

1. 在B+树中，数据都存储在叶子节点，非叶子节点仅存储索引信息；而B树的非叶子节点既存储索引也存储部分数据
2. B+树的叶子节点使用链表连接，便于顺序访问和范围查找
3. B+树的查找性能更稳定，每次查找都在叶子节点中，而B树的查找可能在非叶子节点中，性能相对不稳定

B+树的好处是什么？

MySQL 默认的存储引擎 InnoDB 采用的是 B+ 作为索引的数据结构，原因有：

1. B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。
2. B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；
3. B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

MySQL数据页

页是内存与磁盘交互的最小单元，默认大小为16KB

数据页是用来存储数据和索引，包含页头、页尾、数据行

页文件头和页文件尾包含的信息

重要的是上一页号和下一页号，通过这两个属性可以把页与页之间链接起来，形成⼀个双向链表。

页主体
保存真实数据的主要区域，每当创建一个新页，都会自动创建一个最小行(Infimun)和最大行(Supremun),这两个行并不存储任何真实信息，而是作为数据行链表的头和尾，第⼀个数据行有⼀个记录下一行的地址偏移量的区域 next_record 将页内所有数据行组成了⼀个单向链表，此时新页的结构如下：

当向⼀个新页插入数据时

页目录

从头查到尾，效率低，为了提高查询效率，InnoDB采用二分查找来解决查询效率问题
在每⼀个页中加入⼀个叫做页目录Page Directory 的结构，将页内包括头行、尾行在内的所有行进行分组，约定头行单独为⼀组，其他每个组最多8条数据，同时把每个组最后⼀行在页中的地址，按主键从小到大的顺序记录在页目录中在，页目录中的每⼀个位置称为⼀个槽，每个槽都对应了⼀个分组，⼀旦分组中的数据行超过分组的上限8个时，就会分裂出⼀个新的分组；

后续在查询某行时，就可以通过二分查找，先找到对应的槽，然后在槽内最多8个数据行中进行遍历即可，从而大幅提高了查询效率，这时⼀个页的核心结构就完成了；

计算三层树高的B+树可以存放多少条记录

• 假设⼀条用户数据大小为1KB，在忽略数据页中数据页自身属性空间占用的情况下，⼀页可以存16条数据

• 索引页⼀条数据的大小为，主键用BIGINT类型占8Byte，下⼀页地址6Byte，⼀共是14Byte，⼀个索引页可以保存 16*1024/14 = 1170 条索引记录

• 如果只有三层树高的情况，综合只保存索引的根节点和二级节点的索引页以及保存真实数据的数据页，那么⼀共可以保存 1170117016 = 21,902,400 条记录，也就是说在两千多万条数据的表中，可以通过三次IO就完成数据的检索

索引的分类

1. 主键索引
   • 当在一个表上定义⼀个主键 PRIMARY KEY 时，InnoDB使⽤它作为聚集索引。
   • 推荐为每个表定义⼀个主键。如果没有逻辑上唯⼀且非空的列或列集可以使用主键，则添加⼀个自增列
2. 普通索引
   • 最基本的索引类型，没有唯⼀性的限制。
   • 可能为多列创建组合索引，称为复合索引
3. 唯⼀索引
   • 当在⼀个表上定义⼀个唯⼀键 UNQUE 时，自动创建唯⼀索引。
   • 与普通索引类似，但区别在于唯⼀索引的列不允许有重复值。
4. 全文索引
   • 基于文本列(CHAR、VARCHAR或TEXT列)上创建，以加快对这些列中包含的数据查询和DML操作
   • 用于全文搜索，仅MyISAM和InnoDB引擎支持。
5. 聚集索引
   • 与主键索引是同义词
   • 如果没有为表定义 PRIMARY KEY, InnoDB使用第⼀个 UNIQUE 和 NOT NULL 的列作为聚集索引。
   • 如果表中没有 PRIMARY KEY 或合适的 UNIQUE 索引，InnoDB会为新插入的行生成⼀个行号并用6字节的 ROW_ID 字段记录， ROW_ID 单调递增，并使用ROW_ID 做为索引。
6. 非聚集索引
   • 聚集索引以外的索引称为非聚集索引或二级索引
   • 二级索引中的每条记录都包含该行的主键列，以及而级索引指定的列。
   • InnoDB使用这个主键值来搜索聚集索引中的行，这个过程称为回表查询
7. 索引覆盖
   • 当⼀个select语句使用了普通索引且查询列表中的列刚好是创建普通索引时的所有或部分列，这时就可以直接返回数据，而不用回表查询，这样的现象称为索引覆盖

索引失效

索引失效有哪些？

1. 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
2. 当我们在查询条件中对索引列使用函数，就会导致索引失效。
3. 当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。
4. MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
5. 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
6. 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

索引的优缺点？

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

1. 需要占用物理空间，数量越大，占用空间越大；
2. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
3. 会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。

所以，索引不是万能钥匙，它也是根据场景来使用的。