Mysql/PARTITION BY RANGE

PARTITION BY RANGEとは

カラムをPARTITION BY RANGE対応にすると、1つのパーティション内の条件のsqlの速度向上が期待できる。

注意：innodb限定

公式：https://dev.mysql.com/doc/refman/8.0/ja/partitioning-range.html 例

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

一ヶ月単位だと

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
PARTITION p201503 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-04-01 00:00:00') ),
PARTITION p201504 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-05-01 00:00:00') ),
PARTITION p201505 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-06-01 00:00:00') ),
PARTITION p201506 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-07-01 00:00:00') ),
PARTITION p201507 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-08-01 00:00:00') ),
PARTITION pmax VALUES LESS THAN (MAXVALUE)
);