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