多变的执行计划 - 非官方 MySQL 8.0 优化指南 - 学
2019-04-02 本文已影响3人
mokou591
有时看起来相似的查询会有大不相同的执行计划。我们通过修改人口条件已经看到：在例子8中，多于5亿 和 多于5百万会导致索引选择的不同。
这在生产环境中经常发生，查询的一部分是从用户输入中产生的：
- 在几个月的数据中查询日期范围“昨天到现在”很可能可以用到日期列的索引。而查询“去年到现在”就不会。
-
WHERE is_deleted=1能够过滤掉许多记录，而且很适合用于索引。而相似的WHERE is_deleted=0可能就无法用到索引。
这是合乎预期的，是已有的上下文环境（元数据和统计信息）造成的结果。下面的表格展示了随着“人口”和“大洲”的查询条件改变，查询代价的变化。
| p>5百万 c=亚洲 |
p>5百万 c=南极洲 |
p>5千万 c=亚洲 |
p>5千万 c=南极洲 |
p>5亿 c=亚洲 |
p>5亿 c=南极洲 |
|
|---|---|---|---|---|---|---|
| p | 152.21 | 152.21 | 34.61 | 34.61 | 3.81 | 3.81 |
| c | 28.20 | 6.00 | 28.20 | 6.00 | 28.20 | 6.00 |
| c,p | 24.83 | 2.41 | 16.41 | 2.41 | 3.81 | 2.41 |
| p,c | 152.21 | 152.21 | 34.61 | 34.61 | 3.81 | 3.81 |
| 全表扫描 | 53.80 | 53.80 | 53.80 | 53.80 | 53.80 | 53.80 |
- 全表扫描有相对固定的查询代价，因为它只是查看表里的每一行。
- 使用 p 或 c 索引的查询代价会随着查询值的过滤能力而改变（南极洲的国家较少；人口很多的国家较少）。
- 复合索引（c,p）在减少查询代价上相对更有效。
- 使用的数据集中，南极洲有 4 个人口为 0 的国家。复合索引（c,p）在南极洲的查询中展现了极低的的查询代价，因为只有 0 条结果。
这些信息也可以通过可视化展示。注意在大洲上的全表扫描和索引访问都有固定的代价，而当人口条件选择性不高时，复合索引（c,p）和索引c的代价相同。
如果复合索引不是非常有效，你会发现和使用单列索引的结果非常相似。最后，因为人口多于 3 亿的国家都在亚洲，没有哪一列的选择性比人口更好了。
下方的信息图源于强制使用人口索引，并设置人口查询值从1百万到5亿，取100次执行的中位数。执行的时间是从performance_schema.events_statements_history_long中取出并转为微秒（μs）的。有一些执行时间的数据点聚集起来了，我认为是因为数据集比较小，并且有少量只有很少人口的国家，这些数据可以从同一个页访问到。
译自：
Transient Plans - The Unofficial MySQL 8.0 Optimizer Guide
