mysql一次只能执行一个索引_mysql--数据库查询记录时是否每次只能使用一个索引...

最新推荐文章于 2024-03-25 15:18:27 发布

共同努力学习学习

最新推荐文章于 2024-03-25 15:18:27 发布

阅读量832

点赞数

文章标签： mysql一次只能执行一个索引

本文链接：https://blog.csdn.net/weixin_28267011/article/details/113600587

版权

起因，今天有同学问一个sql的问题，sql的where语句中的查询条件有两个列，每个列都建了单列索引，但通过explain查询计划看到只使用了一个索引，不知道为什么。

我在自己机器上试了下，使用的mysql官方提供的sakila库，结果如下：

mysql> explain select customer_id, rental_id from payment where customer_id=500 and rental_id=9290;

+----+-------------+---------+------+--------------------------------------+-------------------+---------+-------+------+-------------+

+----+-------------+---------+------+--------------------------------------+-------------------+---------+-------+------+-------------+

+----+-------------+---------+------+--------------------------------------+-------------------+---------+-------+------+-------------+

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;

+----+-------------+---------+-------------+--------------------------------------+--------------------------------------+---------+------+------+----------------------------------------------------------------+

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": [

{

}

] /* 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)

参考与感谢：

共同努力学习学习

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql一次只能执行一个索引_mysql--数据库查询记录时是否每次只能使用一个索引...

起因，今天有同学问一个sql的问题，sql的where语句中的查询条件有两个列，每个列都建了单列索引，但通过explain查询计划看到只使用了一个索引，不知道为什么。我在自己机器上试了下，使用的mysql官方提供的sakila库，结果如下：mysql> explain select customer_id, rental_id from payment where customer_id=5...
复制链接

扫一扫