MySQL查詢截取的深入分析

#假設(shè) a表10000數(shù)據(jù)，b表20數(shù)據(jù)
select * from a join b on a.bid =b.id

a表驅(qū)動(dòng)b表為:
for 20條數(shù)據(jù)
 匹配10000數(shù)據(jù)（根據(jù)on a.bid=b.id的連接條件，進(jìn)行B+樹查找）
查找次數(shù)為:20+ log10000
b表驅(qū)動(dòng)a表為
for 10000條數(shù)據(jù)
 匹配20條數(shù)據(jù)（根據(jù)on a.bid=b.id的連接條件，進(jìn)行B+樹查找）查找次數(shù)為:10000+ log20

#采用in則是，內(nèi)表B驅(qū)動(dòng)外表A
select * from A where id in (select id from B)
#采用exists則是，外表A驅(qū)動(dòng)內(nèi)表B
select * from A where exists(select 1 from B where B.id = A.id)

永遠(yuǎn)記住小表驅(qū)動(dòng)大表
當(dāng) B 表數(shù)據(jù)集小于 A 表數(shù)據(jù)集時(shí)，使用 in
當(dāng) A 表數(shù)據(jù)集小于 B 表數(shù)據(jù)集時(shí)，使用 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());
#創(chuàng)建復(fù)合索引
create index idx_A_ageBirth on tblA(age, birth);

select * from user where name = "zs" order by age
#雙路排序
1)從 name 找到第一個(gè)滿足 name = 'zs' 的主鍵id
2)根據(jù)主鍵 id 取出整行，把排序字段 age 和主鍵 id 這兩個(gè)字段放到 sort buffer(排序緩存) 中
3)從name 取下一個(gè)滿足 name = 'zs' 記錄的主鍵 id
4)重復(fù) 2、3 直到不滿足 name = 'zs'
5)對(duì) sort_buffer 中的字段 age 和主鍵 id 按照字段 age進(jìn)行排序
6)遍歷排序好的 id 和字段 age ，按照 id 的值回到原表中取出 所有字段的值返回給客戶端

#單路排序
1)從name找到第一個(gè)滿足 name ='zs' 條件的主鍵 id
2)根據(jù)主鍵 id 取出整行，取出所有字段的值，存入 sort_buffer(排序緩存)中
3)從索引name找到下一個(gè)滿足 name = 'zs' 條件的主鍵 id
4)重復(fù)步驟 2、3 直到不滿足 name = 'zs'
5)對(duì) sort_buffer 中的數(shù)據(jù)按照字段 age 進(jìn)行排序，返回結(jié)果給客戶端

問題：
 由于單路是改進(jìn)的算法，總體而言好過(guò)雙路
 在sort_buffer中，方法B比方法A要多占用很多空間，因?yàn)榉椒˙是把所有字段都取出，所以有可能取出的數(shù)據(jù)的總大小超出了sort_buffer的容量，導(dǎo)致每次只能取sort_buffer容量大小的數(shù)據(jù)，進(jìn)行排序（創(chuàng)建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排…… 從而會(huì)導(dǎo)致多次I/O。
優(yōu)化策略：
 增大sort_buffer_size參數(shù)的設(shè)置
 增大max_length_for_sort_data參數(shù)的設(shè)置
注意事項(xiàng):
　　Order by時(shí)select *是一個(gè)大忌，只Query需要的字段。因?yàn)樽侄卧蕉嘣趦?nèi)存中存儲(chǔ)的數(shù)據(jù)也就也多，這樣就導(dǎo)致每次I/O能加載的數(shù)據(jù)列越少。

#查看是否開啟慢日志
show variables like 'slow_query_log%';
#開啟慢查詢?nèi)罩?，想要永久有效在my.cnf中設(shè)置
set global slow_query_log = 1 ;

#查看慢查詢?nèi)罩镜拈撝禃r(shí)間 默認(rèn)為10s
show variables like 'long_query_time%';
#設(shè)置為3s 重啟失效，想要永久有效在my.cnf中設(shè)置
set global long_query_time = 3
#再次查看，需要切換窗口查看
show variables like 'long_query_time%';

[mysqld]
#持久化慢查詢?nèi)罩?
slow_query_log=1；
slow_query_log_file=/var/lib/mysql/hadoop102-slow.log
long_query_time=3；
log_output=FILE

#查詢等待4s
select sleep(4); 

#在linux系統(tǒng)中，查看慢查詢?nèi)罩?
cat /var/lib/mysql/hadoop102-slow.log

show global status like '%Slow_queries%';

#得到返回記錄集最多的10個(gè)SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop102-slow.log
#得到訪問次數(shù)最多的10個(gè)SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/hadoop102-slow.log
#得到按照時(shí)間排序的前10條里面含有左連接的查詢語(yǔ)句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hadoop102-slow.log
#這些命令時(shí)結(jié)合 | 和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狀態(tài)
show variables like 'log_bin%';
#添加可以信任存儲(chǔ)函數(shù)創(chuàng)建者
set global log_bin_trust_function_creators = 1;

# 定義兩個(gè) $$ 表示結(jié)束 (替換原先的;)
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條數(shù)據(jù)
DELIMITER ;
CALL insert_dept(100, 10);
#向 員工表插入50w條數(shù)據(jù)
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