【MySQL】十二，索引的概念

为什么使用索引

提高数据查询的效率。

定义

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取的数据的数据结构。

使用索引的优点

• 提高数据检索的效率，降低数据库的IO成本。
• 通过创建唯一索引，可以保证数据库中每一行数据的唯一性。
• 在实现数据的参考完整性方面，可以加速表和表之间的连接。对于有依赖关系的子表和父表，联合查询时，可以提高查询速度。
• 在使用分组和排序子句进行查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

使用索引的缺点

• 创建索引和维护索引需要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。
• 索引需要占用磁盘空间。
• 虽然提高了查询速度，却降低了更新表的速度。当对表中的数据进行增、删、改的时候，索引也需要动态的维护，这就降低了数据的维护速度。

常见的索引

假设存在这样一张表

mysql> CREATE TABLE index_demo(
-> c1 INT,
-> c2 INT,
-> c3 CHAR(1),
-> PRIMARY KEY(c1)
-> ) ROW_FORMAT = Compact;

• record_type ：记录头信息的一项属性，表示记录的类型，0表示普通记录、1表示目录项记录、2表示最小记录、3表示最大记录。
• next_record ：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表明下一条记录是谁。
• 各个列的值 ：这里只记录在 index_demo 表中的三个列，分别是 c1 、 c2 和 c3 。
• 其他信息 ：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

把一些记录放到页里

聚簇索引

特点
使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

• 页内 的记录是按照主键的大小顺序排成一个 单向链表。
• 各个存放 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表。
• 存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个 双向链表 。

B+树的 叶子节点 存储的是完整的用户记录。
所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

优点

• 数据访问更快 ，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
• 聚簇索引对于主键的 排序查找 和 范围查找 速度非常快
• 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的IO操作 。

缺点

• 插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
• 更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
• 二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据。

非聚簇索引（二级索引、辅助索引）

我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程称为 回表 。也就是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树

联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照 c2和c3列 的大小进行排序，这个包含两层含义：

• 先把各个记录和页按照c2列进行排序。
• 在记录的c2列相同的情况下，采用c3列进行排序。

注意一点，以c2和c3列的大小为排序规则建立的B+树称为 联合索引 ，本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：

• 建立 联合索引 只会建立如上图一样的1棵B+树。
• 为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。