开发者社区> 漫天花雨> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MySQL 8.0.3加force index优化跳过index dive

简介:
+关注继续查看

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【C#】【MySQL】【GridView】删除出现Parameter index is out of range
【C#】【MySQL】【GridView】删除出现Parameter index is out of range
0 0
MySQL 覆盖索引(Cover Index)
MySQL 覆盖索引(Cover Index)
0 0
【实施工程师之家】——mysql四种索引PRIMARY(主键索引)、INDEX(一般索引)、UNIQUE(非空索引)、FULLTEXT(全文索引)应用
【实施工程师之家】——mysql四种索引PRIMARY(主键索引)、INDEX(一般索引)、UNIQUE(非空索引)、FULLTEXT(全文索引)应用
0 0
mysql 索引(index)
mysql 索引(index)
0 0
mysql index cond push down 索引下推
mysql 索引下推在8.0的代码中进行了重构,目前的逻辑比较清晰。本文对该代码进行相关的分析。本文介绍的代码为mysql-8.0.21版本。
0 0
[MySQL] 联合索引与using index condition
[MySQL] 联合索引与using index condition1.测试联合索引的最左原则的时候, 发现了5.6版本后的新特性Index Condition Pushdown 2.含义就是存储引擎层根据索引尽可能的过滤数据,然后在返回给服务器层根据where其他条件进行过滤 3.
1494 0
MySQL Index Extensions
Index Extensions含义 MySQL5.6开始 InnoDB可以通过主键自动扩展二级索引的功能称为Index Extensions,即二级索引除了存储本列索引的key值外,还存储着主键列key值。
858 0
MySQL · myrocks · clustered index特性
Cluster index介绍 最近在RDS MyRocks中,我们引入了一个重要功能,二级聚集索引(secondary clustering index). 我们知道innodb和rocksdb引擎的主键就是clustered index。
1296 0
Linux安装Mysql(图文解说详细版,安装包tar包版)
Linux安装Mysql(图文解说详细版,安装包tar包版)
0 0
+关注
漫天花雨
支持阿里云数据库,希望能为更多云用户服务;之前支持阿里巴巴集团数据库,承担了业务的数据库设计,架构优化,异地多活,库存热点优化,稳定性保障等工作,经历数次双11,有丰富的实战和优化经验和对业务的敏感度。关注数据库、数据仓库、大数据等领域。
文章
问答
文章排行榜
最热
最新
相关电子书
更多
让 MySQL 原生分布式触手可及
立即下载
好的 MySQL 兼容可以做到什么程度
立即下载
云数据库RDS MySQL从入门到高阶
立即下载