MySQL 索引优化实践，java大数据分析技术栈
一个人可以走的很快，但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎扫码加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！字段的值，存入 sort_buffer 中**从索引name找到下一个满足 name = ‘SAN’ 条件的主键 id重复步骤 2、3 直到不满足 name = ‘SAN’对 sort_buffer 中
分析： 和Case 3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为name,age,position，但是排序的时候age和position颠倒位置了。
Case 5：
explain select * from employees where name = ‘SAN’ and age = 30 order by position, age;
复制代码
分析： 与Case 4对比，在Extra中并未出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。
Case 6：
explain select * from employees where name = ‘SAN’ order by age desc ,position asc;
复制代码
分析： 虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。
Case 7：
explain select * from employees where name in (‘SAN’, ‘SI’, ‘WU’) order by age ,position;
复制代码
分析： 对于排序来说，多个相等条件也是范围查询
Case 8：
explain select * from employees where name > ‘a’ order by name;
复制代码
可以用覆盖索引优化
explain select name, age, position from employees where name > ‘a’ order by name;
复制代码
优化总结：
1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。 2、order by满足两种情况会使用Using index。
-
order by语句使用索引最左前列。
-
使用where子句与order by子句条件列组合满足索引最左前列。
3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。 4、如果order by的条件不在索引列上，就会产生Using filesort。 5、能用覆盖索引尽量用覆盖索引 6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。
Using filesort 文件排序原理详解
filesort 文件排序方式
-
单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
-
双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示< sort_key, rowid >
-
MySQL 通过比较系统变量 **max_length_for_sort_data(默认1024字节) **的大小和需要查询的字段总大小来判断使用哪种排序模式。
-
如果 字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式；
-
如果 字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模式。
示例验证下各种排序方式：
查看下这条sql对应trace结果如下(只展示排序部分)：
set session optimizer_trace=“enabled=on”,end_markers_in_json=on; --开启trace
select * from employees where name = ‘SAN’ order by position;
select * from information_schema.OPTIMIZER_TRACE;
trace排序部分结果：
{
“steps”: [
{
“join_preparation”: {
“select#”: 1,
“steps”: [
{
“expanded_query”: “/* select#1 */ select employees.id AS id,employees.name AS name,employees.age AS age,employees.position AS position,employees.hire_time AS hire_time from employees where (employees.name > ‘a’) order by employees.position”
}
] /* steps */
} /* join_preparation */
},
{
“join_optimization”: {
“select#”: 1,
“steps”: [
{
“condition_processing”: {
“condition”: “WHERE”,
“original_condition”: “(employees.name > ‘a’)”,
“steps”: [
{
“transformation”: “equality_propagation”,
“resulting_condition”: “(employees.name > ‘a’)”
},
{
“transformation”: “constant_propagation”,
“resulting_condition”: “(employees.name > ‘a’)”
},
{
“transformation”: “trivial_condition_removal”,
“resulting_condition”: “(employees.name > ‘a’)”
}
] /* steps */
} /* condition_processing */
},
{
“substitute_generated_columns”: {
} /* substitute_generated_columns */
},
{
“table_dependencies”: [
{
“table”: “employees”,
“row_may_be_null”: false,
“map_bit”: 0,
“depends_on_map_bits”: [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
“ref_optimizer_key_uses”: [
] /* ref_optimizer_key_uses */
},
{
“rows_estimation”: [
{
“table”: “employees”,
“range_analysis”: {
“table_scan”: {
“rows”: 120085,
“cost”: 24372
} /* table_scan */,
“potential_range_indexes”: [
{
“index”: “PRIMARY”,
“usable”: false,
“cause”: “not_applicable”
},
{
“index”: “idx_name_age_position”,
“usable”: true,
“key_parts”: [
“name”,
“age”,
“position”,
“id”
] /* key_parts */
}
] /* potential_range_indexes */,
“setup_range_conditions”: [
] /* setup_range_conditions */,
“group_index_range”: {
“chosen”: false,
“cause”: “not_group_by_or_distinct”
} /* group_index_range */,
“analyzing_range_alternatives”: {
“range_scan_alternatives”: [
{
“index”: “idx_name_age_position”,
“ranges”: [
“a < name”
] /* ranges */,
“index_dives_for_eq_ranges”: true,
“rowid_ordered”: false,
“using_mrr”: false,
“index_only”: false,
“rows”: 60042,
“cost”: 72051,
“chosen”: false,
“cause”: “cost”
}
] /* range_scan_alternatives */,
“analyzing_roworder_intersect”: {
“usable”: false,
“cause”: “too_few_roworder_scans”
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */
} /* range_analysis */
}
] /* rows_estimation */
},
{
“considered_execution_plans”: [
{
“plan_prefix”: [
] /* plan_prefix */,
“table”: “employees”,
“best_access_path”: {
“considered_access_paths”: [
{
“rows_to_scan”: 120085,
“access_type”: “scan”,
“resulting_rows”: 120085,
“cost”: 24370,
“chosen”: true,
“use_tmp_table”: true
}
] /* considered_access_paths */
} /* best_access_path */,
“condition_filtering_pct”: 100,
“rows_for_plan”: 120085,
“cost_for_plan”: 24370,
“sort_cost”: 120085,
“new_cost_for_plan”: 144455,
“chosen”: true
}
] /* considered_execution_plans */
},
{
“attaching_conditions_to_tables”: {
“original_condition”: “(employees.name > ‘a’)”,
“attached_conditions_computation”: [
] /* attached_conditions_computation */,
“attached_conditions_summary”: [
{
“table”: “employees”,
“attached”: “(employees.name > ‘a’)”
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
“clause_processing”: {
“clause”: “ORDER BY”,
“original_clause”: “employees.position”,
“items”: [
{
“item”: “employees.position”
}
] /* items */,
“resulting_clause_is_simple”: true,
“resulting_clause”: “employees.position”
} /* clause_processing */
},
{
“reconsidering_access_paths_for_index_ordering”: {
“clause”: “ORDER BY”,
“index_order_summary”: {
“table”: “employees”,
“index_provides_order”: false,
“order_direction”: “undefined”,
“index”: “unknown”,
“plan_changed”: false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
“refine_plan”: [
{
“table”: “employees”
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
“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”: 120003, --文件排序信息
“examined_rows”: 120003, --预计扫描行数
“number_of_tmp_files”: 34, --参与排序的行
“sort_buffer_size”: 262056, --使用临时文件的个数，如果这个值为 9 代表全部使用的 sort_buffer 内存排序，否则使用的磁盘文件排序
“sort_mode”: “<sort_key, packed_additional_fields>” --排序方式，这里采用的是单路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
set max_length_for_sort_data = 2; --employees表所有字段长度总和肯定大于2字节
select * from employees where name = ‘SAN’ order by position;
select * from information_schema.OPTIMIZER_TRACE;
trace排序部分结果：
{
“steps”: [
{
“join_preparation”: {
“select#”: 1,
“steps”: [
{
“expanded_query”: “/* select#1 */ select employees.id AS id,employees.name AS name,employees.age AS age,employees.position AS position,employees.hire_time AS hire_time from employees where (employees.name = ‘SAN’) order by employees.position”
}
] /* steps */
} /* join_preparation */
},
{
“join_optimization”: {
“select#”: 1,
“steps”: [
{
“condition_processing”: {
“condition”: “WHERE”,
“original_condition”: “(employees.name = ‘SAN’)”,
“steps”: [
{
“transformation”: “equality_propagation”,
“resulting_condition”: “(employees.name = ‘SAN’)”
},
{
“transformation”: “constant_propagation”,
“resulting_condition”: “(employees.name = ‘SAN’)”
},
{
“transformation”: “trivial_condition_removal”,
“resulting_condition”: “(employees.name = ‘SAN’)”
}
] /* steps */
} /* condition_processing */
},
{
“substitute_generated_columns”: {
} /* substitute_generated_columns */
},
{
“table_dependencies”: [
{
“table”: “employees”,
“row_may_be_null”: false,
“map_bit”: 0,
“depends_on_map_bits”: [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
“ref_optimizer_key_uses”: [
{
“table”: “employees”,
“field”: “name”,
“equals”: “‘SAN’”,
“null_rejecting”: false
}
] /* ref_optimizer_key_uses */
},
{
“rows_estimation”: [
{
“table”: “employees”,
“range_analysis”: {
“table_scan”: {
“rows”: 120085,
“cost”: 24372
} /* table_scan */,
“potential_range_indexes”: [
{
“index”: “PRIMARY”,
“usable”: false,
“cause”: “not_applicable”
},
{
“index”: “idx_name_age_position”,
“usable”: true,
“key_parts”: [
“name”,
“age”,
“position”,
“id”
] /* key_parts */
}
] /* potential_range_indexes */,
“setup_range_conditions”: [
] /* setup_range_conditions */,
“group_index_range”: {
“chosen”: false,
“cause”: “not_group_by_or_distinct”
} /* group_index_range */,
“analyzing_range_alternatives”: {
“range_scan_alternatives”: [
{
“index”: “idx_name_age_position”,
“ranges”: [
“SAN <= name <= SAN”
] /* ranges */,
“index_dives_for_eq_ranges”: true,
“rowid_ordered”: false,
“using_mrr”: false,
“index_only”: false,
“rows”: 1,
“cost”: 2.21,
“chosen”: true
}
] /* range_scan_alternatives */,
“analyzing_roworder_intersect”: {
“usable”: false,
“cause”: “too_few_roworder_scans”
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
“chosen_range_access_summary”: {
“range_access_plan”: {
“type”: “range_scan”,
“index”: “idx_name_age_position”,
“rows”: 1,
“ranges”: [
“SAN <= name <= SAN”
] /* ranges */
} /* range_access_plan */,
“rows_for_plan”: 1,
“cost_for_plan”: 2.21,
“chosen”: true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
“considered_execution_plans”: [
{
“plan_prefix”: [
] /* plan_prefix */,
“table”: “employees”,
“best_access_path”: {
“considered_access_paths”: [
{
“access_type”: “ref”,
“index”: “idx_name_age_position”,
“rows”: 1,
“cost”: 1.2,
“chosen”: true
},
{
“access_type”: “range”,
“range_details”: {
“used_index”: “idx_name_age_position”
} /* range_details */,
“chosen”: false,
“cause”: “heuristic_index_cheaper”
}
] /* considered_access_paths */
} /* best_access_path */,
“condition_filtering_pct”: 100,
“rows_for_plan”: 1,
“cost_for_plan”: 1.2,
“chosen”: true
}
] /* considered_execution_plans */
},
{
“attaching_conditions_to_tables”: {
“original_condition”: “(employees.name = ‘SAN’)”,
“attached_conditions_computation”: [
] /* attached_conditions_computation */,
“attached_conditions_summary”: [
{
“table”: “employees”,
“attached”: null
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
“clause_processing”: {
“clause”: “ORDER BY”,
“original_clause”: “employees.position”,
“items”: [
{
“item”: “employees.position”
}
] /* items */,
“resulting_clause_is_simple”: true,
“resulting_clause”: “employees.position”
} /* clause_processing */
},
{
“added_back_ref_condition”: “((employees.name <=> ‘SAN’))”
},
{
“reconsidering_access_paths_for_index_ordering”: {
“clause”: “ORDER BY”,
“index_order_summary”: {
“table”: “employees”,
“index_provides_order”: false,
“order_direction”: “undefined”,
“index”: “idx_name_age_position”,
“plan_changed”: false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
“refine_plan”: [
{
“table”: “employees”,
“pushed_index_condition”: “(employees.name <=> ‘SAN’)”,
“table_condition_attached”: null
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
“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”: 0,
“examined_rows”: 0,
“number_of_tmp_files”: 0,
“sort_buffer_size”: 262136,
“sort_mode”: “<sort_key, rowid>” --排序方式，双路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
set session optimizer_trace=“enabled=off”; --关闭trace
复制代码
我们先看单路排序的详细过程：
-
从索引name找到第一个满足 name = ‘SAN’ 条件的主键 id
-
根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
-
从索引name找到下一个满足 name = ‘SAN’ 条件的主键 id
-
重复步骤 2、3 直到不满足 name = ‘SAN’
-
对 sort_buffer 中的数据按照字段 position 进行排序
-
返回结果给客户端
自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。
深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！
因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！
由于文件比较大，这里只是将部分目录大纲截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新
如果你觉得这些内容对你有帮助，可以添加V获取：vip1024b （备注Java）
最后
如果觉得本文对你有帮助的话，不妨给我点个赞，关注一下吧！
一个人可以走的很快，但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎扫码加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
字段的值，存入 sort_buffer 中**
-
从索引name找到下一个满足 name = ‘SAN’ 条件的主键 id
-
重复步骤 2、3 直到不满足 name = ‘SAN’
-
对 sort_buffer 中的数据按照字段 position 进行排序
-
返回结果给客户端
自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。
深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！
因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
[外链图片转存中…(img-GWJoGJaf-1712657748384)]
[外链图片转存中…(img-i7sNta7s-1712657748385)]
[外链图片转存中…(img-C3Y0XMyZ-1712657748385)]
[外链图片转存中…(img-ak69N3aF-1712657748385)]
[外链图片转存中…(img-nRajyPOJ-1712657748386)]
[外链图片转存中…(img-dMGsZ37r-1712657748386)]
既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！
由于文件比较大，这里只是将部分目录大纲截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新
如果你觉得这些内容对你有帮助，可以添加V获取：vip1024b （备注Java）
[外链图片转存中…(img-x4ggQFXc-1712657748386)]
最后
如果觉得本文对你有帮助的话，不妨给我点个赞，关注一下吧！
[外链图片转存中…(img-SpolxDod-1712657748387)]
[外链图片转存中…(img-XSwsO6MD-1712657748387)]
一个人可以走的很快，但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎扫码加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
[外链图片转存中…(img-IVnGq3Bf-1712657748387)]
永洪科技，致力于打造全球领先的数据技术厂商，具备从数据应用方案咨询、BI、AIGC智能分析、数字孪生、数据资产、数据治理、数据实施的端到端大数据价值服务能力。
更多推荐
21
0- 0
已为社区贡献1条内容
所有评论(0)