Mysql——查询截取分析(四)

-----分析-------
1.观察，至少跑一天，看看生产的慢sql情况
2.开启慢查询日志，设置阙值，比如超过5秒钟的就是慢sql,并将它抓取出来
3.explain+慢sql分析
4.show profile
5.运维经理 或者 dba，进行sql数据库服务器的参数调优

总结：
1.慢查询的开启并捕获
2.explain+慢sql分析
3.show profile 查询sql在mysql服务器里面的执行细节和生命周期情况
4.sql数据库服务器的参数调优

查询优化

小表驱动大表

为什么要小表驱动大表呢?

类似循环嵌套

for(int i=5;.......) //小表有5条数据
{
     for(int j=1000;......)//大表有1000条数据
     {}
}

如果小的循环在外层，对于数据库连接来说就只连接5次，进行5000次操作，如果1000在外，则需要进行1000次数据库连接，从而浪费资源，增加消耗。这就是为什么要小表驱动大表。

数据准备：
https://www.cnblogs.com/developer_chan/p/9229845.html

案例演示

①当B表的数据集小于A表数据集时，用in优于exists。

select *from tb_emp_bigdata A where A.deptno in (select B.deptno from tb_dept_bigdata B)

B表为tb_dept_bigdata：100条数据，A表tb_emp_bigdata：5000条数据。
用in的查询时间为：

将上面sql转换成exists：

SELECT * from tb_emp_bigdata A WHERE 
EXISTS(SELECT 1 from tb_dept_bigdata B WHERE B.deptno=A.deptno );

用exists的查询时间：

经对比可看到，在B表数据集小于A表的时候，用in要优于exists，当前的数据集并不大，所以查询时间相差并不多。

②当A表的数据集小于B表的数据集时，用exists优于in。

select *from tb_dept_bigdata A where A.deptno in(select B.deptno from tb_emp_bigdata B);

用in的查询时间为：

将上面sql转换成exists：

select *from tb_dept_bigdata A where exists(select 1 from tb_emp_bigdata B where B.deptno=A.deptno);

用exists的查询时间：

由于数据量并不是很大，因此对比并不是难么的强烈。

结论：

总结

下面结论都是针对in或exists的。
in后面跟的是小表，exists后面跟的是大表。
简记：in小，exists大。
对于exists
select .....from table where exists(subquery);
可以理解为：将主查询的数据放入子查询中做条件验证，根据验证结果（true或false）来决定主查询的数据是否得以保留。

order by关键字优化

前言：在使用order by时，经常出现Using filesort，因此对于此类sql语句需尽力优化，使其尽量使用Using index。
准备：

drop table if exists test;
create table test(
id int primary key auto_increment,
c1 varchar(10),
c2 varchar(10),
c3 varchar(10),
c4 varchar(10),
c5 varchar(10)
) ENGINE=INNODB default CHARSET=utf8;

insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

创建索引：

CREATE index idx_test_c1234 on test(c1,c2,c3,c4);
show index from test;

根据Case分析order by的使用情况

https://www.cnblogs.com/developer_chan/p/9225638.html

Case 1：

分析：
①在c1,c2,c3,c4上创建了索引，直接在c1上使用范围，导致了索引失效，全表扫描：type=ALL，ref=Null。因为此时c1主要用于排序，并不是查询。
②使用c1进行排序，出现了Using filesort。
③解决方法：使用覆盖索引。

Case 1.1：

分析：
排序时按照索引的顺序，所以不会出现Using filesort。

Case 1.2：

分析：
出现了Using filesort。原因：排序用的c2，与索引的创建顺序不一致，对比Case1.1可知，排序时少了c1（带头大哥），因此出现Using filesort。

Case 1.3：

分析：
出现了Using filesort。因为排序索引列与索引创建的顺序相反，从而产生了重排，也就出现了Using filesort。

Case 2：

分析：
直接使用c2进行排序，出现Using filesort，因为不是从最左列索引开始排序的（没有带头大哥）。

Case 2.1：

分析：
排序使用了索引顺序（带头大哥在），因此不会出现Using filesort。

Case 2.2：

分析：
虽然排序的字段列与索引顺序一样，且order by默认升序，这里c2 desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。

总结

①MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
②order by满足两种情况会使用Using index。
#1.order by语句使用索引最左前列。
#2.使用where子句与order by子句条件列组合满足索引最左前列。
③尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最佳左前缀法则。
④如果order by的条件不在索引列上，就会产生Using filesort
⑤提升order by速度的方式：
#1.在使用order by时，不要用select *，只查询所需的字段。
因为当查询字段过多时，会导致sort_buffer不够，从而使用多路排序或进行多次I/O操作。
#2.尝试提高sort_buffer_size。
#3.尝试提高max_length_for_sort_data。
⑥group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最佳左前缀法则。当无法使用索引列的时候，也要对sort_buffer_size和max_length_for_sort_data参数进行调整。注意where高于having，能写在where中的限定条件就不要去having限定了。
⑦附上一张从视频中截取出来的总结图。

慢查询日志

前言：慢查询日志是MySQL提供的一种日志记录，它记录MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的sql语句，该sql语句会被记录到慢查询日志中。慢查询日志主要与explain进行联合分析。

查看是否开启以及如何开启

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要，一般不建议开启该参数，因为开启慢查询日志或多或少会带来一定的性能影响。

查看是否开启：

show VARIABLES like "%slow_query_log%";

开启慢查询日志：0关闭

set GLOBAL slow_query_log=1;

注：使用该命令开启慢查询只对当前数据库生效，MySQL重启失效，如果要配置长期有效，请在my.cnf中进行配置。

慢查询的相关参数

查看慢查询的阈值时间，该值有long_query_time控制。默认情况下为10秒：

show VARIABLES like "%long_query_time%";

注：假如sql语句的运行时间正好等于long_query_time时，sql并不会被记录下来，因此判断慢查询是sql的执行时间大于long_query_time，而不是大于等于。

设置long_query_time的值：

set GLOBAL long_query_time=5;

当设置long_query_time值后，查看其值并没有变化，解决方式：
关闭当前连接，重新开一个新的连接，再重新查看即可。

查看慢查询sql的数目：
执行两次慢sql：

select sleep(6);

慢sql数目：

show GLOBAL STATUS like "%Slow_queries%";

查看慢查询日志文件：

可以查看日志地址
SHOW VARIABLES LIKE "%slow%"

配置文件里配置：

可以永久生效

使用mysqldumpslow分析慢查询日志文件

在生产环境中，如果要手动分析日志，查找，分析sql，显然是个体力活，mysql提供了日志分析工具mysqldumpslow。

-s 按照那种方式排序
    c：访问计数
    l：锁定时间
    r:返回记录
    al：平均锁定时间
    ar：平均访问记录数
    at：平均查询时间
-t 是top n的意思，返回多少条数据。
-g 可以跟上正则匹配模式，大小写不敏感。

查看命令：

[root@iz2ze1bne18a1ie99v0zvtz ~]# mysqldumpslow --help
-bash: mysqldumpslow: command not found

[root@iz2ze1bne18a1ie99v0zvtz ~]# ln -s /usr/local/mysql/bin/mysqldumpslow /usr/bin

[root@iz2ze1bne18a1ie99v0zvtz ~]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

使用案例

• 得到返回记录最多的20个sql：mysqldumpslow -s r -t 20 sqlslow.log
• 得到平均访问次数最多的20条sql：mysqldumpslow -s ar -t 20 sqlslow.log
• 得到平均访问次数最多,并且里面含有ttt字符的20条sql：mysqldumpslow -s ar -t 20 -g "ttt" sqldlow.log
• 建议结合 | 和more 使用，否则可能出现爆屏

批量数据脚本

准备

创建tb_dept_bigdata（部门表）：

create table tb_emp_bigdata(
id int unsigned primary key auto_increment,
empno mediumint unsigned not null default 0,/*编号*/
empname 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;

创建tb_emp_bigdata（员工表）：

create table tb_emp_bigdata(
id int unsigned primary key auto_increment,
empno mediumint unsigned not null default 0,/*编号*/
empname 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;

开启log_bin_trust_function_creators参数：

show VARIABLES like "%log_bin_trust_function_creators%"

可通过set global log_bin_trust_function_creators=1的形式开启该功能，也可通过在my.cnf中永久配置的方式开启该功能，在[mysqld]下配置log_bin_trust_function_creators=1。

创建随机生成字符串的函数

delimiter $$
drop function if exists rand_string;
create function rand_string(n int) returns varchar(255)
begin
declare chars_str varchar(52) 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其实就是告诉MySQL解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号;

创建随机生成编号的函数

delimiter $$
drop function if exists rand_num;
create function rand_num() returns int(5)
begin
declare i int default 0;
set i=floor(100+rand()*100);
return i;
end $$

查看函数是否创建成功：show function status;

创建往tb_dept_bigdata表中插入数据的存储过程

delimiter $$
drop procedure if exists insert_dept;
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 tb_dept_bigdata (deptno,dname,loc) values(rand_num(),rand_string(10),rand_string(8));
until i=max_num
end repeat;
commit;
end $$

创建往tb_emp_bigdata表中插入数据的存储过程

delimiter $$
drop procedure if exists insert_emp;
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 tb_emp_bigdata (empno,empname,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'developer',0001,curdate(),2000,400,rand_num());
until i=max_num
end repeat;
commit;
end $$

查看存储过程是否创建成功：show procedure status;

具体执行过程批量插入数据

call insert_dept(100,100)

call insert_emp(1000,500000);

注：对于部门表的deptno和员工表中deptno的数据都使用了rand_num()函数进行赋值，确保两边的值能对应。

删除函数与存储过程

删除函数

drop function rand_num;
drop function rand_string;

删除存储过程

drop procedure insert_dept;
drop procedure insert_emp;

总结

①注意mysql中函数和存储过程的写法。
②注意存储过程的调用，call procedurename。
③注意开启对函数的信任，log_bin_trust_function_creators参数。

show profile

Show Profile是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。

查看是否开启show profile功能

SET profiling=1;  0关闭
show VARIABLES like "profiling";

然后执行如下查询语句：在这里插入代码片

select *from tb_emp_bigdata group by id%10 limit 150000;
select *from tb_emp_bigdata group by id%20 order by 5;

通过show profiles查看结果

show profiles;

Duration:持续时间(耗时)
注：使用navicat会出现一些别的语句。

使用show profile对sql语句进行诊断

/*Query_ID为#3步骤中show profiles列表中的Query_ID*/
show profile cpu,block io for query Query_ID;
show profile cpu,block io for query 15;

show profile的常用查询参数:
①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：创建临时表。先拷贝数据到临时表，用完后再删除临时表。
③Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险！！！
④locked。
如果在show profile诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化。

总结

#1.show profile默认是关闭的，并且开启后只存活于当前会话，也就说每次使用前都需要开启。

#2.通过show profiles查看sql语句的耗时时间，然后通过show profile命令对耗时时间长的sql语句进行诊断。

#3.注意show profile诊断结果中出现相关字段的含义，判断是否需要优化sql语句。

#4.可更多的关注MySQL官方文档，获取更多的知识。

全局查询日志

全局查询日志用于保存所有的sql执行记录，该功能主要用于测试环境，在生产环境中永远不要开启该功能。

通过命令开启该功能。

开启
set global general_log=1;
日志输出
set global log_output='TABLE';
日志查看
select * from mysql.general_log;