配置MySQL慢查詢

1.執行腳本，制造資料

cd /stage/databases/employees_db

mysql -uroot -p < employees.sql

2.登入mysql

mysql -uroot -p

3.配置慢查詢日志

set global slow_query_log_file='/apps/logs/mysql/slow3306.log';

set session long_query_time=0.5;

set global slow_query_log=on;

4.執行employees資料庫

use employees;

select emp_no,salary from salaries

where from_date between '1986-01-01' and '1986-01-07'

order by from _date,salary;

5.檢視慢查詢日志檔案

cat /apps/logs/mysql/slow3306.log

6.檢視慢查詢表

select * from mysql.slow_log;

7.配置開啟日志寫入表

set global log_output='TABLE';

8.再執行employees資料庫

9.再檢視慢查詢表

10.建立索引

create index from_date on salaries(from_date);

11.再檢視慢查詢表

1. 記錄執行緩慢的管理SQL，如alter table,analyze table, check table, create index, drop index, optimize table, repair table等。

set global log_slow_admin_statements=on;

13.删除索引，增加索引。

drop index from_date on salaries;

14.再檢視慢查詢表

15.删除索引