天天写order by，你知道Mysql底层执行原理吗？( 二 ) _Mysql底层

这个执行流程的示意图如下，我把它称为rowid排序。

文章插图
【天天写order by，你知道Mysql底层执行原理吗？】

对比全字段排序，rowid排序多了一次回表查询，即是多了第7步的查询主键索引树。

全字段排序 VS rowid 排序

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。
如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。
这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。
对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

如何避免排序

其实，并不是所有的order by语句，都需要排序操作的。从上面分析的执行过程，我们可以看到，MySQL 之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。
如果能够保证从city这个索引上取出来的行，天然就是按照 name 递增排序的话，是不是就可以不用再排序了呢？
因此想到了联合索引，创建(city,name)联合索引，sql 语句如下：

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

此时的索引树如下：
在这个索引里面，我们依然可以用树搜索的方式定位到第一个满足city='苏州'的记录，并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要 city 的值是杭州，name 的值就一定是有序的。
按照上图，整个查询的流程如下：对应的流程图如下：

文章插图

1. 从索引(city,name)找到第一个满足 city='苏州’条件的主键 id 。2. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回。3. 从索引(city,name)取下一个记录主键 id 。4. 重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city='苏州'条件时循环结束。

可以看到，这个查询过程不需要临时表，也不需要排序。接下来，我们用 explain 的结果来印证一下。

文章插图

从图中可以看到，Extra字段中没有Using filesort了，也就是不需要排序了。而且由于(city,name)这个联合索引本身有序，所以这个查询也不用把 4000 行全都读一遍，只要找到满足条件的前 1000 条记录就可以退出了。也就是说，在我们这个例子里，只需要扫描 1000 次。
难道仅仅这样就能满足了？此条查询语句是否能再优化呢？
朋友们还记得覆盖索引吗？覆盖索引的好处就是能够避免再次回表查询
我们创建(city,name,age)联合索引，这样在执行上面的查询语句就能使用覆盖索引了，避免了回表查询了，sql 语句如下：

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