MySQL的order by是怎么工作的

先定义一个市民表

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;

select city,name,age from t where city=‘杭州’ order by name limit 1000 ;

一 查询执行顺序:

1. 每次查询前,MySQL会给每个线程分配一块内存用于排序,称为sort_buffer;
2. 确定放入city name age 三个字段(通过select后面的字段判断);
3. 判断 city 是否有索引(没有索引,全表扫描);
4. 从索引city找到第一个满足 city=‘杭州’ 条件的主键 ID;
5. 根据主键ID索引获取整行信息,取city name age 三个字段放入sort_buffer中;
6. 从索引city取下一个满足 city=‘杭州’ 条件的主键 ID;
7. 重复五六步骤,直到查询的值不满足 city=‘杭州’ 条件为止;
8. 对sort_buffer中的数据根据name进行快速排序;
9. 取出前1000的数据返回客户端

根据name进行排序,是在内存中排序还是在磁盘中排序,这取决于sort_buffer_size(MySQL为sort_buffer设置的内存大小),如果name小于sort_buffer,内存中排序,反之则利用磁盘的辅助文件进行排序;

二 如果返回值的字段很多,占用的内存大于sort_buffer大小,MySQL会返回该字段主键和要排序的字段,然后返回1000条时,通过ID获取字段对象

1. 每次查询前,MySQL会给每个线程分配一块内存用于排序,称为sort_buffer;
2. 确定放入city name age 三个字段(通过select后面的字段判断);
3. 判断 city 是否有索引(没有索引,全表扫描);
4. 从索引city找到第一个满足 city=‘杭州’ 条件的主键 ID 和 排序字段 name;
5. 从索引city取下一个满足 city=‘杭州’ 条件的主键 ID;
6. 重复四五步骤,直到查询的值不满足 city=‘杭州’ 条件为止;
7. 对sort_buffer中的数据根据name进行快速排序;
8. 根据ID索引获取整行信息,取city name age 三个字段放入;
9. 返回数据到客户端

MySQL的设计思路:如果内存够,就多使用内存,尽量减少磁盘访问

SQL优化:

创建索引

alter table t add index city_user(city, name);

三 插入数据的时候,根据city,name进行排序

1. 从索引(city,name)找到第一个满足city="杭州"条件的主键ID
2. 通过主键ID回表查询这条整行数据,取name age city 数据返回;
3. 从索引(city,name)查询下一条记录主键ID;
4. 重复二 三步骤,直到查询1000条数据或不满足条件数据为止;

再次优化:

创建索引:

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

四 不需要回表查询额外字段

1. 从索引(city,name,name)找到第一个满足city="杭州"条件的数据返回
2. 获取索引下一条记录,同样获取数据返回
3. 直到查询1000条数据或不满足条件数据为止;