MySQL パーティショニングテスト

### MySQLバージョン
```powershell
mysql  Ver 14.14 Distrib 5.7.12, for Linux (x86_64) using  EditLine wrapper
```

---

### テーブル構造（パーティション無し）

```sql
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
```

---

### テーブル構造（パーティション有り）

```sql
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でテストデータ挿入用のプログラムを作りました
 
```sql
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)

```

---

### パーティションの確認

```sql
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を集計

```sql
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回目はクエリーキャッシュが効いて、パーティションされていなくても高速になる

* パーティションされたテーブルはクエリキャッシュが無効になっている

```sql
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)
```

---

### テスト 月をまたいで集計

```sql

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)

```