本文的原文地址在此: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)做出了调整。这能帮助优化器做出更好的决定:例如在高速存储上,随机读取的时间要远远短于常规机械硬盘。