mysql--order by排序 mysql排序

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;

如果要查询city是杭州的所有人名字,并且按姓名排序返回前1000个人的姓名和年龄,sql可以这么写:

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

全字段排序: 【mysql--order by排序】

文章图片

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

排序过程中,可能在内存中完成,也可能使用外部排序,取决于排序所需内存以及参数 sort_buffer_size,小于这个值则使用内存快速排序,否则使用外部归并排序

/* 打开optimizer_trace，只对本线程有效 */ SET optimizer_trace='enabled=on'; /* @a保存Innodb_rows_read的初始值 */ select VARIABLE_VALUE into @a fromperformance_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; //值为4001

文章图片

number_of_tmp_files 表示的是，排序过程中使用的临时文件数。
packed_additional_fields表示字符串做了“紧凑”处理,name字段为varchar(16),排序过程中按实际长度分配空间
rowid排序当返回字段太多时,sort_buffer中存放的行数会很少,需要使用多个临时文件,排序性能会很差;

SET max_length_for_sort_data = https://www.it610.com/article/16; --如果单行长度超过这个值,就会使用rowid排序

初始化 sort_buffer，确定放入两个字段，即 name 和 id；
从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；
对 sort_buffer 中的数据按照字段 name 进行排序；
遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

文章图片

全字段排序VSrowid排序全字段排序浪费内存,在内存足够时会使用,
rowid排序会要求回表多造成磁盘读,不会被优先选择
如果name字段本身就是有序的,那就可以不需要排序操作,节省响应时间;
如果在表上创建一个city和name的联合索引:

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

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

文章图片

如果使用覆盖索引,则可以避免回表,进一步节省时间

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

文章图片

随机展示信息某个英语学习APP首页需要随机展示三个单词,数据库如下:

mysql> CREATE TABLE `words` ( `id` int(11) NOT NULL AUTO_INCREMENT, `word` varchar(64) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; delimiter ; ; create procedure idata() begin declare i int; set i=0; while i<10000 do insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10)))); set i=i+1; end while; end; ; delimiter ; call idata();

内存临时表
查询sql可以使用rand():

mysql> select word from words order by rand() limit 3;

上述sql执行过程中会使用临时表,临时表优先选择rowid排序

创建一个临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。
从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。
现在临时表有 10000 行数据了，接下来你要在这个没有索引的内存临时表上，按照字段 R 排序。
初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。
从内存临时表中一行一行地取出 R 值和位置信息（我后面会和你解释这里为什么是“位置信息”），分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。
在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。
排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。

文章图片