Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

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

More than 3 years have passed since last update.

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

by taroshin
1 / 8

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
  • まったく同じデータを2テーブルに用意
  • 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)

fullspeed
独自のアドテクノロジーにより、DSP、ソーシャルメディア、SEO、リスティング、アフィリエイトを展開
https://www.fullspeed.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away