mysql select order by 多个字段 mysql多字段排序原理

转载

mob6454cc6d3e23 2024-02-02 18:39:52

文章标签 mysql java 数据库 ci 数据 文章分类 MySQL 数据库

文章目录

前言
全字段排序
rowid排序
总结

前言

order by相比大家都用过，它的用法我就不再多介绍了，今天我们主要是介绍order by在mysql 的中的工作原理，方便大家可以更加熟练的使用order by。

全字段排序

首先我们先创建一个表，下面是建表的sql语句

CREATE TABLE `t` (
  `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;

然后我们根据name排序，查出来前一千条，杭州的

select id,city,name,age,addr from t where city='杭州' order by name limit 1000  ;

这里我们给city建一个索引，大家不要给city和name建一个联合索引（如果见了联合索引，这个直接就是有序的，他就不会再去排序了，我们就没法看到这个order by的执行过程了）。之后我们用explain语句看下这个sql 的执行过程。

mysql select order by 多个字段 mysql多字段排序原理_java

这个extra中的using filesort，就是表示需要排序，然后mysql会给每一个线程分配一个sort buffer用来排序。

然后这个排序过程大致分为一下几步
1.走索引查找到到city为杭州的城市的id
2.通过id将值取出来放到sortbuffer中
3.在sort buffer中，将数据按照name排序，取出前一千行，将结果返回

上面就是order by在mysql中的大致执行过程

但是还有一个点就是，在使用sort buffer 排序的时候，可能会用到外部空间，当排序的数据大小大于sort buffer 的大小的时候，就会用到外部磁盘临时文件排序。下面是查看一个语句是否使用了磁盘临时文件排序

/* 打开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 city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

mysql select order by 多个字段 mysql多字段排序原理_数据_02