mysql - MySQL 使用 ORDER by query 做出了糟糕的优化选择,并且 EXPLAIN 不正确
问题描述
我有一个问题,mysql 5.6 没有使用索引并导致查询非常慢。
- 具有以下字段的名为人员的表: id、group_id、city_id、...
- 应该可用于 group_id 的索引:(group_id, city_id)
查询是:
SELECT people.* FROM people WHERE people.group_id = 12345 ORDER BY people.id ASC LIMIT 10 OFFSET 0;
该查询的 EXPLAIN 显示它不使用索引并扫描 9324 行:
possible_keys: index_people_on_group_id_and_city_id
key: PRIMARY
rows: 9324
extra: Using where
但是,我的慢查询日志显示检查的行数很大:
Query_time: 16.254633 Lock_time: 0.000038 Rows_sent: 10 Rows_examined: 7429457
- 为什么它不使用索引?
- 为什么优化器如此错误?我是否需要以某种方式重建元数据,以便它意识到它必须扫描超过 9324 行,然后才能做出更好的选择?
我不知道9324这个数字是从哪里来的。group_id 12345 有超过 10k 条记录。
编辑:我发现 LIMIT 的大小在这里很重要。LIMIT 为 100 时,它使用密钥。限制为 10 它错误地认为它必须扫描 9324 行。限制为 1 它错误地认为它必须扫描 932 行。
解决方案
我发现这可能是 MySQL 5.7.6 中修复的错误的结果。
- 发行说明(https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-6.html)
- 错误:https ://bugs.mysql.com/bug.php?id=73837
对于将 ORDER BY 与 LIMIT 组合的查询,优化器可能会切换到适用于 ORDER BY 的索引。在某些情况下,切换的决定是基于启发式而不是成本。优化器现在统一决定是否基于成本进行切换。当切换会导致查询读取整个索引或其中的大部分以查找符合条件的行时,这应该会带来更好的性能。
推荐阅读
- api - 简单 REST API 访问关系数据库上的用户数据的最佳身份验证实践
- c# - Visual Studio 2019,使用 Azure Devops 上的运行设置进行 UI 测试视频捕获,wmv 0 字节
- javascript - 如何在 WordPress 网站上创建自动打开和关闭警报推送通知?
- java - 如何将复杂的 Maven 版本更新到我所有的 pom 中?
- javascript - 函数 window.jitaJS.rtk.refreshAdUnits 中的第二个参数
- node.js - 我们如何使用 mongodb 和 nodejs 在 $look up 聚合管道中找到 $near
- angular - 如何使用角度 11 在 for 循环中使用数组对象?
- flutter - pod install 错误:给定 podspec 'file_picker' 的名称与预期的 'gx_file_picker' 不匹配
- javascript - 验证表单数组中的表单控件不起作用
- java - 如何更改我的按钮颜色,使用 android:background 给它白色边框?