《高性能MySQL》(四)创建高性能索引

《高性能MySQL》(四)创建高性能索引

一. 概述

1.1 什么是索引?

索引就像是书的目录,在MySQL中,存储引擎先在索引中找到对应值,然后根据匹配的索引记录找到对应的数据行。

1
2
SELECT first_name FROM XXX WHERE actor_id = 5;
--若actor_id列上有创建索引,MySQL先在索引上按值查找,然后返回所有包含值得数据行

MySQL只能高效的使用索引的最左前缀列,索引中列的顺序很重要

1.2 索引的类型

1.2.1 B-Tree 索引

虽然叫B-Tree索引,但存储引擎可能使用不同的存储结构,比如InnoDB使用的是 B+Tree、NDB集群使用的是 T-Tree 。MyISAM使用前缀压缩技术使得索引更小,InnoDB则按原数据格式存储;MyISAM索引通过数据的物理位置引用被索引的行,InnoDB则根据主键引用被索引的行。

B-Tree索引中所有值按顺序存储,每个叶子页到根的距离相同:

存储引擎从索引的根节点开始搜索,根节点的槽中存放了指向子节点的指针,通过指针向下层查找,通过比较节点页和要查找的值大小可以找到合适的指针到下一个子节点。叶子节点的指针指向被索引的数据。这种顺序存储很适合范围查询。

对于People表有索引包含last_name、first_name和dob,索引结构如下:

B-Tree 索引适用于全键值、键值范围或键前缀查找,键前缀查找只适用最左前缀:

  • 全值匹配:和索引中所有列匹配,如查询叫 Cuba Allen、出生于XXX年的人。
  • 匹配最左前缀:只匹配索引第一列,如查询所有姓Allen的人。
  • 匹配列前缀:只匹配列值的开头部分,如查询所有J开头姓的人。
  • 匹配范围值:匹配列值得某个范围,如查询所有姓Allen与Barry之间的人。
  • 精确匹配某一列并范围匹配另外一列:第一列全匹配,第二列范围匹配,如查询所有姓Allen,名以K开头的人。
  • 只访问索引的查询:即查询只需访问索引,无须访问数据行,即覆盖索引

B-Tree索引支持的查询同样可以支持 ORDER BY 操作。

限制:

  • 非最左列开始无法命中索引;
  • 不能跳过索引中的列;
  • 查询中若有某个列的范围查询,则其右边所有列都无法使用索引优化查找。

即从左到右依次扫描索引列,到遇到第一个范围查询(>=, >,<, <=, between ….. and ….)就停止扫描。

1.2.2 哈希索引

  • Memory和NDB存储引擎支持,基于哈希表实现,只有精确匹配索引所有列的查询才有效。
  • InnoDB有一个特殊的自适应哈希索引,在某些索引值使用非常频繁时,会在内存中基于B-Tree索引再创建一个哈希索引,从而加快查找速度。
  • 每行数据会计算得到一个哈希码,存放在哈希索引中,同时在哈希表中保存指向每个数据行的指针。
  • 哈希索引只包含哈希值和行指针,不存储字段值,所以不能像B-Tree索引那样避免读取行,但一般读行速度很快。
  • 不按索引值顺序存储,无法用于排序。
  • 不支持部分索引列匹配查找。
  • 只支持等值比较查询,不支持任何范围查询。
  • 哈希冲突需要遍历链表所有行指针,所以会影响性能。

1.2.3 空间数据索引(R-Tree)

  • MyISAM支持空间索引,用来存储地理位置。

1.2.4 全文索引

  • 全文索引类似于搜索引擎,查找的是文本中的关键词,而不是直接比较索引的值。
  • 适用于 MATCH AGAINST 操作,而不是普通的 WHERE 条件操作。

1.3 索引的优点

  • 提高查询效率。
  • 避免排序和临时表:B-Tree索引可以用于 ORDER BY 和 GROUP BY 操作。
  • 减少了需要扫描的数据量:某些情况只使用索引就可以完成查询。
  • 将随机I/O变为顺序I/O。

索引非银弹,只有在索引能大大提高查询的效率,大于其带来的额外工作时,才是有效的,对于特别小的表全表查询可能会更高效,而特别大的表创建和使用索引的代价可能过于高昂。

二. 高性能索引策略

2.1 独立的列

索引列不能是表达式的一部分,也不能是函数的参数

1
2
SELECT actor_id FROM XXX WHERE actor_id + 1 = 5;
SELECT ... FROM XXX WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

2.2 前缀索引和索引选择性

当需要索引很长的字符列时(特别是BLOB、TEXT或很长的VARCHAR只能使用前缀索引),可以选择模拟哈希索引,也可以只索引开始的部分字符。

我们需要选择足够长的前缀保证有较高的选择性,可以通过真实数据统计前缀长度为多少时最合适。

1
2
--创建前缀索引
ALTER TABLE XXX ADD KEY (city(7));

前缀索引无法用于 ORDER BY 和 GROUP BY,也无法做覆盖扫描。

2.3 多列索引

为多列的每个列创建单独的索引一般不能提高查询性能,最好情况下也可能与最优索引的效率差几个数量级。

在EXPLAIN中看到索引合并(index_merge)大部分情况说明索引构建的很糟糕,有时甚至不如直接改写为UNION的方式。

2.4 选择合适的索引列顺序

一个多列的B-Tree索引会按照最左列排序,然后是第二列、第三列等等。在不考虑排序和分组时,将选择性最高的列(根据条件命中条数最少)放到索引的最前列

案例:

  1. 慢查询SQL:

    1
    2
    3
    4
    SELECT COUNT(DISTINCT threadId) AS COUNT_VALUE
    FROM Message
    WHERE (groupId = 10137) AND (userId = 1288826) AND (anonymous = 0)
    ORDER BY priority DESC, modifiedDate DESC
  2. 执行计划:看似选择了索引 (groupId, userId) 很合理

    1
    2
    3
    4
    5
    6
    7
    8
    9
    id:1
    select_type:SIMPLE
    table:Message
    typeref
    key:ix_groupId_userId
    key_len:18
    ref: const,const
    rows1251162
    Extra:Using where
  3. 选择性分析:条件几乎命中所有行,即索引没起到什么用

    1
    2
    3
    4
    5
    SELECT COUNT(*),              --4142217
    SUM(groupId = 10137), --4092654
    SUM(userId = 1288826), --1288496
    SUM(anonymous = 0) --4141934
    FROM Message
  4. 解决方案是修改应用代码,区分这部分异常数据。

2.5 聚簇索引

聚簇索引是一种数据存储方式,InnoDB的聚簇索引实际上在同一结构中保存了B-Tree索引和数据行。聚簇表示数据行和相邻的键值紧凑的存储在一起,当表存在聚簇索引时,其数据行实际存放在索引的叶子页(leaf page)中。

如下图,叶子页包含了行的全部数据,而节点页只包含索引列:

InnoDB通过主键聚集数据,若没有指定主键,InnoDB会选择一个唯一的非空索引代替,没有这样的索引则会隐式的定义一个主键来作为聚簇索引。InnoDB只聚集同一个页面的记录,非同一页即使相邻键值也可能相距甚远。

聚集的优点:

  • 将相关的数据保存在一起,减少磁盘I/O操作;
  • 数据访问更快,因为索引和数据保存在同一个B-Tree。
  • 使用覆盖索引扫描的查询可以直接使用页节点的主键值。

聚集的缺点:

  • 若数据存放在内存,访问顺序就没那么重要,聚簇索引对于I/O的提升就不明显了。
  • 插入速度严重依赖于插入顺序,最快是按照主键顺序插入,若非此顺序最好加载完成后通过 OPTIMIZE TABLE 重新组织一下表。
  • 更新聚簇索引的代价高昂,会强制InnoDB将每个被更新的行移动到新位置。
  • 基于聚簇索引的表在插入新行、或主键更新导致需要移动行时,会面临页分裂问题。行需要插入到某个已满的页中,存储引擎要把页分裂成两个页来容纳新行。
  • 可能导致全表扫描变慢,特别是行比较稀疏或页分裂导致数据存储不连续的时候。
  • 二级索引(非聚簇索引)要比想象的大,因为叶子节点包含了引用行的主键列。
  • 二级索引访问需要两次索引查找,而不是一次。因为二级索引叶子节点保存的不是行的物理位置,而是行的主键值。在找到二级索引获得主键值后,仍需要去聚簇索引找到对应的行。所以InnoDB引入自适应哈希索引减少这样的重复工作。

聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。若主键是一个列前缀索引,InnoDB也会包含完整的主键列和剩下的其他列。

AUTO_INCREMENT 自增列保证数据行是按顺序写入,从性能考虑使用UUID作为聚簇索引会很糟糕,插入变得完全随机。

2.6 覆盖索引

设计索引时不仅仅要考虑WHERE条件部分,特别是能直接通过索引获取列数据的情况。如果一个索引包含所有需要查询的字段的值,可以被称为覆盖索引。只有B-Tree索引可以作为覆盖索引。在EXPLAIN的Extra对应 Using index 信息。

InnoDB的二级索引的叶子节点包含主键的值,所以即使本身没有包含主键,二级索引也可以额外用于对主键做覆盖查询。

优点:

  • 减少数据访问量;
  • 索引至少在单个页内是按列值顺序存储的,所以范围查询相比随机读取的I/O少很多。
  • 由于聚簇索引的原因,覆盖索引对InnoDB特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值,若二级主键可以覆盖查询,就可以避免对主键索引的二次查询。

全字段模糊查询优化:延迟关联

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
EXPLAIN SELECT * FROM products WHERE actor = 'SEAN CARREY'
AND title like '%APOLLO%';
--type:ref
--possible_keys:ACTOR,IX_PROD_ACTOR
--key:ACTOR
--key_len:52
--Extra:Using where

--查询选择了表的所有列,而没有索引覆盖了这么多列,但WHERE条件actor命中了索引,MySQL不能再索引中执行LIKE操作
--优化方案:创建索引(artist,title,prod_id)
SELECT * FROM products
JOIN (SELECT prod_id FROM products WHERE actor = 'SEAN CARREY'
AND title like '%APOLLO%';) AS T1 ON (t1.prod_id=products.prod_id)
--1.select_type: PRIMARY
--2.select_type: PRIMARY
--3.select_type: DERIVED
--3.type: ref
--3.key: ACTOR_2
--3.Extra: Using where; Using index

2.7 使用索引扫描来做排序

两种方式生成有序结果:

  1. 排序操作;
  2. 按索引顺序扫描。EXPLAIN得到type值为index。

只扫描索引本身很快,但如果不能覆盖所有列,就需要回表查询一次,而回表查询基本是随机I/O,相比顺序地全表扫描要慢。

只有索引顺序和 ORDER BY 操作顺序一样时,才可以作为排序结果。

2.8 压缩索引

MyISAM使用前缀压缩来减小索引的大小,比如索引块第一个值是“perform”,第二个值是“performance”,压缩后第二个值是“7,ance”,减少空间的代价是某些操作会更慢,因为前后的依赖性所以无法使用二分查找。

2.9 冗余和重复索引

  • 产生冗余和重复索引的原因
    • MySQL允许在相同列上创建多个索引,但需要单独维护重复的索引,且优化器在优化查询时也要逐个考虑。
    • 重复索引可能会因为一些失误构建出来,如若发现应移除。表中索引越多会导致INSERT、UPDATE、DELETE速度越慢
  • 区分冗余索引:(A)是(A,B)的冗余索引,但(B,A)或(B)则不是冗余索引。尽量扩展旧索引而不是创建新索引,有时也会需要冗余索引,因为扩展已有索引可能会导致其变得太大,从而影响其他使用该索引的查询。
  • 如何找到冗余和重复索引
    • 可以通过自己编写查询访问 INFORMATION_SCHEMA 表;
    • 或是通过如 Shlomi Noach 的 common_schema 以及 Percona Tookit 的 pt-duplicate-key-checker 等工具找出冗余和重复的索引并删除。

2.10 索引和锁

索引可以使查询锁定更少的行,InnoDB只有在访问行时才会对其加锁,索引可以减少访问的行数,需要存储引擎能够过滤掉不需要的行。

查询只返回2~4之间的行,但却获取了1到4之间行的排它锁,因为MySQL为查询选择的执行计划是索引范围扫描。

1
2
3
4
5
6
SET AUTOCOMMIT=0;
BEGIN;
SELECT actor_id
FROM XXX
WHERE actor_id < 5
AND actor_id <> 1 FOR UPDATE;

存储引擎从索引开头获取满足 actor_id < 5 的记录,并不知道可以过滤第一行记录,Using where 表示MySQL服务器将存储引擎返回行以后再应用过滤条件。

1
2
3
4
5
6
7
EXPLAIN SELECT actor_id 
FROM XXX
WHERE actor_id < 5
AND actor_id <> 1 FOR UPDATE;
--type: range
--key: PRIMARY
--Extra: Using where; Using index

此时保证第一个连接打开,再开启第二个连接查询第一行记录,此查询会被挂起:

1
2
3
4
BEGIN;
SELECT actor_id
FROM XXX
WHERE actor_id = 1 FOR UPDATE;

InnoDB在二级索引上使用共享锁(读锁),但访问主键索引需要排它锁(写锁),从而无法使用覆盖索引,使SELECT FOR UPDATE 比 LOCK IN SHARE MODE 或非锁定查询要慢很多。

2.11 实用技巧

  • 实际创建索引时经常会将一些选择性不高但频繁使用的列作为索引前缀列,如性别SEX,即使某些查询不需要这些列作为查询条件,也可以加上 SEX IN('m','f') 来匹配索引(仅使用列表值不多时)。
  • 一些常用于范围查询的列尽量放在组合索引的最后,比如日期、年纪等,因为匹配索引会在第一个范围查询终止,当然一些情况也可以用IN来代替范围查询。
  • 通过执行 OPTIMIZE TABLE 或者导出再导入的方式重新整理数据,这样可以消除索引和数据的碎片化。


参考:

🔗 《高性能MySQL》