江苏建设网官方网站,龙华营销型网站建设公司,开源小程序模板,建设网站需要多少钱济南兴田德润地址ORDER BY调优的核心原理#xff0c;原则是利用索引的有序性跳过排序环节
关于ORDER BY语句的一些尝试
我们使用employees表进行尝试#xff0c;索引情况如下 在执行计划的结果中#xff0c;Extra里如果存在#xff0c;Using filesort则表示#xff0c;排序没有使用到索…ORDER BY调优的核心原理原则是利用索引的有序性跳过排序环节
关于ORDER BY语句的一些尝试
我们使用employees表进行尝试索引情况如下 在执行计划的结果中Extra里如果存在Using filesort则表示排序没有使用到索引。
explain
select *
from employees
order by first_name,last_name;结果 并没有用到索引发生了全表扫描
explain
select *
from employees
order by first_name,last_name
limit 10;结果 这次的查询就用到了索引。为什么一次是ALL一次是index呢 因为第一次相当于对整张表进行排序的排序是基于成本计算的在优化器发现全表扫描开销更低时会直接使用全表扫描。而第二次是仅仅对前10条数据进行排序扫描索引的成本要小于扫面全表所以用到了索引。 -----------------------------------------------------------------------------------------------------------------
explain
select *
from employees
where first_name Bader
order by last_name;结果 这句SQL是用到了索引排序的当执行查询时查找出来的数据为[‘Bader’last_name[i]emp_no]因为索引是有序的Bader’是确定的那么数据已经按照last_name排好序了就跳过了排序的环节。 -----------------------------------------------------------------------------------------------------------------
explain
select *
from employees
where first_name Bader
order by first_name;结果 根据执行结果是使用了索引的因为在执行查询语句时查找出来的数据为[first_namelast_nameemp_no]这一部分数据已经是按照first_name排好序的所以不需要再次进行排序了。 -----------------------------------------------------------------------------------------------------------------
explain
select *
from employees
where first_name Baderand last_name Peng
order by last_name;结果 跟上面的同理因为在执行查询语句时查找出来的数据为[Baderlast_name[i]emp_no]这一部分数据已经是按照last_name排好序的所以不需要再次进行排序了。 -----------------------------------------------------------------------------------------------------------------
explain
select *
from employees
order by first_name,emp_no limit 10;结果 根据执行结果 该语句没有用到索引因为两个排序字段存在于不同的两个索引中会先按first_name进行排序再将相同first_name的数据按照emp_no进行排序。 -----------------------------------------------------------------------------------------------------------------
explain
select *
from employees
order by first_name desc ,last_name asc limit 10;结果 因为索引中的两个字段在进行排序中的升降序不一致所以无法使用索引。 -----------------------------------------------------------------------------------------------------------------
explain
select *
from employees
where first_name Bader
order by last_name limit 10;结果 根据结果得知在进行查询时使用了索引但在排序时使用的是Using filesort。说明排序时没有用到索引。组合索引中part1范围查询使用part2进行排序是无法使用索引排序的。
排序模式
Using filesort排序原理目前MySQL使用了三种排序模式
模式一rowid排序常规排序
排序过程
从表中获取满足where条件的数据。对于每条记录将记录的主键及排序字段id,order_column取出放入sort buffer由sort_buffer_size控制大小。如果sort buffer能存放所有满足条件的id,order_column则进行排序否则当sort buffer存满后会将sort buffer中的数据排序并存放到临时文件中。 在没有产生临时文件时在内存中使用快速排序算法如果产生了临时文件则需要利用归并排序算法从而保证记录有序 扫描排序好的id,order_column数据并利用id去取select需要返回的其他字段。返回结果集。
排序特点
看sort buffer是否能存放查询出来的所有的结果集如果不满足就会差生临时文件一次排序需要两次IO 第一次把查询出来的id,order_column结果集放入sort buffer中第二次通过id去获取需要返回的其他字段。由于返回结果是按照order_column进行排序的所以主键id是乱序的会存在随机IO的问题。之前文中提到在用主键id取值前会按照主键id进行排序并放入一个缓存中该缓存大小是由read_rnd_buffer_size控制接着再去取记录从而把随机IO转换成顺序IO。
模式二全字段排序优化排序
排序过程
跟第一种模式相比有几点不同
直接取出表中需要的所有字段放到sort buffer种由于sort buffer已经包含了查询需要的所有的字段因此sort buffer种排序完成后直接返回结果集
全字段排序 vs rowid排序
优点性能的提升无需两次IO因为全字段排序已经将需要的所有字段存储到了sort buffer种无需再次用主键id去表中获取缺点由于全字段排序会将需要的所有的字段放入sort buffer中所以占用空间比较大如果sort buffer不够大那么很容易产生临时文件
排序算法的选择
max_length_for_sort_data当OEDER BY中出现的字段的总长度小于该值使用全字段排序反之则使用rowid排序。
模式三打包字段排序
MySQL5.7引入与模式二工作原理一致不同点在于会将字段紧密的排列在一起而不是固定长度的空间。 例如一个字段定义为VARCHAR(32)值为’yes’在不打包的情况下占用32字节打包的情况下23字节。
参数汇总
变量作用sort_buffer_size指定sort buffer的大小max_length_for_fort_data当ORDER BY中出现字段的总长度小于该值时使用全字段排序反之使用rowid排序read_rnd_buffer_size按照主键排序后存放的缓存区大小
使用optimizer_trace分析排序过程
explain展示的排序方式很有限仅仅是Using filesort如果我们想了解更多的细节就需要使用optimizer_trace进行分析了。 以下面语句为例
select *
from employees
where first_name Bader
order by last_name;执行
SET OPTIMIZER_TRACEenabledon,END_MARKERS_IN_JSONon;
SET optimizer_trace_offset-30,optimizer_trace_limit30;开启OPTIMIZER_TRACE执行示例SQL语句再次执行
select * from information_schema.OPTIMIZER_TRACE where QUERY like %Bader%;获取分析结果将trace字段的内容复制出来进行分析 我们主要关注的是filesort_summary “filesort_summary”: { “memory_available”: 262144, “key_size”: 265, “row_size”: 399, “max_rows_per_buffer”: 502, “num_rows_estimate”: 927744, “num_rows_found”: 22287, “num_initial_chunks_spilled_to_disk”: 0, “peak_memory_used”: 204314, “sort_algorithm”: “std::sort”, “unpacked_addon_fields”: “using_priority_queue”, “sort_mode”: “varlen_sort_key, additional_fields” } 其相关字段解读如下
memory_available可用内存其实就是fort_buffer_size设置的值num_rows_found有多少条数据参与排序越小越好num_initial_chunks_spilled_to_disk产生了几个临时文件0表示完全基于内存排序sort_mode varlen_sort_key,rowid使用了rowid排序模式varlen_sort_key, additional_fields使用了全字段排序模式varlen_sort_key, packed_additional_fields使用了打包字段排序模式
如何调优ORDER BY
利用索引防止filesort发生如果发生了filesort且无法避免就要对filesort进行优化
如何调优filesort
调大sort_buffer_size减少/避免临时文件的产生从而进行的归并操作 当optimizer_trace的结果中 num_initial_chunks_spilled_to_disk的值较大时需要调整show status like ‘%sort_merge_passes%’;查看发生归并操作的次数 调大read_rnd_buffer_size让一次顺序IO返回更多的结果设置合理的max_length_for_sort_data的值
