mysql——查询截取分析

你的名字 2021-11-11 11:56 293阅读 0赞

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

# 一、查询优化 #

### 1、永远小表驱动大表 ###

### 2、类似嵌套循环Nested Loop ###

### 3、order by关键字优化 ###

#### （1）、order by子句，尽量使用index方式排序，避免filesort ####

*  mysql支持二种方式的排序：filesort和index
    
    index效率高，指mysql扫描索引本身完成排序  
    filesort方式效率较低
 *  order by满足两种情况，会使用index方式排序：
    
    order by语句使用索引最左前列  
    使用where子句与order by子句条件列组合满足索引最左前列

#### （2）、如果不在索引上，filesort有两种算法：双路和单路 ####

*  双路排序：
    
    mysql4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据  
    读取行指针和order by列，对它们进行排序，然后扫描已经排序好的列表，按照列表中读取对应的数据  
    从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段
 *  单路排序：
    
    取一批数据，要对磁盘进行两次扫描，IO很耗时，所以在spring4.1之后，出现了第二种改进算法——单路排序。  
    从磁盘读取查询需要的所有列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出。  
    效率更快一些，避免了第二次读取数据，并且把随机IO变成了顺序IO，但是他会使用更多的空间，因为他把每一行都保存在内存中了

**简而言之，单路排序总体上比双路排序好，但是单路排序也有问题**  
**问题：**  
在sort\_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据总大小超出了sort\_buffer的容量，导致每次只能取sort\_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取sort\_buffer容量大小，再排…从而多次IO。本来想省一次IO操作，反而导致了大量的IO操作

（3）、优化策略

*  增大sort\_buffer\_size参数的设置
 *  增大max\_length\_for\_sort\_data参数的设置
 *  为排序使用索引：
    
        mysql有两种排序方式：文件排序或扫描有序索引排序，mysql能为排序与查询使用相同的索引<br />（4）、提高order by的速度
 *  order by时select \*是大忌，最好只query需要的字段，带来的影响：
    
        当query的字段大小总和小于max_length_for_sort_data而且排序字段不是textblob类型时，会用单路排序，否则使用双路排序<br />       两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次IO，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size
 *  尝试提高sort\_buffer\_size
    
        不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
 *  尝试提高max\_length\_for\_sort\_data
    
        提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率

###  ###

## 4、exists与in优化 ##

优化原则：**小表驱动大表，即小的数据集驱动大的数据集**  
![image.png][]

#exists语法理解：
    #将主查询的数据放到子查询中做条件验证，根据验证结果（true或false）来决定主查询的数据结果是否得以保留
    #subquery只返回Boolean值，因此子查询中的select *也可以是select 1或其他，实际执行时会忽略select清单
    #子查询也可以用条件表达式、其他子查询或者join来替代，哪种最优具体问题具体分析
    select ..... from table where exists(subquery);
    
    
    
    #exists与in
    #
    #1、当B表的书籍必须小于A表的数据集时，in由于exists
    select * from A where id in (select id from B);
    #等价于
    for select * from B
    for select * from A where A.id=B.id
    
    #2、当A表的数据集小于B的数据集时，exists优于in
    select * from A where exists(select 1 from B where B.id=A.id);
    #等价于
    for select * from A
    for select * from B where B.id=A.id

### 4、group by关键字优化 ###

*  group by实质是先排序后进行分组，遵照索引建的最佳左前缀
 *  当无法使用索引列，增大max\_length\_for\_sort\_data参数的设置+增大sort\_buffer\_size参数的设置
 *  where高于having，能写在where限定的条件就不要去having限定了

# 二、慢查询日志 #

## 1、是什么？ ##

*  mysql的慢查询日志是mysql提供的一种日志记录，它用来记录mysql中响应时间超过阙值的语句，具体指**运行时间超过long\_query\_time值的SQL，则会呗记录到慢查询日志中**
 *  long\_query\_time的默认值为10，意思是运行10秒以上的SQL语句会被记录到慢查询日志中，也**可以自己设置阙值，设置后需要重新连接mysql或者新开启一个会话才会生效**
 *  由慢查询日志来查看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的SQL，结合之前explain进行全面分析

## 2、怎么玩？ ##

**默认**情况下，mysql数据库**没有开启慢查询日志**，需要我们手动来设置这个参数（**可以通过命令的方式，此方式只对当前数据库生效，如果重启mysql后则会失效；也可以通过修改my.cnf配置文件的方式，使用配置文件的方式是永久生效的**）。当然如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

#一、查看“慢查询日志”是否开启
    mysql>show variables like '%slow_query_log%';
    
    #二、手动开启“慢查询日志”
    #1、使用命令：只对当前数据库生效，如果重启mysql后就会失效
    mysql>set global slow_query_log=1;
    
    #2、使用修改my.cnf配置文件的方式：永久生效
    slow_query_log=1
    
    
    #三、查询设置SQL进入慢查询日志的界限值,即阙值（此值限定什么样的SQL语句会被记录到慢查询日志中,运行时间大于阙值时会被记录）
    mysql>show variables like 'long_query_time%';
    
    #四、设置阙值时间
    mysql>set global long_query_time=5;
    
    #五、查询当前系统中有多少条慢查询记录
    mysql>show global status like '%slow_queries%';

## 3、日志分析工具——mysqldumpslow ##

#### （1）、查看mysqldumpslow帮助信息 ####

mysqldumpslow --help

<table> 
 <thead> 
  <tr> 
   <th>s</th> 
   <th>表示按照何种方式排序</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>c</td> 
   <td>访问次数</td> 
  </tr> 
  <tr> 
   <td>l</td> 
   <td>锁定时间</td> 
  </tr> 
  <tr> 
   <td>r</td> 
   <td>返回记录</td> 
  </tr> 
  <tr> 
   <td>t</td> 
   <td>查询时间</td> 
  </tr> 
  <tr> 
   <td>al</td> 
   <td>平均锁定时间</td> 
  </tr> 
  <tr> 
   <td>ar</td> 
   <td>平均返回记录数</td> 
  </tr> 
  <tr> 
   <td>at</td> 
   <td>平均查询时间</td> 
  </tr> 
  <tr> 
   <td>-t</td> 
   <td>返回前面多少条的数据</td> 
  </tr> 
  <tr> 
   <td>g</td> 
   <td>后边搭配一个正则匹配模式，大小写不敏感</td> 
  </tr> 
 </tbody> 
</table>

![image.png][image.png 1]

#### （2）、常用参考 ####

#PS：/var/lib/mysql/localhost-slow.log是记录慢查询日志的日志文件，查询时应找到自己服务器上对应的日志文件路径
    #得到返回记录集最多的10个SQL
    mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log
    
    #得到访问次数最多的10个SQL
    mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log
    
    #得到按照时间排序的前10条里面包含左连接的查询语句
    mysqldumpslow -s t -t 10 /var/lib/mysql/localhost-slow.log
    
    #在使用这些命令时可以结合“|”和“more”使用，否则有可能出现爆屏的情况
    mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log |more

![image.png][image.png 2]

#  #

# 三、show profile #

## 1、是什么？ ##

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

## 2、分析步骤 ##

（1）、是否支持，看看当前的mysql版本是否支持此功能  
（2）、使用前先开启

#查看是否开启:默认关闭
    mysql>show variables like 'profiling%';
    
    #开启show profile
    mysql>set profiling=on;

（3）、运行SQL  
测试时可以**用函数和存储过程写一个批量数据脚本**插入批量数据来执行一些SQL然后使用show profile来对SQL进调优测量。  
函数和存储过程的区别：**函数有返回值，存储过程没有返回值**

CREATE TABLE dept(
    id int UNSIGNED PRIMARY key not null 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;
    
    
    
    CREATE TABLE emp(
    id int UNSIGNED PRIMARY key not null 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;
    
    
    #############################################声明一个函数############################
    delimiter $$  
    CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
    BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
    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 把autocommit设置成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 把autocommit设置成0
    SET autocommit=0;
    REPEAT
    SET i=i+1;
    INSERT INTO emp(deptno,dname,loc)VALUES((start+i),rand_string(6),rand_string(8));
    UNTIL i=max_num
    END REPEAT;
    COMMIT;
    END $$

（4）、查看结果

mysql>show profiles;

![image.png][image.png 3]  
（5）、诊断SQL

#query后边加“show profiles”出来的SQL数字号码
    mysql>show profile cpu,block io for query 1;

诊断SQL时需要用到的参数说明：

<table> 
 <thead> 
  <tr> 
   <th>all</th> 
   <th>显示所有的开销信息</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>block io</td> 
   <td>显示IO相关开销</td> 
  </tr> 
  <tr> 
   <td>context switches</td> 
   <td>上下文切换相关开销</td> 
  </tr> 
  <tr> 
   <td>cpu</td> 
   <td>显示CPU相关开销信息</td> 
  </tr> 
  <tr> 
   <td>IPC</td> 
   <td>显示发送和接收相关开销信息</td> 
  </tr> 
  <tr> 
   <td>memory</td> 
   <td>显示内存相关开销信息</td> 
  </tr> 
  <tr> 
   <td>page faults</td> 
   <td>显示页面错误相关开销信息</td> 
  </tr> 
  <tr> 
   <td>source</td> 
   <td>显示和source_function,source_file,source_line相关的开销信息</td> 
  </tr> 
  <tr> 
   <td>swaps</td> 
   <td>显示交换次数相关开销信息</td> 
  </tr> 
 </tbody> 
</table>

![image.png][image.png 4]

（6）、需要注意的结论：主要查看是否有以下四种情况  
**converting heap to myisam**——查询结果太大，内存都不够用了往磁盘上搬了  
**creating tmp table**——创建临时表：拷贝数据到临时表，用完再删除  
**copying to tmp table** on disk——把内存中临时表复制到磁盘  
**locked**

# 四、全局查询日志 #

PS：**永远不要在生产环境上开启这个功能**，因为开启全局查询日志后SQL语句将会记录到mysql库里  
1、配置启用方法一：修改mysql的my.cnf配置文件

#开启
    general_log=1
    #记录日志文件的路径
    general_log_file=/path/logfile
    #输出格式
    log_output=FILE

2、配置启用方法二：命令

#开启
    mysql>set global general_log=1;
    
    #输出方式
    mysql>set global log_output='TABLE';
    
    #查看
    mysql>select * from mysql.general_log;

[image.png]: /images/20211109/074b8349396d488e856bbff5181f8a22.png
[image.png 1]: /images/20211109/b947f6eb017644fcbb811f636958d242.png
[image.png 2]: /images/20211109/b4b875f33ffe476fae854448407281e7.png
[image.png 3]: /images/20211109/202c893a2fb2426db35d998da496f45a.png
[image.png 4]: /images/20211109/cb83816b0888403aa7cf0197ccfdf7e6.png