在优化阶段index dive会帮助选择到底使用哪一个索引。现在当指定了force index，优化器仍然通过index dive进行代价估算。在某些情况下可以避免index dive，这样就能提升执行时的速度。
在MySQL 8.0.3 因为 WL#6526 优化器在使用了force index时仍然跳过index dive。
什么是index dive？
Jorgen’s说：
“在MySQL里只要存在范围查找方法，就可以通过下钻索引来估计范围内的行数，方法是找出范围的开始和结束，并计算出他们之间的行数。这项技术更精确，所以也是制定良好执行计划的一个基础。”
下面的语句，将会为每个range查询执行两次index dive（每次都在range里查找最大最小值）
SELECT * FROM t1 WHERE (c1 > 1 AND c1 < 10) OR (c1 > 10 AND c1 < 20) ;
SELECT * FROM t1 WHERE c1 IN (11, 22) ;当有force index时index dive可以被跳过，因为索引的选择已经被提前决定，查询方法依赖于where条件（ref-access只在简单等值查询时才会被用到，其他情况都是选择的范围查找）。
优化器在下面的情况会跳过index dive：
- 查询时只访问一张表
- force index(某个索引)
- 没有子查询
- 没有涉及全文索引
- 没有GROUP-BY or DISTINCT
- 没有ORDER-BY
这种优化目前并不适用于多表查询。
如何确定查询是否使用该优化
使用EXPLAIN
- EXPLAIN FORMAT=TRADITIONAL FOR CONNECTION 将会看到下面的变化:
- 当index dive被跳过后对应表的行数会变成NULL
- “filtered”的值显示NULL
- EXPLAIN FORMAT=JSON FOR CONNECTION将会看到下面的变化:
- 当index dive被跳过后“rows_examined_per_scan” and “rows_produced_per_join” 不会被提及
- 当index dive被跳过后对应表的行数会变成NULL
- “filtered”的值显示NULL.
- EXPLAIN FORMAT=TRADITIONAL and EXPLAIN FORMAT=JSON的展现没有改变
使用Optimizer trace
Optimizer trace包含“skipped_due_to_force_index”.
"range_scan_alternatives": [
{
"index": "c1_idx",
"ranges": [
"1 < c1 < 10",
"10 < c1 < 20"
],
"index_dives_for_range_access": "skipped_due_to_force_index",
"rowid_ordered": false,
"using_mrr": false,
"index_only": true,
"rows": "not applicable",
"cost": "not applicable",
"chosen": true
}
],那么什么样的语句将会从该优化中受益呢？
- 包含很多IN 或者OR的查询
- 范围条件里面包含很多行的查询
举个例子，第一条语句没有用FORCE INDEX ，那么该优化不会起作用。第二条语句加了FORCE INDEX，将会看到在执行时，“statistics”阶段花了很少的时间。
mysql> TRUNCATE TABLE performance_schema.events_stages_history;
Query OK, 0 rows affected (0.00 sec)
mysql> TRUNCATE TABLE performance_schema.events_statements_history ;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT c1 FROM t1 WHERE ( c1 > 10 AND c1 < 11) OR ... <200 clauses>;
+------+
| c1 |
+------+
| 25 |
<more rows>
+------+
832 rows in set (0.02 sec)
mysql>
mysql> SELECT c1 FROM t1 FORCE INDEX(c1_idx) WHERE ( c1 > 10 AND c1 < 11) OR ... <200 clauses>;
+------+
| c1 |
+------+
| 25 |
<more rows>
+------+
832 rows in set (0.01 sec)
mysql>
mysql> select SQL_TEXT, stages.EVENT_NAME, stages.TIMER_WAIT/1000000000 "Time (ms)"
-> from performance_schema.events_stages_history AS stages JOIN performance_schema.events_statements_history AS statements
-> ON (stages.EVENT_ID > statements.EVENT_ID AND stages.EVENT_ID <= statements.END_EVENT_ID)
-> WHERE stages.EVENT_NAME LIKE '%statistics%' AND SQL_TEXT LIKE '%SELECT c1 FROM t1%' ;
+-------------------------------------------------------------------------+----------------------+------------+
| SQL_TEXT | EVENT_NAME | Time (ms) |
+-------------------------------------------------------------------------+----------------------+------------+
| SELECT c1 FROM t1 WHERE ( c1 > 10 AND c1 < 11) ... | stage/sql/statistics | 29.5890 |
| SELECT c1 FROM t1 FORCE INDEX(c1_idx) WHERE ( c1 > 10 AND c1 < 11) ... | stage/sql/statistics | 2.3026 |
+-------------------------------------------------------------------------+----------------------+------------+
2 rows in set (0.00 sec)
mysql>这一特性来自Facebook.
从MySQL 8.0.3该优化将会被默认使用。你不用启动任何设置或者跑任何命令，欢迎试用。
翻译自：
https://mysqlserverteam.com/optimization-to-skip-index-dives-with-force-index/
http://jorgenloland.blogspot.com/2012/04/on-queries-with-many-values-in-in.html
