看看我们的t表定义是这样的:

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

1)现在有一个业务需求,我们的市民表上面我们要查询城市是杭州所有人的名字,年龄,并且按照名字排序返回前1000行,这个sql语句应该是怎样的?

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

2)上面的sql语句中的排序如果正常情况下排序是怎样排序的呢?

  • mysql会为我们的每个线程分配一个排序专用的内存sort_buffer。因为我们在city上面是建立了索引的,所以city的索引值对应的是主键id,我们的mysql找到所有值为杭州的city。去原表中把我们要返回的三个字段,city,name,age拿出来放到我们的排序内存中去,然后根据name进行快速排序,排完之后,再取出前一千条,然后返回。

3)我们知道我们的sort_buffer内存空间大小是有限的,那么要是我们的待排序数据量大于我们的sort_buffer,该怎么办?

  • 借用外部的内存,超的越多,借的越多。

4)上面的排序算法是全排序算法,除此之外还有其他算法吗?

  • row_id算法:比如说我上面的排序如果使用row_id排序的话那流程是这样的。
  • 先根据city把要排序的字段name和我们的主键id取出来放到我们的sort_buffer中进行排序。这个时候虽然name排好序了,但是我们的sort_buffer中是没有我们要的age和city的。所以我们还得按照name对应的顺序去我们的原表中把age,city,name三个都取出来一起返回。

5)全排序和row_id排序有什么区别?

  • 我们的全排序把要返回的字段全部丢进sort_buffer了,按照要求字段排完序就可以返回了,而我们的row_id字段啊,他虽然说只是把我们要排序的字段加载进sort_buffer,节约了内存空间,但是他还得回原表去取出我们需要返回的所有字段,这个操作是要和磁盘打交道的,耗时不得了。相当于是用时间换空间。假如我们的单行字段特别特别大的情况下,就需要使用row_id了。但innodb默认使用的是全字段排序。
  • innoDB默认使用全字段排序,也体现出了我们的数据库设计思想:内存够,尽量使用内存,不要去打扰磁盘。

6)全字段排序和row_id排序算法之间是如何切换的?

  • 通过一个参数:SET max_length_for_sort_data = 16; 单行的长度超过这个值,MySQL 就认为单行太大,要换一个算法。

7)是不是所有的order by语句都需要排序呢?

  • 假如一开始就是排好的,那我就不需要再去排序了啊。

8)按照上一个问题答案的思路,可不可以优化一下我上面的按照name排序的语句,让它一开始就是排好序的呢?

  • 我们可以建立一个联合索引city_name(city,name);建了联合索引的话,我们的city是所有,和city建立关系的name是会排好序的。也就是说假如现在我们使用全字段排序,把我们的city,name,age取进sort_buffer,那么name是已经排序好的了,不需要我再多此一举。

9)还有没有更加深度的排序优化策略?

  • 覆盖索引,假如索引对应的值都能满足了,那就回表都不用了。