涿州网站网站建设,mvc5网站开发之六 管理员,wordpress 改ip,摄影网站建设策划书一、Order by与Group by优化
Case1#xff1a; 分析#xff1a; 利用最左前缀法则#xff1a;中间字段不能断#xff0c;因此查询用到了name索引#xff0c;从key_len74也能看出#xff0c;age索引列用在排序过程中#xff0c;因为Extra字段里没有using filesort
注意…一、Order by与Group by优化
Case1 分析 利用最左前缀法则中间字段不能断因此查询用到了name索引从key_len74也能看出age索引列用在排序过程中因为Extra字段里没有using filesort
注意 order by age 虽然用到了索引但是不会在key_len列体现
Case 2 分析 从explain的执行结果来看key_len74查询使用了name索引由于用了position进行排序跳过了age出现了Using filesort。
注意 这里跳过了age这里position是无序的所以不会走索引
Case 3 分析 查找只用到索引nameage和position用于排序无Using filesort。
Case 4 分析 和Case 3中explain的执行结果一样但是出现了Using filesort因为索引的创建顺序为name,age,position但是排序的时候age和position颠倒位置了。
重点注意 这边颠倒age和positionmysql不会像前面提到的where后面一样优化最左前缀
Case 5 分析 与Case 4对比在Extra中并未出现Using filesort因为age为 常量在排序中被优化所以索引未颠倒不会出现Using filesort。
Case 6 分析 虽然排序的字段列与索引顺序一样且order by默认升序这里position desc变成了降序导致与索引的排序方式不同从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。
Case 7 分析 对于排序来说多个相等条件也是范围查询
Case 8 可以用覆盖索引优化 二、Using filesort 文件排序原理详解
filesort文件排序方式
在使用explain分析查询的时候利用有序索引获取有序数据显示Using index。如果MySQL在排序的时候没有使用到索引那么就会输出using filesort即使用文件排序。
文件排序是通过相应的排序算法将取得的数据在内存中进行排序
MySQL需要将数据在内存中进行排序所使用的内存区域也就是我们通过sort_buffer_size系统变量所设置的sort buffer(排序区)。这个sort buffer是每个Thread独享的所以说可能在同一时刻在MySQL中可能存在多个sort buffer内存区域。
1. 双路排序又叫回表排序模式
首先根据相应的条件取出相应的 排序字段 和可以直接定位行数据的行 ID然后在 sort buffer 内存排序中进行排序排序完后需要再次取回其它需要的字段用trace工具可以看到sort_mode信息里显示 sort_key, rowid
第一遍扫描出需要排序的字段然后进行排序后根据排序结果第二遍再扫描一下需要select的列数据。这样会引起大量的随机IO效率不高但是节约内存。排序使用quick sort但是如果内存不够则会按照 block 进行排序将排序结果写入磁盘文件然后再将结果合并。
2. 单路排序
一次性取出满足条件行的 所有字段然后在 sort buffer 内存中进行排序用trace工具可以看到sort_mode信息里显示 sort_key, additional_fields 或者 sort_key, packed_additional_fields 不需要回表获取其他字段效率高但将所有字段取出在sort buffer中排序占用内存
如何选择文件排序方式
MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。
如果 字段的总长度小于max_length_for_sort_data 那么使用 单路排序模式如果 字段的总长度大于max_length_for_sort_data 那么使用 双路排序模式
示例验证下各种排序方式 查看下这条sql对应trace结果如下(只展示排序部分)
mysql set session optimizer_traceenabledon,end_markers_in_jsonon; --开启trace
mysql select * from employees where name zhuge order by position;
mysql select * from information_schema.OPTIMIZER_TRACE;trace排序部分结果
join_execution: { --Sql执行阶段select#: 1,steps: [{filesort_information: [{direction: asc,table: employees,field: position}] /* filesort_information */,filesort_priority_queue_optimization: {usable: false,cause: not applicable (no LIMIT)} /* filesort_priority_queue_optimization */,filesort_execution: [] /* filesort_execution */,filesort_summary: { --文件排序信息rows: 10000, --预计扫描行数examined_rows: 10000, --参与排序的行number_of_tmp_files: 3, --使用临时文件的个数这个值如果为0代表全部使用的sort_buffer内存排序否则使用的磁盘文件排序sort_buffer_size: 262056, --排序缓存的大小单位Bytesort_mode: sort_key, packed_additional_fields --排序方式这里用的单路排序} /* filesort_summary */}] /* steps */} /* join_execution */mysql set max_length_for_sort_data 10; --employees表所有字段长度总和肯定大于10字节
mysql select * from employees where name zhuge order by position;
mysql select * from information_schema.OPTIMIZER_TRACE;trace排序部分结果
join_execution: {select#: 1,steps: [{filesort_information: [{direction: asc,table: employees,field: position}] /* filesort_information */,filesort_priority_queue_optimization: {usable: false,cause: not applicable (no LIMIT)} /* filesort_priority_queue_optimization */,filesort_execution: [] /* filesort_execution */,filesort_summary: {rows: 10000,examined_rows: 10000,number_of_tmp_files: 2,sort_buffer_size: 262136, sort_mode: sort_key, rowid --排序方式这里用的双路排序} /* filesort_summary */}] /* steps */} /* join_execution */mysql set session optimizer_traceenabledoff; --关闭trace我们先看单路排序的详细过程
从索引name找到第一个满足 name ‘zhuge’ 条件的主键 id根据主键 id 取出整行取出所有字段的值存入 sort_buffer 中从索引name找到下一个满足 name ‘zhuge’ 条件的主键 id重复步骤 2、3 直到不满足 name ‘zhuge’对 sort_buffer 中的数据按照字段 position 进行排序返回结果给客户端
我们再看下双路排序的详细过程
从索引 name 找到第一个满足 name ‘zhuge’ 的主键id根据主键 id 取出整行把排序字段 position 和主键 id 这两个字段放到 sort buffer 中从索引 name 取下一个满足 name ‘zhuge’ 记录的主键 id重复 3、4 直到不满足 name ‘zhuge’对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序遍历排序好的 id 和字段 position按照 id 的值回到原表中取出 所有字段的值返回给客户端
三、总结 其实对比两个排序模式单路排序会把所有需要查询的字段都放到 sort buffer 中而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序然后再通过主键回到原表查询需要的字段。 如果 MySQL 排序内存 sort_buffer 配置的比较小并且没有条件继续增加了可以适当把 max_length_for_sort_data 配置小点让优化器选择使用双路排序算法可以在sort_buffer 中一次排序更多的行只是需要再根据主键回到原表取数据。 如果 MySQL 排序内存有条件可以配置比较大可以适当增大 max_length_for_sort_data 的值让优化器优先选择全字段排序(单路排序)把需要的字段放到 sort_buffer 中这样排序后就会直接从内存里返回查询结果了。 所以MySQL通过 max_length_for_sort_data 这个参数来控制排序在不同场景使用不同的排序模式从而提升排序效率。
注意 如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序但不能因为这个就随便增大sort_buffer(默认1M)mysql很多参数设置都是做过优化的不要轻易调整。
