查询截取分析

拼搏现实的明天。 2022-05-21 10:27 132阅读 0赞

关于SQL的优化步骤分析：

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

总结：

*  慢查询的开启并捕获
 *  Explain+慢SQL分析
 *  show profile查询SQL在MySQL服务器里面的执行细节和生命周期
 *  SQL数据库服务器的参数调优

##### 一、查询优化 #####

**1、永远小表驱动大表（类似嵌套循环）**

for (int i = 0; i < 5; i++) {
        for (int j = 0; j < 1000; j++) {
            
        }
    }
    for (int i = 0; i < 1000; i++) {
        for (int j = 0; j < 5; j++) {
    
        }
    }

第一个优于第二个

优化原则：小表驱动大表，即小的数据集驱动大的数据集

原理：

SELECT * FROM A WHERE id IN (SELECT id FROM B)
    
    #等价于
    FOR SELECT id FROM B
    SELECT * FROM A WHERE A.id = B.id
    #当B表的数据小于A表的数据时，用in优于exists
    
    
    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
    #当A表的数据小于B表的数据时，用exists优于in
    
    注意 AB表的id字段应该建立索引

关于Exists

SELECT ..... FROM TABLE WHERE EXISTS (subquery)

*  该语法可以理解为：将主查询的数据放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留。
 *  提示：

1) EXISTS(subquery)只返回 TRUE或FALSE，因此子查询中的select \* 也可以是select 1 或select ‘X’，官方的说法是实际执行时会忽略select清单，因此没有区别。

2）EXISTS的子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可以进行实验检验以确定是否有效率问题。

3）EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来代替，何种最优需要根据具体问题具体分析。

![70][]

两者结果相同。

**2、order by关键字优化**

*order by字句尽量使用index方式排序，避免使用filesort方式排序*

建表+建索引：

CREATE TABLE tblA(
    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());
    
    ALTER TABLE tblA ADD INDEX idx_A_ageBirth(age,birth);
    
    SELECT * FROM tblA;

1）

![70 1][]

产生filesort的原因是索引建立的顺序是age,birth，但是后两者的order by都不是从age开始的。

2）

![70 2][]

小结：

*  MySQL支持两种方式的排序，Filesort和Index，Index效率高，它指MySQL扫描索引本身完成排序。Filesort方式效率较低。
 *  order by满足两种情况会使用Index方式排序：

1）order by语句使用索引最左前列

2）使用where语句与order by语句条件列组合满足索引最左前列

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀。

如果不能在索引上，filesort有两种算法：MySQL要启用双路排序和单路排序。

1）双路排序

mysql4.1之前是使用双路排序，就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。

从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段 。

取一批数据要对磁盘进行两次扫描，众所周知，IO很耗时，所以mysql4.1之后，出现了第二种改进的算法，就是单路排序。

2）单路排序

从磁盘读取查询需要的所有列，按照order by列在buffer对他们进行排序，然后扫描排序后的列进行输出。它的效率更快一些，避免了第二次读数据，并且把随机IO变成了顺序IO，但是它会使用更多的空间。因为把每一行都保存在了内存中。

结论以及引申出的问题：

由于单路排序是后出的，总体好过双路。

但是用单路有问题——在sort\_buffer中，方法B（单路）比方法A（双路）占用更多空间，因为B是把所有字段都取出，所以有可能取出的数据总大小超过了sort\_buffer的容量，导致每次只能读取sort\_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取sort\_buffer容量大小，再排。。。。从而多次I/O。

本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

*优化策略*

1、增大sort\_buffer\_size参数的设置

2、增大max\_length\_for\_sort\_data参数的设置

Why？——>

提高Order by的查询速度

1）order by时select.\*是一个大忌只Query需要的字段，这点非常重要。在这里的影响：

*  当Query的字段大小总和小于max\_length\_for\_sort\_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。
 *  两种算法的数据都有可能超出sort\_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更加大一些，所以需要提高sort\_buffer\_size。

2）尝试提高sort\_buffer\_size

不管用哪种算法，提高这个参数都会提高效率。当然需要根据系统的能力去提高，因为这个参数是针对这个进程的

3）尝试提高max\_length\_for\_sort\_data

提高这个参数，会增加用改进算法的概率。但是设的太高，数据总容量超出sort\_buffer\_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。

3、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值的SQL，则会被记录到慢查询日志中。long\_query\_time的默认值是10，意思是运行10秒以上的语句。
 *  由它来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒，我们就算慢SQL，希望能收集超过5秒的sql，结合之前的Explain进行分析。

**2、怎么玩？**

**说明**：

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

1）查看是否开启以及如何开启？

查看：

SHOW VARIABLES LIKE '%slow_query_log%';

![70 3][]

默认情况下slow\_query\_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow\_query\_log的值来开启。

开启：

SET GLOBAL slow_query_log=1;

使用SET GLOBAL slow\_query\_log=1;开启了慢查询日志只对当前数据库生效，如果重启MySQL后则会失效（或者使用

SET GLOBAL slow\_query\_log=0;关闭）。

![70 4][]

如果要永久生效，就必须修改配置文件my.cnf（其他系统变量也是如此）

修改my.cnf文件，【mysqld】下增加或修改参数slow\_query\_log和slow\_query\_log\_file后，然后重启MySQL服务器。

也就是将如下两行配置加入my.cnf文件：

slow_query_log
    
    slow_query_log_file=DESKTOP-O360IRK-slow.log（一个地址）

关于慢查询日志的参数slow\_query\_log\_file，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件

host\_name-slow.log（如果没有指定参数slow\_query\_log\_file）。

2）开启了慢查询日志后，怎样的SQL才会记录到慢查询日志里面呢？

这个是由参数long\_query\_time控制的，默认情况下long\_query\_time的值为10秒，

命令：

SHOW VARIABLES LIKE '%long_query_time%';

![70 5][]

可以使用命令修改该参数，也可以在my.cnf里修改。

加入时间正好等于long\_query\_time的情况，是不会被记录，也就是说在mysql源码里是判断大于long\_query\_time，而非大于等于。

3）Case

①查看当前多少秒算慢？

SHOW VARIABLES LIKE '%long_query_time%';

②设置慢的时间阈值

SET GLOBAL long_query_time=3;//3秒

![70 6][]

③为什么设置后看不出变化？

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

SHOW VARIABLES LIKE '%long_query_time%';

或者不用重开或连接，直接用命令：

SHOW GLOBAL VARIABLES LIKE '%long_query_time%';

![70 7][]

④记录慢SQL并后续分析

![70 8][]

然后查看相应地址中的文件

![70 9][]

⑤查看当前系统有多少条慢查询记录？

SHOW GLOBAL STATUS LIKE '%slow_queries%';

![70 10][]

4）配置版

在【mysqld】下配置

slow_query_log=1;
    slow_query_log_file=/....(地址)
    long_query_time=3;
    log_output=FILE

这样就会永久生效。

3、日志分析工具mysqldumpslow

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

1）查看mysqldumpslow的帮助信息

使用命令：

mysqldumpslow --help

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

s：表示按照何种方式排序；

c：访问次数；

I：锁定时间

r：返回记录

t：查询时间

al：平均锁定时间

ar：平均返回记录数

at：平均查询时间

t：返回前面有多少条数据

g：后边搭配一个正则匹配模式，大小写不敏感

2)  工作常用参考

得到返回记录集最多的10个SQL：

mysqldumpslow -s r -t 10 /DESKTOP-O360IRK-slow.log

得到访问次数最多的10个SQL：

mysqldumpslow -s c -t 10 /DESKTOP-O360IRK-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句：

mysqldumpslow -s t -t 10 -g "LEFT JOIN" /DESKTOP-O360IRK-slow.log

另外建议在使用这些属性的时候结合 | 和more使用，否则可能出现爆屏的情况：

mysqldumpslow -s r -t 10 /DESKTOP-O360IRK-slow.log | more

##### 三、批量数据脚本 #####

往数据库表里插入1000W数据

1、建表

CREATE DATABASE BigData;
    use BigData;
    
    CREATE TABLE dept(
    id INT UNSIGNED PRIMARY KEY auto_increment,
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '部门编号',
    dname VARCHAR(20) NOT NULL DEFAULT "" COMMENT '名字',
    Ioc VARCHAR(13) NOT NULL DEFAULT "" COMMENT '位置'
    
    )ENGINE=INNODB DEFAULT charset=gbk COMMENT'部门表';
    
    
    CREATE TABLE emp(
    id INT UNSIGNED PRIMARY KEY auto_increment,
    empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '员工编号',
    ename VARCHAR(20) NOT NULL DEFAULT "" COMMENT '名字',
    job VARCHAR(13) NOT NULL DEFAULT "" COMMENT '工作',
    mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT'上级编号',
    hiredate Date NOT NULL COMMENT'入职时间',
    sal DECIMAL(7,2) NOT NULL COMMENT'薪水',
    comm DECIMAL(7,2) NOT NULL COMMENT'红利',
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '部门编号'
    
    )ENGINE=INNODB DEFAULT charset=gbk COMMENT'员工表';

2、设置参数log\_bin\_trust\_function\_creators

创建函数，假如报错：This function has none of DETERMINISTIC.........

由于开启过慢查询日志，因为我们开启了bin-log，我们就必须为我们的function指定一个参数。

SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

开启：

SET GLOBAL log_bin_trust_function_creators = 1;

![70 11][]

这样添加了参数之后，重启mysqld，上述参数又会失效，永久方法：

Windows下my.ini【mysqld】加上 SET GLOBAL log\_bin\_trust\_function\_creators = 1;

Linux下etc/my.cnf下my.cnf【mysqld】加上 SET GLOBAL log\_bin\_trust\_function\_creators = 1;

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

![70 12][]

①  随机产生字符串

#由于mysql是由‘;’来结束一条语句的，由于函数需要使用多条语句，则需要使用delimiter $$和END $$来定义使用$$来作为结束语句
    delimiter $$
    #创建函数，包括函数名rand_string，返回值
    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 $$

![70 13][]

②  随机产生部门编号

#用于随机产生部门编号
    delimiter $$
    CREATE FUNCTION rand_num()
    RETURNS INT(5)
    BEGIN 
    DECLARE i INT(5) DEFAULT 0;
    SET i = FLOOR(100+RAND()*10);
    RETURN i;
    END $$
    
    
    #如果要删除函数
    DROP FUNCTION rand_num;

4、创建存储过程

1）创建往emp表中插入数据的存储过程

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_str(6),'SALESMAN',0001,CURRENT_DATE(),2000,400,rand_num());
    UNTIL i=max_num
    END REPEAT;
    COMMIT;
    END $$

2）创建往dept表中插入数据的存储过程

#执行存储过程，往dept表添加随机数据
    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,Ioc) VALUES((START+i),rand_string(8),rand_string(6));
    UNTIL i=max_num
    END REPEAT;
    COMMIT;
    END $$

5、调用存储过程

1）dept

CALL insert_dept(101, 10);

2）emp

CALL insert_emp(100001, 500000);

##### 四、Show Profile #####

1、是什么？

Show Profile是mysql提供的可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量。

官网：[https://dev.mysql.com/doc/refman/5.7/en/show-profile.html][https_dev.mysql.com_doc_refman_5.7_en_show-profile.html]

默认情况下，参数是处于关闭状态，并保存最近15次的运行结果

2、分析步骤

1）是否支持，看看当前的mysql版本是否支持

查看

SHOW VARIABLES LIKE 'profiling';

![70 14][]、

2）开启功能，默认是关闭的，使用前需要开启

开启：

SET profiling = ON

![70 15][]

3）运行SQL

SELECT * FROM emp GROUP BY id%10 LIMIT 150000;
    
    SELECT * FROM emp GROUP BY id%20 ORDER BY 5;

4）查看结果

SHOW PROFILES;

![70 16][]其中Query\_ID是编号，Duration是SQL执行的时间，Query是具体执行的SQL。

5）诊断SQL，show profile cup，block io for query + Query\_ID（就是步骤4中的id）

![70 17][]

除了cpu，block io还有其他的：

<table> 
 <tbody> 
  <tr> 
   <td>type</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>ALL</td> 
   <td>显示所有的开销信息</td> 
  </tr> 
  <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>

6）日常开发需要注意的结论

①、converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了

②、Creating tmp table 创建临时表

*  拷贝数据到临时表
 *  用完再删除

③、Copying to tmp table on disk 把内存中临时表复制到磁盘，危险

④、blocked

![70 18][]

##### 五、全局查询日志 #####

**注意**：*只允许在测试环境使用，永远不要在生产环境开启使用*

1、配置启用

在my.cnf中设置如下：

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

2、编码启用

命令

SET GLOBAL general_log=1;
    SET GLOBAL log_output='TABLE'

此后你所编写的SQL语句都会记录到mysql库里的general\_log表，可以用下面的命令查看：

SELECT * FROM mysql.general_log;

[70]: /images/20220521/e46f38425cbd44f486d7bd291f80356f.png
[70 1]: /images/20220521/090acd3c29b349fd961a1696630968fb.png
[70 2]: /images/20220521/7eb7e4026b954683881b1e6c180d4eb0.png
[70 3]: /images/20220521/1efe4e751dc641ff89f5cdb3440776b0.png
[70 4]: /images/20220521/5f95730280e34c18a8ea0f02e037b0ad.png
[70 5]: /images/20220521/288d0e979bd34a7b80ae8263480d43a5.png
[70 6]: /images/20220521/28eb13cc2972488da923073a9d5e6601.png
[70 7]: /images/20220521/57dba2c2811d4fdd95473ff1c479f0b9.png
[70 8]: /images/20220521/2d3f9fc0cebe4177b2b1ad4059d7a3c8.png
[70 9]: /images/20220521/3891798ad92041db89ee079af0214471.png
[70 10]: /images/20220521/b00bc451866e4bd9a09db15425506b62.png
[70 11]: /images/20220521/41da7c9262674ffe848de51764c3fbd1.png
[70 12]: /images/20220521/4f0acf807d01482488ca71e8b6fc12f7.png
[70 13]: /images/20220521/24cf2c5a0c5445ecb8b7fd64fa5a8197.png
[https_dev.mysql.com_doc_refman_5.7_en_show-profile.html]: https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
[70 14]: /images/20220521/d23d2291e4d249fb9eb19433629d03e4.png
[70 15]: /images/20220521/8fa545baa5134fa690ade30064ee6b18.png
[70 16]: /images/20220521/f5e97bd5e8544e6084966028fe20ece9.png
[70 17]: /images/20220521/b174f5145a1646b69ab9a25da6947fee.png
[70 18]: /images/20220521/786fe850cec8409ea051a23f691e6ee2.png