面试题：MySQL中如何开启慢查询日志以及如何分析慢查询日志来优化索引

Question

请阐述在MySQL中开启慢查询日志的具体步骤，以及获取慢查询日志后，如何从日志内容分析出哪些SQL语句需要进行索引优化，举例说明常见的分析思路。

Answer 1

开启慢查询日志步骤

1. 编辑配置文件：打开MySQL配置文件，一般是my.cnf（Linux系统）或my.ini（Windows系统）。
2. 配置慢查询参数：在配置文件中添加或修改以下参数：
   • slow_query_log = 1：开启慢查询日志。
   • long_query_time = 2：设置查询执行时间超过2秒（可根据实际调整）的SQL为慢查询。
   • slow_query_log_file = /var/log/mysql/slow-query.log：指定慢查询日志文件路径（路径可自定义）。
3. 重启MySQL服务：修改配置文件后，重启MySQL服务使配置生效。在Linux下可以使用sudo systemctl restart mysql，Windows下在服务中重启MySQL服务。

从日志分析需索引优化的SQL语句及分析思路

1. 执行时间长：查看日志中执行时间超过设定long_query_time的SQL语句，例如：

# Time: 230520 15:35:12
# User@Host: root[root] @ localhost []  Id:     4
# Query_time: 3.548895  Lock_time: 0.000072 Rows_sent: 1  Rows_examined: 10000
SET timestamp=1684577712;
SELECT * FROM user_table WHERE user_age > 30;

此条SQL执行时间3.548895秒，远超过设定的2秒，可考虑优化。 2. Rows_examined 大：若Rows_examined（查询扫描的行数）很大，说明可能全表扫描。如上述例子扫描了10000行，若表数据量大，可通过添加索引优化。 3. 关联查询：对于多表关联查询，若执行慢且扫描行数多，例如：

# Time: 230520 15:40:20
# User@Host: root[root] @ localhost []  Id:     5
# Query_time: 4.237719  Lock_time: 0.000111 Rows_sent: 10  Rows_examined: 50000
SET timestamp=1684577720;
SELECT a.*, b.* FROM order_table a
JOIN product_table b ON a.product_id = b.product_id
WHERE a.order_date > '2023-01-01';

这里扫描了50000行，可对关联字段product_id和过滤字段order_date添加索引优化。

常见分析思路是关注执行时间长、扫描行数多的SQL，特别是在条件过滤、关联操作等方面，通过添加合适索引减少扫描行数，提高查询效率。