database.sarang.net
UserID
Passwd
Database
DBMS
ㆍMySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MySQL Q&A 31304 게시물 읽기
No. 31304
파티셔닝 조회 중 사용하지 않는 파티션을 조회하는 경우
작성자
정혜란(bluehr)
작성일
2020-07-30 07:21
조회수
1,552

mysql을 사용하고 있는 사용자 입니다.

질의 사항이 있어 글을 올리게 되었습니다.

mysql에서 파티션 테이블을 사용할 경우, 실행계획을 출력하게 되면 데이터가 들어있지 않을 것 같은 파티션도 함께 실행계획에 나타납니다.

예를 들어, 아래와 같이 테이블을 사용하고 있습니다.

CREATE TABLE test (
log_idx int(10) unsigned NOT NULL AUTO_INCREMENT,
to_dt datetime NOT NULL,
mmdv char(2) NOT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=euckr
PARTITION BY RANGE (TO_DAYS(to_dt))
(
PARTITION PART_201907 VALUES LESS THAN (to_days(20190701)) ENGINE = InnoDB,
PARTITION PART_201908 VALUES LESS THAN (to_days(20190801)) ENGINE = InnoDB,
PARTITION PART_201909 VALUES LESS THAN (to_days(20190901)) ENGINE = InnoDB,
PARTITION PART_201910 VALUES LESS THAN (to_days(20191001)) ENGINE = InnoDB,
PARTITION PART_201911 VALUES LESS THAN (to_days(20191101)) ENGINE = InnoDB,
PARTITION PART_201912 VALUES LESS THAN (to_days(20191201)) ENGINE = InnoDB,
PARTITION PART_202001 VALUES LESS THAN (to_days(20200101)) ENGINE = InnoDB,
PARTITION PART_202002 VALUES LESS THAN (to_days(20200201)) ENGINE = InnoDB,
PARTITION PART_202003 VALUES LESS THAN (to_days(20200301)) ENGINE = InnoDB,
PARTITION PART_202004 VALUES LESS THAN (to_days(20200401)) ENGINE = InnoDB,
PARTITION PART_202005 VALUES LESS THAN (to_days(20200501)) ENGINE = InnoDB,
PARTITION PART_202006 VALUES LESS THAN (to_days(20200601)) ENGINE = InnoDB,
PARTITION PART_202007 VALUES LESS THAN (to_days(20200701)) ENGINE = InnoDB,
PARTITION PART_202008 VALUES LESS THAN (to_days(20200801)) ENGINE = InnoDB,
PARTITION PART_202009 VALUES LESS THAN (to_days(20200901)) ENGINE = InnoDB,
PARTITION PART_MAX VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB;

실행계획을 출력하기 위한 조회 구문은 아래와 같습니다.
EXPLAIN PARTITIONS
SELECT to_dt
FROM test
WHERE to_dt BETWEEN DATE_FORMAT('20200720000000', '%Y/%m/%d %H:%i:%S') AND DATE_FORMAT('20200820235959', '%Y/%m/%d %H:%i:%S')
ORDER BY to_dt DESC LIMIT 3000;

이런식으로 조회를 하게 되면 PART_201909,PART_202008,PART_202009 로 결과가 나오게 됩니다.

PART_202008,PART_202009 파티션 사용은 이해가 갑니다만, PART_201909를 왜 사용하는지 알고 싶습니다.

이 글에 대한 댓글이 총 1건 있습니다.

흠 이거 재미있네요. to_days 함수로 파티션 했을때 첫번째 파티션을 포함해서 스캔하는 현상이 있네요.

전 MariaDB에서 테스트 했는데 현상은 동일한 것 같군요.


MariaDB [test]> DROP TABLE test;
log_idx int NOT NULL,
Query OK, 0 rows affected (0.049 sec)

MariaDB [test]> CREATE TABLE test (
    -> log_idx int NOT NULL,
    -> to_dt datetime NOT NULL
    -> )
    -> PARTITION BY RANGE (TO_DAYS(to_dt))
    -> (
    -> PARTITION PART_201907 VALUES LESS THAN (to_days('2019-07-01')) ,
    -> PARTITION PART_201908 VALUES LESS THAN (to_days('2019-08-01')) ,
    -> PARTITION PART_201909 VALUES LESS THAN (to_days('2019-09-01')) ,
    -> PARTITION PART_201910 VALUES LESS THAN (to_days('2019-10-01')) ,
    -> PARTITION PART_201911 VALUES LESS THAN (to_days('2019-11-01')) ,
    -> PARTITION PART_201912 VALUES LESS THAN (to_days('2019-12-01')) ,
    -> PARTITION PART_202001 VALUES LESS THAN (to_days('2020-01-01')) ,
    -> PARTITION PART_202002 VALUES LESS THAN (to_days('2020-02-01')) ,
    -> PARTITION PART_202003 VALUES LESS THAN (to_days('2020-03-01')) ,
    -> PARTITION PART_202004 VALUES LESS THAN (to_days('2020-04-01')) ,
    -> PARTITION PART_202005 VALUES LESS THAN (to_days('2020-05-01')) ,
    -> PARTITION PART_202006 VALUES LESS THAN (to_days('2020-06-01')) ,
    -> PARTITION PART_202007 VALUES LESS THAN (to_days('2020-07-01')) ,
    -> PARTITION PART_202008 VALUES LESS THAN (to_days('2020-08-01')) ,
    -> PARTITION PART_202009 VALUES LESS THAN (to_days('2020-09-01')) ,
    -> PARTITION PART_MAX VALUES LESS THAN (MAXVALUE));
Query OK, 0 rows affected (0.051 sec)

MariaDB [test]> EXPLAIN PARTITIONS
    -> SELECT to_dt
    -> FROM test
    -> WHERE to_dt >= '2020-07-20 00:00:00';
+------+-------------+-------+----------------------------------------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | partitions                                   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+----------------------------------------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | test  | PART_201907,PART_202008,PART_202009,PART_MAX | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+------+-------------+-------+----------------------------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.003 sec)


첫번째 파티션 스캔을 방지하기 위해 빈 파티션을 만드는 workaround가 있네요.

참고하세요.


http://datacharmer.blogspot.com/2010/05/two-quick-performance-tips-with-mysql.html

정상규(pajama)님이 2020-07-31 09:43에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
31307blob 타입의 길이
기훈
2020-08-09
1547
31306MariaDB 암호화 오류 [3]
Maria
2020-08-05
4011
31305Restore Workspace 라는 창이 뜨면서 지금까지 했던 작업들이 안나옵니다 [1]
이효상
2020-08-04
1505
31304파티셔닝 조회 중 사용하지 않는 파티션을 조회하는 경우 [1]
정혜란
2020-07-30
1552
31303DB 복제 후 Reading table information for completion of table and column names... 메시지 [5]
이규영
2020-07-27
3560
31302select 해서 insert시 특정 컬럼에 값이 안들어가는 현상 문의드립니다.. [2]
최봉수
2020-07-24
1598
31301랜덤하게 가지고 오기 [1]
ㅠㅠ
2020-07-22
1518
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.047초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다