MySQL索引

一、概念

简单说一下索引，它是在存储引擎中实现的帮助MySQL高效获取数据的数据结构。

它的目的是为了快速查询和检索数据。索引中包含着对数据表里所有记录的引用指针，使用索引可快速访问数据库表中的特定信息

二、优缺点：

优点：

1）建立索引可以提高数据检索的效率，降低数据库的IO成本；

2）通过创建唯一索引，可以保证数据库表中每一行数据的唯一性；

3）可以加快表与表之间的连接，例如联合查询有依赖关系的子表和父表(外键)，可以提高查询速度；

4）在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

缺点：

1）创建和维护索引需要耗费时间，随着数据量的增加，耗时也会增加；

2）索引需要占据额外的磁盘空间；

3）会降低更新表的速度，增删改表需要动态维护索引。

总之，享受索引带来的便捷的同时，还要承担索引的代价。

• 空间上：每个索引都是一个B+树，由许多页组成，不只是包括实际的数据，还有目录项数据
• 时间上：每次增删改操作都需要重新修改各个B+树索引。需要修改叶子结点的双向链表以及内节点的单向链表，需要额外的时间进行记录移位、页面分裂、页面回收等操作，且一旦聚簇索引改变， 所有非聚簇索引都会跟着变

三、索引的分类

（一）按照物理实现方式：

1、聚合索引

介绍：根据主键进行构建的一种数据存储方式(所有的用户记录都存储在叶子结点)

特点：

聚簇索引就是满足一定特性的B+树，需要满足的特性包括：

1）单页内的记录是根据主键大小顺序排成单向链表；

2）底层数据页间是根据用户记录的主键大小顺序排成的双向链表；

3）同一层的目录项记录页也是根据目录项记录的主键大小排成双向链表；

4）B+树的叶子结点存储的是完整的用户记录。

聚合索引的索引结构和数据在一个文件中存放，数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同。

对InnoDB来说，数据文件跟索引文件全部放在 ibd 文件中，包含聚簇索引，其主键索引是聚集索引，二级索引是非聚集索引；而对MyISAM来说，索引和数据分别放在myd和myi文件中，不包含聚簇索引；

优缺点：

优点：

1）数据访问更快：索引和数据保存在同一个B+树中，比非聚簇索引访问更快；

2）针对主键的排序查找和范围查找，速度更快；

3）节省IO操作：在进行范围查找时，因为数据都是紧密相连的，数据库不需要从多个数据块中提取数据。

缺点：

1）插入速度严重依赖于插入顺序：不按照主键顺序进行插入，会出现页分裂，严重影响性能——对InnoDB来说，一般采用自增ID作为主键避免此类情况；

2）主键更新代价高：当主键更新后，更新的行要进行移动，影响性能——对InnoDB，一般定义主键不可更新来避免；

3）二级索引访问需要两次索引查找：需要先在目录项记录表中找到主键值，再根据主键值到数据表中找到行数据。

另：

除以上内容之外，还需要注意：

1）InnoDB支持聚簇索引，MyISAM不支持；

2）因为数据的物理存储排序方式只能存在一种，所以每张表只能有一个聚簇索引(一般根据主键进行创建)；

3）当没有定义主键时，InnoDB会自动选择非空的唯一索引代替，如果还没有则会隐式创建一个6byte的自增主键；

2、非聚合索引

背景：

因为聚簇索引的优势只在搜索条件是主键时才会发挥作用，但实际场景中，以其他列作为搜索条件，则无法使用。此时就需要再创建非聚簇索引。

介绍：

非聚合索引是一种索引结构和数据分开存放的索引，其索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引

优缺点：

优点：更新代价比聚集索引小；

缺点：依赖于有序的数据；可能会二次回表；

回表操作：

当查询到主键时，已经满足查询条件，则直接返回；如果还需要其他数据记录，则需要使用主键到聚簇索引中再查一遍，该过程就是回表。

和聚簇索引的区别：

1）叶子结点存储的不是完整的数据记录，而是“索引列+主键”，而非叶子节点，实际为了保证唯一性，非叶子结点的格式也为“索引列的值+主键值+页号”；

2）一张表只能有一个聚簇索引，但是可以有多个非聚簇索引；

3）查询操作聚簇索引效率更高，但增删改操作，非聚簇索引效率比聚簇索引高。

（二）按照功能逻辑：

1、普通索引

创建时不需要附加任何限制条件，只是用于提高查询效率。其是否唯一和非空需要通过其字段本身的完整性约束条件决定。

2、唯一索引

使用UNIQUE参数设置，可以保证数据记录的唯一性。

3、主键索引

特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，即NOT NULL+UNIQUE，一张表最多有一个

4、全文索引

• 通过建立倒排索引，可以极大的提升检索效率，解决判断字段是否包含的问题， 是目前搜索引擎使用的一种关键技术
• 全文索引适合大型数据集，对查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。
• 使用参数FULLTEXT可以设置索引为全文索引。

5、空间索引

• 使用SPATIAL可以设置索引为空间索引。
• 只能建立在GEOMETRY、POINT、LINESTRING和POLYGON等空间数据类型上，目前只有MyISAM支持空间索引，且索引字段不能为空。

（三）按照作用字段个数：

1、单列索引

2、联合索引

• 严格来说，联合索引属于非聚簇索引。
• 介绍：同时为多个列建立索引，排序规则根据特定顺序(例如从第一个值开始依次向后进行比对)，考虑全部列
• 注意：只有查询条件中使用了额定字段中的第一个字段时才会被使用，使用时遵循最左前缀原则。

四、索引的底层数据结构

（一）Hash：

特点：查询单条快，范围查询慢

应用场景：

• Memory存储引擎：Memory存储引擎支持将某个字段设置为Hash索引
• InnoDB的自适应Hash索引：如果某个数据经常被访问，当满足一定条件时，会将该数据页的地址存放在Hash表中，下次查询就直接找到该页面所在位置。

• • 开启方式：show variables like '%adaptive_hash_index';——默认是开启状态。

（二）B树：

• B树所有节点存放key和value，B+树只有叶子结点存放value；
• B树叶子结点独立；B+树叶子结点有引用链；

（三）B+树：

1、InnoDB的B+树索引的注意事项

（1）根页面位置万年不动

此处涉及到InnoDB形成B+树的过程：

1）当要为某个表创建B+树索引时，首先会为索引创建根节点页。该页最初创建时没有数据，也没有目录项记录；

2）开始插入用户记录，不断存储到这个根节点中；

3）当根节点空间用完时，会将根节点的所有记录复制到新分配的页，进行页分裂操作，再创建一个新页，再执行插入操作；

4）叶分裂后，根节点便升级为目录项纪录页。

（2）非叶子节点中目录项记录的唯一性

背景：对二级索引来说，如果索引列不唯一，会出现无法确定数据在哪个叶子结点的情况；

解决方式：为了保证在B+树的同一层非叶子节点的目录项记录除页号之外的项的唯一性，其组成包括：“索引列的值+主键值+页号”(联合索引也一样)

（3）一个数据页最少存储2条记录

2、为什么Mysql用B+树做索引而不用B-树或红黑树

（1）B+树只有叶节点存放数据，其余节点用来索引，而B-树是每个索引节点都会有Data域，在衡量查询效率方面，B树有Data域，那就增大了节点大小也就是增加了磁盘IO，而B+树除了叶子节点其它节点并不存储数据，节点小，磁盘IO次数。

（2）查询效率更高。B+树所有的Data域在叶子节点，一般来说都会进行一个优化，就是将所有的叶子节点用指针串起来。这样遍历叶子节点就能获得全部数据，这样就能进行区间访问。而B树不支持这种遍历操作。

（3）查询效率稳定，因为数据都在叶子结点