mysql进阶（五）

小鱼儿 2022-05-18 04:13 220阅读 0赞

mysql查询截取分析：

![70][]

### 1.优化原则：小表驱动大表；小数据集驱动大的数据集 ###

![70 1][]

![70 2][]

![70 3][]

### 2.order by 排序优化 ###

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

![70 4][]

采用的表：

create table tb1A(
    	#id int primary key not null auto_increment,
    	age int,
    	birth timestamp not null
    
    );
    
    
    insert into tb1A(age,birth) values (22,now());
    insert into tb1A(age,birth) values (23,now());
    insert into tb1A(age,birth) values (24,now());
    
    create index idx_A_ageBirth on tb1A(age,birth);

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

![70 5][]

如果索引的字段，都是降序那也会用到索引,但是一会儿降序，一边升序就会出现问题。32

![70 6][]

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

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

**特点：**从磁盘中取排序字段，在buffer中进行排序，再从磁盘中取其他字段。取一批数据，要对磁盘进行两次扫描，I/O操作耗时，在mysql4.1之后就开始出现优化算法——单路排序

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

**在sort\_buffer中，方法B比方法A要占用很多空间，因为方法B是把所有字段都提取出，所以有可能取出的数据总大小超出了sort\_buffer的容量，导致每次只能读取sort\_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排序完再去取sort-buffer容量大小，再排…… 从而多次I/O。这样会导致效率，大大降低！**

**为例解决上述可能出现的问题：可以调整数据库服务器的参数，max\_length\_for\_sort\_data，sort\_buffer**

![70 7][]

![70 8][]

### **3.group by** ###

![70 9][]

### 4.慢查询优化日志 ###

![70 10][]

**①**默认情况下，mysql数据库没有开启慢查询日志，需要通过收动设置这个参数。建议只在优化情况下开启，因为开启慢查询日志会给性能带来一定的影响。慢查询日志支持将日志记录转入文件。

show variables like "%low_query_log%";

![70 11][]

开启慢查询日志：（下列方式支队当前数据库有效，重启mysql之后将失效）

set global slow_query_log = 1;

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

修改my.cnf文件，\[mysqld\]下增加或者修改参数

show\_query\_log和slow\_query\_log\_file后，然后重启mysql服务器。

开启了慢查询之后：通过控制long\_query\_time控制， long\_query\_time默认为10s，当sql运行时间超过大于10s，就会被记录下来。命令如下：

show variables like 'long_query_time%';

设置时间为3s;当新建会话的时候才会生效。而且只是暂时修改，当重启mysql就会失效，只有修改配置文件才行！

set global long_query_time 3;

![70 12][]

下面是慢查询日志的内容：慢查询日志的名称一般为hostname\_slow.log

![70 13][]

查看 系统中慢查询的记录：

show global status like '%slow_queries%';

![70 14][]

![70 15][]

![70 16][]

[70]: /images/20220518/de44fbdc66334e9a8ff08436b0e04696.png
[70 1]: /images/20220518/0e4bf4d7c6bb4bb8bbb165618f7ac922.png
[70 2]: /images/20220518/be576222217544d4968646bd02262153.png
[70 3]: /images/20220518/37566df7dc8345828319b07b88388e6d.png
[70 4]: /images/20220518/c4141ab432b54b53953869921ae0c4ce.png
[70 5]: /images/20220518/fac261494e87434f80d85e9002ddde92.png
[70 6]: /images/20220518/9467e162bd8c41ca963b9fafbf6d4a3b.png
[70 7]: /images/20220518/7eb6e306b76a423b80c9a8e51bd76b58.png
[70 8]: /images/20220518/31457974c0d848909f7bccd296fcb38c.png
[70 9]: /images/20220518/855c4379d1ce45b5a4a2405a10e01bc0.png
[70 10]: /images/20220518/237a12ac7f6b4e048b858d0733335e53.png
[70 11]: /images/20220518/6533ca738887464180be560b680a641f.png
[70 12]: /images/20220518/6982df737a05484080b90c466aaf826d.png
[70 13]: /images/20220518/3eedb1651fa44e9d889aa2b9d793e84f.png
[70 14]: /images/20220518/7f8a4efb5cbd474c805888a3bebe85fc.png
[70 15]: /images/20220518/82f19ae067204429891961696017b500.png
[70 16]: /images/20220518/17526d801961404793aea049c8143afb.png