MySQL 8.0.3加force index优化跳过index dive

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

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

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
23天前
|
关系型数据库 MySQL Linux
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
|
26天前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
18天前
|
缓存 算法 关系型数据库
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
MySQL底层概述—8.JOIN排序索引优化
|
19天前
|
SQL 关系型数据库 MySQL
MySQL底层概述—7.优化原则及慢查询
本文主要介绍了:Explain概述、Explain详解、索引优化数据准备、索引优化原则详解、慢查询设置与测试、慢查询SQL优化思路
MySQL底层概述—7.优化原则及慢查询
|
19天前
|
存储 缓存 关系型数据库
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
|
10天前
|
监控 关系型数据库 MySQL
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时,优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略,可以显著提高查询效率。本文介绍的技巧和方法,可以为开发人员在数据处理和查询优化中提供有效的指导,提升系统的性能和用户体验。掌握这些技巧后,您可以在处理海量数据时更加游刃有余。
41 9
|
4天前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
17 2
|
21天前
|
关系型数据库 MySQL 数据库
从MySQL优化到脑力健康:技术人与效率的双重提升
聊到效率这个事,大家应该都挺有感触的吧。 不管是技术优化还是个人状态调整,怎么能更快、更省力地完成事情,都是我们每天要琢磨的事。
61 23
|
9天前
|
关系型数据库 MySQL
图解MySQL【日志】——磁盘 I/O 次数过高时优化的办法
当 MySQL 磁盘 I/O 次数过高时,可通过调整参数优化。控制刷盘时机以降低频率:组提交参数 `binlog_group_commit_sync_delay` 和 `binlog_group_commit_sync_no_delay_count` 调整等待时间和事务数量;`sync_binlog=N` 设置 write 和 fsync 频率,`innodb_flush_log_at_trx_commit=2` 使提交时只写入 Redo Log 文件,由 OS 择机持久化,但两者在 OS 崩溃时有丢失数据风险。
21 3
|
21天前
|
SQL 关系型数据库 MySQL
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。