MySQL慢查询优化

1. 作者QQ：67065435 QQ群：669756510

2. 本站内容全部为作者原创，转载请注明出处！

启用慢查询日志

1. 启用慢查询日志

   # 启用慢查询日志
   SET GLOBAL slow_query_log = 'ON';
   # 设置慢查询阈值2秒
   SET GLOBAL long_query_time = 2;
   # 设置慢查询日志文件位置（这样就可以通过慢查询日志找出慢查询sql语句）
   SET GLOBAL slow_query_log_file = '/var/log/mysql-slow.log';
   

2. 使用EXPLAIN分析

   EXPLAIN SELECT c1,c2,c3 FROM t1 WHERE c1 = 12345;
   # 主要观察type（《阿里巴巴java开发手册》要求SQL性能优化的目标是至少达到range级别）
   # 1. system: 表只有一条数据且存储引擎可以准确的统计到一条数据，一般出现在MyISAM存
   # 储引擎、内存型表查询的场景，由于一般是用InnoDB存储引擎，所以很少用到；
   
   # 2. const‌: 通过主键或者唯一索引等值查询来定位一条数据的场景(WHERE id = 1)；
   
   # 3. ref: 普通索引等值查询的场景(SELECT * FROM t1 WHERE c2 = 1)
   
   # 3. eq_ref‌: ‌多表连接查询，从表通过主键、唯一索引进行等值查询的场景
   # (SELECT t1.c1,t1.c2,t2.c1,t2.c2 FROM t1 LEFT JOIN t2 ON t1.id=t2.id)；
   
   # 3. ref_or_null: 普通索引等值查询+IS NULL的场景(SELECT * FROM t1 WHERE c2 = 1 OR c2 IS NULL)
   
   # 4. range‌: 查询范围记录时（between in > <），type为range；
   
   # 5. index‌: ‌可以使用索引查询但是要扫描全部索引，type为index，需要优化查询；
   
   # 6. all‌: ‌无法使用索引进行全表扫描，此时type为all‌，需要优化查询。
   

3. 语句没问题-优化索引

   CREATE INDEX idx_name ON your_table(column_name);
   

4. 索引没问题-优化语句

   1. 避免使用 SELECT * 而是使用 SELECT c1,c2 只查询需要的列；
   2. 使用表连接代替子查询，使用连表代替子查询可以提高查询效率；
   3. 减少排序和临时表，使用 ORDER BY 和 GROUP BY 时确保字段使用了索引；
   4. 使用LIMIT：使用LIMIT可以减少处理的数据量；
   

5. 优化MySQL配置

   1. 调整缓存大小：设置innodb_buffer_pool_size 为服务器内存的50%-70%，提高InnoDB存储引擎性能；
   2. 调整连接参数：max_connections = 256、thread_cache_size = 32
   

6. 使用慢查询日志分析工具

   mysqldumpslow [选项] [慢查询日志文件路径]
   
   -s <排序方式>：按指定维度排序：
     t：按总执行时间排序（默认）；
     l：按锁定时间排序；
     c：按执行次数排序；
     r：按返回行数排序‌；
   
   -t
     N：显示前 N 条最慢的查询；
   
   -g <关键词>：仅筛选包含指定关键词的SQL；
   
   -a：显示完整 SQL 语句。
   

7. 定期维护和优化表

   OPTIMIZE TABLE t1;
   

8. 升级服务器硬件

   当优化无法满足硬件瓶颈时，可考虑通过硬件升级提高数据库性能。