Mysql-Order by的原理

MySQL中order by是怎么工作的？

准备案例

CREATE TABLE `user_info` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

写一个存储过程，往user_info中添加信息：

CREATE DEFINER=`root`@`localhost` PROCEDURE `userInfo`( )
BEGIN
	DECLARE
		i INT;
	
	SET i = 1;
	WHILE
			( i <= 100000 ) DO
			INSERT INTO user_info (`id`, `city`, `name`,`age`,`addr` )
		VALUES
			( i,'长沙',CONCAT(i,'糜了鹿'),i,'湖南省长沙市' );
		
		SET i = i + 1;
		
	END WHILE;
	
END

有了以上的数据后，然后写一条查询语句，带有排序的：

SELECT name,age,city from user_info where city='长沙' order by name limit 1000;

全字段排序

Mysql会为每一个线程分配一块额外的内存区域，用来排序，叫做：sort_buffer。

SQL语句执行的过程：

1. 创建sort_buffer，放入查询的字段（name,age,city）;
2. 从索引city中找到满足“长沙”条件的主键id；
3. 到主键id中取整行数据，把要查询的字段的值放入到sort_buffer中；
4. 继续从索引city取下一条满足条件记录的主键id，重复2、3步骤，直到city条件不满足后；
5. 对sort_buffer中的name进行排序操作；
6. 按照排序的最终结果取出1000条记录（limit 1000）。

sort_buffer_size：配置sort_buffer大小，如果配置的足够大，且排序的数据小于这个大小，排序就会再内存中进行，否则借助磁盘临时文件进行排序。

问题：排序都是在sort_buffer中完成，如果要排序的字段特别多的情况下，sort_buffer不够，就会产生很多分片的临时文件进行排序，效率会降低。

rowId 排序

max_length_for_sort_data:控制单行的数据长度，如果city、name、age这三个字段加起来总长度是36，把max_length_for_sort_data设置成16，sort_buffer里面只需要排序的字段加id字段。

SQL语句执行的过程：

1. 创建sort_buffer，放入排序的name字段，和id字段；
2. 从索引city中找到满足“长沙”条件的主键id；
3. 到主键id中取整行数据，把name，id字段放入sort_buffer中；
4. 继续从索引city取下一条满足条件记录的主键id，重复2、3步骤，直到city条件不满足后；
5. 对sort_buffer中的name进行排序操作；
6. 按照排序的最终结果取出1000条记录，最后根据ID回表查询要查询的字段返回。

以上执行的sql ，排序的数据量变小了，临时文件也会相应的变小。

两种排序的对比

全字段排序相比rowid排序来说，少了一次回表查询的动作，但是它限制于内存，内存小的话就会产生临时文件排序；

rowid排序，减少了临时文件，但是也相对的增加了回表操作。

innodb觉得rowid回表对磁盘访问造成的性能比较大，不会被优先选择。

优化排序

• 让字段天然就是排好序的：

  • alter table user_info add index city_user(city, name);
    

    把排序字段跟条件字段创建联合索引，这样B+树就帮我们保证了顺序，

    SQL语句执行的过程：

    1. 从索引（city,name）中取满足条件的主键id；
    2. 到主键id中取查询的字段放回到结果集中；
    3. 重复1、2操作知道city不满足条件为止。

    这样执行查询的话效率高很多，可以使用explain查看，extra中没有Using filesort了，就是没有用到文件排序。

• 使用覆盖索引：

  • alter table t add index city_user_age(city, name, age);
    

    SQL语句执行的过程：

    1. 从索引city_user_age中找到满足条件的记录，取出city, name, age字段（覆盖索引）放回到结果集中；
    2. 重复1操作，知道条件不满足，或者等于1000条记录了就退出并返回结果；

    可以通过一些手段去优化一些细节，特别是让字段默认就排好序的时候，这个时候可以大大的减少资源浪费。但是也是要根据你自己的查询语句去做出对应的对策，也不是所有的SQL语句是可以避免掉排序的。这里描述的是简单SQL，但是也是大同小异，执行过程都是一样的。