使用 mysql partition table sample

Mysql server version:

mysql> show variables like '%version%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| protocol_version         | 10                           |
| version                  | 5.1.46-community             |
| version_comment          | MySQL Community Server (GPL) |
| version_compile_machine  | x86_64                       |
| version_compile_os       | unknown-linux-gnu            |
+--------------------------+------------------------------+
9 rows in set (0.01 sec)

Check things…

mysql> show variables like '%inno%';
+-----------------------------------------+------------------------+
| Variable_name                           | Value                  |
+-----------------------------------------+------------------------+
| have_innodb                             | YES                    |

mysql> show variables like '%part%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |

my schema:

--
CREATE TABLE `tbl_ts_ip` (
  `xdate` int(8) NOT NULL default 0,
  `xhour` varchar(2) NOT NULL default '00',
  `tag1` varchar(100) NOT NULL default '',
  `tag2` varchar(300) NOT NULL default '',
  `val` int(10) unsigned  NOT NULL default '0',
  KEY `ndx_xdate` (`xdate`),
  KEY `ndx_xhour` (`xhour`),
  KEY `ndx_tag1` (`tag1`),
  KEY `ndx_tag2` (`tag2`)
) ENGINE=InnoDB
partition by range(xdate) (
  partition p201003 values less than (20100400),
  partition p201004 values less than (20100500),
  partition p201005 values less than (20100600),
  partition p201006 values less than (20100700)
);

testing

--
insert into tbl_ts_ip values (20100301,'01','tag1','tag2',1);
insert into tbl_ts_ip values (20100401,'01','tag1','tag2',1);
insert into tbl_ts_ip values (20100501,'01','tag1','tag2',1);
insert into tbl_ts_ip values (20100601,'01','tag1','tag2',1);
insert into tbl_ts_ip values (20100701,'01','tag1','tag2',1);
--

result:

--
mysql> insert into tbl_ts_ip values (20100301,1,'tag1','tag2',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_ts_ip values (20100401,1,'tag1','tag2',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_ts_ip values (20100501,1,'tag1','tag2',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_ts_ip values (20100601,1,'tag1','tag2',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_ts_ip values (20100701,1,'tag1','tag2',1);
ERROR 1526 (HY000): Table has no partition for value 20100701
mysql> select * from tbl_ts_ip;
+----------+-------+------+------+-----+
| xdate    | xhour | tag1 | tag2 | val |
+----------+-------+------+------+-----+
| 20100301 |     1 | tag1 | tag2 |   1 |
| 20100401 |     1 | tag1 | tag2 |   1 |
| 20100501 |     1 | tag1 | tag2 |   1 |
| 20100601 |     1 | tag1 | tag2 |   1 |
+----------+-------+------+------+-----+
4 rows in set (0.00 sec)
--

data files :

[root@ stat]# ls -l
total 676
-rw-rw---- 1 mysql mysql     65 May 18 17:02 db.opt
-rw-rw---- 1 mysql mysql   8682 May 18 17:07 tbl_ts_ip.frm
-rw-rw---- 1 mysql mysql     52 May 18 17:07 tbl_ts_ip.par
-rw-rw---- 1 mysql mysql 163840 May 18 17:09 tbl_ts_ip#P#p201003.ibd
-rw-rw---- 1 mysql mysql 163840 May 18 17:09 tbl_ts_ip#P#p201004.ibd
-rw-rw---- 1 mysql mysql 163840 May 18 17:09 tbl_ts_ip#P#p201005.ibd
-rw-rw---- 1 mysql mysql 163840 May 18 17:09 tbl_ts_ip#P#p201006.ibd

add partitions:

--
alter table tbl_ts_ip
add partition (
  partition p201007 values less than (20100800),
  partition p201008 values less than (20100900),
  partition p201009 values less than (20101000),
  partition p201010 values less than (20101100),
  partition p201011 values less than (20101200),
  partition p201012 values less than (20110100)
);
--

explain partitions:

--
mysql> explain partitions select * from tbl_ts_ip where xdate=20100501;
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table     | partitions | type | possible_keys | key       | key_len | ref   | rows | Extra |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+-------+
|  1 | SIMPLE      | tbl_ts_ip | p201005    | ref  | ndx_xdate     | ndx_xdate | 4       | const |    1 |       |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+-------+
--
--
--
mysql> explain partitions select * from tbl_ts_ip where xdate>20100101 and xdate<20100501 and xhour=1 and tag1='x1' and tag2='x2';
+----+-------------+-----------+-------------------------+------+---------------------------------------+-----------+---------+-------+------+-------------+
| id | select_type | table     | partitions              | type | possible_keys                         | key       | key_len | ref   | rows | Extra       |
+----+-------------+-----------+-------------------------+------+---------------------------------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | tbl_ts_ip | p201003,p201004,p201005 | ref  | ndx_xdate,ndx_xhour,ndx_tag1,ndx_tag2 | ndx_xhour | 4       | const |    3 | Using where |
+----+-------------+-----------+-------------------------+------+---------------------------------------+-----------+---------+-------+------+-------------+
--

######################

sample schema:

create table t1 (

d date

) engine=innodb partition by range ( to_days(d) ) (

partition p1 values less than (to_days(‘1999-01-01’)),

partition p2 values less than (to_days(‘2000-01-01’))

);

— drop old partition

alter table tablename

drop partition p1;

######

--
CREATE TABLE `tbl_ts_ip` (
  `xdate` int(8) NOT NULL default 0,
  `xhour` int(2) NOT NULL default 0,
  `tag1` varchar(100) NOT NULL default '',
  `tag2` varchar(300) NOT NULL default '',
  `val` int(10) unsigned  NOT NULL default '0',
  KEY `ndx_xdate` (`xdate`),
  KEY `ndx_xhour` (`xhour`),
  KEY `ndx_tag1` (`tag1`),
  KEY `ndx_tag2` (`tag2`)
) ENGINE=InnoDB
partition by range(xdate) (
  partition p200901 values less than (20090200),
  partition p200902 values less than (20090300),
  partition p200903 values less than (20090400),
  partition p200904 values less than (20090500),
  partition p200905 values less than (20090600),
  partition p200906 values less than (20090700),
  partition p200907 values less than (20090800),
  partition p200908 values less than (20090900),
  partition p200909 values less than (20091000),
  partition p200910 values less than (20091100),
  partition p200911 values less than (20091200),
  partition p200912 values less than (20100100),
  partition p201001 values less than (20100200),
  partition p201002 values less than (20100300),
  partition p201003 values less than (20100400),
  partition p201004 values less than (20100500),
  partition p201005 values less than (20100600),
  partition p201006 values less than (20100700),
  partition p201007 values less than (20100800),
  partition p201008 values less than (20100900),
  partition p201009 values less than (20101000),
  partition p201010 values less than (20101100),
  partition p201011 values less than (20101200),
  partition p201012 values less than (20110100),
  partition p201101 values less than (20110200),
  partition p201102 values less than (20110300),
  partition p201103 values less than (20110400),
  partition p201104 values less than (20110500),
  partition p201105 values less than (20110600),
  partition p201106 values less than (20110700),
  partition p201107 values less than (20110800),
  partition p201108 values less than (20110900),
  partition p201109 values less than (20111000),
  partition p201110 values less than (20111100),
  partition p201111 values less than (20111200),
  partition p201112 values less than (20120100)
);
--

###

一天一個 partition …

--
CREATE TABLE `tbl_ts_ip` (
  `xdate` date NOT NULL DEFAULT '0000-00-00',
  `xhour` int(2) NOT NULL DEFAULT '0',
  `tag1` varchar(100) NOT NULL DEFAULT '',
  `tag2` varchar(300) NOT NULL DEFAULT '',
  `val` int(10) unsigned NOT NULL DEFAULT '0',
  KEY `ndx_xdate` (`xdate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE ( to_days(xdate) )
(PARTITION p0 VALUES LESS THAN ( to_days('2005-1-1') ) );
--
--
alter table tbl_ts_ip
add partition (
  partition p20100401 values less than ( to_days('20100401')+1 )
);

--

###

產生 alter table … add partition 的 code

$start_date = mktime( 0,0,0,4,1,2010);
$day_count = 365;

for ( $i=0; $i < $day_count; $i++ ) {
  echo "
    alter table tbl_ts_ip
    add partition (
      partition p".date("Ymd",$start_date)." values less than ( to_days('".date("Ymd",$start_date)."')+1 )
    );
  ";
  echo "
    alter table tbl_ts_member
    add partition (
      partition p".date("Ymd",$start_date)." values less than ( to_days('".date("Ymd",$start_date)."')+1 )
    );
  ";
  $start_date += 86400;
}

###

--
mysql> explain partitions select * from tbl_ts_ip where xdate>20100510 and xdate<20100517 and tag2='114.39.237.77'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_ts_ip
   partitions: p0,p20100511,p20100512,p20100513,p20100514,p20100515,p20100516
         type: ALL
possible_keys: ndx_xdate
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 37706064
        Extra: Using where
1 row in set (0.01 sec)

mysql> select * from tbl_ts_ip where xdate>20100510 and xdate<20100517 and tag2='114.39.237.77';
+------------+-------+--------------------+---------------+-----+
| xdate      | xhour | tag1               | tag2          | val |
+------------+-------+--------------------+---------------+-----+
| 2010-05-16 |    23 | 37043600380A380C3E | 114.39.237.77 |   2 |
+------------+-------+--------------------+---------------+-----+
1 row in set (24.26 sec)
--

###

Oracle partition table

MySQL partition table

Oracle 說 partition 的好處是…

Reduce the possibility of data corruption in multiple partitions
Back up and recover each partition independently
Control the mapping of partitions to disk drives (important for balancing I/O load)
Improve manageability, availability, and performance

Partitioning is transparent to existing applications and standard DML statements run against partitioned tables. However, an application can be programmed to take advantage of partitioning by using partition-extended table or index names in DML.

You can use SQL*Loader and the import and export utilities to load or unload data stored in partitioned tables. These utilities are all partition and subpartition aware.

two main advantages are:

Increased performance – during scan operations, the MySQL optimizer knows what partitions contain the data that will satisfy a particular query and will access only those necessary partitions during query execution. For example, a million row table may be broken up into ten different partitions in range style so that each partition contains 100,000 rows. If a query is issued that only needs data from one of the partitions, and a table scan operation is necessary, only 100,000 rows will be accessed instead of a million. Obviously, it is much quicker for MySQL to sample 100,000 rows than one million so the query will complete much sooner. The same benefit is derived should index access be possible as local partitioned indexes are created for partitioned tables. Finally, it is possible to stripe a partitioned table across different physical drives by specifying different file system/directory paths for specific partitions. This allows physical I/O contention to be reduced when multiple partitions are accessed at the same time.
Simplified data management – partitioning allows a DBA to have more control over how data is managed inside of the database. By intelligently creating partitions, a DBA can simplify how certain data operations are performed. For example, a DBA can drop specific partitions in a partitioned table while the remaining partitions remain intact (as opposed to crafting a fragmentation-producing mass delete operation for the whole table). Further, partitions are automatically maintained by MySQL so the DBA doesn’t have to manually separate and maintain a horizontal partitioning scheme for a table. For example, a DBA can create a history table that holds data for customers that are partitioned across various year ranges, and have those partitioned automatically enforced by the database server with no DBA intervention being necessary.

to make single inserts and selects faster
to make range selects faster
to help split the data across different paths
to store historical data efficiently

ref: MySQL partitions tutorial ,

http://www.slideshare.net/datacharmer/mysql-partitions-tutorialdownload: http://dn.monster.tw/my/docs/mysql/partitionstutorial-090426130303-phpapp02.pdf

—

用 mysql partition table 要注意的事:

Check resources!

InnoDB
check disk space (uses more than MyISAM)check CPU usage
MyISAM
use 2 file handles per partitionIf you use more than one partitioned table, count total file handles
Archive
check CPU usagecheck memory usage