在开发中,高效能的程序 也包括 高效能的查询,所以优化SQL也是程序员必要技能之一。要优化就必须要有慢日志记录才可以知道哪些查询慢,然后反向去修改
编辑my.conf 中修改 log_slow_queries 的日志地址
- $ cd /etc/mysql
- $ cat my.cnf |grep slow
- log_slow_queries = /data/logs/mysql/mysql-slow.log
- $ sudo /etc/init.d/mysql restart
简单验证
- $ mysql -uroot -p
- mysql> show variables like '%slow_query_log%';
- +---------------------+---------------------------------+
- | Variable_name | Value |
- +---------------------+---------------------------------+
- | slow_query_log | ON |
- | slow_query_log_file | /data/logs/mysql/mysql-slow.log |
- +---------------------+---------------------------------+
- 2 rows in set (0.00 sec)
-
- $ tail -f /data/logs/mysql/mysql-slow.log
- # Time: 161110 23:20:22
- # User@Host: root[root] @ localhost []
- # Query_time: 3.007048 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
- SET timestamp=1478791222;
- select sleep(3);
先查看目前日志输出方式
- mysql> show variables like '%log_output%';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | log_output | FILE |
- +---------------+-------+
- 1 row in set (0.00 sec)
设置输出方式为FILE,TABLE
- mysql> set global log_output='FILE,TABLE';
- Query OK, 0 rows affected (0.00 sec)
-
- mysql> show variables like '%log_output%';
- +---------------+------------+
- | Variable_name | Value |
- +---------------+------------+
- | log_output | FILE,TABLE |
- +---------------+------------+
- 1 row in set (0.00 sec)
-
- mysql> select count(*) from mysql.slow_log;
- +----------+
- | count(*) |
- +----------+
- | 2 |
- +----------+
- 1 row in set (0.00 sec)


备注: log_output 参数设定日志文件的输出,可选值为 TABLE, FILE ,NONE; "TABLE" 意思为设定日志分别记录到 mysql 库的 general_log 和 slow_log 表中; "FILE" 意思为记录日志到操作系统的文件中, "NONE" 意思为取消日志记录。
http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html
不错
回复 @ apanly: 非常不错