聊一下MySQL的慢SQL优化方向
2023-06-13 12:59:46
MySQL慢SQL优化是数据库性能调优的核心环节，其优化方向需结合系统化排查流程与针对性优化手段。以下是关键优化方向及实施要点：
一、慢SQL发现与诊断慢查询日志配置
核心参数：
long_query_time：设定慢查询阈值（秒），支持小数（如0.5秒）。
slow_query_log：需显式开启（ON），默认关闭。
slow_query_log_file：指定日志路径（如/data/mysql/mysql_slow.log）。
配置方式：
持久化配置：修改my.cnf/my.ini文件，重启生效。
临时配置：通过SET GLOBAL命令动态调整（重启失效）。
日志分析工具
mysqldumpslow：按条件聚合慢查询日志，常用参数：
-s r：按返回记录数排序。
-t 5：显示前5条记录。
-g "pattern"：正则匹配特定SQL。
示例：mysqldumpslow -s r -t 5 /data/mysql/mysql_slow.log
- EXPLAIN关键指标：
type：关注ALL（全表扫描）、index（索引扫描）、ref（索引引用）等。
key：确认是否使用预期索引。
rows：预估扫描行数，与实际数据量对比。
Extra：避免出现Using filesort、Using temporary等低效操作。
查询条件优化
避免在WHERE子句中对字段使用函数或表达式（如WHERE DATE(create_time) = '2023-01-01'）。
替换!=/<>为BETWEEN AND或NOT IN（需评估数据分布）。
模糊查询优化：LIKE '123%'可走索引，LIKE '%123%'则不能。
字段选择与索引利用
明确指定查询字段（避免SELECT *），确保覆盖索引。
为高频查询条件（如status、create_time）建立复合索引。
遵循最左前缀原则设计复合索引。
存储引擎与数据类型
InnoDB：默认引擎，支持事务与行级锁。
MyISAM：读密集型场景可选（无事务支持）。
字段类型：使用INT而非VARCHAR存储ID，TIMESTAMP替代DATETIME存储时间。
索引设计原则
高选择性列优先（如用户ID > 性别）。
避免过度索引（影响写入性能）。
定期分析索引使用率：SHOW INDEX FROM table_name。
索引失效场景
隐式类型转换（如字符串列用数字查询）。
OR条件未全部命中索引。
索引列参与计算（如WHERE age + 1 = 10）。
查询重写
将复杂查询拆分为多个简单查询（减少临时表使用）。
使用JOIN替代子查询（视情况而定）。
数据库参数调优
调整sort_buffer_size、join_buffer_size等内存参数。
优化innodb_buffer_pool_size（通常设为物理内存的50-70%）。
架构层优化
读写分离：主库写，从库读。
分库分表：水平/垂直拆分超大规模表。
- 使用pt-query-digest（Percona工具）分析慢查询趋势。
- 定期执行ANALYZE TABLE更新统计信息。
- 建立基线测试（如sysbench模拟生产负载）。
总结：慢SQL优化需结合日志分析、执行计划解读、代码级优化及索引策略，形成“发现-分析-优化-验证”的闭环。建议从高频慢查询入手，优先解决全表扫描和索引失效问题，再逐步优化复杂查询逻辑。
热门标签
