获取慢查询日志 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) |