order by如何工作

在你开发应用的时候，一定会经常碰到需要根据指定的字段排序来显示结果的需求。
还是以我们前面举例用过的市民表为例，假设你要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前 1000 个人的姓名、年龄。

假设这个表的部分定义是这样的：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

这时，你的 SQL 语句可以这么写：

SELECT city, name, age FROM t WHERE city='杭州' ORDER BY name LIMIT 1000;

这个语句看上去逻辑很清晰，但是你了解它的执行流程吗？现在来聊聊这个语句是怎么执行的，以及有什么参数会影响执行的行为。

全字段排序

为避免全表扫描，我们需要在 city 字段加上索引。

在 city 字段上创建索引之后，我们用 explain 命令来看看这个语句的执行情况。

图 1 使用 explain 命令查看语句的执行情况

Extra 这个字段中的“Using filesort”表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

为了说明这个 SQL 查询语句的执行过程，我们先来看一下 city 这个索引的示意图。

图 2 city 字段的索引示意图

从图中可以看到，满足 city='杭州’条件的行，是从 ID_X 到 ID_(X+N) 的这些记录。

通常情况下，这个语句执行流程如下所示 ：

1. 初始化 sort_buffer，确定放入 name、city、age 这三个字段；
2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
3. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
4. 从索引 city 取下一个记录的主键 id；
5. 重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
6. 对 sort_buffer 中的数据按照字段 name 做快速排序；
7. 按照排序结果取前 1000 行返回给客户端。

我们暂且把这个排序过程，称为全字段排序，执行流程的示意图如下所示:

图 3 全字段排序

图中“按 name 排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。

sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。
但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

你可以用下面介绍的方法，来确定一个排序语句是否使用了临时文件。

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

这个方法是通过查看 OPTIMIZER_TRACE 的结果来确认的，你可以从 number_of_tmp_files 中看到是否使用了临时文件。

图 4 全排序的 OPTIMIZER_TRACE 部分结果

number_of_tmp_files 表示的是，排序过程中使用的临时文件数。你一定奇怪，为什么需要 12 个文件？内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。
可以这么简单理解，MySQL 将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件。

如果 sort_buffer_size 超过了需要排序的数据量的大小，number_of_tmp_files 就是 0，表示排序可以直接在内存中完成。

否则就需要放在临时文件中排序。sort_buffer_size 越小，需要分成的份数越多，number_of_tmp_files 的值就越大。

接下来，我再和你解释一下图 4 中其他两个值的意思。

我们的示例表中有 4000 条满足 city='杭州’的记录，所以你可以看到 examined_rows=4000，表示参与排序的行数是 4000 行。

sort_mode 里面的 packed_additional_fields 的意思是，排序过程对字符串做了“紧凑”处理。即使 name 字段的定义是 varchar(16)，在排序过程中还是要按照实际长度来分配空间的。

同时，最后一个查询语句 select @b-@a 的返回结果是 4000，表示整个执行过程只扫描了 4000 行。

这里需要注意的是，为了避免对结论造成干扰，我把 internal_tmp_disk_storage_engine 设置成 MyISAM。否则，select @b-@a 的结果会显示为 4001。

这是因为查询 OPTIMIZER_TRACE 这个表时，需要用到临时表，而 internal_tmp_disk_storage_engine 的默认值是 InnoDB。
如果使用的是 InnoDB 引擎的话，把数据从临时表取出来的时候，会让 Innodb_rows_read 的值加 1。

rowid 排序

在上面这个算法过程里面，只对原表的数据读了一遍，剩下的操作都是在 sort_buffer 和临时文件中执行的。
但这个算法有一个问题，就是如果查询要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。

所以如果单行很大，这个方法效率不够好。

那么，如果 MySQL 认为排序的单行长度太大会怎么做呢？

接下来，我来修改一个参数，让 MySQL 采用另外一种算法。

SET max_length_for_sort_data = 16;

max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。
它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。

city、name、age 这三个字段的定义总长度是 36，我把 max_length_for_sort_data 设置为 16，我们再来看看计算过程有什么改变。

新的算法放入 sort_buffer 的字段，只有要排序的列（即 name 字段）和主键 id。

但这时，排序的结果就因为少了 city 和 age 字段的值，不能直接返回了，整个执行流程就变成如下所示的样子：

1. 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
3. 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
4. 从索引 city 取下一个记录的主键 id；
5. 重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；
6. 对 sort_buffer 中的数据按照字段 name 进行排序；
7. 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

这个执行流程的示意图如下，我把它称为 rowid 排序。

图 5 rowid 排序

对比图 3 的全字段排序流程图你会发现，rowid 排序多访问了一次表 t 的主键索引，就是步骤 7。

需要说明的是，最后的“结果集”是一个逻辑概念，实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id，然后到原表查到 city、name 和 age 这三个字段的结果，不需要在服务端再耗费内存存储结果，是直接返回给客户端的。

根据这个说明过程和图示，你可以想一下，这个时候执行 select @b-@a，结果会是多少呢？

现在，我们就来看看结果有什么不同。

首先，图中的 examined_rows 的值还是 4000，表示用于排序的数据是 4000 行。但是 select @b-@a 这个语句的值变成 5000 了。

因为这时候除了排序过程外，在排序完成后，还要根据 id 去原表取值。由于语句是 limit 1000，因此会多读 1000 行。

图 6 rowid 排序的 OPTIMIZER_TRACE 部分输出

从 OPTIMIZER_TRACE 的结果中，你还能看到另外两个信息也变了。

• sort_mode 变成了 ，表示参与排序的只有 name 和 id 这两个字段。
• number_of_tmp_files 变成 10 了，是因为这时候参与排序的行数虽然仍然是 4000 行，但是每一行都变小了，因此需要排序的总数据量就变小了，需要的临时文件也相应地变少了。

全字段排序 VS rowid 排序

我们来分析一下，从这两个执行流程里，还能得出什么结论。

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

这个结论看上去有点废话的感觉，但是你要记住它

看到这里，你就了解了，MySQL 做排序是一个成本比较高的操作。
那么你会问，是不是所有的 order by 都需要排序操作呢？
如果不排序就能得到正确的结果，那对系统的消耗会小很多，语句的执行时间也会变得更短。

其实，并不是所有的 order by 语句，都需要排序操作的。
从上面分析的执行过程，我们可以看到，MySQL 之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。

你可以设想下，如果能够保证从 city 这个索引上取出来的行，天然就是按照 name 递增排序的话，是不是就可以不用再排序了呢？

确实是这样的。

所以，我们可以在这个市民表上创建一个 city 和 name 的联合索引，对应的 SQL 语句是：

alter table t add index city_user(city, name);

作为与 city 索引的对比，我们来看看这个索引的示意图。

图 7 city 和 name 联合索引示意图

在这个索引里面，我们依然可以用树搜索的方式定位到第一个满足 city='杭州’的记录，并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要 city 的值是杭州，name 的值就一定是有序的。

这样整个查询过程的流程就变成了：

1. 从索引 (city,name) 找到第一个满足 city='杭州’条件的主键 id；
2. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；
3. 从索引 (city,name) 取下一个记录主键 id；
4. 重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。

图 8 引入 (city,name) 联合索引后，查询语句的执行计划

可以看到，这个查询过程不需要临时表，也不需要排序。接下来，我们用 explain 的结果来印证一下。

图 9 引入 (city,name) 联合索引后，查询语句的执行计划

从图中可以看到，Extra 字段中没有 Using filesort 了，也就是不需要排序了。
而且由于 (city,name) 这个联合索引本身有序，所以这个查询也不用把 4000 行全都读一遍，只要找到满足条件的前 1000 条记录就可以退出了。
也就是说，在我们这个例子里，只需要扫描 1000 次。

既然说到这里了，我们再往前讨论，这个语句的执行流程有没有可能进一步简化呢？

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。

按照覆盖索引的概念，我们可以再优化一下这个查询语句的执行流程。

针对这个查询，我们可以创建一个 city、name 和 age 的联合索引，对应的 SQL 语句就是：

alter table t add index city_user_age(city, name, age);

这时，对于 city 字段的值相同的行来说，还是按照 name 字段的值递增排序的，此时的查询语句也就不再需要排序了。
这样整个查询语句的执行流程就变成了：

1. 从索引 (city,name,age) 找到第一个满足 city='杭州’条件的记录，取出其中的 city、name 和 age 这三个字段的值，作为结果集的一部分直接返回；
2. 从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
3. 重复执行步骤 2，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。

图 10 引入 (city,name,age) 联合索引后，查询语句的执行流程

然后，我们再来看看 explain 的结果。

图 11 引入 (city,name,age) 联合索引后，查询语句的执行计划

可以看到，Extra 字段里面多了“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 的索引

(rental_date, inventory_id, customer_id):
CREATE TABLE renta (
    ...
    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\G

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;

下面这个查询也没问题，因为 ORDER BY 使用的两列就是索引的最左前缀:

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

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

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

... 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;

• 下面这个查询的 WHERE 和 ORDER BY 中的列无法组合成索引的最左前缀:

... WHERE rental_date = '2005-05-25' ORDER BY customer_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 表当作关联的第二张表，所以实际上无法使用索引:

使用索引做排序的一个最重要的用法是当查询同时有 ORDER BY 和 LIMIT 子句的时候

冗余和重复索引

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 的唯一限制和主键限制都是通过索引实现的，因此，上面的写法实际上在相同的列上创建了三个重复的索引。
通常没有理由这样做，除非是在同一列上创建不同类型的索引来满足不同的查询需求(如果索引类型不同，并不算是重复索引。例如经常有很好的理由创建 KEY(col) 和 FULLTEXTKEY(col) 两种索引)

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

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

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

例如，如果在整数列上有一个索引，现在需要额外增加一个很长的 VARCHAR 列来扩展该索引，那性能可能会急剧下降。
特别是有查询把这个索引当作覆盖索引，或者这是 MyISAM 表并且有很多范围查询(由于 MyISAM 的前缀压缩)的时候

考虑一下 userinfo 表。这个表有 1000000 行，对每个 state_id 值大概有 20000 条记录。
在 state_id 列有一个索引对下面的查询有用，假设查询名为 Q1:

SELECT count(*) FROM userinfo WHERE state_id=5;

一个简单的测试表明该查询的执行速度大概是每秒 115 次(QPS)。
还有一个相关查询需要检索几个列的值，而不是只统计行数，假设名为 Q2:

SELECT state_id, city, address FROM userinfo WHERE state_id=5;

对于这个查询，测试结果 QPS 小于 10. 提升该查询性能的最简单办法就是扩展索引为 (state_id, city, address)，让索引能覆盖查询

ALTER TABLE userinfo DROP KEY state_id, ADD KEY state_id_2 (state_id, city, address);

索引扩展后，Q2 运行得更快了，但是 Q1 却变慢了。
如果我们想让两个查询都变得更快，就需要两个索引，尽管这样以来原来的单列索引是冗余的了。

下表显示这两个查询在不同的索引策略下的详细结果，分别使用 MyISAM 和 InnoDB 存储引擎。
注意到只有 state_id_2 索引时，InnoDB 引擎上的查询 Q1 的性能下降并不明显，这是因为 InnoDB 没有使用索引压缩。

	只有 state_id	只有 state_id_2	同时有 state_id 和 state_id_2
MyISAM, Q1	114.96	25.40	112.19
MyISAM, Q2	9.97	16.34	16.37
InnoDB, Q1	108.55	100.33	107.97
InnoDB, Q2	12.12	28.04	28.06

有两个索引的缺点时索引成本更高。
下表显示了向表中插入 100 万行数据所需要的时间

	只有 state_id	同时有 state_id 和 state_id_2
InnoDB, 对两个索引都有足够的内容	80 秒	136 秒
MyISAM，只有一个索引有足够的内容	72 秒	470 秒

可以看到，表中的索引越多插入速度会越慢。
一般来说，增加新索引将会导致 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 子句就无法使用该索引做排序，而只能用文件排序了。
所以，建议使用 Precona 工具箱中的 pt-uptrade 工具来仔细检查计划中的索引变更。

未使用的索引

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

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

索引案例学习

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

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

出人意料的是第一件需要考虑的事情是需要使用索引来排序，还是先检索数据再排序。使用索引排序会严格限制索引和查询的设计。
例如，如果希望使用索引做根据其他会员对用户的评分的排序，则 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" 来描述这两种情况。

EXPLAIN SELECT actor_id FROM sakila.actor
WHERE actor_id > 45\G

id: 1
select_type: SIMPLE
table: actor
typeL range

但是下面这条查询呢？

EXPLAIN SELECT actor_id FROM sakila.actor
WHERE actor_id IN(1,4,99)\G

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 子句，但不加入到索引中。

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

在这个案例中，优化器的特性是影响索引策略的一个很重要的因素。
如果未来版本的 MySQL 能够实现松散索引扫描，就能在一个索引上使用多个范围条件，那也就不需要为上面考虑的这类查询使用 IN() 列表了

优化排序

使用文件排序对小数据集时很快的，但如果一个查询匹配的结果有上百万行的话会怎样？
例如如果 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>);

总结

索引时一个非常复杂的话题！
MySQL 和存储引擎访问数据的方式，加上索引的特性，使得索引成为一个影响数据访问的有力而灵活的工作(无论数据时再磁盘中还是在内存中)

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

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

1. 单行访问是很慢的。特别是在机械硬盘存储中(SSD 的随机 I/O 要快很多，不过这一点仍然成立)。
   如果服务器从存储中读取一个数据块知识为了获取其中一行，那么就浪费了很多工作。
   最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率
2. 按顺序访问范围数据时很快的，这有两个原因。第一，顺序 I/O 不需要多次磁盘寻道，所以比随机 I/O 要快很多(特别是对机械硬盘)。
   第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且 GROUP BY 查询也无须再做排序和将行按组进行聚合计算了。
3. 索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。
   这避免了大量的单行访问，而上面的第 1 点已经写明单行访问时很慢的

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

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

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

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

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

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