MySQL高级3-查询截取分析

1. 查询优化

1.1 永远小表驱动大表

• 类似嵌套循环Nested Loop
• 小的数据集驱动大的数据集
• EXISTS 将主查询的数据，放到子查询中做条件验证，根据验证结果（True或False）来决定主查询的数据结果是否得以保留

select .. from table where exists(subquery)

select * from tbl_emp e where e.deptid in (select id from tbl_dept d);
select * from tbl_emp e where exists (select 1 from tbl_dept d where d.id=e.deptid);

1.2 order by 关键字优化

1. order by子句，尽量使用Index方式排序，避免使用FileSort方式排序
2. MySQL支持两种方式排序，FileSort和Index，Index效率高，他指MySQL扫描索引本身完成排序，FileSort效率低

create table tblA(
#id int primary key not null auto_increment,
age int,
birth timestamp not null
);

insert into tblA(age, birth) values(22, now());
insert into tblA(age, birth) values(23, now());
insert into tblA(age, birth) values(24, now());

create index idx_A_ageBirth on tblA(age, birth);

select * from tblA;

explain select * from tblA where age>20 order by age，birth;
explain select * from tblA where age>20 order by birth,age;#Using filesort
explain select * from tblA order by age asc,birth desc;#Using filesort
/*
ORDER BY满足两种情况会使用Index方式排序：
1.order by语句使用索引最左前列
2.使用where子句与order by子句条件列组合满足索引最左前列
*/

3. 如果不在索引列上，fileSort有两种算法，MySQL就要启动双路排序和单路排序

1.双路排序，MySQL4.1前是使用双路排序，两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应数据输出，I\O很耗时，MySQL4.1后出现了单路排序
2.单路排序，从磁盘读取查询需要的所有列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出，效率更快，避免了第二次读取数据，并把随机IO变成顺序IO，但他会使用更多的空间，因为他把每一行都保存在内存中

4. 优化策略：
   order by 不要使用select *
   增大sort_buffer_size参数的设置
   增大max_length_for_sort_data参数的设置

1.3 group by关键字优化

• group by实质是先排序后进行分组，遵照索引建的最佳左前缀
• 当无法使用索引列，增大max_length_for_sort_dta参数设置+增加sort_buffer_size参数设置
• where高于having，能写在where限定的条件就不要having限定

2. 慢查询日志

• MySQL慢查询日志是MySQL提供的一种日志记录，用来记录MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值（默认10）的SQL，会被记录到慢查询日志中
• 默认MySQL不开始慢查询日志。不是调优需要，不建议打开，影响性能

#查看，文件在/var/lib/mysql/xxx-slow.log
show variables like '%slow_query_log%';
#开启，重启MySQL无效
set global slow_query_log=1;
#永久生效，修改my.cnf文件，不建议
slow_query_log=1
slow_query_log_file=/var/lib/mysql/xxx-slow.log
#查看long_query_time值
show variables like '%long_query_time%';
#改变默认值，重新连接后查看
set global long_query_time=3;

select sleep(4);#模拟
#查看“健康度”
show global status like '%slow_queries%';

2.1 日志分析工具mysqldumpslow

mysqldumpslow --help
-s 按照何种方式排序
 c 访问次数
 l 锁定时间
 r 返回记录
 t 查询时间
 al 平均锁定使劲
 ar 平均返回记录数
 at 平均查询时间
-t 返回前面几条的数据
-g 后加一个正则表达式，大小写不敏感 
#得到返回记录最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/XXX-slow.log | more
#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/XXX-slow.log | more

3. 批量数据脚本

#建库
create database bigDataTest;
use bigDataTest;
#建表dept
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;
#设置参数，mysql重启后消失，在my.cnf[mysqld]加入就不会了
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;
#随机产生字符串函数
delimiter $$
create function rand_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM';
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 $$
#创建往emp表中插入数据的存储过程
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),'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),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);

4. Show Profile

• MySQL提供可以用来分析当前会话中语句执行的资源消耗情况，用于SQL的调优的测量
• 官网链接
• 默认情况下，参数处于关闭状态，并保存最近15次的运行结果

#查看当前MySQL版本是否支持
show variables like 'profiling%';
#开启功能
set profiling=on;
#运行SQL
select * from emp group by id%10 limit 150000;
select * from emp group by id%20 order by 5;
#查看结果
show profiles;
#诊断SQL
show profile [all/cpu/ipc/memory/source/block io] for query  + ID;
/*
使用需要注意的：
converting HEAP to MyISAM  查询结果太大，内存都不够用了往磁盘上搬
Creating tmp table 创建临时表，拷贝数据都临时表，用完再删除
Copying to tmp table on disk把内存中临时表复制到磁盘，很危险
locked
*/

5. 全局查询日志

#永远永远不要在生产环境开启这个功能，只能测试
#开启
set global general_log=1;
set global log_output='TABLE';
#此后，编写的sql语句，将会记录到MySQL库中的general_log表
select * from mysql.general_log;