求助一个排查了半年没解决的 MySQL order by 子句导致索引失效的问题， 500 多万条记录的小表要查快两分钟
drymonfidelia 2024-04-27 02:39:50 +08:00 2496 次点击这是一个创建于 536 天前的主题，其中的信息可能已经有所发展或是发生改变。
打码的是查询中完全没用到的字段。
阿里云数据库，版本号是 5.7 
是一个目前 500 多万条记录的小表。索引信息： 
查询语句： 
EXPLAIN 信息： 
通过不断增删条件，确定导致索引失效的问题来自 order by 子句，删除 order by 可以正常索引。 实在不会数据库，请教一下怎么操作
看不清图可以右键放大
 | 1 zhiouzhou 2024-04-27 03:03:06 +08:00 https://www.51cto.com/article/702691.html 现查了下，也没有讲清楚为什么不走索引 |
 | 2 nodejx 2024-04-27 07:49:15 +08:00 via Android 找阿里云查，花钱解决。 |
 | 3 c6h6benzene 2024-04-27 08:36:51 +08:00 via iPhone 不知道为什么，图片就是一个正方形，查询语句也看不完… |
 | 4 buaasoftdavid 2024-04-27 08:56:51 +08:00 你这个是四个表的 join ，500 万的四次方也很大了。写成子查询看看呢？做个 archived 和 userid 的联合 btree 索引试一试？ |
 | 5 lscho 2024-04-27 09:04:13 +08:00 via iPhone 你放的截图不如不放，完全看不出来有效信息 |
 | 6 sagaxu 2024-04-27 10:01:55 +08:00  1 新版 MySQL 有 EXPLAIN ANALYZE 可以更准确的分析。 索引的使用是根据统计信息和规则制定的，统计信息并不一定准确，尤其是发生过大量删除和 update 的时候。 这种没什么好查的，直接强制指定索引就完事了。 不仅 MySQL ，Oracle 也有类似情况，表上加了个索引之后，导致查询选择了错误的索引，只能强制指定索引。 |
 | 7 centerzZ 2024-04-27 11:02:11 +08:00 碰到过 order by + limit 一起用就很慢的情况，应该是 mysql 选择了错误的索引 |
 | 8 9y7cz863P00C7Lie 2024-04-27 11:29:30 +08:00 信息太少，不好确定到具体的原因，你先试试把 order by codeid 改成 order by codeid + 1 看有没有效果 |
 | 9 wuyiccc 2024-04-27 13:36:44 +08:00 force index |
| 10 wuyiccc 2024-04-27 13:52:08 +08:00 把几个表的 ddl 都贴出来研究下 |
 | 11 rekulas 2024-04-27 22:59:12 +08:00 新建一列 Archived,UserId,CodeId 的索引排查是否有效 是否服务器索引异常,重建索引有没有试过,或者将表结构复制到本地测试是否有有效索引 感觉你的索引过于冗余了,可能会导致一些问题 |
 | 12 drymonfidelia OP |
| 13 drymonfidelia OP @rekulas 重建索引指把索引删了再加一遍么？一开始排查这个问题的时候我就把 analyze/check/optimize 命令都运行了一遍 |
| 14 drymonfidelia OP 很奇怪 EXPLAIN 里面为什么显示没使用索引的是 C 表，C 表只有 8 条记录，C 表 PRIMARY 索引的也就是查询条件。我给 C 表加了 FORCE INDEX (PRIMARY) ，他还是不使用索引 @wuyiccc @centerzZ @lscho 还需要什么信息？ @c6h6benzene 右键打开可以看吗？不行的话我再找个图床，imgur 我打不开提示 429 ，应该是公司 VPN 的 IP 被 ban 了 |
| 15 drymonfidelia OP @rekulas IDX2 那个索引是我为了排查这个问题建的 |
| 16 rekulas 2024-04-27 23:46:03 +08:00 @drymonfidelia 对 删除重建 mysql 本身存在丢失索引的 bug 不过阿里云改版这个应该修复过 只是一个尝试方向 |
| 17 drymonfidelia OP @rekulas 新建 Archived,UserId,CodeId 的索引没有效果。强制指定成新建的索引后还是不行，有必要测试重建么？ |
 | 18 LiaoMatt 2024-04-28 09:21:27 +08:00 问题 1: 你的表有些索引的基数太低了, 效率不高, 比如 CodeStatusId, packageId, 看 cardinality 这个字段, 数字越大数据越分散越好, 如果基数太小, MySQL 可能会觉得不用索引效率更高, 建议合并或者删除一些索引; 问题 2: 都是 inner join 会产生, ,每次都要创建临时表, 做文件排序, 可以尝试通过子查询或者其他方式写. MySQL 本身提供 optimizer_trace 功能, 但是需要开启, 可以看到 MySQL 分析结果,不过 RDS 怎么搞还没弄过 |
| 19 LiaoMatt 2024-04-28 09:34:19 +08:00 你说的索引失效是指 C 表全表扫描了吗 |
 | 20 8355 2024-04-28 10:45:10 +08:00 猜测问题来自于滥用联合索引导致的 你可以把整个表的除唯一索引之外的联合索引删掉，按照单字段索引创建问题应该可以顺利解决。 |
 | 21 kxct 2024-04-28 11:32:24 +08:00 使用子查询试试，c 表数据太少没必要走索引 |
| 22 drymonfidelia OP @LiaoMatt C 表只有 8 条，全表查询也不可能导致每个查询都要 2 分钟吧 |
| 23 LiaoMatt 2024-04-28 14:08:09 +08:00 @drymonfidelia 8 * 500W 就是 4000 万了- -, 你可以试着把 join 的表去掉, 看看单表查询效率 |
| 24 drymonfidelia OP @8355 把联合查询删掉, 全站 504 Timeout, 还好是在半夜 |
| 25 drymonfidelia OP @8355 是 联合索引 ,打错了 |
| 26 8355 2024-04-29 09:38:08 +08:00 当然是在测试环境测试啊 锅锅。。。 你直接干生产啊。 |
| 27 8355 2024-04-29 09:38:26 +08:00 @drymonfidelia #25 |
| 28 drymonfidelia OP @8355 测试环境只有几万条数据，不建索引都不卡 |
| 29 8355 2024-04-29 13:52:56 +08:00 @drymonfidelia #28 在测试环境删掉索引不是为了测试 EXPLAIN 是否能命中索引嘛。。。 跟快不快又啥关系啊。。 |
| 30 c6h6benzene 2024-05-01 22:38:53 +08:00 用电脑终于看到了全部的语句。 虽然可能影响不大，不过我习惯上会把关联条件写到 JOIN 那边，就是 FROM tbl_codes A INNER JOIN tbl_packages B on A.PackageId = B.PackageId INNER JOIN tbl_code_status C on A.CodeStatusId = C.CodeStatusId INNER JOIN tbl_package_category D ON B.CategoryId = D.CategoryId WHERE Archived = 0 AND UserId = [UserId] ORDER BY 的话试试写 A.CodeId 看看 |
Select Language