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

概述目标：合理索引建模与查询改写，避免全表扫描与深分页造成的抖动；通过参数与慢日志治理保障稳定性。适用：订单/用户/日志等高并发读写、分页检索与聚合统计场景。核心与实战复合索引与覆盖索引：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在高并发场景的可预测性能。