Description:
The cost value of that the execution plan for full index scan and the execution plan need return from table is close.The second scenario seems to ignore the cost of retrieving data from the clustered index.
mysql> show create table zhouqch.test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id_bank` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '本机机构号',
`code_bank` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '本机实际IFAR机构号',
`name_bank` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '机构名称',
`ename_bank` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '机构简称',
`lvl_bank_cd` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '机构级别代码',
`seqno_bank` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '机构序号',
`stat_bank_cd` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '机构状态代码',
`up_code_bank` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '上级机构号',
`sum_ind` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '汇总标识',
`enabe_dt` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '启用日期',
`enable_ind` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '有效标识',
`id_bank_0` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '最上层机构号',
`name_bank_0` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '最上层机构名称',
`id_bank_1` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第二层机构号',
`name_bank_1` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第二层机构名称',
`id_bank_2` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第三层机构号',
`name_bank_2` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第三层机构名称',
`id_bank_3` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第四层机构号',
`name_bank_3` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第四层机构名称',
`id_bank_4` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第五层机构号',
`name_bank_4` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第五层机构名称',
`id_bank_5` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第六层机构号',
`name_bank_5` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第六层机构名称',
`id_bank_6` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第七层机构号',
`name_bank_6` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第七层机构名称',
`id_bank_7` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第八层机构号',
`name_bank_7` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第八层机构名称',
`startdt` date DEFAULT NULL COMMENT '开始日期',
`validdate` date DEFAULT NULL COMMENT '有效日期',
`enddt` date DEFAULT NULL COMMENT '结束日期',
`timestamp` datetime DEFAULT NULL COMMENT '处理的时间戳',
PRIMARY KEY (`id_bank`),
KEY `idx_org_tree_id_bank` (`id_bank`) USING BTREE,
KEY `idx_org_tree_id_bank_3` (`id_bank_3`) USING BTREE,
KEY `idx_org_tree_id_bank_2` (`id_bank_2`) USING BTREE,
KEY `idx_org_tree_id_bank_1` (`id_bank_1`) USING BTREE,
KEY `idx_org_tree_id_bank_4` (`id_bank_4`) USING BTREE,
KEY `idx_org_tree_id_bank_5` (`id_bank_5`) USING BTREE,
KEY `idx_org_tree_id_bank_6` (`id_bank_6`) USING BTREE,
KEY `idx_org_tree_id_bank_7` (`id_bank_7`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci STATS_SAMPLE_PAGES=10000 ROW_FORMAT=DYNAMIC
1 row in set (0.01 sec)
mysql>
mysql> explain analyze select * from zhouqch.test where id_bank_3='PCT60';
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on test using idx_org_tree_id_bank_3 (id_bank_3='PCT60') (cost=2461.55 rows=23408) (actual time=0.187..147.081 rows=28090 loops=1)
|
+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.15 sec)
mysql> explain analyze select id_bank_3 from zhouqch.test where id_bank_3='PCT60';
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on test using idx_org_tree_id_bank_3 (id_bank_3='PCT60') (cost=2447.45 rows=23408) (actual time=0.055..7.047 rows=28090 loops=1)
|
+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
How to repeat:
mysql> show create table zhouqch.test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id_bank` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '本机机构号',
`code_bank` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '本机实际IFAR机构号',
`name_bank` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '机构名称',
`ename_bank` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '机构简称',
`lvl_bank_cd` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '机构级别代码',
`seqno_bank` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '机构序号',
`stat_bank_cd` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '机构状态代码',
`up_code_bank` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '上级机构号',
`sum_ind` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '汇总标识',
`enabe_dt` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '启用日期',
`enable_ind` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '有效标识',
`id_bank_0` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '最上层机构号',
`name_bank_0` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '最上层机构名称',
`id_bank_1` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第二层机构号',
`name_bank_1` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第二层机构名称',
`id_bank_2` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第三层机构号',
`name_bank_2` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第三层机构名称',
`id_bank_3` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第四层机构号',
`name_bank_3` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第四层机构名称',
`id_bank_4` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第五层机构号',
`name_bank_4` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第五层机构名称',
`id_bank_5` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第六层机构号',
`name_bank_5` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第六层机构名称',
`id_bank_6` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第七层机构号',
`name_bank_6` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第七层机构名称',
`id_bank_7` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第八层机构号',
`name_bank_7` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '第八层机构名称',
`startdt` date DEFAULT NULL COMMENT '开始日期',
`validdate` date DEFAULT NULL COMMENT '有效日期',
`enddt` date DEFAULT NULL COMMENT '结束日期',
`timestamp` datetime DEFAULT NULL COMMENT '处理的时间戳',
PRIMARY KEY (`id_bank`),
KEY `idx_org_tree_id_bank` (`id_bank`) USING BTREE,
KEY `idx_org_tree_id_bank_3` (`id_bank_3`) USING BTREE,
KEY `idx_org_tree_id_bank_2` (`id_bank_2`) USING BTREE,
KEY `idx_org_tree_id_bank_1` (`id_bank_1`) USING BTREE,
KEY `idx_org_tree_id_bank_4` (`id_bank_4`) USING BTREE,
KEY `idx_org_tree_id_bank_5` (`id_bank_5`) USING BTREE,
KEY `idx_org_tree_id_bank_6` (`id_bank_6`) USING BTREE,
KEY `idx_org_tree_id_bank_7` (`id_bank_7`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci STATS_SAMPLE_PAGES=10000 ROW_FORMAT=DYNAMIC
1 row in set (0.01 sec)
mysql>
mysql> select id_bank_3,count(*) from zhouqch.test group by id_bank_3;
+-----------+----------+
| id_bank_3 | count(*) |
+-----------+----------+
| 0001265b | 18718 |
| 04H414 | 8 |
| PCT60 | 28090 |
+-----------+----------+
3 rows in set (0.01 sec)
mysql> explain select * from zhouqch.test where id_bank_3='PCT60';
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------+-------+----------+-------+
| 1 | SIMPLE | test | NULL | ref | idx_org_tree_id_bank_3 | idx_org_tree_id_bank_3 | 75 | const | 23408 | 100.00 | NULL |
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>
mysql> explain select id_bank_3 from zhouqch.test where id_bank_3='PCT60';
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------+-------+----------+-------------+
| 1 | SIMPLE | test | NULL | ref | idx_org_tree_id_bank_3 | idx_org_tree_id_bank_3 | 75 | const | 23408 | 100.00 | Using index |
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>
mysql> explain analyze select * from zhouqch.test where id_bank_3='PCT60';
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on test using idx_org_tree_id_bank_3 (id_bank_3='PCT60') (cost=2461.55 rows=23408) (actual time=0.187..147.081 rows=28090 loops=1)
|
+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.15 sec)
mysql> explain analyze select id_bank_3 from zhouqch.test where id_bank_3='PCT60';
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on test using idx_org_tree_id_bank_3 (id_bank_3='PCT60') (cost=2447.45 rows=23408) (actual time=0.055..7.047 rows=28090 loops=1)
|
+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
Suggested fix:
For the second scenario, the cost of retrieving the cluster index is added to the estimate of the execution plan, so that the correct execution plan is selected when most of the data in the table is scanned.