执行时间 0.1 秒
SELECT * FROM employees WHERE name > ‘SAN ZHANG’；
执行时间 0.15 秒
SELECT * FROM employees force index(idx_name_age_position) WHERE name > ‘SAN ZHANG’;；
复制代码
3、覆盖索引优化
EXPLAIN SELECT name,age,position FROM employees WHERE name > ‘SAN ZHANG’ AND age = 22 AND position =‘dev’;
复制代码
4、in 和 or 在数据量比较大的情况下下会走索引，在表数据记录不多的情况下会选择全表扫描
EXPLAIN SELECT name,age,position FROM employees WHERE name in (‘SAN ZHANG’, ‘SI Li’, ‘MAZI WQNAG’, ‘LIU ZHAO’) AND age = 22 AND position =‘dev’;
复制代码
EXPLAIN SELECT name,age,position FROM employees WHERE
(name = ‘SAN ZHANG’ or name = ‘SI Li’ or name = ‘MAZI WQNAG’ or name = ‘LIU ZHAO’)
AND age = 22 AND position =‘dev’;
复制代码
创建一张 employees_temp 表里面就保留少量几条记录
CREATE TABLE employees_temp (
id INT(10) NOT NULL AUTO_INCREMENT,
name VARCHAR(24) NOT NULL DEFAULT ‘’ COMMENT ‘姓名’ ,
age INT(10) NOT NULL DEFAULT ‘0’ COMMENT ‘年龄’,
position VARCHAR(20) NOT NULL DEFAULT ‘’ COMMENT ‘职位’ ,
hire_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘入职时间’,
PRIMARY KEY (id) USING BTREE,
INDEX idx_name_age_position (name, age, position) USING BTREE
)
COMMENT=‘员工记录表’;
insert into employees_temp(name, age, position) values (‘SAN ZHANG’, 23, ‘dev’);
insert into employees_temp(name, age, position) values (‘SI Li’, 23, ‘dev’);
insert into employees_temp(name, age, position) values (‘LIU ZHAO’, 26, ‘dev’);
insert into employees_temp(name, age, position) values (‘LING AI’, 38, ‘manager’);
复制代码
EXPLAIN SELECT * FROM employees_temp WHERE name in (‘SAN ZHANG’, ‘SI Li’, ‘MAZI WQNAG’, ‘LIU ZHAO’) AND age = 22 AND position =‘dev’;
EXPLAIN SELECT * FROM employees_temp WHERE (name = ‘SAN ZHANG’ or name = ‘SI Li’ or name = ‘MAZI WQNAG’ or name = ‘LIU ZHAO’) AND age = 22 AND position =‘dev’;
5、like ‘SAN%’ 一般都会走索引
EXPLAIN SELECT name,age,position FROM employees WHERE name like ‘SAN%’ AND age = 22 AND position =‘dev’;
复制代码
EXPLAIN SELECT * FROM employees WHERE name like ‘SAN%’ AND age = 22 AND position =‘dev’;
复制代码
选择合适的索引
=======
索引分析案例
EXPLAIN select * from employees where name > ‘a’;
复制代码
如果用name索引需要遍历name字段联合索引树，然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描还高，可以用覆盖索引优化，这样只需要遍历name字段的联合索引树就能拿到所有结果，如下：
EXPLAIN select name,age,position from employees where name > ‘a’ ;
复制代码
EXPLAIN select * from employees where name > ‘zzz’ ;
复制代码
Trace 工具使用
对于上面这两种 name>‘a’ 和 name>‘zzz’ 的执行结果，mysql最终是否选择走索引或者一张表涉及多个索引，mysql最终如何选择索引，我们可以用trace工具来一查究竟，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭 trace工具用法：
set session optimizer_trace=“enabled=on”,end_markers_in_json=on; --开启trace
select * from employees where name > ‘a’ order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
查看trace字段：
{
“steps”: [
{
“join_preparation”: { --第一阶段：SQL准备阶段，格式化sql
“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”: { --第二阶段：SQL 优化阶段
“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”, --访问类型：为 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,
“sort_mode”: “<sort_key, packed_additional_fields>”
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
结论：全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描
select * from employees where name > ‘zzz’ order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
查看trace字段可知索引扫描的成本低于全表扫描，所以mysql最终选择索引扫描
set session optimizer_trace=“enabled=off”; --关闭trace
复制代码
常见sql深入优化
=============
Order by 与 Group by 优化
Case 1：
explain select * from employees where name = ‘SAN’ and position = ‘dev’ order by age desc;
复制代码
分析：利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有using filesort
Case 2：
explain select * from employees where name = ‘SAN’ order by position;
复制代码
分析： 从explain的执行结果来看：key_len=74，查询使用了name索引，由于用了position进行排序，跳过了age，出现了Using filesort。
Case 3：
explain select * from employees where name = ‘SAN’ order by age, position;
复制代码
分析： 查找只用到索引name，age和position用于排序，无Using filesort。
Case 4：
explain select * from employees where name = ‘SAN’ order by position, age;
复制代码
分析： 和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”: [
最后
找小编（vip1024c）领取
loyees.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”: [
最后
[外链图片转存中…(img-6XQOD7kF-1721718735075)]
找小编（vip1024c）领取
393
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
