mysql 查询分析(慢查询日志，show profile)

1慢查询日志

• mysql 的慢查询日志是mysql提供的一种日志记录，用于记录在mysql 中响应时间超过阈值的语句，具体指运行时间超过long_query_time的sql

• 具体运行时间超过long_query_time值的sql，则会被记录到慢查询日志中。long_query_time的默认值是10

• 默认情况下，mysql数据库没有开启慢查询日志，需要我们手动来设置这个参数（如果不是调优需要的话，一般不建议启动该参数）

1.1 查看慢查询日志

show variables like '%slow_query_log%';

默认是关闭的通过set global slow_query_log=1进行开启

要想永久生效必须修改配置文件 my.cnf

1.2 设置最大查询时间

mysql 默认最大查询时间是10秒,可以通过set global long_query_time进行设置

1.3 查看当前系统中有多少条慢查询记录

show global status like '%slow_queries%';

1.4 日志分析工具 mysqldumpslow

s:表示按照何种方式排序
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:返回前面多少条数据

常用的查询

得到返回记录最多的10个sql

mysqldumpslow -s r -t 10 /var/lib/mysql/logfile

得到访问次数最多的10个sql

mysqldumpslow -s c -t 10 /var/lib/mysql/logfile

2 批量插入数据脚本

• 创建表

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,
ename varchar(20) not null default "",
job varchar(9) not null default "",
mgr mediumint unsigned not null default 0,
hiredate date not null,
sal decimal(7,2) not null,
comm decimal(7,2) not null,
deptno mediumint unsigned not null default 0
)ENGINE=INNODB DEFAULT CHARSET=GBK;

• 添加参数

由于开启了慢查询日志，开启了bin-log，要为function 指定一个参数

show variables like 'log_bin_trust_function_creators';

set global log_bin_trust_function_creators=1;

• 创建函数 随机字符串与随机数字

delimiter $$
create function ran_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),ran_string(6),'salesman',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),ran_string(10),ran_string(8));
until i=max_num
end repeat;
commit;
end $$

注意：创建后需要使用delimiter;将结尾标志给修改回来

• 调用函数插入50万数据

call insert_emp (100001,500000);

3 show profile

是mysql提供可以用来分析当前会话中语句执行的资源消耗的情况，用于sql调优，默认情况下，参数处于关闭并保存最近15次的运行结果。
show variables like 'profiling';进行查看，默认是关闭，使用前需要开启set profiling = on

• 查看当前所记录的sql语句

show profiles;

• 查看sql语句详细的执行

show profile cpu,block io for query 11;

• 坏的的参数

converting HEAP to MyISAM ：查询结果太大，内存不够用，往磁盘上搬

Creating tmp table ： 创建临时表

Copying to tmp table on disk : 把内存中的临时表复制到磁盘，危险

locked

4 全局查询日志

永远不要在生产环境开启此日志

命令

• set global general_log =1;
• set global log_output=‘TABLE’;

在开启此功能后，你编写的sql语句，将会记录到mysql库里的general_log表，可以使用查看

select *  from mysql.general_log