《高性能 MySQL》读书笔记——索引优化

索引是数据库得以高效的关键,以最常见的 B+Tree 索引为例,至少有以下三个优点:1)索引大大减少了服务器需要扫描的数据量;2)索引可以帮助服务器避免排序和临时表;3)索引可以将随机 I/O 变为顺序 I/O。因此,如何使用索引也成了高效 MySQL 重要的一部分。

一、MySQL 中的索引

1、B-Tree 索引

B-Tree 和其变体 B+Tree 是绝大部分数据库引擎默认使用的数据结构,我们在谈到索引时若无特殊指代一般是指 B+Tree(B-Tree 和 B+Tree 的区别及选择这种数据结构的原因可以看这里

优点

B-Tree 索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。以下表为例

family_name first_name birthday
1960-1-1
1962-3-2
1966-4-5

依次在 family_name、first_name 和 birthday 上建立 B-Tree 索引(暂不考虑主键的影响),则上述的索引对如下的索引有效:

  • 全值匹配:全值匹配指的是和索引中的所有列进行匹配,如查找 family_name = 张,first_name = 三,birthday = 1960-1-1 的人。
  • 匹配最左前缀:查找所有 family_name= 张 的人。
  • 匹配列前缀:查找所有 family_name = 张,first_name = 三,birthday = 196X 的人。列前缀的语法有很多,比如 where birthday(3) = 196 或者 where birthday like “196%” 都是合法的匹配列前缀的语法,但 birthday like “%96%” 不是合法的列前缀匹配,不会使用索引查找。
  • 匹配范围值:查找所有 family_name = 张,first_name = 三,birthday > 1960-1-1 and birthday < 1969-12-31 的人。
  • 只访问索引的查询:若查询的内容在索引中便可全部找到,则无需回表查询,可以节省大量磁盘 IO,这种索引有个专有名词叫“覆盖索引”。

缺点

B-Tree 索引的功能强大,但也有局限,仍以上述的索引为例:

  • 如果不是按照索引的最左列开始查找,则无法使用索引。例如我们无法用索引查找 first_name = 四 的人。
  • 不能跳过索引中的列。例如我们无法用索引查找 family_name = 张,birthday = 1960-1-1 的人。
  • 查询中可以使用范围查询,但只能使用一次,且它右边所有的列都无法使用索引优化查找。如查找 family_name = 张,first_name > 三 and first_name < 五(字典序),birthday = 1960-1-1 的人,只会用到姓和名两个索引列,无法使用第三个索引列。(这个涉及到联合索引底层的数据结构,如下图)

B-Tree 在建立联合索引的时候只会对第一个字段建立 B-Tree 索引,其它字段会在对应的叶子节点的 data 域上按给定字段的顺序作为优先级排序后储存。如上图,对 id、family_name、first_name 三个列建立索引。则底层存储时会先按 id 构造 B-Tree,再在 B-Tree 的叶子节点上按 family_name、first_name 的优先级排序后存储对应的地址。对于叶子节点上数据的查找,会采用二分查找的方式。而一旦确定了前一个字段使用范围查找后,得到的一组数据对于后一个字段而言是无序的,无法继续使用二分查找,只能遍历,此时索引失效。除了范围查询,整个 B-Tree 的最左匹配原则的原因也是和这个数据存储的方式息息相关的,理解了这个数据结构也就理解了 B-Tree 的最左匹配原则。

2、哈希索引

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

在 MySQL 中,只有 Memory 引擎显式支持哈希索引,这也是 Memory 引擎表的默认索引类型。

优点

  • 索引的结构十分紧凑,查找的速度非常快。

缺点

  • 哈希索引只包含哈希值和指针,而不存储字段值,所以不能使用覆盖查询。
  • 哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序。
  • 哈希索引也不支持部分索引列匹配查找,因为哈希值的计算是使用索引列的全部内容计算的。
  • 哈希索引只支持等值比较,不支持任何范围查询

哈希索引的缺点也决定了哈希索引只适用于某些特定的场合,但一旦适合哈希索引,则它带来的性能提升将非常显著。

3、全文索引

在标准的 MySQL 中只有 MyISAM 引擎支持全文索引,同时 innodb 也开始实验性质地支持全文索引。

MyISAM 的全文索引作用对象是一个“全文集合”,这可能是某个数据表的一列,也可能使多个列。具体的,对数据表的某一条记录,MySQL 会将需要索引的列全部拼接成一个字符串,然后进行索引。

MyISAM 的全文索引是一类特殊的 B-Tree 索引,共有两层。第一层是所有关键字,然后对于每一个关键字的第二层,包含的是一组相关的“文档指针”。

二、innodb 中的索引

1、聚簇索引

聚簇索引不是一种单独的索引类型,而是一种数据存储方式。innodb 的聚簇索引是在 B-tree 的叶子节点上存放了数据行。

innodb 所有表中的数据都会以这种形式保存在磁盘,这也意味着 innodb 每张表中至少要有一个主键。如果没有显式地定义主键,innodb 会选择一个唯一的非空索引代替;如果没有这样的索引,innodb 会隐式定义一个主键作为聚簇索引。

聚簇索引中,每个叶子节点称为一个数据页,相邻的数据页之间有双向指针相连,范围查找可以直接按顺序读出,速度非常快。

2、非聚簇索引(辅助索引)

innodb 中每张表有且仅有一个聚簇索引,剩下的都是非聚簇索引。对于非聚簇索引,叶子节点并不会包含行记录的全部数据,而是保存指向聚簇索引中某一条记录的指针。比如 user 表中使用 user_id 作为主键,那么在它的非聚簇索引的叶子节点中,保存的就是 user_id。对于一次使用了非聚簇索引的查找,数据库引擎会先在非聚簇索引上找到 user_id,再根据 user_id 在聚簇索引上找到对应的数据行,这也就是 innodb 中的二次查询。

3、自适应哈希索引

自适应哈希索引是 innodb 上的一种优化措施。InnoDB 存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升,则建立哈希索引,称之为自适应哈希索引 (Adaptive Hash Index, AHI)。AHI 是通过缓冲池的 B+树页构造而来,因此建立的速度很快,而且不需要对整张表构建哈希索引。InnoDB 存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。

AHI 有一个要求,对这个页的连续访问模式必须是一样的。例如对于 (a,b) 这样的联合索引页,其访问模式可以是下面情况:

  • where a=xxx
  • where a =xxx and b=xxx

访问模式一样是指查询的条件是一样的,若交替进行上述两种查询,那么 InnoDB 存储引擎不会对该页构造 AHI。
AHI 还有下面几个要求:

  • 以该模式连续访问了 100 次
  • 以该模式连续访问了 页中记录总数/16 次

必须同时满足上述所有要求才会建立 AHI。

InnoDB 存储引擎官方文档显示,启用 AHI 后,读取和写入速度可以提高 2 倍,辅助索引的连接操作性能可以提高 5 倍。

三、常见索引失效场景

1、查询条件包含 or

SELECT * FROM order WHERE order_id = 1 OR pay_method=’123’;

当 or 左右查询字段只有一个是索引,该索引失效;只有当 or 左右查询字段均为索引时,才会生效。

2、索引列上有计算、函数等操作

SELECT * FROM order WHERE order_id +1 = 2;

3、使用负向查询(!=、<>、not in、not exists、not like 等)

SELECT * FROM order WHERE order_id <> 2;

4、5.7 之前的 is null 和 is not null

SELECT * FROM order WHERE order_id is not null;

5.7 之后 is null 和 is not null 也会走索引,但对于使用了声明了 NOT NULL 的索引行不会。

5、不符合最左前缀原则的组合索引

当查询涉及到联合索引时,查询的条件必须是联合索引的一个前缀。比如对于联合索引 A/B/C/D,查询的条件可以是 A,也可以是 A/B/C,但不能是 B/C。另外对于范围查询,只能有一个条件是范围查询且必须是最后一个。比如查询 A/B/C,只有 C 可以是范围查询。另外在 MySQL 中,IN 被定义为范围查询,但却是当作多个条件等于来处理,因此 IN 语句放在中间,也会走索引。

6、like 以通配符开头

SELECT * FROM order WHERE pay_method LIKE ‘%23’;

7、字符串不加单引号

SELECT * FROM order WHERE pay_method = 123;

这个原因是不加单引号时,是字符串跟数字的比较,它们类型不匹配,MySQL 会做隐式的类型转换,把它们转换为浮点数再做比较。而类型转换是一个函数,MySQL 不支持带有函数的索引查询,所以不会走索引。

8、当全表扫描速度比索引速度快时,mysql 会使用全表扫描,此时索引失效

一个有意思的例子是 IN 的索引失效。MySQL 优化器对开销代价的估算方法有两种:index dive 和 index statistics。前者统计速度慢但是能得到精准的值,后者统计速度快但是数据未必精准。老版本的 MySQL 默认使用 index dive 这种统计方式,但在 IN() 组合条件过多的时候会发生很多问题。查询优化可能需要花很多时间,并消耗大量内存。因此新版本 MySQL 在组合数超过一定的数量(eq_range_index_dive_limit)就会使用 index statistics 统计。而 index statistics 统计的结果不精确,因此可能会出现 IN 不走索引的情况。此时可以尝试通过增加 eq_range_index_dive_limit 的值(5.6 中默认是 10,5.7 中默认是 200)让 IN 语句走索引。

四、高性能索引策略

1、使用前缀索引

在对一个比较长的字符串建立索引的时候,把字符串所有字符放入索引是比较低效的做法。前文对字符串做哈希是一种方式。也可以使用字符串的前缀做索引。比如

CREATE INDEX index_name ON table_name (column_name(10));

表示将列的前 10 个字符做索引,这样做的好处是减少索引字段的大小,可以在一页内放入更多的数据,从而降低 B-tree 的高度,同时更短的索引字段带来更短的匹配时间,提高了查找效率。

2、使用覆盖索引

覆盖索引是一种索引包含了查询所需所有数据的情况,在这种情况下,MySQL 可以使用索引来直接获取列的数据,这样就不需要再读取数据行。覆盖索引是非常有用的工具,能够极大地提升性能:

  • 索引条目远小于数据行大小,所以如果只需要读取索引,MySQL 就会极大地减少数据访问量。这对缓存的负载非常重要,因为这种情况下响应时间大部分花费在数据拷贝上。
  • 因为索引是按值顺序存储的(至少在单个页内如此),对于 I/O 密集型的范围查询会比随机从磁盘读取每一行数据的 I/O 要少得多。
  • 对于 innodb 的聚簇索引,覆盖索引特别有用。innodb 的二级索引在叶子节点中保存了行的主键值,所以如果二级主键能够覆盖索引,则可以避免对主键索引的二次查询。

3、延迟关联

覆盖索引可以极大地提升查找的效率,但很多时候我们会遇到 select * 这样的需求,这时使用覆盖索引就不可能了。不过我们可以使用延迟关联的方式利用覆盖索引。

比如对于语句:

select from t_portal_user where create_time > ‘2012-10:10’ and create_time<’2017:10:10’ LIMIT 5000,10;

可以改写成:

SELECT from t_portal_user INNER JOIN (select id from t_portal_user where create_time > ‘2012-10:10’ and create_time<’2017:10:10’ LIMIT 5000,10) as a USING(id);

对于子查询:

select id from t_portal_user where create_time > ‘2012-10:10’ and create_time<’2017:10:10’ LIMIT 5000,10;

如果在 create_time 上做了索引(innodb 中主键会被默认添加进索引中),则可以利用覆盖索引找到符合条件的 id,再根据 id 做普通查询。

4、利用索引来做排序

MySQL 支持二种方式的排序,文件排序和索引,后者效率高,它指 MySQL 扫描索引本身完成排序。文件排序方式效率较低。ORDER BY 满足以下情况,会使用 Index 方式排序:

  • 使用覆盖索引,即通过扫描索引本身就可完成排序
  • ORDER BY 语句 或者 WHERE , JOIN 子句和 ORDER BY 语句组成的条件组合满足最左前缀(一个例外是 IN,IN 在没有排序的最左匹配中被视为等值查询,对排序来说是一种范围查询)
  • ORDER BY 语句中条件的排序顺序是一样(都为正序或者都为倒序)

5、自定义哈希索引

如果存储引擎不支持哈希索引,则可以在 B-tree 基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事,因为还是使用 B-tree 进行查找,但它使用的是哈希值而不是键本身进行查找。

SELECT id FROM user WHERE address = “zhejiang ningbo”;

若删除原来 URL 列上的索引,而新增一个被索引的 address_crc 列,使用 CRC32 做哈希,就可以使用下面的方式查询:

SELECT id FROM user WHERE address=”zhejiang ningbo” AND url_crc=CRC32(“zhejiang ningbo”);

这样做的性能会非常高,因为 MySQL 优化器会使用这个选择性很高而体积很小的基于 url_crc 列的索引来完成查找。即使有多个记录有相同的索引值,查找仍然很快,只需要根据哈希值做快速的整数比较就能找到索引条目,然后一一比较返回对应的行。