MySQL预处理语句未使用预期索引导致执行速度远慢于普通查询

问题根因 #

这个是MySQL 8.0服务端预处理语句的优化器已知缺陷，不属于操作错误。
直接执行SQL时，优化器可以拿到所有查询条件的具体值，识别到device_id为固定常量，联合索引(device_id, created_at DESC, organization_id)完全可以覆盖排序需求，只需按索引顺序扫描，找到第一条满足id != 给定值的记录即可返回，所以耗时极短。
而使用服务端PREPARE预处理时，优化器在生成执行计划阶段还未拿到占位符的实际绑定值，错误判定该联合索引无法满足ORDER BY的排序要求，最终生成了全表扫描+文件排序的执行计划，上亿条数据的排序操作直接导致耗时飙升到分钟级。提供的优化器追踪日志里，预处理场景index_provides_order返回false就是直接证据。

解决方案 #

• 方案1：查询中强制指定索引
  修改预处理SQL，显式指定走目标联合索引，强制优化器放弃全表扫计划：

prepare test_prep from 'select * from `samples` FORCE INDEX (`samples_reverse_device_id_created_at_organization_id_index`) where `samples`.`device_id` = ? and `samples`.`device_id` is not null and `id` != ? order by `created_at` desc limit 1';

• 方案2：改用客户端预处理
  如果是用PDO连接数据库，可以开启PDO的模拟预处理配置：

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

该配置会让预处理逻辑在客户端完成，实际发送给MySQL的是拼接完参数的完整SQL，优化器可以拿到所有参数值生成正确的执行计划，同时还能避免SQL注入风险。

• 方案3：升级MySQL版本
  该优化器缺陷在MySQL 8.0.28及以上版本已被修复，升级后服务端预处理会在绑定参数后重新校验执行计划，自动选择正确的索引排序方案。

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