一个具有挑战性的单表查询

本文的原文地址在此：https://www.percona.com/blog/2015/05/08/mysql-indexing-101-a-challenging-single-table-query/，以下是译文。

-----------------------------------------------------------这是一条分割线-----------------------------------------------------------

在之前的一篇文章《MySQL单表查询优化技巧》（英文原文地址，中文我自己翻译地址），我们讨论了不同情况下单表查询的应该如何创建索引。下面我们介绍一个在真实世界中会遇到的查询优化问题：两个相似的查询语句，一个是全表查询，另一个却使用了索引。是数据库的bug还是意料之中？请继续阅读！

两个相似的查询语句（Our two similar queries）

# Q1
mysql> explain select col1, col2 from t where ts >= '2015-04-30 00:00:00';
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | t             | ALL  | ts            | NULL | NULL    | NULL | 4111896 | Using where |
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+

# Q2
mysql> explain select count(*) from t where ts >='2015-04-30 00:00:00';
+----+-------------+---------------+-------+---------------+--------------+---------+------+---------+--------------------------+
| id | select_type | table         | type  | possible_keys | key          | key_len | ref  | rows    | Extra                    |
+----+-------------+---------------+-------+---------------+--------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | t             | range | ts            | ts           | 5       | NULL | 1809458 | Using where; Using index |
+----+-------------+---------------+-------+---------------+--------------+---------+------+---------+--------------------------+

上面两个查询语句，Q1是全表查询，Q2使用了索引ts，而且还是覆盖索引（Extra中显示的是Using index）。为什么这两个查询语句的会有这么大的差别？

让我们看看Q1执行时发生了什么。

这是包含一个不等于查询的语句，并且在ts列建有索引。优化器会尝试ts索引是否可用（possible_keys），迄今为止没什么异常。让我们看看Q1和Q2的rows列，Q2的扫描行数约为Q1的45%（1809458/4111896），虽然还不足以令人兴奋，但还是比全表扫描快，不是么？

如果你这么想，那请你仔细阅读本文下面的部分。因为刚才这种想法是错误的。

简单估算执行计划的消耗（Estimating the cost of an execution plan (simplified)）

首先，优化器并不知道数据或索引是存储在内存里还是硬盘上，它简单的认为所有的数据和索引都在硬盘上。优化器唯一知道的是顺序读取比随机读取要快得多。

那么我们来执行一下Q1。第一步，优化器根据索引确定有180万行数据符合要求（这是顺序读取，因此非常快）。然而，第二步是取出这180万行数据的col1和col2两列。当前索引仅能获得主键值，根据主键值再获得符合条件的所有记录，因此我们不得不借助主键再重新查找这180万条数据。

那么问题来了：第二步查找180万条数据是随机读取的方式，因此这会消耗非常多的时间。比顺序读取的全表扫描所花的时间还要多得多。

现在来看看Q2。第一步与Q1完全一样。但不同的是，Q2没有第二步！这也是为什么我们把这个索引称为“覆盖索引”的原因：我们不需要再通过主键进行二次查找来获得其他字段。因此这一次使用ts索引将比全表扫描快得多。

另外你还要明白的一点是：虽然全表扫描在逻辑上是顺序操作，但是由于硬盘上的页（InnoDB pages）不可能是顺序存储。因此在硬盘层面，全表扫描绝大部分情况都是随机读取。

不过即使是这样，全表扫描还是比海量的点查询（point query）要快，这也比较容易理解：当你进行全表扫描时，一个数据页（InnoDB默认为16KB）内的所有数据都会被完全利用。但是当你进行点查询时，很可能只是用了整个16KB数据页内的一条数据。因此最糟糕的情况是，读取180万条数据要进行180万次随机读取，而全表扫描400万条数据仅仅需要10万次随机读取。全表扫描依然要快一个数量级。

优化我们的查询（Optimizing our query）

现在我们已经知道为什么Q1为执行全表扫描，那么有没有什么办法让Q1使用索引呢？如果我们创建一个覆盖索引，我们就可以避免耗时很多的主键二次查询。要创建的索引如下：

ALTER TABLE t ADD INDEX idx_ts_col1_col2 (ts, col1, col2);

你可能会说ts列的查询条件是不等于，这时它会阻止优化器调用其他列的索引。这个说法确实没有错，但是只有where中包含col1或col2列查询时才会被阻止。而现在这种情况则不会。

结论（Conclusion）

对于优化查询（即使是很简单的查询）来说，了解索引是如何进行过滤、排序以及覆盖是至关重要的。SQL语句的执行计划，对于理解（或大概理解）优化器是如何运作非常有用。否则，你将对优化器做出的决定感到费解。

另外要注意的是，MySQL 5.7对优化器的消耗模型（cost model）做出了调整。这能帮助优化器做出更好的决定：例如在高速存储上，随机读取的时间要远远短于常规机械硬盘。