[MySQL高级](五) 查询截取分析

柔情只为你懂 2022-01-29 12:41 255阅读 0赞

# 1. 查询优化 #

## 1.1 永远小表驱动大表 ##

小表驱动大表的理解我们可以通过下面的java代码来理解一下：

> // 第一种循环的方式  
> 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++)\{
> 
>     \}  
> \}

虽然上面的Java代码都表示循环5000次，但是在mysql中，推荐使用第一种循环方式，即小表驱动大表。

![70][]

![70 1][]

## 1.2 order by 关键字优化 ##

### 1.2.1 order by子句，尽量使用index方式排序，避免使用filesort方式排序 ###

*  **建表和创建索引**

> CREATE TABLE \`tbla\` (  
>   \`id\` int(11) NOT NULL,  
>   \`age\` int(11) DEFAULT NULL,  
>   \`brith\` timestamp NULL DEFAULT NULL ON UPDATE CURRENT\_TIMESTAMP,  
>   PRIMARY KEY (\`id\`)  
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> 
>   
> CREATE INDEX idx\_A\_ageBirth ON tblA(age,birth);

*  **各种order by条件的执行情况 **

![70 2][]

![70 3][]

*  **MySQL支持两种方式的排序，FileSort和Index。Index效率高，它指MySQL扫描索引本身完成排序，FileSort方式效率低。**
 *  **ORDER BY满足两种情况，会使用Index方式排序。第一种是ORDER BY语句使用索引最左前列，第二种是使用Where子句与ORDER BY子句条件列组合满足索引最左前列**

### 1.2.2 FileSort算法介绍 ###

尽可能在索引列上完成排序操作，遵照索引建的最佳做前缀。如果不在索引列上，filesort有两种算法，mysql就要启动双路排序和单路排序。

*  **双路排序 **

MySQL 4.1 之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据。读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

*  **单路排序 **

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

**结论以及引申出的问题：**   
由于单路是一次磁盘IO，总体而言效率高于双路，但是单路有问题：

![70 4][]

### 1.2.3 优化策略 ###

*  **增大sort\_buffer-size参数的设置**
 *  **增大max\_length\_for\_sort\_data参数的设置**

![70 5][]

### 1.2.4 为排序使用索引 ###

![70 6][]

## 1.2 group by 关键字优化 ##

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

# 2. 慢查询日志 #

## 2.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.2 使用 ##

### 2.2.1 说明 ###

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

### 2.2.2 查看是否开启及如何开启 ###

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

> SHOW VARIABLES LIKE '%slow\_query\_log%';

*  开启慢查询日志只对当前数据库生效，如果MySQL重启后则会失效

> SET GLOBAL slow\_query\_log = 1;

![70 7][]

如果要永久生效，就必须修改配置文件my.cnf，修改my.cnf，\[mysqld\]下增加或修改参数   
slow\_query\_log和slow\_query\_log\_file后，然后重启MySQL服务器，也即将如下两行配置进my.cnf文件

> slow\_query\_log=1  
> slow\_query\_log\_file=/var/lib/mysql/why-solw.log

关于慢查询的参数slow\_query\_log\_file，它指定慢查询日志文件的存放路径，系统默认会给你个缺省的文件host\_name\_log\_file.log（如果没有指定参数slow\_query\_log\_file的话）

### 2.2.3 开启慢查询日志后，什么样的SQL才会记录到日志中 ###

这个是有参数long\_query\_time控制，默认情况下值为10秒。   
命令：

> SHOW VARIABLES LIKE 'long\_query\_time%'

可以使用命令修改，也可以在my.cnf参数里面修改。   
假如运行时间正好等于long\_query\_time的情况，并不会被记录下来，也就是说，在mysql源码里是判断大于long\_query\_time，而非大于等于。

### 2.2.4 使用方法 ###

*  查看当前多少秒算慢

> SHOW VARIABLES LIKE 'long\_query\_time%'

*  设置慢的阙值时间

> SET GLOBAL long\_query\_time = 3;

*  为什么设置后重新查看仍然没改变需要重新连接或断开当前会话才能看到修改值或执行下面的命令

> SHOW GLOBAL VARIABLES LIKE 'long\_query\_time';

*  查询当前系统中有多少条慢SQL查询记录

> SHOW GLOBAL STATUS LIKE '%Slow\_queries%';

## 2.3 日志分析工具mysqldumpslow ##

![70 8][]

![70 9][]

# 3. Show Profile #

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

## 3.1 分析步骤 ##

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

> SHOW VARIABLES LIKE 'profiling'

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

> SET profiling = ON;  
> SHOW VARIABLES LIKE 'profiling';

![70 10][]

*  运行SQL
 *  查看结果

> show profiles;

*  诊断SQL

> show profile cpu,block,io for query 上一步问题SQL的数字号码

![70 11][]

\- 日常开发需要注意的结论

> converting HEAP to MyISAM  查询结果太大，内存都不够用了往磁盘上搬了  
> Creating tmp table 创建临时表  
> Copying to tmp table on disk 把内存中临时表复制到磁盘，危险  
> locked

本文原文地址：[https://blog.csdn.net/why15732625998/article/details/80423742][https_blog.csdn.net_why15732625998_article_details_80423742]

[70]: /images/20220129/232880f0f9a64ceb9338b35ef22e9778.png
[70 1]: /images/20220129/7360e279ab7d43f29b106b802265ab83.png
[70 2]: /images/20220129/5ee8b2ecf66d4d1399c226d8c6816cd4.png
[70 3]: /images/20220129/9fa7b4d2c0c64fa28ae1f5235a1e54ad.png
[70 4]: /images/20220129/845abd9b242642d49639a1cb1335fecc.png
[70 5]: /images/20220129/dd1ecf523d7f4e5cb0ffde9002a30fae.png
[70 6]: /images/20220129/d2a1c74eda5948ab83c39d5f286c83ab.png
[70 7]: /images/20220129/5ecae51716ed4cf58ce299c7f20199c9.png
[70 8]: /images/20220129/ed30cbb93ab64ffdab39530927fd65ad.png
[70 9]: /images/20220129/bcf4d038597f46b690127d3970c41584.png
[70 10]: /images/20220129/65bcf1d6a1404d938a22511429a7929c.png
[70 11]: /images/20220129/fb10cd484eb64f27b6f44ffcc73e7c57.png
[https_blog.csdn.net_why15732625998_article_details_80423742]: https://blog.csdn.net/why15732625998/article/details/80423742