MySQL索引详解-优快云博客

本文链接：https://blog.youkuaiyun.com/u011424653/article/details/80142094

索引是存储引擎用于快速定位记录的一种数据结构，是优化查询性能最有效的手段。使用索引有如下三个优点：

索引大大减少了服务器需要扫描的数据量；
索引可以帮助服务器避免排序和临时表；
索引可以将随机I/O变为顺序I/O。

1. 索引类型

索引有很多种类型，可以为不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以，没有统一的索引标准，不同的存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引，即使多个存储引擎支持同一种类型的索引，其底层实现也可能不同。

1.1 B-Tree索引

1.1.1 简介

B-Tree索引使用B-Tree数据结构存储数据，大多数MySQL引擎都支持这种索引。实际上很多存储引擎使用的时B+Tree，即每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。InnoDB使用的就是B+Tree。

存储引擎以不同的方式使用B-Tree索引，其性能也各有优劣。例如，MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。MyISAM通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

B-Tree索引能够加快数据的访问速度。存储引擎不再需要进行全表扫描来获取需要的数据，而是从索引的根节点开始进行搜索。根节点中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下一层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么找到对应的值，要么要查找的记录不存在。

B-Tree对索引列是顺序存储的，很适合查找范围数据。由于B-Tree索引节点是有序的，所以除了按值查找外，还可以用于查询中的 Order By 和 Group By 操作。索引对多个值进行排序的依据是定义索引时列的顺序。

为了方便下面举例，我们假定在MySQL的information_schema.TABLES表上创建如下索引：
CREATE INDEX idx_tables_union1 ON information_schema.TABLES(TABLE_SCHEMA,TABLE_NAME);
CREATE INDEX idx_tables_union1 ON information_schema.TABLES(TABLE_SCHEMA,TABLE_ROWS);

1.1.2 B-Tree索引适用的查询类型

B-Tree索引适用于以下几种查询类型。

全值匹配：和索引的所有列进行匹配，例如查找ims.comm_company表信息的SQL：

SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'ims' AND TABLE_NAME = 'comm_company';

匹配最左前缀：例如查找以ims开头的所有库的所有表信息的SQL：

SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'ims%';

匹配列前缀：例如查找ims库中以comm开头的表的信息的SQL：

SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'ims' AND TABLE_NAME LIKE 'comm_%';

匹配范围值：例如查找ims库中记录数>100W的所有表名的SQL：

SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'ims' AND TABLE_ROWS > 1000000;

精确匹配某一列并范围匹配另一列：例如查找ims库中记录数<100W的所有表名的SQL：

SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'ims' AND TABLE_ROWS < 1000000;

只访问索引的查询（覆盖索引查询）：即查询只需要访问索引，无需访问数据行。例如查询ims库中所有表名称的SQL：

SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'ims';

1.1.3 B-Tree索引列的顺序对索引优化查询的限制

如果不是按照索引的最左列开始查找，则无法使用索引；
不能跳过多列索引中的列；
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询。

1.2. 哈希索引

1.2.1 简介

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。
对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，不同键值的行计算的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。在MySQL中，只有Memory引擎显式支持哈希索引，并且是非唯一哈希索引。如果多个列的哈希值相同，索引会以链表的方式存放多个记录的指针到同一个哈希条目中。

1.2.2 哈希索引的限制

因为哈希索引只存储对于的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。但哈希索引也有如下限制。

哈希索引只包含哈希值和行指针，不存储字段值，所以也不能使用索引覆盖查询；
哈希索引数据并不按照索引值顺序存储，无法用于排序；
哈希索引不支持按照部分索引列匹配查找，因为哈希索引使用索引列的全部内容计算哈希值的；
哈希索引只支持等值比较查询（包括 =、IN()、<=>），不支持任何范围查询；
访问哈希索引的数据非常快，当有哈希冲突时，存储引擎必须遍历链表中所有的指针行并逐行进行比较，直到找到所有符合条件的行；
如果哈希冲突很多的话，索引的维护代价会很高。

1.2.3 创建自定义哈希索引

InnoDB存储引擎不支持哈希索引，但可以在表中新增一列存储键值的哈希值，然后在该列上建立索引，即可达到模拟哈希索引的目的。这样就可以利用哈希索引的优势快速定位要查找的数据行。

为方便举例，新建author表如下：

CREATE TABLE `author` (
   `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
   `name` varchar(32) NOT NULL COMMENT '姓名',
   `gender` tinyint(1) NOT NULL COMMENT '性别，0-男，1-女',
   `age` tinyint(3) NOT NULL DEFAULT '0' COMMENT '年龄',
   `email` varchar(32) NOT NULL DEFAULT '' COMMENT '邮箱',
   `homepage` varchar(128) NOT NULL DEFAULT '' COMMENT '主页',
   `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
   PRIMARY KEY (`id`),
   KEY `idx_author_homepage` (`homepage`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

现在author表数据量很大，根据homepage查询的频率非常高，在homepage列上建立索引是提高查询效率的最直接方法，查询如下：

SELECT * FROM author WHERE homepage = 'https://blog.youkuaiyun.com/u011424653';

但homepage列长度较大，直接在homepage列创建索引，存储的内容会很大，因为homepage本身比较长。
如果删除homepage列上的索引，而新增一个被索引的homepage_myhash列（myhash是一个哈希函数），就可以使用下面的方式查询：

SELECT * FROM author WHERE homepage = 'https://blog.youkuaiyun.com/u011424653' AND homepage_myhash = myhash('https://blog.youkuaiyun.com/u011424653');

这样做的性能会很高，因为MySQL优化器会使用这个选择性很高整数列的索引来完成查找。
另外，为了防止出现哈希冲突，当使用哈希索引进行查找时，必须在WHERE条件中带人哈希值和对应列值（如上述SQL所示）。