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;
- 记录执行缓慢的管理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;