漫谈Mysql之索引

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

常见索引

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，第一个条件过滤效果明显，优化后反而效率下降，因为第一个条件过滤后的结果集已经很小，所以子查询带来的成本反而比从表中直接提取完整行更高