SQL 并发查询性能调优方法

加索引后select仍慢，主因是并发下索引引发锁争用、内存压力及配置失衡。需用explain确认走索引、优化联合索引顺序、调优buffer_pool_size、避免for update死锁，并禁用query cache。

为什么加了索引，SELECT 还是慢？

索引不是万能的，尤其在并发查询下，它可能变成锁争用源或内存压力点。常见现象是：单条 SELECT 很快，但 50 个并发一跑，响应时间陡增、CPU 拉满、甚至出现 Lock wait timeout exceeded。

• 确认是否真走索引：用 EXPLAIN 看 type 字段，避免 ALL 或 index 全扫描；注意 key_len 是否符合预期（比如只用了联合索引前两列，第三列条件失效）
• 联合索引顺序必须匹配 WHERE + ORDER BY 的最左前缀，否则排序阶段无法复用索引，触发 using filesort
• 高并发读场景下，READ COMMITTED 隔离级别比 REPEATABLE READ 更轻量，后者在 mysql 中默认启用间隙锁，容易导致行锁升级为范围锁

innodb_buffer_pool_size 设多大才不拖后腿？

这个参数决定 InnoDB 能缓存多少数据页。设小了，每次查询都去磁盘捞；设大了，又可能挤占系统内存，触发 swap，反而更卡。

• 一般设为物理内存的 50%–75%，但必须留足空间给 OS 和其他进程（比如 PHP-FPM、redis）；线上曾见设到 90%，结果系统频繁 OOM killer 杀掉 mysqld
• 动态调整需重启（MySQL 5.7+ 支持在线调大，但不能缩小），上线前务必压测验证；可用 SHOW ENGINE INNODB STATUS 查看 Buffer pool hit rate，低于 99% 就该警惕
• 注意监控 Innodb_buffer_pool_wait_free：非零说明 buffer pool 太小，后台刷脏页跟不上分配速度

并发 SELECT ... FOR UPDATE 怎么避免死锁？

这类语句本质是写锁，即使只读也要加行级锁，高并发时极易相互等待形成环路。典型错误是事务里先查再更新，且顺序不一致。

• 尽量用主键或唯一索引做 WHERE 条件，避免锁住非目标行（比如用普通索引会锁索引树+对应主键行，范围更大）
• 所有业务逻辑中，对同一张表的加锁操作，按固定字段顺序（如始终按 id ASC）获取锁，不要一会儿 id > 100，一会儿 name LIKE 'a%'
• 减少事务内操作：把校验、计算等非 DB 逻辑提到事务外；避免在事务里调外部 API 或 sleep

要不要开 query_cache_type=1？

MySQL 5.7 默认开启但已标记为 deprecated，8.0 直接移除。在并发查询场景下，它反而是性能杀手。

• 查询缓存对每个 SELECT 都要加全局锁校验 cache key，QPS 上千时锁竞争严重，实测反而比关闭慢 2–3 倍
• 只要表有任一写入（哪怕 INSERT 一行），整个表相关 cache 全部失效，高写入表下命中率趋近于 0
• 替代方案更实在：应用层用 Redis 缓存聚合结果；或用 SELECT SQL_NO_CACHE ... 显式绕过（仅调试用）

真正卡住并发查询的，往往不是某条 SQL 写得差，而是多个看似合理的配置叠加后，在连接数、锁粒度、内存分配上悄悄失衡。调优时别只盯着慢日志，得看 SHOW PROCESSLIST 里一堆 Locked 状态，或者 information_schema.INNODB_TRX 里长事务没提交。