1、为了优化sql语句,应该开启Mysql的慢查询日志,以方便查看Mysql的耗时的sql语句
2、查看Mysql是否开启,慢查询日志
show variables like '%slow%';
+---------------------+--------------------------------------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------------------------------------+
| log_slow_queries | OFF |
| slow_launch_time | 3 |
| slow_query_log | OFF |
| slow_query_log_file | C:\ProgramData\MySQL\MySQL Server 5.5\Data\zhangbaowei-PC-slow.log |
+---------------------+--------------------------------------------------------------------+
3、开启慢查询日志
set global slow_query_log=ON; <span style="font-family: Arial, Helvetica, sans-serif;">(注意等号两边不要有空格)</span>
show variables like '%slow%';
+---------------------+--------------------------------------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------------------------------------+
| log_slow_queries | ON |
| slow_launch_time | 3 |
| slow_query_log | ON |
| slow_query_log_file | C:\ProgramData\MySQL\MySQL Server 5.5\Data\zhangbaowei-PC-slow.log |
+---------------------+--------------------------------------------------------------------+
5、查看慢查询日志的时间
show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
6、重新设置慢查询的时间
set long_query_time=5;
Query OK, 0 rows affected
7、利用sleep()函数,测试慢查询日志的开启
select sleep(6);
+----------+
| sleep(6) |
+----------+
| 0 |
+----------+
8、观察慢查询日志里面的内容如下
# Time: 160331 20:40:32
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 5.999343 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use testdatabase;
SET timestamp=1459428032;
select sleep(6);