mysql数据库order by正确使用姿势杂谈

喜欢ヅ旅行 2022-11-17 10:16 206阅读 0赞

## 一、引言 ##

在MySQL数据库中，Order by语句的使用频率是比较高的。但是众所周知，在使用这个语句时，往往会降低数据查询的性能，而最近也经常收到一些慢查询的告警，通过排查是因为order by排序引起的，那么order by在mysql内部是如何执行的呢，以及应该如何正确使用来提升SQL的性能，下面简单分析一下。

## 二、案例分析 ##

order by在mysql内部排序一般分为两种，一是利用索引的有序性获取有序数据，二是利用内存/磁盘文件排序获取结果 ，废话少说，先建一张表插入100万数据并以此表举例来进一步分析。

CREATE TABLE `product` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `product_name` VARCHAR(128) NOT NULL,
      `brand_name` varchar(16) NOT NULL,
      `price` int(11) NOT NULL,
      `image` varchar(128) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_productName` (`product_name`)
    ) ENGINE=INNODB;
    
    delimiter ;;
    create procedure idata()
    	begin declare i int; 
    	set i=0; 
    	while i<1000 do 
    		insert into product(product_name,brand_name,price,image) 
    			values(CONCAT('productName',i),CONCAT('brandName',i),200+i,'http://test/test.jpg'); 
    	 set i=i+1;
       end while;
    	end;;
    	delimiter ;
    	call idata();//循环1000次

#### 2.1利用索引排序 ####

先来执行一个简单查询，然后通过explain或desc查一下，其SQL如下：

explain
    SELECT * FROM product  ORDER BY id DESC limit 10000

![image.png][]  
通过执行计划分析，扫描行数为39W，type列的值为“index”，则说明mysql用了索引扫描来做排序，扫描索引本身是很快的，因此该SQL的性能还是较好的。

explain
    SELECT * FROM product WHERE product_name IN ('productName1','productName2') 
    ORDER BY product_name DESC LIMIT 10000

![image.png][image.png 1]  
接下来我们想通过productName来进行排序，这个SQL也非常简单，是查询商品名称为“produnctName1”和"productName2"的商品，用执行计划看看该SQL的情况，type为range,key为idx\_productName,extra则为using index condition,通过上面可以看到使用了idx\_productName索引扫描来做排序。此时如果我们把这个SQL改一下按ID进行降序排序，看看是否还会利用idx\_productName这个索引排序功能呢，调整如下：

explain
    SELECT * FROM product WHERE product_name IN ('productName1','productName2') 
    ORDER BY id DESC LIMIT 10000

![image.png][image.png 2]  
此时看到extra的值变为了"using index condition;using filesort"，Extra 这个字段中的“Using filesort”表示的就是需要排序，表明并没有使用idx\_productName这个索引排序，而是需要使用mysql缓存，并在缓存中进行排序。现在再在该表上新加一个索引，建一个商品名称和品牌的联合索引，此时按商品筛选并按品牌来进行排序，看看是否可以利用到索引来进行排序，SQL如下：

ALTER TABLE product ADD INDEX idx_productName_brandName (product_name,brand_name)
    desc
    SELECT * FROM product WHERE product_name IN ('productName1','productName2','productName3') 
    ORDER BY id DESC LIMIT 10000

![image.png][image.png 3]  
再次通过执行查看，此时并没有用到索引来排序，而是用mysql的sortbuffer来进行排序了。通过上面的实践例子，对利用索引排序可以简单总结一下：  
mysql的索引本身已经排序，若order by能正确利用索引则不需要在额外排一次序，ORDER BY的sql语句应遵循以下规则，将filesort优化为index:  
1、ORDER BY 语句使用索引最左前列。  
2、使用WHERE子句与ORDER BY子句条件列组合满足索引最左前列。

##### 备注：以下情况会导致filesort： #####

*  没有覆盖索引
 *  where语句与order by语句使用了不同的索引，MySQL每回只采用一个索引
 *  同时使用了ASC和DESC
 *  where语句或者ORDER BY语句中索引列使用了表达式，包括函数表达式
 *  order by子句中加入了非索引列，且非索引列不在where子句中
 *  order by或者它与where组合没有满足索引最左前列
 *  join时使用右表的字段排序

#### 2.2、利用内存/文件排序 ####

在上面的例子中，当extra值里有filesort则需要利用内存进行排序。先说一下mysql利用内存或文件进行排序时内部的执行流程，mysql利用内存/文件排序，分为两种：一种是全字段排序，也就是将满足条件列全部放入sortBuffer,另一种则是rowid排序：

##### 2.2.1 全字段排序执行流程 #####

*  1、初始化 sort\_buffer，mysql会为每个线程分配。
 *  2、从表中获取满足WHERE条件的记录。
 *  3、对于每条记录，将记录的主键ID,然后根据ID回表查询该表所有的字段取出放入sort buffer中。
 *  4、从索引取出满足下一行的主键ID,重复上面第2、第3步聚，取出所有满足条件行，然后在sortbuffer中进行排序,如果需要排序的内容较大，sort buffer满后，进行排序并固化到临时文件中。(排序算法采用的是快速排序算法也就是优先在内存中进行排序)。
 *  5、若排序中产生了临时文件，需要利用归并排序算法，保证临时文件中记录是有序的
 *  6、 将获取的结果集返回给客户端。  
    先回过头来对上面的例子进行进一步分析，脚本如下：

/* 打开optimizer_trace，只对本线程有效 */
    SET optimizer_trace='enabled=on';
    /* @a保存Innodb_rows_read的初始值 */
    select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';
    
    SELECT * FROM product WHERE product_name IN ('productName1','productName2','productName3') 
    ORDER BY id DESC LIMIT 10000
    /* 查看 OPTIMIZER_TRACE 输出 */
    SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`

![image.png][image.png 3]  
![image.png][image.png 4]  
  先将商品名称满足productName1、productName2、productName3记录找到，然后将id和product\_name 字段存放在sortbuffer中进行排序，因为此例子当中表的字段不算大，所以并没有使用临时文件来进行排序，而是使用内存进行排序，通过“number\_of\_tmp\_files：0”可以分析出来，然后继续一条条查询所有满足条件的行进行排序，排序完成后取出10000条给到客户端。  
  如果number\_of\_tmp\_files大于0，就需要使用外部排序，也就是临时文件，外部排序一般使用归并排序算法，为了让number\_of\_tmp\_files大于0，对上面的SQL进行简单调整，如下：

SET optimizer_trace='enabled=on';
    
    select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';
    
    SELECT * FROM product 
    ORDER BY brand_name DESC LIMIT 200000
    
    SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`

![image.png][image.png 5]  
通过调整后，number\_of\_tmp\_files临时文件分为了5个，这个表MySQL 将需要排序的数据分成 5 份，每一份单独排序后存在这些临时文件中，然后把这 5个有序文件再合并成一个有序的大文件，上面图中sort\_mode 里面的 packed\_additional\_fields 的意思是，排序过程对字符串做了“紧凑”处理。即使 brand\_name字段的定义是 varchar(16)，在排序过程中还是要按照实际长度来分配空间的。

##### 2.2.1 rowId排序 #####

在上面这个算法过程里面，只对原表的数据读了一遍，剩下的操作都是在 sort\_buffer 和临时文件中执行的，但这个算法有一个问题，就是如果查询要返回的字段很多的话，那么 sort\_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差，因此MYSQL进行了优化，采用rowId排序，也就是双路排序：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序，与rowid排序相关的一个参数是max\_length\_for\_sort\_data：

SET max_length_for_sort_data = 10;
    max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数，它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。

MYSQL执行流程如下：

*  1、初始化 sort\_buffer，确定放入两个字段，即 brand\_name和 id；
 *  2、从索引找到第一个满足条件的主键 id，到主键 id 索引取出整行，取 brand\_name、id 这两个字段，存入 sort\_buffer 中；
 *  3、从索引取下一个记录的主键 id；
 *  4、重复步骤 2、3 直到不满足 条件为止，然后对 sort\_buffer 中的数据按照字段 brand\_name进行排序；
 *  5、遍历排序结果取前行，并按照 id 的值回到原表中取出所有字段字段返回给客户端。  
    从上面的流程中可以看到，相对于全字段排序，rowid 排序会要求回表多造成磁盘读，因此在mysql中不会被优先选择。  
    现简单小结一下：  
    1、利用内存\\文件排序，mysql会优先选择全字段排序，也就是单路排序。  
    2、如果取出行的数据太大，大小由max\_length\_for\_sort\_data来确定，会采用rowid的方式来进行排序，这种排序要求回表多次，造成多次磁盘读，性能会受到影响。  
    3、为避免rowId排序，可以增大max\_length\_for\_sort\_data，或者减少查询字段。

## 三、优化建议 ##

在实际项目中，能不使用排序就尽量不要使用排序，如果无法避免排序操作时，则优先利用索引的排序方式，如果实在无法满足利用索引排序的情况下，尽可能让 MySQL 选择使用第二种单路算法来进行排序。这样可以减少大量的随机IO操作,很大幅度地提高排序的效率。

*  1、加大 max\_length\_for\_sort\_data 参数的设置  
       在 MySQL 中,决定使用老式排序算法还是改进版排序算法是通过参数max\_length\_for\_sort\_data来决定的。当所有返回字段的最大长度小于这个参数值时,MySQL 就会选择改进后的排序算法,反之则选择老式的算法，所以,如果有充足的内存让MySQL 存放须要返回的非排序字段,就可以加大这个参数的值来让 MySQL 选择使用改进版的排序算法。
 *  2、去掉不必要的返回字段  
       当内存不是很充裕时,不能简单地通过强行加大上面的参数来强迫 MySQL 去使用改进版的排序算法,否则可能会造成 MySQL 不得不将数据分成很多段,然后进行排序,这样可能会得不偿失。此时就须要去掉不必要的返回字段,让返回结果长度适应 max\_length\_for\_sort\_data 参数的限制。  
       同时也要规范MySQL开发规范，尽量避免大字段。当有select 查询列含有大字段blob或者text 的时候,MySQL 会选择常规排序。 “The optimizer selects which filesort algorithm to use. It normally uses the modified algorithm except when [BLOB][] or [TEXT][BLOB] columns are involved, in which case it uses the original algorithm.”
 *  3、增大 sort\_buffer\_size 参数设置  
       这个值如果过小的话,再加上你一次返回的条数过多,那么很可能就会分很多次进行排序,然后最后将每次的排序结果再串联起来,这样就会更慢,增大 sort\_buffer\_size 并不是为了让 MySQL选择改进版的排序算法,而是为了让MySQL尽量减少在排序过程中对须要排序的数据进行分段,因为分段会造成 MySQL 不得不使用临时表来进行交换排序。但是这个值不是越大越好：  
    1）、 sort\_buffer\_size 是一个connection级参数,在每个connection第一次需要使用这个buffer的时候,一次性分配设置的内存。  
    2）、 sort\_buffer\_size 并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。

## 四、数据字典 ##

##### 4.1 explain说明 #####

id:每个select子句的标识id  
select\_type:select语句的类型  
table:当前表名  
显示查询将访问的分区，如果你的查询是基于分区表  
type：当前表内访问方式  
possible\_keys:可能使用到的索引  
key:经过优化器评估最终使用的索引  
key\_length:使用到的索引长度  
ref:引用到的上一个表的列  
rows:rows\_examined，要得到最终记录索要扫描经过的记录数  
filtered:表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。  
Extra:额外的信息说明

##### 4.2 type #####

![image.png][image.png 6]

##### 4.3 常见extra #####

*  Using where: 查询条件中
 *  Using index condition: 索引下推（Index Condition Pushdown）
 *  Using filesort：Server层需要做额外的排序操作，需要优化，让排序使用到索引
 *  no matching row in const table：唯一索引（包括主键）查询不到数据
 *  Using index：覆盖索引，查询的行都在对应的索引中
 *  Using MRR：MRR优化，就是为了减少访问磁盘的次数，回表时，先把主键索引排序后再访问，因为接近的索引，可能在相同的页上
 *  Using temporary：使用了临时表，需要优化

作者：唐伟辉

[image.png]: /images/20221022/c7445a1f4471488a87364d65c1d72094.png
[image.png 1]: /images/20221022/c2f8a9f5ad7c4e1d9142c3b3d4cac753.png
[image.png 2]: /images/20221022/cb039657f38e452bad28733db941216e.png
[image.png 3]: /images/20221022/a1a90a7ba8a14bc4b2fe80cd2e56864c.png
[image.png 4]: /images/20221022/694def855b3343f797196504f13565af.png
[image.png 5]: /images/20221022/88a34bd6e1e04e60a0ddda6dc1d1e3ad.png
[BLOB]: http://dev.mysql.com/doc/refman/5.6/en/blob.html
[image.png 6]: /images/20221022/1cb3d36300674608a2ea1391f7fcb9ff.png