乐趣区

关于mysql:mysqlorder-by排序

jiezi 
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  ;

全字段排序:

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

排序过程中, 可能在内存中实现, 也可能应用内部排序, 取决于排序所需内存以及参数 sort_buffer_size, 小于这个值则应用内存疾速排序, 否则应用内部归并排序

/* 关上 optimizer_trace，只对本线程无效 */
SET optimizer_trace='enabled=on'; 

/* @a 保留 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from  performance_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 = 16; -- 如果单行长度超过这个值, 就会应用 rowid 排序
  1. 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
  2. 从索引 city 找到第一个满足 city=’ 杭州’条件的主键 id，也就是图中的 ID_X；
  3. 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
  4. 从索引 city 取下一个记录的主键 id；
  5. 反复步骤 3、4 直到不满足 city=’ 杭州’条件为止，也就是图中的 ID_Y；
  6. 对 sort_buffer 中的数据依照字段 name 进行排序；
  7. 遍历排序后果，取前 1000 行，并依照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

全字段排序 VSrowid 排序

全字段排序节约内存, 在内存足够时会应用,
rowid 排序会要求回表多造成磁盘读, 不会被优先选择

如果 name 字段自身就是有序的, 那就能够不须要排序操作, 节俭响应工夫;
如果在表上创立一个 city 和 name 的联结索引:

alter table t add index city_user(city, name);
  1. 从索引 (city,name) 找到第一个满足 city=’ 杭州’条件的主键 id；
  2. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为后果集的一部分间接返回；从索引 (city,name) 取下一个记录主键 id；
  3. 反复步骤 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 排序

  1. 创立一个长期表。这个长期表应用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了前面形容不便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。
  2. 从 words 表中，按主键程序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 别离存入长期表的 R 和 W 字段中，到此，扫描行数是 10000。
  3. 当初长期表有 10000 行数据了，接下来你要在这个没有索引的内存长期表上，依照字段 R 排序。
  4. 初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。
  5. 从内存长期表中一行一行地取出 R 值和地位信息（我前面会和你解释这里为什么是“地位信息”），别离存入 sort_buffer 中的两个字段里。这个过程要对内存长期表做全表扫描，此时扫描行数减少 10000，变成了 20000。
  6. 在 sort_buffer 中依据 R 的值进行排序。留神，这个过程没有波及到表操作，所以不会减少扫描行数。
  7. 排序实现后，取出前三个后果的地位信息，顺次到内存长期表中取出 word 值，返回给客户端。这个过程中，拜访了表的三行数据，总扫描行数变成了 20003。

总结: order by rand() 应用了内存长期表，内存长期表排序的时候应用了 rowid 排序办法。

磁盘长期表

退出移动版