MySQL表分区技术

MySQL表分区技术

MySQL有4种分区类型:
  1.RANGE 分区 - 连续区间的分区 - 基于属于一个给定连续区间的列值，把多行分配给分区；
  2.LIST 分区  - 离散区间的分区 - 类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择；
  3.HASH 分区  - 平均分区       - 基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算，这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式；
  4.KEY 分区   - Key分区        - 类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数；

分区能做什么：
  1.逻辑数据分割
  2.提高单一的写和读应用速度
  3.提高分区范围读查询的速度
  4.分割数据能够有多个不同的物理文件路径
  5.高效的保存历史数据
  6.一个表上的约束检查
  7.不同的主从服务器分区策略，例如master按Hash分区，slave按range分区

分区的限制：
  1.最大分区数目不能超过1024
  2.如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内
  3.不支持外键
  4.不支持全文索引（fulltext）

什么时候使用分区：
  1.海量数据表
  2.历史表快速的查询，可以采用ARCHIVE+PARTITION的方式
  3.数据表索引大于服务器有效内存
  4.对于大表，特别是索引远远大于服务器有效内存时，可以不用索引，此时分区效率会更有效

使用分区体验总结：
  1.分区和未分区占用文件空间大致相同(数据和索引文件)
  2.如果查询语句中有未建立索引字段，分区时间远远优于未分区时间
  3.如果查询语句中字段建立了索引，分区和未分区的差别缩小，分区略优于未分区。
  4.对于大数据量，建议使用分区功能。
  5.去除不必要的字段
  6.根据手册，增加myisam_max_sort_file_size 会增加分区性能

普通表与分区表的性能比较实验：
1.创建普通表，并测试
  create table tb1(
   id int unsigned not null auto_increment comment '主键',
   username varchar(32) comment '用户名',
   sid int unsigned comment '学号',
   age int unsigned comment '年龄',
   gender char(1) default 'M' comment '性别：F女；M男',
   birthday date comment '出生日期',
   address varchar(32) comment '地址',
   createtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后添加或修改时间',
   primary key(id) comment '主键'
  )engine=MyISAM default charset=utf8;

  # 导入测试数据
  mysql> insert  into `tb1`(`id`,`username`,`sid`,`age`,`gender`,`birthday`,`address`,`createtime`) values (1,'0.6151317913226148',14,0,'M','2010-11-2','0.183316911741455','2013-12-03 17:13:19'),(2,'0.8548431665216043',10,1,'M','2010-1-1','0.11118550515844068','2013-12-03 17:13:42'),(3,'0.3855390412598281',8,16,'F','2012-1-1','0.2879664397686407','2013-12-03 17:13:42'),(4,'0.9976026853524174',1,12,'M','2010-1-1','0.7664709442914193','2013-12-03 17:13:43'),(5,'0.9490667553144198',6,7,'M','2013-1-1','0.5742602921782622','2013-12-03 17:13:43'),(6,'0.7240875807815116',13,6,'M','2013-1-1','0.8871417510203474','2013-12-03 17:13:44'),(7,'0.4876410490718121',11,8,'F','2010-1-1','0.774544955021278','2013-12-03 17:13:44'),(8,'0.6097938098104613',10,15,'M','2010-1-1','0.8104457611315379','2013-12-03 17:13:45'),(9,'0.6603598116527869',13,7,'F','2010-4-1','0.24476273287531242','2013-12-03 17:13:45'),(10,'0.11012482373986843',12,15,'F','2011-1-1','0.3075187460589397','2013-12-03 17:13:45'),(11,'0.28367782038047706',7,12,'M','2010-1-1','0.6531565567973596','2013-12-03 17:13:46'),(12,'0.38138968905563436',14,11,'M','2013-9-1','0.1236885722015878','2013-12-03 17:13:46'),(13,'0.83876791674529',12,11,'M','2010-1-1','0.5195071860398233','2013-12-03 17:13:47'),(14,'0.8048386516094568',6,18,'M','2010-10-1','0.1721984848121167','2013-12-03 17:13:47'),(15,'0.11946426715651924',1,0,'F','2013-1-1','0.9840062428116857','2013-12-03 17:13:47'),(16,'0.7843208758014448',14,9,'F','2011-1-1','0.5660720361080691','2013-12-03 17:13:48'),(17,'0.345462781698874',0,2,'F','2010-1-1','0.458210738802525','2013-12-03 17:13:48'),(18,'0.9637512042780884',6,6,'M','2010-5-1','0.3144565059938063','2013-12-03 17:13:49'),(19,'0.5841857712568582',14,2,'F','2011-5-1','0.7435197343523798','2013-12-03 17:13:49'),(20,'0.31188001605857163',4,14,'M','2013-1-1','0.5562999188493005','2013-12-03 17:13:50'),(21,'0.6558060577658993',9,1,'F','2010-1-1','0.5857817042486572','2013-12-03 17:13:50'),(22,'0.6792045344404919',9,3,'M','2014-1-1','0.8628391519774116','2013-12-03 17:13:51'),(23,'0.8468726555322043',9,13,'M','2010-1-1','0.5055191055922947','2013-12-03 17:13:51'),(24,'0.46176157934755235',11,11,'M','2010-3-1','0.5030899294680822','2013-12-03 17:13:51'),(25,'0.7875446395832529',6,15,'M','2011-1-1','0.6128062760576665','2013-12-03 17:13:52'),(26,'0.7251314713853705',11,15,'F','2010-1-1','0.44227642234626824','2013-12-03 17:13:52'),(27,'0.9289869004199159',4,16,'M','2010-11-1','0.06351252558036942','2013-12-03 17:13:53'),(28,'0.9068656832975016',5,19,'M','2011-1-1','0.9604140855003279','2013-12-03 17:13:54'),(29,'0.8069984585111947',2,6,'F','2012-4-1','0.27752477608390597','2013-12-03 17:13:54'),(30,'0.3443589437234764',13,8,'M','2015-1-1','0.3974652927345273','2013-12-03 17:13:55'),(31,'0.7482505792269954',8,9,'M','2010-1-1','0.8510965666278233','2013-12-03 17:13:56'),(32,'0.7568822547391824',3,17,'M','2010-1-1','0.7314416633280382','2013-12-03 17:13:56'),(33,'0.0023244796342444416',12,1,'M','2010-5-1','0.9456766461447595','2013-12-03 17:13:58'),(34,'0.4898419021534192',9,11,'F','2010-2-1','0.12032291956275973','2013-12-03 17:13:58'),(35,'0.827497609730342',11,8,'M','2010-6-1','0.6709610937824111','2013-12-03 17:13:59'),(36,'0.1544940845617038',11,6,'M','2010-1-1','0.39350670426479234','2013-12-03 17:13:59'),(37,'0.9641726379880426',9,6,'M','2010-7-1','0.6249589879298201','2013-12-03 17:14:00'),(38,'0.19720200188197382',1,19,'M','2010-1-1','0.48689783596144787','2013-12-03 17:14:00'),(39,'0.5423109899669056',3,12,'F','2010-8-1','0.38428773394253823','2013-12-03 17:14:01'),(40,'0.03931542769010684',0,2,'F','2010-1-1','0.37197605089468516','2013-12-03 17:14:01');

  # 统计全表总数据条数：
  mysql> select count(*) from tb1;
  +----------+
  | count(*) |
  +----------+
  |       40 |
  +----------+

  # 统计sid=7的数据条数：
  mysql> select count(*) from tb1 where sid=6;
  +----------+
  | count(*) |
  +----------+
  |        4 |
  +----------+

  # 解析sql查询语句
  mysql> desc select * from tb1 where sid=6\G;
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: tb1
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 40 --------------------------> 扫描了全表40行
          Extra: Using where
  1 row in set (0.00 sec)

2.【Range分区】创建分区表(range单列分区)，并测试
  create table tb2(
   id int unsigned not null auto_increment comment '主键',
   username varchar(32) comment '用户名',
   sid int unsigned comment '学号',
   age int unsigned comment '年龄',
   gender char(1) default 'M' comment '性别：F女；M男',
   birthday date comment '出生日期',
   address varchar(32) comment '地址',
   createtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后添加或修改时间',
   primary key(id,sid) comment '分区字段sid必须包含在主键字段内'
  )engine=MyISAM default charset=utf8
  partition by range columns(sid)
  (
   partition p01 values less than(5) comment 'sid<5',
   partition p02 values less than(10) comment 'sid<10',
   partition p03 values less than(15) comment 'sid<10'
  );

  # 导入测试数据(将tb1表数据导入)
  mysql> insert into tb2 select * from tb1;

  # 后期还可以继续添加新分区(分区时没有使用MAXVALUE是可以的，如果使用了MAXVALUE是不能再添加新分区了)
  mysql> alter table tb2 add partition (partition p04 values less than(20) comment 'sid<20');

  # 统计全表总数据条数：
  mysql> select count(*) from tb2;
  +----------+
  | count(*) |
  +----------+
  |       40 |
  +----------+

  # 统计sid=7的总条数
  mysql> select count(*) from tb1 where sid=6;
  +----------+
  | count(*) |
  +----------+
  |        4 |
  +----------+
  1 row in set (0.00 sec)

  # 解析sql查询语句
  mysql> desc partitions select * from tb2 where sid=6\G;
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: tb2
     partitions: p02 ---------------------------> 扫描p02分区
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 13  ---------------------------> 扫描了13行
          Extra: Using where
  1 row in set (0.00 sec)

  总结: 有分区的表同条件查询是扫描表行数减少了，为什么是13行？

  # 查看一下分区详情
  mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows
         from information_schema.partitions
         where table_schema = schema() and table_name='tb2';
         +------+-------+----------+------------+
         | part | expr  | descr    | table_rows |
         +------+-------+----------+------------+
         | p01  | `sid` | 5        |         10 |
         | p02  | `sid` | 10       |         13 |
         | p03  | `sid` | MAXVALUE |         17 |
         +------+-------+----------+------------+

  原因：当给出条件sid=6时，DBMS自动就只在p02分区查询了，此分区目前存储13条数据。当数据量超级大时，分区可以减少查询扫描的行数，利于优化。

3.【Range分区】创建分区表(range多列分区)，并测试
  create table tb3(
   id int unsigned not null auto_increment comment '主键',
   username varchar(32) comment '用户名',
   sid int unsigned comment '学号',
   age int unsigned comment '年龄',
   gender char(1) default 'M' comment '性别：F女；M男',
   birthday date comment '出生日期',
   address varchar(32) comment '地址',
   createtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后添加或修改时间',
   primary key(id,sid,age) comment '分区字段sid,age必须包含在主键字段内'
  )engine=MyISAM default charset=utf8
  partition by range columns(sid,age)
  (
   partition p01 values less than(5,8) comment 'sid<5,age<8',
   partition p02 values less than(10,15) comment 'sid<10,age<15',
   partition p03 values less than(maxvalue,maxvalue) comment '其它值'
  );

  # 注：一旦使用MAXVALUE值以后就无法再添加新分区了

  # 导入测试数据(将tb1表数据导入)
  mysql> insert into tb3 select * from tb1;

  # 查看一下分区详情
  mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows
         from information_schema.partitions
         where table_schema = schema() and table_name='tb3';
         +------+-------------+-------------------+------------+
         | part | expr        | descr             | table_rows |
         +------+-------------+-------------------+------------+
         | p01  | `sid`,`age` | 5,8               |         10 |
         | p02  | `sid`,`age` | 10,15             |         14 |
         | p03  | `sid`,`age` | MAXVALUE,MAXVALUE |         16 |
         +------+-------------+-------------------+------------+

  mysql> desc partitions select * from tb3 where sid=4 and age=6;   //扫描p01分区 10行
  mysql> desc partitions select * from tb3 where sid=5 and age=6;   //扫描p01分区 10行
  mysql> desc partitions select * from tb3 where sid=5 and age=8;   //扫描p02分区 14行
  mysql> desc partitions select * from tb3 where sid=6 and age=8;   //扫描p02分区 14行
  mysql> desc partitions select * from tb3 where sid=10 and age=14; //扫描p02分区 14行
  mysql> desc partitions select * from tb3 where sid=10 and age=15; //扫描p03分区 16行
  mysql> desc partitions select * from tb3 where sid=10 and age=16; //扫描p03分区 16行

  当有多个列时，比较规则：
  先比较第1个参数，如果第1参数小于对应的第一个列值，第2个参数无需则直接进入该分区；如果第1个参数等于对应的第一个列值，再比较第二个列值决定扫描哪个分区；
  A.(sid=4,age=6) < (5,8) 由于第1个参数sid<4,第2个参数无需比较，直接扫描p01分区；

  B.(sid=5,age=6) < (5,8) 由于第1个参数sid=5,比较第2个参数age<8,扫描p01分区；

  C.(sid=5,age=8) !< (5,8) 而是 (sid=5,age=8) < (10,15)  扫描p02分区；

  D.(sid=6,age=8) < (10,15) 由于第1个参数sid<10,第2个参数无需比较，直接扫描p02分区；

  E.(sid=10,age=14) < (10,15) 由于第1个参数sid=10,比较第2个参数age<15；扫描p02分区；

  F.(sid=10,age=15) !< (10,15) 而是 (sid=10,age=15) < (maxvalue,maxvalue) 直接扫描p03分区；

  G.(sid=10,age=16) !< (10,15) 而是 (sid=10,age=16) < (maxvalue,maxvalue) 直接扫描p03分区；

4.【Range分区】创建分区表(range分区，在日期列上分区)，MySQL5.5开始按日期分区已经不需使用to_days()函数转换了
  create table tb4(
   id int unsigned not null auto_increment comment '主键',
   username varchar(32) comment '用户名',
   sid int unsigned comment '学号',
   age int unsigned comment '年龄',
   gender char(1) default 'M' comment '性别：F女；M男',
   birthday date comment '出生日期',
   address varchar(32) comment '地址',
   createtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后添加或修改时间',
   primary key(id,gender,birthday) comment '分区字段 gender,birthday 必须包含在主键字段内'
  )engine=MyISAM default charset=utf8
  partition by range columns(gender,birthday)
  (
   partition p01 values less than('F','2011-1-1') comment 'F birthday<2011-1-1',
   partition p02 values less than('F','2012-1-1') comment 'F birthday<2012-1-1',
   partition p03 values less than('F','2013-1-1') comment 'F birthday<2013-1-1',
   partition p04 values less than('F',maxvalue) comment 'F其它值',
   partition p05 values less than('M','2011-1-1') comment 'M birthday<2011-1-1',
   partition p06 values less than('M','2012-1-1') comment 'M birthday<2012-1-1',
   partition p07 values less than('M','2013-1-1') comment 'M birthday<2013-1-1',
   partition p08 values less than('M',maxvalue) comment 'M其它值',
   partition p09 values less than(maxvalue,maxvalue) comment 'FM其它值'
  );

  # 导入测试数据(将tb1表数据导入)
  mysql> insert into tb4 select * from tb1;

  # 查看一下分区详情
  mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows
         from information_schema.partitions
         where table_schema = schema() and table_name='tb4';
         +------+---------------------+-------------------+------------+
         | part | expr                | descr             | table_rows |
         +------+---------------------+-------------------+------------+
         | p01  | `gender`,`birthday` | 'F','2011-1-1'    |          8 |
         | p02  | `gender`,`birthday` | 'F','2012-1-1'    |          3 |
         | p03  | `gender`,`birthday` | 'F','2013-1-1'    |          2 |
         | p04  | `gender`,`birthday` | 'F',MAXVALUE      |          1 |
         | p05  | `gender`,`birthday` | 'M','2011-1-1'    |         18 |
         | p06  | `gender`,`birthday` | 'M','2012-1-1'    |          2 |
         | p07  | `gender`,`birthday` | 'M','2013-1-1'    |          0 |
         | p08  | `gender`,`birthday` | 'M',MAXVALUE      |          6 |
         | p09  | `gender`,`birthday` | MAXVALUE,MAXVALUE |          0 |
         +------+---------------------+-------------------+------------+

  mysql> desc partitions select * from tb4 where gender='F' and birthday = '2010-4-1';        //扫描p01分区 8行
  mysql> desc partitions select * from tb4 where gender='F' and birthday < '2010-10-2';       //扫描p01分区 8行
  mysql> desc partitions select * from tb4 where gender='F' and birthday < date('2010-10-2'); //扫描p01分区 8行
  mysql> desc partitions select * from tb4 where gender='F' and birthday between '2011-1-1' and '2011-4-1';  //扫描p02分区 3行

5.【List分区】MySQL5.5开始已经支持按字符分区了
  # 费用表
  CREATE TABLE expenses (
    expense_date DATE NOT NULL comment '费用日期',
    category VARCHAR(30) comment '分类',
    amount DECIMAL (10,3) comment '金额'
  )ENGINE=MyISAM DEFAULT CHARSET=utf8;

  # 导入测试数据
  mysql> insert into expenses values(curdate(),'lodging',round(rand()*1000,4)),(curdate(),'food',round(rand()*1000,4)),(curdate(),'flights',round(rand()*1000,4)),(curdate(),'ground transportation',round(rand()*1000,4)),(curdate(),'leisure',round(rand()*1000,4)),(curdate(),'customer entertainment',round(rand()*1000,4)),(curdate(),'communications',round(rand()*1000,4)),(curdate(),'fees',round(rand()*1000,4)),(curdate(),'lodging',round(rand()*1000,4)),(curdate(),'ground transportation',round(rand()*1000,4)),(curdate(),'customer entertainment',round(rand()*1000,4)),(curdate(),'fees',round(rand()*1000,4)),(curdate(),'food',round(rand()*1000,4)),(curdate(),'fees',round(rand()*1000,4)),(curdate(),'communications',round(rand()*1000,4)),(curdate(),'ground transportation',round(rand()*1000,4)),(curdate(),'leisure',round(rand()*1000,4)),(curdate(),'flights',round(rand()*1000,4)),(curdate(),'leisure',round(rand()*1000,4)),(curdate(),'food',round(rand()*1000,4)),(curdate(),'lodging',round(rand()*1000,4)),(curdate(),'food',round(rand()*1000,4)),(curdate(),'lodging',round(rand()*1000,4)),(curdate(),'flights',round(rand()*1000,4)),(curdate(),'leisure',round(rand()*1000,4)),(curdate(),'communications',round(rand()*1000,4)),(curdate(),'fees',round(rand()*1000,4)),(curdate(),'fees',round(rand()*1000,4));

  # 已有数据的表也可以修改为分区表(注：必须注意分区字段的范围值要能涵盖表内全部的现有范围数据)
  ALTER TABLE expenses
  PARTITION BY LIST COLUMNS (category)
  (
    PARTITION p01 VALUES IN ('lodging', 'food') comment '分区01：房租 食品为',
    PARTITION p02 VALUES IN ('flights', 'ground transportation') comment '分区02：机票 陆地运费',
    PARTITION p03 VALUES IN ('leisure', 'customer entertainment') comment '分区03：游玩 客户招待',
    PARTITION p04 VALUES IN ('communications') comment '分区04：通信',
    PARTITION p05 VALUES IN ('fees') comment '分区05：服务费'
  );

  # 后期还可以继续添加新分区
  mysql> alter table expenses add partition (partition p06 values in ('medical') comment '医疗保健');

  # 查看一下分区详情
  mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows
         from information_schema.partitions
         where table_schema = schema() and table_name='expenses';
         +------+------------+------------------------------------+------------+
         | part | expr       | descr                              | table_rows |
         +------+------------+------------------------------------+------------+
         | p01  | `category` | 'lodging','food'                   |          8 |
         | p02  | `category` | 'flights','ground transportation'  |          6 |
         | p03  | `category` | 'leisure','customer entertainment' |          6 |
         | p04  | `category` | 'communications'                   |          3 |
         | p05  | `category` | 'fees'                             |          5 |
         +------+------------+------------------------------------+------------+

  mysql> desc partitions select * from expenses where category = 'food';             //扫描p01分区 8行
  mysql> desc partitions select * from expenses where category = 'flights';          //扫描p02分区 6行
  mysql> desc partitions select * from expenses where category in('food','flights'); //扫描p01和p02分区 共14行

6.【Hash分区】- 按store_id商店id随机存入分区
  CREATE TABLE hash_employees (
    id INT NOT NULL auto_increment,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT,
    primary key(id,store_id)
  )ENGINE=MyISAM DEFAULT CHARSET=utf8
  PARTITION BY HASH(store_id)
  PARTITIONS 4;

  # 插入测试数据
  mysql> insert into hash_employees values(null, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, floor(rand()*10));

7.【KSY分区】- 类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且 MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
  CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
  )
  PARTITION BY LINEAR KEY (col1)
  PARTITIONS 3;  

  # 插入测试数据
  mysql> insert into tk values(floor(rand()*10), 'mysql', curdate());

8.【子分区】- 子分区是分区表中每个分区的再次分割
   几点要注意的语法项：
   ·每个分区必须有相同数量的子分区。
   ·如果在一个分区表上的任何分区上使用SUBPARTITION 来明确定义任何子分区，那么就必须定义所有的子分区。
   ·每个SUBPARTITION 子句必须包括 (至少)子分区的一个名字。否则，你可能要对该子分区设置任何你所需要的选项，或者允许该子分区对那些选项采用其默认的设置。
   ·在每个分区内，子分区的名字必须是唯一的，但是在整个表中，没有必要保持唯一。例如，下面的 CREATE TABLE 语句是有效的：

  CREATE TABLE ts (id INT, purchased DATE)
   PARTITION BY RANGE(YEAR(purchased))
    SUBPARTITION BY HASH(TO_DAYS(purchased))
    (
      PARTITION p0 VALUES LESS THAN (1990)
      (
        SUBPARTITION s0,
        SUBPARTITION s1
      ),
      PARTITION p1 VALUES LESS THAN (2000)
      (
        SUBPARTITION s0,
        SUBPARTITION s1
      ),
      PARTITION p2 VALUES LESS THAN MAXVALUE
      (
        SUBPARTITION s0,
        SUBPARTITION s1
      )
  );

9.【Innodb引擎分区】
  # 要对Innodb引擎表进行分区，前提是必须使用独立表空间的Innodb表，在MySQL配置文件my.cnf中[mysqld]区块添加：
  innodb_file_per_table = 1

  # 创建表
  create table tb2_innodb(
   id int unsigned not null auto_increment comment '主键',
   username varchar(32) comment '用户名',
   sid int unsigned comment '学号',
   age int unsigned comment '年龄',
   gender char(1) default 'M' comment '性别：F女；M男',
   birthday date comment '出生日期',
   address varchar(32) comment '地址',
   createtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后添加或修改时间',
   primary key(id,sid) comment '分区字段sid必须包含在主键字段内'
  )engine=Innodb default charset=utf8
  partition by range columns(sid)
  (
   partition p01 values less than(5) comment 'sid<5',
   partition p02 values less than(10) comment 'sid<10',
   partition p03 values less than(15) comment 'sid<10'
  );

  # 导入测试数据(将tb1表数据导入)
  mysql> insert into tb2_innodb select * from tb1;

  # 实验略......

10.我们甚至可以建立一个小于1天的临时分区，即一天内按时间段进行分区存储，如：
   CREATE TABLE t2 (
     dt datetime
   )ENGINE=MyISAM DEFAULT CHARSET=utf8
   PARTITION BY RANGE (to_seconds(dt))
   (
     PARTITION p01 VALUES LESS THAN (to_seconds('2009-11-30 08:00:00')) ,
     PARTITION p02 VALUES LESS THAN (to_seconds('2009-11-30 16:00:00')) ,
     PARTITION p03 VALUES LESS THAN (to_seconds('2009-12-01 00:00:00')) ,
     PARTITION p04 VALUES LESS THAN (to_seconds('2009-12-01 08:00:00')) ,
     PARTITION p05 VALUES LESS THAN (to_seconds('2009-12-01 16:00:00')) ,
     PARTITION p06 VALUES LESS THAN (MAXVALUE)
   ); 

附：MySQL可用的分区函数
DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
DATEDIFF()
EXTRACT()
HOUR()
MICROSECOND()
MINUTE()
MOD()
MONTH()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
FROM_DAYS()
WEEKDAY()
YEAR()
YEARWEEK()

参考文档：
http://database.51cto.com/art/201002/184392.htm
http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html
http://lehsyh.iteye.com/blog/732719
http://www.cnblogs.com/acpp/archive/2010/08/09/1795464.html
http://database.51cto.com/art/200811/98728.htm
http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html