MySQL 8.0.3加force index优化跳过index dive

2021-07-21 06:13:01

在优化阶段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

码农公寓