注册

一个奇怪的索引问题,执行查询数据库直接崩掉

青菜白玉汤 2023/05/16 1274 2

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:DM8
【操作系统】:UOS20
【CPU】:loongarch64
【问题描述】*:一个大表的状态字段加了索引,单独查询没问题,和其他字段一起查询导致达梦数据库挂掉。最后的图是干掉了family_statu字段的索引。DDL语句在最后,单表记录:2452136条
WBKDBRW2LYDPL3AX1CC0.png
image.png
image.png

CREATE TABLE "SHTS"."TB_MZ_FAMILY_M"
(
"M_FAMILY_ID" BIGINT NOT NULL,
"LAST_TIME" DEC(18,0) DEFAULT 0,
"CREATE_PARTY_ID" BIGINT DEFAULT (-1),
"FAMILY_ID" BIGINT DEFAULT (-1),
"FAMILY_CODE" VARCHAR(60),
"FAMILY_UID" VARCHAR(60),
"FAMILY_NAME" VARCHAR(200),
"PARTY_ID" BIGINT DEFAULT (-1),
"FAMILY_PERSON_COUNT" TINYINT DEFAULT 0,
"FAMILY_ENSURE_COUNT" TINYINT DEFAULT 0,
"REGI_PERSON_COUNT" TINYINT DEFAULT 0,
"FAMILY_INCOME" DEC(18,2) DEFAULT 0.,
"FAMILY_INCOME_TYPE" TINYINT DEFAULT 3,
"FAMILY_STATU" TINYINT DEFAULT 0,
"STAGE_INT" SMALLINT DEFAULT 0,
"REQ_YEAR" SMALLINT DEFAULT 0,
"ARCHIVE_TYPE" VARCHAR(60),
"FAMILY_ARCHIVE_ID" BIGINT DEFAULT (-1),
"REQMAN_BANK_IS" TINYINT DEFAULT 1,
"PERIOD_ID" BIGINT DEFAULT (-1) NOT NULL,
"M_PARTY_ID" BIGINT DEFAULT (-1),
"M_ARCHIVE_TYPE_CODE" VARCHAR(60),
"FAMILY_MODE" VARCHAR(50) DEFAULT '-1',
NOT CLUSTER PRIMARY KEY("M_FAMILY_ID", "PERIOD_ID")) STORAGE(ON "SHTS", CLUSTERBTR) ;

CREATE INDEX "I_MZ_FAMILY_TYPE_2096103373" ON "SHTS"."TB_MZ_FAMILY_M"("ARCHIVE_TYPE" ASC) STORAGE(ON "SHTS", CLUSTERBTR) ;
CREATE INDEX "I_MZ_FAMILY_M_P_ID" ON "SHTS"."TB_MZ_FAMILY_M"("FAMILY_ID" ASC,"PERIOD_ID" ASC) STORAGE(ON "SHTS", CLUSTERBTR) ;
CREATE INDEX "I_FAMILY_PTID_2096103373" ON "SHTS"."TB_MZ_FAMILY_M"("PARTY_ID" ASC) STORAGE(ON "SHTS", CLUSTERBTR) ;
CREATE INDEX "I_MZ_HIS_M_PPA_2096103373" ON "SHTS"."TB_MZ_FAMILY_M"("PERIOD_ID" ASC,"M_PARTY_ID" ASC,"M_ARCHIVE_TYPE_CODE" ASC) STORAGE(ON "SHTS", CLUSTERBTR) ;
CREATE INDEX "I_MZ_FAMILY_ARCHIVE_2096103373" ON "SHTS"."TB_MZ_FAMILY_M"("FAMILY_ARCHIVE_ID" ASC) STORAGE(ON "SHTS", CLUSTERBTR) ;
CREATE INDEX "IX_TB_MZ_FAMILY_M_CPID" ON "SHTS"."TB_MZ_FAMILY_M"("CREATE_PARTY_ID" ASC) STORAGE(ON "SHTS", CLUSTERBTR) ;
CREATE INDEX "I_FAMILY_UID_2096103373" ON "SHTS"."TB_MZ_FAMILY_M"("FAMILY_UID" ASC) STORAGE(ON "SHTS", CLUSTERBTR) ;
CREATE INDEX "I_MZ_FAMILY_M_PAP_ID" ON "SHTS"."TB_MZ_FAMILY_M"("PERIOD_ID" ASC,"ARCHIVE_TYPE" ASC,"PARTY_ID" ASC) STORAGE(ON "SHTS", CLUSTERBTR) ;
CREATE INDEX "TB_MZ_FAMILY_M_FAMILY_ARCHIVE_ID_IDX" ON "SHTS"."TB_MZ_FAMILY_M"("FAMILY_ARCHIVE_ID" ASC,"PERIOD_ID" ASC,"FAMILY_STATU" ASC) STORAGE(ON "SHTS", CLUSTERBTR) ;

回答 0
暂无回答
扫一扫
联系客服