使用mysql慢查日志监控有效率问题的SQL语句

通过慢查日志发现有问题的SQL
1、查询次数多且每次查询占用时间长的SQL
通常为pt-query-digest分析的前几个查询
2、IO大的SQL
注意pt-query-digest分析中的Rows examine项，既是扫描的行数越多，IO消耗越大
3、未命中索引的SQL
注意pt-query-digest分析中Rows examine和Rows send的对比，如果扫描的行数远大于发送的行数，那命中率太低了。

1、show variables like ‘slow_query_log’
2、设置变量

set global slow_query_log_file='/var/lib/mysql/mysql_slow_20170829.log';  //慢查日志的记录路径，变量路径一定是mysql用户和组有权限，否则设置失败
set global log_queries_not_using_indexes=on; //重点，不用索引的需要重点优化
set global long_query_time=1; //设置0.01秒，设置为0表示任何sql命令都会被记录

3、limit是mysql的语法
select * from table limit m,n
其中m是指记录开始的index，从0开始，表示第一条记录，一共输出n条

select * from table limit n

从索引0开始，一共输出n条

mysql> show variables like 'long_query_time';

+-----------------+----------+

| Variable_name   | Value    |

+-----------------+----------+

| long_query_time | 0.000000 |

+-----------------+----------+

1 row in set (0.00 sec)

mysql> show variables like 'slow_query_log';

+----------------+-------+

| Variable_name  | Value |

+----------------+-------+

| slow_query_log | ON    |

+----------------+-------+

1 row in set (0.00 sec)

mysql> show variables like 'slow%';

+---------------------+----------------------------------------+

| Variable_name       | Value                                  |

+---------------------+----------------------------------------+

| slow_launch_time    | 2                                      |

| slow_query_log      | ON                                     |

| slow_query_log_file | /var/lib/mysql/mysql_slow_20170829.log |

+---------------------+----------------------------------------+

3 rows in set (0.00 sec)

mysql> use sakila;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+----------------------------+

| Tables_in_sakila           |

+----------------------------+

| actor                      |

| actor_info                 |

| address                    |

| category                   |

| city                       |

| country                    |

| customer                   |

| customer_list              |

| film                       |

| film_actor                 |

| film_category              |

| film_list                  |

| film_text                  |

| inventory                  |

| language                   |

| nicer_but_slower_film_list |

| payment                    |

| rental                     |

| sales_by_film_category     |

| sales_by_store             |

| staff                      |

| staff_list                 |

| store                      |

+----------------------------+

23 rows in set (0.00 sec)

mysql> select * from store limit 1,3;

+----------+------------------+------------+---------------------+

| store_id | manager_staff_id | address_id | last_update         |

+----------+------------------+------------+---------------------+

|        2 |                2 |          2 | 2006-02-15 04:57:12 |

+----------+------------------+------------+---------------------+

1 row in set (0.00 sec)

mysql> quit;

Bye

zjy@ubuntu:~$ su parallels 

Password: 

parallels@ubuntu:/home/zjy$ sudo -i

[sudo] password for parallels: 

root@ubuntu:~# tail /var/lib/mysql/mysql_slow_20170829.log 

# Time: 2017-08-29T08:37:20.167516Z

# User@Host: root[root] @ localhost []  Id:    17

# Query_time: 0.000435  Lock_time: 0.000190 Rows_sent: 1  Rows_examined: 2

SET timestamp=1503995840;

select * from store limit 1,3;

# Time: 2017-08-29T08:38:45.837452Z

# User@Host: root[root] @ localhost []  Id:    17

# Query_time: 0.000007  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 2

SET timestamp=1503995925;

# administrator command: Quit;

root@ubuntu:~#