Never too late to learn.

0%

高性能MySQL-读书笔记2

高性能MySQL第三版

[美] Baron Scbwartz, Peter Zaitsev, Vadim Tkacbenko

第5章 创建高性能的索引

索引(MySQL中也叫‘键Key’)是存储引擎用于快速找到记录的一种数据结构。

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

5.1 索引基础

5.1.1 索引的类型

在MySQL中,索引是存储在引擎层而不是服务器层实现的。不同存储引擎的索引的工作方式并不一样,也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引,其底层实现也可能不同。

  • B-Tree索引

很多存储引擎使用的是B+Tree,即每一个叶子节点都包含指向下一个叶子节点的指针,从而方便叶子节点的范围遍历。

InnoDB使用B+Tree

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

B-Tree通常意味着所有的值都是按顺序存储的,并且每个叶子页到根的距离相同。

mysql-btree.jpg

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

叶子节点比较特别,他们的指针指向的是被索引的数据,而不是其它的节点页。树的深度和表的大小直接相关。

B-Tree对索引列是顺序组织存储的,所以很适合查找范围数据。B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

B-Tree索引的限制:

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

因此,索引的顺序很重要,这些限制都和索引的顺序有关。

  • 哈希索引(hash index)

哈希索引基于哈希表实现,只有精确匹配索引所有列的查询才有效。

在MySQL中,只有Memory引擎显式支持哈希索引,且支持非唯一哈希索引。如果多个列的哈希值相同,索引会以链表的方式存放多个记录指针到同一个哈希条目中。

哈希索引的限制:

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

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引(adaptive hash index)”。当InnoDB注意到某些索引值被使用得非常频繁时,他会在内存中基于B-Tree索引之上再创建一个哈希索引,这样就让B-Tree索引也具有哈希索引的一些优点,比如快速的哈希查找。

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

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

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

5.2 索引的优点

索引可以让服务器快速地定位到表的指定位置,根聚索引的数据结构不同,索引页有一些其它的附加作用。例如B-Tree索引,按照顺序存储数据,所以MySQL可以用来做order by和group by操作。因为数据时有序的,所以B-Tree也就会将相关的列值都存储在一起。最后因为索引中存储了实际的列值,所以某些查询只使用索引就能够完成全部查询。

索引的三个优点:

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

索引推荐的书籍,Relational Database Index Design and the Optimizers(Wiley出版社), 由Tapio Lahdenmaki和Mike Leach编写。

1
2
3
4
如何评价一个索引是否适合某个查询的“三星系统”(three-star system)
* 索引将相关的记录放到一起则获得一星;
* 如果索引中的数据顺序和查找中的排列顺序一致则获得二星;
* 如果索引中的列包含了查询中需要的全部列则获得“三星”;

5.3 高性能的索引策略

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

5.3.1 独立的列

“独立的列”是指索引列不能是表达式的一部分,也不能是函数的参数。

5.3.2 前缀索引和索引选择性

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

对于BLOB、TEXT或者很长的VARCHAR类型的列,必须使用前缀索引,因为MySQL不允许所以额这些列的完整长度。诀窍在于要选择足够长的前缀以保证较高的选择性,同时又不能太长。即:前缀的“基数”应该接近于完整列的“基数”。

前缀索引是一种能使索引更小、更快的有效办法,单另一方面也有其缺点:MySQL无法使用前缀索引做order by和group by,也无法使用前缀索引做覆盖扫描。

5.3.3 多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫“索引合并”(index merge)的策略,一定程度上可以使用表上的多个单列索引来定位指定的行。

索引合并策略有时候是一种优化的结果,但实际上更多时候说明了表上的索引建的很糟糕。如果在explain中看到有索引合并,应该好好检查一下查询和表的结构,看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能。也可以使用IGNORE INDEX提示让优化器忽略掉某些索引。

5.3.4 选择合适的索引列顺序

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

在一个多列B-Tree索引中,索引列的顺序意味着索引首先按照最左列进行排序,其次是第二列,等等。

对于如何选择索引的列顺序有一个经验法则:将选择性最高的列放到索引最前列。这个建议在某些场景可能有帮助,但通常不如避免随机IO和排序那么重要。

当不需要考虑排序和分组时,将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化where条件的查询。然而,性能不只是依赖于所有索引列的选择性(整体基数),也和查询条件的具体值有关,也就是和值得分布有关。

5.3.5 聚簇索引

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式

InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

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

mysql-index.jpg

InnoDB通过主键聚集数据,如果没有定义主键,InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键来作为聚簇索引。

聚簇索引有一些重要的优点:

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

缺点:

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

5.3.6 覆盖索引

如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们成之为“覆盖索引”。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值,而哈希索引、空间索引和全文索引等都不存储索引列的值,所以MySQL只能使用B-Tree索引做覆盖索引。

5.3.7 使用索引扫描来做排序

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

5.3.8 压缩(前缀压缩)索引

MyISAM使用前缀压缩来减少索引的大小,从而让更多的索引可以放入内存中,这在某些情况下能极大地提高性能。

5.3.9 冗余和重复索引

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引,发现以后也应该立即一处。

冗余索引和重复索引有一些不同。如果创建了索引(A, B),再创建索引(A)就是冗余索引,因为这只是前一个索引的前缀索引。冗余索引通常发生在为表添加新索引的时候。

5.3.10 未使用的索引

5.3.11 索引和锁

索引可以让查询锁定更少的行,这对性能有好处。首先,虽然InnoDB的行锁效率很高,内存使用也很少,但是锁定行的时候仍然会带来额外开销;其次,锁定超过需要的行会增加锁争用并减少并发性。

5.5 维护索引和表

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

5.5.1 找到并修复损坏的表(corruption)

5.5.2 更新索引统计信息

5.5.3 减少索引和数据的碎片

5.6 总结

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

    1. 单行访问是很慢的。
    1. 按顺序访问范围数据是很快的。
    1. 索引覆盖查询时很快的。

总的来说,编写查询语句时应该尽可能选择合适的索引以避免单行查询、尽可能地使用数据原生顺序从而避免额外的排序操作,并尽可能使用索引覆盖查询。

Coffee? ☕