3查询截获分析

分析：

1观察，至少跑一天，看看生产的慢sql情况。

2开启慢查询日志，设置阙值，比如超过5秒钟的就是慢sql，并将它抓出来。

3explain+慢sql分析

4show profile

5运维经理或dba，进行sqql数据库服务器的参数调优。

总结：

1慢查询的开启并捕获。

2explain+慢sql分析

3show profile 查询sql在mysql服务器里面的执行细节和生命周期情况

4sql数据库服务器的参数调优。

3.1查询优化

3.1.1永远小表驱动大表

【类似嵌套循环，外层小，内层大】

原理RBO，【先查的应该时小的数据集】

select * from A where **id in(select id from B)**等价于

for select id from B

for select * from A where A.id=B.id

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

select * from A where exsits(select 1 from B **whereB.id=A.id)**等价于

for select * from A

for select * from B where A.id=B.id

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

exsits的语法

select … from table where exsits(子查询)应该这样理解：将主查询的数据放到子查询中做条件验证，根据验证结果true或false来决定主查询的数据结果是否得以保留。子查询写select *和select 1 或select x 没有区别。

3.1.2order by关键字优化【重点关注order by子句写了啥】

【为排序使用索引】

mysql两种排序方式：文件排序filesort或者扫描有序索引排序index

mysql能为 查询和排序使用相同的索引。【一举两得】

1. 关注order by 子句，尽量使用 index方式排序【首先得索引覆盖】，避免使用filesort方式排序。【index效率高】【这里关注会不会产生filesort】

   满足两种情况会使用index方式排序。1.order by语句使用索引最左前列，2使用where子句与order by子句条件组合满足索引最左前列。

   explain select age，birth from tb1A where age>20 order by age;

   | id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

   |1 | simple | tb1A | index|idx_A_agebirth|idx_A_agebirth|9|null|3|using where；using index

   explain select age，birth from tb1A where age>20 order by age，birth;

   | id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

   |1 | simple | tb1A | index|idx_A_agebirth|idx_A_agebirth|9|null|3|using where；using index

   explain select age，birth from tb1A where age>20 order by birth;【带头大哥是个范围】

   | id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

   |1 | simple | tb1A | index|idx_A_agebirth|idx_A_agebirth|9|null|3|using where；using index；using filesort

   explain select age，birth from tb1A where age>20 order by birth,age;

   | id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

   |1 | simple | tb1A | index|idx_A_agebirth|idx_A_agebirth|9|null|3|using where；using index；using filesort

   explain select age，birth from tb1A order by birth;

   | id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

   |1 | simple | tb1A | index|null|idx_A_agebirth|9|null|3|using index；using filesort

   explain select age，birth from tb1A where birth>“2015-2-3 00:00:00:00” order by birth;

   | id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

   |1 | simple | tb1A | index|null|idx_A_agebirth|9|null|3|using where；using index；using filesort

   explain select age，birth from tb1A where birth>“2015-2-3 00:00:00:00” order by age;【带头大哥在，不会产生filesort】

   | id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

   |1 | simple | tb1A | index|null|idx_A_agebirth|9|null|3|using where；using index

   explain select age，birth from tb1A order by age asc，birth desc;【order by默认升序，建好的顺序是升的，sql语句出现降序，建好的索引用不上，就会内部产生新的，就会出现filesort】

   | id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

   |1 | simple | tb1A | index|null|idx_A_agebirth|9|null|3|using index；using filesort

2. 尽可能在索引列上完成排序操作，遵照索引建的最左前缀原则【order by后面字段的顺序要按最左前缀】

   索引是abc，

   order by a

   order by ab

   order by abc

   order by a desc, b desc, c desc

   还有一种情况是带头大哥本身就是常量，

   where a=1 order by b,c

   where a=1 and b=2 order by c

   where a=1 and b>2order by b,c

   不能使用索引排序的情况：【会出现filesort】

   1【排序不一致】order by a asc,b desc, c desc

   2【没有带头大哥且带头大哥不是常数】where d=1 order by b,c

   3【中间断了】where a=1 order by c【没有b】

   4【不是索引的一部分】where a=1 order by a,d

   5【对于排序来说，多个相等条件也是范围查询】where a in(…) order by b,c

3. 如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

   【双路排序】：两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取相应的数据输出。从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

   【单路排序】mysql4.1之后出现了改进算法—单路排序。从磁盘读取查询需要的所有列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出，他的效率更快，**避免了二次读取数据，并且把随机IO变成了顺序IO，**但是会用很多空间，因为他把每一行都保存在内存了。

   单路的问题：如果一次拿不完，还不如双路算法。在sort_buffer中，单路要比双路占用更多空间，因为单路是吧所有字段都取出，**有可能取出的数据总大小超过了sort_buffer的容量，**导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并）排完再取再排，，，从而多次IO，这样反而得不偿失。

4. 优化策略：

   • 增大sort_buffer_size参数的设置
   • 增大max_length_for_sort_data参数的设置

5. 总结：提高order by速度

   • 不要使用select * ，只是select需要的字段。当查询的字段大小总和小于max_length_for_sort_data，而且排序字段不是text|blob类型，会使用单路排序；否则多路排序；两种算法都有可能超出sort_buffer的容量，超出之后会创建tmp文件进行合并排序，导致多次IO，但是单路的风险更大，所以要提高sort_buffer_size。
   • 提高sort_buffer_size，不管哪种算法，不过也要根据系统的能力，这个参数是针对每个进程的。
   • 提高max_length_for_sort_data，会增加使用单路算法的概率。但是设得太高，总量超出sort_buffer_size的概率增大，会出现高磁盘IO活动和低的处理器使用率。

3.1.3group by关键字优化

group by 实质是先排序后进行分组，遵照索引建的最佳左前缀。

但无法使用索引列，增大sort_buffer_size参数的设置+增大max_length_for_sort_data参数的设置

where高于having，能写在where限定的条件就不要去having限定了。

3.2慢查询日志

3.2.1是什么

Mysql的慢查询日志是Mysql提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值【大于会，等于并不会被记录】的语句。具体是指运行时间超过long_query_time的sql，则会被记录在慢查询日志中。通过慢查询日志，可以查找出哪些查询语句的执行效率低，以便进行优化。

3.2.2怎么弄

如果不是调优需要，**一般不建议启动该参数，**因为开启慢查询日志会或多或少带来一定的性能影响。默认情况下，慢查询日志功能是关闭的。可以通过以下命令查看是否开启慢查询日志功能。

1. 查看是否开启

   mysql> SHOW VARIABLES LIKE 'slow_query_log%';
   +---------------------+---------------------------------------------------------------------+
   | Variable_name       | Value                                                               |
   +---------------------+---------------------------------------------------------------------+
   | slow_query_log      | OFF                                                                 |
   | slow_query_log_file | C:\ProgramData\MySQL\MySQL Server 5.7\Data\LAPTOP-UHQ6V8KP-slow.log |
   +---------------------+---------------------------------------------------------------------+
   2 rows in set, 1 warning (0.02 sec)
   

   参数说明如下：

   • slow_query_log：慢查询开启状态,默认情况下是关闭的，通过设置slow_query_log的值来开启
   • slow_query_log_file：慢查询日志存放的位置（一般设置为 MySQL 的数据存放目录）如果没有给定这个参数值的话，系统默认会给一个缺省的文件host_name-slow.log
   • long_query_time：查询超过多少秒才记录，默认10s，这个值可以用命令修改也可以在配置文件修改。

2. 如何开启

   SET GLOBAL slow_query_log=1

   如果要永久生效，必须修改配置文件my.inf

   将slow_query_log选项和slow_query_log_file 选项加入到配置文件的 [mysqld] 组中。格式如下：

   [mysqld]
   slow_query_log=1
   slow_query_log_file=/var/lib/mysql/aguigu-slow.log

   其他的配置long_query_time=3

   ​ long_output=file

3. 开启了之后，什么样的sql会被记录到慢查询日志【大于不是大于等于】

   • 查看当前多少秒算慢

     mysql> SHOW VARIABLES LIKE ‘long_query_time’;

     ±----------------±----------+
     | Variable_name | Value |
     ±----------------±----------+
     | long_query_time | 10.000000 |
     ±----------------±----------+
     1 row in set, 1 warning (0.01 sec)

   • 设置慢的阈值时间

     set global long_query_time=3

   • 为什么设置后看不出变化

     需要重新连接或者新开一个会话才能看到修改值。

     或者 SHOW global VARIABLES LIKE ‘long_query_time’;

   • 记录慢sql并进行后续分析

     例如模拟以下得很长时间

     mysql> select sleep（4）;大于3s
     

     这样就会捕获这个记录

     # Time: 2020-06-01T01:59:18.368780Z
     # User@Host: root[root] @ localhost [::1]  Id:     3
     # Query_time: 4.069439  Lock_time: 0.000000 Rows_sent: 1  Rows_examined:0
     use test;库
     SET timestamp=1590976758;
     select sleep（4）；导致慢查询的语句
     

   • 查看当前系统有多少慢查询记录

     SHOW global status LIKE ‘slow_queries%’

4. 日志分析工具mysqldumpslow

   帮助信息：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aNtj3n4T-1623831618090)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210615230754594.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zxIosi4R-1623831618099)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210615230803282.png)]

3.3批量数据脚本

复习函数和存储过程。

create table/index/procedure/function/view

存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，

函数：有返回值的过程,调用函数用select

存储过程：没有返回值的函数。调用存储过程用call

3.3.1创建数据库和表

首先新建数据库，

CREATE DATABASE bigData;
USE bigData;

再建部门表dept

CREATE TABLE dept (
id INT unsigned primary key auto_increment,
deptno mediumint unsigned not null default 0,
dname VARCHAR(20) not null default"",
loc VARCHAR(13) not null default""注意最后一句没有逗号
)engine=innodb default charset=gbk;

建员工表emp

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

3.3.2设置参数log_bin_trust_function_creators

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SM7gwBE7-1623831618102)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210616150743685.png)]

3.3.3创建函数，保证每条数据都不同

3.3.3.1随机产生字符串【随机串】

创建一个函数随机产生字符串

delimiter 用 来 定 义 用来定义 用来定义为结束标志。

delimiter $
create function rand_string(n int) returns varchar(255)//注意是returns
begin
   declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
   declare return_str varchar(25) default'';
   declare i int default 0;
   while i<n do
      set  return_str=concat( return_str,substring(chars_str,floor(1+rand()*52),1));//注意concat和floor的用法
      set i=i+1;
   end while;
   return  return_str;
end $

3.3.3.2随机产生部门编号【随机数】

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

3.3.4创建存储过程

3.3.4.1创建往员工表插入数据的存储过程

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+1),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());
until i=max_num
end repeat;
commit;
end $

3.3.4.2创建往部门表插入数据的存储过程

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+1),rand_string(10),rand_string(8));
until i=max_num
end repeat;
commit;
end $

3.3.5调用存储过程

delimiter;
call insert_dept(100,10);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-55OaKOC9-1623831618106)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210616154955648.png)]

可以把数字调大例如1万。

3.4show profile

【比explain更细致】

show profile是由Jeremy Cole捐献给MySQL社区版本的。默认的是关闭的，并保存最近15次的运行结果，但是会话级别可以开启这个功能。开启它可以让MySQL收集在执行语句的时候所使用的资源消耗情况，用于sql的调优的测量。

分析步骤：

1看当前mysql是否支持

show variables like ‘profiling’;默认关闭是off

2使用前开启功能

   SET profiling = 1;或者on

3运行sql

select * from emp group by id%10 limit 150;

4查看结果

SHOW PROFILES\G

Query_ID: 1
Duration: 0.772285200持续时间
Query: select * from emp group by id%10 limit 150;执行的sql

5诊断sql

主要用到的参数type是可选的，取值范围可以如下：

• ALL 显示所有性能信息
• BLOCK IO 显示块IO操作的次数
• CONTEXT SWITCHES 显示上下文切换次数，不管是主动还是被动
• CPU 显示用户CPU时间、系统CPU时间
• IPC 显示发送和接收的消息数量
• MEMORY [暂未实现]
• PAGE FAULTS 显示页错误数量
• SOURCE 显示源码中的函数名称与位置
• SWAPS 显示SWAP的次数

查看某一条sql的cpu等情况

show profile cpu,block io for query 1;//sql的序号是几就写几，可以看到一条sql的执行记录，过程。

如果没有指定FOR QUERY，那么输出最近一条语句的信息。

sql的stastus可能出现以下坏事的，意味着这条sql不太好，太耗费资源

• converting HEAP to MyISAM查询结果太大，内存不够了，往磁盘上搬了
• Creating tmp table创建临时表，有三步创建临时表，拷贝数据到临时表，用完再删除【分组时可能会出现】
• Copying to tmp table on disk把内存中临时表复制到磁盘，危险！
• locked

3.5全局查询日志

【测试环境用，不要在生产环境用】

配置启用

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3SsKIXKG-1623831618109)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210616161910139.png)]

编码启用

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cndXj3Co-1623831618110)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210616161921189.png)]