MySQL 索引的分类和各种索引的简单理解

摘要:mysql 的索引,索引是什么,索引的优点,索引的使用条件,b-tree 索引,哈希索引,全文索引, 空间数据索引,主键索引,唯一索引,普通索引,组合索引,覆盖索引,索引下推,聚簇索引,非聚簇索引。

MySQL 的锁、索引、事务、隔离级别系列文档

MySQL 索引的分类和各种索引的简单理解(本文)

MySQL 锁的分类和死锁的解决方案

MySQL 事务的概念和事务 ACID 基本实现原理

MySQL 数据库之 InnoDB 事务隔离级别和锁的关系


什么是索引

一般别人问我们索引是什么的时候?我们都会说,索引就相当于是一本书的目录,让你从书中找到你想要的内容更快些。没错,的确是这样的,但是这个回答并不准确,只是让人们更好的去理解索引的定义。再次碰到这个问题,我们应该这样回答。索引是存储引擎用于快速找到记录的一种数据结构,通俗点讲,相当于一本书的目录部分。索引是在存储引擎层实现的,而不是在服务器层实现的,所以不同存储引擎具有不同的索引类型和实现。

索引可以说是 mysql 优化最有效的手段了,一般我们检查一条慢查询 sql,想到的第一个优化手段肯定是先看看这个表的索引是怎么建的。还有就是我们经常提到的 mysql 的锁,也是根据索引来加锁和解锁的。


索引分类的划分

经常有人问,你都是知道哪些索引,我们一般都是回答普通索引、唯一索引、主键索引、联合索引等等。其实这个问题很难回答,因为这要看你如何对索引进行划分,正常来讲我们这样划分索引。比如:

◆ 从索引结构上讲:b-tree、哈希、空间数据索引(r-tree)、全文索引等。

◆ 从索引种类上讲:普通索引、唯一索引、主键索引、组合索引等。

◆ 从存储结构上讲:innodb 索引对数据存储方式还分为聚簇索引和非聚簇索引。

◆ 从索引优化上讲:覆盖索引、索引下推、innodb 的自适应哈希索引等。

一般来讲,当人有问你 mysql 都是有哪些索引,你应该从索引的底层存储结构分类去回答 btree、哈希、rtree、全文索引等,这样回答起来更专业些。


索引的优点

◆ 索引是查询性能优化的最有效手段。

◆ 索引大大减少了服务器需要扫描的数据行数。

◆ 索引可以将随机 i / o 变成顺序 i / o。

◆ 索引可以帮助服务器避免排序和临时表。


索引使用条件

索引虽然优点很多,也很重要,但是不是说索引是可以随便新建的,使用索引也是需要条件的,建立索引要和当前表的结构和业务逻辑去综合考虑,不过也有一些常见的使用条件。如下:

◆ 数据量少的表不适合使用索引,因为全表扫描要比索引更快,所以你加上索引也体现不到索引的价值,还会增加 cpu 的 i/o。

◆ 对于中到大型的数据表,使用索引优化就非常有效,但是也不是每个字段都要建立索引,一般一个表里的索引不要超过五个,还有就是能使用联合索引的尽量使用联合索引,而不是单个索引。

◆ 更新字段比较频繁的表不适合索引,因为大部分都是 innodb 存储引擎,这种存储引擎使用的 b+tree 索引结构,为了保证 b+tree 的平衡性,所以每次插入、更新、删除等操作都需要维护树的平衡性,所以为了减少磁盘的 i / o 的开销,提升写操作的速度,频繁更新的字段不应该建立索引。


B-Tree 索引

为了更好的理解 b-tree 索引结构,应该先了解下二分查找法和平衡二叉树。其实 b-tree 索引结构是基于二分查找法和平衡二叉树算法之上的,只不过是 b-tree 相比他们的排序方式和树的高度有所改变。

b-tree 索引也可以叫做 btree 索引,其实是一个意思。大部分存储引擎都是使用的这种索引结构,不同的存储引擎以不同的方式使用 b-tree  索引,实现方式、性能、优势都各有不同。比如 innodb 和 myisam 都是使用 b+tree 索引结构,是在 b-tree 基础之上实现的的一种索引结构,其实也可以说是从技术实现上叫做 b+tree 索引。

b-tree 是按照从左到右(左子节点小于父节点,父节点小于右子节点)的顺序来建立索引树的,并且每一个叶子页到根的距离相同,b-tree 的查找速度取决于树的高度(一般来讲都是三层)。b-tree 索引还可以指定多个列形成组合索引,适用于全键值、键值范围和键前缀查找。其中键前缀查找只适用于最左前缀查找,如果 where 条件中没有匹配上索引最左边的列,那么会造成索引失效。还支持只走访问索引的查询,覆盖索引。

但是维护一棵 b-tree 的代价是非常大的,因为要保证 b-tree 的平衡性,我们每次在插入、更新、删除数据后,都要通过一次或多次的左旋和右旋来更新这棵树的平衡性。


哈希索引

哈希索引基于哈希表实现,只有精确匹配索引列的查询才有效,对于每一行数据,存储引擎都会对所有的索引计算出一个哈希码,哈希码是一个很小的值,并且不同的键值计算出来的哈希码也不一样,哈希索引将所有的哈希码存储在索引中同时在哈希表中保存指向每个数据行的指针。

在 mysql 中 memory 存储引擎显式的支持哈希索引,也是 memory 的默认存储引擎,但同时 memory 也支持 b-tree 索引。不过 memory 是支持非唯一哈希索引的(这个比较特别,切记),如果多个列的哈希值相同,索引会以链表的方式存放多个记录指针到同一哈希条目中。

哈希索引只支持等值比较查询,比如 =、in()、<=>(注意 <> 和 <=> 是不同的操作),但是不支持任何范围查询,例如 where price > 100。因为是等值比较查询,所以哈希索引查找的速度非常快。

innodb 引擎也支持唯一哈希索引。innodb 存储引擎有一个特殊的功能叫“自适应哈希索引”,当某个索引值被使用的非常频繁时,会在 b+tree 索引之上再创建一个哈希索引,这样就让 b+tree 索引具有哈希索引的一些优点,比如快速的哈希查找,这是一个完全自动的内部行为,但是用户可以手动关闭该功能。


R-Tree  空间数据索引

myisam 存储引擎支持空间数据索引(r-tree),可以用于地理数据存储。

和 b-tree 不同,这类索引无需前缀查询。空间数据索引会从所有维度来索引数据,查询时,可以有效地使用任意维度来进行组合查询。

必须使用 gis 相关的函数来维护数据。


全文索引

mysql 的全文索引类型是 fulltext。

全文索引只支持 myisam 和 innodb(>= 5.6)存储引擎,并且只能用于创建 char,varchar,text 类型。

并且全文索引最开始只支持英文不支持中文,自 >= 5.7.6 开始,引入了一个 ngram 全文分析器解决了这个问题。


聚簇索引

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式,一些数据库可以选择指定索引作为聚簇索引,但是 mysql 内建的存储引擎都不支持这一点。innodb 是通过主键 primary key 聚集数据,也就是说利用主键来生成一颗 b+tree 结构索引树。也可以说主键就是聚簇索引,但是我们不能说聚簇索引就是主键。

innodb 的 b+tree 的索引结构会分为三层,分别是根节点 -> 非叶子节点 -> 叶子结点,但是非叶子结点的数据都会冗余一份在叶子节点上,叶子节点的数据正常来讲是按照主键排序的。如果表中没有主键,那么 innodb 会选择一个 unique not null(不为 unll 的唯一索引) 来当做聚簇索引,如果存在多个 unique not null 索引,那么就选择建表以来第一个建立的 unique not null 索引。再如果 unique not null 索引都没有,那么 innodb 会默认为每一行生成一个 6 字节的 rowid,并以此为主键。

聚簇索引就是依靠主键构造一棵 b+tree 索引结构,同时叶子节点中存放的是当前 id 行的完整数据,所以也可以将聚簇索引的叶子节点称为数据页,由于聚簇索引是依靠 id 来进行构造和进行排序的,所以一个表中只能有一个聚簇索引。

聚簇索引有一个概念就是数据及索引,索引及数据,也就是说我们找到了索引所在叶节点的位置就相当于找到了数据。

mysql> explain select * from my_innodb where id = 2;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | my_innodb | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

比如上面这个 my_innodb 表的 b+tree 索引树高度是 3 层,那么这个 sql 查询使用 id = 2 主键进行查询,只需要 3 次 i / o 就可以找到聚簇索引所在叶节点的位置,然后就可以获取完整的行记录,所以这也就是为什么我们常说使用主键 id 当做 where 条件查询比较快的原因。


非聚簇索引

如果说一个表只能有一个聚簇索引,那么一个表就可以有多个非聚簇索引了,我们可以给其它任意字段加上非聚簇索引。其实非聚簇索引和聚簇索引差不多,都是使用 b+tree 这种索引结构,只不过叶子节点存放的数据不一样,聚簇索引存放的是整行完整数据,非聚簇索引存放的当前字段的数据和一个书签,这个书签就是聚簇索引键。

假如我们给 name 字段加上非聚簇索引,那么就会构建一个以 name 字段的 b+tree 结构的索引树,如果再给 age 字段加上非聚簇索引,那么就会再构建一个以 age 字段的 b+tree 索引结构树,也就是说每给一个字段加上非聚簇索引,那么这个字段的数据就会被复制出来,用于生成索引。所以说表中的索引也不是越多越好,索引越多就意味会增加表的体积,和占用磁盘空间。非聚簇索引之间没有任何关系,但是所有的非聚簇索引都和聚簇索引有关系,因为所有的非聚簇索引的叶子节点都存放着聚簇索引键。

mysql> explain select * from my_innodb where author = 11;  
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | my_innodb | NULL       | ALL  | author        | NULL | NULL    | NULL |    6 |    16.67 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

比如上面这个 sql 查询,我们并没有使用主键,而是使用 author 普通索引来查询数据,假如 author 和 id 的 b+tree 都是 3层。那么存储引擎会先遍历 author 这棵索引树,使用 3 次 i / o 来获取聚簇索引键,然后再用 3 次 i / o 获取聚簇索引的叶子节点,然后再获取完整的数据。因为我们使用的 select * 查询,索引值通过 author 索引树是无法获取我们想要的数据,必须要再次回表一次通过聚簇索引来获取完整数据,也就是说总共需要 6 次 i / o 才可以。


覆盖索引

上面的 select * 查询,非聚簇索引为了获取完整数据才需要回表。但是我们一般不会使用 select * 查询,只是查询我们需要的字段,不需要的字段我们不会获取。如果是这样,非聚簇索引也可以不用回表,像聚簇索引那样,直接通过非聚簇索引的叶子节点位置就可以获取数据,这种查询称作覆盖索引查询。

如果想触发覆盖索引,那么你要 select 的字段,正好是添加索引的字段,比如:

KEY `index_name1_name2_name3` (`name1`,`name2`,`name3`)

mysql> explain select name1, name2, name3 from mytable;
+----+-------------+-----------+------------+-------+---------------+-------------------------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key                     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+-------------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | mytable | NULL       | index | NULL          | index_name1_name2_name3 | 310     | NULL |   19 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+-------------------------+---------+------+------+----------+-------------+

上面这个 sql 会触发覆盖索引,因为我们查询的字段 name1、name2、name3 是一个组合索引 index_name1_name2_name3,也就是说我们会把 name1、name2、name3 这三个字段的数据拿出来建立一个组合索引 b+tree,哪么 index_name1_name2_name3 这个索引的叶子节点除了存放这三个字段数据,还会存放聚簇索引键,但是我们只获取 name1、name2、name3 这三个字段值,所以我们可以直接在非聚簇索引的 b+tree 叶子节点获取,并不需要再去利用聚簇索引键回表查询。

由于覆盖索引可以减少树的搜索次数,显著的提升性能,所以说覆盖索引也是优化查询性能的一个手段。如果查询中使用覆盖索引技术的话,那么 explain 分析结果会发现 extra 列出现 using index 标识。


索引下推

mysql 5.6 引入索引下推(Index Condition Pushdown,简称 ICP),官方文档:https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html,也可以参考这篇文章:https://www.zhihu.com/search?type=content&q=索引下推

如果不启动 ICP,那么存储引擎将遍历索引然后一个个回表比对,然后返回给 mysql 的服务层,再一个个进行比对。如果启动 ICP 后,如果 where 条件中有可以使用索引中的列,则 mysql 会把这部分条件下推到存储引擎,然后存储引擎中会把不满足条件的行过滤掉,只会比对满足条件的行。

KEY `idx_title_author` (`title`,`author`)

mysql> select * from my_innodb;
+----+--------+--------+-------+
| id | title  | author | state |
+----+--------+--------+-------+
|  1 | 西瓜   | 11     |     0 |
|  2 | 西瓜   | 11     |     0 |
|  3 | 香蕉   | 14     |     1 |
|  4 | 柚子   | 13     |     0 |
|  5 | 橘子   | 15     |     1 |
|  6 | 橙子   | 16     |     0 |
+----+--------+--------+-------+

mysql> explain select title, author from my_innodb where title like '西%' and author = 11 and state = 0; 
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+------------------------------------+
| id | select_type | table     | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | my_innodb | NULL       | range | idx_title_author | idx_title_author | 302     | NULL |    2 |    16.67 | Using index condition; Using where |
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+------------------------------------+
1 row in set, 2 warnings (0.00 sec)

我们可以看出上面这个 my_innodb 表结构中,有一个组合索引 idx_title_author,根据索引最左匹配规则,where 条件中的 title like '西%' 肯定会命中组合索引 idx_title_author,然后条件 title like '西%' 会定位到 idx_title_author 索引的叶子节点聚簇索引键,也就是 id = 1,然后再回表判断其他条件是否满足,因为 state 字段没有索引,所以肯定要进行回表比对的。

如果在 mysql 5.6 之前,只能找到 id = 1 的值开始一个个回表,到聚簇索引的主键上把数据取出来,然后进行比对。但是在 mysql 5.6 之后引入了索引下推优化,可以在遍历索引过程中,对索引中包含的字段进行优先判断,也就是说会对 author 字段进行判断,把不满足 author = 11 的列都过滤掉了,只拿着满足 title like '西%' and author = 11 的 id 值去回表比对就可以了。

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

索引下推由 optimizer_switch 参数控制,可以利用上面的语句进行关闭和打开。如果查询中使用了索引下推优化,那么 explain 分析结果中 extra 会显示 Using index condition 内容。


InnoDB 的自适应哈希索引

自适应哈索引(Adaptive Hash Index,简称 AHI),官方文档:https://dev.mysql.com/doc/refman/8.0/en/innodb-adaptive-hash.html

哈希是一种非常快的查找方法,一般只需要一次定位就可以定位数据。然而 b+tree 的查找速度要取决于树的高度。生产环境中树的高度一般为 3 - 4 层。但是 innodb 存储引擎会监控各个索引页的查询,通过缓冲池中的 b+tree 页构造的,并不需要对整张表表结构建立索引。innodb 存储引擎会自动根据访问的频率和模式来自动地为某些页建立哈希索引。但是 AHI 有一个要求,就是对这个页的连续访问模式必须是一样的。比如对于 a,b 这种联合索引,其访问模式可以是以下这种情况。

where a = xxx;
where a = xxx and b = xxx;

如果想要生成自适应哈希索引,那就必须要对以上的两种 where 条件其中的一种一直访问到一定次数才可以激活自适应哈希索引,但不可以交替访问这两种 where 条件。ahi 的设计思想是数据库自由化的,无需人为的调整。

可以通过  show engine innodb status 命令返回的以下这部分内容观察当前的 AHI 的大小、使用情况、每秒使用 AHI 搜索的情况。

mysql> show engine innodb status\G
*************************** 1. row ***************************
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 10 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 0 buffer(s)
Hash table size 69239, node heap has 2 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 1 buffer(s)
Hash table size 69239, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

但是自适应哈希索引只能用来进行等值查询,对于其他的范围查询之类的,是无法使用自适应哈希索引的。我们还可以使用 innodb_adaptive_hash_index 来启动还是禁用自适应哈希索引,默认 AHI 是开启状态。


MyISAM 和 InnoDB 存储引擎 B+Tree 的区别

[root@localhost transaction]# ll
-rw-r----- 1 mysql mysql   8654 12月 25 06:03 my_innodb.frm
-rw-r----- 1 mysql mysql 131072 12月 25 06:03 my_innodb.ibd

通过查看 innodb 的物理存储文件就可以看出来,innodb 只有两个物理文件,一个 frm 一个 ibd 文件,不过前提是开启单独表空间,否则只有一个 frm 文件。

innodb 的数据和索引是放在一起的,都放在 ibd 文件中,利用主键来聚簇数据的,数据及索引,索引及数据,非聚簇索引中存储的是聚簇索引键。所以说利用主键查询要比普通索引查询要快。

-rw-r----- 1 mysql mysql   8710 9月  21 21:35 my_myisam.frm
-rw-r----- 1 mysql mysql    384 12月  5 06:30 my_myisam.MYD
-rw-r----- 1 mysql mysql   5120 12月 10 19:38 my_myisam.MYI

通过查看 myisam 的物理存储文件可以看出来,除了 frm 文件,还有 MYD 和 MYI 文件,其中 MYD 是存放数据的,MYI 是单独存放索引的。

myisam 的索引和数据是分开存储的,所以无论是主键索引还是普通索引,他们的叶子节点都是存放着指向实际数据物理地址的指针,所以说 myisam 的主键查询和普通索引查询没有什么区别。


参考资料:

《MySQL 技术内幕 InnoDB 存储引擎 第二版》第 5 章 索引与算法

MySQL索引背后的数据结构及算法原理

详细聊聊MySQL中 聚簇、非聚簇索引和覆盖索引

结束语:感谢您对本网站文章的浏览,欢迎您的分享和转载,但转载请说明文章出处。
Top