MySQL-----索引

一、什么是索引

存储引擎用于快速找到记录的一种数据结构。

索引类似于目录。就比如我们要找书里的一段话，我们先按目录找，然后再具体定位，这样速度会很快。

二、索引的作用

1. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

2. 可以大大加快数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。

3. 帮助服务器避免排序和临时表。

4. 将随机IO变为顺序IO（索引加快查询速度的根因）

5. 可以加速表和表之间的连接。

三、Mysql索引主要使用的两种数据结构

MySQL索引使用的数据结构主要有BTree索引 和 哈希索引 。

单条记录查询用哈希索引；其余大部分场景，建议选择BTree索引。

MySQL的BTree索引使用的是B树中的B+Tree，但对于主要的两种存储引擎的实现方式是不同的。

• InnoDB: 其数据文件本身就是索引文件。这就是聚簇索引（或聚集索引）。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。（回表）辅助索引还包含当前列的值。因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

• MyISAM: B+Tree 叶节点有key 和 value。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”（数据和索引分开）。

四、索引的分类

（1）逻辑上

按功能划分：

主键索引(Primary Key)：一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

数据表的主键列使用的就是主键索引。

在 mysql 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

二级索引(辅助索引)：二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

唯一索引，普通索引，前缀索引等索引属于二级索引。

• 唯一索引(Unique Key) ：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。建立唯一索引的目的大部分是为了该属性列的数据的唯一性，而不是为了查询效率。
• 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
• 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
• 全文索引(Full Text) ：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；

按列数划分

• 单列索引：一个索引只包含一个列，一个表可以有多个单例索引。
• 组合索引：一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。

（2）物理上（聚簇索引和非聚簇索引）

聚簇索引：索引结构和数据一起存放的索引。主键索引属于聚集索引。

在 Mysql 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

非聚簇索引：非聚簇索引即索引结构和数据分开存放的索引。二级索引属于非聚集索引。

MYISAM 引擎的表的.MYI 文件包含了表的索引， 该表的索引(B+树)的每个非叶子节点存储索引， 叶子节点存储索引和索引对应数据的指针，指向.MYD 文件的数据。

可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

总结：聚簇索引优缺点
优点：

• 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
• 聚簇索引对于主键的排序查找和范围查找速度非常快

缺点：

• 依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
• 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

补充覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢。覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

eg:

现在我创建了索引(username,age)，我们执行下面的 sql 语句

select username , age from users where username = 'zql' and age = 21

在查询数据的时候：要查询出的列在叶子节点都存在！所以，就不用回表。