MySQL查询优化100条军规
概述
- 以下是MySQL查询优化的关键军规，分为不同类别，帮助您系统化提升数据库性能
- 资料已经分类整理好，喜欢的朋友自取：https://pan.quark.cn/s/f52968c518d3
一、索引优化
- 为WHERE、JOIN、ORDER BY字段建索引
- 联合索引遵循最左前缀原则
- 避免在索引列使用函数或计算
- 高选择性列适合建索引（区分度>80%）
- 控制单表索引数量（建议≤5个）
- 避免重复索引（如已有(a,b)再单独建a）
- 长字符串使用前缀索引（前N个字符）
- 排序ORDER BY字段尽量使用索引
- 覆盖索引避免回表查询
- 定期分析索引使用率（SHOW INDEXES）
- 删除冗余/未使用的索引
- 外键字段必须建索引
- 避免在索引列使用NOT、<>、!=操作
- 使用索引条件下推（ICP）特性
- 范围查询后的列无法使用索引
二、查询语句优化
- 避免SELECT *，明确指定字段
- 用JOIN代替子查询（WHERE IN）
- 避免使用%前缀的LIKE查询
- 分页查询优化（避免OFFSET过大）
- 用UNION ALL代替UNION去重
- 批量INSERT使用多值语法
- 避免在WHERE子句进行类型转换
- 使用EXISTS代替COUNT(*)判断存在性
- 用BETWEEN代替多个OR条件
- 优先使用INNER JOIN而非OUTER JOIN
- LIMIT分页结合WHERE id > N
- 避免在WHERE子句使用OR连接条件
- 使用强制索引时需谨慎（FORCE INDEX）
- 拆分复杂查询为多个简单查询
- 避免在WHERE子句使用数学运算
- 使用预编译语句（Prepared Statements）
- 避免在循环中执行查询
- 处理NULL值要谨慎（IS NULL无法用索引）
- 使用下推条件优化子查询
- 合并多个相同条件的查询请求
三、表结构优化
- 优先选择整型而非字符串类型
- 字段定义为NOT NULL并设置默认值
- 用ENUM代替字符串类型
- 大表拆分：水平分表/垂直分表
- 控制单表数据量（建议≤500万行）
- 使用合适的数据类型（如TIMESTAMP代替DATETIME）
- 避免使用TEXT/BLOB存储频繁查询的数据
- 归档历史数据（分区表或独立存储）
- 表字段注释和索引注释要完整
- 主键建议使用自增整型（AUTO_INCREMENT）
- 避免过度范式化设计（适当冗余）
- 使用生成列（Generated Columns）优化查询
- 删除无用字段和废弃表
- 大字段单独存储到扩展表
- 使用CHAR固定长度存储短字符串
四、配置优化
- 合理设置innodb_buffer_pool_size（70-80%内存）
- 调整query_cache_size（8.0+版本已移除）
- 优化max_connections连接数
- 设置合适的innodb_flush_log_at_trx_commit
- 关闭性能模式非必要功能
- 启用慢查询日志（slow_query_log）
- 设置合理的tmp_table_size
- 调整thread_cache_size减少连接开销
- 使用SSD存储提升IO性能
- 定期更新统计信息（ANALYZE TABLE）
五、事务与锁优化
- 避免长事务（减少锁持有时间）
- 使用SELECT … FOR UPDATE要谨慎
- 合理选择事务隔离级别
- 批量操作分批次提交
- 监控锁等待（SHOW ENGINE INNODB STATUS）
- 避免间隙锁导致死锁
- 优先使用乐观锁机制
- 及时提交或回滚未完成事务
- 大表DDL操作使用pt-online-schema-change
- 使用SELECT … LOCK IN SHARE MODE需谨慎
六、高级技巧
- 使用EXPLAIN分析执行计划
- 启用查询重写插件（query_rewrite）
- 利用窗口函数减少多次查询
- 使用CTE（公用表表达式）优化复杂查询
- 配置读写分离架构
- 使用连接池管理数据库连接
- 热点数据使用Redis缓存
- 定期执行OPTIMIZE TABLE重组表
- 使用SQL_NO_CACHE测试真实性能
- 监控InnoDB行锁竞争情况
七、设计规范
- 所有表必须包含主键
- 禁止使用触发器/存储过程实现核心逻辑
- 统一字符集为utf8mb4
- 时间字段统一使用UTC时间
- 数据删除使用软删除标记
- 建立数据归档机制
- 禁止开发环境直连生产库
- 重要操作记录审计日志
- 建立数据库字段变更流程
- 定期进行数据库健康检查
八、工具使用
- 使用pt-query-digest分析慢查询
- 配置Prometheus+Granafa监控
- 使用Percona Toolkit进行优化
- 使用mysqldumpslow分析日志
- 利用Performance Schema监控性能
- 使用SHOW PROFILE分析查询细节
- 配置自动化的备份恢复机制
- 使用EXPLAIN FORMAT=JSON获取详细信息
- 使用mysqlslap进行压力测试
- 定期执行CHECK TABLE检查表完整性
注意事项
- 所有优化需结合业务场景
- 修改前务必进行备份
- 优先优化高频率的查询
- 基准测试验证优化效果
- 监控优化后的长期稳定性