第五章 创建高性能的索引 高性能MySql阅读总结

        索引（在MySQL中也叫做 “键(key)")是存储引擎用于快速找到记录的一种数据结构。这是索引的基本功能。

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

        索引优化应该是对查询性能优化最有效的手段了。 索引能够轻易将查询性能提高几个数量级， “最优” 的索引有时比一个 “好的“ 索引性能要好两个数量级。 创建一个真正 “最优 的索引经常需要重写查询， 所以， 本章和下一章的关系非常紧密。

5.1索引基础

        要理解MySQL中索引是如何工作的，最简单的方法就是去看看一本书的 “索引” 部分：如果想在一本书中找到某个特定主题，一般会先看书的 “索引”，找到对应的页码。

        在MySQL中， 存储引擎用类似的方法使用索引， 其先在索引中找到对应值， 然后根据 匹配的索引记录找到对应的数据行。 假如要运行下面的查询：

mysql> SELECT first_name FRom sakila.actor WHERE actor_id = 5;

        如果在actor_id列上建有索引， 则MySQL将使用该索引找到actor_id为5的行， 也就是说， MySQL先在索引上按值进行查找， 然后返回所有包含该值的数据行。索引可以包含一个或多个列的值。 如果索引包含多个列， 那么列的顺序也十分重要， 因为MySQL只能高效地使用索引的最左前缀列。 创建一个包含两个列的索引， 和创建两 个只包含一列的索引是大不相同的， 下面将详细介绍。

5.1.1索引的类型

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

        下面我们先来看看MySQL支持的索引类型， 以及它们的优点和缺点。

B-Tree索引

        当人们谈论索引的时候， 如果没有特别指明类型， 那多半说的是B-Tree索引， 它使用 B-Tree数据结构来存储数据。大多数MySQL引擎都支持这种索引。 Archive引擎是一个例外：5.1之前Archive不支持任何索引， 直到5.1才开始支持单个自增列(AUTO INCREMENT)的索引。

        不过， 底层的存储引擎也可能使用不同的存储结构， 例如， NDB集群存储引擎内部实际上使用了T-Tree结构存储这种索引， 即使其名字是BTREE I lnnoDB则使用的是B+Tree,各种数据结构和算法的变种不在本书的讨论范围之内。

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

        B-Tree通常意味着所有的值都是按顺序存储的， 并且每一个叶子页到根的距离相同。图5-1展示了B-Tree索引的抽象表示， 大致反映了InnoDB索引是如何工作的。MyISAM使用的结构有所不同， 但基本思想是类似的。

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

        叶子节点比较特别， 它们的指针指向的是被索引的数据， 而不是其他的节点页（不同引擎的 ”指针” 类型不同）。 图5-1中仅绘制了一个节点和其对应的叶子节点， 其实在根节点和叶子节点之间可能有很多层节点页。 树的深度和表的大小直接相关。

        B-Tree 对索引列是顺序组织存储的， 所以很适合查找范围数据。 例如， 在一个基于文本域的索引树上， 按字母顺序传递连续的值进行查找是非常合适的， 所以像 “找出所有以 I 到 K 开头的名字“ 这样的查找效率会非常高。

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

全值匹配

全值匹配指的是和索引中的所有列进行匹配， 例如前面提到的索引可用于查找姓名为CubaAllen、出生千1960-01-01 的人。

匹配最左前缀

前面提到的索引可用于查找所有姓为Allen的人， 即只使用索引的第一列。

匹配列前缀

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

匹配范围值

例如前面提到的索引可用于查找姓在Allen和Barrymore之间的人。这里也只使用了索引的第一列。

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

前面提到的索引也可用于查找所有姓为Allen, 井且名字是字母K开头（比如Kim、Karl等） 的人。即第一列last_name全匹配，第二列first_name范围匹配。

只访问索引的查询

B-Tree通常可以支持“只访问索引的查询", 即查询只需要访问索引， 而无须访问数据行。后面我们将单独讨论这种“覆盖索引” 的优化。

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

下面是一些关于B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找， 则无法使用索引。例如上面例子中的索引无法用于查找名字为B ill的人， 也无法查找某个特定生日的人， 因为这两列都不是最左数据列。类似地， 也无法查找姓氏以某个字母结尾的人。

• 不能跳过索引中的列。 也就是说， 前面所述的索引无法用千查找姓为 Smith 并且在某个特定日期出生的人。 如果不指定名 (first_name), 则 MySQL 只能使用索引的 第一列。

• 如果查询中有某个列的范围查询， 则其右边所有列都无法使用索引优化查找。 例如 有查询 WHERE last_name='Smith' AND first_name LIKE 'J%' AND dob = '1976- 12-23 I, 这个查询只能使用索引的前两列， 因为这里LIKE是一个范围条件（但是服务器可以把其余列用千其他目的）。 如果范围查询列值的数最有限， 那么可以通过使用多个等千条件来代替范围条件。 在本章的索引案例学习部分， 我们将演示一个详细的案例。

        到这里读者应该可以明白， 前面提到的索引列的顺序是多么的重要：这些限制都和索引 列的顺序有关。 在优化性能的时候， 可能需要使用相同的列但顺序不同的索引来满足不 同类型的查询需求。

        也有些限制井不是 B-Tree 本身导致的， 而是 MySQL 优化器和存储引擎使用索引的方式导致的， 这部分限制在未来的版本中可能就不再是限制了。

哈希索引

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

        在MySQL中，只有Memory引擎显式支持哈希索引。这也是Memory引擎表的默认索引类型，Memory引擎同时也支持B-Tree索引。值得一提的是，Memory引擎是支持非唯一哈希索引的， 这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同，

索引会以链表的方式存放多个记录指针到同一个哈希条目中。

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

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。 不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的 影响并不明显。

• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用千排序。

• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内 容来计算哈希值的。 例如，在数据列(A,B)上建立哈希索引，如果查询只有数据列A, 则无法使用该索引。

• 哈希索引只支持等值比较查询，包括＝、 IN() 、<:=>(注意<>和＜＝＞是不同的操作）。 也不支持任何范围查询，例如WHERE price> 100。

• 访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈 希值）。 当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行 比较，直到找到所有符合条件的行。

• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。 例如，如果在某个选择性很低（哈希冲突很多） 的列上建立哈希索引，那么当从表中删除一行时， 存储 引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多， 代价越大。

因为这些限制，哈希索引只适用于某些特定的场合。 而一且适合哈希索引，则它带来的 性能提升将非常显著。 举个例子，在数据仓库应用中有一种经典的 “星型" schema, 需要关联很多查找表，哈希索引就非常适合查找表的需求。

除了Memory引擎外，NOB集群引擎也支持唯一哈希索引，且在NDB集群引擎中作用 非常特殊，但这不属千本书的范围。

InnoDB引擎有一个特殊的功能叫做 “自适应哈希索引(adaptivehash index)"。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基千B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希 查找。 这是一个完全自动的、 内部的行为，用户无法控制或者配置，不过如果有必要， 完全可以关闭该功能。

创建自定义哈希索引。 如果存储引擎不支持哈希索引，则可以模拟像InnoDB一样创建哈希索引，这可以享受一些哈希索引的便利，例如只需要很小的索引就可以为超长的键创建索引。

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

5.2索引的优点

        索引可以让服务器快速地定位到表的指定位置。 但是这并不是索引的唯一作用，到目前为止可以看到，根据创建索引的数据结构不同，索引也有一些其他的附加作用。

        最常见的B-Tree索引，按照顺序存储数据，所以MySQL可以用来做ORDER BY和GROUP BY操作。 因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起。 最后， 因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。 据此特性，总结下来索引有如下三个优点：

1. 索引大大减少了服务器需要扫描的数据量。

2. 索引可以帮助服务器避免排序和临时表。

3. 索引可以将随机I/O变为顺序I/O。

5.3高性能的索引策略

        正确地创建和使用索引是实现高性能查询的基础。 前面已经介绍了各种类型的索引及其 对应的优缺点。 现在我们一起来看看如何真正地发挥这些索引的优势。

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

5.3.1独立的列

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

例如， 下面这个查询无法使用actor_id列的索引：

mysql> SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5

5.3.2前缀索引和索引选择性

        有时候需要索引很长的字符列， 这会让索引变得大且慢。 一个策略是前面提到过的模拟哈希索引。 但有时候这样做还不够， 还可以做些什么呢？

通常可以索引开始的部分字符，这样可以大大节约索引空间， 从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值（也称为基数，cardinality)和数据表的记录总数(#T)的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1, 这是最好的索引选择性，性能也是最好的。

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

        诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数” 应该接近于完整列的“基数”。

mysql> ALTER TABLE sakila.city_demo ADD KEY (city(7));

        前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点： MySQL无法使用前缀索引做ORDER BY和GROUP BY, 也无法使用前缀索引做覆盖扫描。

5.3.3多列索引

        很多人对多列索引的理解都不够。 一个常见的错误就是，为每个列创建独立的索引，或 者按照错误的顺序创建多列索引。

        在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫 “索引合并" (index merge)的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的 MySQL只能使用其中某一个单列索引，然而这种情况下没有哪一个独立的单列索引是非常有效的。例如，表 film_actor在字段 film_id 和 actor_id 上各有一个单列索引。但对于下面这个查询WHERE条件，这两个单列索引都不是好的选择：

5.3.4选择合适的索引列顺序

        我们遇到的最容易引起困惑的问题就是索引列的顺序。 正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要（顺便说明，本节内容适用于B-Tree索引；哈希或者其他类型的索引并不会像B-Tree索引一样按顺序存储数据）。

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

        所以多列索引的列顺序至关重要。在Lahdenmaki和Leach的“ 三星索引” 系统中，列顺序也决定了一个索引是否能够成为一个真正的“ 三星索引”（关千三星索引可以参考本章前面的5.2节）。在本章的后续部分我们将通过大量的例子来说明这一点。对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。这个建议有用吗？在某些场景可能有帮助，但通常不如避免随机IO和排序那么重要， 考虑问题需要更全面（场景不同则选择不同，没有一个放之四海皆准的法则。这里只是说明，这个经验法则可能没有你想象的重要）。

        当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在WHE RE子旬中只使用了索引部分前缀列的查询来说选择性也更高。然而，性能不只是依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关。这和前面介绍的选择前缀的长度需要考虑的地方一样。可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

5.3.5聚簇索引

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

        当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中。 术语 “聚簇” 表示数据行和相邻的键值紧凑地存储在一起注 8。因为无法同时把数据行存放在两个不同 的地方，所以一个表只能有一个聚簇索引（不过，覆盖索引可以模拟多个聚簇索引的情况， 本章后面将详细介绍）。

        因为是存储引擎负责实现索引， 因此不是所有的存储引擎都支持聚簇索引。 本节我们主要关注InnoDB, 但是这里讨论的原理对于任何支持聚簇索引的存储引擎都是适用的。

        图5-3展示了聚簇索引中的记录是如何存放的。 注意到， 叶子页包含了行的全部数据，但是节点页只包含了索引列。 在这个案例中， 索引列包含的是整数值。

        一些数据库服务器允许选择哪个索引作为聚簇索引， 但直到本书写作之际， 还没有任何一个MySQL内建的存储引擎支持这一点。InnoDB将通过主键聚集数据， 这也就是说图5-3中的 ”被索引的列” 就是主键列。

        如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。 如果没有这样的索引， lnnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远。

        聚簇主键可能对性能有帮助， 但也可能导致严重的性能问题。 所以需要仔细地考虑聚簇索引， 尤其是将表的存储引擎从InnoDB改成其他引擎的时候（反过来也一样）。

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

• 可以把相关数据保存在一起。 例如实现电子邮箱时， 可以根据用户ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。 如果没有使用聚簇索引， 则每封邮件都可能导致一次磁盘I/O。

• 数据访问更快。 聚簇索引将索引和数据保存在同一个B-Tree中， 因此从聚簇索引中 获取数据通常比在非聚簇索引中查找要快。

• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

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

• 聚簇数据最大限度地提高了1/0密集型应用的性能， 但如果数据全部都放在内存中，则访问的顺序就没那么重要了， 聚簇索引也就没什么优势了。

• 插入速度严重依赖于插入顺序。 按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。 但如果不是按照主键顺序加载数据， 那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。

• 更新聚簇索引列的代价很高， 因为会强制InnoDB将每个被更新的行移动到新的位置。

• 基于聚簇索引的表在插入新行， 或者主键被更新导致需要移动行的时候， 可能面临 ”页分裂(page split) n 的问题。 当行的主键值要求必须将这一行插入到某个已满的页中时， 存储引擎会将该页分裂成两个页面来容纳该行， 这就是一次页分裂操作。 页分裂会导致表占用更多的磁盘空间。

• 聚簇索引可能导致全表扫描变慢， 尤其是行比较稀疏， 或者由于页分裂导致数据存储不连续的时候。

• 二级索引（非聚簇索引） 可能比想象的要更大， 因为在二级索引的叶子节点包含了引用行的主键列。

•二级索引访问需要两次索引查找，而不是一次。

        最后一点可能让人有些疑惑，为什么二级索引需要两次索引查找？答案在于二级索引中保存的 “行指针” 的实质。 要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。

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

5.3.6覆盖索引

        通常大家都会根据查询的WHERE条件来创建合适的索引， 不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询， 而不单单是WHERE条件部分。索引确实是一种查找数据的高效方式， 但是MySQL也可以使用索引来直接获取列的数据， 这样就不再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据， 那么还有什么必要再回表查询呢？如果一个索引包含（或者说覆盖）所有需要查询的字段的值， 我们就称之为“覆盖索引” 。

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

• 索引条目通常远小于数据行大小， 所以如果只需要读取索引， 那MySQL就会极大地减少数据访问量。这对缓存的负载非常重要， 因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于1/0密集型的应用也有帮助， 因为索引比数据更小，更容易全部放入内存中（这对于MylSAM尤其正确， 因为MyISAM能压缩索引以变得更小）。

• 因为索引是按照列值顺序存储的（至少在单个页内是如此）， 所以对千1/0密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。对千某些存储引擎， 例如MyISAM和Percona XtraDB, 甚至可以通过OPTIMIZE命令使得索引完全顺序排列， 这让简单的范围查询能使用完全顺序的索引访问。

• 一些存储引擎如MyISAM在内存中只缓存索引， 数据则依赖千操作系统来缓存， 因此要访问数据需要一次系统调用。这可能会导致严重的性能问题， 尤其是那些系统调用占了数据访问中的最大开销的场景。

• 由于InnoDB的聚簇索引， 覆盖索引对lnnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值， 所以如果二级主键能够覆盖查询， 则可以避免对主键索引的二次查询。

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

        不是所有类型的索引都可以成为覆盖索引。 覆盖索引必须要存储索引列的值，而哈希索 引、 空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做 覆盖索引。 另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引（在写作本书时，Memory存储引擎就不支持覆盖索引）。

        索引覆盖查询还有很多陷阱可能会导致无法实现优化。 MySQL查询优化器会在执行查 询前判断是否有一个索引能进行覆盖。 假设索引覆盖了WHERE条件中的字段，但不是整 个查询涉及的字段。 如果条件为假(false), MySQL 5.5和更早的版本也总是会回表获取数据行，尽管并不需要这一行且最终会被过滤掉。

5.3.7使用索引扫描来做排序

        MySQL 有两种方式可以生成有序的结果：通过排序操作 1 或者按索引顺序扫描注 13 I 如果 EXPLAIN 出来的 type 列的值为 "index", 则说明 MySQL 使用了索引扫描来做排序（不 要和 Extra 列的 "Using index" 搞混淆了）。

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

        MySQL 可以使用同一个索引既满足排序， 又用千查找行。 因此， 如果可能， 设计索引 时应该尽可能地同时满足这两种任务， 这样是最好的。

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

        有一种情况下ORDER BY 子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果WHERE 子旬或者JOIN 子旬中对这些列指定了常量，就可以“弥补” 索引的不足。

5.3.9冗余和重复索引

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

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

        冗余索引和重复索引有一些不同。 如果创建了索引(A, B)， 再创建索引 (A) 就是冗余索引，因为这只是前一个索引的前缀索引。 因此索引(A, B) 也可以当作索引 (A) 来使用（这种冗余只是对B-Tree索引来说的）。 但是如果再创建索引,(B, A), 则不是冗余索引，索引 (B)也不是，因为 B不是索引 (A, B) 的最左前缀列。 另外，其他不同类型的索引（例如哈希索引或者全文索引） 也不会是B-Tree索引的冗余索引，而无论覆盖的索引列是什么。

        冗余索引通常发生在为表添加新索引的时候。 例如，有人可能会增加一个新的索引(A, B)而不是扩展已有的索引 (A)。还有一种情况是将一个索引扩展为(A, ID), 其中 ID 是主键，对于InnoDB来说主键列已经包含在二级索引中了，所以这也是冗余的。大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。 但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。

5.3.11索引和锁

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

        InnoDB只有在访问行的时候才会对其加锁， 而索引能够减少InnoDB访问的行数， 从而减少锁的数量。 但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。 如果索引无法过滤掉无效的行， 那么在InnoDB检索到数据井返回给服务器层以后， MySQL服务器才能应用WHERE子旬注 19。 这时已经无法避免锁定行了： InnoDB已经锁住了这些行，到适当的时候才释放。 在MySQL5.1和更新的版本中，lnnoDB可以在服务器端过滤掉行后就释放锁， 但是在早期的MySQL版本中，InnoDB只有在事务提交后才能释放锁。

        尽可能将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。

总结

        通过本章可以看到， 索引是一个非常复杂的话题! MySQL和存储引擎访问数据的方式，加上索引的特性， 使得索引成为一个影响数据访问的有力而灵活的工作（无论数据是在磁盘中还是在内存中）。

        在MySQL中， 大多数情况下都会使用B-Tree索引。其他类型的索引大多只适用千特殊的目的。如果在合适的场景中使用索引， 将大大提高查询的响应时间。本章将不再介绍更多这方面的内容了， 最后值得总的回顾一下这些特性以及如何使用B-Tree索引。在选择索引和编写利用这些索引的查询时， 有如下三个原则始终需要记住：

1. 单行访问是很慢的。特别是在机械硬盘存储中(SSD的随机1/0要快很多， 不过这一点仍然成立）。如果服务器从存储中读取一个数据块只是为了获取其中一行， 那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率。

2. 按顺序访问范围数据是很快的，这有两个原因。第一，顺序1/0不需要多次磁盘寻道，所以比随机1/0要快很多（特别是对机械硬盘）。第二， 如果服务器能够按需要顺序读取数据， 那么就不再需要额外的排序操作， 井且GROUP BY查询也无须再做排序和将行按组进行聚合计算了。

3. 索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列， 那么存储引擎就不需要再回表查找行。这避免了大最的单行访问， 而上面的第1点已经写明单行访问是很慢的。

        总的来说， 编写查询语句时应该尽可能选择合适的索引以避免单行查找、尽可能地使用数据原生顺序从而避免额外的排序操作， 井尽可能使用索引覆盖查询。这与本章开头提到的Lahdenmaki和Leach的书中的“三星” 评价系统是一致的。

        如果表上的每一个查询都能有一个完美的索引来满足当然是最好的。但不幸的是， 要这么做有时可能需要创建大量的索引。还有一些时候对某些查询是不可能创建一个达到“ 三屋” 的索引的（例如查询要按照两个列排序， 其中一个列正序， 另一个列倒序）。这时必须有所取舍以创建最合适的索引， 或者寻求替代策略（例如反范式化， 或者提前计算汇总表等）。

        理解索引是如何工作的非常重要， 应该根据这些理解来创建最合适的索引， 而不是根据一些诸如“在多列索引中将选择性最高的列放在第一列” 或“应该为WHERE 子句中出现的所有列创建索引” 之类的经验法则及其推论。

        那如何判断一个系统创建的索引是合理的呢？一般来说， 我们建议按响应时间来对查询进行分析。找出那些消耗最长时间的查询或者那些给服务器带来最大压力的查询（第3章中介绍了如何测量）， 然后检查这些查询的schema、SQL和索引结构， 判断是否有查询扫描了太多的行， 是否做了很多额外的排序或者使用了临时表， 是否使用随机1/0访问数据， 或者是有太多回表查询那些不在索引中的列的操作。

        如果一个查询无法从所有可能的索引中获益， 则应该看看是否可以创建一个更合适的索引来提升性能。如果不行， 也可以看看是否可以重写该查询， 将其转化成一个能够高效利用现有索引或者新创建索引的查询。这也是下一章要介绍的内容。

        如果根据第3章介绍的基千响应时间的分析不能找出有问题的查询呢？是否可能有我们没有注意到的“很糟糕” 的查询， 需要一个更好的索引来获取更高的性能？一般来说，不可能。对千诊断时抓不到的查询， 那就不是问题。但是， 这个查询未来有可能会成为问题，因为应用程序、数据和负载都在变化。如果仍然想找到那些索引不是很合适的查询，井在它们成为问题前进行优化， 则可以使用pt-query-digest的查询审查"review" 功能，分析其EXPLAIN出来的执行计划。

.