江苏龙网

  1. 主页 > 生活百科 > >

教你一招：orderBy排序优化

Order

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

文章插图
 
【教你一招：orderBy排序优化】 
排序的方式index（索引排序，性能最佳）尽可能使用索引字段来排序filesort（文件排序）2.1 双路排序MySQL4.1 之前的版本，通过两次扫描磁盘，最终得到数据 。先从磁盘中读取行指针和 order by 列，并对它们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读出（再一次从磁盘中读），要对磁盘进行两次扫描，IO是很耗时的 。2.2 单路排序MySQL4.1 之后，增加的更优排序算法，从磁盘读取查询需要的所有列，按照order by列在buffer（缓冲区）对它们进行序，然后扫描排序后的列表进行输出，它的效率要更快一些，避免了第二次读取数据（从磁盘读）并且把随机IO变成了顺序IO，但是它会使用过多空间，因为它把每一行都保存在内存中了 。不足：在sort_buffer中，单路算法比双路算法要多占用很多空间，因为单路算法是把所有字段都取出，所以有可能取出的数据总大小超出了，sort_buffer（MySQL会给每个线程分配一块内存用于排序） 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取出 。sort_buffer容量太小，再排......从而多次IO操作，本想着省一次IO操作，反而导致了大量的IO操作，反而得不偿失 。使用单路排序满足的条件：1. 查询语句所取出的字段类型大小总和要小于max_length_for_sort_data2. 排序字段中不包含text和blob类型优化策略3.1 只query需要的字段1. 当query的字段大小总和小于max_length_for_sort_data，而且排序字段不是TEXT｜BLOB类型，会使用单路排序算法，否则使用多路排序算法 。2. 两种算法的数据都有可能超出sort_buffer的容量，超出之后，创建tmp文件进行合并排序，导致多次的IO，但是使用单路排序的风险更大，所以要提高sort_buffer_size 。3.2 尝试提高sortbuffersize不管使用哪种算法，提高这个参数都会提高效率，要根据系统的自身能力去提高，因为这个参数是针对每个进程的 。3.3 尝试提高maxlengthforsortdata提高这个参数，会增加用改进算法的概率 。但如果设置得太高，数据总容量超出sort_buffer_size的概率会增大，明显症状是高的磁盘IO活动和低的处理器使用率 。实例数据表*************************** ***************************Table: userCreateTable: 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_ciorder 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;
教你一招：orderBy排序优化

文章插图
 

教你一招：orderBy排序优化

文章插图
 

教你一招：orderBy排序优化

文章插图
 
如果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;
教你一招：orderBy排序优化

文章插图
 

教你一招：orderBy排序优化

文章插图
 

教你一招：orderBy排序优化

文章插图
 
不能使用索引进行排序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;//含有非索引字段


推荐阅读


上一篇：三分钟读懂“中台”

下一篇：超级推荐付费方式 超级推荐是怎么收费的