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

来源:转载

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

[email protected]:~$ su parallels 

Password: 

[email protected]:/home/zjy$ sudo -i

[sudo] password for parallels: 

[email protected]:~# tail /var/lib/mysql/mysql_slow_20170829.log 

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

# [email protected]: 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

# [email protected]: 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;

[email protected]:~# 


分享给朋友:
您可能感兴趣的文章:
随机阅读: