LoginSignup
22

More than 5 years have passed since last update.

DATE PartitionにTO_DAYS()を使うのはやめよう

Last updated at Posted at 2014-12-04

まとめ

PARTITION BY RANGE COLUMNS()を使えばよい

悪いテーブル定義例

CREATE TABLE `presents` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `present_id` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`,`created_at`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS(created_at))
(PARTITION p201402 VALUES LESS THAN (735658) ENGINE = InnoDB,
 -- 省略
 PARTITION p201409 VALUES LESS THAN (735872) ENGINE = InnoDB,
 PARTITION p201410 VALUES LESS THAN (735903) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

クエリ

EXPLAIN PARTITIONS
SELECT  `presents`.* FROM `presents`
WHERE `presents`.`user_id` = 1067510
AND (created_at BETWEEN "2014-08-25  16:11:07" AND '2014-09-25 16:11:07')
;

刈り込み結果

p201402,p201408,p201409
  • REORGANIZEするとき、 上記クエリがロックされてしまう!!

何故p201402が対象なのか

MySQLのパーティショニングでは、評価式の結果がNULLになった場合、
先頭のパーティションにレコードが格納されます。
TO_DAYS()はzero date(月や日が0の日付)などを入力すると
NULLを出力するのですが、WHERE句の条件ではzero dateが含まれるかどうか
までは分かりませんので、評価式の結果がNULLとなる可能性が否定できません。
そのため、評価式の結果がNULLとなるレコードが格納されている先頭の
パーティションもSELECTの対象になるのです。

良いテーブル定義

CREATE TABLE `presents` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `present_id` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`,`created_at`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(created_at)
(PARTITION p201402 VALUES LESS THAN ('2014-03-01') ENGINE = InnoDB,
 -- 省略
 PARTITION p201409 VALUES LESS THAN ('2014-10-01') ENGINE = InnoDB,
 PARTITION p201410 VALUES LESS THAN ('2014-11-01') ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
22