如何优化高并发下 MySQL 查询性能以应对 500+ 用户访问-php教程-PHP中文网

如何优化高并发下 MySQL 查询性能以应对 500+ 用户访问

本文针对 php + mariadb 应用在用户量从 25 人增至 500+ 时查询严重变慢、甚至应用挂起的问题，提供从索引设计、数据类型优化到表维护的完整调优方案。

本文针对 php + mariadb 应用在用户量从 25 人增至 500+ 时查询严重变慢、甚至应用挂起的问题，提供从索引设计、数据类型优化到表维护的完整调优方案。

当您的 kcc_requests 表在低并发（20–25 用户）下响应良好（如 0.19 秒），但在高并发场景中单条查询飙升至 21 秒以上，这通常并非服务器配置或连接数限制的直接问题，而是数据库层存在隐性性能瓶颈。从您提供的慢查询日志可见：两例 SELECT * FROM kcc_requests WHERE aadhar_num = 'XXXXX' 的执行时间差异巨大（0.19s vs 21s），但 Rows_examined 基本一致（约 5.2 万行），说明查询逻辑未变，但执行路径效率大幅下降——这强烈指向索引失效或低效。

? 核心问题：索引未被真正利用

您提到“已为常用字段添加索引”，但关键在于：单一列索引 INDEX(aadhar_num) 在实际执行中可能未生效。原因有二：

数据类型不匹配导致隐式转换
若 aadhar_num 字段定义为 VARCHAR（常见于存储带前导零或校验字符的身份证号），而应用中传入的是纯数字（如 WHERE aadhar_num = 123456789012），MySQL 会强制将整数转为字符串再比对，绕过索引的 B-Tree 查找逻辑，退化为全索引扫描甚至全表扫描。
✅ 正确做法：始终使用字符串字面量查询
```
-- ✅ 推荐：保持类型一致，确保索引命中
SELECT * FROM kcc_requests WHERE aadhar_num = '123456789012';

-- ❌ 避免：触发隐式转换，索引失效
SELECT * FROM kcc_requests WHERE aadhar_num = 123456789012;
```
缺少覆盖索引，加剧 I/O 与锁竞争
单列索引仅加速 WHERE 条件过滤，但 SELECT * 需回表读取所有字段，产生大量随机磁盘 I/O。在高并发下，I/O 瓶颈和行级锁等待会指数级放大延迟。
✅ 解决方案：创建复合覆盖索引，将高频查询字段一并包含：
```
-- 示例：假设常查 id, status, created_at 等字段
ALTER TABLE kcc_requests 
  DROP INDEX idx_aadhar_num, 
  ADD INDEX idx_aadhar_covering (aadhar_num, id, status, created_at);
```
? 覆盖索引（Covering Index）使查询完全通过索引完成，无需回表，显著降低 I/O 和锁持有时间。

?️ 必须执行的运维操作

即使索引结构正确，长期运行的表也可能因数据碎片、统计信息陈旧导致优化器选错执行计划：

更新表统计信息（轻量高效）

MySQL
编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载
```
ANALYZE TABLE kcc_requests;
```
此命令快速刷新索引基数等元数据，帮助优化器生成更优执行计划，推荐每周执行。
重建索引与整理碎片（深度优化）
```
OPTIMIZE TABLE kcc_requests;
```
适用于大表（如您 Rows_examined 达 5 万+），它会重建表和所有索引，消除碎片、压缩空间，并重置统计信息。注意：执行期间表会被锁定（MyISAM）或需额外空间（InnoDB），建议在低峰期操作。

⚠️ 其他关键检查项

确认存储引擎：确保 kcc_requests 使用 InnoDB（支持行锁、事务），而非 MyISAM（表锁，高并发下极易阻塞）。
监控锁等待：执行 SHOW ENGINE INNODB STATUS\G 查看 TRANSACTIONS 部分是否存在长时间 LOCK WAIT。
PHP 连接池管理：避免短连接频繁创建销毁，启用 mysqlnd 的持久连接或使用连接池（如 ProxySQL）。

MariaDB 配置调优（示例）：

# /etc/my.cnf.d/server.cnf
[mariadb]
innodb_buffer_pool_size = 2G          # 至少占物理内存 70%
innodb_log_file_size = 256M
max_connections = 500                 # 匹配应用最大连接数
query_cache_type = 0                  # MariaDB 10.4+ 已弃用，关闭