Mysql优化-order by

数据库目录-CSDN博客

1. 技巧

• ORDER BY 语句使用索引最左前列
• 使用Where子句与Order BY子句条件列组合满足索引最

2. 排序模式

2.1. rowid排序（常规排序)

流程：

1. 从表中获取满足WHERE条件的记录
2. 对于每条记录，将记录的主键及排序键(id,order_column)取出，放入sort buffer(由sortbuffer_size控制)
3. 如果sort buffer能存放所有满足条件的(id,order_column),则进行排序；否则sort buffer满后，排序并写到临时文件，排序算法：快速排序算法
4. 若排序中产生了临时文件,需要利用归并排序算法，从而保证记录有序
5. 循环执行上述过程，直到所有满足条件的记录全部参与排序
6. 扫描排好序的(id,order column)对，并利用id去取SELECT需要返回的其他字段

特点：

1. 看sort buffer是否能存放结果集里面的所有(id,order_.column),如果不满足，就会产生临时文件
2. 一次排序需要两次IO
3. (id,order,column)扔到sort_buffer,
4. 通过id去获取需要返回的其他字段。由于返回结果是按照order column排序的，所以id是乱序的，会存在随机IO问题。
5. MySQL内部钎对这种情况做了个优化，在用ID取数据之前，会按照ID排排序拼放到一个缓存里面，这个缓存大小由read_rnd_buer_size控制，接着再丢取记泉，从而把随机IO转换成顺序IO

2.2. 全字段排序（优化排序）

• 直接取出SQL中需要的所有字段，放到sort buffer
• 由于sort buffer已经包含了查询需要的所有字段，因此在sort bufferr中排序完成后可直接返回

2.3. 算法如何选择

max_length_for_sort_data

当ORDER BY SQL中出现字段的总长度小于该值，使用全字段排序，否侧使用rowid排序

2.4. 打包字段排序

MySQL5.7引入

全字段模式的优化，工作原理一样，但是将字段紧密地排列在一起，而不是使用固定长度空间

VARCHAR(255) "yes” : 不打包：255字节； 打包：2+3字节

3. 参数汇总

3.1. sort_buffer_size

指定sort bufferl的大小

3.2. max_length_for _sort _data

当ORDER BY SQL中出现字段的总长度小于该值，使用全字段排序，否则使用rowid排序

3.3. read_rnd_buffer_size

按照主键排序后存放的缓存区

4. 示例

create index idx_test03_c1234 on test03(c1,c2,c3,c4)

4.1. 常量的的范围内可以顺序不一样

optimizer优化器会自己调整在常量的的范围内可以顺序不一样,但最好一至,避免底层翻译

explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3'

4.2. 范围之后失效

explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4'

explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';

自动排序4个

explain select * from test03 where c1='a1' and c2='a2' order by c3   3

4.3. filesort

有：

explain select * from test03 where c1='a1' and c2='a2' order by c4; 
explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;

无：

select * from test03 where c1='a1' and c5='a5' order by c2,c3;
select * from test03 where c1='a1' and c2='a2' order by c2,c3;
select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;