mysql-order by原理

测试数据创建

1.新建表

CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `city` varchar(16) NOT NULL COMMENT '城市',
  `name` varchar(16) NOT NULL COMMENT '姓名',
  `age` int(11) NOT NULL COMMENT '年龄',
  `addr` varchar(128) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.刷测试数据存储过程新建

-- 声明存储过程
DELIMITER //
CREATE PROCEDURE callback()
begin
  declare num int;
  set num = 1;
  LOOP_LABLE :
  loop
    INSERT INTO user_info(id,city,`name`,age,addr) 
    VALUES(num,CONCAT("成都", num),CONCAT("小李", num),CONCAT(num),CONCAT("高新区",num));
    set num = num + 1;
    if num >= 100000
    then leave LOOP_LABLE;
    end if;
  end loop;
end;

3.执行存储过程

CALL callback();

4.删除存储过程

drop procedure IF EXISTS callback;

5.再重新创建存储过程并执行

CREATE PROCEDURE callback()
begin
  declare num int;
  set num = 100000;
  LOOP_LABLE :
  loop
    INSERT INTO user_info(id,city,`name`,age,addr) 
    VALUES(num,"广州",CONCAT("小李", num),CONCAT(num),CONCAT("高新区",num));
    set num = num + 1;
    if num >= 200000
    then leave LOOP_LABLE;
    end if;
  end loop;
end;

两种排序算法

全字段排序

我们在工作中常常使用这一的写法

select * from user_info i
where i.city='成都1'
order by age desc limit 400,100

对应执行计划执行计划各个列解读可以参考跳转

mysql查询已经选用了city作为索引 age就不会走索引(仅仅支持一个索引 优化的可以建立组合索引)

Using filesort表需要排序,MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

执行流程

1.初始化 sort_buffer，确定放入 city、name、age，addr 这四个字段；

2.从索引 city 找到第一个满足 city='广州’条件的主键 id索引；

3.通过主键索引取出整行，取 name、city、age、addr 四个字段的值，存入 sort_buffer 中 根据select 如果select name city from ...则只会放入name,city,age到sort_buffer；

4.从索引 city 取下一个记录的主键 id；

5.重复步骤 3、4 直到 city 的值不满足查询条件为止；

6.对 sort_buffer 中的数据按照字段 age做快速排序；按照排序结果,去除前400行再取最后100行

正常流程再内存就能完成排序,取决于sort_buffer_size大小 如果排序数据小于sort_buffer_size则在内存就完成排序,大于则会利用文件辅助排序,最终将所有文件整合成1个文件再完成排序

使用此命令可以查看sort_buffer_size大小

show VARIABLES like '%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 * from user_info i
where i.city='广州'
order by age desc limit 0,10000

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

 

sort_buffer_size :排序内存的大小

number_of_tmp_files 利用了多少个文件完成排序

examined_rows 参与了排序的行数

sort_mode 排序算法       

　　　　　　rowid 对应的是上文的rowid排序

　　　　　　additional_fields 对应的是全字段排序

　　　　　　packed_additional_fields 是"打包数据排序模式”，此排序模式改进仅仅在于将char和varchar字段存到sort buffer中，该模式下用户定义的varchar(255)会根据实际字符类型来sort_buffer中排序，例如dzx三个字符，只需要三个字符存储空间，相比原先的255个字符空间，可以压缩空间，使的sort_buffer可以排序更多的字段

优化

     1.建立组合索引alter table user_info add INDEX `idx_city_age` (`city`,`age`) 按照组合索引作前缀匹配原则age是可以走索引的

     2.如果万不得已使用了这种算法,我们可以根据业务需求尽量减少select 返回 比如只需要city  则将select * 改为select city。避免超过sort_buffer利用临时文件辅助排序，尽量在内存完成排序

rowId排序

    某些情况如果我们单行内容很大,比如商品描述的富文本,查询结果又很多，那么就会需要大量的临时文件辅助排序,排序性能会很差,mysql通过max_length_for_sort_data来判断是否使用这种算法,如果单行长度超过这个参数值则会使用rowId排序

show VARIABLES like '%max_length_for_sort_data%';

我们通过改小此参数让数据库选择这种排序算法(仅会修改此会话)

SET max_length_for_sort_data = 16;

 

执行步骤

1.初始化 sort_buffer，确定放入 id,age 这2个字段；

2.从索引 city 找到第一个满足 city='广州’条件的主键 id索引；

3.通过主键索引取出整行，取 id,age 2个字段的值，存入 sort_buffer 中

4.从索引 city 取下一个记录的主键 id；

5.重复步骤 3、4 直到 city 的值不满足查询条件为止；

6.对 sort_buffer 中的数据按照字段 age做快速排序；

7.通过sort_buffer里面的排序id limit 400,100再次回表获得相关数据(多了一次查询)

优化

1.通过全字段排序和row_id排序 全字段排序是优于row_id排序的

2.通过建立组合索引让取出的数据天然支持排序不需要依赖sortbuffer进行排序

规避一些坑

如标签打印场景

product_id	level	code
1	1	A
1	1	B
1	1	C
1	1	D
1	1	E

 

1.前端点击某个商品标签打印查询出5个标签 根据优先级排序  查询结果是A B  C D E

select * from table  where product_id=? order by level desc

2.前端用户说打印前2个标 

select * from table  where product_id=?  limit 0,2 order by level desc 

这个时候查出数据有几种情况

1.查出结果也是A B C D E 取前2条 A B 没有任何问题

2.查出结果是是C B D E A类似这样无序的 导致业务异常

 

第一种情况level要保证也走索引。因为索引是天然排序的无须经过排序

第二种如果level没有命中索引，就会导致通过mysql sort_buffer 进行排序 可能就会导致无须 想要走索引 需要通过prouct_id,level建立组合索引

没有命中索引情况下 需要排序字段区分度唯一 比如主键id一样自增不会重复