MySQL索引设计与高并发查询优化实践

---

title: MySQL索引设计与高并发查询优化实践

keywords:

• MySQL
• 索引设计
• 高并发
• EXPLAIN
• 覆盖索引
• 深分页优化

description: 用可验证的索引与查询策略提升MySQL高并发性能，包含EXPLAIN分析、深分页优化与核心参数校验方法。

date: 2025-11-26

tags:

• EXPLAIN
• MySQL
• 优化
• 数据库
• 深分页优化
• 索引设计
• 覆盖索引
• 高并发

categories:

• 文章资讯
• 技术教程

---

概述

• 目标：合理索引建模与查询改写，避免全表扫描与深分页造成的抖动；通过参数与慢日志治理保障稳定性。
• 适用：订单/用户/日志等高并发读写、分页检索与聚合统计场景。

核心与实战

• 复合索引与覆盖索引：

CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC, id);
-- 查询仅投影索引列可形成覆盖索引
SELECT id, created_at FROM orders WHERE user_id = ? ORDER BY created_at DESC, id DESC LIMIT 50;

• 避免前缀模糊与函数导致索引失效：

-- 失效示例：LIKE '%abc' 或 DATE(created_at) 会绕过索引
-- 改写：LIKE 'abc%' 或使用范围条件 WHERE created_at >= '2025-11-01' AND created_at < '2025-12-01'

• 深分页优化（求下一页锚点而非 OFFSET）：

-- 传统深分页（不推荐）：
SELECT id, created_at FROM orders WHERE user_id=? ORDER BY created_at DESC, id DESC LIMIT 50 OFFSET 10000;

-- 推荐：基于上一页最后记录作为游标
SELECT id, created_at FROM orders
WHERE user_id=? AND (created_at < ? OR (created_at = ? AND id < ?))
ORDER BY created_at DESC, id DESC LIMIT 50;

• 统计与聚合建议：

-- 计数热点表可使用近似或预聚合
SELECT COUNT(*) FROM orders WHERE status='PAID' AND created_at>=NOW()-INTERVAL 7 DAY;
-- 可用物化表/异步任务维护聚合结果，主查走索引列

示例

• EXPLAIN分析：

EXPLAIN FORMAT=JSON
SELECT id, created_at FROM orders WHERE user_id=? ORDER BY created_at DESC, id DESC LIMIT 50;
-- 关注 key / rows / filtered / using_index / using_index_condition

• 慢查询日志与核心参数：

-- 启用慢日志（示例my.cnf）
[mysqld]
slow_query_log=1
long_query_time=0.5
log_slow_admin_statements=1
log_queries_not_using_indexes=1

-- 运行时校验
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'query_cache_type'; -- 对InnoDB与高并发通常应为OFF

• 连接池与并发：

-- 推荐使用短事务与参数化查询，避免长事务锁表
SET SESSION tx_isolation='READ COMMITTED'; -- 或使用事务隔离级别在配置中设定

验证与监控

• 索引使用率与执行计划：

EXPLAIN SELECT ...;
SHOW INDEX FROM orders;

• 慢日志分析：

mysqldumpslow /var/log/mysql/slow.log | head
-- 或使用pt-query-digest进行详尽分析

• 关键观测：
• InnoDB缓冲池命中率、死锁统计SHOW ENGINE INNODB STATUS;
• 等待与锁：INFORMATION_SCHEMA.INNODB_TRX/INNODB_LOCKS/INNODB_LOCK_WAITS

常见误区

• 对非选择性字段单列索引过多，导致写入放大与查询仍走全表；应基于查询模式设计复合索引。
• 依赖OFFSET做深分页造成CPU与IO浪费；应使用游标条件改写或ID范围扫描。
• 忽视慢日志与事务隔离导致锁等待堆积；需限制事务范围与语句粒度。

结语

• 以查询驱动的索引设计、游标式分页与慢日志治理，可显著提升MySQL在高并发场景的可预测性能。