Laravel查询MySQL prospects表按ID倒序速度过慢如何优化

• 优化器选择了低效的执行计划
  对于channel_id=1 order by id desc limit 100的查询，优化器有两个可选执行路径：
  • 路径1：走channel_id二级索引，筛选出所有170万条channel_id=1的记录，按id倒序排序后取前100条，需要对170万条数据做排序，成本很高。
  • 路径2：走主键索引倒序扫描，每扫描一条就判断是否满足channel_id=1的条件，凑够100条就直接返回。
    因为limit 100很小，优化器误判路径2成本更低，但实际上channel_id=2的记录占总数据的80%+，且大多分布在id更大的区间（后插入的记录id自增更大，通常后期业务channel_id=2的写入量更高），导致需要扫描数十万甚至数百万条channel_id=2的无效记录才能凑够100条目标数据，大量随机IO直接导致耗时飙升。
    这也刚好解释你的其他测试场景：
  • 筛选channel_id=2时倒序查询快：因为channel_id=2占绝大多数，倒序扫主键时前100条几乎都是目标数据，很快就能返回。
  • 无where条件倒序取100条快：直接走主键索引取数，不需要过滤，自然速度快。
• 现有索引无法覆盖排序需求
  你单独创建的channel_id二级索引，同一个索引值下关联的主键id是天然按升序存储的，所以order by id asc的查询可以直接按索引顺序取前100条，不需要额外排序，速度极快。但order by id desc的查询无法利用这个顺序特性，只能走上面的两种高成本路径。
• 单独的id倒序索引无效
  该索引未包含channel_id字段，无法满足where条件的过滤需求，优化器不会选择走这个索引，所以没有优化效果。

另外你提到的channel_id=1的data字段存储量更大不是本次慢查询的核心原因，本次查询仅读取id字段，不需要访问data列所在的行数据。

• 首选方案：创建联合覆盖索引
  直接创建联合索引 idx_channel_id_id(channel_id, id desc)，该索引可以同时覆盖where过滤和排序需求：同一个channel_id下的id按倒序存储，查询时可以直接定位到channel_id=1的索引段，取前100条即可返回，不需要排序也不需要扫描额外数据，性能可以和升序查询持平。
  如果你使用的数据库版本不支持降序索引，直接创建idx_channel_id_id(channel_id, id)也可以，InnoDB支持反向扫描同个索引键下的有序数据，性能几乎没有损失。
• 分页场景附加优化
  如果是分页查询，避免使用大offset，改用游标分页方案：比如上一页查询得到的最小id是12345，下一页查询改为where channel_id=1 and id < 12345 order by id desc limit 100，可以完全避免大offset带来的性能损耗。
• 临时应急方案
  如果暂时无法创建索引，可以强制查询走已有的channel_id单独索引，比如SQL中添加FORCE INDEX(你的channel_id索引名)，性能会优于走主键扫描的方案，但远不如联合索引高效，仅适合临时救急。

内容的提问来源于stack exchange，提问作者Resera