mysql order by 算法_Orderby 排序优化

梦里梦外; 2022-11-05 03:02 218阅读 0赞

在日常的业务开发中，order by 排序是少不了的。但要写出高效的排序SQL，需要先花点精力和时间来了解排序的底层原理，这样才能找到优化排序的好策略。

排序的方式

index(索引排序，性能最佳)

尽可能使用索引字段来排序

filesort(文件排序)

2.1 双路排序

MySQL4.1 之前的版本，通过两次扫描磁盘，最终得到数据

先从磁盘中读取行指针和 order by 列，并对它们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读出(再一次从磁盘中读)，要对磁盘进行两次扫描，I\\O是很耗时的。

2.2 单路排序

MySQL4.1 之后，增加的更优排序算法

从磁盘读取查询需要的所有列，按照order by列在buffer(缓冲区)对它们进行排序，然后扫描排序后的列表进行输出

它的效率要更快一些，避免了第二次读取数据(从磁盘读)并且把随机I\\O变成了顺序I\\O

但是它会使用过多空间，因为它把每一行都保存在内存中了

不足：

在sort\_buffer中，单路算法比双路算法要多占用很多空间

因为单路算法是把所有字段都取出，所以有可能取出的数据总大小超出了 sort\_buffer(MySQL会给每个线程分配一块内存用于排序) 的容量，导致每次只能取 sort\_buffer 容量大小的数据，进行排序(创建tmp文件，多路合并)，排完再取出

sort\_buffer容量太小，再排......从而多次I\\O操作

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

使用单路排序满足的条件：

1. 查询语句所取出的字段类型大小总和要小于 max\_length\_for\_sort\_data

2. 排序字段中不包含text和blob类型

优化策略

order by时，只query需要的字段

1. 当query的字段大小总和小于max\_length\_for\_sort\_data，而且排序字段不是TEXT｜BLOB类型，会使用单路排序算法，否则使用多路排序算法

2. 两种算法的数据都有可能超出sort\_buffer的容量，超出之后，创建tmp文件进行合并排序，导致多次的I\\O，但是使用单路排序的风险更大，所以要提高sort\_buffer\_size

尝试提高sort\_buffer\_size

不管使用哪种算法，提高这个参数都会提高效率

要根据系统的自身能力去提高，因为这个参数是针对每个进程的

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

提高这个参数，会增加用改进算法的概率。

但如果设置得太高，数据总容量超出sort\_buffer\_size的概率会增大，明显症状是高的磁盘I\\O活动和低的处理器使用率

实例

数据表

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table: user

Create Table: CREATE TABLE \`user\` (

\`id\` int(10) unsigned NOT NULL AUTO\_INCREMENT,

\`name\` varchar(20) NOT NULL,

\`age\` int(10) NOT NULL DEFAULT '0',

\`city\` varchar(20) NOT NULL,

\`addr\` varchar(50) DEFAULT NULL,

PRIMARY KEY (\`id\`),

KEY \`idx\_name\_age\_city\` (\`name\`,\`age\`,\`city\`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_0900\_ai\_ci

order by能使用索引最左前缀

\* select id,name,age,city from user order by name;

\* select id,name,age,city from user order by name,age,city;

\* explain select id,name,age,city from user order by name desc,age desc,city desc;

![c0b68ab3144bd3be88eb04dbbefc1a6f.png][]

![4f67ea131e080416ad36a8eb54e19804.png][]

![4203ab404c213c635f45bf9adc7a25dc.png][]

如果where使用索引的最左前缀定义为常量，则order by 能使用索引

\* select \* from user where name = 'zhangsan' order by age,city;

\* select \* from user where name = 'zhangsan' and age = 20 order by city;

\* select \* from user where name = 'zhangsan' and age > 20 order by age,city;

![1e28c1f88449c5021472e1cada5b576a.png][]

![1d2c9f8cd85544d43ed234c80796f31d.png][]

![c39409625643889d5f708ca1361fbcb0.png][]

不能使用索引进行排序

select \* from user order by name,age,city;//query\*字段

select \* from user order by addr;//非索引字段排序

select \* from user order by name,addr;//含有非索引字段

select \* from user where age = 20 order by city;//跳过了name字段，违反最左前缀法则

select \* from user where name = 'zhangsan' order by city;//跳过了age字段，违反最左前缀法则

select \* from user where name = 'zhangsan' order by age,addr;//含有非索引字段

![98575b82315fd63fe779bcf43c540486.png][]

![215614827251840fc1442691ecae0285.png][]

![761cbfa8addcd6a26a37790ca8829412.png][]

![24ae4390d4af83c2af63783db680f0bc.png][]

![acfc610ea605db31e137e6ebcf6bc80d.png][]

本作品采用《CC 协议》，转载必须注明作者和本文链接

阿德

[c0b68ab3144bd3be88eb04dbbefc1a6f.png]: /images/20221023/c823e482cdaa49f599c301f6a56f6aec.png
[4f67ea131e080416ad36a8eb54e19804.png]: /images/20221023/f47f3299443c4724b27ede1e3276f7d4.png
[4203ab404c213c635f45bf9adc7a25dc.png]: /images/20221023/5be3f28d5e06469ea3b9616dbb5f4ab2.png
[1e28c1f88449c5021472e1cada5b576a.png]: /images/20221023/6d2cfa83b4a94b2b9b9872284e721202.png
[1d2c9f8cd85544d43ed234c80796f31d.png]: /images/20221023/040d522708904d5fbd5ef8f31cb0442c.png
[c39409625643889d5f708ca1361fbcb0.png]: /images/20221023/291f6047f13a41f1ad05e5bec47db1d9.png
[98575b82315fd63fe779bcf43c540486.png]: /images/20221023/21cb0aeb3e564cba9af91d29f3dd163a.png
[215614827251840fc1442691ecae0285.png]: /images/20221023/5b9d9b4f92e54d9f8401474365261a72.png
[761cbfa8addcd6a26a37790ca8829412.png]: /images/20221023/3882119088554979b8d34905069cb1f6.png
[24ae4390d4af83c2af63783db680f0bc.png]: /images/20221023/113d8204be6e417bbe795048414ef36c.png
[acfc610ea605db31e137e6ebcf6bc80d.png]: /images/20221023/3c37e584d1284209842bf5224c8a0055.png