mysql dba体系进修（9）slow query log慢查询日记成果
|
3，，慢查询相关的 参数log_queries_not_using_indexes 如果log_queries_not_using_indexes为ON的话，当执行一个sql语句的时候，如果一个表没有索引就会把这个信息记录在慢查询文件中 mysql> show variables like "%log_queries_not%" 查看本栏目更多精彩内容：http://www.bianceng.cn/database/MySQL/ -> ; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | OFF | +-------------------------------+-------+ 1 row in set (0.00 sec) 我们来吧log_queries_not_using_indexes设置为ON的时候来试试吧 mysql> set global log_queries_not_using_indexes=on; Query OK, 0 rows affected (0.00 sec) mysql> show variables like "%log_queries_not%"; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | ON | +-------------------------------+-------+ 1 row in set (0.01 sec) mysql> show index from t; 表t没有索引 Empty set (0.00 sec) mysql> select * from t where TABLE_NAME="xxxxxxxxxxx"; Empty set (0.11 sec) 这个时候慢日志文件记录下来了这个sql语句 mysql> system tail /tmp/mysqlslow.log; # User@Host: root[root] @ localhost [] # Query_time: 1.150157 Lock_time: 0.000205 Rows_sent: 0 Rows_examined: 13568 use test; SET timestamp=1378205188; insert into t select * from t; # Time: 130903 18:58:17 # User@Host: root[root] @ localhost [] # Query_time: 0.100749 Lock_time: 0.057377 Rows_sent: 0 Rows_examined: 27136 SET timestamp=1378205897; select * from t where TABLE_NAME="xxxxxxxxxxx"; mysql> 4，工具mysqldumpslow用来分析sql语句慢查询 [root@test4 Desktop]# mysqldumpslow -s t -t 2 /tmp/mysqlslow.log Reading mysql slow query log from /tmp/mysqlslow.log Count: 1 Time=1.15s (1s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost insert into t select * from t Count: 1 Time=0.04s (0s) Lock=0.06s (0s) Rows=0.0 (0), root[root]@localhost select * from t where TABLE_NAME="S" 出处：http://wolfword.blog.51cto.com/4892126/1287992 （编辑：淮北站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
