配置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数据库
use employees;
select emp_no,salary from salaries
where from_date between '1986-01-01' and '1986-01-07'
order by from _date,salary;

9.再查看慢查询表
select * from mysql.slow_log;

10.创建索引
create index from_date on salaries(from_date);

11.再查看慢查询表
select * from mysql.slow_log;

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;
create index from_date on salaries(from_date);

14.再查看慢查询表
select * from mysql.slow_log;

15.删除索引
drop index from_date on salaries;