转载请备注来源: 《MySQL性能优化学习笔记-(2)索引优化》 | shuwoom.com

一、前言

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

索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时,索引对性能的影响愈发重要。在数据量较小且负载较低时,不恰当的索引对性能的影响可能还不明显,但当数据量逐渐增大时,性能则会急剧下降。

索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高几个数量级,“最优”的索引有时比“好的”索引性能要好两个数量级。创建一个真正“最优”索引经常需要重写查询,这个会在下一篇文章介绍。

二、索引基础

mysql在使用索引时,现在索引中找到对应值,然后根据匹配的索引记录找到对应的数据行。例如要运行下面的查询:

如果在actor_id列上建有索引,则mysql将使用索引找到actor_id为5的行。

索引可以包含一个或多个列的值。如果索引包含多个列,那么列的顺序也十分重要,因为mysql只能高效地使用索引的最左前缀列。

1. 索引类型

在mysql中,索引是在存储引擎层而不是服务器层实现的。

(1) B-Tree索引

一般没有特别指明类型,多半都是说B-Tree索引。

存储引擎以不同的方式使用B-Tree索引,性能也各有不同,各有优劣。例如,MyISAM使用前缀压缩技术使得索引更小,但InnoDB则按照原数据格式进行存储。MyISAM索引通过数据的物理位置引用被索引的行,而InnoDB则根据主键引用被索引的行。

B-Tree通常意味着所有的值都是按顺序存储的。下图展示了B-Tree索引的抽象表示,大致反映了InnoDB索引是如何工作的。

B-Tree索引的抽象表示

B-Tree索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始进行搜索。

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

假如有如下数据表:

对表中的每一行数据,索引包含了last_name、first_name和dob列的值,下图展示了该索引是如何组织数据存储的。

B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀查找。其对如下类型的查询有效。

  • 全值匹配
  • 匹配最左前缀
  • 匹配列前缀
  • 匹配范围值
  • 精确匹配某一列并范围匹配另一列
  • 只访问索引的查询。B-Tree通常可以支持“只访问索引的查询”,即查询只需要访问索引,而不需要访问数据行(覆盖索引)

因为索引树中的节点是有序的,所以除了按值查找之外,索引还可以用于查询中的ORDER BY操作(按顺序查找)。

当然B-Tree索引也有一些限制:

  • 如果不是按照索引的最左列开始查找,则无法使用索引
  • 不能跳过索引中的列
  • 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找。例如:where last_name=’smith’ and first_name like’J%’ and dob=’1976’。由于LIKE是一个范围查询,则dob无法使用索引。

(2) 哈希索引

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

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

下面看一个例子。

表中包含如下数据:

假设索引使用假象的哈希函数f(),它返回下面的值:


则哈希索引的数据结构如下:

因为索引自身只需要存储对应的哈希值,所以索引的结构十分紧凑,这也让哈希索引查找的速度非常快。然而,哈希索引也有它的限制:

  • 哈希索引值包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行
  • 哈希索引数据并不是按照索引值顺序存储的,所以也无法用于排序
  • 哈希索引不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值
  • 哈希索引只支持等值比较查询
  • 当出现哈希冲突的时候,存储引擎必须遍历链表中的所有的行指针,逐行进行比较,直到找到所有符合条件的行
  • 如果哈希冲突很多的话,一些索引维护操作的代价也会很高

(3) 空间数据索引

MyISAM表支持空间索引,可以用作地理数据存储。空间索引会从所有维度来索引数据。查询时,可以有效地使用任意维度来组合查询。

(4) 全文索引

全文索引是一种特殊类型的索引,它查找的是文本中的关键词,而不是直接比较索引中的值。全文索引更类似于搜索引擎做的事情,而不是简单的WHERE条件匹配。

2. 索引的优点

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

总结下来,索引有如下三个优点:

  • 索引大大减少了服务器需要扫描的数据量
  • 索引可以帮助服务器避免排序和临时表
  • 索引可以将所及I/O变为顺序I/O
如何评价一个索引是否适合某个查询(三星系统):

判断标准:索引将相关的记录放到一起则获得一星;如果索引中的数据顺序和查找中的排列顺序一致则获得二星;如果索引中的列包含了查询中需要的全部列则获得三星。

对于非常小的表,大部分情况下简单的全表扫描更高效。对于中型到大型的表,索引就非常有效。但对于特大型的表,建立和使用索引的代价将随之增长。这种情况下需要一种技术可以直接区分出查询需要的一组数据,而不是一条记录一条记录地查询。例如可以使用分区技术。

如果表的数量特别多,可以建立一个元数据信息表(块级别元数据技术),用来查询需要用到的某些特性。例如执行哪些需要聚合多个应用分布在多个表的数据的查询,则需要记录“哪个用户的信息存储在哪个表中”的元数据,这样在查询时就可以直接忽略哪些不包含指定用户信息的表。对于大型系统,这是一个常用的技巧。

3. 高性能的索引策略

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

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

(1) 独立的列

如果查询中的列不是独立的,则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的 一部分,也不能是函数的参数。

例如,下面这个查询就无法使用actor_id列的索引:

(2) 前缀索引和索引选择性

有时候需要索引很长的字符列,这会让索引变得大且慢。一个策略是使用模拟哈希索引。但是这样还不够,还可以做些什么呢?

通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。但这样也会降低索引的选择性。

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

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

这里的诀窍在于要选择足够长的前缀以保证较高的选择性,同时又不能太长(节约空间)。

举例,如下表city_demo,字段是VARCHAR(50),我们需要对其设置前缀索引:

完整列的选择性如下:

我们通过计算不同前缀长度的选择性,选择尽量接近0.0312的前缀长度,基本就可用了。

查询显示当前缀长度达到7的时候,再增加前缀长度,选择性提升的幅度已经很小。

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

前缀索引是一种能使索引更小、更快的有效办法,但是它的缺点也很明显:无法做ORDER BY和GROUP BY,也无法使用前缀索引做覆盖扫描。

一个比较常见的场景是针对很长的十六进制唯一ID使用前缀索引。

(3) 多列索引

多列索引使用的一个常见错误就是,为每个列创建独立的索引,这个做法是非常错误的。这种索引策略,最好的情况下也只能是“一星”索引,其性能比起真正最优的索引可能差几个数量级。有时如果无法设计一个“三星”索引,那么不如忽略掉WHERE字句,集中精力优化索引列的顺序,或者创建一个全覆盖索引。

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

在MySQL5.0和更新的版本中,引入了一种叫“索引合并”的策略,一定程度上可以使用表上的多个单列索引来定位指定的行。

如下,actor_id和film_id是file_actor表上两个独立的索引。在MySQL5.0和新版本中,查询能够同时使用这两个单列索引进行扫描,并将结果进行合并。

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

  • 当出现服务器对多个索引做相交操作是(通常有多个AND条件),通常意味着需要一个包含所有相关列的多列索引,而不是多个独立的单列索引。
  • 当服务器需要对多个索引做联合操作是(通常有多个OR条件),通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高,需要合并扫描返回的大量数据的时候。

如果在EXPLAIN中看到有索引合并,应该好好检查一下查询和表的结构,看是不是已经是最优的。

(4) 选择合适的索引顺序

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

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

当不需要考虑排序和分组时,将选择性最高的列放在前面通常是很好的。

以下面的查询为例:


这里应该是创建(staff_id,customer_id)索引还是应该颠倒一下顺序?可以通过跑一些查询来确定这个表中的值的分布情况,并确定哪个列的选择性高。

看看各个WHERE条件的分支对应的数据基数有多大:

根据前面的经验法则,应该将索引customer_id放到前面,因为对应条件值的customer_id数量更小。

这个方法有一个需要注意的地方,查询的结果非常依赖于选定的具体值,可能对其他一些条件的查询不公平。

(5) 聚簇索引

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

当表有聚簇索引时,它的数据行实际上存放在索引的叶子页中。“聚簇“表示数据行和相邻的键值紧凑地存储在一起。

下图展示了聚簇索引中的记录是如何存放的。注意到,叶子页包含了行的全部数据但是节点只包含了索引列。

聚簇索引的数据存储方式

InnoDB将通过主键聚集数据,这也就是说上图中的”被索引的列“就是主键列。

聚集的数据有一些重要的优点:

  • 可以把相关数据保存在一起。例如实现电子邮箱时,可以根据用户ID来聚集数据,这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引,则每封邮件都可能导致一次磁盘I/O。
  • 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中,因此从聚簇索引中获取数据通常比在非聚簇索引中查找更快。
  • 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。

如果在设计表和查询时能充分利用上面的优点,那就能极大地提升性能。同时聚簇索引也有一些缺点:

  • 聚簇数据最大限度地提高了I/O密集型应用的性能,但如果数据全部都放在内存中,则访问的顺序就没那么重要了,聚簇索引也就没什么优势。
  • 插入速度严重依赖于插入顺序。按照主键的顺序插入是数据加载到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据,那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
  • 更新聚簇索引列的代价很高,因为会强制InnoDB将每个被更新的行移动到新的位置。
  • 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动的时候,可能面临”页分裂“的问题。
  • 聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候。
  • 二级索引(非聚簇索引)可能比想象的要更大,因为在二级索引的叶子节点包含了引用行的主键列。
  • 二级索引访问需要两次索引查找,而不是一次。

这意味着通过二级索引查找行,存储引擎需要找到二级索引的叶子节点获得对应的主键值,然后根据这个值去聚簇索引中查到对应的行。这里做了重复的工作:两次B-Tree查找而不是一次。

InnoDB的二级索引和聚簇索引很不同。InnoDB二级索引的叶子节点中存储的不是”行指针“,而是主键值,并一次作为指向行的”指针“。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值做指针会让二级索引占用更多的空间,换来的好处是,InnoDB在移动行时无需更新二级索引中的这个”指针“。

在InnoDB表中按主键顺序插入行

如果正在使用InnoDB表并且没有什么数据需要聚集,那么可以定义一个代理作为主键,这种主键的数据应该和应用无关,最简单的方式是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入,对于根据主键做关联操作的性能也会更好。

最好避免随机的(不连续且值分布范围非常大)聚簇索引,特别是针对I/O密集型的应用。

例如,从性能角度考虑,使用UUID来作为聚簇索引则会很糟糕:它使得聚簇索引的插入变得完全随机,这是最坏的情况,使得数据没有任何聚集特性。由于主键字段更长,以及页分裂和碎片,从而导致UUID主键插入行不仅花费的时间更长,而且索引占用的空间更大。

因为新行的主键值不一定比之前插入的大,所以InnoDB无法简单地总是把新行插入到索引的最后,而是需要为新的行寻找合适的位置—–通常是已有数据的中间位置—-并且分配空间。这回增加很多额外的工作,并导致数据分布不够优化。下面是总结的一些缺点:

  • 写入的目标页可能已经刷到磁盘上并从缓存中移除,或者是还没有被加载到缓存中,InnoDB在插入之前不得不先找到从磁盘读取目标页到内存中。这将导致大量的随机I/O。
  • 因为写入是乱序的,InnoDB不得不频繁地做页分裂操作,以便新的行分配空间。页分裂会导致移动大量数据,一次插入最少需要修改三个页而不是一个页。
  • 由于频繁的页分裂,页会变得稀疏并被不规则地填充,所以最终数据会有碎片。

把这些随机值载入到聚簇索引以后,也许需要做一次OPTIMIZE TABLE来重建表并优化页的填充。

从这个案例可以看出,使用InnoDB时应该尽可能地按逐渐顺序插入数据并且尽可能地使用单调增加的聚簇键的值来插入新行。

那么顺序的主键什么时候会造成更坏的结果?

对于高并发工作负载,在InnoDB中按逐渐顺序插入可能会造成明显的争用。

(6) 覆盖索引

索引确实是一种查找数据的高效方式,但是MySQL也可以使用索引来直接获取列的数据,这样就不再需要读取数据行。如果一个索引包含所有需要查询的字段的值,我们就称之为”覆盖索引“。

覆盖索引是非常有用的工具,能极大地提高性能。考虑一下如果查询只需要扫描索引而无须回表,会带来多少好处:

  • 索引条目通常远小于数据行大小,如果只需要读取索引,那MySQL就会极大地减少数据访问量。
  • 因为索引是按照列值顺序存储的(至少在单个页内是如此),所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。
  • 一些存储引擎如MyISAM在内存中只缓存索引,数据则依赖于操作系统来缓存,因此要访问数据需要一次系统调用。这会导致严重的性能问题,尤其是那些系统调用占用了数据访问中的最大开销场景。
  • 由于InnoDB的聚簇索引,覆盖索引对InnoDB的二级索引在叶子节点中保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询。

在所有这些场景中,在索引中满足查询的成本一般比查询行要小得多。

覆盖索引必须要存储索引列的值,只能使用B-Tree索引做覆盖索引。

当发起一个被覆盖索引的查询时,在EXPLAIN的Extra列可以看到”Using index”的信息。如下,表sakila.inventory有一个多列索引(story_id,film_id)。MySQL如果只需访问这两列,就可以使用这个索引做覆盖索引:

延迟关联

下图的查询索引无法覆盖,有两个原因:

  • 没有任何索引能够覆盖这个查询,因为查询从表中选择了所有的列,而没有任何索引覆盖了所有的列。
  • MySQL不能在索引中执行LIKE操作。

上面的问题,需要重写查询并巧妙设计索引。先将索引扩展至覆盖三个数据列(artist,title,prod_id),然后如下方式重写查询:

这种方式叫做延迟关联,因为延迟了对列的访问。在查询的第一阶段MySQL可以使用覆盖索引,在FROM子句的子查询中找到匹配的prod_id,然后根据这些prod_id值在外层查询匹配获取需要的所有列值。虽然无法使用索引覆盖整个查询,但总算比完全无法利用索引覆盖的好。

这样的优化效果取决于WHERE条件匹配返回的行数。

(7) 使用索引扫描来做排序

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

扫描索引本身是很快的,因为只需要从一条索引记录移动到紧接着的小一条记录。但如果索引不能覆盖查询所需的全部列,那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O,因此按索引顺序读取数据的速度通常比顺序地全表扫描的慢,尤其是在I/O密集型的工作负载时。

只有当索引的列顺序和ORDER BY字句的顺序完全一致,并且所有列的排序方向都一样时,MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表,则只有当ORDER BY子句引用的字段全部为第一个表时,才能使用索引做排序。ORDER BY子句和查找型的限制是一样的:需要满足索引的最左前缀的要求;否则,MySQL都需要执行排序操作,而无法利用索引排序。

如下所示,rental在列(rental_date,inventory_id,customer_id)上有名为rental_date的索引。


MySQL可以使用rental_date索引为下面的查询做排序,从EXPLAIN中可以看到没有出现文件排序(filesort)操作:

(8) 冗余和重复索引

MySQL允许在相同列上创建多个索引,无论是有意的还是无意的。MySQL需要单独维护重复的索引,并且优化器在优化查询的时候也需要逐个地进行考虑,这会影响性能。

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引,发现以后也应该立即移除。如下图所示:

冗余索引和重复索引有一些不同。如果创建了索引(A,B),再创建索引(A)就是冗余索引,因为这这是前一个索引的前缀索引。

一般来说,表中的索引越多插入速度会越慢。增加新索引将会导致INSERNT、UPDATE、DELETE等操作的速度变慢,特别是新增索引以后导致达到了内存瓶颈的时候。

对于冗余索引和重复索引,可以通过Toolkit中的pt-duplicate-key-checker工具分析表结构来找出冗余和重复的索引。对于大型服务器来说,使用外部的工具可能更合适些;如果服务器上有大量的数据或者大量的表,查询INFORMATION_SCHEMA表可能会导致性能问题。

(9) 未使用的索引

除了冗余索引和重复索引,可能还会有一些服务器永远用不上的索引。这些索引完全是累赘,可以考虑删除。有两个工具可以帮助定位未使用的索引。最简单的有效的方法是在Percona Server或者在MariaDB中先打开userstates服务器变量,然后让服务器正常运行一段时间,再通过查询INFORMATION_SCHEMA.INDEX_STATICS就能查询到每个索引的使用频率。

另外也可以使用Percona Toolkit中的pt-index-usage,该工具可以读取查询日志,并对日志中的每条查询进行EXPLAIN操作,然后打印出关于索引和查询的报告。

(10) 索引和锁

索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行,那么就会锁定更少的行,从两个方面来看这对性能都有好处。首先,虽然InnoDB的行锁效率很高,内存使用也很少,但是锁定行的时候仍然会带来额外开销;其次,锁定超过需要的行会增加锁征用并减少并发性。

InnoDB只有在访问行的时候才会对其加锁,而索引能够减少InnoDB访问的行数,从而减少锁的数量。

4 维护索引和表

即使正确的类型创建了表并加上了合适的索引,工作也没有结束:还需要维护表和索引来确保它们能正常工作。维护表主要有三个目的:找到并修复损坏的表、怀虎准确的索引统计信息、减少碎片。

(1) 更新索引统计信息

如果存储引擎向优化器提供的扫描行数信息是不准确的数据,或者执行计划本身太复杂以致无法准确地获取各个阶段匹配的行数,那么优化其会使用索引统计信息来估算扫描行数。MySQL优化器使用的是基于成本的模型,而衡量成本的主要指标就是一个查询需要扫描多少行。如果表没有统计信息,或者统计信息不准确,优化器很有可能做出错误的决定。可以通过运行ANALYZE TABLE来重新统计信息解决这个问题。

InnoDB在打开某些INFORMATION_SCHEMA表,或者使用SHOW TABLES STATUS和SHOW INDEX,亦或在MySQL客户端开启自动补全功能的时候都会促发索引统计信息的更新。如果服务器上有大量的数据,这可能就是个很严重的问题,尤其是当I/O比较慢的时候。索引信息更新时会导致大量的所,并给服务器带来额外的压力。

(2) 减少索引和数据的碎片

B-Tree索引可能会碎片化,这会降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。

根据设计,B-Tree需要随机磁盘访问才能定位到叶子节点,所以随机访问是不可避免的。然而,如果叶子页在物理分布上是顺序且紧密的,那么查询的性能就会更好。否则,对于范围查询、索引覆盖扫描等操作来说,速度可能会降低很多倍;对于覆盖扫描这一点更加明显。

表的数据存储也可能碎片化。其主要有三种类型:

  • 行碎片

这种碎片指的是数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一行记录,行碎片也会导致性能下降。

  • 行间碎片

行间碎片是指逻辑上顺序的页,或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大影响,因为这些操作原本就能够从磁盘上是顺序存储的数据中获益。

  • 剩余空间碎片

剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据,从而造成浪费。

三、总结

在选择索引和编写利用这些索引的查询时,有如下三个原则使用需要记住:

  • 单行访问是很慢的。使用索引可以创建位置引用以提升效率。
  • 按顺序访问数据是很快的,这有两个原因。第一,顺序I/O不需要多次磁盘寻到,所以比随机I/O要快得多(特别是对机械硬盘)。第二,如果服务器能够按需顺序读取数据,那么就不再需要额外的排序操作,并且GROUP BY查询也无需再做排序和将行按组进行聚合计算了。
  • 索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列,那么存储引擎就不需要在会表查找行。这避免了大量的当行访问。

如果想找到那些索引不是很合适的查询,并在它们成为问题前进行优化,则可以使用pt-query-digest的查询审查review功能分析其EXPLAIN出来的执行计划。

转载请备注来源: 《MySQL性能优化学习笔记-(2)索引优化》 | shuwoom.com

打赏

发表评论

电子邮件地址不会被公开。