MySQL 索引学习笔记

索引基本概念

索引（在 MySQL 中也叫做“键（Key）”）是存储引擎用于快速找到记录的一种数据结构。

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

所以说索引优化应该是对查询性能优化最有效的手段了。

索引是如何工作的呢？

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

​ 在 MySQL 中，存储引擎用类似的方法使用索引，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。假如要运行下面的查询： mysql> SELECT first_name FROM sakila.actor WHERE actor_id=5;

​ 如果在 actor_id 列上建有索引，则 MySQL 将使用该索引找到 actor_id 为 5 的行， 也就是说，MySQL 先在索引上按值进行查找，然后返回所有包含该值的数据行。

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

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

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

MyISAM 使用前缀压缩技术使得索引更小，但 InnoDB 则按照原数据格式进行存储。

MyISAM 索引通过数据的物理位置引用被索引的行，而 InnoDB 则根据主键引用被索引的行。

索引优点

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

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

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

索引并不总是最好的工具。总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增长。这种情况下，则需要一种技术可以 直接区分出查询需要的一组数据，而不是一条记录一条记录地匹配。例如可以使用分区技术。

B-Tree 索引

基本原理

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8R87OKKK-1647682207393)(C:\Users\YangShuKai\AppData\Roaming\Typora\typora-user-images\image-20210305201304809.png)]

建立在 B-Tree 结构（从技术上来说是 B+Tree）上的索引

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

根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找**。通过比较节点页的值和要查找的值可以找到合适的指针进入下层节点**，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么找到对应的值，要么该记录不存在。

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

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

假如有如下数据表：

CREATE TABLE People (
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    dob DATE NOT NULL,
    gender ENUM('m','f') NOT NULL,
    KEY(last_name, first_name, dob)
);

索引中包含了 last_name、first_name 和 dob 列的值，索引对多个值进行排序的依据是 CREATE TABLE 语句中定义索引时列的顺序。

图：索引是如何组织数据的存储的

使用场景

重点注意——索引最左匹配原则

B-Tree 索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

• 全值匹配：和索引中的所有列进行匹配。例如上面提到的索引可用于查找姓名为 Cuba Allen、出生于 1960-01-01 的人。
• 匹配最左前缀：只使用索引的第一列。例如上面提到的索引可用于查找所有姓为 Allen 的人。
• 匹配列前缀：可以只匹配某一列的值的开头部分。例如前面提到的所有可用于查找所有以 J 开头的姓的人，这里也只使用了索引的第一列。
• 匹配范围值：只使用索引的第一列。例如前面提到的所有可用于查找姓在 Allen 和 Barrymore 之间的人。
• 精确匹配某一列并范围匹配另外一列：即索引的第一列全匹配，第二列范围匹配。前面提到的索引也可用于查找所有姓为 Allen，并且名字是字母 K 开头（比如 Kim、Karl 等）的人。
• 只访问索引的查询：B-Tree 通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。

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

使用限制

重点注意——索引最左匹配原则

• 如果不是按照索引的最左列开始查找，则无法使用索引。例如上面例子中的索引 无法用于查找名字为 Bill 的人，也无法查找某个特定生日的人，因为这两列都不是 最左数据列。类似地，也无法查找姓氏以某个字母结尾的人。
• 不能跳过索引中的列。也就是说，前面所述的索引无法用于查找姓为 Smith 并且在 某个特定日期出生的人。如果不指定名（first_name），则 MySQL 只能使用索引 的第一列。
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如有查询 WHERE last_name='Smith' AND first_name LIKE 'J％' AND dob='1976- 12-23'，这个查询只能使用索引的前两列因为这里 LIKE 是一个范围条件（但是服务器可以把其余列用于其他目的）。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。

哈希索引

基本原理

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

在 MySQL 中，只有 Memory 引擎显示支持哈希索引。这也是 Memory 引擎表的默认索引类型，Memory 引擎同时也支持 B-Tree 索引。值得一提的是，Memory 引擎是支持非唯一哈希索引的，这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

假设有如下表

CREATE TABLE testhash (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    KEY USING HASH(fname)
) ENGINE = MEMORY;

表中包含如下数据

假设索引使用假想的哈希函数 f()，它返回下面的值（都是示例数据，非真实数据）：

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

注意每个槽的编号是顺序的，但是数据行不是。现在，来看如下查询：

SELECT lname FROM testhash WHERE fname = 'Peter';

MySQL 先计算’Peter’的哈希值，并使用该值寻找对应的记录指针。因为 f（‘Peter’）=8784，所以 MySQL 在索引中查找 8784，可以找到指向第 3 行的指针，最后一步是比较第三行的值是否为’Peter’，以确保就是要查找的行。

使用限制

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

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列（A，B）上建立哈希索引，如果查询只有数据列 A，则无法使用该索引。
• 哈希索引只支持等值比较查询，包括 =、IN()、<=>、（注意 <> 和<=>是不同的操作）。也不支持任何范围查询，例如。WHERE price>100。
• 访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐级进行比较，直到找到所有符合条件的行。
• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时， 存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

因为这些限制，哈希索引只适用于某些特定的场合。而一旦适合哈希索引，则它带来的性能提升将非常显著。

自适应哈希索引

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

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

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

下面是一个实例，例如需要存储大量的 URL，并需要根据 URL 进行搜索查找。如果使用 B-Tree 来存储 URL，存储的内容就会很大，因为 URL 本身都很长。正常情况下会有如下查询：

SELECT id FROM url WHERE url = "http://www.mysql.com";

若删除原来 URL 列上的索引，而新增一个被索引的 url_crc 列，使用 CRC32 做哈希，就可以使用下面的方式查询：

SELECT id FROM url WHERE url = "http://www.mysql.com" AND url_crc=CRC32("http://www.mysql.com");

这样做的性能会非常高，因为 MySQL 优化器会使用这个选择性很高而体积很小的基于 url_crc 列的索引来完成查找（在上面的案例中，索引值为 1560514994）。即使有多个记录有相同的索引值，查找仍然很快，只需要根据哈希值做快速的整数比较就能找到索引条目，然后一 一比较返回对应的行。另外一种方式就是对完整的 URL 字符串做索引，那样会非常慢。

这样实现的缺陷是需要维护哈希值。可以手动维护，也可以使用触发器实现。下面的案例演示了触发器如何在插入和更新时维护 url_crc 列。首先创建如下表：

CREATE TABLE pseudohash (
	id INT UNSIGNED NOT NULL auto_increment,
    url VARCHAR(255) NOT NULL,
    url_crc INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY(id)
);

然后创建触发器。先临时修改一下语句分隔符，这样就可以在触发器定义中使用分号：

DELIMITER //
CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW
SET NEW.url_crc = crc32(NEW.url);
END;
//

CREATE TRIGGER pseudohash_crc_ins BEFORE UPDATE ON pseudohash FOR EACH ROW
SET NEW.url_crc = crc32(NEW.url);
END;
DELIMITER;

剩下的工作就是验证一下触发器如何维护哈希索引：

如果采用这种方式，记住不要使用 SHA1()和 MD5()作为哈希函数。因为这两个函数计算出来的哈希值是非常长的字符串，会浪费大量空间，比较时也会更慢。SHA1()和 MD5()是强加密函数，设计目标是最大限度消除冲突，但这里并不需要这样高的要求。 简单哈希函数的冲突在一个可以接受的范围，同时又能够提供更好的性能。

如果数据表非常大，CRC32()会出现大量的哈希冲突，则可以考虑自己实现一个简单的 64 位哈希函数。这个自定义函数要返回整数，而不是字符串。一个简单的办法可以使用 MD5()函数返回值的一部分来作为自定义哈希函数。这可能比自己写一个哈希算法的性能要差，不过这样实现最简单：

处理哈希冲突

当使用哈希索引进行查询的时候，必须在 WHERE 子句中包含常量值：

SELECT id FROM url WHERE url_crc = CRC32("http://www.mysql.com") AND url = "http://www.mysql.com";

一旦出现哈希冲突，另一个字符串的哈希值也恰好是 1560514994，则下面的查询是无法正确工作的。

SELECT id FROM url WHERE url = "http://www.mysql.com";

冲突让下面的查询返回了多条记录：

正确的写法应该如下：

要避免冲突问题，必须在 WHERE 条件中带入哈希值和对应列值。如果不是想查询具体值，例如只是统计记录数（不精确的），则可以不带入列值，直接使用 CRC32()的哈 希值查询即可。还可以使用如 FNV64()函数作为哈希函数，这是移植自 Percona Server 的 函数，可以以插件的方式在任何 MySQL 版本中使用，哈希值为 64 位，速度快，且冲突 比 CRC32()要少很多。

相关面试题

面试官：索引具体采用的哪种数据结构呢？

我：常见的 MySQL 主要有两种结构：Hash 索引和 B-Tree 索引，我们使用的是 InnoDB 引擎，默认的是 B + 树。
面试官：既然你提到 InnoDB 使用的 B+ Tree 的索引模型，那么你知道为什么采用 B+ 树吗？这和 Hash 索引比较起来有什么优缺点吗？

我：因为 Hash 索引底层是哈希表，哈希表是一种以 key-value 存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的。所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。 而B+ Tree 是一种多路平衡查询树，他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描。
面试官：除了上面这个范围查询的，你还能说出其他的一些区别吗？

我：B+ Tree 索引和 Hash 索引区别

哈希索引适合等值查询，但是不无法进行范围查询；哈希索引没办法利用索引完成排序；哈希索引不支持多列联合索引的最左匹配规则 ，如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。
面试官：刚刚我们聊到 B+ Tree ，那你知道 B+ Tree 的叶子节点都可以存哪些东西吗？

我：InnoDB 的 B+ Tree 可能存储的是整行数据，也有可能是主键的值。
面试官：那这两者有什么区别吗？

我：在 InnoDB 里，索引 B+Tree 的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引 B+ Tree 的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。
面试官：那么，聚簇索引和非聚簇索引，在查询数据的时候有区别吗？

我：聚簇索引查询会更快
面试官：为什么呢？

我：因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点，是主键的值，查到主键的值以后，还需要再通过主键的值再进行一次查询(这个过程叫做回表)。
面试官：刚刚你提到主键索引查询只会查一次，而非主键索引需要回表查询多次。是所有情况都是这样的吗？非主键索引一定会查询多次吗？

我：通过覆盖索引也可以只查询一次，

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL 只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少 I/O 提高效率。 如，表 covering_index_sample 中有一个普通索引 idx_key1_key2(key1,key2)。当我们通过 SQL 语句：select key2 from covering_index_sample where key1 = ‘keytest’; 的时候，就可以通过覆盖索引查询，无需回表。
面试官：不知道的话没关系，想问一下，你们在创建索引的时候都会考虑哪些因素呢？

我：我们一般对于查询概率比较高，经常为 where 条件的字段设置索引
面试官：那你们有用过联合索引吗？

我：用过呀，我们有对一些表中创建过联合索引。
面试官：那你们在创建联合索引的时候，需要做联合索引多个字段之间顺序你们是如何选择的呢？

我：我们把识别度最高的字段放到最前面。
面试官：为什么这么做呢？

我：（这个问题有点把我问蒙了，稍微有些慌乱）这样的话可能命中率会高一点吧。。。
面试官：那你知道最左前缀匹配吗？

我：在创建多列索引时，我们根据业务需求，where 子句中使用最频繁的一列放在最左边，因为 MySQL 索引查询会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。所以当我们创建一个联合索引的时候，如 (key1,key2,key3)，相当于创建了（key1）、(key1,key2) 和(key1,key2,key3)三个索引，这就是最左匹配原则。
面试官：你们线上用的 MySQL 是哪个版本啊呢？

我：我们 MySQL 是 5.7
面试官：那你知道在 MySQL 5.6 中，对索引做了哪些优化吗？

我：Index Condition Pushdown Optimization（索引下推优化） MySQL 5.6 引入了索引下推优化，默认开启，使用 SET optimizer_switch = ‘index_condition_pushdown=off’; 可以将其关闭。官方文档中给的例子和解释如下： people 表中（zipcode，lastname，firstname）构成一个索引

SELECT * FROM people WHERE zipcode=‘95054’ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;

如果没有使用索引下推技术，则 MySQL 会通过 zipcode=‘95054’从存储引擎中查询对应的数据，返回到 MySQL 服务端，然后 MySQL 服务端基于 lastname LIKE’%etrunia%‘和 address LIKE’%Main Street%'来判断数据是否符合条件。 如果使用了索引下推技术，则 MYSQL 首先会返回符合 zipcode='95054’的索引，然后根据 lastname LIKE '%etrunia%'和 address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接 reject 掉**。有了索引下推优化，可以在有 like 条件查询的情况下，减少回表次数**。
面试官：有什么手段可以知道有没有走索引查询呢？

我：可以通过 explain 查看 sql 语句的执行计划，通过执行计划来分析索引使用情况
面试官：那什么情况下会发生明明创建了索引，但是执行的时候并没有通过索引呢？

我：查询优化器。一条 SQL 语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。 在一条单表查询语句真正执行之前，MySQL 的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。 优化过程大致如下： 1、根据搜索条件，找出所有可能使用的索引 2、计算全表扫描的代价 3、计算使用不同索引执行查询的代价 4、对比各种执行方案的代价，找出成本最低的那一个

高性能索引策略

正确地创建和使用索引是实现高性能查询的基础。高效地选择和使用索引有很多种方式，其中有些是针对特殊案例的优化方法，有些则是针对特定行为的优化。

独立的列

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

下面两个查询就无法使用索引：

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5；
SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

凭肉眼很容易看出 WHERE 中的表达式其实等价于 actor_id=4，但是 MySQL 无法自动解析这个方程式。这完全是用户行为。我们应该养成简化 WHERE 条件的习惯，始终 将索引列单独放在比较符号的一侧。

前缀索引和索引选择性

概念

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

索引的选择性是指，不重复的索引值（也称为基数，cardlinality）和数据表的记录总数（#T）的比值，范围从 1/#T 到 1 之间。

索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。

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

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

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

寻找合适前缀长度

首先找到最常见的城市列表

注意，上面每个值都出现了 45~65 次。现在查找最频繁的城市前缀。

从 3 个前缀字母开始

每个前缀都比原来的城市出现的次数更多，因此唯一前缀比唯一城市要少得多

从 7 个前缀字母开始

前缀长度为 7 时，其选择性接近完整列的选择性，则前缀长度为 7 时比较合适。

计算完整列的选择性

通常来说（尽管也有例外情况），这个例子中如果前缀的选择性能够接近 0.031， 基本上就可用了。可以在一个查询中针对不同前缀长度进行计算，这对于大表非常有用。下面给出了如何在同一个查询中计算不同前缀长度的选择性：

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

但只看平均选择性是不够的，也有例外的情况，需要考虑最坏情况下的选择性。平均选择性会让你认为前缀长度为 4 或者 5 的索引已经足够了，但如果数据分布不均匀，可能就会有陷阱。下面查询结果就出现了这个情况（观察前缀为 4 的最常出现城市的次数，可以看到明显不均匀）

如果前缀是 4 个字节，则最常出现的前缀的出现次数比最常出现的城市的出现次数要大很多**。即这些值的选择性比平均选择性要低**。

例如在真实的城市名上建一个长度为 4 的前缀索引，对于以 “San” 和“New”开头的城市的选择性就会非常糟糕，因为很多城市都以这两个词开头。

创建前缀索引

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

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

多列索引

以下这种索引策略，一般是由于人们听到一些专家诸如 “把 WHERE 条件里面的列都建上索引” 这样模糊的建议导致的。实际上这个建议是非常错误的。

CREATE TABLE t (
	c1 INT,
	c2 INT,
	c3 INT,
	KEY(c1),
	KEY(c2),
	KEY(c3)
);

这样一来最好的情况下 也只能是 “一星” 索引，其性能比起真正最优的索引可能差几个数量级。有时如果无法设计一个 “三星” 索引，那么不如忽略掉 WHERE 子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。

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

例如， 表 film_actor 在字段 film_id 和 actor_id 上各有一个单列索引。

在老的 MySQL 版本中，MySQL 对以下这个查询会使用全表扫描

 SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1 OR film_id = 1;

除非改写成如下的两个查询 UNION的方式：

SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1
UNION ALL
AND actor_id <> 1;

在 MySQL 5.0 和更新的版本中，以下这个查询能够同时使用这两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：

OR 条件的联合（union），AND 条件的相交 （intersection），组合前两种情况的联合及相交。

SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1 OR film_id = 1;

通过 EXPLAIN 关键字来查看，从 Extra 列中可以看到这点：

* *** *** *** *** *** *** *** *** **1. row* *** *** *** *** *** *** *** *** **
id: 1
select_type: SIMPLE
table: film_actor
type: index_merge
possible_keys: PRIMARY,idx_fk_film_id
key: PRIMARY,idx_fk_film_id
key_len: 2,2
ref: NULL
rows: 29
Extra: Using union(PRIMARY,idx_fk_film_id); Using where

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

• 当出现服务器对多个索引做相交操作时（通常有多个 AND 条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
• 当服务器需要对多个索引做联合操作时（通常有多个 OR 条件），通常需要耗费大量 CPU 和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
• 更重要的是，优化器不会把这些计算到“查询成本”（cost）中，优化器只关心随机页面读取。这会使得查询的成本被“低估”，导致该执行计划还不如直接走全表扫 描。这样做不但会消耗更多的 CPU 和内存资源，还可能会影响查询的并发性，但 如果是单独运行这样的查询则往往会忽略对并发性的影响。通常来说，还不如像 在 MySQL 4.1 或者更早的时代一样，将查询改写成 UNION 的方式往往更好。

如果在 EXPLAIN 中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。也可以通过参数 optimizer_switch 来关闭索引合并功能。也可以使用 IGNORE INDEX 提示让优化器忽略掉某些索引。

选择合适的索引列顺序

以下内容适用于 B-Tree 索引；哈希或者其他类型的索引并不会像 B-Tree 索引一样按顺序存储数据。

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

对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。这个建议有用吗? 在某些场景可能有帮助，但通常不如避免随机 IO 和排序那么重 要，考虑问题需要更全面（场景不同则选择不同，没有一个放之四海皆准的法则。这 里只是说明，这个经验法则可能没有你想象的重要）。

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化 WHERE 条件的查找

性能不只是依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关。

可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

以下面的查询为例

SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

先用下面的查询预测一下，看看各个 WHERE 条件的分支对应的数据基数有多大，以此来确定哪个列的选择性更高

SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment;
结果如下：
* *** *** *** *** *** *** *** *** **1. row* *** *** *** *** *** *** *** *** **
SUM(staff_id = 2): 7992
SUM(customer_id = 584): 30

根据前面的经验法则，应该将索引列 customer_id 放到前面，因为对应条件值的 customer_id 数量更小。我们再来看看对于这个 customer_id 的条件值，对应的 staff_id 列的选择性如何：

SELECT SUM(staff_id = 2) FROM payment WHERE customer_id = 584;
结果如下：
* *** *** *** *** *** *** *** *** **1. row* *** *** *** *** *** *** *** *** **
SUM(staff_id = 2): 17

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

如果没有类似的具体查询来运行，那么最好还是按经验法则来做，因为经验法则考虑的是全局基数和选择性，而不是某个具体查询：

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;
结果如下：
* *** *** *** *** *** *** *** *** **1. row* *** *** *** *** *** *** *** *** **
staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049

从结果可以看出，custom_id 的选择性更高，所以答案是将其作为索引列的第一列：

ALTER TABLE payment ADD KEY(customer_id, staff_id);

当使用前缀索引的时候，在某些条件值的基数比正常值高的时候，问题就来了。 例如，在某些应用程序中，对于没有登录的用户，都将其用户名记录为 “guset”，在记录用户行为的会话（session）表和其他记录用户活动的表中“guest” 就成为了一个特殊用户 ID。一旦查询涉及这个用户，那么和对于正常用户的查询就大不同了，因为通常有很多会话都是没有登录的。系统账号也会导致类似的问题。一个应用通常都有一个特殊的管理员账号，和普通账号不同，它并不是一个具体的用户，系统中所有的其他用户都是这个用户的好友，所以系统往往通过它向网站的所有用户发送状态通知和其他消息。这个账号的巨大的好友列表很容易导致网站出现服务器性能问题。 这实际上是一个非常典型的问题。任何的异常用户，不仅仅是那些用于管理应用的设计糟糕的账号会有同样的问题；那些拥有大量好友、图片、状态、收藏的用户， 也会有前面提到的系统账号同样的问题。

下面是一个真实案例，在一个用户分享购买商品和购买经验的论坛上，这个特殊表上的查询运行得非常慢：

SELECT COUNT(DISTINCT threadId) AS COUNT_VALUE
FROM Message
WHERE (groupId = 10137) AND (userId = 1288826) AND (anonymous = 0)
ORDER BY priority DESC, modifiedDate DESC;

这个查询看似没有建立合适的索引，来看看是否可以优化。EXPLAIN 的结果如下：

id: 1
select_type: SIMPLE
table: Message
type: ref
key: ix_groupId_userId
key_len: 18
ref: const,const
rows: 1251162
Extra: Using where

MySQL 为这个查询选择了索引（groupId，userId），如果不考虑列的基数，这看起来是一个非常合理的选择。但如果考虑一下 user ID 和 group ID 条件匹配的行数， 可能就会有不同的想法了：

SELECT COUNT(*), SUM(groupId = 10137),
SUM(userId = 1288826), SUM(anonymous = 0)
FROM Message;
结果如下：
* *** *** *** *** *** *** *** *** **1. row* *** *** *** *** *** *** *** *** **
count(*): 4142217
sum(groupId = 10137): 4092654
sum(userId = 1288826): 1288496
sum(anonymous = 0): 4141934

从上面的结果来看符合组（groupId）条件几乎满足表中的所有行，符合用户（userId）条件的有 130 万条记录——也就是说索引基本上没什么用。因为这些数据是 从其他应用中迁移过来的，迁移的时候把所有的消息都赋予了管理员组的用户**。这个案例的解决办法是修改应用程序代码，区分这类特殊用户和组，禁止针对这类用户和组执行这个查询**。

从这个小案例可以看到经验法则和推论在多数情况是有用的，但要注意不要假设平均情况下的性能也能代表特殊情况下的性能，特殊情况可能会摧毁整个应用的性能。

最后，尽管关于选择性和基数的经验法则值得去研究和分析，但一定要记住别忘了 WHERE 子句中的排序、分组和范围条件等其他因素，这些因素可能对查询的性能造成非常大的影响。

聚簇索引

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

具体的细节依赖于其实现方式，但 InnoDB 的聚簇索引实际上在同一个结构中保存了 B-Tree 索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）中。

术语**“聚簇”表示数据行和相邻的键值紧凑地存储在一起**。

因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引（不过，覆盖索引可以模拟多个聚簇索引的情况）

因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引。

叶子页包含了行的全部数据，但是节点页只包含了索引列。在这个案例中，索引列包含的是整数值。

聚簇索引的数据分布

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

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

设计表和查询时能充分利用以下聚簇索引的优点，那就能极大地提升性能。

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

聚簇索引也有一些缺点：

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

为什么二级索引需要两次索引查找？

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

InnoDB 和 MyISAM 的数据分布对比

聚簇索引和非聚簇索引的数据分布有区别，以及对应的主键索引和二级索引的数据分布也有区别。

来看看看 InnoDB 和 MyISAM 是如何存储下面这个表的：

CREATE TABLE layout_test (
	col1 int NOT NULL,
	col2 int NOT NULL,
	PRIMARY KEY(col1),
	KEY(col2)
);

假设该表的主键取值为 1～10000，按照随机顺序插入并使用 OPTIMIZE TABLE 命令做了优化。换句话说，数据在磁盘上的存储方式已经最优，但行的顺序是随机的。列 col2 的值是从 1～100 之间随机赋值，所以有很多重复的值。

MyISAM 的数据分布

MyISAM 的数据分布如下图所示。MyISAM 的数据分布非常简单，按照数据插入的顺序存储在磁盘上。

在行的旁边显示了行号，从 0 开始递增。因为行是定长的，所以 MyISAM 可以从表的开头跳过所需的字节找到需要的行（MyISAM 并不总是使用图中的“行号”，而是根据定长还是变长的行使用不同策略）。

这种分布方式很容易创建索引。下面显示的一系列图，隐藏了页的物理细节，只显示索引中的“节点”，索引中的每个叶子节点包含“行号”。图一显示了表的主键。

这里忽略了一些细节，例如前一个 B-Tree 节点有多少个内部节点，不过这并不影响对非聚簇存储引擎的基本数据分布的理解。col2 列上的索引和其他索引没有什么区别。

MyISAM 中主键索引和其他索引在结构上没有什么不同**。主键索引就是一个名为 PRIMARY 的唯一非空索引**。

InnoDB 的数据分布

因为 InnoDB 支持聚簇索引，所以使用非常不同的方式存储同样的数据，其存储方式如下：

InnoDB 表 layout_test 的主键分布

因为在 InnoDB 中，聚簇索引 “就是” 表，所以不像 MyISAM 那样需要独立的行存储。

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

还有一点和 MyISAM 的不同是，InnoDB 的二级索引和聚簇索引很不相同。

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

下图显示了示例表的 col2 索引。每一个叶子节点都包含了索引列（这里是 col2），紧接着是主键值（col1）。 下图也展示了 B-Tree 的叶子节点结构，非叶子节点这样的细节被省略了。 InnoDB 的非叶子节点包含了索引列和一个指向下级节点的指针（下一级节点可以是非叶子节点，也可以是叶子节点）。这对聚簇索引和二级索引都适用。

InnoDB 表 layout_test 的二级索引分布

InnoDB 和 MyISAM 如何存放表的抽象图

聚簇和非聚簇表对比图

覆盖索引

MySQL 也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据，就不必再回表查询。

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，则称之为“覆盖索引”。

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

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL 就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上**。覆盖索引对于 I/O 密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中**（这对于 MyISAM 尤其正确，因为 MyISAM 能压缩索引以变得更小）。
• 因为索引是按照列值顺序存储的（至少在单个页内是如此），所以对于 I/O 密集型的范围查询会比随机从磁盘读取每一行数据的 I/O 要少得多。对于某些存储引擎， 例如 MyISAM 和 Percona XtraDB，甚至可以通过 OPTIMIZE 命令使得索引完全顺序 排列，这让简单的范围查询能使用完全顺序的索引访问。
• 一些存储引擎如 MyISAM 在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景。
• 由于 InnoDB 的聚簇索引，覆盖索引对 InnoDB 表特别有用**。InnoDB 的二级索引在子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询**。

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

当发起一个被索引覆盖的查询（也叫做索引覆盖查询）时，在 EXPLAIN 的 Extra 列可以看到 “Using index” 的信息。例如，表 sakila.inventory 有一个多列索引 220（store_id，flm_id）。MySQL 如果只需访问这两列，就可以使用这个索引做覆盖索引，如下所示：

 EXPLAIN SELECT store_id, film_id FROM sakila.inventory;
 结果如下：
* *** *** *** *** *** *** *** *** **1. row* *** *** *** *** *** *** *** *** **
id: 1
select_type: SIMPLE
table: inventory
type: index
possible_keys: NULL
key: idx_store_id_film_id
key_len: 3
ref: NULL
rows: 4673
Extra: Using index

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

来看看下面的查询：

EXPLAIN SELECT * FROM products WHERE actor='SEAN CARREY' AND title like '%APOLLO%';
结果如下：
* *** *** *** *** *** *** *** *** **1. row* *** *** *** *** *** *** *** *** **
id: 1
select_type: SIMPLE
table: products
type: ref
possible_keys: ACTOR,IX_PROD_ACTOR
key: ACTOR
key_len: 52
ref: const
rows: 10
Extra: Using where

这里索引无法覆盖该查询，有两个原因：

• 没有任何索引能够覆盖这个查询。因为查询从表中选择了所有的列，而没有任何索引覆盖了所有的列。不过，理论上 MySQL 还有一个捷径可以利用：WHERE 条件中的列是有索引可以覆盖的，因此 MySQL 可以使用该索引找到对应的 actor 并检查 title 是否匹配，过滤之后再读取需要的数据行。
• MySQL 不能在索引中执行 LIKE 操作。这是底层存储引擎 API 的限制，MySQL 5.5 和更早的版本中只允许在索引中做简单比较操作（例如等于、不等于以及大 于）。MySQL 能在索引中做最左前缀匹配的 LIKE 比较，因为该操作可以转换为简单的比较操作，但是如果是通配符开头的 LIKE 查询，存储引擎就无法做比较匹配。这种情况下，MySQL 服务器只能提取数据行的值而不是索引值来做比较。

重写查询并巧妙地设计索引

先将索引扩展至覆盖三个数据列（artist，title，prod_id），然后按如下方式重写查询：

EXPLAIN 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. row* *** *** *** *** *** *** *** *** **
id: 1
select_type: PRIMARY
table: <derived2>
...omitted...
* *** *** *** *** *** *** *** *** **2. row* *** *** *** *** *** *** *** *** **
id: 1
select_type: PRIMARY
table: products
...omitted...
* *** *** *** *** *** *** *** *** **3. row* *** *** *** *** *** *** *** *** **
id: 2
select_type: DERIVED
table: products
type: ref
possible_keys: ACTOR,ACTOR_2,IX_PROD_ACTOR
key: ACTOR_2
key_len: 52
ref:
rows: 11
Extra: Using where; Using index

延迟关联

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

延迟关联优化的效果取决于 WHERE 条件匹配返回的行数。假设这个 products 表有 100 万 行，来看一下上面两个查询在三个不同的数据集上的表现，每个数据集都包含 100 万行：

第一个数据集	Sean Carrey 出演了 30000 部作品，其中有 20000 部的标题中包含了 Apollo
第二个数据集	Sean Carrey 出演了 30000 部作品，其中 40 部的标题中包含了 Apollo
第三个数据集	Sean Carrey 出演了 50 部作品，其中 10 部的标题中包含了 Apollo

使用上面的三种数据集来测试两种不同的查询，得到的结果如表所示

下面是对结果的分析：

• 在示例 1 中，查询返回了一个很大的结果集，因此看不到优化的效果。大部分时间都花在读取和发送数据上了。
• 在示例 2 中，经过索引过滤，尤其是第二个条件过滤后只返回了很少的结果集，优化的效果非常明显：在这个数据集上性能提高了 5 倍，优化后的查询的效率主要得益于只需要读取 40 行完整数据行，而不是原查询中需要的 30000 行。
• 在示例 3 中，显示了子查询效率反而下降的情况。因为索引过滤时符合第一个条件的结果集已经很小，所以子查询带来的成本反而比从表中直接提取完整行更高。

在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询。不过，可以更进一步优化 InnoDB。回想一下，InnoDB 的二级索引的叶子节点都包含了主键的值，这意味着 InnoDB 的二级索引可以有效地利用这些 “额外” 的主键列来覆盖查询。

例如，sakila.actor 使用 InnoDB 存储引擎，并在 last_name 字段有二级索引，虽然该索引的列不包括主键 actor_id，但也能够用于对 actor_id 做覆盖查询：

EXPLAIN SELECT actor_id, last_name FROM sakila.actor WHERE last_name = 'HOPPER';
结果如下：
* *** *** *** *** *** *** *** *** **1. row* *** *** *** *** *** *** *** *** **
id: 1
select_type: SIMPLE
table: actor
type: ref
possible_keys: idx_actor_last_name
key: idx_actor_last_name
key_len: 137
ref: const
rows: 2
Extra: Using where; Using index

使用索引扫描来做排序

MySQL 有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描。

如果 EXPLAIN 出来的 type 列的值为 “index”，则说明 MySQL 使用了索引扫描来做排序（不要和 Extra 列的“Using index” 搞混淆了）。

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

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

可使用索引做排序的场景

• 只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL 才能够使用索引来对结果做排序。

• 如果查询需要关联多张表，则只有当ORDER BY 子句引用的字段全部为第一个表时，才能使用索引做排序。

使用限制

ORDER BY 子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL 都需要执行排序操作，而无法利用索引排序。

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

例如，Sakila 示例数据库的表 rental 在列（rental_date，inventory_id，customer_id）上有名为 rental_date 的索引。

CREATE TABLE rental (
...
	PRIMARY KEY (rental_id),
	UNIQUE KEY rental_date (rental_date,inventory_id,customer_id),
	KEY idx_fk_inventory_id (inventory_id),
	KEY idx_fk_customer_id (customer_id),
	KEY idx_fk_staff_id (staff_id),
...
);

MySQL 可以使用 rental_date 索引为下面的查询做排序，从 EXPLAIN 中可以看到没有出现文件排序（filesort）操作：

EXPLAIN SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date = '2005-05-25' ORDER BY inventory_id, customer_id;
结果如下：
   * *** *** *** *** *** *** *** *** **1. row* *** *** *** *** *** *** *** *** **
             type: ref
    possible_keys: rental_date
              key: rental_date
             rows: 1
            Extra: Using where

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

下面这个查询可以利用索引排序，是因为查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两列组合在一起，就形成了索引的最左前缀：

... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC;

下面是一些不能使用索引做排序的查询：

• 下面这个查询使用了两种不同的排序方向，但是索引列都是正序排序的：

  ... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC, customer_id ASC;
  

• 下面这个查询的 ORDER BY 子句中引用了一个不在索引中的列：

  ... WHERE rental_date='2005-05-25' ORDER BY inventory_id，staff_id;
  

• 下面这个查询在索引列的第一列上是范围条件，所以 MySQL 无法使用索引的其余列：

  ... WHERE rental_date > '2005-05-25' ORDER BY inventory_id, customer_id;
  

• 这个查询在 inventory_id 列上有多个等于条件。对于排序来说，这也是一种范围查询：

  ... WHERE rental_date = '2005-05-25' AND inventory_id IN(1,2) ORDER BY customer_id;
  

下面这个例子理论上是可以使用索引进行关联排序的，但由于优化器在优化时将 film_actor 表当作关联的第二张表，所以实际上无法使用索引：

压缩（前缀压缩）索引

MyISAM 使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。默认只压缩字符串，但通过参数设置也可以对整数做压缩。

MyISAM 压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。例如，索引块中的第一个值是 “perform”，第二个值是“performance”，那么第二个值的前缀压缩后存储的是类似“7,ance” 这样的形式。MyISAM 对行指针也采用类似的前缀压缩方式。

压缩块使用更少的空间，代价是某些操作可能更慢**。因为每个值的压缩前缀都依赖前面的值，所以 MyISAM 查找时无法在索引块使用二分查找而只能从头开始扫描。正序的扫描速度还不错，但是如果是倒序扫描——例如 ORDER BY DESC——就不是很好了**。所有在块中查找某一行的操作平均都需要扫描半个索引块。

测试表明，对于 CPU 密集型应用，因为扫描需要随机查找，压缩索引使得 MyISAM 在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了**。压缩索引需要在 CPU 内存资源与磁盘之间做权衡**。压缩索引可能只需要十分之一大小的磁盘空间，如果是 I/O 密集型应用，对某些查询带来的好处会比成本多很多。

可以在 CREATE TABLE 语句中指定 PACK_KEYS 参数来控制索引压缩的方式。

冗余和重复索引

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

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

有时会在不经意间创建了重复索引，例如下面的代码：

CREATE TABLE test (
	ID INT NOT NULL PRIMARY KEY,
	A INT NOT NULL,
	B INT NOT NULL,
	UNIQUE(ID),
	INDEX(ID)
) ENGINE=InnoDB;

一个经验不足的用户可能是想创建一个主键，先加上唯一限制，然后再加上索引以供查询使用。事实上，MySQL 的唯一限制和主键限制都是通过索引实现的，因此，上面的写法实际上在相同的列上创建了三个重复的索引****。通常并没有理由这样做，除非是在同一列上创建不同类型的索引来满足不同的查询需求。

冗余索引和重复索引有一些不同

如果创建了索引（A，B），再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引。因此索引（A，B）也可以当作索引（A）来使用（这种冗余只是对 B-Tree 索引来说的）。

但是如果再创建索引（B，A），则不是冗余索引，索引（B）也不是，因为 B 不是索引（A，B）的最左前缀列。

另外，其他不同类型的索引（例如哈希索引或者全文索引）也不会是 B-Tree 索引的冗余索引，而无论覆盖的索引列是什么。

冗余索引通常发生在为表添加新索引的时候

例如，有人可能会增加一个新的索引（A，B）而不是扩展已有的索引（A）。还有一种情况是将一个索引扩展为（A，ID），其中 ID 是主键，对于 InnoDB 来说主键列已经包含在二级索引中了，所以这也是冗余的。

大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。

表中的索引越多插速度会越慢

一般来说，增加新索引将会导致 INSERT、UPDATE、DELETE 等操作的速度变慢，特别是当新增索引后导致达到了内存瓶颈的时候。

解决冗余索引和重复索引的方法很简单，删除这些索引就可以，但首先要做的是找出这样的索引。

可以通过写一些复杂的访问 INFORMATION_SCHEMA 表的查询来找，不过还有两个更简单的方法。可使用 Shlomi Noach 的 common_schema 中的一些视图来定位，common_schema 是一系列可以安装到服务器上的常用的存储和视图（http://code.google.com/p/common-schema/）。这比自己编写查询要快而且简单。另外也可以使用 Percona Toolkit 中的 pt-duplicate-key-checker，该工具通过分析表结构来找出冗余和重复的索引。对于大型服务器来说，使用外部的工具可能更合适些；如果服务器上有大量的数据或者大量的表，查询 INFORMATION_SCHEMA 表可能会导致性能问题。

在决定哪些索引可以被删除的时候要非常小心。回忆一下，在前面的 InnoDB 的示例表中，因为二级索引的叶子节点包含了主键值，所以在列（A）上的索引就相当于在（A，ID）上的索引。如果有像 WHERE A=5 ORDER BY ID 这样的查询，这个索引会很有作用。但如果将索引扩展为（A，B），则实际上就变成了（A，B，ID），那么上面查询的 ORDER BY 子句就无法使用该索引做排序，而只能用文件排序了。

所以，建议使用 Percona 工具箱中的 pt-upgrade 工具来仔细检查计划中的索引变更

未使用的索引

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

另外，还可以使用 Percona Toolkit 中的 pt-index-usage，该工具可以读取查询日志，并对日志中的每条查询进行 EXPLAIN 操作，然后打印出关于索引和查询的报告。这个工具不仅可以找出哪些索引是未使用的，还可以了解查询的执行计划——例如在某些情况有些类似的查询的执行方式不一样，这可以帮助你定位到那些偶尔服务质量差的查询，优化它们以得到一致的性能表现。该工具也可以将结果写入到 MySQL 的表中，方便查询结果。

索引和锁

索引可以让查询锁定更少的行

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

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

通过下面的例子再次使用数据库 Sakila 很好地解释了这些情况：

SET AUTOCOMMIT=0;
BEGIN;
SELECT actor_id FROM sakila.actor WHERE actor_id < 5 AND actor_id <> 1 FOR UPDATE;

这条查询仅仅会返回 2～4 之间的行，但是实际上获取了 1～4 之间的行的排他锁。InnoDB 会锁住第 1 行，这是因为 MySQL 为该查询选择的执行计划是索引范围扫描：

换句话说，底层存储引擎的操作是“从索引的开头开始获取满足条件 actor_id<5 的记录”，服务器并没有告诉 InnoDB 可以过滤第 1 行的 WHERE 条件。注意到 EXPLAIN 的 Extra 列出现了“Using where”，这表示 MySQL 服务器将存储引擎返回行以后再应用 WHERE 过滤条件。

下面的第二个查询就能证明第 1 行确实已经被锁定，尽管第一个查询的结果中并没有这个第 1 行**。保持第一个连接打开，然后开启第二个连接并执行如下查询**：

SET AUTOCOMMIT=0;
BEGIN;
SELECT actor_id FROM sakila.actor WHERE actor_id = 1 FOR UPDATE;

这个查询将会挂起，直到第一个事务释放第 1 行的锁。这个行为对于基于语句的复制的正常运行来说是必要的。

就像这个例子显示的，即使使用了索引，InnoDB 也可能锁住一些不需要的数据。如果不能使用索引查找和锁定行的话问题可能会更糟糕，MySQL 会做全表扫描并锁住所有的行，而不管是不是需要。

关于 InnoDB、索引和锁有一些很少有人知道的细节：InnoDB 在二级索引上使用共享（读）锁，但访问主键索引需要排他（写）锁。这消除了使用覆盖索引的可能性，并且使得 SELECT FOR UPDATE 比 LOCK IN SHARE MODE 或非锁定查询要慢很多。

索引案例学习

理解索引最好的办法是结合示例，所以这里准备了一个索引的案例。

假设要设计一个在线约会网站，用户信息表有很多列，包括国家、地区、城市、性别、眼睛颜色，等等。网站必须支持上面这些特征的各种组合来搜索用户，还必须允许根据用户的最后在线时间、其他会员对用户的评分等对用户进行排序并对结果进行限制。如何设计索引满足上面的复杂需求呢?

出人意料的是第一件需要考虑的事情是需要使用索引来排序，还是先检索数据再排序。使用索引排序会严格限制索引和查询的设计。例如，如果希望使用索引做根据其他会员对用户的评分的排序，则 WHERE 条件中的 age BETWEEN 18 AND 25 就无法使用索引**。如果 MySQL 使用某个索引进行范围查询，也就无法再使用另一个索引（或者是该索引的后续字段）进行排序了**。如果这是很常见的 WHERE 条件，那么我们当然就会认为很多查询需要做排序操作（例如文件排序 filesort）。

支持多种过滤条件

现在需要看看哪些列拥有很多不同的取值，哪些列在 WHERE 子句中出现得最频繁。在有更多不同值的列上创建索引的选择性会更好。这可以让 MySQL 更有效地过滤掉不需要的行。

country 列的选择性通常不高，但可能很多查询都会用到。sex 列的选择性肯定很低，但也会在很多查询中用到。所以考虑到使用的频率，还是建议在创建不同组合索引的时候将（sex，country）列作为前缀。

但根据传统的经验不是说不应该在选择性低的列上创建索引的吗？

这里要将两个选择性都很低的字段作为索引的前缀列，有两个理由：第一点，如前所述几乎所有的查询都会用到 sex 列。前面曾提到，几乎每一个查询都会用到 sex 列，甚至会把网站设计成每次都只能按某一种性别搜索用户。更重要的一点是，索引中加上这一列也没有坏处，即使查询没有使用 sex 列也可以通过下面的 “诀窍” 绕过。

这个 “诀窍” 就是：如果某个查询不限制性别，那么可以通过在查询条件中新增 AND SEX IN（‘m’,‘f’）来让 MySQL 选择该索引。这样写并不会过滤任何行，和没有这个条件时返回的结果相同。但是必须加上这个列的条件，MySQL 才能够匹配索引的最左前缀。这个 “诀窍” 在这类场景中非常有效，但如果列有太多不同的值，就会让 IN()列表太长，这样做就不行了。

这个案例显示了一个基本原则：考虑表上所有的选项。当设计索引时，不要只为现有的查询考虑需要哪些索引，还需要考虑对查询进行优化****。如果发现某些查询需要创建新索引，但是这个索引又会降低另一些查询的效率，那么应该想一下是否能优化原来的查询。应该同时优化查询和索引以找到最佳的平衡，而不是闭门造车去设计最完美的索引。

接下来，需要考虑其他常见 WHERE 条件的组合，并需要了解哪些组合在没有合适索引的情况下会很慢。（sex，country，age）上的索引就是一个很明显的选择，另外很有可能还需要（sex，country，region，age）和（sex，country，region，city，age）这样的组合索引。

这样就会需要大量的索引。如果想尽可能重用索引而不是建立大量的组合索引，可以使用前面提到的 IN()的技巧来避免同时需要（sex，country，age）和（sex，country，region，age）的索引。如果没有指定这个字段搜索，就需要定义一个全部国家列表，或者国家的全部地区列表，来确保索引前缀有同样的约束（组合所有国家、地区、性别将会是一个非常大的条件）。

这些索引将满足大部分最常见的搜索查询，但是如何为一些生僻的搜索条件（比如 has_pictures、eye_color、hair_color 和 education）来设计索引呢？这些列的选择性高、使用也不频繁，可以选择忽略它们，让 MySQL 多扫描一些额外的行即可。另一个可选的方法是在 age 列的前面加上这些列，在查询时使用前面提到过的 IN()技术来处理搜索时没有指定这些列的场景。

你可能已经注意到了，我们一直将 age 列放在索引的最后面。age 列有什么特殊的地方吗？为什么要放在索引的最后？我们总是尽可能让 MySQL 使用更多的索引列，因为查询只能使用索引的最左前缀，直到遇到第一个范围条件列。前面提到的列在 WHERE 子句中都是等于条件，但是 age 列则多半是范围查询（例如查找年龄在 18～25 岁之间的人）。

当然，也可以使用 IN()来代替范围查询，例如年龄条件改写为 IN（18，19，20，21，22，23，24，25），但不是所有的范围查询都可以转换。这里描述的基本原则是，尽可能将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。

前面提到可以在索引中加入更多的列，并通过 IN()的方式覆盖那些不在 WHERE 子句中的列。但这种技巧也不能滥用，否则可能会带来麻烦。因为每额外增加一个 IN()条件，优化器需要做的组合都将以指数形式增加，最终可能会极大地降低查询性能。考虑下面的 WHERE 子句：

WHERE eye_color   IN('brown','blue','hazel')
AND hair_color IN('black','red','blonde','brown')
AND sex IN('M','F')

优化器则会转化成 4×3×2=24 种组合，执行计划需要检查 WHERE 子句中所有的 24 种组合。对于 MySQL 来说，24 种组合并不是很夸张，但如果组合数达到上千个则需要特别小心。老版本的 MySQL 在 IN()组合条件过多的时候会有很多问题。查询优化可能需要花很多时间，并消耗大量的内存**。新版本的 MySQL 在组合数超过一定数量后就不再进行执行计划评估了，这可能会导致 MySQL 不能很好地利用索引**。

避免多个范围条件

什么是范围条件？

从 EXPLAIN 的输出很难区分 MySQL 是要查询范围值，还是查询列表值。EXPLAIN 使用同样的词 “range” 来描述这两种情况。例如，从 type 列来看，MySQL 会把下面这种查询当作是 “range” 类型：

EXPLAIN SELECT actor_id FROM sakila.actor WHERE actor_id > 45;
* *** *** *** *** *** *** *** ***1. row* *** *** *** *** *** *** *** ***
           id: 1
  select_type: SIMPLE
        table: actor
         type: range

但是下面这条查询呢？

EXPLAIN SELECT actor_id FROM sakila.actor WHERE actor_id IN(1, 4, 99)；
* *** *** *** *** *** *** *** ***1. row* *** *** *** *** *** *** *** ***
           id: 1
  select_type: SIMPLE
        table: actor
         type: range

从 EXPLAIN 的结果是无法区分这两者的，但可以从值的范围和多个等于条件来得出不同。在我们看来，第二个查询就是多个等值条件查询。

我们不是挑剔：这两种访问效率是不同的**。对于范围条件查询，MySQL 无法再使用范围列后面的其他索引列了，但是对于 “多个等值条件查询” 则没有这个限制**。

假设我们有一个 last_online 列并希望通过下面的查询显示在过去几周上线过的用户：

WHERE eye_color IN('brown','blue','hazel')
AND hair_color  IN('black','red','blonde','brown')
AND sex IN('M','F')
AND last_online > DATE_SUB(NOW(), INTERVAL 7 DAY)
AND age BETWEEN 18 AND 25

这个查询有一个问题：它有两个范围条件，last_online 列和 age 列，MySQL 可以使用 last_online 列索引或者 age 列索引，但无法同时使用它们。

如果条件中只有 last_online 而没有 age，那么我们可能考虑在索引的后面加上 last_online 列。这里考虑如果我们无法把 age 字段转换为一个 IN()的列表，并且仍要求对于同时有 last_online 和 age 这两个维度的范围查询的速度很快，那该怎么办? 答案是，很遗憾没有一个直接的办法能够解决这个问题。但是我们能够将其中的一个范围查询转换为一个简单的等值比较。为了实现这一点，我们需要事先计算好一个 active 列，这个字段由定时任务来维护。当用户每次登录时，将对应值设置为 1，并且将过去连续七天未曾登录的用户的值设置为 0。

这个方法可以让 MySQL 使用（active，sex，country，age）索引。active 列并不是完全精确的，但是对于这类查询来说，对精度的要求也没有那么高。如果需要精确数据，可以把 last_online 列放到 WHERE 子句，但不加入到索引中。这和本章前面通过计算 URL 哈希值来实现 URL 的快速查找类似。所以这个查询条件没法使用任何索引，但因为这个条件的过滤性不高，即使在索引中加入该列也没有太大的帮助。换个角度来说，缺乏合适的索引对该查询的影响也不明显。

到目前为止，我们可以看到：如果用户希望同时看到活跃和不活跃的用户，可以在查询中使用 IN()列表。我们已经加入了很多这样的列表，但另外一个可选的方案就只能是为不同的组合列创建单独的索引。至少需要建立如下的索引：（active，sex，country，age），（active，country，age），（sex，country，age）和（country，age）。这些索引对某个具体的查询来说可能都是更优化的，但是考虑到索引的维护和额外的空间占用的代价，这个可选方案就不是一个好策略了。

优化排序

使用文件排序对小数据集是很快的，但如果一个查询匹配的结果有上百万行的话会怎样？例如如果 WHERE 子句只有 sex 列，如何排序？

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

SELECT<cols>  FROM profiles WHERE sex='M' ORDER BY rating LIMIT 10;

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

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

SELECT<cols>  FROM profiles WHERE sex='M' ORDER BY rating LIMIT 100000,10;

无论如何创建索引，这种查询都是个严重的问题。因为随着偏移量的增加，MySQL 需要花费大量的时间来扫描需要丢弃的数据。反范式化、预先计算和缓存可能是解决这类查询的仅有策略**。一个更好的办法是限制用户能够翻页的数量，实际上这对用户体验的影响不大，因为用户很少会真正在乎搜索结果的第 10000 页**。

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

SELECT <cols> FROM profiles INNER JOIN (
SELECT <primary key cols> FROM profiles
WHERE x.sex='M' ORDER BY rating LIMIT 100000, 10
) AS x USING(<primary key cols>);

维护索引和表

维护表有三个主要的目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

找到并修复损坏的表

损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题，严重时甚至还会导致数据库的崩溃。如果你遇到了古怪的问题——例如一些不应该发生的错误 ——可以尝试运行 CHECK TABLE 来检查是否发生了表损坏（注意有些存储引擎不支持该命令；而有些引擎则支持以不同的选项来控制完全检查表的方式）

可以使用 REPAIR TABLE 命令来修复损坏的表，但同样不是所有的存储引擎都支持该命令。

如果存储引擎不支持，也可通过一个不做任何操作（no-op）的 ALTER 操作来重建表，例如修改表的存储引擎为当前的引擎。

ALTER TABLE innodb_tbl ENGINE=INNODB;

也可以使用一些存储引擎相关的离线工具，例如 myisamchk；或者将数据导出一份，然后再重新导入。不过，如果损坏的是系统区域，或者是表的 “行数据” 区域，而不是索引，那么上面的办法就没有用了。在这种情况下，可以从备份中恢复表，或者尝试从损坏的数据文件中尽可能地恢复数据。

如果 InnoDB 引擎的表出现了损坏，那么一定是发生了严重的错误，需要立刻调查一下原因**。InnoDB 一般不会出现损坏**。InnoDB 的设计保证了它并不容易被损坏。如果发生损坏，一般要么是数据库的硬件问题例如内存或者磁盘问题（有可能），要么是由于数据库管理员的错误例如在 MySQL 外部操作了数据文件（有可能），抑或是 InnoDB 本身的缺陷（不太可能）。常见的类似错误通常是由于尝试使用 rsync 备份 InnoDB 导致的。不存在什么查询能够让 InnoDB 表损坏，也不用担心暗处有“陷阱”。如果某条查询导致 InnoDB 数据的损坏那一定是遇到了 bug，而不是查询的问题。

如果遇到数据损坏，最重要的是找出是什么导致了损坏，而不只是简单地修复， 否则很有可能还会不断地损坏。可以通过设置 innodb_force_recovery 参数进入 InnoDB 的强制恢复模式来修复数据。

更新索引统计信息

MySQL 的查询优化器会通过两个 API 来了解存储引擎的索引值的分布信息，以决定如何使用索引

1. records_in_range()：通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录。对于某些存储引擎，该接口返回精确值，例如 MyISAM；但对于另一些存储引擎则是一个估算值，例如 InnoDB。
2. info()：该接口返回各种类型的数据，包括索引的基数（每个键值有多少条记录）。

如果存储引擎向优化器提供的扫描行数信息是不准确的数据，或者执行计划本身太复杂以致无法准确地获取各个阶段匹配的行数，那么优化器会使用索引统计信息来估算扫描行数。

MySQL 优化器使用的是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行。如果表没有统计信息，或者统计信息不准确，优化器就很有可能做出错误的决定。可以通过运行 ANALYZE TABLE 来重新生成统计信息解决这个问题。

每种存储引擎实现索引统计信息的方式不同，所以需要进行 ANALYZE TABLE 的频率也因不同的引擎而不同，每次运行的成本也不同：

• Memory 引擎根本不存储索引统计信息。
• MyISAM 将索引统计信息存储在磁盘中，ANALYZE TABLE 需要进行一次全索引扫描来计算索引基数。在整个过程中需要锁表。
• 直到 MySQL 5.5 版本，InnoDB 也不在磁盘存储索引统计信息，而是通过随机的索 y 引访问进行评估并将其存储在内存中。
  可以使用 SHOW INDEX FROM 命令来查看索引的基数（Cardinality）。例如：

mysql> SHOW INDEX FROM sakila.actor
* *** *** *** *** *** *** *** *** **1. row* *** *** *** *** *** *** *** *** **
       Table: actor
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: actor_id
   Collation: A
 Cardinality: 200
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
  Comment:
   * *** *** *** *** *** *** *** *** **2. row* *** *** *** *** *** *** *** *** **
           Table: actor
      Non_unique: 1
        Key_name: idx_actor_last_name
    Seq_in_index: 1
     Column_name: last_name
       Collation: A
     Cardinality: 200
        Sub_part: NULL
          Packed: NULL
            Null:
      Index_type: BTREE
         Comment:

这个命令输出了很多关于索引的信息，在 MySQL 手册中对上面每个字段的含义都有详细的解释。这里需要特别提及的是索引列的基数（Cardinality），其显示了存储引擎估算索引列有多少个不同的取值。在 MySQL 5.0 和更新的版本中，还可以通过 INFORMATION_SCHEMA.STATISTICS 表很方便地查询到这些信息。例如基于 INFORMATION_SCHEMA 的表，可以编写一个查询给出当前选择性比较低的索引。需要注意的是，如果服务器上的库表非常多，则从这里获取元数据的速度可能会非常慢，而且会给 MySQL 带来额外的压力。

InnoDB 引擎通过抽样的方式来计算统计信息，首先随机地读取少量的索引页面，然后以此为样本计算索引的统计信息。在老的 InnoDB 版本中，样本页面数是 8，新版本的 InnoDB 可以通过参数 innodb_stats_sample_pages 来设置样本页的数量。设置更大的值，理论上来说可以帮助生成更准确的索引信息，特别是对于某些超大的数据表来说，但具体设置多大合适依赖于具体的环境。

InnoDB 会在表首次打开，或者执行 ANALYZE TABLE，抑或表的大小发生非常大的变化（大小变化超过十六分之一或者新插入了 20 亿行都会触发）的时候计算索引的统计信息。

InnoDB 在打开某些 INFORMATION_SCHEMA 表，或者使用 SHOW TABLE STATUS 和 SHOW INDEX，抑或在 MySQL 客户端开启自动补全功能的时候都会触发索引统计信息的更新。如果服务器上有大量的数据，这可能就是个很严重的问题，尤其是当 I/O 比较慢的时候。客户端或者监控程序触发索引信息采样更新时可能会导致大量的锁，并给服务器带来很多的额外压力，这会让用户因为启动时间漫长而沮丧。只要 SHOW INDEX 查看索引统计信息，就一定会触发统计信息的更新。可以关闭 innodb_stats_on_metadata 参数来避免上面提到的问题。

如果使用 Percona 版本，使用的就是 XtraDB 引擎而不是原生的 InnoDB 引擎，那么可以通过 innodb_stats_auto_update 参数来禁止通过自动采样的方式更新索引统计信息，这时需要手动执行 ANALYZE TABLE 命令来更新统计信息。如果某些查询执行计划很不稳定的话，可以用该办法固化查询计划。我们当初引入这个参数也正是为了解决一些客户的这种问题。

如果想要更稳定的执行计划，并在系统重启后更快地生成这些统计信息，那么可以使用系统表来持久化这些索引统计信息。甚至还可以在不同的机器间迁移索引统计信息，这样新环境启动时就无须再收集这些数据。在 Percona 5.1 版本和官方的 5.6 版本都已经加入这个特性。在 Percona 版本中通过 innodb_use_sys_stats_table 参数可以启用该特性，官方 5.6 版本则通过 innodb_analyze_is_persistent 参数控制。

一旦关闭索引统计信息的自动更新，那么就需要周期性地使用 ANALYZE TABLE 来手动更新。否则，索引统计信息就会永远不变。如果数据分布发生大的变化，可能会出现一些很糟糕的执行计划。

减少索引和数据的碎片

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

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

表的数据存储也可能碎片化。然而，数据存储的碎片化比索引更加复杂。有三种类型的数据碎片。

1. 行碎片（Row fragmentation）：这种碎片指的是数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能下降。
2. 行间碎片（Intra-row fragmentation）：行间碎片是指逻辑上顺序的页，或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。
3. 剩余空间碎片（Free space fragmentation）：剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。

减少索引和数据的碎片的方法有如下 5 种：

对于 MyISAM 表，这三类碎片化都可能发生。但 InnoDB 不会出现短小的行碎片；InnoDB 会移动短小的行并重写到一个片段中。

1. 可以通过执行 OPTIMIZE TABLE 或者导出再导入的方式来重新整理数据。这对多数存储引擎都是有效的。

2. 对于一些存储引擎如 MyISAM，可以通过排序算法重建索引的方式来消除碎片。老版本的 InnoDB 没有什么消除碎片化的方法。

3. 最新版本 InnoDB 新增了 “在线” 添加和删除索引的功能，可以通过先删除，然后再重新创建索引的方式来消除索引的碎片化。

4. 对于那些不支持 OPTIMIZE TABLE 的存储引擎，可以通过一个不做任何操作（no-op）的 ALTER TABLE 操作来重建表。只需要将表的存储引擎修改为当前的引擎即可：

   ALTER TABLE <table> ENGINE=<engine>;
   

5. 对于开启了 expand_fast_index_creation 参数的 Percona Server，按这种方式重建表，则会同时消除表和索引的碎片化。但对于标准版本的 MySQL 则只会消除表（实际上是聚簇索引）的碎片化。可用先删除所有索引，然后重建表，最后重新创建索引的方式模拟 Percona Server 的这个功能。

应该通过一些实际测量而不是随意假设来确定是否需要消除索引和表的碎片化。Percona 的 XtraBackup 有个 --stats 参数以非备份的方式运行，而只是打印索引和表的统计情况，包括页中的数据量和空余空间。这可以用来确定数据的碎片化程度。另外也要考虑数据是否已经达到稳定状态，如果你进行碎片整理将数据压缩到一起，可能反而会导致后续的更新操作触发一系列的页分裂和重组，这会对性能造成不良的影响（直到数据再次达到新的稳定状态）。

总结

在 MySQL 中，在大多数情况下都会使用 B-Tree 索引。其他类型的索引大多只适用于特殊的目的。如果在合适的场景中使用索引，将大大提高查询的响应时间。

在选择索引和编写利用这些索引的查询时，有三个原则始终需要记住：

1. 单行访问是很慢的。
2. 按顺序访问范围数据是很快的，这有两个原因。第一，顺序 I/O 不需要多次磁盘寻道，所以比随机 I/O 要快很多（特别是对机械硬盘）。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且 GROUP BY 也查询也无需再做排序和将行按组进行聚合计算了。
3. 索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问，而上面的第 1 点已经写明单行访问时很慢的。

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

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

那如何判断一个系统创建的索引是合理的呢？

一般来说，我们建议按响应时间来对查询进行分析。找出那些消耗最长时间的查询或者那些给服务器带来最大压力的查询，然后检查这些查询的 schema、SQL 和索引结构，判断是否有查询扫描了太多的行，是否做了很多额外的排序或者使用了临时表，是否使用随机 I/O 访问数据，或者是否有太多回表查询那些不在索引中的列的操作。

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