最全MySQL8.0实战教程 22 MySQL的优化 22.8 SQL优化【3 优化order by语句】

﹏ヽ暗。殇╰゛Y 2024-03-25 12:34 47阅读 0赞

#### 最全MySQL8.0实战教程 ####

#### 文章目录 ####

*   *   *  最全MySQL8.0实战教程
         *  22 MySQL的优化
         *   *  22.8 SQL优化【3 优化order by语句】
             *   *  22.8.3 环境准备
                 *  22.8.4 两种排序方式
                 *  22.8.5 Filesort的优化

[【黑马程序员MySQL知识精讲+mysql实战案例\_零基础mysql数据库入门到高级全套教程】 ][MySQL_mysql_mysql_]

#### 22 MySQL的优化 ####

##### 22.8 SQL优化【3 优化order by语句】 #####

###### 22.8.3 环境准备 ######

CREATE TABLE `emp` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(100) NOT NULL,
      `age` int(3) NOT NULL,
      `salary` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    );
     
    insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');
     
    create index idx_emp_age_salary on emp(age,salary);

![在这里插入图片描述][52a447646e974b55a1572e637804e6ea.png_pic_center]

###### 22.8.4 两种排序方式 ######

第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

![在这里插入图片描述][358b99db83cd4ba38f90d0d344ad0acc.png_pic_center]

这就是没有用索引的情况，其效率是比较低的

![在这里插入图片描述][9b517ae14e68450ca3b9f94a46e442de.png_pic_center]

刚刚是因为用了 \* 号，而且id 是主键

加上age

![在这里插入图片描述][9e4a18d021e3454d96b70db53554d6b1.png_pic_center]

当排序方式不同时

![在这里插入图片描述][1f0a977456cb4d7597df53e53c942a35.png_pic_center]

所以order by 后面的排序字段，尽量要求排序方式相同

且顺序尽量和组合索引字段顺序一致，如果不一致就像下面这样

![在这里插入图片描述][c0903b1867824837941d22387a1ad20e.png_pic_center]

###### 22.8.5 Filesort的优化 ######

通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快 Filesort的排序操作。对于Filesort ， MySQL 有两种排序算法：

1） 两次扫描算法 ：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。

2）一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

MySQL 通过比较系统变量 max\_length\_for\_sort\_data 的大小和Query语句取出的字段总大小， 来判定是否那种排序算法，如果max\_length\_for\_sort\_data 更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高 sort\_buffer\_size 和 max\_length\_for\_sort\_data 系统变量，来增大排序区的大小，提高排序的效率。

查看当前系统变量值：

show variables like '变量名';

[MySQL_mysql_mysql_]: https://www.bilibili.com/video/BV1iF411z7Pu
[52a447646e974b55a1572e637804e6ea.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/65babfd23ec84a05a24285b0588f09d7.png
[358b99db83cd4ba38f90d0d344ad0acc.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/b1696b9ffcba4482b3fa2400875975bb.png
[9b517ae14e68450ca3b9f94a46e442de.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/96b122ca4c034c679902a55f2ba8dbda.png
[9e4a18d021e3454d96b70db53554d6b1.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/e1f7a88072174013ac7d3157c75d5d94.png
[1f0a977456cb4d7597df53e53c942a35.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/67f6dc9f29e44148b56b40a7e63c5053.png
[c0903b1867824837941d22387a1ad20e.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/b8ff0cb83d89470884ff0bf791400b39.png