MySQL 索引

什么是索引

索引是一种能够提高数据库查询效率的数据结构，MySQL 中默认使用 B+ 树索引

索引的分类

从数据结构来分

B+ 树索引

MySQL 默认使用 B+ 树索引，B+ 树是一种多叉树，非叶子节点存储索引，叶子节点存储数据，每个叶子节点之间通过双向链表相连接，从而支持范围查找；

Hash 索引

Fulltext 全文索引

从物理存储角度分

聚簇索引

使用记录主键值的大小进行记录和页的排序；
B+ 树的叶子节点存储的是完整的用户记录

非聚簇索引（二级索引）

使用 c2 列的大小进行记录和页的排序；
各个存放用户记录的页也是根据页中的记录的 c2 列的大小顺序排成一个双向链表；
B+ 树的叶子节点上记录的不再是完整的用户记录，而是 c2 列 + 主键值；
目录项记录中不再是主键 + 页号，而是 c2 列 + 页号；

联合索引

我们可以使用以多个列的大小作为排序规则，同时为多个列建立索引，例如我们以 c2 列和 c3 列建立索引

各个记录和页先按照 c2 的大小进行排序，若 c2 列的值相同，则按照 c3 列的值进排序；
B+ 树的叶子节点有 c2 + c3 + 主键 组成；
B+ 树的非叶子节点由 c2 + c3 + 页号组成；

从字段特性来分

主键索引

默认把主键的字段设置为主键索引，该字段为非空且不可重复；

唯一索引

把有 unique 修饰的字段设置为唯一索引，该字段可以为空但不能重复；

普通索引

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE

CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

索引失效的场景

使用了左或左右模糊匹配会使索引失效，使用右模糊匹配才不会让索引失效，"abc%";
对索引列进行了一些计算的操作；
联合索引没有遵循最左匹配原则；
使用 or 连接符时，如果在 or 前的条件列是索引列，而在 or 后的条件列不是索引列，那么索引会失效。

索引的特性

页分裂

假如某个数据页中记录的聚簇索引的记录已经满了

现在加入一条 data 为 4 的记录

这时就要把本页中的一些记录移动到新的数据页中去，造成性能的损耗，导致有大量的内存碎片，索引结构不够紧凑，这就是要求主键尽量递增加入的原因，减少页分裂带来的损耗；

最左匹配原则

对于一个联合索引 index(a, b, c)，只有依次从最左的索引列开始匹配，才能使用该联合索引。

符合的情况：index(a)，index(a,b)，index(a,b,c)

不符合的情况：index(b)，index(c)，index(b,c)

回表操作

当我们根据 非聚簇索引 进行查询的时候，查询到一些不在该索引中的一些字段时，就会发生回表操作，根据非聚簇索引中得到的主键值，根据主键索引再次查找一次。

因为非聚簇索引中叶子节点的值为：索引值 + 主键值

覆盖索引

为了避免回表操作带来的性能损耗，查询的列表中只包含索引列，即索引中已经包含需要读取的列的查询方式称为覆盖索引；

create index on user(name, age)
select name, age from user where name = "lwj"

MySQL 为什么采用 B+ 作为索引的数据结构

满足两点要求

减少磁盘的 I/O 次数，即树的高度要低；
支持高效的范围查询

从数据结构的角度出发

相对于二叉树、二叉搜索树、AVL树等，B+ 树的一个节点上可以存储更多的数据，而这些树只能存储一个数据

相对于 B 树来说，B 树的每个节点存放着数据 + 索引，当数据所占用的空间比索引的空间大时，那么 B 树的高度就会增加，而 B+ 树的叶子节点存放的是数据，非叶子节点存放着索引，从而一个非叶子节点可以存储很多的索引，从而 B+ 树的高度就减少，显得 "矮胖"；

同时，B+ 树的叶子节点之间形成 双向链表，支持高效的范围查询

从磁盘 I/O 的角度出发

磁盘读取的最小单位是扇区，每个扇区的大小只有 512B ，操作系统一次会读取多个扇区，所以操作系统读写的最小单位是块

B+ 树的高度较低，读取一个节点的数据大约进行 2~3 次的 I/O 操作，而其他的数据结构的高度较高，会进行多次的 I/O 操作，性能较低；

若 B+ 树叶子节点的内存大小与操作系统中块的大小一致，则可以一次读取完整的页面；

MySQL中的索引