MySQL慢查询优化、日志收集定位排查、慢查询sql分析

MySQL慢查询日志收集、定位，慢查询分析、排查。

一 MySQL慢查询定位

1. 确定是否已开启慢查询日志

  查看慢查询日志是否已经被开启：

    SHOW VARIABLES LIKE 'slow_query_log';

   

 如果返回值是'OFF'，你需要开启它。

2. 开启慢查询日志

    你可以临时在运行中的MySQL实例中开启慢查询日志：

  SET GLOBAL slow_query_log = 'ON';

或者你可以在`my.cnf`（或`my.ini`）配置文件中，加入以下设置，然后重启MySQL来永久开启它：

 slow_query_log = 1 slow_query_log_file = /path/to/your/logfile.log   # 指定慢查询日志的路径

3. 设置慢查询的阈值

    默认情况下，超过10秒的查询会被记录为慢查询。但你可以根据实际需求调整这个阈值：

    SET GLOBAL long_query_time = X;   # X是你想设置的秒数

     或在`my.cnf`中设置：

    long_query_time = X

4. 开启全查询日志（非必须）

    默认情况下，只有不使用索引的查询才会被记录。如果你想记录所有查询，可以设置：

    SET GLOBAL log_queries_not_using_indexes = 'ON';

5. 查看慢查询日志

    等待一段时间后，可以查看`/path/to/your/logfile.log`来定位慢查询。

6. 分析慢查询日志

    你可以手动查看日志，或者使用工具如`mysqldumpslow`来帮助分析日志内容。

    使用`mysqldumpslow`可以按照不同的维度（如执行时间、平均查询时长等）对慢查询进行排序和分析：

 mysqldumpslow /path/to/your/logfile.log

7. 进一步的查询优化

    在定位到具体的慢查询后，可以使用`EXPLAIN`语句来进一步分析查询执行计划，并基于此进行优化。

8. 定期轮转和清理慢查询日志

慢查询日志可能会很大，所以定期轮转和清理是很有必要的。你可以使用例如`logrotate`这样的工具来帮助管理日志文件。

二 可能导致慢查询的代码

1. 缺乏索引

   -- 未对name列建索引   SELECT * FROM users WHERE name = 'John';

2. 不适当的索引

   假设存在一个first_name和last_name的复合索引，但没有单独的last_name索引   SELECT * FROM users WHERE last_name = 'Doe';

3. 过多的JOIN操作

   SELECT * FROM users    JOIN orders ON users.id = orders.user_id    JOIN products ON orders.product_id = products.id    ... -- 更多JOIN

4. 子查询不当

   SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

5. 选择了过多的数据

 选择了所有列
   SELECT * FROM users;

6. 大量数据排序

   如果没有为last_name建索引，此查询可能会很慢   SELECT * FROM users ORDER BY last_name DESC;

7. 数据类型不匹配

   假设id是INT类型   SELECT * FROM users WHERE id = '12345';

8. 在索引列上使用函数

   SELECT * FROM users WHERE LOWER(name) = 'john';

9. 锁竞争

   START TRANSACTION;   UPDATE users SET balance = balance - 100 WHERE id = 1; -- 这里可能被锁   -- 另一个查询试图更新相同的行   UPDATE users SET balance = balance + 100 WHERE id = 1;

10. 磁盘I/O限制

这主要是硬件问题，但例如，频繁的大型数据读取会导致磁盘I/O问题。

  

11. 查询设计不良

 SELECT * FROM users WHERE age <> 30; -- 不使用索引

12. 数据库正处于高负载状态

不是代码问题，但当有大量的并发查询时，会导致慢查询。

13. 硬件限制

当服务器的CPU或RAM达到瓶颈时，查询可能会变慢。

14. 网络延迟

在分布式环境中，网络延迟会导致查询延迟。

15. 数据量过大

  如果表中有数百万条记录，此查询可能会很慢 SELECT COUNT(*) FROM large_table;

16. 查询中使用`OR`

 SELECT * FROM users WHERE country = 'USA' OR state = 'California';

17. 临时表的使用

SELECT DISTINCT name FROM users ORDER BY age;

18. 磁盘空间不足

不是代码问题，但当硬盘空间不足时，写入操作可能会变慢。

19. 数据库配置不当

例如，配置了小的`innodb_buffer_pool_size`，导致频繁的磁盘I/O。

20. 没有合适的分区策略

 SELECT * FROM large_table WHERE date = '2023-01-01'; -- 如果数据没有按日期分区

21. 使用了慢的存储引擎

例如，在MyISAM和InnoDB之间选择不当。

22. 没有合适的数据库设计

    查询需要访问多个表，但数据可能可以存储在一个表中    SELECT * FROM users, user_profiles WHERE users.id = user_profiles.user_id;

    

23. 外键约束

    DELETE FROM users WHERE id = 1; -- 如果其他表有外键指向users表，可能导致延迟

24. 触发器的过度使用

创建了一个触发器，每次插入或更新`users`表时会执行额外的操作。

  

CREATE TRIGGER after_user_insert AFTER INSERT ON users FOR EACH ROW     BEGIN        -- 一些可能导致慢查询的操作    END;

   

25. 复杂的视图

SELECT * FROM complex_view; -- 视图背后可能有多个表的JOIN操作

26. 避免使用存储过程

有些存储过程可能设计不当，导致效率问题。

27. 大量插入数据

   INSERT INTO users (...) VALUES (...), (...), ...; -- 插入大量数据

28. 频繁的小批量操作

    连续多次小批量操作，而不是批量插入或更新    INSERT INTO users (name) VALUES ('John');    INSERT INTO users (name) VALUES ('Jane');    ...

29. 没有考虑读写分离

所有查询（读和写）都在一个主数据库上执行，没有利用从数据库来分担读的负载。

30. 使用了太多的事务

START TRANSACTION;    -- 一系列的查询    COMMIT;

三 慢查询优化

优化MySQL慢查询通常涉及对查询、数据库结构、配置和有时是硬件的调整。以下是优化慢查询的一些建议和步骤：

1、使用EXPLAIN分析查询

使用EXPLAIN命令可以查看MySQL如何执行查询。这可以帮助你理解查询为什么慢，以及如何优化它。

EXPLAIN SELECT * FROM your_table WHERE your_conditions;

2、添加或调整索引

如果EXPLAIN显示查询正在进行全表扫描，考虑为WHERE子句中的字段添加索引。

考虑添加复合索引来优化涉及多个字段的查询条件。

使用COVERING索引减少数据查找次数。

3、优化查询结构

避免在WHERE子句中使用“!=”或“NOT IN”。

限制使用子查询，特别是在SELECT, FROM和WHERE子句中。

使用JOIN代替子查询。

4、避免在索引列上使用函数

 如果你在索引列上使用函数，索引可能不会被使用。例如，避免这样的查询:

SELECT * FROM users WHERE UPPER(last_name) = 'SMITH';

5、减少查询数据的量

只选择你需要的列，而不是使用SELECT *。

使用LIMIT来限制结果的数量。

7、优化JOIN

总是为JOIN条件中的每个表的字段使用索引。

尽量减少使用左连接（LEFT JOIN）。

8、优化数据库结构

根据你的查询模式和数据访问模式对数据库进行正规化或反正规化。

考虑使用分区来分隔大表。

9、考虑读写分离

 将读操作从写操作中分离，使主数据库处理写操作，而从数据库处理读操作。

10、定期维护

定期使用OPTIMIZE TABLE来优化表。

使用ANALYZE TABLE来更新表的统计信息。