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

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS PostgreSQL Serverless,0.5-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数据库索引的数据结构?
MySQL中默认使用B+tree索引,它是一种多路平衡搜索树,具有树高较低、检索速度快的特点。所有数据存储在叶子节点,非叶子节点仅作索引,且叶子节点形成双向链表,便于区间查询。
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
MySQL覆盖索引解释
总之,覆盖索引就像是图书馆中那些使得搜索变得极为迅速和简单的工具,一旦正确使用,就会让你的数据库查询飞快而轻便。让数据检索就像是读者在图书目录中以最快速度找到所需信息一样简便。这样的效率和速度,让覆盖索引成为数据库优化师傅们手中的尚方宝剑,既能够提升性能,又能够保持系统的整洁高效。
61 9
对比MySQL全文索引与常规索引的互异性
现在,你或许明白了这两种索引的差异,但任何技术决策都不应仅仅基于理论之上。你可以创建你的数据库实验环境,尝试不同类型的索引,看看它们如何影响性能,感受它们真实的力量。只有这样,你才能熟悉它们,掌握什么时候使用全文索引,什么时候使用常规索引,以适应复杂多变的业务需求。
83 12
阿里达摩院MindOpt优化求解器-月刊(2024年3月)
### MindOpt 优化求解器月刊(2024年3月) - 发布亮点:MAPL建模语言升级至V2.4,支持云上无安装使用和向量化建模语法。 - 新增功能:Linux用户可本地安装`maplpy`,并支持Python与MAPL混编。 - 实例分享:介绍背包问题的组合优化,展示如何在限定容量下最大化收益。 - 用户投稿:探讨机票超售时的最优调派策略,以最小化赔付成本。 - 加入互动:官方钉钉群32451444,更多资源及。 [查看详细内容](https://opt.aliyun.com/)
187 0
阿里达摩院MindOpt优化求解器-月刊(2024年3月)
阿里达摩院MindOpt优化求解器-月刊(2024年4月)
【摘要】2024.04.30,阿里云发布了MindOpt优化求解器的新商品和功能。MindOpt现在已上架,提供超低价零售求解器,支持按需购买,可在阿里云平台上直接购买联网或不联网License。新版本V1.2发布,提升MILP性能,并增加PostScaling参数。此外,MindOpt Studio推出租户定制版,正处于邀测阶段。同时分享了使用MindOpt解决二分类SVM问题的案例。更多内容,可访问相关链接。
343 0
光储荷经济性调度问题【数学规划的应用(含代码)】阿里达摩院MindOpt
本文介绍使用MindOpt工具优化光储荷经济性调度的数学规划问题。光储荷经济性调度技术旨在最大化能源利用率和经济效益,应用场景包括分布式光伏微网、家庭能源管理系统、商业及工业用电、电力市场参与者等。文章详细阐述了如何通过数学规划方法解决虚拟电厂中的不确定性与多目标优化难题,并借助MindOpt云建模平台、MindOpt APL建模语言及MindOpt优化求解器实现问题建模与求解。最终案例展示了如何通过合理充放电策略减少37%的电费支出,实现经济与环保双重效益。读者可通过提供的链接获取完整源代码。
切割问题【数学规划的应用(含代码)】阿里达摩院MindOpt
本文主要讲述了使用MindOpt工具对切割问题进行优化的过程与实践。切割问题是指从一维原材料(如木材、钢材等)中切割出特定长度的零件以满足不同需求,同时尽可能减少浪费的成本。文章通过实例详细介绍了如何使用MindOpt云上建模求解平台及其配套的MindOpt APL建模语言来解决此类问题,包括数学建模、代码实现、求解过程及结果分析等内容。此外,还讨论了一维切割问题的应用场景,并对其进行了扩展,探讨了更复杂的二维和三维切割问题。通过本文的学习,读者能够掌握利用MindOpt工具解决实际切割问题的方法和技术。
智慧楼宇多目标调度问题【数学规划的应用(含代码)】阿里达摩院MindOpt
本文探讨了使用MindOpt工具优化智慧楼宇的多目标调度问题,特别是在虚拟电厂场景下的应用。智慧楼宇通过智能化技术综合考虑能耗、舒适度等多目标,实现楼宇设备的有效管理和调度。虚拟电厂作为多能源聚合体,能够参与电力市场,提供调峰、调频等辅助服务。文章介绍了如何使用MindOpt云上建模求解平台及MindOpt APL建模语言对楼宇多目标调度问题进行数学建模和求解,旨在通过优化储能设备的充放电操作来最小化用电成本、碳排放成本和功率变化成本,从而实现经济、环保和电网稳定的综合目标。最终结果显示,在使用储能设备的情况下,相比不使用储能设备的情形,成本节约达到了约48%。

相关产品

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

    更多
    AI助理

    你好,我是AI助理

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

    登录插画

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

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