个人理解在使用explain查看sql后的执行语句,查看索引是否生效,优化索引已经很方便了,今天发现新大陆,总结一下,下边是一个小的demo
建表语句:
create TABLE t (id int PRIMARY KEY, val int ,name varchar(20));
insert into t VALUES(1,1,'a'),(2,1,'b'),(3,1,'c'),(4,1,'d'),(5,1,'e'),(6,1,'f'),(7,1,'g'),(8,1,'w'),(9,1,'q'),(10,1,'z');
开启optimizer_trace
SET optimizer_trace="enabled=on";
给val加索引
alter table t add index idx_val('val');
执行查询语句,查看结果
EXPLAIN select name from t where val=1;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
摘出来TRACE这个字段
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `t`.`name` AS `name` from `t` where (`t`.`val` = 1)"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`t`.`val` = 1)",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "multiple equal(1, `t`.`val`)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal(1, `t`.`val`)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(1, `t`.`val`)"
}
]
}
},
{
"table_dependencies": [
{
"table": "`t`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`t`",
"field": "val",
"equals": "1",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`t`",
"range_analysis": {
"table_scan": {
"rows": 10,
"cost": 5.1
},
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_val",
"usable": true,
"key_parts": [
"val",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_val",
"ranges": [
"1 <= val <= 1"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 10,
"cost": 13.01,
"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": "idx_val",
"rows": 10,
"cost": 4,
"chosen": true
},
{
"access_type": "scan",
"rows": 8,
"cost": 3,
"chosen": true
}
]
},
"cost_for_plan": 3,
"rows_for_plan": 8,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`t`.`val` = 1)",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`t`",
"attached": "(`t`.`val` = 1)"
}
]
}
},
{
"refine_plan": [
{
"table": "`t`",
"access_type": "table_scan"
}
]
}
]
}
},
{
"join_explain": {
"select#": 1,
"steps": [
]
}
}
]
}
{
"join_explain": {
"select#": 1,
"steps": [
]
}
}
]
}
通过explain查出来是all类型的 key是空的,说明没有用到索引,那么就束手无策了吗,不是,看看为啥,这个就和mysql执行sql的时候有关系,分为几步,先建立连接,然后mysql会优化sql,然后是执行sql,到存储引擎,经过以上的步骤,在优化阶段,会选择合适的优化索引来执行这个sql,上边展示的就是
我们看rows_estimation 字段里边的table_scan全表扫描10行花费5.1,优化后的 analyzing_range_alternatives字段range_scan_alternatives扫描行10花费13.01这就是mysql会自己优化选择用哪一个查询索引,是否是要全表查还是使用val索引查,我们看那个查询语句,val使用了索引但是还是all类型的,这种就出现了,他么有使用覆盖索引,根据mysql innodb存储引擎上特性是索引和数据是绑定到一起的,由于name和val并没有建立聚集索引,所以会导致查询的时候先查出来id主键,然后通过id主键去查所对应的行,找到数据,其实是多增加了操作,会导致查询了两次,io成本就会增加。
所以在回表数据量比较大 时候,mysql会对回表查询代价预估代价太大而导致不使用索引的原因。
一般来说,当查询表中数据量大于五分之一的记录,且不使用覆盖索引的情况下,会导致回表操作代价太大导致使用了全表扫描,且随着单行记录的字节增加而略微增大。
可以通过range_analysis的相关数据对where从句中使用多个索引列,如何选择执行是使用索引的情况进行分析。