MySQL为什么"错误"选择代价更大的索引
2022/1/10 19:03:40
本文主要是介绍MySQL为什么"错误"选择代价更大的索引，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
1.问题描述
群友提出问题，表里有两个列c1、c2，分别为INT、VARCHAR类型，且分别创建了unique key。
SQL查询的条件是 WHERE c1 = ? AND c2 = ?，用EXPLAIN查看执行计划，发现优化器优先选择了VARCHAR类型的c2列索引。
他表示很不理解，难道不应该选择看起来代价更小的INT类型的c1列吗？
2.问题复现
创建测试表t1：
[root@yejr.run]> CREATE TABLE `t1` ( `c1` int NOT NULL AUTO_INCREMENT, `c2` int unsigned NOT NULL, `c3` varchar(20) NOT NULL, `c4` varchar(20) NOT NULL, PRIMARY KEY (`c1`), UNIQUE KEY `k3` (`c3`), UNIQUE KEY `k2` (`c2`) ) ENGINE=InnoDB;
利用 mysql_random_data_load 写入一万行数据：
mysql_random_data_load -h127.0.0.1 -uuser -p123456 wang t1 10000
查看执行计划：
mysql> EXPLAIN SELECT * FROM t1 WHERE c2 = 950391508 AND c3 = 'Patrick Price'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: const
possible_keys: k3,k2
key: k3
key_len: 82
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)
mysql>
可以看到优化器的确选择了 k3 索引，而非"预期"的 k2 索引，这是为什么呢？
3.问题分析
其实原因很简单粗暴：优化器认为这两个索引选择的代价都是一样的，只是优先选中排在前面的那个索引而已。
再建一个相同的表 t2，只不过把 k2、k3 的索引创建顺序对调下：
[root@yejr.run]> CREATE TABLE `t2` ( `c1` int NOT NULL AUTO_INCREMENT, `c2` int unsigned NOT NULL, `c3` varchar(20) NOT NULL, `c4` varchar(20) NOT NULL, PRIMARY KEY (`c1`), UNIQUE KEY `k2` (`c2`), UNIQUE KEY `k3` (`c3`) ) ENGINE=InnoDB;
再次利用利用 mysql_random_data_load 写入一万行数据：
mysql_random_data_load -h127.0.0.1 -uuser -p123456 wang t2 10000
再查看执行计划：
mysql> EXPLAIN SELECT * FROM t2 WHERE c2 = 1051998464 AND c3 = 'Tammy Bell'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: const
possible_keys: k2,k3
key: k2
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)
mysql>
我们利用 EXPLAIN ANALYZE 来查看下两次执行计划的代价对比：
-- 查看t1表执行计划代价 mysql> EXPLAIN ANALYZE SELECT * FROM t1 WHERE c2 = 950391508 AND c3 = 'Patrick Price'\G *************************** 1. row *************************** EXPLAIN: -> Rows fetched before execution (cost=0.00..0.00 rows=1) (actual time=0.002..0.003 rows=1 loops=1) 1 row in set (0.00 sec) mysql> -- 查看t2表执行计划代价 mysql> EXPLAIN ANALYZE SELECT * FROM t2 WHERE c2 = 1051998464 AND c3 = 'Tammy Bell'\G *************************** 1. row *************************** EXPLAIN: -> Rows fetched before execution (cost=0.00..0.00 rows=1) (actual time=0.002..0.003 rows=1 loops=1) 1 row in set (0.00 sec) mysql>
可以看到，很明显代价都是一样的。
再利用 OPTIMIZE_TRACE 查看执行计划，也能看到两个SQL的代价是一样的（重点查看rows_estimation部分）：
查看t1表执行计划代价
--开启OPTIMIZER_TRACE功能，并设置要展示的数据条目数：
set optimizer_trace="enabled=on",end_markers_in_json=on;
-- 查看t1表执行计划代价
mysql> SELECT * FROM t1 WHERE c2 = 950391508 AND c3 = 'Patrick Price'\G
*************************** 1. row ***************************
c1: 55
c2: 950391508
c3: Patrick Price
c4: Roger Harvey
1 row in set (0.00 sec)
mysql>
mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30 \G;
*************************** 1. row ***************************
QUERY: SELECT * FROM t1 WHERE c2 = 950391508 AND c3 = 'Patrick Price'
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2`,`t1`.`c3` AS `c3`,`t1`.`c4` AS `c4` from `t1` where ((`t1`.`c2` = 950391508) and (`t1`.`c3` = 'Patrick Price'))"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`t1`.`c2` = 950391508) and (`t1`.`c3` = 'Patrick Price'))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal(950391508, `t1`.`c2`) and multiple equal('Patrick Price', `t1`.`c3`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal(950391508, `t1`.`c2`) and multiple equal('Patrick Price', `t1`.`c3`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(multiple equal(950391508, `t1`.`c2`) and multiple equal('Patrick Price', `t1`.`c3`))"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [
{
"table": "`t1`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
{
"table": "`t1`",
"field": "c3",
"equals": "'Patrick Price'",
"null_rejecting": true
},
{
"table": "`t1`",
"field": "c2",
"equals": "950391508",
"null_rejecting": true
}
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`t1`",
"rows": 1,
"cost": 1,
"table_type": "const",
"empty": false
}
] /* rows_estimation */
},
{
"condition_on_constant_tables": "true",
"condition_value": true
},
{
"attaching_conditions_to_tables": {
"original_condition": "true",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
--分析完成，关闭OPTIMIZER_TRACE
SET optimizer_trace="enabled=off";
查看t2表执行计划代价
--开启OPTIMIZER_TRACE功能，并设置要展示的数据条目数：
set optimizer_trace="enabled=on",end_markers_in_json=on;
-- 查看t2表执行计划代价
mysql> SELECT * FROM t2 WHERE c2 = 1051998464 AND c3 = 'Tammy Bell'\G
*************************** 1. row ***************************
c1: 143
c2: 1051998464
c3: Tammy Bell
c4: Jane Perry
1 row in set (0.00 sec)
mysql>
mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30 \G;
*************************** 1. row ***************************
QUERY: SELECT * FROM t2 WHERE c2 = 1051998464 AND c3 = 'Tammy Bell'
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `t2`.`c1` AS `c1`,`t2`.`c2` AS `c2`,`t2`.`c3` AS `c3`,`t2`.`c4` AS `c4` from `t2` where ((`t2`.`c2` = 1051998464) and (`t2`.`c3` = 'Tammy Bell'))"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`t2`.`c2` = 1051998464) and (`t2`.`c3` = 'Tammy Bell'))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal(1051998464, `t2`.`c2`) and multiple equal('Tammy Bell', `t2`.`c3`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal(1051998464, `t2`.`c2`) and multiple equal('Tammy Bell', `t2`.`c3`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(multiple equal(1051998464, `t2`.`c2`) and multiple equal('Tammy Bell', `t2`.`c3`))"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [
{
"table": "`t2`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
{
"table": "`t2`",
"field": "c2",
"equals": "1051998464",
"null_rejecting": true
},
{
"table": "`t2`",
"field": "c3",
"equals": "'Tammy Bell'",
"null_rejecting": true
}
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`t2`",
"rows": 1,
"cost": 1,
"table_type": "const",
"empty": false
}
] /* rows_estimation */
},
{
"condition_on_constant_tables": "true",
"condition_value": true
},
{
"attaching_conditions_to_tables": {
"original_condition": "true",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
--分析完成，关闭OPTIMIZER_TRACE
SET optimizer_trace="enabled=off";
所以，优化器认为选择哪个索引都是一样的，就看哪个索引排序更靠前。
从执行SELECT时的debug trace结果也能佐证：
-- 1、 T1表，k3索引在前面 PRIMARY KEY (`c1`), UNIQUE KEY `k3` (`c3`), UNIQUE KEY `k2` (`c2`) T@2: | | | | | | | | opt: (null): starting struct T@2: | | | | | | | | opt: table: "`t1`" T@2: | | | | | | | | opt: field: "c3" (C3在前面，因此最后使用k3) T@2: | | | | | | | | >convert_string T@2: | | | | | | | | | >alloc_root T@2: | | | | | | | | | | enter: root: 0x40a8068 T@2: | | | | | | | | | | exit: ptr: 0x4b41ab0 T@2: | | | | | | | | | <alloc_root 304 T@2: | | | | | | | | <convert_string 2610 T@2: | | | | | | | | opt: equals: "'Louise Garrett'" T@2: | | | | | | | | opt: null_rejecting: 0 T@2: | | | | | | | | opt: (null): ending struct T@2: | | | | | | | | opt: Key_use: optimize= 0 used_tables=0x0 ref_table_rows= 18446744073709551615 keypart_map= 1 T@2: | | | | | | | | opt: (null): starting struct T@2: | | | | | | | | opt: table: "`t1`" T@2: | | | | | | | | opt: field: "c2" T@2: | | | | | | | | opt: equals: "22896242" T@2: | | | | | | | | opt: null_rejecting: 0 T@2: | | | | | | | | opt: null_rejecting: 0 T@2: | | | | | | | | opt: (null): ending struct T@2: | | | | | | | | opt: Key_use: optimize= 0 used_tables=0x0 ref_table_rows= 18446744073709551615 keypart_map= 1 T@2: | | | | | | | | opt: (null): starting struct T@2: | | | | | | | | opt: table: "`t1`" T@2: | | | | | | | | opt: field: "c2" T@2: | | | | | | | | opt: equals: "22896242" T@2: | | | | | | | | opt: null_rejecting: 0 T@2: | | | | | | | | opt: (null): ending struct T@2: | | | | | | | | opt: ref_optimizer_key_uses: ending struct T@2: | | | | | | | | opt: (null): ending struct -- 2、 T2表，k2索引在前面 PRIMARY KEY (`c1`), UNIQUE KEY `k2` (`c2`), UNIQUE KEY `k3` (`c3`) T@2: | | | | | | | | opt: (null): starting struct T@2: | | | | | | | | opt: table: "`t2`" T@2: | | | | | | | | opt: field: "c2" （C2在前面因此使用k2索引） T@2: | | | | | | | | opt: equals: "22896242" T@2: | | | | | | | | opt: null_rejecting: 0 T@2: | | | | | | | | opt: (null): ending struct T@2: | | | | | | | | opt: Key_use: optimize= 0 used_tables=0x0 ref_table_rows= 18446744073709551615 keypart_map= 1 T@2: | | | | | | | | opt: (null): starting struct T@2: | | | | | | | | opt: table: "`t2`" T@2: | | | | | | | | opt: field: "c3" T@2: | | | | | | | | >convert_string T@2: | | | | | | | | | >alloc_root T@2: | | | | | | | | | | enter: root: 0x40a8068 T@2: | | | | | | | | | | exit: ptr: 0x4b41ab0 T@2: | | | | | | | | | <alloc_root 304 T@2: | | | | | | | | <convert_string 2610 T@2: | | | | | | | | opt: equals: "'Louise Garrett'" T@2: | | | | | | | | opt: null_rejecting: 0 T@2: | | | | | | | | opt: (null): ending struct T@2: | | | | | | | | opt: ref_optimizer_key_uses: ending struct T@2: | | | | | | | | opt: (null): ending struct
4.问题延伸
到这里，我们不禁有疑问，这两个索引的代价真的是一样吗？
就让我们用 mysqlslap 来做个简单对比测试吧：
-- 测试1：对c2列随机point select
[root@ocky-Linux-8-4 ~]# mysqlslap -h127.0.0.1 -uuser -p123456 --no-drop --create-schema wang -i 3 --number-of-queries 1000000 -q "set @xid = cast(round(rand()*2147265929) as unsigned); select * from t1 where c2 = @xid" -c 8
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 40.534 seconds
Minimum number of seconds to run all queries: 39.465 seconds
Maximum number of seconds to run all queries: 41.746 seconds
Number of clients running queries: 8
Average number of queries per client: 125000
-- 测试2：对c3列随机point select
[root@ocky-Linux-8-4 ~]# mysqlslap -h127.0.0.1 -uuser -p123456 --no-drop --create-schema wang -i 3 --number-of-queries 1000000 -q "set @xid = concat('u',cast(round(rand()*2147265929) as unsigned)); select * from t1 where c3 = @xid" -c 8
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 42.275 seconds
Minimum number of seconds to run all queries: 41.714 seconds
Maximum number of seconds to run all queries: 43.138 seconds
Number of clients running queries: 8
Average number of queries per client: 125000
可以看到，如果是走 c3 列索引，耗时会比走 c2 列索引多出来约 4%（在我的环境下测试的结果，不同环境、不同数据量可能也不同）。
看来，MySQL优化器还是有必要进一步提高的哟 ：）
测试使用版本：MySQL 8.0.27（MySQL 5.6.39结果亦是如此）。
原文链接：https://mp.weixin.qq.com/s/8pwqetWeORuDgs2VSSfEkg
这篇关于MySQL为什么"错误"选择代价更大的索引的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-06-25MySQL报错Duplicate entry '0' for key 'PRIMARY'
- 2024-05-29阿里 Canal 实时同步 MySQL 增量数据至 ClickHouse 数据库
- 2024-05-24在Linux下管理MySQL的大小写敏感性
- 2024-04-26MySQL查出时间比实际晚8小时的解决方案
- 2024-04-01JPA不识别MySQL的枚举类型
- 2024-03-30mysql数据库表卡死解决方法
- 2024-03-15MySQL多数据源笔记5-ShardingJDBC实战
- 2024-03-11natural join mysql
- 2024-03-11关于VS2017，VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案
- 2024-02-26mysql 阿里云xb后缀备份文件恢复-icode9专业技术文章分享
