MySQL-- 优化（慢查询）

MySQL–优化（慢查询）

1. 定位慢查询
2. SQL执行计划
3. 索引
   • 存储引擎
   • 索引底层数据结构
   • 聚簇和非聚簇索引
   • 索引创建原则
   • 索引失效场景
4. SQL优化经验

---

一、如何定位慢查询？

定位慢查询是数据库性能优化的关键步骤之一。当数据库执行某些查询时，如果响应时间过长，就可能导致系统性能下降，影响用户体验。以下是一些定位慢查询的方法：

1、识别常见的慢查询场景

• 聚合查询
  • 涉及大量数据的聚合操作，如 SUM(), AVG(), COUNT() 等。
• 多表查询
  • 特别是涉及多个大表的连接操作。
• 表数据量过大查询
  • 当表中的数据量非常大时，即使简单的查询也可能变得缓慢。
• 深度分页查询
  • 在大数据集上进行深度分页时，性能可能会显著下降。

这些场景的主要表现包括页面加载过慢、接口响应时间过长（通常超过1秒）。

2、使用MySQL慢查询日志

MySQL慢查询日志是定位慢查询的一种有效方法。它记录了执行时间超过指定阈值（由 long_query_time 参数定义，默认是10秒）的所有SQL语句。

slow_query_log = 1  
slow_query_log_file = /var/lib/mysql/localhost-slow.log  
long_query_time = 1  
log_queries_not_using_indexes = 1

• slow_query_log = 1： 启用慢查询日志。
• slow_query_log_file： 指定慢查询日志文件的路径。
• long_query_time： 定义慢查询的时间阈值，单位是秒。这里设置为1秒，意味着执行时间超过1秒的查询将被记录。
• log_queries_not_using_indexes： 记录没有使用索引的查询。这对于发现潜在的优化点非常有用。

重新启动MySQL服务器

配置完成后，需要重新启动MySQL服务器以使更改生效。这可以通过以下命令完成（具体命令可能因系统而异）：

sudo service mysql restart  
# 或者  
sudo systemctl restart mysqld

查看慢查询日志

一旦 MySQL 服务器重新启动并运行了一段时间，就可以查看慢查询日志文件（在本例中为 /var/lib/mysql/localhost-slow.log）以分析记录的慢查询。日志文件通常包含查询的SQL语句、执行时间、锁定时间等信息。

注意事项

• 开启慢查询日志会对MySQL服务器的性能产生一定的影响，因为记录日志本身需要消耗资源。因此，在生产环境中通常只在需要调试性能问题时临时开启慢查询日志。
• 定期检查和分析慢查询日志是优化数据库性能的重要步骤。通过分析日志中的查询，可以发现潜在的优化机会，如添加索引、优化查询语句等。