[大数据]浅析MySQL索引

简述MySQL索引

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。MySQL里同一个数据表里的索引总数限制为16个。

以汉语字典的目录页（索引）举例，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

虽然索引大大提高了查询速度，同时却会降低更新表的速度，比如，对表进行INSERT、UPDATE和DELETE操作。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间的索引文件，同时，过多的使用索引将会造成滥用。

总之，索引就是用来提高速度的，但是需要维护索引，从而造成资源的浪费；所以，合理的创建索引是必要的。

索引优缺点

优点

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 建立索引可以有效缩短数据的查询时间。
• 建立索引可以加快表与表之间的连接。
• 为用来排序或者是分组的字段添加索引可以加快分组和排序速度。

缺点

• 创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大。
• 索引需要占用物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
• 会降低表的增删改的效率，因为每次增删改操作，索引需要进行动态维护，导致时间变长。
• 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
• 对于非常小的表，大部分情况下简单的全表扫描更高效；

索引分类

按功能划分

普通索引

最基本的索引，没有任何限制。

CREATE INDEX index_name ON `table_name` (`字段名`);

ALTER TABLE `table_name` ADD INDEX index_name (`column`) COMMENT '普通索引'

唯一索引

与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

CREATE UNIQUE INDEX index_name ON `table_name` (`column`);

ALTER TABLE `table_name` ADD UNIQUE (`column`)

ALTER TABLE `table_name` ADD UNIQUE INDEX (`column`)

ALTER TABLE `table_name` ADD UNIQUE KEY (`column`)

# 指定索引名
ALTER TABLE `table_name` ADD UNIQUE index_name (`column`)

主键索引

它是一种特殊的唯一索引，不允许有空值。

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

全文索引

仅可用于MyISAM存储引擎的表，针对较大的数据，生成全文索引很耗时耗空间。

ALTER TABLE `table_name` ADD FULLTEXT ( `column` )

按列数划分

单例索引

一个索引只包含一个列，一个表可以有多个单列索引，但这不是组合索引。

组合索引

一个索引包含多个列。为了更多的提高MySQL效率可建立组合索引，遵循”最左前缀“原则。

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

按物理结构划分

聚簇索引

聚簇索引（clustered index）不是单独的一种索引类型，而是一种数据存储方式。

这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树，当B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。

聚簇索引也可理解为将数据存储与索引放到了一块，找到索引也就找到了数据。

非聚簇索引（有时也称为辅助索引或二级索引）

数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录。

叶子节点中存储主键值，每次查找数据时，根据索引找到叶子节点中的主键值，根据主键值再到聚簇索引中得到完整的一行记录。总之，二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

拓展：聚簇索引优缺点

优点

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

缺点

• 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键（主键列不要选没有意义的自增列，选经常查询的条件列才好，不然无法体现其主键索引性能）
• 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

虽然InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引，但是只有InnoDB的主键索引才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。

索引存储类型

常用的索引存储类型（索引底层的数据结构）有：B-TREE ，B+TREE ，HASH 等，Mysql索引主要有两种结构：B+Tree索引和Hash索引。

B-TREE(B数，多路搜索树)

B树是一种多路搜索树，每个节点有多个孩子节点，一棵m阶的B-Tree有如下性质：

1. 树中的每个节点最多含有m个孩子(m>=2)
2. 除根节点和叶子节点外，其它每个节点至少有ceil(m/2)个孩子（其中ceil(x)是一个取上限的函数）
3. 根结点至少有2个孩子，根节点同时是叶子节点的话除外
4. 所有叶子结点都出现在同一层
5. 内部节点至少半满
6. 树内的每个节点都存储数据
7. 叶子节点之间无指针相邻

它的特点如下：

1. 保持键值有序，以顺序遍历
2. 使用层次化的索引来最小磁盘读取
3. 使用不完全填充的块来加速插入和删除
4. 通过优雅的遍历算法来保持索引平衡
5. 通过保证内部节点至少半满来最小化空间浪费
6. 一棵树可以处理任意数目的插入和删除

B+TREE(B+数)

B+Tree是B-Tree的一个变种。它与B数的不同之处如下：

1. 叶子节点存储了所有的关键字信息（数据只出现在叶子节点）
2. 叶子节点的最后一个指针指向相邻的下一个叶子节点（所有叶子节点增加了一个链指针）

它的特点如下：

1. 所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的
2. 不可能在非叶子结点命中
3. 非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层

B树与B+树的区别

1. B树的树内存储数据，因此查询单条数据的时候，B树的查询效率不固定，最好的情况是O(1)。我们可以认为在做单一数据查询的时候，使用B树平均性能更好。但是，由于B树中各节点之间没有指针相邻，因此B树不适合做一些数据遍历操作。

2. B+树的数据只出现在叶子节点上，因此在查询单条数据的时候，查询速度非常稳定。因此，在做单一数据的查询上，其平均性能并不如B树。但是，B+树的叶子节点上有指针进行相连，因此在做数据遍历的时候，只需要对叶子节点进行遍历即可，这个特性使得B+树非常适合做范围查询。

通常情况下，在关系型数据中，遍历操作比较常见，因此采用B+树作为索引，比较合适，如MySQL。而在非关系型数据库中，单一查询比较常见，因此采用B树作为索引，比较合适，如Mongodb。

HASH

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。Memory存储引擎默认使用的Hash。

MySQL 指定索引类型

一些存储引擎允许您在创建索引时指定索引类型(index_type)。

例如：

CREATE TABLE lookup (id INT) ENGINE = MEMORY; 

CREATE INDEX id_index ON lookup (id) USING BTREE;

不同存储引擎支持指定的索引类型

下表显示了不同存储引擎支持的索引类型值。

如果列出了多个索引类型，当没有给出索引类型说明时，第一个是默认值。

表中未列出的存储引擎，在索引定义中，不支持index_type子句。

创建索引的准则

应该创建索引的列

• 在经常需要搜索的列上，可以加快搜索的速度
• 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
• 在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度
• 在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
• 在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
• 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不该创建索引的列

• 对于那些在查询中很少使用的列不应该创建索引。若列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
• 对于那些只有很少数据值或者重复值多的列也不应该增加索引。这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。这时候增加索引，并不能明显加快检索速度。
• 对于那些定义为text, image和bit数据类型的列不应该增加索引。这些列的数据量要么相当大，要么取值很少。
• 当该列修改性能要求远远高于检索性能时，不应该创建索引。（修改性能和检索性能是互相矛盾的）

参考文档

• MySQL官网-创建索引
• MySQL官网-修改表时创建索引
• 数据库索引的优缺点
• 一文搞懂MySQL索引