5000w表通过主键in 查询查询走的是全表扫描

End · 2022 年8 月 5 日 14:25

【使用环境】测试环境
【 OB 】
【使用版本】3.1
【问题描述】表的数据有5000w，sql通过主键id in查询发现是全表扫描，in里的id有200个
【复现路径】

explain
SELECT id, user_id, community_id,name, gender, birthday, mobile, credential_type, credential_number FROM inha1
WHERE id IN ( ‘7b1685107706483a954bee3175186bb3’ , ‘7b752e38424a48af85e1dfc5c8de7636’ , ‘7bba52184bd4419daf46c692666f33a4’ , ‘7bd26d8c671e4c04b898eca48ac27fe7’ , ‘7bef8799ecef4de3a1ec589bb75c7cd3’ , ‘7bf3396b6cb145fd9650dd3c24261848’ , ‘7bff651a478245bc89a0198a292d72f3’ , ‘7c237ea11e884bec8d5b443025e643c8’ , ‘7c6dc76a71bb474ab669cc04c82abf27’ , ‘7c8d54ee887145ca85c87b3b1a4d8c32’ , ‘7cb59cd9bfe64fa0ae1e2bf1dd1ab172’ , ‘7cb71c8688b7481e887722e884ed01ae’ , ‘7ce6eb3d67c94559bb0e8bd3c4ccbcd4’ , ‘7cf8874712e94b109ee7bea012bed5fc’ , ‘7d110f9c227b4511b48d889d1dc5cb87’ , ‘7d22e3aca675436988c71f621e54ddd1’ , ‘7d2f20870c0c4a3a9fea7ae47cfc692d’ , ‘7d4d95b9dcc647729a8d140e76d6ed98’ , ‘7d9a0f1c503a48b8a42f1ca5831a0fef’ , ‘7d9c9813fb534c4b996fd82ebef4ba28’ , ‘7da10f07f68a474aacdab48a4931f3c3’ , ‘7db1436671134067a69253a44c247588’ , ‘7db95ac2e9b44ac1a7e93e8e9128072c’ , ‘7de5683c0d834a3b8ba5060436c940dd’ , ‘7e35c743d889464a9a88f766e7605672’ , ‘7e5df4c700b84f2b8f3062916571e9eb’ , ‘7e602cfc596c43b081a2ea8fa59fcb0b’ , ‘7e912004a3de4164877a182f0e4b3dc2’ , ‘7e9d1856e77f4845b8dfbc79d5c5b148’ , ‘7f01474dc337485586873240cb2ea721’ , ‘7f203a0827444d8d9c934e6b5453083c’ , ‘7f248fb19d224cc59e81f5068168c4ba’ , ‘7f336cfcfe1545c996affdb3804113c0’ , ‘7f50b8ddca4948cd964935bfa5a227c2’ , ‘7f5d2d8d19d24623918df5f22c62698e’ , ‘7f92131cea7c499fb8d6418b6994c0d7’ , ‘7f9a5c47080c490f9deb91e7820e050e’ , ‘7fa0ae95dd3d4c69be47c764c66074fe’ , ‘7fafa9bd0f684307932b8bfbbb6a2db7’ , ‘7fc0a7cd950a4703b7798cca0135ac7e’ , ‘7fca6f2ac3d74442955f7a41b4dc69d1’ , ‘7fcee58e57b54d55ae74952554832d01’ , ‘7ffb2e00ae09463f995cae938917b362’ , ‘80552301e08c4a8b9b7cfa37a589c729’ , ‘8068aa75528740cb99879e23072c7bc3’ , ‘807390bea50b4bd1b4ca86b56e5c4299’ , ‘8096d2f5f894437a8ce97445e905a930’ , ‘80a73f87a08747a5aa3053ce73ceaf33’ , ‘80c32d09018e48b8a021349cd07aaa7a’ , ‘80fc92d4ba994ae89a12f646f54b4fc3’ , ‘8101d71476644b4fadcf186ec717fb2f’ , ‘812902f419d5428897d411879f97cac1’ , ‘814802dd9e664dedbbe8ccaf8656c6cf’ , ‘816894f21e3b4f8fa7e94e65151359a3’ , ‘8192d4c630e848a89b4f7a4ce2b800f4’ , ‘81c8bfc26d024069b4babf130bd8aafd’ , ‘81d8e5aaee56404bb7c510efe204bfc7’ , ‘81f012e866904cdfbd5410f9690c568c’ , ‘81f9531de97549a7b6c75bb4bcd16500’ , ‘820a0be9b21b40b3911c4401d45af7f3’ , ‘822894df85f944c1a895bcf2f0885e5e’ , ‘8228c09cab6e4de78f15ff188eede6b2’ , ‘82353aef9022436cba0359292154e861’ , ‘823daf49d58a4e9dbb06e26aac4e479c’ , ‘825c79569ad0424c9b472707e3c90d96’ , ‘8285419cf3e24122924188b053a6866f’ , ‘82901574a6404ae59b13b7b94e6f7ff4’ , ‘8292a90cb1f54e0695ea6529bfb4ba33’ , ‘829879f4a4cb4a5b8d0b020963fff427’ , ‘82d232cbb4b04d62af8f50d6259ea605’ , ‘82e47f7298c641a693707eb47bd50898’ , ‘82fff01f7c934e4fbaf90705b132fb48’ , ‘8384b47c9d4f464b943f5cc50f72a35c’ , ‘83a49bc71ac64c59ba860b1af0ec8da7’ , ‘83c31ca3a90e48a3b76a5f17c703bb51’ , ‘840a90977c7e446fa347a7cd2d437668’ , ‘84592772471047f38335fa72f6cd45fb’ , ‘847ec76eb07c4b8a90d3ba46be1ab7cc’ , ‘84904d29e19345f09d7dbcaf506b7439’ , ‘8499310b15d141fe8557bae37cb0cecb’ , ‘84d8c24f54b047c7b994bcae568467b6’ , ‘84d9e218fd70442896580aeb492c5b7d’ , ‘85048e6c03184add8db28a9d9baa8af5’ , ‘85628ef2772443c7824be0a1af2fc5a2’ , ‘856735dac35a40faba04af9a01e2f885’ , ‘858ad82a0c274d43a86c39c82ad5c87c’ , ‘858df78da83847789e3333810e2227ec’ , ‘85963a4cdf8f47d18fa714098f6a94e6’ , ‘8597bff7f645460baf5dbed7dd5221f5’ , ‘85c2278193bf4f6d881150ebcbb484dc’ , ‘85c3b04f513041bc8782956679428e83’ , ‘85c7a155e7264467b10d1cc35dcc2e8b’ , ‘85c9a3b1e22740e6b1e8b0ee723f15e3’ , ‘85e140fb4ff04c02bf6c315ab1f62406’ , ‘8603ec1b0603443789919c4b52caf9dd’ , ‘8605d52597ec4ecf8e4764f0d856a50d’ , ‘8635caf983164d7d9a2d2d222e8920cb’ , ‘864625d7087c4be6b12cf9c47e615e6e’ , ‘865040298c0f4ff882479f76d07520ff’ , ‘8664d75e7a804ab0a9d753af59f083fb’ );
±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ===============================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |

|0 |PX COORDINATOR | |49582509 |73066857|
|1 | EXCHANGE OUT DISTR |:EX10000 |49582509 |20009875|
|2 | PX PARTITION ITERATOR| |49582509 |20009875|
|3 | TABLE SCAN |inha1|49582509 |20009875|

Outputs & filters:

0 - output([INTERNAL_FUNCTION(inha1.id, inha1.user_id, inha1.community_id, inha1.name, inha1.gender, inha1.birthday, inha1.mobile, inha1.credential_type, inha1.credential_number)]), filter(nil)
1 - output([INTERNAL_FUNCTION(inha1.id, inha1.user_id, inha1.community_id, inha1.name, inha1.gender, inha1.birthday, inha1.mobile, inha1.credential_type, inha1.credential_number)]), filter(nil), dop=1
2 - output([inha1.community_id], [inha1.id], [inha1.user_id], [inha1.name], [inha1.gender], [inha1.birthday], [inha1.mobile], [inha1.credential_type], [inha1.credential_number]), filter(nil)
3 - output([inha1.community_id], [inha1.id], [inha1.user_id], [inha1.name], [inha1.gender], [inha1.birthday], [inha1.mobile], [inha1.credential_type], [inha1.credential_number]), filter([inha1.id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]),
access([inha1.community_id], [inha1.id], [inha1.user_id], [inha1.name], [inha1.gender], [inha1.birthday], [inha1.mobile], [inha1.credential_type], [inha1.credential_number]), partitions(p[0-15])
|
±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)

表的数据量有5000w ，通过一个列做了16个分区
CREATE TABLE inha1 (
id char(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
community_id char(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
user_id char(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
name varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
pinyin_name varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’ ,
gender tinyint(1) NOT NULL DEFAULT ‘-1’ COMMENT ',
mobile varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
birthday date DEFAULT NULL,
credential_type tinyint(1) NOT NULL DEFAULT ‘1’,
credential_number varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
nation varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
educational_level tinyint(1) DEFAULT NULL,
work_unit varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
position varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
religion tinyint(1) DEFAULT NULL,
english_name varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
email varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ',
province varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
city varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
county varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
address_detail varchar(640) COLLATE utf8mb4_unicode_ci DEFAULT ',
remark varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
have_room tinyint(4) NOT NULL DEFAULT ‘0’ ',
upload_face tinyint(4) NOT NULL DEFAULT ‘0’ ',
is_certification tinyint(1) NOT NULL DEFAULT ‘0’,
third_id varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ',
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
call_enable tinyint(4) NOT NULL DEFAULT ‘0’ ,
PRIMARY KEY (community_id, id),
UNIQUE KEY uniq_community_id_user_id (community_id, user_id) BLOCK_SIZE 16384 GLOBAL,
KEY idx4 (id, community_id) BLOCK_SIZE 16384 LOCAL,
KEY idx_credential_number (credential_number) BLOCK_SIZE 16384 LOCAL,
KEY idx_id (id) BLOCK_SIZE 16384 LOCAL,
KEY idx_user_id (user_id) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
partition by key(community_id)
(partition p0,
partition p1,
partition p2,
partition p3,
partition p4,
partition p5,
partition p6,
partition p7,
partition p8,
partition p9,
partition p10,
partition p11,
partition p12,
partition p13,
partition p14,
partition p15) |
±------------

【问题现象及影响】
问题就是主键查询却使用了全表扫描
【附件】

夏进 · 2022 年8 月 5 日 15:04

你好，麻烦 explain extended 看看？

夏进 · 2022 年8 月 5 日 15:13

注意到你的主键是组合键，但是你在in查询时使用的只有id，因此不能走主键索引流程

End · 2022 年8 月 5 日 15:16

explain extended SELECT id, user_id, community_id,name, gender, birthday, mobile, credential_type, credential_number FROM
inha1 WHERE id IN ( ‘7b1685107706483a954bee3175186bb3’ , ‘7b752e38424a48af85e1dfc5c8de7636’ , ‘7bba52184bd4419daf46c692666f33a4’ , ‘7bd26d8c671e4c04b898eca48ac27fe7’ , ‘7bef8799ecef4de3a1ec589bb75c7cd3’ , ‘7bf3396b6cb145fd9650dd3c24261848’ , ‘7bff651a478245bc89a0198a292d72f3’ , ‘7c237ea11e884bec8d5b443025e643c8’ , ‘7c6dc76a71bb474ab669cc04c82abf27’ , ‘7c8d54ee887145ca85c87b3b1a4d8c32’ , ‘7cb59cd9bfe64fa0ae1e2bf1dd1ab172’ , ‘7cb71c8688b7481e887722e884ed01ae’ , ‘7ce6eb3d67c94559bb0e8bd3c4ccbcd4’ , ‘7cf8874712e94b109ee7bea012bed5fc’ , ‘7d110f9c227b4511b48d889d1dc5cb87’ , ‘7d22e3aca675436988c71f621e54ddd1’ , ‘7d2f20870c0c4a3a9fea7ae47cfc692d’ , ‘7d4d95b9dcc647729a8d140e76d6ed98’ , ‘7d9a0f1c503a48b8a42f1ca5831a0fef’ , ‘7d9c9813fb534c4b996fd82ebef4ba28’ , ‘7da10f07f68a474aacdab48a4931f3c3’ , ‘7db1436671134067a69253a44c247588’ , ‘7db95ac2e9b44ac1a7e93e8e9128072c’ , ‘7de5683c0d834a3b8ba5060436c940dd’ , ‘7e35c743d889464a9a88f766e7605672’ , ‘7e5df4c700b84f2b8f3062916571e9eb’ , ‘7e602cfc596c43b081a2ea8fa59fcb0b’ , ‘7e912004a3de4164877a182f0e4b3dc2’ , ‘7e9d1856e77f4845b8dfbc79d5c5b148’ , ‘7f01474dc337485586873240cb2ea721’ , ‘7f203a0827444d8d9c934e6b5453083c’ , ‘7f248fb19d224cc59e81f5068168c4ba’ , ‘7f336cfcfe1545c996affdb3804113c0’ , ‘7f50b8ddca4948cd964935bfa5a227c2’ , ‘7f5d2d8d19d24623918df5f22c62698e’ , ‘7f92131cea7c499fb8d6418b6994c0d7’ , ‘7f9a5c47080c490f9deb91e7820e050e’ , ‘7fa0ae95dd3d4c69be47c764c66074fe’ , ‘7fafa9bd0f684307932b8bfbbb6a2db7’ , ‘7fc0a7cd950a4703b7798cca0135ac7e’ , ‘7fca6f2ac3d74442955f7a41b4dc69d1’ , ‘7fcee58e57b54d55ae74952554832d01’ , ‘7ffb2e00ae09463f995cae938917b362’ , ‘80552301e08c4a8b9b7cfa37a589c729’ , ‘8068aa75528740cb99879e23072c7bc3’ , ‘807390bea50b4bd1b4ca86b56e5c4299’ , ‘8096d2f5f894437a8ce97445e905a930’ , ‘80a73f87a08747a5aa3053ce73ceaf33’ , ‘80c32d09018e48b8a021349cd07aaa7a’ , ‘80fc92d4ba994ae89a12f646f54b4fc3’ , ‘8101d71476644b4fadcf186ec717fb2f’ , ‘812902f419d5428897d411879f97cac1’ , ‘814802dd9e664dedbbe8ccaf8656c6cf’ , ‘’ , ‘84d9e218fd70442896580aeb492c5b7d’ , ‘85048e6c03184add8db28a9d9baa8af5’ , ‘85628ef2772443c7824be0a1af2fc5a2’ , ‘856735dac35a40faba04af9a01e2f885’ , ‘858ad82a0c274d43a86c39c82ad5c87c’ , ‘858df78da83847789e3333810e2227ec’ , ‘85963a4cdf8f47d18fa714098f6a94e6’ , ‘8597bff7f645460baf5dbed7dd5221f5’ , ‘85c2278193bf4f6d881150ebcbb484dc’ , ‘85c3b04f513041bc8782956679428e83’ , ‘85c7a155e7264467b10d1cc35dcc2e8b’ , ‘85c9a3b1e22740e6b1e8b0ee723f15e3’ , ‘85e140fb4ff04c02bf6c315ab1f62406’ , ‘8603ec1b0603443789919c4b52caf9dd’ , ‘8605d52597ec4ecf8e4764f0d856a50d’ , ‘8635caf983164d7d9a2d2d222e8920cb’ , ‘864625d7087c4be6b12cf9c47e615e6e’ , ‘865040298c0f4ff882479f76d07520ff’ , ‘8664d75e7a804ab0a9d753af59f083fb’ );
±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| ===============================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |

|0 |PX COORDINATOR | |49582509 |73066857|
|1 | EXCHANGE OUT DISTR |:EX10000 |49582509 |20009875|
|2 | PX PARTITION ITERATOR| |49582509 |20009875|
|3 | TABLE SCAN |inha1|49582509 |20009875|

Outputs & filters:

0 - output([INTERNAL_FUNCTION(inha1.id(0x7f084e9df630), inha1.user_id(0x7f084e9e2510), inha1.community_id(0x7f084e9cc8a0), inha1.name(0x7f084e9e2db0), inha1.gender(0x7f084e9e3360), inha1.birthday(0x7f084e9e3910), inha1.mobile(0x7f084e9e3ec0), inha1.credential_type(0x7f084e9e4470), inha1.credential_number(0x7f084e9e4a20))(0x7f0734bc39a0)]), filter(nil)
1 - output([INTERNAL_FUNCTION(inha1.id(0x7f084e9df630), inha1.user_id(0x7f084e9e2510), inha1.community_id(0x7f084e9cc8a0), inha1.name(0x7f084e9e2db0), inha1.gender(0x7f084e9e3360), inha1.birthday(0x7f084e9e3910), inha1.mobile(0x7f084e9e3ec0), inha1.credential_type(0x7f084e9e4470), inha1.credential_number(0x7f084e9e4a20))(0x7f0734bc39a0)]), filter(nil), dop=1
2 - output([inha1.community_id(0x7f084e9cc8a0)], [inha1.id(0x7f084e9df630)], [inha1.user_id(0x7f084e9e2510)], [inha1.name(0x7f084e9e2db0)], [inha1.gender(0x7f084e9e3360)], [inha1.birthday(0x7f084e9e3910)], [inha1.mobile(0x7f084e9e3ec0)], [inha1.credential_type(0x7f084e9e4470)], [inha1.credential_number(0x7f084e9e4a20)]), filter(nil),
force partition granule.
3 - output([inha1.community_id(0x7f084e9cc8a0)], [inha1.id(0x7f084e9df630)], [inha1.user_id(0x7f084e9e2510)], [inha1.name(0x7f084e9e2db0)], [inha1.gender(0x7f084e9e3360)], [inha1.birthday(0x7f084e9e3910)], [inha1.mobile(0x7f084e9e3ec0)], [inha1.credential_type(0x7f084e9e4470)], [inha1.credential_number(0x7f084e9e4a20)]), filter([inha1.id(0x7f084e9df630) IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)(0x7f084e9cd530)(0x7f084e9ccba0)]),
access([inha1.community_id(0x7f084e9cc8a0)], [inha1.id(0x7f084e9df630)], [inha1.user_id(0x7f084e9e2510)], [inha1.name(0x7f084e9e2db0)], [inha1.gender(0x7f084e9e3360)], [inha1.birthday(0x7f084e9e3910)], [inha1.mobile(0x7f084e9e3ec0)], [inha1.credential_type(0x7f084e9e4470)], [inha1.credential_number(0x7f084e9e4a20)]), partitions(p[0-15]),
is_index_back=false, filter_before_indexback[false],
range_key([inha1.community_id(0x7f084e9cc8a0)], [inha1.id(0x7f084e9df630)]), range(MIN,MIN ; MAX,MAX)always true

Used Hint:

/*+
*/

Outline Data:

/*+
BEGIN_OUTLINE_DATA
FULL(@“SEL$1” “neptune.inha1”@“SEL$1”)
END_OUTLINE_DATA
*/

Plan Type:

DISTRIBUTED

Optimization Info:

inha1:table_rows:50083342, physical_range_rows:50083342, logical_range_rows:50083342, index_back_rows:0, output_rows:49582509, est_method:basic_stat, optimization_method=cost_based, avaiable_index_name[idx4,idx_id,inha1], pruned_index_name[uniq_community_id_user_id,idx_credential_number,idx_user_id]
Parameters

{obj:{“VARCHAR”:“7b1685107706483a954bee3175186bb3”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7b752e38424a48af85e1dfc5c8de7636”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7bba52184bd4419daf46c692666f33a4”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7bd26d8c671e4c04b898eca48ac27fe7”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7bef8799ecef4de3a1ec589bb75c7cd3”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7bf3396b6cb145fd9650dd3c24261848”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7bff651a478245bc89a0198a292d72f3”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7c237ea11e884bec8d5b443025e643c8”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7c6dc76a71bb474ab669cc04c82abf27”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7c8d54ee887145ca85c87b3b1a4d8c32”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7cb59cd9bfe64fa0ae1e2bf1dd1ab172”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7cb71c8688b7481e887722e884ed01ae”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7ce6eb3d67c94559bb0e8bd3c4ccbcd4”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7cf8874712e94b109ee7bea012bed5fc”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7d110f9c227b4511b48d889d1dc5cb87”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7d22e3aca675436988c71f621e54ddd1”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7d2f20870c0c4a3a9fea7ae47cfc692d”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7d4d95b9dcc647729a8d140e76d6ed98”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7d9a0f1c503a48b8a42f1ca5831a0fef”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7d9c9813fb534c4b996fd82ebef4ba28”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7da10f07f68a474aacdab48a4931f3c3”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7db1436671134067a69253a44c247588”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7db95ac2e9b44ac1a7e93e8e9128072c”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7de5683c0d834a3b8ba5060436c940dd”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7e35c743d889464a9a88f766e7605672”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7e5df4c700b84f2b8f3062916571e9eb”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7e602cfc596c43b081a2ea8fa59fcb0b”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7e912004a3de4164877a182f0e4b3dc2”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7e9d1856e77f4845b8dfbc79d5c5b148”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7f01474dc337485586873240cb2ea721”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7f203a0827444d8d9c934e6b5453083c”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7f248fb19d224cc59e81f5068168c4ba”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7f336cfcfe1545c996affdb3804113c0”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7f50b8ddca4948cd964935bfa5a227c2”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7f5d2d8d19d24623918df5f22c62698e”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7f92131cea7c499fb8d6418b6994c0d7”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7f9a5c47080c490f9deb91e7820e050e”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7fa0ae95dd3d4c69be47c764c66074fe”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7fafa9bd0f684307932b8bfbbb6a2db7”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7fc0a7cd950a4703b7798cca0135ac7e”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7fca6f2ac3d74442955f7a41b4dc69d1”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7fcee58e57b54d55ae74952554832d01”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“VARCHAR”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}}, {obj:{“VARCHAR”:“7ffb2e00ae09463f995cae938917b362”, collation:“utf8mb4_unicode_ci”, coercibility:“INVALID”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1,

夏进 · 2022 年8 月 5 日 15:17

是的，因为id不是主键，因此id in走的不是主键索引，而是全表扫描，通过看这里的range_key范围[min,max]也可以知道。另外因为id是local index，使用in查询时还需要回表一次，还不如直接全表扫

End · 2022 年8 月 5 日 15:18

但是我把表的数据重新导入导入了一下就正常了
就算回表，要检索数据量顶多200+行，也不至于走全表吧

|ID|OPERATOR |NAME |EST. ROWS|COST|

|0 |PX COORDINATOR | |1 |93 |
|1 | EXCHANGE OUT DISTR |:EX10000 |1 |92 |
|2 | PX PARTITION ITERATOR| |1 |92 |
|3 | TABLE SCAN |inha2(idx4)|1 |92 |

Outputs & filters:

0 - output([INTERNAL_FUNCTION(inhabitant.id, inhabitant.user_id, inhabitant.community_id, inhabitant.name, inhabitant.gender, inhabitant.birthday, inhabitant.mobile, inhabitant.credential_type, inhabitant.credential_number)]), filter(nil)
1 - output([INTERNAL_FUNCTION(inhabitant.id, inhabitant.user_id, inhabitant.community_id, inhabitant.name, inhabitant.gender, inhabitant.birthday, inhabitant.mobile, inhabitant.credential_type, inhabitant.credential_number)]), filter(nil), dop=1
2 - output([inhabitant.community_id], [inhabitant.id], [inhabitant.user_id], [inhabitant.name], [inhabitant.gender], [inhabitant.birthday], [inhabitant.mobile], [inhabitant.credential_type], [inhabitant.credential_number]), filter(nil)
3 - output([inhabitant.community_id], [inhabitant.id], [inhabitant.user_id], [inhabitant.name], [inhabitant.gender], [inhabitant.birthday], [inhabitant.mobile], [inhabitant.credential_type], [inhabitant.credential_number]), filter(nil),
access([inhabitant.community_id], [inhabitant.id], [inhabitant.user_id], [inhabitant.name], [inhabitant.gender], [inhabitant.birthday], [inhabitant.mobile], [inhabitant.credential_type], [inhabitant.credential_number]), partitions(p[0-15])
|

夏进 · 2022 年8 月 5 日 16:12

是否id的distinct数量跟in list中的差不多？
可能基于上述条件，OB认为大部分的行不会被过滤，基于filter的预估模型，认为代价太大，即便有id列上有local index，最后仍然选择了全表扫

End · 2022 年8 月 5 日 16:58

可是i我数据是一样的，放到同样的表结构其他的名字的表里查询就正常了。所以不是很理解

夏进 · 2022 年8 月 5 日 17:00

这里肯定是有不一样的地方吧，你再排查一下？

End · 2022 年8 月 5 日 17:01

会不会跟表经常rename 有关系？因为我是做表同样的数据下，分区与不分区的测试，经常会rename来rename去的，我感觉好像经常rename就会发生这种情况，其他的表也是会有类似的情况的。

End · 2022 年8 月 5 日 17:02

我自己在看看吧

Kongdom · 2022 年8 月 9 日 09:03

不懂就问，组合主键只使用其中一个属性时，不会走主键么？应该也会走吧

End · 2022 年8 月 10 日 11:40

如果是组合主键第一个列可以走，其他的不能走

5000w表通过主键in 查询查询走的是全表扫描

|0 |PX COORDINATOR | |49582509 |73066857| |1 | EXCHANGE OUT DISTR |:EX10000 |49582509 |20009875| |2 | PX PARTITION ITERATOR| |49582509 |20009875| |3 | TABLE SCAN |inha1|49582509 |20009875|

Outputs & filters:

|0 |PX COORDINATOR | |49582509 |73066857| |1 | EXCHANGE OUT DISTR |:EX10000 |49582509 |20009875| |2 | PX PARTITION ITERATOR| |49582509 |20009875| |3 | TABLE SCAN |inha1|49582509 |20009875|

Outputs & filters:

Used Hint:

Outline Data:

Plan Type:

Optimization Info:

但是我把表的数据重新导入导入了一下就正常了 就算回表，要检索数据量顶多200+行，也不至于走全表吧

|ID|OPERATOR |NAME |EST. ROWS|COST|

|0 |PX COORDINATOR | |1 |93 | |1 | EXCHANGE OUT DISTR |:EX10000 |1 |92 | |2 | PX PARTITION ITERATOR| |1 |92 | |3 | TABLE SCAN |inha2(idx4)|1 |92 |

Outputs & filters:

|0 |PX COORDINATOR | |49582509 |73066857|
|1 | EXCHANGE OUT DISTR |:EX10000 |49582509 |20009875|
|2 | PX PARTITION ITERATOR| |49582509 |20009875|
|3 | TABLE SCAN |inha1|49582509 |20009875|

|0 |PX COORDINATOR | |49582509 |73066857|
|1 | EXCHANGE OUT DISTR |:EX10000 |49582509 |20009875|
|2 | PX PARTITION ITERATOR| |49582509 |20009875|
|3 | TABLE SCAN |inha1|49582509 |20009875|

但是我把表的数据重新导入导入了一下就正常了
就算回表，要检索数据量顶多200+行，也不至于走全表吧

|0 |PX COORDINATOR | |1 |93 |
|1 | EXCHANGE OUT DISTR |:EX10000 |1 |92 |
|2 | PX PARTITION ITERATOR| |1 |92 |
|3 | TABLE SCAN |inha2(idx4)|1 |92 |