mysql高并发查询中的数据库表设计与优化

高并发下SELECT查询慢的主因是索引未覆盖查询条件和返回字段，导致全表扫描或回表；应建立复合索引如(status, created_at, name, email)，避免函数操作索引字段，拆分TEXT/BLOB字段，改用游标分页替代LIMIT偏移分页。

高并发下 SELECT 查询慢，先看索引是否覆盖了查询条件和返回字段

很多场景下，查询变慢不是因为并发高，而是单条 SELECT 本身没走索引或走了索引却要回表。比如：

SELECT user_id, name, email FROM users WHERE status = 1 AND created_at > '2025-01-01'

如果只有 status 单列索引，created_at 又没被索引覆盖，MySQL 就可能全表扫描或使用索引合并（效率低）。更糟的是，如果 name 和 email 不在索引里，还要回主键 B+ 树查数据行（回表），并发一高，I/O 和锁竞争立刻放大。

实操建议：

• 用 EXPLAIN 看 type 是否为 ref/range，key 是否命中预期索引，Extra 是否含 Using index（说明是覆盖索引）
• 复合索引顺序按「等值查询字段 + 最左前缀匹配的范围字段」排列，例如 (status, created_at)；若还需返回 name、email，可扩展为 (status, created_at, name, email)（注意总长度别超 innodb_large_prefix 限制）
• 避免在索引字段上用函数或隐式类型转换，比如 WHERE DATE(created_at) = '2025-01-01' 会让索引失效

读多写少场景慎用 TEXT / BLOB 字段，它们会拖慢整个行读取

当表里有 TEXT 或 BLOB 字段，且这些字段经常被 SELECT * 或未显式排除时，InnoDB 默认把它们存在主键 B+ 树的叶子节点外（off-page storage），但每次读行仍需额外 I/O 去加载这些溢出页。高并发下，大量线程争抢磁盘或 buffer pool 中的溢出页缓存，延迟飙升。

实操建议：

• 把大字段拆到单独的扩展表，主表只留关键查询字段，用 JOIN 按需加载（注意关联字段加索引）
• 如果业务允许，用外部存储（如对象存储）存文件内容，数据库只存 URL 或哈希值
• 确认 innodb_file_per_table=ON，避免所有大字段挤在系统表空间里加剧争用

分页查询 LIMIT offset, size 在大数据量下性能断崖式下降

像 SELECT * FROM orders ORDER BY created_at DESC LIMIT 10000, 20 这类语句，MySQL 必须先扫描前 10020 行才能跳过前 10000 行——offset 越大，扫描越多，CPU 和 I/O 成倍增长。并发一上来，线程堆积，innodb_row_lock_waits 和 Threads_running 都会明显升高。

实操建议：

• 改用游标分页（cursor-based pagination）：用上一页最后一条记录的 created_at 和 id 作为下一页起点，例如 WHERE created_at
• 如果必须用 offset，确保 ORDER BY 字段有联合索引，且该索引包含查询所需字段（避免回表）
• 对后台管理类接口，限制最大 offset（如超过 10000 直接报错或走 ES 检索）

连接池配置不当比 SQL 慢更致命：别让 wait_timeout 和应用层连接复用打架

常见错误是应用设了长连接池（如 HikariCP 的 maxLifetime=30min），但 MySQL 服务端 wait_timeout=60s。结果连接池里的空闲连接在 60 秒后被 MySQL 主动断开，应用再取连接时抛 Connection reset 或 Packet too large，重连逻辑又加剧了并发压力。

实操建议：

• MySQL 端调大 wait_timeout（至少 ≥ 应用连接池的 maxLifetime），生产建议设为 28800（8 小时）
• 应用连接池开启 testOnBorrow 或 validationTimeout，但不要每取都 PING；推荐用轻量验证 SQL，如 SELECT 1
• 监控 Aborted_connects 和 Threads_connected，异常升高往往说明连接生命周期不匹配

高并发查询优化真正的难点不在 SQL 写法，而在数据访问模式和连接生命周期的耦合——一个没被意识到的 wait_timeout 错配，可能比十个没优化的 SELECT 更早压垮数据库。