Mysql慢查询优化笔记

PHPABC Mysql 875 次浏览 , 没有评论

获取慢查询日志 mysql-slow.log

一般慢查询日志文件存放在/data/mysql/mysql-slow.log,可以通过以下命令来查看:

mysql> show variables like '%slow%';
+---------------------+----------------------------+
| Variable_name       | Value                      |
+---------------------+----------------------------+
| log_slow_queries    | ON                         |
| slow_launch_time    | 2                          |
| slow_query_log      | ON                         |
| slow_query_log_file | /data/mysql/mysql-slow.log |
+---------------------+----------------------------+

部分云数据库厂商不提供直接登录数据库服务器功能,但是会提供额外的日志获取途径,具体请咨询相关厂商。

##查看日志中慢查询语句

# Time: 160315 15:02:52
# User@Host: ecourse[ecourse] @  [192.168.100.2]
# Query_time: 5.440974  Lock_time: 0.000126 Rows_sent: 13  Rows_examined: 7086046
SET timestamp=1458025372;
select subject0_.ID as ID1_29_, subject0_.CONF_ID as CONF_ID2_29_, subject0_.GROUP_NAME as GROUP_NA3_29_, subject0_.IS_OPT as IS_OPT4_29_, subject0_.SUB_NAME as SUB_NAME5_29_, subject0_.SORT_NUM as SORT_NUM6_29_, subject0_.SUB_TYPE as SUB_TYPE7_29_ from SUBJECTS subject0_ where 1=1 and (subject0_.ID in (select distinct classsubje1_.SUB_ID from CLASSES_SUBJECTS classsubje1_ where classsubje1_.CONF_ID='ff808181536deb2e01537099b04b2462')) order by subject0_.SORT_NUM ASC;

以上信息中,此条sql语句执行了5.44秒,检查数据行数7086046行。可能数据库表中一共也没这么多行,但是在连接查询、子查询的情况下,会导致m*n这种复杂度的检查。

EXPLAIN 分析

mysql> EXPLAIN select subject0_.ID as ID1_29_, subject0_.CONF_ID as CONF_ID2_29_, subject0_.GROUP_NAME as GROUP_NA3_29_, subject0_.IS_OPT as IS_OPT4_29_, subject0_.SUB_NAME as SUB_NAME5_29_, subject0_.SORT_NUM as SORT_NUM6_29_, subject0_.SUB_TYPE as SUB_TYPE7_29_ from SUBJECTS subject0_ where 1=1 and (subject0_.ID in (select distinct classsubje1_.SUB_ID from CLASSES_SUBJECTS classsubje1_ where classsubje1_.CONF_ID='ff808181536deb2e01537099b04b2462')) order by subject0_.SORT_NUM ASC;
+----+--------------------+--------------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type        | table        | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+--------------------+--------------+------+---------------+------+---------+------+------+------------------------------+
|  1 | PRIMARY            | subject0_    | ALL  | NULL          | NULL | NULL    | NULL | 1226 | Using where; Using filesort  |
|  2 | DEPENDENT SUBQUERY | classsubje1_ | ALL  | NULL          | NULL | NULL    | NULL | 6197 | Using where; Using temporary |
+----+--------------------+--------------+------+---------------+------+---------+------+------+------------------------------+
2 rows in set (0.00 sec)

这个前面加了EXPLAIN的语句可以放心执行,他只是分析语句,不具体查库。
以上结果,我们首先要关心 type 和 rows 这两列。type里面的ALL应该不是我们想看到的,表示全表扫描了。rows里面的两个数相乘,基本上是慢查询记录的检查行数的值(慢查询记录时间和我执行这个的时间不一样,有差距,另外这个值也是一个估值,不准)。再结合咱们的sql看看,基本上能确定算法复杂度不是我们想要的m*n。

SQL修改

mysql> EXPLAIN select distinct subject0_.ID as ID1_29_, subject0_.CONF_ID as CONF_ID2_29_, subject0_.GROUP_NAME as GROUP_NA3_29_, subject0_.IS_OPT as IS_OPT4_29_, subject0_.SUB_NAME as SUB_NAME5_29_, subject0_.SORT_NUM as SORT_NUM6_29_, subject0_.SUB_TYPE as SUB_TYPE7_29_ from SUBJECTS subject0_ , CLASSES_SUBJECTS classsubje1_ where subject0_.ID = classsubje1_.SUB_ID and classsubje1_.CONF_ID='ff808181536deb2e01537099b04b2462'  order by subject0_.SORT_NUM ASC;
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------+------+----------------------------------------------+
| id | select_type | table        | type   | possible_keys | key     | key_len | ref                         | rows | Extra                                        |
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | classsubje1_ | ALL    | NULL          | NULL    | NULL    | NULL                        | 6197 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | subject0_    | eq_ref | PRIMARY       | PRIMARY | 96      | ecourse.classsubje1_.SUB_ID |    1 |                                              |
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------+------+----------------------------------------------+

去掉子查询使用join,复杂度降低了一个数量级。

添加索引

我们看到classsubje1_.CONF_ID=’402881f1536e732a01536e7c066e6d6e’这么一个条件,考虑可以在这个字段上建立索引:

mysql> CREATE INDEX index_cs_config_id ON `CLASSES_SUBJECTS` (CONF_ID);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

再次分析:

mysql> EXPLAIN select distinct subject0_.ID as ID1_29_, subject0_.CONF_ID as CONF_ID2_29_, subject0_.GROUP_NAME as GROUP_NA3_29_, subject0_.IS_OPT as IS_OPT4_29_, subject0_.SUB_NAME as SUB_NAME5_29_, subject0_.SORT_NUM as SORT_NUM6_29_, subject0_.SUB_TYPE as SUB_TYPE7_29_ from SUBJECTS subject0_ , CLASSES_SUBJECTS classsubje1_ where subject0_.ID = classsubje1_.SUB_ID and classsubje1_.CONF_ID='ff808181536deb2e01537099b04b2462'  order by subject0_.SORT_NUM ASC;
+----+-------------+--------------+--------+--------------------+--------------------+---------+-----------------------------+------+----------------------------------------------+
| id | select_type | table        | type   | possible_keys      | key                | key_len | ref                         | rows | Extra                                        |
+----+-------------+--------------+--------+--------------------+--------------------+---------+-----------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | classsubje1_ | ref    | index_cs_config_id | index_cs_config_id | 97      | const                       |  158 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | subject0_    | eq_ref | PRIMARY            | PRIMARY            | 96      | ecourse.classsubje1_.SUB_ID |    1 |                                              |
+----+-------------+--------------+--------+--------------------+--------------------+---------+-----------------------------+------+----------------------------------------------+
2 rows in set (0.00 sec)

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据

Go