配置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.删除索引