乐趣区

关于mysql:mysqlorder-by排序

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 排序办法。

磁盘长期表

退出移动版