MySQL查询优化求助：SELECT语句查询耗时过长问题

问题根源分析 #

从EXPLAIN结果能看出，虽然查询用到了idx_ids_botid索引，但执行的是全索引扫描（type: index），扫描了2057946行数据，仅过滤出1%的结果，这是查询耗时1.68秒的核心原因。这种情况通常是索引设计不合理或字段类型不匹配导致的。

具体优化步骤 #

1. 调整复合索引顺序
   复合索引的字段顺序直接影响查询效率，当前查询的过滤条件是ids = ? AND bot_id = ?，需要确保复合索引的顺序与查询条件的等值匹配顺序一致。

   • 如果现有idx_ids_botid索引是(bot_id, ids)的顺序，会导致无法高效定位数据，建议删除旧索引并创建新索引：

     DROP INDEX idx_ids_botid ON users;
     ALTER TABLE users ADD INDEX idx_ids_bot_id (ids, bot_id);
     

   • 该索引是覆盖索引（包含查询条件和返回字段ids），优化后查询可以直接从索引中获取数据，无需回表。

2. 修复SQL注入风险并确保字段类型匹配
   你当前的PDO代码直接拼接变量，存在严重的SQL注入风险，同时可能因隐式类型转换导致索引失效：

   • 改用预处理语句，同时确保PHP变量类型与数据库字段类型一致（比如数据库ids/bot_id是BIGINT，PHP就用整数类型传入）：

     $stmt = $PDO->prepare("SELECT ids FROM `users` WHERE ids = ? AND bot_id = ?");
     $stmt->execute([$from_id, $botid]);
     $result = $stmt->fetchColumn(); // 直接获取单个字段结果，提升效率
     

3. 验证优化效果
   重新执行EXPLAIN查询，目标结果应为：

   • type字段变为ref或eq_ref（表示通过索引快速定位单行/少量行）
   • rows字段数值大幅降低（理想情况为1）
   • Extra字段仅显示Using index（无需额外过滤）

4. 辅助优化措施

   • 调整数据库缓存参数（如MySQL的innodb_buffer_pool_size），确保索引和数据能全部加载到内存，减少磁盘IO
   • 定期执行OPTIMIZE TABLE users;整理表碎片，提升索引性能

内容的提问来源于stack exchange，提问作者D I S A S T E R