【MySQL】MySQL索引优化——从原理分析到实践对比（TRACE分析+ORDER BY & GROUP BY 优化）
使用TRACE分析MySQL优化
某些情况下，MySQL是否走索引是不确定的=[,,_,,]:3，那、我就想确定。。。咋办？
首先，在FROM 表名后加上FORCE INDEX(索引名称)可以强制MySQL走索引
举个🌰
SELECT name FROM app_user FORCE INDEX(index_age) WHERE age > 9;
当然本着尊重以及信赖MySQL的原则，还是不要强迫他(˶‾᷄ ⁻̫ ‾᷅˵)。。毕竟MySQL有自己的一套很靠谱的优化方式，针一条SQL语句，我们可以通过TRACE来查看他的优化结果
开启TRACE
使用下面👇的语句开启TRACE（开启会影响性能，因此默认关闭，只会在做分析的时候开启）
set session optimizer_trace="enabled=on",end_markers_in_json=on
在执行语句下面加一行，举个🌰
SELECT name FROM app_user WHERE age > 9;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
TRACE 结果集
如下是一个trace结果集的示例（完整版太长，部分省略）
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `app_user`.`name` AS `name` from `app_user` where (`app_user`.`age` > 9)"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{……},
{
"substitute_generated_columns": {} /* substitute_generated_columns */
},
{
"table_dependencies": [……] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`app_user`",
"range_analysis": {
"table_scan": {
"rows": 992599,
"cost": 102998
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "id_app_user_name",
"usable": false,
"cause": "not_applicable"
},
{
"index": "index_age",
"usable": true,
"key_parts": [
"age",
"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 */,
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "index_age",
"usable": false,
"cause": "query_references_nonkey_column"
}
] /* potential_skip_scan_indexes */
} /* skip_scan_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [……]
/* 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": "`app_user`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 992599,
"access_type": "scan",
"resulting_rows": 992599,
"cost": 102996,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 992599,
"cost_for_plan": 102996,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {……} /* attaching_conditions_to_tables */
},
{
"finalizing_table_conditions": [……] /* finalizing_table_conditions */
},
{
"refine_plan": [
{
"table": "`app_user`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
主要字段含义
-
"steps"：步骤
-
"join_preparation"
- 第一阶段：准备阶段，会进行SQL格式化
-
"join_optimization"
-
第二阶段：优化阶段
-
"condition_processing" 条件处理
- 联合索引的顺序优化就是在这一步
-
"table_dependencies" 表依赖详情
-
"rows_estimation" 预估表的访问成本（选择依据）
-
"table_scan" 全表扫描情况
-
"rows" 扫描行数
-
"cost" 查询成本
- 主要依据，除了扫描行数还会考虑回表等别的消耗，无单位，MySQL一般会选小的）
-
-
"potential_range_indexes" 查询可能使用到的索引
-
"index"
- "PRIMARY" 主键索引
- 其他的表示辅助索引
-
-
"analyzing_range_alternatives" 分析各个索引的成本
- "rowid_ordered" 使用该索引获取的记录是否按照主键排序
- "index_only" 是否使用覆盖索引
- "rows" 扫描行数
- "cost" 索引使用成本
- "chosen" 是否确认选择该索引
-
-
"considered_execution_plans"
-
"best_access_path" 最优访问路径
-
"considered_access_paths" 最终选择的访问路径
-
"rows_to_scan" 扫描行数
-
"access_type" 访问类型
-
"range_details"
- "used_index" 使用索引
- "resulting_rows" 扫描行数
- "cost" 查询成本
- "chosen" 确定选择
-
-
-
-
-
ORDER BY & GROUP BY 优化
Extra中的值表示了ORDER BY是否走索引，Extra中的值是Using index condition表示ORDER BY走索引，Extra中的值是Using filesort表示ORDER BY未走索引；ORDER by默认升序，如果ORDER by使用降序（与索引的排序方式不同），于是会产生Using filesort（MySQL8.0以上的版本有降序索引可以支持这种查询优化）
GROUP BY和ORDER by很类似，其实质是先排序后分组
Using filesort 原理
排序方式
- 单路排序：一次性取出满足条件的所有字段，然后在sort buffer中进行排序
- 双路排序（回表排序模式）：首先根据相应的条件取出相应字段的排序字段和ID，然后在sort buffer中进行排序，排序完需要再次取回其他需要的字段
如果使用了Using filesort，那么使用上面介绍的TRACE工具🔧就会有相应的信息，即sort_mode信息
如果是单路排序sort_mode字段的信息为<sort_key,additional_fields>或者<sort_key,packed_additional_fields>；如果是双路排序sort_mode字段的信息为<sort_key,rowid>
那么，如何MySQL是如何判断是否使用了Using filesort的？
自问自答：通过比较系统变量max_length_for_sort_data（默认1024字节）的大小来判断使用哪种排序
- 字段总长度小于
max_length_for_sort_data，使用单路 - 字段总长度大于
max_length_for_sort_data，使用双路
优化方式
-
MySQL支持两种方式的排序：filesort（效率低） 和index（效率高） ，
Using index是指MySQL扫描索引本身就能完成排序 -
ORDER by满足两种情况会使用Using indexORDER by使用索引最左前列- 使用
WHERE子句和ORDER by子句条件列组合满足索引最左前列
-
尽量在索引列上完成排序，遵循索引建立（索引创建的顺序） 时的最左前缀法则，如果
ORDER by的条件不在索引列上，就会产生Using filesort -
尽量使用覆盖索引
-
遵循索引创建的最左前缀法则，对于
GROUP BY的优化如果不需要排序的可以加上ORDER by null禁止排序，注意WHERE高于HAVING，能写在WHERE中就不要使用HAVING
转载自:https://juejin.cn/post/7291936784782917695