MySQL · 捉虫动态 · order by limit 造成优化器选择索引错误

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: 问题描述 bug 触发条件如下: 优化器先选择了 where 条件中字段的索引,该索引过滤性较好; SQL 中必须有 order by limit 从而引导优化器尝试使用 order by 字段上的索引进行优化,最终因代价问题没有成功。 复现case 表结构 create table t

问题描述

bug 触发条件如下:

  1. 优化器先选择了 where 条件中字段的索引,该索引过滤性较好;
  2. SQL 中必须有 order by limit 从而引导优化器尝试使用 order by 字段上的索引进行优化,最终因代价问题没有成功。

复现case

表结构

create table t1(
  	id int auto_increment primary key,
  	a int, b int, c int,
  	key iabc (a, b, c),
  	key ic (c)
) engine = innodb;
AI 代码解读

构造数据

insert into t1 select null,null,null,null;
insert into t1 select null,null,null,null from t1;
insert into t1 select null,null,null,null from t1;
insert into t1 select null,null,null,null from t1;
insert into t1 select null,null,null,null from t1;
insert into t1 select null,null,null,null from t1;
update t1 set a = id / 2, b = id / 4, c = 6 - id / 8;
AI 代码解读

触发SQL

mysql> explain select id from t1 where a<3 and b in (1, 13) and c>=3 order by c limit 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: iabc,ic
          key: iabc
      key_len: 15
          ref: NULL
         rows: 32
         Extra: Using where; Using index; Using filesort
AI 代码解读

使用 force index 可以选择过滤性好的索引

mysql> explain select id from t1 force index(iabc) where a<3 and b in (1, 13) and c>=3 order by c limit 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: iabc
          key: iabc
      key_len: 5
          ref: NULL
         rows: 3
        Extra: Using where; Using index; Using filesort
AI 代码解读

问题分析

optimizer_trace 可以帮助分析这个问题。

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G

                "range_scan_alternatives": [
                  {
                    "index": "iabc",
                    "ranges": [
                      "NULL < a < 3"
                    ],
                    "index_dives_for_eq_ranges": true,
                    "rowid_ordered": false,
                    "using_mrr": false,
                    "index_only": true,
                    "rows": 3,
                    "cost": 1.6146,
                    "chosen": true
                  },
                  {
                    "index": "ic",
                    "ranges": [
                      "3 <= c"
                    ],
                    "index_dives_for_eq_ranges": true,
                    "rowid_ordered": false,
                    "using_mrr": false,
                    "index_only": false,
                    "rows": 17,
                    "cost": 21.41,
                    "chosen": false,
                    "cause": "cost"
                  }
                ],
AI 代码解读

range_scan_alternatives 计算 range_scan,各个索引的开销,从上面的结果可以看出,联合索引 iabc 开销较小,应该选择 iabc。

        "considered_execution_plans": [
          {
            "plan_prefix": [
            ],
            "table": "`t1`",
            "best_access_path": {
              "considered_access_paths": [
                {
                  "access_type": "range",
                  "rows": 3,
                  "cost": 2.2146,
                  "chosen": true
                }
              ]
            },
            "cost_for_plan": 2.2146,
            "rows_for_plan": 3,
            "chosen": true
          }
        ]
AI 代码解读

considered_execution_plans 表索引选择过程,access_type 是 range,rows_for_plan=3,到这里为止,执行计划还是符合预期的。

      {
        "clause_processing": {
          "clause": "ORDER BY",
          "original_clause": "`t1`.`c`",
          "items": [
            {
              "item": "`t1`.`c`"
            }
          ],
          "resulting_clause_is_simple": true,
          "resulting_clause": "`t1`.`c`"
        }
      },
      {
        "refine_plan": [
          {
            "table": "`t1`",
            "access_type": "index_scan"
          }
        ]
      },
      {
        "reconsidering_access_paths_for_index_ordering": {
          "clause": "ORDER BY",
          "index_order_summary": {
            "table": "`t1`",
            "index_provides_order": false,
            "order_direction": "undefined",
            "index": "unknown",
            "plan_changed": false
          }
        }
      }
AI 代码解读

clause_processing 用于简化 order by,经过 clause_processing access_type 变成 index_scan(全索引扫描,过滤性较range差),此时出现了和预期不符的结果。

因此可以推测优化器试图优化 order by 时出现了错误:

  • 第一阶段,优化器选择了索引 iabc,采用 range 访问;
  • 第二阶段,优化器试图进一步优化执行计划,使用 order by 的列访问,并清空了第一阶段的结果;
  • 第三阶段,优化器发现使用 order by 的列访问,代价比第一阶段的结果更大,但是第一阶段结果已经被清空了,无法还原,于是选择了代价较大的访问方式(index_scan),触发了bug。

问题解决

  1. 我们在索引优化函数SQL_SELECT::test_quick_select 最开始的时候保存访问计划变量(quick);
  2. 在索引没变的时候,还原这个变量;
  3. 在索引发生改变的时候,删除这个变量。

在不修改 mysql 源码的情况下,可以通过 force index 强制指定索引规避这个bug。

SQL_SELECT::test_quick_select 调用栈如下

    #0  SQL_SELECT::test_quick_select
    #1  make_join_select
    #2  JOIN::optimize
    #3  mysql_execute_select
    #4  mysql_select
    #5  mysql_explain_unit
    #6  explain_query_expression
    #7  execute_sqlcom_select
    #8  mysql_execute_command
    #9  mysql_parse
    #10 dispatch_command
    #11 do_command
AI 代码解读
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
db匠
+关注
目录
打赏
0
0
0
1
9496
分享
相关文章
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
MySQL数据库索引的数据结构?
MySQL中默认使用B+tree索引,它是一种多路平衡搜索树,具有树高较低、检索速度快的特点。所有数据存储在叶子节点,非叶子节点仅作索引,且叶子节点形成双向链表,便于区间查询。
Mysql的索引
MYSQL索引主要有 : 单列索引 , 组合索引和空间索引 , 用的比较多的就是单列索引和组合索引 , 空间索引我这边没有用到过 单列索引 : 在MYSQL数据库表的某一列上面创建的索引叫单列索引 , 单列索引又分为 ● 普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。 ● 唯一索引:索引列中的值必须是唯一的,但是允许为空值 ● 主键索引:是一种特殊的唯一索引,不允许有空值 ● 全文索引: 只有在MyISAM引擎、InnoDB(5.6以后)上才能使⽤用,而且只能在CHAR,VARCHAR,TEXT类型字段上使⽤用全⽂文索引。
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
MySQL覆盖索引解释
总之,覆盖索引就像是图书馆中那些使得搜索变得极为迅速和简单的工具,一旦正确使用,就会让你的数据库查询飞快而轻便。让数据检索就像是读者在图书目录中以最快速度找到所需信息一样简便。这样的效率和速度,让覆盖索引成为数据库优化师傅们手中的尚方宝剑,既能够提升性能,又能够保持系统的整洁高效。
56 9
对比MySQL全文索引与常规索引的互异性
现在,你或许明白了这两种索引的差异,但任何技术决策都不应仅仅基于理论之上。你可以创建你的数据库实验环境,尝试不同类型的索引,看看它们如何影响性能,感受它们真实的力量。只有这样,你才能熟悉它们,掌握什么时候使用全文索引,什么时候使用常规索引,以适应复杂多变的业务需求。
81 12
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
1296 9
MySQL索引学习笔记
本文深入探讨了MySQL数据库中慢查询分析的关键概念和技术手段。
482 81
MySQL选错索引了怎么办?
本文探讨了MySQL中因索引选择不当导致查询性能下降的问题。通过创建包含10万行数据的表并插入数据,分析了一条简单SQL语句在不同场景下的执行情况。实验表明,当数据频繁更新时,MySQL可能因统计信息不准确而选错索引,导致全表扫描。文章深入解析了优化器判断扫描行数的机制,指出基数统计误差是主要原因,并提供了通过`analyze table`重新统计索引信息的解决方法。
104 3

相关产品

  • 云数据库 RDS MySQL 版
  • 推荐镜像

    更多
    AI助理

    你好,我是AI助理

    可以解答问题、推荐解决方案等

    登录插画

    登录以查看您的控制台资源

    管理云资源
    状态一览
    快捷访问