求助一个排查了半年没解决的 MySQL order by 子句导致索引失效的问题， 500 多万条记录的小表要查快两分钟
drymonfidelia · 248 天前 · 2129 次点击这是一个创建于 248 天前的主题，其中的信息可能已经有所发展或是发生改变。
打码的是查询中完全没用到的字段。
阿里云数据库，版本号是 5.7
是一个目前 500 多万条记录的小表。索引信息：
查询语句：
EXPLAIN 信息：
通过不断增删条件，确定导致索引失效的问题来自 order by 子句，删除 order by 可以正常索引。 实在不会数据库，请教一下怎么操作
看不清图可以右键放大
30 条回复 • 2024-05-01 22:38:53 +08:00
 |
1
zhiouzhou 248 天前
https://www.51cto.com/article/702691.html
现查了下，也没有讲清楚为什么不走索引 |
 |
2
nodejx 247 天前 via Android
找阿里云查，花钱解决。
|
 |
3
c6h6benzene 247 天前 via iPhone
不知道为什么，图片就是一个正方形，查询语句也看不完…
|
 |
4
buaasoftdavid 247 天前
你这个是四个表的 join ，500 万的四次方也很大了。写成子查询看看呢？做个 archived 和 userid 的联合 btree 索引试一试？
|
 |
5
lscho 247 天前 via iPhone
你放的截图不如不放，完全看不出来有效信息
|
 |
6
sagaxu 247 天前  1
新版 MySQL 有 EXPLAIN ANALYZE 可以更准确的分析。
索引的使用是根据统计信息和规则制定的，统计信息并不一定准确，尤其是发生过大量删除和 update 的时候。 这种没什么好查的，直接强制指定索引就完事了。 不仅 MySQL ，Oracle 也有类似情况，表上加了个索引之后，导致查询选择了错误的索引，只能强制指定索引。 |
 |
7
centerzZ 247 天前
碰到过 order by + limit 一起用就很慢的情况，应该是 mysql 选择了错误的索引
|
 |
8
9y7cz863P00C7Lie 247 天前
信息太少，不好确定到具体的原因，你先试试把 order by codeid 改成 order by codeid + 1 看有没有效果
|
 |
9
wuyiccc 247 天前
force index
|
|
10
wuyiccc 247 天前
把几个表的 ddl 都贴出来研究下
|
 |
11
rekulas 247 天前
新建一列 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 247 天前
@drymonfidelia 对 删除重建
mysql 本身存在丢失索引的 bug 不过阿里云改版这个应该修复过 只是一个尝试方向 |
|
17
drymonfidelia OP @rekulas 新建 Archived,UserId,CodeId 的索引没有效果。强制指定成新建的索引后还是不行，有必要测试重建么？
|
 |
18
LiaoMatt 246 天前
问题 1: 你的表有些索引的基数太低了, 效率不高, 比如 CodeStatusId, packageId, 看 cardinality 这个字段, 数字越大数据越分散越好, 如果基数太小, MySQL 可能会觉得不用索引效率更高, 建议合并或者删除一些索引; 问题 2: 都是 inner join 会产生, ,每次都要创建临时表, 做文件排序, 可以尝试通过子查询或者其他方式写. MySQL 本身提供 optimizer_trace 功能, 但是需要开启, 可以看到 MySQL 分析结果,不过 RDS 怎么搞还没弄过
|
|
19
LiaoMatt 246 天前
你说的索引失效是指 C 表全表扫描了吗
|
 |
20
8355 246 天前
猜测问题来自于滥用联合索引导致的
你可以把整个表的除唯一索引之外的联合索引删掉，按照单字段索引创建问题应该可以顺利解决。 |
 |
21
kxct 246 天前
使用子查询试试，c 表数据太少没必要走索引
|
|
22
drymonfidelia OP @LiaoMatt C 表只有 8 条，全表查询也不可能导致每个查询都要 2 分钟吧
|
|
23
LiaoMatt 246 天前
@drymonfidelia 8 * 500W 就是 4000 万了- -, 你可以试着把 join 的表去掉, 看看单表查询效率
|
|
24
drymonfidelia OP @8355 把联合查询删掉, 全站 504 Timeout, 还好是在半夜
|
|
25
drymonfidelia OP @8355 是 联合索引 ,打错了
|
|
26
8355 245 天前
当然是在测试环境测试啊 锅锅。。。 你直接干生产啊。
|
|
27
8355 245 天前
@drymonfidelia #25
|
|
28
drymonfidelia OP @8355 测试环境只有几万条数据，不建索引都不卡
|
|
29
8355 245 天前
@drymonfidelia #28 在测试环境删掉索引不是为了测试 EXPLAIN 是否能命中索引嘛。。。 跟快不快又啥关系啊。。
|
|
30
c6h6benzene 243 天前
用电脑终于看到了全部的语句。
虽然可能影响不大，不过我习惯上会把关联条件写到 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