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在高并发场景的可预测性能。