SELECT s.student_id, s.class_id, s.student_number, s.student_name, s.student_sex, s.student_type, s.student_facepic_url, s.student_facethumbnail_url, s.student_pic_url, s.student_phone, s.student_certificate_type, s.student_certificate_number, s.student_native, s.student_nation, s.student_address, s.birth_date, s.entrance_date, s.graduate_date, s.tenant_id, s.create_time, s.update_time, s.COMMENT, s.is_delete, s.union_id, s.face_token, s.health_code, s.health_update_time, c.`name` as className FROM student st LEFT JOIN class cs on s.class_id = c.id where s.is_delete=0 and s.is_graduate=0 and s.union_id ='3000000000630431559' and s.tenant_id ='5e3be47d47df492bac27879e5d210c6e'; 表结构如下 s 表 数据量900w CREATE TABLE `st` ( `student_id` char(32) NOT NULL, `class_id` char(32) DEFAULT NULL, `student_number` varchar(20) DEFAULT NULL, `student_name` varchar(30) DEFAULT NULL, `student_sex` tinyint(4) DEFAULT NULL, `student_type` tinyint(4) DEFAULT NULL, `student_facepic_url` varchar(255) DEFAULT NULL, `student_facethumbnail_url` varchar(255) DEFAULT NULL, `student_pic_url` varchar(255) DEFAULT NULL, `student_phone` varchar(32) DEFAULT NULL COMMENT '', `student_certificate_type` tinyint(4) DEFAULT NULL, `student_certificate_number` varchar(64) DEFAULT NULL, `student_native` varchar(256) DEFAULT NULL COMMENT '', `student_nation` varchar(32) DEFAULT NULL, `student_address` varchar(512) DEFAULT NULL COMMENT '', `birth_date` varchar(64) DEFAULT NULL, `entrance_date` varchar(32) DEFAULT NULL, `graduate_date` varchar(32) DEFAULT NULL, `tenant_id` char(32) DEFAULT NULL, `is_delete` tinyint(4) DEFAULT NULL, `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `comment` varchar(64) DEFAULT NULL, `is_graduate` tinyint(4) DEFAULT '0', `union_id` varchar(64) DEFAULT NULL, `face_token` varchar(100) DEFAULT NULL, `health_code` varchar(10) DEFAULT NULL, `health_update_time` varchar(16) DEFAULT NULL, `qy_wechat_id` varchar(64) DEFAULT 'a' COMMENT '', PRIMARY KEY (`student_id`), KEY `idx_tenantid` (`tenant_id`) BLOCK_SIZE 16384 LOCAL, KEY `idx_classid` (`class_id`) BLOCK_SIZE 16384 LOCAL, KEY `idx_ctid` (`class_id`, `tenant_id`) BLOCK_SIZE 16384 LOCAL, KEY `idx_tid_sid` (`student_number`, `tenant_id`) BLOCK_SIZE 16384 LOCAL ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0; c 表 数据量 31w CREATE TABLE `cs` ( `id` char(32) NOT NULL, `tenant_id` char(32) NOT NULL, `grade_id` char(32) NOT NULL COMMENT '', `class_adviser_id` char(32) DEFAULT NULL COMMENT '', `code` varchar(20) NOT NULL, `name` varchar(40) NOT NULL, `remark` varchar(250) DEFAULT NULL, `is_delete` tinyint(1) NOT NULL DEFAULT '0', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `is_graduted` tinyint(4) DEFAULT '0', `class_room_id` char(32) DEFAULT NULL, `monitor_id` char(32) DEFAULT NULL, `class_alias` varchar(15) DEFAULT NULL, `class_slogan` varchar(32) DEFAULT NULL COMMENT '', `union_id` varchar(64) DEFAULT NULL, `class_no` int(11) NOT NULL DEFAULT '0' COMMENT '，~499', `entrance_year` int(11) NOT NULL DEFAULT '0' COMMENT '', `graduate_year` int(11) DEFAULT NULL COMMENT '', `qy_wechat_num` int(11) DEFAULT '1', `district_id` char(32) DEFAULT NULL COMMENT '校区企微id', PRIMARY KEY (`id`), KEY `Index_gradeid` (`grade_id`, `is_delete`) BLOCK_SIZE 16384 LOCAL, KEY `Index_tenantid` (`tenant_id`, `is_delete`) BLOCK_SIZE 16384 LOCAL, KEY `idx_class_room_id` (`class_room_id`) BLOCK_SIZE 16384 LOCAL ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 sql如下 SELECT s.student_id, s.class_id, s.student_number, s.student_name, s.student_sex, s.student_type, s.student_facepic_url, s.student_facethumbnail_url, s.student_pic_url, s.student_phone, s.student_certificate_type, s.student_certificate_number, s.student_native, s.student_nation, s.student_address, s.birth_date, s.entrance_date, s.graduate_date, s.tenant_id, s.create_time, s.update_time, s.COMMENT, s.is_delete, s.union_id, s.face_token, s.health_code, s.health_update_time, c.`name` as className FROM student s LEFT JOIN class c on s.class_id = c.id where s.is_delete=0 and s.is_graduate=0 and s.union_id ='3000000000630431559' and s.tenant_id ='1586c6c99a5c4bd1b12bd0d602c8e637'; 租户id占用16w select count(*) from student s where s.union_id ='3000000000630431559' and s.tenant_id ='1586c6c99a5c4bd1b12bd0d602c8e637'; +----------+ | count(*) | +----------+ | 43 | +----------+ 目前其实需要针对s表创建 union_id tenant_id 的联合索引，就可以解决查询慢的问题， 我的问题是先抛开这个不加索引，只看着目前的这个sql的执行计划，为什么执行计划中估算的是1行 ====================================================================== |ID|OPERATOR |NAME |EST. ROWS|COST | ---------------------------------------------------------------------- |0 |PX COORDINATOR | |1 |648754| |1 | EXCHANGE OUT DISTR |:EX10001 |1 |648754| |2 | NESTED-LOOP OUTER JOIN | |1 |648754| |3 | EXCHANGE IN DISTR | |1 |648754| |4 | EXCHANGE OUT DISTR (BC2HOST)|:EX10000 |1 |648754| |5 | TABLE SCAN |s(idx_tenantid)|1 |648754| |6 | TABLE GET |c |1 |36 | ====================================================================== Outputs & filters: ------------------------------------- 0 - output([INTERNAL_FUNCTION(s.student_id(0x7fb1fb73b2c0), s.class_id(0x7fb1fb736a50), s.student_number(0x7fb1fb73bb60), s.student_name(0x7fb1fb73c110), s.student_sex(0x7fb1fb73c6c0), s.student_type(0x7fb1fb73cc70), s.student_facepic_url(0x7fb1fb73d220), s.student_facethumbnail_url(0x7fb1fb73d7d0), s.student_pic_url(0x7fb1fb73dd80), s.student_phone(0x7fb1fb73e330), s.student_certificate_type(0x7fb1fb73e8e0), s.student_certificate_number(0x7fb1fb73ee90), s.student_native(0x7fb1fb741450), s.student_nation(0x7fb1fb741a00), s.student_address(0x7fb1fb741fb0), s.birth_date(0x7fb1fb742560), s.entrance_date(0x7fb1fb742b10), s.graduate_date(0x7fb1fb7510d0), s.tenant_id(0x7fb1fb73ac50), s.create_time(0x7fb1fb751970), s.update_time(0x7fb1fb751f20), s.comment(0x7fb1fb7524d0), s.is_delete(0x7fb1fb737ce0), s.union_id(0x7fb1fb739c80), s.face_token(0x7fb1fb753060), s.health_code(0x7fb1fb753610), s.health_update_time(0x7fb1fb753bc0), c.name(0x7fb1fb754170))(0x7fb1fb7e9a70)]), filter(nil) 1 - output([INTERNAL_FUNCTION(s.student_id(0x7fb1fb73b2c0), s.class_id(0x7fb1fb736a50), s.student_number(0x7fb1fb73bb60), s.student_name(0x7fb1fb73c110), s.student_sex(0x7fb1fb73c6c0), s.student_type(0x7fb1fb73cc70), s.student_facepic_url(0x7fb1fb73d220), s.student_facethumbnail_url(0x7fb1fb73d7d0), s.student_pic_url(0x7fb1fb73dd80), s.student_phone(0x7fb1fb73e330), s.student_certificate_type(0x7fb1fb73e8e0), s.student_certificate_number(0x7fb1fb73ee90), s.student_native(0x7fb1fb741450), s.student_nation(0x7fb1fb741a00), s.student_address(0x7fb1fb741fb0), s.birth_date(0x7fb1fb742560), s.entrance_date(0x7fb1fb742b10), s.graduate_date(0x7fb1fb7510d0), s.tenant_id(0x7fb1fb73ac50), s.create_time(0x7fb1fb751970), s.update_time(0x7fb1fb751f20), s.comment(0x7fb1fb7524d0), s.is_delete(0x7fb1fb737ce0), s.union_id(0x7fb1fb739c80), s.face_token(0x7fb1fb753060), s.health_code(0x7fb1fb753610), s.health_update_time(0x7fb1fb753bc0), c.name(0x7fb1fb754170))(0x7fb1fb7e9a70)]), filter(nil), is_single, dop=1 2 - output([s.student_id(0x7fb1fb73b2c0)], [s.class_id(0x7fb1fb736a50)], [s.student_number(0x7fb1fb73bb60)], [s.student_name(0x7fb1fb73c110)], [s.student_sex(0x7fb1fb73c6c0)], [s.student_type(0x7fb1fb73cc70)], [s.student_facepic_url(0x7fb1fb73d220)], [s.student_facethumbnail_url(0x7fb1fb73d7d0)], [s.student_pic_url(0x7fb1fb73dd80)], [s.student_phone(0x7fb1fb73e330)], [s.student_certificate_type(0x7fb1fb73e8e0)], [s.student_certificate_number(0x7fb1fb73ee90)], [s.student_native(0x7fb1fb741450)], [s.student_nation(0x7fb1fb741a00)], [s.student_address(0x7fb1fb741fb0)], [s.birth_date(0x7fb1fb742560)], [s.entrance_date(0x7fb1fb742b10)], [s.graduate_date(0x7fb1fb7510d0)], [s.tenant_id(0x7fb1fb73ac50)], [s.create_time(0x7fb1fb751970)], [s.update_time(0x7fb1fb751f20)], [s.comment(0x7fb1fb7524d0)], [s.is_delete(0x7fb1fb737ce0)], [s.union_id(0x7fb1fb739c80)], [s.face_token(0x7fb1fb753060)], [s.health_code(0x7fb1fb753610)], [s.health_update_time(0x7fb1fb753bc0)], [c.name(0x7fb1fb754170)]), filter(nil), conds(nil), nl_params_([s.class_id(0x7fb1fb736a50)]), batch_join=true 3 - output([s.class_id(0x7fb1fb736a50)], [s.is_delete(0x7fb1fb737ce0)], [s.union_id(0x7fb1fb739c80)], [s.tenant_id(0x7fb1fb73ac50)], [s.student_id(0x7fb1fb73b2c0)], [s.student_number(0x7fb1fb73bb60)], [s.student_name(0x7fb1fb73c110)], [s.student_sex(0x7fb1fb73c6c0)], [s.student_type(0x7fb1fb73cc70)], [s.student_facepic_url(0x7fb1fb73d220)], [s.student_facethumbnail_url(0x7fb1fb73d7d0)], [s.student_pic_url(0x7fb1fb73dd80)], [s.student_phone(0x7fb1fb73e330)], [s.student_certificate_type(0x7fb1fb73e8e0)], [s.student_certificate_number(0x7fb1fb73ee90)], [s.student_native(0x7fb1fb741450)], [s.student_nation(0x7fb1fb741a00)], [s.student_address(0x7fb1fb741fb0)], [s.birth_date(0x7fb1fb742560)], [s.entrance_date(0x7fb1fb742b10)], [s.graduate_date(0x7fb1fb7510d0)], [s.create_time(0x7fb1fb751970)], [s.update_time(0x7fb1fb751f20)], [s.comment(0x7fb1fb7524d0)], [s.face_token(0x7fb1fb753060)], [s.health_code(0x7fb1fb753610)], [s.health_update_time(0x7fb1fb753bc0)]), filter(nil) 4 - output([s.class_id(0x7fb1fb736a50)], [s.is_delete(0x7fb1fb737ce0)], [s.union_id(0x7fb1fb739c80)], [s.tenant_id(0x7fb1fb73ac50)], [s.student_id(0x7fb1fb73b2c0)], [s.student_number(0x7fb1fb73bb60)], [s.student_name(0x7fb1fb73c110)], [s.student_sex(0x7fb1fb73c6c0)], [s.student_type(0x7fb1fb73cc70)], [s.student_facepic_url(0x7fb1fb73d220)], [s.student_facethumbnail_url(0x7fb1fb73d7d0)], [s.student_pic_url(0x7fb1fb73dd80)], [s.student_phone(0x7fb1fb73e330)], [s.student_certificate_type(0x7fb1fb73e8e0)], [s.student_certificate_number(0x7fb1fb73ee90)], [s.student_native(0x7fb1fb741450)], [s.student_nation(0x7fb1fb741a00)], [s.student_address(0x7fb1fb741fb0)], [s.birth_date(0x7fb1fb742560)], [s.entrance_date(0x7fb1fb742b10)], [s.graduate_date(0x7fb1fb7510d0)], [s.create_time(0x7fb1fb751970)], [s.update_time(0x7fb1fb751f20)], [s.comment(0x7fb1fb7524d0)], [s.face_token(0x7fb1fb753060)], [s.health_code(0x7fb1fb753610)], [s.health_update_time(0x7fb1fb753bc0)]), filter(nil), is_single, dop=1 5 - output([s.class_id(0x7fb1fb736a50)], [s.is_delete(0x7fb1fb737ce0)], [s.union_id(0x7fb1fb739c80)], [s.tenant_id(0x7fb1fb73ac50)], [s.student_id(0x7fb1fb73b2c0)], [s.student_number(0x7fb1fb73bb60)], [s.student_name(0x7fb1fb73c110)], [s.student_sex(0x7fb1fb73c6c0)], [s.student_type(0x7fb1fb73cc70)], [s.student_facepic_url(0x7fb1fb73d220)], [s.student_facethumbnail_url(0x7fb1fb73d7d0)], [s.student_pic_url(0x7fb1fb73dd80)], [s.student_phone(0x7fb1fb73e330)], [s.student_certificate_type(0x7fb1fb73e8e0)], [s.student_certificate_number(0x7fb1fb73ee90)], [s.student_native(0x7fb1fb741450)], [s.student_nation(0x7fb1fb741a00)], [s.student_address(0x7fb1fb741fb0)], [s.birth_date(0x7fb1fb742560)], [s.entrance_date(0x7fb1fb742b10)], [s.graduate_date(0x7fb1fb7510d0)], [s.create_time(0x7fb1fb751970)], [s.update_time(0x7fb1fb751f20)], [s.comment(0x7fb1fb7524d0)], [s.face_token(0x7fb1fb753060)], [s.health_code(0x7fb1fb753610)], [s.health_update_time(0x7fb1fb753bc0)]), filter([s.union_id(0x7fb1fb739c80) = '3000000000630431559'(0x7fb1fb739590)], [s.is_delete(0x7fb1fb737ce0) = 0(0x7fb1fb7375f0)], [s.is_graduate(0x7fb1fb738cb0) = 0(0x7fb1fb7385c0)]), access([s.class_id(0x7fb1fb736a50)], [s.is_delete(0x7fb1fb737ce0)], [s.is_graduate(0x7fb1fb738cb0)], [s.union_id(0x7fb1fb739c80)], [s.tenant_id(0x7fb1fb73ac50)], [s.student_id(0x7fb1fb73b2c0)], [s.student_number(0x7fb1fb73bb60)], [s.student_name(0x7fb1fb73c110)], [s.student_sex(0x7fb1fb73c6c0)], [s.student_type(0x7fb1fb73cc70)], [s.student_facepic_url(0x7fb1fb73d220)], [s.student_facethumbnail_url(0x7fb1fb73d7d0)], [s.student_pic_url(0x7fb1fb73dd80)], [s.student_phone(0x7fb1fb73e330)], [s.student_certificate_type(0x7fb1fb73e8e0)], [s.student_certificate_number(0x7fb1fb73ee90)], [s.student_native(0x7fb1fb741450)], [s.student_nation(0x7fb1fb741a00)], [s.student_address(0x7fb1fb741fb0)], [s.birth_date(0x7fb1fb742560)], [s.entrance_date(0x7fb1fb742b10)], [s.graduate_date(0x7fb1fb7510d0)], [s.create_time(0x7fb1fb751970)], [s.update_time(0x7fb1fb751f20)], [s.comment(0x7fb1fb7524d0)], [s.face_token(0x7fb1fb753060)], [s.health_code(0x7fb1fb753610)], [s.health_update_time(0x7fb1fb753bc0)]), partitions(p0), is_index_back=true, filter_before_indexback[false,false,false], range_key([s.tenant_id(0x7fb1fb73ac50)], [s.student_id(0x7fb1fb73b2c0)]), range(1586c6c99a5c4bd1b12bd0d602c8e637,MIN ; 1586c6c99a5c4bd1b12bd0d602c8e637,MAX), range_cond([s.tenant_id(0x7fb1fb73ac50) = '1586c6c99a5c4bd1b12bd0d602c8e637'(0x7fb1fb73a560)]) 6 - output([c.name(0x7fb1fb754170)]), filter(nil), access([c.name(0x7fb1fb754170)]), partitions(p0), is_index_back=false, range_key([c.id(0x7fb1fb736d10)]), range(MIN ; MAX), range_cond([? = c.id(0x7fb1fb736d10)(0x7fb1fb7c6430)]) Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("minerva.s"@"SEL$1" "minerva.c"@"SEL$1" )) USE_NL(@"SEL$1" ("minerva.c"@"SEL$1" )) PQ_DISTRIBUTE(@"SEL$1" ("minerva.c"@"SEL$1" ) BC2HOST NONE) NO_USE_NL_MATERIALIZATION(@"SEL$1" ("minerva.c"@"SEL$1" )) INDEX(@"SEL$1" "minerva.s"@"SEL$1" "idx_tenantid") FULL(@"SEL$1" "minerva.c"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- s:table_rows:9559567, physical_range_rows:165055, logical_range_rows:165055, index_back_rows:165055, output_rows:0, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_tenantid,idx_classid,idx_ctid,student], pruned_index_name[idx_tid_sid], estimation info[table_id:1101710651082522, (table_type:1, version:0-1666720810273326-1666720810273326, logical_rc:165055, physical_rc:165055), (table_type:7, version:1666720800186663-1666720800186663-1666720828995256, logical_rc:0, physical_rc:0), (table_type:5, version:1666720800186663-1666720800186663-1666720828995256, logical_rc:0, physical_rc:0), (table_type:0, version:1666720828995256-1666720828995256-9223372036854775807, logical_rc:0, physical_rc:0)] c:table_rows:313700, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback Parameters