MySQL查询截取的深入分析

#假设 a表10000数据，b表20数据
select * from a join b on a.bid =b.id

a表驱动b表为:
for 20条数据
 匹配10000数据（根据on a.bid=b.id的连接条件，进行B+树查找）
查找次数为:20+ log10000
b表驱动a表为
for 10000条数据
 匹配20条数据（根据on a.bid=b.id的连接条件，进行B+树查找）查找次数为:10000+ log20

#采用in则是，内表B驱动外表A
select * from A where id in (select id from B)
#采用exists则是，外表A驱动内表B
select * from A where exists(select 1 from B where B.id = A.id)

永远记住小表驱动大表
当 B 表数据集小于 A 表数据集时，使用 in
当 A 表数据集小于 B 表数据集时，使用 exist

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 user where name = "zs" order by age
#双路排序
1)从 name 找到第一个满足 name = 'zs' 的主键id
2)根据主键 id 取出整行，把排序字段 age 和主键 id 这两个字段放到 sort buffer(排序缓存) 中
3)从name 取下一个满足 name = 'zs' 记录的主键 id
4)重复 2、3 直到不满足 name = 'zs'
5)对 sort_buffer 中的字段 age 和主键 id 按照字段 age进行排序
6)遍历排序好的 id 和字段 age ，按照 id 的值回到原表中取出 所有字段的值返回给客户端

#单路排序
1)从name找到第一个满足 name ='zs' 条件的主键 id
2)根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer(排序缓存)中
3)从索引name找到下一个满足 name = 'zs' 条件的主键 id
4)重复步骤 2、3 直到不满足 name = 'zs'
5)对 sort_buffer 中的数据按照字段 age 进行排序，返回结果给客户端

问题：
 由于单路是改进的算法，总体而言好过双路
 在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排…… 从而会导致多次I/O。
优化策略：
 增大sort_buffer_size参数的设置
 增大max_length_for_sort_data参数的设置
注意事项:
　　Order by时select *是一个大忌，只Query需要的字段。因为字段越多在内存中存储的数据也就也多，这样就导致每次I/O能加载的数据列越少。

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop102-slow.log
#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/hadoop102-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hadoop102-slow.log
#这些命令时结合 | 和more使用
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop102-slow.log | more

CREATE TABLE dept
(
 deptno int unsigned primary key auto_increment,
 dname varchar(20) not null default '',
 loc varchar(8) not null default ''
)ENGINE=INNODB DEFAULT CHARSET=utf8;

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=utf8;

#查看binlog状态
show variables like 'log_bin%';
#添加可以信任存储函数创建者
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 '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),'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 $$

#向 部门表插入10条数据
DELIMITER ;
CALL insert_dept(100, 10);
#向 员工表插入50w条数据
CALL insert_emp(100001, 500000);

#查看 Show Profile 是否开启
show variables like ‘profiling%';
#开启 Show Profile
set profiling=on;

select * from emp group by id%10 limit 150000;
select * from emp group by id%10 limit 150000;
select * from emp group by id%10 order by 5;
select * from emp
select * from dept
select * from emp left join dept on emp.deptno = dept.deptno

# 开启
general_log=1
# 记录日志文件的路径
general_log_file=/path/logfile
# 输出格式
log_output=FILE

set global general_log=1;
set global log_output='TABLE';

select * from mysql.general_log;