sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。

但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

案例复现

调小sort_buffer_size参数，让SQL语句使用临时文件进行排序

使用存储过程创建一个大表并插入数据，注意varchar要定义大一点

create table student(
id int AUTO_INCREMENT,
name varchar(1000),
addr varchar(1000) ,
 PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

drop procedure if exists insert_tt;
delimiter $$
create procedure insert_tt()
begin
    declare n int default 1;
    declare MAX int default 1000000;
    while n < MAX do
        insert into student values (null,n,n);
        set n = n + 1;
    end while;
end
$$
delimiter ;

call insert_tt();

lsof -p 4581 |grep deleted 查看的临时文件

执行一个排序的查询

观察临时文件，可以看到2个临时文件24G左右了

而数据文件总共才241M，排序花费了24G

原因在于，排序的时候对于order by字段来讲不能启用压缩，按照可变字段varchar按照定义的长度进行内存和物理空间占用。上面的表定义中name 和addr的字段定义都是varchar（100），因此在排序中占用的临时文件巨大。

总结

（1）对于排序字段（order by后面的字段），不要过大的定义可变字段的长度，应该合理定义，这些空间虽然在Innodb层存储会压缩，但是对于排序的字段却不行。

（2）避免排序，在需要排序的字段上加索引。