Mysql优化之高级

小灰灰 2021-05-12 11:55 566阅读 0赞

## 一、exists和in - - - 小表驱动大表 ##

**前言：** 500\*10000和10000\*500，在数学角度来说是没什么区别的，从java角度来说是这样的：

for（int i=0;i<500;i++）{ for(int j;j<10000;j++){ }
    }

和

for（int i=0;i<10000;i++）{ for(int j;j<500;j++){ }
    }

但是对于mysql来说是有区别的。这关系到连接和释放的次数。

**简介：** exists和in到底谁性能好是没有绝对一说的，这需要看表数据的大小。

**in：** `SELECT * FROM dept where deptno in (SELECT deptno FROM emp )`这句sql，在mysql翻译之后是这样的：

for（SELECT deptno FROM emp） { for（SELECT * FROM dept where emp.deptno =dept.deptno）{ } }

**exists：** `SELECT * FROM dept where EXISTS (SELECT 1 FROM emp where emp.deptno =dept.deptno)`这句sql，在mysql翻译之后是这样的（这里的1可以换成任意常量，比如A、a之类的）：

for（SELECT deptno FROM dept ） { for（SELECT * FROM dept where emp.deptno =dept.deptno）{ } }

**分析：** 如果使用in，则需要连接emp的条数次；如果使用exists，则需要连接dept 的条数次。

**案例：**向emp中插入100万条记录，向dept表中插入5万条记录。  
![这里写图片描述][70]  
![这里写图片描述][70 1]

**结论：** emp表比dept表小的时候使用in，emp表比dept表大的时候使用exists。即：内小in，外小exists。

## 二、order by 之 filesort ##

**简介：** order by的排序总共有两种，一种是使用索引（index）进行排序，还有一种是没法使用索引进行排序的，称为filesort排序（文件排序）。using index效率比using filesort高。

**准备工作：**

CREATE TABLE test01 ( c1 varchar(3) , c2 varchar(3), c3 varchar(3), c4 varchar(3) );
    create index c1c2c3 on test01(c1,c2,c3);
    insert into test01 values('a1','a2','a3','a4');
    insert into test01 values('b1','b2','b3','b4');

**几种常见的场景：**

**1、只有order by 没有where条件：**这个可能其他版本的mysql是不会产生filesort的（因为我看到网上说这种情况是不会产生的），我也没试过，但是mariadb的10.0.17版本是会产生的  
![这里写图片描述][70 2]  
![这里写图片描述][70 3]  
**2、where条件中有c1，但是order by中没有c1，只有c2和c3**  
![这里写图片描述][70 4]  
**3、where 条件中有c1 ，但是order by中只有c3：** 这个就属于中间兄弟丢了  
![这里写图片描述][70 5]  
**4、where条件中有c1，但是order by中没有c1，只有c2和c3，而且c2正序，c3倒序：**必须同正同逆  
![这里写图片描述][70 6]  
**5、where条件中有c1，order by中有c1、c2和c3，但是order by c1 ,c3,c2：**必须要和复合索引字段顺序一致  
![这里写图片描述][70 7]  
**6、where 条件中有c1 ，但是c1的条件是个范围，order by中只有c2和c3：** 范围之后全失效  
![这里写图片描述][70 8]  
**7、where 条件中有c1 ，但是c1的条件是个范围，order by中有c1、c2和c3：**  
![这里写图片描述][70 9]  
**8、where条件中没有c1，有c2，但是order by中有c1：**这个可能不同版本之间也有不同的答案  
![这里写图片描述][70 10]  
**9、where条件中没有c1，但是order by中也没有c1，只有c2和c3：** 这个就属于带头大哥死了![这里写图片描述][70 11]

**总结：** 产生filesort的原因主要有：

1、带头大哥或者中间兄弟丢了。

2、order by之后的字段顺序和复合索引中的字段顺序不一致。

3、没有where条件（不同版本的mysql可能不一样）

4、尚硅谷总结图：  
![这里写图片描述][70 12]

**filesort的两种算法：**双路排序和单路排序

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

**单路排序：** 双路排序需要读取两次磁盘，但是i/o是非常消耗内存和时间的，所以，能不能将读取磁盘的时间减少为1次呢？于是就有了单路排序：从磁盘中读取查询需要的所有列，按照order by列在buffer中对他们进行排序，然后扫描排序之后的列表进行输出，他的效率要比双路快，因为他将每一行的所有字段都保存在了内存中。

**单路排序的缺点：** 因为单路是将所有的字段都放在了内存中，假如，这张表是在太大，大的超过了sort\_buffer这个值了，那么每次都只能取sort\_buffer的数据，来进行排序（会创建临时tmp文件，然后多路合并），相当于多路了，这样一来效率比双路还要差。

**优化单路：**

1、尽量少用select \*，想要那个字段就查那个。

2、在配置文件中增大sort\_buffer\_size 参数的值。

3、适当调大max\_length\_for\_sort\_data，这个值将会决定filesort使用哪种算法，如果查询的字段大小综合小于这个值，那么将会使用单路算法，否则使用双路算法。但是如果设置的太高，因为单路算法是查询所有字段，这样就会导致单路超过sort\_buffer\_size的可能性增大，从而产生单路的缺点。

## 三、group by 之 temporary ##

**简介：** 分组之前必排序，换句话说，有using temporary 的地方必定有using filesort。using temporary 的意思就是创建临时文件，using filesort的意思就是使用文件排序，两者都是导致你查询速度变慢的原因。

**案例：**  
![这里写图片描述][70 13]

**几种常见情况：**  
![这里写图片描述][70 14]  
**总结：** 情况和order by的差不多，只是从using filesort变到了using temporary，优化方式当然是想方设法使用索引来进行排序，当然必要的时候可以修改或者重新创建一个符合需求的索引。

## 四、慢查询日志分析 ##

**查看是否开启：** show variables like ‘%slow\_query\_log%’; mysql默认是关闭的，需要我们手动去打开。  
![这里写图片描述][70 15]  
**开启慢查询：**

1、本次有效：即重启mysql之后就会失效。set global slow\_query\_log=1;  
![这里写图片描述][70 16]  
注意：mysql中默认的慢查询时间为10秒，但是生产环境中怎么可能让一条sql执行10秒钟。。。  
![这里写图片描述][70 17]

2、永久有效：即修改配置文件。这里我们配置慢查询的时间为3秒（超过三秒中的查询都会别记录到慢查询日志中）

slow_query_log=TRUE
    slow_query_log_file="D:/mariaDB/mariadb-10.0.17-winx64/slow_query_log.txt"
    long_query_time=3

![这里写图片描述][70 18]  
**测试：** 我们可以写几句sql测试一下。  
![这里写图片描述][70 19]  
**捕获慢查询：** select sleep（4） 的意思就是睡眠4秒钟。我们打开慢查询日志文件（slow\_query\_log.txt），我们可以看到超过3秒钟的sql都被记录了下来。  
![这里写图片描述][70 20]

[70]: /images/20210511/431d5acfdf994b1d87825cc5fa62e837.png
[70 1]: /images/20210511/e0e830d4f886402886c2b1edcd26692b.png
[70 2]: /images/20210511/9fff2f6041f94985a25fd6812e0e616b.png
[70 3]: /images/20210511/8c726cacfefb4cbea20bc9b372d5b1ae.png
[70 4]: /images/20210511/6df9f14275864323a68277acbd9d3286.png
[70 5]: /images/20210511/291a857cdc1345a8ab4cc46cdef13082.png
[70 6]: /images/20210511/8313b4e617a64cb789d66a536cdc7d2e.png
[70 7]: /images/20210511/4e5512f8ddfd4c1b8eb122971723036e.png
[70 8]: /images/20210511/da2a589a533649c29c250d081a6436e9.png
[70 9]: /images/20210511/9ed70753e9cb467a917ee745f4cd7d3c.png
[70 10]: /images/20210511/b8228ead210f4bce9405316c88aec270.png
[70 11]: /images/20210511/f30e9001df0b4062980b31c6dc291f23.png
[70 12]: /images/20210511/bd1dd78d418f40c99e02a9b2c04f4922.png
[70 13]: /images/20210511/09eb76ed86e241c5b0e6764f2e52220b.png
[70 14]: /images/20210511/44315598802e44e788ed8129a8e6b478.png
[70 15]: /images/20210511/34cf3eddfa35420aa564ec50b4b5fe4a.png
[70 16]: /images/20210511/2a80b1d6651642369187364e89afec08.png
[70 17]: /images/20210511/1a95effe4467463381590e7850314a38.png
[70 18]: /images/20210511/89d95950b3844ce3926eafbf0be437c4.png
[70 19]: /images/20210511/ecf1f956b2db4843a07b891d28e57b5d.png
[70 20]: /images/20210511/e6822124160d4b0fa39352a78b5681f4.png