mysql-高性能的索引

索引

索引是数据结构。对数据库表中一列或多列的值进行排序的结构。类比于字典的目录,如搜索”mysql”,先在目录定位到”m”,然后找到”mysql”所在的页数,找到”mysql”。也可以理解为排好序的快速查找结构,对于MySQL数据库,除了存储着数据,还存储着满足特定查找算法的数据结构(即索引),这些数据结构以某种方式指向数据。

索引类型

索引的类型有很多种,可以为不同的场景提供更好的性能。在MySQL中,索引是在存储引擎层而不是服务器层实现的。所以没有统一的标准:不同存储引擎的索引的工作方式并不一致,也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引,其底层的实现也可能不同。

B-Tree索引

大多数MySQL索引都支持这种索引。不过存储引擎底层可能使用不同的存储结构。对于InnoDB来说,使用的是B+Tree。

存储引擎以不同的方式使用B-Tree索引,性能各有不同,各有优略。

B-Tree通常意味着所有的值都是按顺序存储的,并且每个叶子页到根的距离相同。对于主键索引来说,叶子节点包含了所有的数据,以及指向下一个叶子页的指针。

对于InnoDB来说,一个逻辑页的大小为16k。

B-Tree能够加快访问数据的速度,因为存储引擎不需要进行全表扫描来获取需要的数据, 取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针,存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点,这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么找到值要么该记录不存在。

叶子节点比较特别,他们的指针指向的是被索引的数据,而不是其他节点页。

B-Tree的索引列是顺序组织存储的,所以很适合范围查找。

需要注意的是,索引对多个值进行排序的依据是定义索引时列的顺序。

可以使用B-Tree索引的查询类型

B-Tree的结构决定了其适合于全键值、键值范围或键前缀查找(最左前缀法则)。

假设有如下数据表

1
2
3
4
5
6
7
create table people (
last_name varchar(50) not null ,
first_name varchar(50) not null ,
dob date not null ,
email varchar(50) not null ,
key(last_name, first_name, dob)
)

前面所说的索引对如下类型的查询有效。

  • 全值匹配

    全值匹配指的是和索引中的所有列进行匹配。

  • 匹配最左前缀

    可以查找所有 last_name 为 tom的人,即只使用索引的第一项。

  • 匹配列前缀

    也可以只匹配某一列的值的开头部分。例如上面的索引可以用于查找所有以J开头的姓的人。也只使用了索引的第一列。

  • 匹配范围值

    可以用于查找姓在 Allen 和 Barry 之间的人。也只使用索引的第一列。

  • 精确匹配某一列并范围匹配另外一列

    可以用于查找姓为 Allen ,并且名字是字母K开头的人,即第一列last_name全匹配,第二列first_name范围匹配。

  • 只访问索引的查询

    B-Tree通常可以支持“只访问索引的查询”,即查询只需要访问索引,而无需访问数据行。后面单独说。

因为索引树中的节点是有序的,所以除了按值查找外,索引还可以用于查询中的ORDER BY操作。一般来说,如果B-Tree可以按照某种方式查找到值,那么也可以按照这种方式用于排序。所以,如果ORDER BY字句满足前面列出的几种查询类型,则这个索引页可以满足对应排序的需求。

B-Tree索引的一些限制

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

哈希索引

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

在MySQL中,只有Memory引擎支持哈希索引。也是Memory引擎默认的索引类型,Memory引擎同时也支持B-Tree索引。

哈希索引的限制

  • 哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行。不过访问内存中的行速度非常快,所以大部分情况下这一点对性能的影响并部明显。
  • 哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序。
  • 哈希索引也不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如,在数据列(A, B)上建立哈希索引,如果查询只有数据列A,则无法使用该索引。
  • 哈希索引只支持等值查询。包括 = 、in()、<=>,也不支持任何范围查询。
  • 访问哈希索引的数据非常快,除非有很多哈希冲突。
  • 哈希冲突很多的话,一些索引的维护代价会很高。

因为这些限制,哈希索引只适用于特定的场合。

InnoDB引擎有一个特殊的功能叫“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时,它会在内存中基于B-Tree索引之上再创建一个哈希索引,这样久让B-Tree索引也具有哈希索引的一些优点,比如快速的哈希查找。这是一个完全自动化、内部的行为,用户无法控制或配置。

创建自定义哈希索引

若存储引擎不支持哈希索引,可以使用如下方式来创建伪哈希索引。

思路是这样的:在B-Tree基础上创建一个伪哈希索引。和真正的哈希索引不一样,还是使用B-Tree进行查找,但是它使用哈希值而不是键本身进行索引查找。需要做的就是在where子句中手动指定使用哈希函数。

索引的优点

索引可以让服务器快速地定位到表的指定位置。但这不是索引的唯一作用。

对于B-Tree索引来说,按照顺序存储数据,所以MySQL可以用来做ORDER BY和GROUP BY操作。因为数据是有序的,所以B-Tree也就会将相关的列值都存储在一起。最后,因为索引中存储了实际的列值,所以某些查询只使用索引就能够完成全部查询。

据此,总结出索引有如下三个优点:

  1. 索引大大减少了服务器需要扫描的数据量。
  2. 索引可以帮助服务器避免排序和临时表。
  3. 索引可以将随机I/O变为顺序I/O。

高性能的索引策略

正确创建和使用索引时实现高性能查询的基础。

高效地选择和使用索引有很多种方式,其中有些是针对特殊案例的优化方法,有些则是针对特定行为的优化。使用哪个索引,以及如何评估选择不同索引的性能影响的技巧,需要不断的学习。接下来几个小节将学习如何高效地使用索引。

独立的列

通常会看到一些查询不当地使用索引,使得MySQL无法使用已有的索引。如果查询中的列不是独立的,则MySQL就不会使用索引。所谓独立的列,指的是,索引列不能是表达式的一部分,也不能是函数的参数。

如:

1
2
3
select name from user where age + 1 = 18;
或是
select name from user where TO_DAYS(CURRENT_DATE) - TODAYS(date_col) <= 10;

我们应该养成简化where条件的习惯,始终将索引列单独放在比较符号的一侧。

前缀索引和索引选择姓

有时候需要索引很长的字符列,这会让索引变得大且慢。通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引的效率,但这样也会降低索引的选择性。

索引的选择性

指的是不重复的索引值(也称为基数)和数据表记录总数(#T)的比值,范围从 1/#T 到 1 之间,索引的选择性越高则查询效率越高,因为选择性高的索引可以让MySQL过滤更多的行。

唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。

完整列的选择性计算如下:

1
2
> select count(distinct name) / count(*) from t_table;
>

一般情况下,某个列前缀的选择性也是足够高的,足以满足查询性能。对于很长的VARCHAR类型的列,必须使用前缀索引,因为MySQL不允许索引这些列的完整长度。

诀窍在于要选择足够长的前缀以保证较高的选择性,同时又不能太长。简单来说,就是前缀的“基数”应该接近于完整列的“基数”。

下面展示在同一个查询中计算不同前缀长度的选择性:

1
2
3
4
5
6
select count(distinct left(name, 3)) / count(*) as sel3,
count(distinct left(name, 4)) / count(*) as sel4,
count(distinct left(name, 4)) / count(*) as sel5,
count(distinct left(name, 4)) / count(*) as sel6,
count(distinct left(name, 4)) / count(*) as sel7
from t_table;

当前缀的长度达到一定程度时,再增加前缀长度,选择性提升的幅度已经很小的话,我们就可以确定前缀索引的长度。

下面展示如何创建前缀索引:

1
alter table t_table add key(name(7));

前缀索引时一种使索引更小、更快的有效办法,但也有其缺点:MySQL无法使用前缀索引做ORDER BY和GROUP BY,也无法使用前缀索引做覆盖扫描。

一种常见的做法是,针对很长的UUID使用前缀索引。

多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。

如表t_table在字段 a 和 b 上各有一个单列索引。对于下面这种WHERE条件,两个单列索引都不是很好的选择。

1
select a, b from t_table where a = 1 or b = 1;

在5.0以上的版本中,查询能够同时使用这两个单列索引进行扫描,并且将结果进行合并。这种算法有三个变种:OR条件的联合、AND条件的相交、组合前两种情况的联合和相交。

索引合并策略有时候是一种优化的结果,但实际上更多时候说明了表上的索引建的都很糟糕。

  • 当服务器对多索引做相交操作的适合(通常是多个AND条件),通常意味着需要一个包含所有相关列的多列索引。
  • 当服务器需要对多个索引做联合操作时(通常有多个OR条件),通常需要耗费大量CPU和内存资源在算法缓存、排序和合并操作上。
  • 还有一点是,优化器不会把这些计算到“查询成本”中,导致查询的成本被低估,导致该执行计划还不如走全表扫描。

选择合适的索引列顺序

正确的顺序依赖于使用该索引的查询,并且同时要考虑如何更好地满足排序和分组的要求。

在一个多列索引中,索引列的顺序意味着索引首先按照最左列进行排序,其次是第二列等等。所以,索引可以按照升序或降序进行扫描,以满足精确符合列顺序的ORDER BY,GROUP BY和DISTINCT等子句的查询需求。

所以多列索引的列顺序至关重要。

对于如何选择索引列顺序有一个经验法则:将选择性最高的列放到索引最前列。这在某些场景下可能有帮助,但通常不如避免随机IO和排序那么重要,考虑问题需要更加全面。当不需要考虑排序和分组时,将选择性最高的列放到最前面通常是很好的。这种时候索引的作用只是用于优化WHERE条件的查找。在这种情况下,这样设计的索引确实能够最快过滤出需要的行,对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高,然而性能不只是依赖于所有索引列的选择性(整体基数),也和查询条件的具体值分布有关。可能需要根据那些运行频率最高的查询来调整索引列的顺序,让这种情况下索引的选择性更高。

以下面查询为例:

1
select * from t_table where a = 1 and b = 2;

应该创建一个(a,b)索引还是应该颠倒一下顺序?可以先来确认一下值的分布情况,并确定哪个列的选择性更高。先看看给个WHERE条件的分支对应的数据基数有多大:

1
2
3
select sum(a = 1), sum(b = 2) from t_table;
sum(a = 1) : 5000
sum(b = 2) : 100

根据经验法则,应该将b放到索引列的前面,因为对应条件值时,b数量更小,再来看看对于这个 b = 2的条件值,对应的a列选择性如何:

1
2
select sum(a = 1) form t_table where b = 2;
sum(a = 1) : 20

这样做有一个地方需要注意,查询的结果非常依赖于选定的具体值。如果按照上述办法进行优化,可能对于其他一些条件值的查询不公平,服务器的整体性能可能变得很糟糕,或者某些查询变得不如预期。

聚簇索引

并不是一种单独的索引类型,而是一种数据存储方式。具体细节依赖于实现方式,对于InnoDB来说,聚簇索引实际上在同一个结构中保存了B-Tree索引和数据。

当表有聚簇索引时,它的数据行实际上存放在索引的叶子页,术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引。

对于InnoDB来说,叶子页包含了行的全部数据,但是节点页只包含了索引列。其中索引列只的就是主键列。

聚集的数据优点:

  • 可以把相关数据保存在一起
  • 数据访问更快
  • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

聚簇索引的缺点:

  • 聚簇数据最大限度地提高了I/O密集型应用的性能,但如果数据全部都放在内存,则访问的顺序就没那么重要。
  • 插入速度严重依赖于插入顺序。
  • 更新聚簇索引列的代价很高。
  • 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临“页分裂”的问题。页分裂会导致表占用更多的磁盘空间。
  • 聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候。
  • 二级索引可能比想象的大,因为在二级索引的叶子节点包含了引用行的主键列。
  • 二级索引访问需要两次索引查找,而不是一次。

二级索引保存的不是指向行的物理位置的指针,而是行的主键值。意味着通过二级索引查找行,存储引擎需要找到二级索引的叶子节点获得对应的主键值,然后根据这个值去聚簇索引中查找对应的行。

InnoDB的数据分布

InnoDB聚簇索引叶子节点包含:主键列、事务ID(TID)、回滚指针(RP,用于事务和MVCC)、以及非主键列。

InnoDB二级索引叶子节点包含:索引列和主键列。

在InnoDB表中按照主键顺序插入行。这样的一个好处是,会保证顺序插入到页:每条新纪录总是在前一条记录的后面插入,当页被插满后,继续插入到新的页。

若主键按照随机值插入行。会有这么一些缺点:写入是乱序的,InnoDB不得不频繁地做页分裂操作,以便为新行分配空间。页分裂会导致移动大量数据,一次插入最少需要修改三个页而不是一个页。

覆盖索引

MySQL可以使用索引来直接获取列的数据,这样就不需要读取数据行。如果索引的叶子节点中已经包含了需要查询的数据,就不需要回表查询了。如果索引包含(或者覆盖)所有需要查询的字段的值,那么就称为“覆盖索引”。

覆盖索引会带来很多好处:

  • 索引条目通常远小于数据行大小,所以如果只需要读取索引,那么MySQL就会极大地减少数据访问量。
  • 因为索引是按照列值顺序存储的,所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多。
  • 对于InnoDB来说,而二级索引在叶子节点保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询。

MySQL只能使用B-Tree索引做覆盖索引。当使用到一个覆盖的查询时,在EXPLAIN的Extra列可以看到“Using Index”的信息。

使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果:通过排序操作;或者按索引顺序扫描;如果EXPLAIN出来的type列的值为”index”,则说明MySQL使用索引扫描来做排序。

MySQL可以使用同一个索引即满足排序,又用于查找行。

只有当索引列的顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向(倒序或正序),MySQL才能使用索引来对结果做排序。如果查询需要关联多张表,则只有ORDER BY子句引用的字段全部为第一个表时,才能使用索引做排序。ORDER BY子句需要满足索引的最左前缀法则,否则无法利用索引排序。

有一种情况下,ORDER BY子句可以不满足索引的最左前缀法则,就是前导列为常量的时候。如果WHERE子句或者JOIN子句对这些列制定了常量,就可以弥补索引的不足。

如:

对数据库表t_table在列(a, b, c)创建索引。对于下面的查询,可以使用到该索引:

1
select b, c from t_table where a = 10 order by b, c;

即使ORDER BY不满足索引最左前缀的要求,也可以用于查询排序,这是因为第一列被指定为一个常数。

下面这个查询也没问题:

1
select b, c from t_table where a = 10 order by b desc;

下面这个查询也没有问题:

1
select b, c from t_table where a > 10 order by a, b;

下面展示不能使用索引做排序的查询:

  • 使用两种不同的排序方向,但是索引列都是正序排序的

    1
    select b, c from t_table where a = 1 order by b desc c asc;
  • ORDER BY子句中引用了一个不在索引中的列:

    1
    select b, c from t_table where a = 1 order by  b, d;
  • 无法组合成最左前缀

    1
    select b, c from t_table where a = 1 order by c;
  • 第一列使用范围条件,MySQL无法使用索引的其余列

    1
    select b,c from t_table where a> 1 order b , c;
  • 在b列上有多个等于条件。对于排序来说,也是一种范围查找

    1
    select b, c from t_table where a = 1 and  b in (1,2) order by c;

如何设计索引

对于一些复杂的需求,我们第一件要考虑的事情是需要使用索引来排序,还是先检索数据再排序。使用索引排序会严格限制索引和查询的设计。

支持多种过滤条件

例如对于 ast_mq_task 来说,yn和status列的选择性通常不高,很多查询都会用到,考虑到使用的频率,建议在创建不同组合索引的时候将(yn, status)作为前缀。

如果某个查询不显示status,也可以通过 and status in (1,2,3)来让MySQL选择此索引。这样就不会过滤任何行,和没有这个条件时返回的结果相同。

避免多个范围条件

优化排序

文件排序对于小数据集时很快的,但是对于一个查询匹配的结果有上百万行数据的时候,就会特别慢。

对于那些选择性非常低的列,可以增加一些特殊的索引来排序。例如可以创建(sex, rating)索引用于下面的查询:

1
select <cols> from profiles where sex = 'M' order by rating Limit 10;

这个查询同时使用了 ORDER BY 和 LIMIT,如果没有索引的话查询会很慢。

即使有索引,如果用户界面上需要翻页,并且翻页翻到比较靠后时查询也可能非常慢。下面这个查询就通过ORDER BY和LIMIT偏移量的组合翻页到很后面的时候:

1
select <cols> from profiles where sex = 'M' order by rating limit 10000, 10;

无论如何创建索引,这种查询都是个严重的问题。因为随着偏移量的增加,MySQL可能需要花费大量时间来扫描需要丢弃的数据。

优化这类索引的另一个比较好的策略是使用延迟关联,通过使用覆盖索引查询返回需要的主键,再根据这些主键关联原表获得需要的行。这可以减少MySQL扫描那些需要丢弃的行数。下面这个查询显示了如何高效地使用(sex, rating)索引进行排序和分页:

1
2
3
select <cols> from profiles inner join (
select <primary key> from profiles where x.sex= 'M' order by rating Limit 10000, 10
) as x using(<primary key cols>);

索引碎片

B-Tree索引可能会碎片化,这会降低查询的效率。表的数据存储也可能碎片化。

可以通过执行 OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。对于InnoDB来说,可以通过先删除,再创建的方式来消除索引的碎片化。

什么情况下创建索引

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 查询中与其它表关联的字段,外键关系建立索引
  4. 频繁更新的字段不适合创建索引
  5. Where条件里用不到的字段不创建索引
  6. 单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)
  7. 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
  8. 查询中统计或者分组字段

什么情况下不创建索引

  1. 表记录太少
  2. 经常增删改的表
  3. 数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列建立索引。注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
0%