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一样自增不会重复