안녕하세요, 작은 회사에서 DBA를 수행하고 있습니다.
인덱스는 아래 테이블 명세서에 파티션과 모두 있습니다.
여기서 질문이 하나 생겼습니다.
다름이 아니옵고
select * from POP_MOVE_TEMP2 where ETL_YMD = 20180101
로 실행하려고 할 때 아래와 같은 실행 목표가 나오고
실제 데이터가 있는데도 데이터가 나오지 않습니다.
건수가 약 테이블이 4억건 정도 되어서 성능이 너무 나오지 않는데
성능 향상을 위한 팁을 얻을 수 있을까요?
/*---------------------------------------------
-- 오브젝트명: `KT_MOVE`.`POP_MOVE_TEMP2`
-- 생성일자 : 2019-07-04 20:28:01.0
-- 상태: VALID
---------------------------------------------*/
CREATE TABLE `KT_MOVE`.`POP_MOVE_TEMP2`
(
`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(10),
`ADMI_CD` int(15)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=Dynamic
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
);