作者:不才陈某

前言
  • 在实际的开发中一定会碰到根据某个字段进行排序后来显示结果的需求,但是你真的理解order by在 Mysql 底层是如何执行的吗?
  • 假设你要查询城市是苏州的所有人名字,并且按照姓名进行排序返回前 1000 个人的姓名、年龄,这条 sql 语句应该如何写?
  • 首先创建一张用户表,sql 语句如下:
  • CREATE TABLE user ( id int(11) NOT NULL, city varchar(16) NOT NULL, name varchar(16) NOT NULL, age int(11) NOT NULL, PRIMARY KEY (id), KEY city (city) ) ENGINE=InnoDB;

    select city,name,age from user where city='苏州' order by name limit 1000;

    全字段排序

    mysql中order by用法(天天写orderby你知道Mysql底层如何执行吗)(1)

    mysql中order by用法(天天写orderby你知道Mysql底层如何执行吗)(2)

    1. 初始化 sort_buffer,确定放入 name、city、age 这三个字段。
    2. 从索引 city 找到第一个满足city='苏州'条件的主键id,也就是图中的ID3。
    3. 到主键id索引取出整行,取name、city、age三个字段的值,存入sort_buffer中。
    4. 从索引city取下一个记录的主键 id。
    5. 重复步骤 3、4 直到 city 的值不满足查询条件为止,对应的主键 id 也就是图中的IDX。
    6. 对sort_buffer中的数据按照字段name做快速排序。
    7. 按照排序结果取前 1000 行返回给客户端。

    mysql中order by用法(天天写orderby你知道Mysql底层如何执行吗)(3)

    rowid 排序

    SET max_length_for_sort_data = 16;

    1. 初始化sort_buffer,确定放入两个字段,即name和id。
    2. 从索引 city 找到第一个满足city='苏州'条件的主键id,也就是图中的ID3。
    3. 到主键id索引取出整行,取 name、id 这两个字段,存入 sort_buffer 中。从索引city取下一个记录的主键 id。
    4. 重复步骤 3、4 直到 city 的值不满足查询条件为止,对应的主键 id 也就是图中的IDX。
    5. 对sort_buffer中的数据按照字段name做快速排序。
    6. 遍历排序结果,取前 1000 行,并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。
    7. 这个执行流程的示意图如下,我把它称为rowid排序。

    mysql中order by用法(天天写orderby你知道Mysql底层如何执行吗)(4)

    全字段排序 VS rowid 排序如何避免排序

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

    mysql中order by用法(天天写orderby你知道Mysql底层如何执行吗)(5)

    mysql中order by用法(天天写orderby你知道Mysql底层如何执行吗)(6)

    mysql中order by用法(天天写orderby你知道Mysql底层如何执行吗)(7)

    讲真,这两款idea插件,能治愈你英语不好的病alter table user add index city_user_age(city, name, age);

    mysql中order by用法(天天写orderby你知道Mysql底层如何执行吗)(8)

    总结

    来源:掘金 链接:https://juejin.im/post/5e945b9651882573b7537c2a

    ,