In-depth analysis of MySQL query interception

#Assume that table a has 10,000 data and table b has 20 data select * from a join b on a.bid =b.id

Table a drives table b as follows:
for 20 data to match 10000 data (based on the join condition on a.bid=b.id, perform a B+ tree search)
The number of searches is: 20+ log10000
Table b drives table a to match 20 data for 10,000 data (based on the join condition on a.bid=b.id, a B+ tree search is performed). The number of searches is: 10,000+ log20

#If in is used, the internal table B drives the external table A
select * from A where id in (select id from B)
#Using exists, external table A drives internal table B
select * from A where exists(select 1 from B where B.id = A.id)

Always remember that when the B table data set is smaller than the A table data set, use in
When the data set of table A is smaller than that of table B, use 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 a composite index create index idx_A_ageBirth on tblA(age, birth); 

select * from user where name = "zs" order by age
#Dual sort 1) Find the first primary key id that satisfies name = 'zs' from name
2) Take the entire row based on the primary key id, and put the sort fields age and primary key id into the sort buffer. 3) Take the next primary key id from name that satisfies the record with name = 'zs'.
4) Repeat 2 and 3 until name = 'zs' is no longer satisfied
5) Sort the age field and primary key id in sort_buffer according to the age field 6) Traverse the sorted id and age field, return to the original table according to the id value, retrieve the values ​​of all fields and return them to the client #Single-way sorting 1) Find the first primary key id from name that meets the condition name = 'zs'
2) Take the entire row based on the primary key id, take out the values ​​of all fields, and store them in the sort_buffer (sort buffer) 3) Find the next primary key id that satisfies the condition name = 'zs' from the index name
4) Repeat steps 2 and 3 until name = 'zs' is no longer satisfied
5) Sort the data in sort_buffer by the field age and return the result to the client

question:
 Since the single-pass algorithm is an improved one, it is generally better than the dual-pass algorithm in the sort_buffer. Method B takes up much more space than method A because method B takes out all fields. Therefore, the total size of the data taken out may exceed the capacity of the sort_buffer, resulting in only taking out data up to the capacity of the sort_buffer each time for sorting (creating a tmp file, merging multiple passes), taking out data up to the capacity of the sort_buffer, and sorting again... This will result in multiple I/Os.
Optimization strategy:
 Increase the setting of the sort_buffer_size parameter Increase the setting of the max_length_for_sort_data parameter Notes:
  Selecting * when ordering by is a big taboo. Only query the required fields. Because the more fields there are, the more data must be stored in memory, which results in fewer data columns being loaded per I/O.

#Check whether slow log is enabled show variables like 'slow_query_log%';
# Enable the slow query log. To make it permanent, set global slow_query_log = 1 in my.cnf; 

#The default threshold time for viewing slow query logs is 10s
show variables like 'long_query_time%';
#Set to 3s. Restart will fail. If you want it to be permanent, set global long_query_time = 3 in my.cnf
#To view again, you need to switch windows to view show variables like 'long_query_time%'; 

[mysqld]
#Persistent slow query log slow_query_log=1;
slow_query_log_file=/var/lib/mysql/hadoop102-slow.log
long_query_time = 3;
log_output=FILE

#Query wait 4s
select sleep(4);

#In Linux system, view the slow query log cat /var/lib/mysql/hadoop102-slow.log

show global status like '%Slow_queries%';

#Get the 10 SQL statements that return the most records
mysqldumpslow -sr -t 10 /var/lib/mysql/hadoop102-slow.log
#Get the 10 most visited SQLs
mysqldumpslow -sc -t 10 /var/lib/mysql/hadoop102-slow.log
#Get the first 10 queries sorted by time that contain left joins mysqldumpslow -st -t 10 -g "left join" /var/lib/mysql/hadoop102-slow.log
#These commands are combined with | and more to use mysqldumpslow -sr -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;

#View binlog status show variables like 'log_bin%';
#Add trusted storage function creators set global log_bin_trust_function_creators = 1; 

# Define two $$ to indicate the end (replace the original ;)
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 $$

#Insert 10 records into the department table DELIMITER;
CALL insert_dept(100, 10);
#Insert 500,000 records into the employee table CALL insert_emp(100001, 500000);

#Check whether Show Profile is enabled show variables like 'profiling%';
#Open 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

# Enable general_log=1
# Record the path of the log file general_log_file=/path/logfile
# Output format log_output=FILE

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

select * from mysql.general_log;