explain是MySQL中的一个关键字,可以用来模拟优化器执行SQL语句,分析你的SQL的性能瓶颈。
怎么使用
要使用它,只需将此关键字添加到 SQL 语句前面即可,例如:
explain select * from users where name = 'foo'
explain返回的结果有很多字段,都是什么意思?
下面是users表,有一个联合索引:name_age_school。
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '',
`age` int(11) NOT NULL DEFAULT 0,
`school` varchar(20) NOT NULL DEFAULT '',
`birthday` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_name_age_school` (`name`,`age`,`school`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1;
执行sql:
explain select * from users where name = 'foo';
执行计划的结果:
复杂查询
接下来,看一个稍微复杂一点的例子。
explain
select * from users u1
join (select id from users order by name limit 10000, 10) u2
ON u1.id = u2.id;
该语句的含义是:查询按name字段排序的第10000到10010行的记录。
现在,解释一下结果中返回的这些列都是什么意思:
1. id
select 查询的序列号,它是一组数字,表示 select 子句或操作表在查询中执行的顺序。
它有三种结果
- id相同:表示执行顺序是从上到下。
- id不同:如果是子查询,ID的序数会递增,ID的值越大,优先级越高,最先执行
- id有相同也有不相同:如果id相同,则可以认为是一组,按照从上到下的顺序执行。 所有组中,id值越高,优先级越高,越先执行。例如,使用 union 的查询。
2. select_type
查询类型,有 6 个值。
- SIMPLE:没有子查询或联合查询的简单查询。
- PRIMARY:如果查询包含任何复杂的子部分,则最外面的查询将被标记为主查询。
- SUBQUERY: select 或 where语句中包含子查询。
- DERIVED:FROM 列表中包含的子查询标记为 DERIVED,MySQL 这些子查询以递归方式执行,将结果放置在临时表中。
- UNION:如果第二个select出现在union之后,则被标记为UNION。
- UNION RESULT:选择从UNION表中获取结果
3. type
- system:这是最基本和最简单的类型。它通常涉及单个行,并用于访问表中的单个行的查询。
- const:当查询可以基于常量主键查找而仅能检索一行时使用此类型。
- eq_ref:此类型用于执行使用主键或唯一索引进行唯一索引查找的查询。
- ref:此类型用于查询使用非唯一索引来根据单个值检索行的情况。
- range:此类型表示查询正在使用索引上的范围搜索,以检索在指定范围内的行。
- index:此类型类似于range,但用于查询扫描整个索引。当需要检查索引的较大部分时,它可能不如range高效。
- all:此类型用于查询执行完整表扫描,即检查表中的所有行。
- fulltext:此类型专用于全文搜索,用于查询使用全文索引的情况。
- unique_subquery:当查询使用具有UNIQUE索引的子查询时使用此类型。
- index_subquery:当查询使用带有索引的子查询时使用此类型。
- index_merge:表示使用索引合并的优化方法。
4. table
查询哪个表
5. possible_keys
显示可能应用于此表的一个或多个索引。
如果查询涉及的字段存在索引,则会列出该索引,但可能不会使用该索引
6. key
实际使用的索引,如果为NULL,则没有使用索引。 如果查询中使用了覆盖索引(查询的列恰好是索引),则该索引仅出现在key列表中。
7. key_len
表示索引中使用的字节数,计算查询中使用的索引的长度。 在不损失准确性的情况下,长度越短越好。
显示的 key_len 值是索引字段的最大可能长度,而不是实际使用的长度。 key_len是根据定义计算出来的,并不是从表中检索出来的。
8. ref
显示使用了索引的哪一列,以及使用哪些列或常量在索引列上查找值。
9.rows
根据表统计信息和索引选择,粗略估计查找所需记录所需读取的行数。
10. partitions
匹配分区
11. filtered
查询的表行数占表的百分比。
12. Extra
包含重要但不适合在其他列中显示的额外信息。
- Using filesort:请注意,MySQL 使用外部索引对数据进行排序,而不是按照表内索引的顺序读取数据。
- Using temporary:临时表用于保存中间结果,MySQL在对结果进行排序时使用临时表,常见于排序 order by 和分组查询 group by 。
- Using index:表示对应的select操作使用Covering Index,避免访问表的数据行,效率较高。如果还出现using where,则表明该索引用于执行索引键值查找。如果没有同时使用where,则使用索引来读取数据而不是执行查找。
- Using where:使用where 条件。
- Using join buffer:使用连接缓存。
- impossible where:where子句的值始终为假,不能用于获取任何元组。
- unique:一旦MySQL找到与该行共同匹配的行,就不再搜索。
- Select Table Optimizedaway:选择操作已经优化到无法再优化的程度。
怎么trace?
有时候我们发现explain的结果和我们期望的不一样,因为MySQL优化器做了成本计算,MySQL觉得不使用索引更快。
例如以下例子:
explain select * from users where name > 'foo';
可以看到执行计划中没有使用任何索引。
关于执行成本,我们可以通过trace工具查看。
-- open trace
set session optimizer_trace="enabled=on",end_markers_in_json=on;
-- execute SQL
explain select * from users where name > 'foo';
-- get result
SELECT * FROM information_schema.OPTIMIZER_TRACE;
结果是:
{
"steps": [
{
"join_preparation": { -- step1: Format SQL
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `users`.`id` AS `id`,`users`.`name` AS `name`,`users`.`age` AS `age`,`users`.`school` AS `school`,`users`.`birthday` AS `birthday` from `users` where (`users`.`name` > 'foo')"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { -- step2: Optimize SQL
"select#": 1,
"steps": [
{
"condition_processing": { -- step3: Use where
"condition": "WHERE",
"original_condition": "(`users`.`name` > 'foo')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`users`.`name` > 'foo')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`users`.`name` > 'foo')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`users`.`name` > 'foo')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [
{
"table": "`users`",
"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": "`users`",
"range_analysis": {
"table_scan": {
"rows": 1,
"cost": 3.3
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_age_school",
"usable": true,
"key_parts": [
"name",
"age",
"school",
"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_school",
"ranges": [
"foo < name"
] /* 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_school",
"rows": 1,
"ranges": [
"foo < name"
] /* 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": "`users`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 1,
"access_type": "range",
"range_details": {
"used_index": "idx_name_age_school"
} /* range_details */,
"resulting_rows": 1,
"cost": 2.41,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 2.41,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`users`.`name` > 'foo')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`users`",
"attached": "(`users`.`name` > 'foo')"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
{
"table": "`users`",
"pushed_index_condition": "(`users`.`name` > 'foo')",
"table_condition_attached": null
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_explain": {
"select#": 1,
"steps": [
] /* steps */
} /* join_explain */
}
] /* steps */
}