MySQL表超10万行后SELECT查询从0.01秒升至0.25秒如何优化

你单独为三个查询字段创建单列索引无法匹配该查询的过滤逻辑，MySQL通常只会选择其中一个索引完成首轮过滤，剩余的两个过滤条件需要回表扫描海量数据，这是查询耗时骤升的核心原因。
你需要创建联合索引才能完全命中查询条件，索引创建语句如下：

CREATE INDEX idx_obj_id_process_status ON `table` (`object_id`, `object_processed`, `object_processing`);

如果表字段数量较少，可以进一步创建覆盖索引避免回表操作，直接在索引中返回所有需要的字段，性能提升更明显：

-- 示例：除三个过滤条件外，把业务需要的其他字段也加入索引，根据实际表结构调整
CREATE INDEX idx_obj_id_process_status_covering ON `table` (`object_id`, `object_processed`, `object_processing`, col1, col2, col3);

创建完成后执行EXPLAIN SELECT * FROM tableWHEREobject_id= '1' ANDobject_processed= '0' ANDobject_processing = '0' LIMIT 1，确认执行计划中key字段命中了新建的联合索引，Extra字段出现Using index则说明覆盖索引生效。

针对你提供的现有配置，可修改以下参数进一步提升性能：

• innodb_buffer_pool_instances：当前设置为20不合理，单buffer pool实例建议最小1G容量，40G的buffer pool设置4-8个实例即可，推荐改为8，过多实例会增加调度开销
• innodb_thread_concurrency：当前设置为34，MySQL 5.7及以上版本建议设置为0，由InnoDB自动调度线程，手动固定值容易引发线程阻塞瓶颈
• read_buffer_size：当前设置为2M过大，该内存是每个连接分配的专属内存，高频点查场景下设置为128K即可，节省的内存可留给数据缓存使用
• key_buffer_size：当前设置为64M，如果你使用的是InnoDB引擎，该参数是MyISAM引擎的索引缓存，几乎无用，可降低为8M
• 新增以下配置关闭查询缓存：高频更新的表查询缓存失效开销极高，完全没必要开启

  query_cache_type = 0
  query_cache_size = 0
  

你提到需要循环执行该查询数千次，这部分的优化收益远高于配置调整：

• 取消单条循环查询，改为批量拉取符合条件的数据，比如一次查询LIMIT 100，放到应用内存中依次处理，可减少99%的数据库交互开销
• 新增应用层短缓存：如果查询返回无符合条件的结果，可缓存该状态100-500毫秒，避免空查询频繁访问数据库
• 避免使用SELECT *：只查询业务需要的字段，进一步降低回表开销和数据传输成本

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