【MySQL】MySQL 调优

一、调优流程

• 开启慢查询日志，设置阈值，如超过5秒钟的就是慢SQL，并将它抓取出来
• explain + 慢 SQL 分析
• show profile
• 进行 SQL 数据库服务器的参数调优

 

二、慢查询日志

2.1 概念

2.2 使用

慢查询日志默认是关闭的

# 查看慢查询日志位置和开关状态

show variables like '%slow_query_log%';

# 开启慢查询日志

# 打开配置文件
vim /etc/my.cnf

# 修改配置文件
# 在[mysqld]下增加
slow_query_log=ON   #开启慢查询
slow_query_log_file=/opt/mysql/data/mysqls-low.log  #慢查询日志位置
long_query_time = 1 #设置的阈值(秒)
log_output=FILE # 慢查询写入方式，表写入关键字为 TABLE
log_bin_trust_function_creators=1 #信任存储函数创建者

# 查看阈值设置时间

show variables like '%long_query_time%';

# 修改写入方式

MySQL的查询日志支持写入文件或写入数据表两种形式，这个由参数log_output控制

show variables like 'log_output';

# 测试

# 模拟查询动作，睡眠4秒
select sleep(4);

  

 

2.3 mysqldumpslow

MySQL慢查询（二） - pt-query-digest详解慢查询日志

mysqldumpslow -help

  

三、批量插入数据

-- create table dept(
-- 	id int unsigned primary key auto_increment,
-- 	deptno mediumint unsigned not null default 0,
-- 	dname varchar(20) not null default '',
-- 	loc varchar(13) not null default ''
-- )engine=innodb default charset=gbk;

-- create table emp(
-- 	id int unsigned primary key auto_increment,
-- 	empno mediumint unsigned not null default 0 comment '编号',
-- 	ename varchar(20) not null default '' comment '名字',
-- 	job varchar(9) not null default '' comment '工作',
-- 	mgr mediumint unsigned not null default 0 comment '上级编号',
-- 	hiredate date not null comment '入职时间',
-- 	sal decimal(7,2) not null comment '薪水',
-- 	comm decimal(7,2) not null comment '红利',
-- 	deptno mediumint unsigned not null default 0 comment '部门编号'
-- )engine=innodb default charset=gbk;

-- delimiter $$
-- create function rand_string(n INT) returns varchar(255)
-- begin
-- declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
-- declare return_str varchar(255) default '';
-- declare i int default 0;
-- while i<n do
-- set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52),1));
-- set i=i+1;
-- end while;
-- return return_str;
-- end $$
-- 

-- delimiter $$
-- create function rand_num() returns int(5)
-- begin
-- declare i int default 0;
-- set i = floor(100 + rand()*10);
-- return i;
-- end $$

-- delimiter $$
-- create procedure insert_emp(in start int(10), in max_num int(10))
-- begin
-- declare i int default 0;
-- set autocommit=0;
-- repeat
-- set i = i+1;
-- insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'SALEMAN',0001,CURDATE(),2000,400,rand_num());
-- until i = max_num
-- end repeat;
-- commit;
-- end $$

delimiter $$
create procedure insert_dept(in start int(10), in max_num int(10))
begin
declare i int default 0;
set autocommit=0;
repeat
set i=i+1;
insert into dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));
until i = max_num
end repeat;
commit;
end $$

delimiter ;
call insert_dept(100, 10);
call insert_emp(100001,500000);

 

四、Show Profile

4.1 概述

 

4.2 开启 Show Profile

# 查看 profile 是否开启
show variables like 'profiling';

# 开启 profile
set profiling=on;

# 测试

select * from emp group by id%10 limit 150000;

  

# 分析某个具体的语句开销

show profile cpu,block io for query 8;

# 有问题的情况

 

五、全局查询日志

可以抓取所有 sql