这阅读本篇之前 建议还是从 mysql order by 工作流程开始阅读
还是以上篇文章的表来说明哈
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`city` varchar(16) NOT NULL,
`name` varchar(16) NOT NULL,
`age` int(11) NOT NULL,
`addr` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `city` (`city`)
) ENGINE=InnoDB;
select city,name,age from t where city='山西' order by name limit 1000 ;
MySQL使用optimizer_trace 来查看执行的流程 具体使用 我测试使用的mysql版本是5.6.38
1.开启optimizer_trace (mysql5.6 开始支持optimizer_trace 默认是关闭的)
SET optimizer_trace = "enabled=on";
-
执行SQL todo
select city,name,age from t where city='山西' order by name limit 1000 ; -
查看optimizer_trace 详情
select trace from `information_schema`.`optimizer_trace`
执行之后结果如下 截取部分
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`t`.`city` = '山西')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`t`.`city` = '山西')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`t`.`city` = '山西')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`t`.`city` = '山西')"
}
]
}
},
{
"table_dependencies": [
{
"table": "`t`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`t`",
"field": "city",
"equals": "'山西'",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`t`",
"range_analysis": {
"table_scan": {
"rows": 2643,
"cost": 540.7
},
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "city",
"usable": true,
"key_parts": [
"city",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "city",
"ranges": [
"山西 <= city <= 山西"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 1824,
"cost": 2189.8,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`t`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "city",
"rows": 1824,
"cost": 394.8,
"chosen": true
},
{
"access_type": "scan",
"rows": 1824,
"cost": 538.6,
"chosen": false
}
]
},
"cost_for_plan": 394.8,
"rows_for_plan": 1824,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`t`.`city` = '山西')",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`t`",
"attached": "(`t`.`city` = '山西')"
}
]
}
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`t`.`name`",
"items": [
{
"item": "`t`.`name`"
}
],
"resulting_clause_is_simple": true,
"resulting_clause": "`t`.`name`"
}
},
{
"refine_plan": [
{
"table": "`t`",
"pushed_index_condition": "(`t`.`city` = '山西')",
"table_condition_attached": null
}
]
},
{
"added_back_ref_condition": "((`t`.`city` <=> '山西'))"
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`t`",
"field": "name"
}
],
"filesort_priority_queue_optimization": {
"limit": 1000,
"rows_estimate": 9513,
"row_size": 492,
"memory_available": 262144,
"strip_additional_fields": {
"row_size": 396,
"chosen": false,
"cause": "not_enough_space"
}
},
"filesort_execution": [
],
"filesort_summary": {
"rows": 1825,
"examined_rows": 1825,
"number_of_tmp_files": 4,
"sort_buffer_size": 262000,
"sort_mode": "<sort_key, additional_fields>"
}
}
]
}
}
]
}
主要看 join_execution 这部分数据
第一步对需要排序数据信息 filesort_information 这部没啥可说的
第二步 filesort_priority_queue_optimization 表示优先队列排序(也叫堆排序)
1. rows_estimate 读取行数
2. memory_available 使用内存大小
接下来 filesort_summary
-
filesort_summary.examined_rows 排序扫描行数
-
filesort_summary.number_of_tmp_files 表示是否使用外部排序
number_of_tmp_files = 0 说明sort_buffer_size 的大小比需要排序的数据量大 直接在内存中就可以完成排序 上面的列子中为4 表示使用外部排序 外部排序使用的是归并算法 可以这么理解 mysql需要将排序的数据分成4份 每一份单独排序之后存在临时文件中 然后吧这4个文件合并成一个大的有序的文件 -
filesort_summary.sort_buffer_size
-
filesort_summary.sort_mode 排序方式
<sort_key, additional_fields> 可以称为全字段排序 将所查询的字段全部放入内存中 进行排序 <sort_key, rowid> <sort_key, packed_additional_fields> 和第一种类似 不过排序过程中对字符串做了 ”紧凑“处理 及时name字段定义varchar(16) 在排序过程中还是按照实际的长度来分配空间
