求助一个排查了半年没解决的 MySQL order by 子句导致索引失效的问题， 500 多万条记录的小表要查快两分钟

[drymonfidelia]

打码的是查询中完全没用到的字段。

阿里云数据库，版本号是 5.7

是一个目前 500 多万条记录的小表。索引信息：

查询语句：

EXPLAIN 信息：

通过不断增删条件，确定导致索引失效的问题来自 order by 子句，删除 order by 可以正常索引。 实在不会数据库，请教一下怎么操作

看不清图可以右键放大

[zhiouzhou]

https://www.51cto.com/article/702691.html
现查了下，也没有讲清楚为什么不走索引

[nodejx]

找阿里云查，花钱解决。

[c6h6benzene]

不知道为什么，图片就是一个正方形，查询语句也看不完…

[buaasoftdavid]

你这个是四个表的 join ，500 万的四次方也很大了。写成子查询看看呢？做个 archived 和 userid 的联合 btree 索引试一试？

[lscho]

你放的截图不如不放，完全看不出来有效信息

[sagaxu]

新版 MySQL 有 EXPLAIN ANALYZE 可以更准确的分析。

索引的使用是根据统计信息和规则制定的，统计信息并不一定准确，尤其是发生过大量删除和 update 的时候。

这种没什么好查的，直接强制指定索引就完事了。

不仅 MySQL ，Oracle 也有类似情况，表上加了个索引之后，导致查询选择了错误的索引，只能强制指定索引。

[centerzZ]

碰到过 order by + limit 一起用就很慢的情况，应该是 mysql 选择了错误的索引

[123qwerty]

信息太少，不好确定到具体的原因，你先试试把 order by codeid 改成 order by codeid + 1 看有没有效果

[wuyiccc]

force index

[wuyiccc]

把几个表的 ddl 都贴出来研究下

[rekulas]

新建一列 Archived,UserId,CodeId 的索引排查是否有效
是否服务器索引异常,重建索引有没有试过,或者将表结构复制到本地测试是否有有效索引
感觉你的索引过于冗余了,可能会导致一些问题

[drymonfidelia]

@wuyiccc @sagaxu 加了 FORCE INDEX 还是不使用索引，下午也尝试了拆分查询还是不行

[drymonfidelia]

@rekulas 重建索引指把索引删了再加一遍么？一开始排查这个问题的时候我就把 analyze/check/optimize 命令都运行了一遍

[drymonfidelia]

很奇怪 EXPLAIN 里面为什么显示没使用索引的是 C 表，C 表只有 8 条记录，C 表 PRIMARY 索引的也就是查询条件。我给 C 表加了 FORCE INDEX (PRIMARY) ，他还是不使用索引
@wuyiccc
@centerzZ


@lscho 还需要什么信息？
@c6h6benzene 右键打开可以看吗？不行的话我再找个图床，imgur 我打不开提示 429 ，应该是公司 VPN 的 IP 被 ban 了

[drymonfidelia]

@rekulas IDX2 那个索引是我为了排查这个问题建的

[rekulas]

@drymonfidelia 对 删除重建
mysql 本身存在丢失索引的 bug 不过阿里云改版这个应该修复过 只是一个尝试方向

[drymonfidelia]

@rekulas 新建 Archived,UserId,CodeId 的索引没有效果。强制指定成新建的索引后还是不行，有必要测试重建么？

[LiaoMatt]

问题 1: 你的表有些索引的基数太低了, 效率不高, 比如 CodeStatusId, packageId, 看 cardinality 这个字段, 数字越大数据越分散越好, 如果基数太小, MySQL 可能会觉得不用索引效率更高, 建议合并或者删除一些索引; 问题 2: 都是 inner join 会产生, ,每次都要创建临时表, 做文件排序, 可以尝试通过子查询或者其他方式写. MySQL 本身提供 optimizer_trace 功能, 但是需要开启, 可以看到 MySQL 分析结果,不过 RDS 怎么搞还没弄过

[LiaoMatt]

你说的索引失效是指 C 表全表扫描了吗

[8355]

猜测问题来自于滥用联合索引导致的
你可以把整个表的除唯一索引之外的联合索引删掉，按照单字段索引创建问题应该可以顺利解决。

[kxct]

使用子查询试试，c 表数据太少没必要走索引

[drymonfidelia]

@LiaoMatt C 表只有 8 条，全表查询也不可能导致每个查询都要 2 分钟吧

[LiaoMatt]

@drymonfidelia 8 * 500W 就是 4000 万了- -, 你可以试着把 join 的表去掉, 看看单表查询效率

[drymonfidelia]

@8355 把联合查询删掉, 全站 504 Timeout, 还好是在半夜

[drymonfidelia]

@8355 是 联合索引 ,打错了

[8355]

当然是在测试环境测试啊 锅锅。。。 你直接干生产啊。

[8355]

@drymonfidelia #25

[drymonfidelia]

@8355 测试环境只有几万条数据，不建索引都不卡

[8355]

@drymonfidelia #28 在测试环境删掉索引不是为了测试 EXPLAIN 是否能命中索引嘛。。。 跟快不快又啥关系啊。。

[c6h6benzene]

用电脑终于看到了全部的语句。

虽然可能影响不大，不过我习惯上会把关联条件写到 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 看看