데이터베이스 질문과 답변
by 아니8083 [2019.07.17 09:46:24]
DB를 작업하다가 질의 사항이 생겨서 질문 드립니다.
A 라는 테이블이 약 3억건 정도 되고 (컬럼은 약 20개)
구성이 아래와 같이 되어 있는 상태에서
인덱스를 ETL_YMD 로 01 인덱스
ETL_YMD, ID, HT로 02 인덱스를 설정하면 성능에 효과가 있을까요?
인덱스와 파티션을 동시에 하면 안된다는 이야기도 있어서 질의 드립니다!
CREATE TABLE `KT_MOVE`.`MOVE_POP_ORG`
(
`ID` int(20),
`X_COORD` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci,
`Y_COORD` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci,
`HT` int(5),
`M00` double(10,2),
`M10` double(10,2),
`M15` double(10,2),
`M20` double(10,2),
`M25` double(10,2),
`M30` double(10,2),
`M35` double(10,2),
`M40` double(10,2),
`M45` double(10,2),
`M50` double(10,2),
`M55` double(10,2),
`M60` double(10,2),
`M65` double(10,2),
`M70` double(10,2),
`F00` double(10,2),
`F10` double(10,2),
`F15` double(10,2),
`F20` double(10,2),
`F25` double(10,2),
`F30` double(10,2),
`F35` double(10,2),
`F40` double(10,2),
`F45` double(10,2),
`F50` double(10,2),
`F55` double(10,2),
`F60` double(10,2),
`F65` double(10,2),
`F70` double(10,2),
`TOT` double(10,2),
`MEGA_CD` int(11),
`ETL_YMD` int(8),
`ADMI_CD` int(15),
INDEX Iidx_pop_move_02 (ETL_YMD),
INDEX idx_pop_move_01 (ETL_YMD, ID, HT)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=Dynamic COMMENT='KT유동인구'
PARTITION BY RANGE (ETL_YMD)
(
PARTITION `p201601` VALUES LESS THAN (20160131) ENGINE=InnoDB,
PARTITION `p201602` VALUES LESS THAN (20160231) ENGINE=InnoDB,
PARTITION `p201603` VALUES LESS THAN (20160331) ENGINE=InnoDB,
PARTITION `p201604` VALUES LESS THAN (20160431) ENGINE=InnoDB,
PARTITION `p201605` VALUES LESS THAN (20160531) ENGINE=InnoDB,
PARTITION `p201606` VALUES LESS THAN (20160631) ENGINE=InnoDB,
PARTITION `p201607` VALUES LESS THAN (20160731) ENGINE=InnoDB,
PARTITION `p201608` VALUES LESS THAN (20160831) ENGINE=InnoDB,
PARTITION `p201609` VALUES LESS THAN (20160931) ENGINE=InnoDB,
PARTITION `p201610` VALUES LESS THAN (20161031) ENGINE=InnoDB,
PARTITION `p201611` VALUES LESS THAN (20161131) ENGINE=InnoDB,
PARTITION `p201612` VALUES LESS THAN (20161231) ENGINE=InnoDB,
PARTITION `p201701` VALUES LESS THAN (20170131) ENGINE=InnoDB,
PARTITION `p201702` VALUES LESS THAN (20170231) ENGINE=InnoDB,
PARTITION `p201703` VALUES LESS THAN (20170331) ENGINE=InnoDB,
PARTITION `p201704` VALUES LESS THAN (20170431) ENGINE=InnoDB,
PARTITION `p201705` VALUES LESS THAN (20170531) ENGINE=InnoDB,
PARTITION `p201706` VALUES LESS THAN (20170631) ENGINE=InnoDB,
PARTITION `p201707` VALUES LESS THAN (20170731) ENGINE=InnoDB,
PARTITION `p201708` VALUES LESS THAN (20170831) ENGINE=InnoDB,
PARTITION `p201709` VALUES LESS THAN (20170931) ENGINE=InnoDB,
PARTITION `p201710` VALUES LESS THAN (20171031) ENGINE=InnoDB,
PARTITION `p201711` VALUES LESS THAN (20171131) ENGINE=InnoDB,
PARTITION `p201712` VALUES LESS THAN (20171231) ENGINE=InnoDB,
PARTITION `p201801` VALUES LESS THAN (20180131) ENGINE=InnoDB,
PARTITION `p201802` VALUES LESS THAN (20180231) ENGINE=InnoDB,
PARTITION `p201803` VALUES LESS THAN (20180331) ENGINE=InnoDB,
PARTITION `p201804` VALUES LESS THAN (20180431) ENGINE=InnoDB,
PARTITION `p201805` VALUES LESS THAN (20180531) ENGINE=InnoDB,
PARTITION `p201806` VALUES LESS THAN (20180631) ENGINE=InnoDB,
PARTITION `p201807` VALUES LESS THAN (20180731) ENGINE=InnoDB,
PARTITION `p201808` VALUES LESS THAN (20180831) ENGINE=InnoDB,
PARTITION `p201809` VALUES LESS THAN (20180931) ENGINE=InnoDB,
PARTITION `p201810` VALUES LESS THAN (20181031) ENGINE=InnoDB,
PARTITION `p201811` VALUES LESS THAN (20181131) ENGINE=InnoDB,
PARTITION `p201812` VALUES LESS THAN (20181231) ENGINE=InnoDB,
PARTITION `p201901` VALUES LESS THAN (20190131) ENGINE=InnoDB,
PARTITION `p201902` VALUES LESS THAN (20190231) ENGINE=InnoDB,
PARTITION `p201903` VALUES LESS THAN (20190331) ENGINE=InnoDB,
PARTITION `p201904` VALUES LESS THAN (20190431) ENGINE=InnoDB,
PARTITION `p201905` VALUES LESS THAN (20190531) ENGINE=InnoDB,
PARTITION `p201906` VALUES LESS THAN (20190631) ENGINE=InnoDB,
PARTITION `p201907` VALUES LESS THAN (20190731) ENGINE=InnoDB,
PARTITION `p201908` VALUES LESS THAN (20190831) ENGINE=InnoDB,
PARTITION `p201909` VALUES LESS THAN (20190931) ENGINE=InnoDB,
PARTITION `p201910` VALUES LESS THAN (20191031) ENGINE=InnoDB,
PARTITION `p201911` VALUES LESS THAN (20191131) ENGINE=InnoDB,
PARTITION `p201912` VALUES LESS THAN (20191231) ENGINE=InnoDB,
PARTITION `pmax` VALUES LESS THAN (MAXVALUE) ENGINE=InnoDB
);
ETL_YMD 컬럼이 "년월일"이고 정확히 이 컬럼으로 범위 파티셔닝을 했기 때문에.. 이 컬럼 단독의 인덱스나 이 컬럼으로 시작하는 인덱스는 의미가 없습니다.
왜냐하면 SELECT, UPDATE, DELETE 문의 WHERE 절에 ETL_YMD 컬럼이 있다고 했을 때, 어차피 파티션 프루닝에 의해서 특정 파티션에 대해서만 I/O가 발생하는데, 해당 파티션에서 ETL_YMD 값은 모두 동일하기 때문입니다.
따라서 01 인덱스는 삭제, 02 인덱스에서는 ETL_YMD 컬럼을 제외하는 것이 좋다고 보입니다.
참고로.. MySQL, MariaDB의 파티셔닝은 INSERT 속도를 저하시키는 단점이 있습니다. 시스템적인 환경에 따라 문제가 일어나는 파티션의 수는 다를테니 테스트가 필요하겠지만.. 아무튼 적절한 파티션 수를 유지할 수 있는 계획도 필요합니다.
아1 그렇군요, 소중한 답변 감사 드립니다! 인덱스를 다른 컬럼으로 걸어야 겠네요! 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.