* SELECT COUNT 会造成全表扫描？回去等通知吧( 二 ) _SELECT

-- 使用覆盖索引执行时间: 2.0 ms
SELECT create_time FROM person force index(create_time) WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'
从实际执行的效果看使用覆盖索引查询比使用全表扫描执行的时间快了一倍！说明 MySQL 在查询前做的成本估算不准！我们先来看看 MySQL 做全表扫描的成本有多少。
前面我们说了成本主要 IO 成本和 CPU 成本有关，对于全表扫描来说也就是分别和聚簇索引占用的页面数和表中的记录数。执行以下命令：
SHOW TABLE STATUS LIKE 'person'

文章插图
可以发现：

行数是 100264，我们不是插入了 10 w 行的数据了吗，怎么算出的数据反而多了，其实这里的计算是估算，也有可能这里的行数统计出来比 10 w 少了，估算方式有兴趣大家去网上查找，这里不是本文重点，就不展开了。得知行数，那我们知道 CPU 成本是 100264 * 0.2 = 20052.8 。
数据长度是 5783552，InnoDB 每个页面的大小是 16 KB，可以算出页面数量是 353 。

也就是说全表扫描的成本是 20052.8 + 353 = 20406 。
这个结果对不对呢，我们可以用一个工具验证一下。在 MySQL 5.6 及之后的版本中，我们可以用 optimizer trace 功能来查看优化器生成计划的整个过程，它列出了选择每个索引的执行计划成本以及最终的选择结果，我们可以依赖这些信息来进一步优化我们的 SQL 。
optimizer_trace 功能使用如下：
SET optimizer_trace="enabled=on";
SELECT create_time FROM person WHERE NAME >'name84059' AND create_time > '2020-05-23 14:39:18';
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";
执行之后我们主要观察使用 name_score ， create_time 索引及全表扫描的成本。
先来看下使用 name_score 索引执行的的预估执行成本:
{
"index": "name_score",
"ranges": [
"name84059 <= name"
],
"index_dives_for_eq_ranges": true,
"rows": 25372,
"cost": 30447
}
可以看到执行成本为 30447，高于我们之前算出来的全表扫描成本：20406 。所以没选择此索引执行。
注意：这里的 30447 是查询二级索引的 IO 成本和 CPU 成本之和，再加上回表查询聚簇索引的 IO 成本和 CPU 成本之和。
再来看下使用 create_time 索引执行的的预估执行成本：
{
"index": "create_time",
"ranges": [
"0x5ec8c516 < create_time"
],
"index_dives_for_eq_ranges": true,
"rows": 50132,
"cost": 60159,
"cause": "cost"
}
可以看到成本是 60159 ，远大于全表扫描成本 20406，自然也没选择此索引。
再来看计算出的全表扫描成本：
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`person`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 100264,
"access_type": "scan",
"resulting_rows": 100264,
"cost": 20406,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 100264,
"cost_for_plan": 20406,
"chosen": true
}
]
}
注意看 cost：20406，与我们之前算出来的完全一样！这个值在以上三者算出的执行成本中最小，所以最终 MySQL 选择了用全表扫描的方式来执行此 SQL 。
实际上 optimizer trace 详细列出了覆盖索引，回表的成本统计情况，有兴趣的可以去研究一下。
从以上分析可以看出，MySQL 选择的执行计划未必是最佳的，原因有挺多，就比如上文说的行数统计信息不准，再比如 MySQL 认为的最优跟我们认为不一样，我们可以认为执行时间短的是最优的，但 MySQL 认为的成本小未必意味着执行时间短。
总结
本文通过一个例子深入剖析了 MySQL 的执行计划是如何选择的，以及为什么它的选择未必是我们认为的最优的，这也提醒我们，在生产中如果有多个索引的情况，使用 WHERE 进行过滤未必会选中你认为的索引，我们可以提前使用 EXPLAIN,optimizer trace 来优化我们的查询语句。
来源丨公众号：程序员大彬（ID：gh_cd7e6f4ba41d）