MySQLバージョン
mysql Ver 14.14 Distrib 5.7.12, for Linux (x86_64) using EditLine wrapper
テーブル構造（パーティション無し）
CREATE TABLE `disable_partitions` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`cost` double DEFAULT NULL,
`imp` bigint(20) DEFAULT NULL,
`click` bigint(20) DEFAULT NULL,
`cv` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_date` (`date`)
) ENGINE=MyISAM AUTO_INCREMENT=62091507 DEFAULT CHARSET=utf8
テーブル構造（パーティション有り）
CREATE TABLE `enable_partitions` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`cost` double DEFAULT NULL,
`imp` bigint(20) DEFAULT NULL,
`click` bigint(20) DEFAULT NULL,
`cv` int(11) DEFAULT NULL,
PRIMARY KEY (`id`,`date`),
KEY `idx_date` (`date`)
) ENGINE=MyISAM AUTO_INCREMENT=62091507 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (TO_DAYS(date))
(PARTITION p201201 VALUES LESS THAN (734899) ENGINE = MyISAM,
PARTITION p201202 VALUES LESS THAN (734928) ENGINE = MyISAM,
PARTITION p201203 VALUES LESS THAN (734959) ENGINE = MyISAM,
PARTITION p201204 VALUES LESS THAN (734989) ENGINE = MyISAM,
PARTITION p201205 VALUES LESS THAN (735020) ENGINE = MyISAM,
PARTITION p201206 VALUES LESS THAN (735050) ENGINE = MyISAM,
PARTITION p201207 VALUES LESS THAN (735081) ENGINE = MyISAM,
PARTITION p201208 VALUES LESS THAN (735112) ENGINE = MyISAM,
PARTITION p201209 VALUES LESS THAN (735142) ENGINE = MyISAM,
PARTITION p201210 VALUES LESS THAN (735173) ENGINE = MyISAM,
PARTITION p201211 VALUES LESS THAN (735203) ENGINE = MyISAM,
PARTITION p201212 VALUES LESS THAN (735234) ENGINE = MyISAM,
PARTITION p201301 VALUES LESS THAN (735265) ENGINE = MyISAM,
PARTITION p201302 VALUES LESS THAN (735293) ENGINE = MyISAM,
PARTITION p201303 VALUES LESS THAN (735324) ENGINE = MyISAM,
PARTITION p201304 VALUES LESS THAN (735354) ENGINE = MyISAM,
PARTITION p201305 VALUES LESS THAN (735385) ENGINE = MyISAM,
PARTITION p201306 VALUES LESS THAN (735415) ENGINE = MyISAM,
PARTITION p201307 VALUES LESS THAN (735446) ENGINE = MyISAM,
PARTITION p201308 VALUES LESS THAN (735477) ENGINE = MyISAM,
PARTITION p201309 VALUES LESS THAN (735507) ENGINE = MyISAM,
PARTITION p201310 VALUES LESS THAN (735538) ENGINE = MyISAM,
PARTITION p201311 VALUES LESS THAN (735568) ENGINE = MyISAM,
PARTITION p201312 VALUES LESS THAN (735599) ENGINE = MyISAM,
PARTITION p201401 VALUES LESS THAN (735630) ENGINE = MyISAM,
PARTITION p201402 VALUES LESS THAN (735658) ENGINE = MyISAM,
PARTITION p201403 VALUES LESS THAN (735689) ENGINE = MyISAM,
PARTITION p201404 VALUES LESS THAN (735719) ENGINE = MyISAM,
PARTITION p201405 VALUES LESS THAN (735750) ENGINE = MyISAM,
PARTITION p201406 VALUES LESS THAN (735780) ENGINE = MyISAM,
PARTITION p201407 VALUES LESS THAN (735811) ENGINE = MyISAM,
PARTITION p201408 VALUES LESS THAN (735842) ENGINE = MyISAM,
PARTITION p201409 VALUES LESS THAN (735872) ENGINE = MyISAM,
PARTITION p201410 VALUES LESS THAN (735903) ENGINE = MyISAM,
PARTITION p201411 VALUES LESS THAN (735933) ENGINE = MyISAM,
PARTITION p201412 VALUES LESS THAN (735964) ENGINE = MyISAM,
PARTITION p201501 VALUES LESS THAN (735995) ENGINE = MyISAM,
PARTITION p201502 VALUES LESS THAN (736023) ENGINE = MyISAM,
PARTITION p201503 VALUES LESS THAN (736054) ENGINE = MyISAM,
PARTITION p201504 VALUES LESS THAN (736084) ENGINE = MyISAM,
PARTITION p201505 VALUES LESS THAN (736115) ENGINE = MyISAM,
PARTITION p201506 VALUES LESS THAN (736145) ENGINE = MyISAM,
PARTITION p201507 VALUES LESS THAN (736176) ENGINE = MyISAM,
PARTITION p201508 VALUES LESS THAN (736207) ENGINE = MyISAM,
PARTITION p201509 VALUES LESS THAN (736237) ENGINE = MyISAM,
PARTITION p201510 VALUES LESS THAN (736268) ENGINE = MyISAM,
PARTITION p201511 VALUES LESS THAN (736298) ENGINE = MyISAM,
PARTITION p201512 VALUES LESS THAN (736329) ENGINE = MyISAM,
PARTITION p201601 VALUES LESS THAN (736360) ENGINE = MyISAM,
PARTITION p201602 VALUES LESS THAN (736389) ENGINE = MyISAM,
PARTITION p201603 VALUES LESS THAN (736420) ENGINE = MyISAM,
PARTITION p201604 VALUES LESS THAN (736450) ENGINE = MyISAM,
PARTITION p201605 VALUES LESS THAN (736481) ENGINE = MyISAM,
PARTITION p201606 VALUES LESS THAN (736511) ENGINE = MyISAM,
PARTITION p201607 VALUES LESS THAN (736542) ENGINE = MyISAM,
PARTITION p201608 VALUES LESS THAN (736573) ENGINE = MyISAM,
PARTITION p201609 VALUES LESS THAN (736603) ENGINE = MyISAM,
PARTITION p201610 VALUES LESS THAN (736634) ENGINE = MyISAM,
PARTITION p201611 VALUES LESS THAN (736664) ENGINE = MyISAM,
PARTITION p201612 VALUES LESS THAN (736695) ENGINE = MyISAM)
テストデータ挿入
- 合計62,091,506件のデータを挿入
- dateカラムにはランダムで挿入し、日付のふり幅は5年分としました
- 2011//07/28 ~ 2016/07/28
- まったく同じデータを２テーブルに用意
- PHPでテストデータ挿入用のプログラムを作りました
mysql> select count(*) from disable_partitions;
+----------+
| count(*) |
+----------+
| 62091506 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from enable_partitions;
+----------+
| count(*) |
+----------+
| 62091506 |
+----------+
1 row in set (0.00 sec)
mysql> select * from enable_partitions order by id limit 10;
+----+------------+------+------+-------+------+
| id | date | cost | imp | click | cv |
+----+------------+------+------+-------+------+
| 1 | 2015-05-22 | 595 | 244 | 106 | 843 |
| 2 | 2013-05-16 | 862 | 508 | 595 | 987 |
| 3 | 2013-05-06 | 80 | 558 | 882 | 20 |
| 4 | 2012-04-30 | 826 | 155 | 492 | 765 |
| 5 | 2015-06-27 | 289 | 35 | 429 | 376 |
| 6 | 2011-09-04 | 161 | 144 | 411 | 810 |
| 7 | 2012-01-02 | 753 | 3 | 159 | 648 |
| 8 | 2013-08-03 | 393 | 739 | 105 | 799 |
| 9 | 2013-07-27 | 632 | 413 | 159 | 751 |
| 10 | 2014-02-10 | 500 | 396 | 648 | 8 |
+----+------------+------+------+-------+------+
10 rows in set (0.00 sec)
mysql> select * from disable_partitions order by id limit 10;
+----+------------+------+------+-------+------+
| id | date | cost | imp | click | cv |
+----+------------+------+------+-------+------+
| 1 | 2015-05-22 | 595 | 244 | 106 | 843 |
| 2 | 2013-05-16 | 862 | 508 | 595 | 987 |
| 3 | 2013-05-06 | 80 | 558 | 882 | 20 |
| 4 | 2012-04-30 | 826 | 155 | 492 | 765 |
| 5 | 2015-06-27 | 289 | 35 | 429 | 376 |
| 6 | 2011-09-04 | 161 | 144 | 411 | 810 |
| 7 | 2012-01-02 | 753 | 3 | 159 | 648 |
| 8 | 2013-08-03 | 393 | 739 | 105 | 799 |
| 9 | 2013-07-27 | 632 | 413 | 159 | 751 |
| 10 | 2014-02-10 | 500 | 396 | 648 | 8 |
+----+------------+------+------+-------+------+
10 rows in set (0.04 sec)
パーティションの確認
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_NAME = 'enable_partitions';
+--------------+-------------------+----------------+----------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+--------------+-------------------+----------------+----------------------------+------------+
| sample | enable_partitions | p201201 | 1 | 6357387 |
| sample | enable_partitions | p201202 | 2 | 887509 |
| sample | enable_partitions | p201203 | 3 | 961434 |
| sample | enable_partitions | p201204 | 4 | 1003516 |
| sample | enable_partitions | p201205 | 5 | 1150614 |
| sample | enable_partitions | p201206 | 6 | 1022272 |
| sample | enable_partitions | p201207 | 7 | 1098798 |
| sample | enable_partitions | p201208 | 8 | 1107408 |
| sample | enable_partitions | p201209 | 9 | 993469 |
| sample | enable_partitions | p201210 | 10 | 1066097 |
| sample | enable_partitions | p201211 | 11 | 949994 |
| sample | enable_partitions | p201212 | 12 | 1144131 |
| sample | enable_partitions | p201301 | 13 | 1376811 |
| sample | enable_partitions | p201302 | 14 | 800656 |
| sample | enable_partitions | p201303 | 15 | 1229001 |
| sample | enable_partitions | p201304 | 16 | 1100059 |
| sample | enable_partitions | p201305 | 17 | 1047743 |
| sample | enable_partitions | p201306 | 18 | 963829 |
| sample | enable_partitions | p201307 | 19 | 963368 |
| sample | enable_partitions | p201308 | 20 | 1079019 |
| sample | enable_partitions | p201309 | 21 | 1146258 |
| sample | enable_partitions | p201310 | 22 | 1072845 |
| sample | enable_partitions | p201311 | 23 | 973791 |
| sample | enable_partitions | p201312 | 24 | 1061375 |
| sample | enable_partitions | p201401 | 25 | 977939 |
| sample | enable_partitions | p201402 | 26 | 960100 |
| sample | enable_partitions | p201403 | 27 | 952252 |
| sample | enable_partitions | p201404 | 28 | 994816 |
| sample | enable_partitions | p201405 | 29 | 1092858 |
| sample | enable_partitions | p201406 | 30 | 1035720 |
| sample | enable_partitions | p201407 | 31 | 1185655 |
| sample | enable_partitions | p201408 | 32 | 1047969 |
| sample | enable_partitions | p201409 | 33 | 1099022 |
| sample | enable_partitions | p201410 | 34 | 1004201 |
| sample | enable_partitions | p201411 | 35 | 905609 |
| sample | enable_partitions | p201412 | 36 | 990872 |
| sample | enable_partitions | p201501 | 37 | 1060028 |
| sample | enable_partitions | p201502 | 38 | 947257 |
| sample | enable_partitions | p201503 | 39 | 949262 |
| sample | enable_partitions | p201504 | 40 | 907387 |
| sample | enable_partitions | p201505 | 41 | 1009986 |
| sample | enable_partitions | p201506 | 42 | 1023597 |
| sample | enable_partitions | p201507 | 43 | 1072154 |
| sample | enable_partitions | p201508 | 44 | 1093643 |
| sample | enable_partitions | p201509 | 45 | 898128 |
| sample | enable_partitions | p201510 | 46 | 1260871 |
| sample | enable_partitions | p201511 | 47 | 1016620 |
| sample | enable_partitions | p201512 | 48 | 1101602 |
| sample | enable_partitions | p201601 | 49 | 954951 |
| sample | enable_partitions | p201602 | 50 | 970302 |
| sample | enable_partitions | p201603 | 51 | 930857 |
| sample | enable_partitions | p201604 | 52 | 1101062 |
| sample | enable_partitions | p201605 | 53 | 1036356 |
| sample | enable_partitions | p201606 | 54 | 917072 |
| sample | enable_partitions | p201607 | 55 | 1035974 |
| sample | enable_partitions | p201608 | 56 | 0 |
| sample | enable_partitions | p201609 | 57 | 0 |
| sample | enable_partitions | p201610 | 58 | 0 |
| sample | enable_partitions | p201611 | 59 | 0 |
| sample | enable_partitions | p201612 | 60 | 0 |
+--------------+-------------------+----------------+----------------------------+------------+
60 rows in set (0.01 sec)
テスト 対象月のimpを集計
mysql> select sum(imp) from disable_partitions
where date >= '2016/07/01' and date <= '2016/07/31';
+-----------+
| sum(imp) |
+-----------+
| 577149713 |
+-----------+
1 row in set (1 min 15.51 sec)
mysql> select sum(imp) from enable_partitions
where date >= '2016/07/01' and date <= '2016/07/31';
+-----------+
| sum(imp) |
+-----------+
| 577149713 |
+-----------+
1 row in set (0.26 sec)
ちなみに2回目はクエリーキャッシュが効いて、パーティションされていなくても高速になる
- パーティションされたテーブルはクエリキャッシュが無効になっている
mysql> select sum(imp) from disable_partitions
where date >= '2016/07/01' and date <= '2016/07/31';
+-----------+
| sum(imp) |
+-----------+
| 577149713 |
+-----------+
1 row in set (3.88 sec)
mysql> select sum(imp) from enable_partitions
where date >= '2016/07/01' and date <= '2016/07/31';
+-----------+
| sum(imp) |
+-----------+
| 577149713 |
+-----------+
1 row in set (0.22 sec)
テスト 月をまたいで集計
mysql> select sum(imp) from disable_partitions
where date >= '2016/02/01' and date <= '2016/03/31';
+-----------+
| sum(imp) |
+-----------+
| 961035428 |
+-----------+
1 row in set (24.84 sec)
mysql> select sum(imp) from enable_partitions
where date >= '2016/02/01' and date <= '2016/03/31';
+-----------+
| sum(imp) |
+-----------+
| 961035428 |
+-----------+
1 row in set (1.01 sec)
mysql> select sum(imp) from disable_partitions
where date >= '2015/01/01' and date <= '2015/12/31';
+------------+
| sum(imp) |
+------------+
| 6097954159 |
+------------+
1 row in set (7.58 sec)
mysql> select sum(imp) from enable_partitions
where date >= '2015/01/01' and date <= '2015/12/31';
+------------+
| sum(imp) |
+------------+
| 6097954159 |
+------------+
1 row in set (3.34 sec)