MySQL查询截取分析
⼀、查询优化
1，mysql的调优⼤纲
1. 慢查询的开启并捕获
2. explain+慢SQL分析
3. show profile查询SQL在Mysql服务器⾥⾯的执⾏细节和⽣命周期情况
4. SQL数据库服务器的参数调优
2，⼩表驱动⼤表
  mysql的join实现原理是，以驱动表的数据为基础，“嵌套循环”去被驱动表匹配记录。驱动表的索引会失效，⽽被驱动表的索引有效。
#假设 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
3，in和exists
  exists的使⽤
1. EXISTS 语法：EXISTS(subquery) 只返回TRUE或FALSE，因此⼦查询中的SELECT *也可以是SELECT 1或其他，官⽅说法是实际执⾏时会忽略SELECT清单，因此没有区别
SELECT ... FROM table WHERE EXISTS(subquery)
该语法可以理解为：将查询的数据，放到⼦查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。
2. EXISTS⼦查询的实际执⾏过程可能经过了优化⽽不是我们理解上的逐条对⽐，如果担忧效率问题，可进⾏实际检验以确定是否有效率问题。
3. EXISTS⼦查询往往也可以⽤条件表达式、其他⼦查询或者JOIN来替代，何种最优需要具体问题具体分析
#采⽤in则是，内表B驱动外表A
select*from A where id in (select id from B)
#采⽤exists则是，外表A驱动内表B
select*from A where exists(select1from B where B.id = A.id)
  结论：
1. 永远记住⼩表驱动⼤表
2. 当 B 表数据集⼩于 A 表数据集时，使⽤ in
3. 当 A 表数据集⼩于 B 表数据集时，使⽤ exist
4，order by
创建表
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);
order by命中索引的情况
order by未命中索引的情况
结论：
MySQL⽀持两种排序⽅式：Using index和Using filesort。filesort效率较低，⽽要使⽤index⽅式排序需满⾜两种使⽤条件尽可能在索引列上完成排序操作，遵照索引的最佳左前缀order by语句⾃⾝使⽤索引的最左前列
使⽤where⼦句与order by⼦句条件列组合满⾜最左前列
如果order by不在索引列上，会使⽤filesort算法：双路排序和单路排序
MySQL4.1之前是使⽤双路排序，字⾯意思是两次扫描磁盘，最终得到数据。读取⾏指针和order by列，对他们进⾏排序，然后扫描已经排序好的列表，按照列表中的值重新从列
表中读取对应的数据传输
从磁盘读取查询需要的所有列，按照order by列在buffer对它们进⾏排序，然后扫描排序后的列表进⾏输出，它的效率更快⼀些，避免了第⼆次读取数据，并且把随机IO变成顺序
IO，但是它会使⽤更多的空间，因为它把每⼀⾏都保存在内存中了。
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容量⼤⼩的数据，进⾏排序（创建tm 优化策略：
增⼤sort_buffer_size参数的设置
增⼤max_length_for_sort_data参数的设置
注意事项:
  Order by时select *是⼀个⼤忌，只Query需要的字段。因为字段越多在内存中存储的数据也就也多，这样就导致每次I/O能加载的数据列越少。
5，group by优化
1)group by实质是先排序后进⾏分组，遵照索引的最佳左前缀
2)当⽆法使⽤索引列，增⼤max_length_for_sort_data参数的设置+增⼤sort_buffer_size参数的设置
3)where⾼于having，能写在where限定的条件就不要去having限定了
4)其余的规则均和order by⼀致
⼆、慢查询⽇志
1，慢查询⽇志是什么?
MySQL的慢查询⽇志是MySQL提供的⼀种⽇志记录，它⽤来记录在MySQL中响应时间超过阀值的语句，具体指运⾏时间超过long_query_time值的SQL，则会被记录到慢查询⽇志
中。
long_query_time的默认值为10，意思是运⾏10秒以上的SQL语句会被记录下来
由他来查看哪些SQL超出了我们的最⼤忍耐时间值，⽐如⼀条sql执⾏超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进⾏全⾯分析。
2，慢查询⽇志的开启
  默认情况下，MySQL的慢查询⽇志是没有开启的。如果不是调优需要的话，⼀般不建议启动该参数，因为开启慢查询⽇志会影响到性能，慢查询⽇志⽀持将⽇志记录写⼊⽂件。a）开启慢查询⽇志
#查看是否开启慢⽇志
show variables like 'slow_query_log%';
#开启慢查询⽇志，想要永久有效在myf中设置
set global slow_query_log = 1 ;
b）设置慢查询⽇志的阈值
#查看慢查询⽇志的阈值时间默认为10s
show variables like 'long_query_time%';
#设置为3s 重启失效，想要永久有效在myf中设置
set global long_query_time = 3
#再次查看，需要切换窗⼝查看
show variables like 'long_query_time%';
c）持久化慢查询⽇志和时间阈值
[mysqld]
#持久化慢查询⽇志
slow_query_log=1；
slow_query_log_file=/var/lib/mysql/hadoop102-slow.log
long_query_time=3；
log_output=FILE
d）慢查询案例
#查询等待4s
select sleep(4);
#在linux系统中，查看慢查询⽇志
cat /var/lib/mysql/hadoop102-slow.log
e）查看当前系统中存在的慢查询⽇志条数
show global status like '%Slow_queries%';
3，⽇志分析命令mysqldumpslow
a）参数解释
-s：是表⽰按何种⽅式排序
c：访问次数
l：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
-t：即为返回前⾯多少条的数据
-g：后边搭配⼀个正则匹配模式，⼤⼩写不敏感的
b）常⽤⽅法
#得到返回记录集最多的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
三、批量写数据脚本
1，建表
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 default0,
ename varchar(20) not null default'',
job varchar(9) not null default'',
mgr mediumint unsigned not null default0,
hiredate date not null,
sal decimal(7,2) not null,
comm decimal(7,2) not null,
deptno mediumint unsigned not null default0
)
ENGINE=INNODB DEFAULT CHARSET=utf8;
View Code
2，设置是否可以信任存储函数创建者
#查看binlog状态
show variables like'log_bin%';
#添加可以信任存储函数创建者
set global log_bin_trust_function_creators =1;
3，创建函数
随机产⽣字符串的函数
# 定义两个 $$ 表⽰结束 (替换原先的;)
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 default0;
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 default0;
set i=floor(100+rand()*10);
return i;
end $$
4，创建存储过程
创建往emp表中插⼊数据的存储过程
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default0;
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 $$
创建往dept表中插⼊数据的存储过程
delimiter $$
create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default0;
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 $$
5，调⽤存储过程⽣成数据
#向部门表插⼊10条数据
DELIMITER ;
CALL insert_dept(100, 10);
#向员⼯表插⼊50w条数据
CALL insert_emp(100001, 500000);
四、show profiles 1，介绍
show profile是mysql提供可以⽤来分析当前会话中语句执⾏的资源消耗情况。可以⽤于SQL的调优测量。
默认情况下，参数处于关闭状态，并保存最近15次的运⾏结果
2，开启
#查看 Show Profile 是否开启
show variables like 'profiling%';
#开启 Show Profiles
set profiling=on;
3，使⽤show profiles
创建测试数据
select*from emp group by id%10 limit 150000;
select*from emp group by id%10 limit 150000;
select*from emp group by id%10order by5;
select*from emp
select*from dept
select*from emp left join dept on emp.deptno = dept.deptno
执⾏show profiles
执⾏ show profile cpu, block io for query Query_ID;
检索参数
ALL：显⽰所有的开销信息
BLOCK IO：显⽰块IO相关开销
CONTEXT SWITCHES：上下⽂切换相关开销
CPU：显⽰CPU相关开销信息
IPC：显⽰发送和接收相关开销信息
MEMORY：显⽰内存相关开销信息
PAGE FAULTS：显⽰页⾯错误相关开销信息
SOURCE：显⽰和Source_function，Source_file，Source_line相关的开销信息
SWAPS：显⽰交换次数相关开销的信息
返回结果
converting HEAP to MyISAM：查询结果太⼤，内存都不够⽤了往磁盘上搬了。
Creating tmp table：创建临时表，mysql 先将拷贝数据到临时表，然后⽤完再将临时表删除

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。