索引是存储引擎用于快速找到记录的一种数据结构。
常见索引
BTree索引
在谈论 Mysql 索引时,如果没有特指索引类型,那说的就是 B-Tree 索引。
大多数存储引擎都支持 B-Tree 索引,但不同的存储引擎实现并不一样,例如,NDB 集群存储引擎内部实现使用了 T-Tree 结构进行存储,而 InnoDB 存储引擎则使用 B+Tree 结构。底层存储方式不同,性能表现也就各不一样。
底层实现原理
BTree / B+Tree 介绍
https://www.cnblogs.com/guohai-stronger/p/9225057.html
应用场景
基于 B+Tree 的数据结构,B-Tree 索引可以认为就是按索引列的内容进行了排序,所以查找及排序效率都比较高。
索引限制:
- 匹配列前缀,例如:like ‘some%’ 会使用索引,而 like ‘%some’ 则不会使用索引。
- 组合索引,如果不是按照最左列开始查找,则索引失效。例如:有组合索引 (A,B,C),仅使用 B 列或者 C列查询,则不会使用索引。
- 组合索引,不能跳过索引中的列。例如:有组合索引 (A,B,C),使用 A,C 列查询,则只有 A 列会使用索引,C 列无法使用索引。
- 组合索引,如果有某列的范围查询,则其后的列无法使用索引。例如:有组合索引 (A,B,C),使用 A,B,C 进行查询,但 B 列使用 like ‘B%’ 语句,因为 like 是一个范围条件,所以这里只有 A,B 列使用了索引,而 C 列无法使用索引。
in (A,B,C) 也是范围查询,但是属于多个等值条件查询,对于范围查询,无法使用其后的索引列,多个等值条件查询则没有这个限制。
Hash索引
底层实现原理
假设有个表结构如下,在 username 列上创建了 hash 索引
username | password |
---|---|
Tom | 123456 |
Cat | 123456 |
Curry | 123456 |
假设 hash 函数为 f()
,username 列 hash 之后的值如下
f('Tom') = 2323
f('Cat') = 2525
f('Curry') = 2424
那么该 hash 索引的实际存储结构为
hash 值 | 行指针 |
---|---|
2323 | 指向第一行的指针 |
2424 | 指向第三行的指针 |
2525 | 指向第二行的指针 |
可以看到,索引实际存储的是列的 hash 值,也是按 hash 值进行顺序排列。
当执行如下查询时
select * from user where usename = 'Tom'
Mysql 会首先计算 ‘Tom’ 的 hash 值,然后在索引中查找到相应的行指针,因为 hash 算法可能存在冲突,所以一个 hash 值对应的行指针可能有多个,此时需要通过行指针找出行数据,然后对比 username 列的值是否为 Tom,以确保就是要查找的行。
应用场景
索引限制:
- 因为存储的是 hash 值,所以哈希索引只支持等值比较操作,包括:=、<>、in(),不支持任何范围查询,如:price > 100
- 当哈希索引是组合索引时,存储的是组合索引中的合部内容的 hash 值,所以不支持部分索引列匹配查找。例如:(A,B) 为哈希索引,仅使用 A 列查询,则无法使用索引。
- 因为存储的是 hash 值,所以不支持排序
- 因为不存储字段值,所以无法使用覆盖索引特性,但行读取速度比较快,这一点性能影响不大。
- 当出现哈希冲突时,存储引擎必须遍历链表中所有的行指针,逐行进行比较,直到找到所有符合条件的行。
InnoDB 提供 自适应哈希索引,当 InnoDB 注意到某些索引值使用得非常频繁时,会在内存中基于 B-Tree 索引之上再创建一个哈希索引,这样可以让 B-Tree 索引也拥有了哈希索引的一些优点,比如快速的哈希查找,这是一个完全自动的内部行为,用户无法控制。
聚簇索引 & 非聚簇索引
聚簇索引
聚簇索引不是一种单独的索引类型,而是一种数据存储方式。当表有聚簇索引时,数据行实际上存放在索引的叶子页中。
一个表只能有一个聚簇索引
在 InnoDB 中主键列就是聚簇索引,如果没有定义主键,InnoDB 会选择一个唯一的非空索引,如果没有这样的索引,InnoDB 会隐式定义一个主键作为聚簇索引。
InnoDB 中主键索引的结构如下:
主键列 |
非主键列 |
优点:
- 索引数据与行数据存储在一起,减少一次磁盘IO。
- 数据访问更快。聚簇索引将索引与数据保存在同一个B-TREE中,因此从聚簇索引中获取数据通常更快。
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
缺点:
- 聚簇索引最大限度提高了IO密集型应用的性能,但如果数据全部放在内存中,聚簇索引没有优势。
- 插入速度严重依赖插入顺序。按照主键的顺序插入是加载数据到 InnoDB 表中速度最快的方式。
- 更新聚簇索引列的代价很高,会导致数据行移动到新的位置。
- 插入新行时或者索引列更新导致需要移动行时,可能面临"页分裂"的问题。当行数据必须插入到某个已满的页中时,存储引擎会将该页分裂成两个页面来容纳该行,这就是页分裂,页分裂会导致表占用更多的磁盘空间。
- 聚簇索引可能导致全表扫描更慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候。
- 二级索引(非聚簇索引)可能变更大,因为二级索引的叶子节点包含了引用行的主键列。
- 二级索引访问需要两次索引查找,而不是一次。
所以 InnoDB 最好不要使用 UUID 作为主键,它使得聚簇索引的插入变得完全随机,是最坏的情况。
MyISAM 不支持聚簇索引,其主键索引的结构如下:
主键列 |
数据行的指针 |
非聚簇索引
如名字的字面意思,也叫二级索引。
InnoDB 二级索引的结构如下,索引中存储的是主键列,查询时,首先通过索引列查找到相应的主键,然后通过主键查找主键索引,获取行数据。所以,InnoDB 使用二级索引,需要进行两次 BTree 查找。
索引列 |
主键 |
MyISAM 二级索引的结构如下,索引中存储的是数据行的指针,查询时,首先通过索引列查找到行指针,然后直接去数据文件中查找行数据。MyISAM 的二级索引与主键索引的结构其实是一样的,只需要进行一次 BTree 查找。
索引列 |
数据行的指针 |
覆盖索引
索引中包含所有需要查询的字段的值,称之为覆盖索引。
不是所有类型的索引都可以成为覆盖索引,覆盖索引必须要存储索引列的值,而哈希索引、空间索引、全文索引等都不存储索引列的值,所以 MYSQL 只能使用 B-TREE 索引做覆盖索引。
优点:
- 索引条目通常远小于数据行大小,所以如果只需要读取索引,会极大地减少数据访问量。
- 因为索引是按照列值顺序存储的,所以对于IO密集型的范围查询比随机从磁盘读取每一行数据的IO要少得多。
- 一些存储引擎如 MyISAM 在内存中只缓存索引,数据则依赖于操作系统来缓存,因此要访问数据需要一次系统调用,这可能会导致严重的性能问题,尤其是那些系统调用占了数据访问中的最大开销的场景。
- InnoDB 的二级索引存储了主键值,所以可以对主键进行覆盖查询,可以避免对主键索引的二次查询。
覆盖索引优化实例:
select * from products where actor = 'SEAN CARREY' and title like '%APOLLO%'
其中 actor、title 上都有创建索引,但是,这个语句不能使用覆盖索引,因为该语句查询了所有列,而且 title 列使用了全匹配而不是左前缀匹配,这会导致在该列上无法使用索引。
优化思路:
查询列无法覆盖索引,但是 where 条件可以覆盖,可以利用覆盖索引先过滤出需要的行主键,然后再读取需要的数据行。这样就可以通过覆盖索引以及主键索引进行快速查找。
优化后的语句
select t.* from products t
inner join (select id from products where actor = 'SEAN CARREY' and title like '%APOLLO%') as t1 on t.id = t1.id
不同场景下的表现:
假设 products 表有 100W 行数据
1、通过 actor = 'SEAN CARREY'
条件过滤有 3w 条数据,再通过 title like '%APOLLO%'
条件过滤有 2w 条数据
2、通过 actor = 'SEAN CARREY'
条件过滤有 3w 条数据,再通过 title like '%APOLLO%'
条件过滤有 40 条数据
3、通过 actor = 'SEAN CARREY'
条件过滤有 50 条数据,再通过 title like '%APOLLO%'
条件过滤有 10 条数据
针对以上三种情况,两条语句的执行结果如下
场景 | 原查询 | 优化后的查询 |
---|---|---|
1 | 每秒5次查询 | 每秒5次查询 |
2 | 每秒7次查询 | 每秒35次查询 |
3 | 每秒2400次查询 | 每秒2000次查询 |
结果分析:
场景1,返回结果集太大,看不到优化效果,大部分时间都花在数据读取和发送上。
场景2,第一个条件过滤效果不明显,第二个条件过滤效果明显,优化后效率提升,主要得益于只需要读取 40 行完整数据行,而不是原查询中的 3w 行。
场景3,第一个条件过滤效果明显,优化后反而效率下降,因为第一个条件过滤后的结果集已经很小,所以子查询带来的成本反而比从表中直接提取完整行更高