mysql--数据库查询记录时是否每次只能使用一个索引
2020-10-12 本文已影响0人
牧码人zhouz
起因，今天有同学问一个sql的问题，sql的where语句中的查询条件有两个列，每个列都建了单列索引，但通过explain查询计划看到只使用了一个索引，不知道为什么。
我在自己机器上试了下，使用的mysql官方提供的sakila库，结果如下：
mysql> explain select customer_id, rental_id from payment where customer_id=500 and rental_id=9290;
+----+-------------+---------+------+--------------------------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+--------------------------------------+-------------------+---------+-------+------+-------------+
| 1 | SIMPLE | payment | ref | idx_fk_customer_id,fk_payment_rental | fk_payment_rental | 5 | const | 1 | Using where |
+----+-------------+---------+------+--------------------------------------+-------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
首先思考下，为什么两列都有索引，最终却选择了rental_id索引列，通过trace查看：
mysql> select * from information_schema.optimizer_trace \G;
*************************** 1. row ***************************
QUERY: explain select customer_id, rental_id from payment where customer_id=500 and rental_id=9290
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `payment`.`customer_id` AS `customer_id`,`payment`.`rental_id` AS `rental_id` from `payment` where ((`payment`.`customer_id` = 500) and (`payment`.`rental_id` = 9290))"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`payment`.`customer_id` = 500) and (`payment`.`rental_id` = 9290))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal(500, `payment`.`customer_id`) and multiple equal(9290, `payment`.`rental_id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal(500, `payment`.`customer_id`) and multiple equal(9290, `payment`.`rental_id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(multiple equal(500, `payment`.`customer_id`) and multiple equal(9290, `payment`.`rental_id`))"
}
] /* steps */
} /* condition_processing */
},
{
"table_dependencies": [
{
"table": "`payment`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
{
"table": "`payment`",
"field": "customer_id",
"equals": "500",
"null_rejecting": false
},
{
"table": "`payment`",
"field": "rental_id",
"equals": "9290",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`payment`",
"range_analysis": {
"table_scan": {
"rows": 16086,
"cost": 3316.3
} /* table_scan */,
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_fk_staff_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_fk_customer_id",
"usable": true,
"key_parts": [
"customer_id",
"payment_id"
] /* key_parts */
},
{
"index": "fk_payment_rental",
"usable": true,
"key_parts": [
"rental_id",
"payment_id"
] /* key_parts */
}
] /* potential_range_indices */,
"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_fk_customer_id",
"ranges": [
"500 <= customer_id <= 500"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 28,
"cost": 34.61,
"chosen": true
},
{
"index": "fk_payment_rental",
"ranges": [
"9290 <= rental_id <= 9290"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 1,
"cost": 2.21,
"chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"intersecting_indices": [
{
"index": "fk_payment_rental",
"index_scan_cost": 1,
"cumulated_index_scan_cost": 1,
"disk_sweep_cost": 0,
"cumulated_total_cost": 1,
"usable": true,
"matching_rows_now": 1,
"isect_covering_with_this_index": false,
"chosen": true
},
{
"index": "idx_fk_customer_id",
"index_scan_cost": 1.0132,
"cumulated_index_scan_cost": 2.0132,
"disk_sweep_cost": 0,
"cumulated_total_cost": 2.0132,
"usable": true,
"matching_rows_now": 0.0017,
"isect_covering_with_this_index": true,
"chosen": false,
"cause": "does_not_reduce_cost"
}
] /* intersecting_indices */,
"clustered_pk": {
"clustered_pk_added_to_intersect": false,
"cause": "no_clustered_pk_index"
} /* clustered_pk */,
"chosen": false,
"cause": "too_few_indexes_to_merge"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "fk_payment_rental",
"rows": 1,
"ranges": [
"9290 <= rental_id <= 9290"
] /* 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": "`payment`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_fk_customer_id",
"rows": 28,
"cost": 33.6,
"chosen": true
},
{
"access_type": "ref",
"index": "fk_payment_rental",
"rows": 1,
"cost": 1.2,
"chosen": true
},
{
"access_type": "range",
"cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 1.2,
"rows_for_plan": 1,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`payment`.`rental_id` = 9290) and (`payment`.`customer_id` = 500))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`payment`",
"attached": "(`payment`.`customer_id` = 500)"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
{
"table": "`payment`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_explain": {
"select#": 1,
"steps": [
] /* steps */
} /* join_explain */
}
] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
从considered_execution_plans中可以看到，选择customer_id的cost为33.6，而rental的cost为1.2，故选择了rental作为实际使用的索引。
那为什么存在两个索引的情况下，只选择了一列索引呢，以下是引用自其它文章的分析：
与其说是“数据库查询只能用到一个索引”，倒不是说是 和全表扫描/只使用一个索引的速度比起来，去分析两个索引二叉树更加耗费时间，所以绝大多数情况下数据库都是是用一个索引。
如这条语句：
select count(1) from table1 where column1 = 1 and column2 = 'foo' and column3 = 'bar'
我们来想象一下当数据库有N个索引并且查询中分别都要用上他们的情况：
查询优化器（用大白话说就是生成执行计划的那个东西）需要进行N次主二叉树查找[这里主二叉树的意思是最外层的索引节点]，此处的查找流程大概如下：
查出第一条column1主二叉树等于1的值，然后去第二条column2主二叉树查出foo的值并且当前行的coumn1必须等于1，最后去column主二叉树查找bar的值并且column1必须等于1和column2必须等于foo。
如果这样的流程被查询优化器执行一遍，就算不死也半条命了，查询优化器可等不及把以上计划都执行一遍，贪婪算法（最近邻居算法）可不允许这种情况的发生，所以当遇到以下语句的时候，数据库只要用到第一个筛选列的索引（column1），就会直接去进行表扫描了。
select count(1) from table1 where column1 = 1 and column2 = 'foo' and column3 = 'bar'
所以与其说是数据库只支持一条查询语句只使用一个索引，倒不如说N条独立索引同时在一条语句使用的消耗比只使用一个索引还要慢。
所以如上条的情况，最佳推荐是使用index(column1,column2,column3） 这种联合索引，此联合索引可以把b+tree结构的优势发挥得淋漓尽致：
一条主二叉树（column=1），查询到column=1节点后基于当前节点进行二级二叉树column2=foo的查询，在二级二叉树查询到column2=foo后，去三级二叉树column3=bar查找。
总结起来就是一句话：mysql认为在查N条独立索引比查一个索引的消耗更大，效率更低，更慢。
那么什么情况下可以使用多个索引呢，答案是当使用索引合并时，会使用多个索引列，以下为示例：
mysql> explain select customer_id, rental_id from payment where customer_id=500 or rental_id=9290;
+----+-------------+---------+-------------+--------------------------------------+--------------------------------------+---------+------+------+----------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------------+--------------------------------------+--------------------------------------+---------+------+------+----------------------------------------------------------------+
| 1 | SIMPLE | payment | index_merge | idx_fk_customer_id,fk_payment_rental | idx_fk_customer_id,fk_payment_rental | 2,5 | NULL | 29 | Using union(idx_fk_customer_id,fk_payment_rental); Using where |
+----+-------------+---------+-------------+--------------------------------------+--------------------------------------+---------+------+------+----------------------------------------------------------------+
1 row in set (0.00 sec)
下面是用trace追踪到的细节：
mysql> select * from information_schema.optimizer_trace \G;
*************************** 1. row ***************************
QUERY: explain select customer_id, rental_id from payment where customer_id=500 or rental_id=9290
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `payment`.`customer_id` AS `customer_id`,`payment`.`rental_id` AS `rental_id` from `payment` where ((`payment`.`customer_id` = 500) or (`payment`.`rental_id` = 9290))"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`payment`.`customer_id` = 500) or (`payment`.`rental_id` = 9290))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal(500, `payment`.`customer_id`) or multiple equal(9290, `payment`.`rental_id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal(500, `payment`.`customer_id`) or multiple equal(9290, `payment`.`rental_id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(multiple equal(500, `payment`.`customer_id`) or multiple equal(9290, `payment`.`rental_id`))"
}
] /* steps */
} /* condition_processing */
},
{
"table_dependencies": [
{
"table": "`payment`",
"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": "`payment`",
"range_analysis": {
"table_scan": {
"rows": 16086,
"cost": 3316.3
} /* table_scan */,
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_fk_staff_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_fk_customer_id",
"usable": true,
"key_parts": [
"customer_id",
"payment_id"
] /* key_parts */
},
{
"index": "fk_payment_rental",
"usable": true,
"key_parts": [
"rental_id",
"payment_id"
] /* key_parts */
}
] /* potential_range_indices */,
"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": [
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"analyzing_index_merge": [
{
"indices_to_merge": [
{
"range_scan_alternatives": [
{
"index": "idx_fk_customer_id",
"ranges": [
"500 <= customer_id <= 500"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": true,
"rows": 28,
"cost": 6.6232,
"chosen": true
}
] /* range_scan_alternatives */,
"index_to_merge": "idx_fk_customer_id",
"cumulated_cost": 6.6232
},
{
"range_scan_alternatives": [
{
"index": "fk_payment_rental",
"ranges": [
"9290 <= rental_id <= 9290"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": true,
"rows": 1,
"cost": 2.21,
"chosen": true
}
] /* range_scan_alternatives */,
"index_to_merge": "fk_payment_rental",
"cumulated_cost": 8.8332
}
] /* indices_to_merge */,
"cost_of_reading_ranges": 8.8332,
"use_roworder_union": true,
"cause": "always_cheaper_than_not_roworder_retrieval",
"analyzing_roworder_scans": [
{
"type": "range_scan",
"index": "idx_fk_customer_id",
"rows": 28,
"ranges": [
"500 <= customer_id <= 500"
] /* ranges */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
},
{
"type": "range_scan",
"index": "fk_payment_rental",
"rows": 1,
"ranges": [
"9290 <= rental_id <= 9290"
] /* ranges */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
}
] /* analyzing_roworder_scans */,
"index_roworder_union_cost": 37.216,
"members": 2,
"chosen": true
}
] /* analyzing_index_merge */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "index_roworder_union",
"union_of": [
{
"type": "range_scan",
"index": "idx_fk_customer_id",
"rows": 28,
"ranges": [
"500 <= customer_id <= 500"
] /* ranges */
},
{
"type": "range_scan",
"index": "fk_payment_rental",
"rows": 1,
"ranges": [
"9290 <= rental_id <= 9290"
] /* ranges */
}
] /* union_of */
} /* range_access_plan */,
"rows_for_plan": 29,
"cost_for_plan": 37.216,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`payment`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "range",
"rows": 29,
"cost": 43.016,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 43.016,
"rows_for_plan": 29,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`payment`.`customer_id` = 500) or (`payment`.`rental_id` = 9290))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`payment`",
"attached": "((`payment`.`customer_id` = 500) or (`payment`.`rental_id` = 9290))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
{
"table": "`payment`",
"access_type": "range"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_explain": {
"select#": 1,
"steps": [
] /* steps */
} /* join_explain */
}
] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
参考与感谢：
