原文：http://blog.chinaunix.net/u/29134/showart_493359.html
Mysql版本说明：
Server version: 5.1.23a-maria-alpha-log MySQL Community Server [Maria] (GPL)
一、讲在前面
注意：
1、ALTER TABLE也可以用于对带分区的表进行重新分区，所以不能在建表之后再用ALTER TABLE语法。
2、如果你表中有KEY。用来分区的字段必须是KEY的一部份。
3、现在的分区属于水平分区。（垂直分区我们可以自己模拟，这个以后再写）
mysql> use t_girl
Database changed
先建立一个普通表
mysql> create table category( cid int unsigned not null auto_increment primary key, cname varchar(64) not null, parent_id int not null);
Query OK, 0 rows affected (0.00 sec)
mysql> create table parent(parent_id int not null auto_increment primary key,pname varchar(64) not null);
Query OK, 0 rows affected (0.00 sec)
分区表
mysql> create table category_part( cid int unsigned not null auto_increment,cname varchar(64) not null,parent_id int not null,primary key (cid,parent_id))
partition by list(parent_id)(
partition p1 values in (1,2,3,6,9),
partition p2 values in (4,5,10,22,23),
partition p3 values in (7,8,11,12,13),
partition p4 values in (14,15,16,17,20),
partition p5 values in (18,19,21,24,25)
);
Query OK, 0 rows affected (0.01 sec)
插入数据部分省略。。。
建立索引。
mysql> create index f_parent_id on category(parent_id);
Query OK, 2048000 rows affected (17.61 sec)
Records: 2048000 Duplicates: 0 Warnings: 0
mysql> show index from category;
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| category | 0 | PRIMARY | 1 | cid | A | 2048000 | NULL | NULL | | BTREE | |
| category | 1 | f_parent_id | 1 | parent_id | A | 25 | NULL | NULL | | BTREE | |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
mysql> create index f_parent_id on category_part(parent_id);
Query OK, 2048000 rows affected (18.57 sec)
Records: 2048000 Duplicates: 0 Warnings: 0
mysql> show index from category_part;
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| category_part | 0 | PRIMARY | 1 | cid | A | 2048000 | NULL | NULL | | BTREE | |
| category_part | 0 | PRIMARY | 2 | parent_id | A | 2048000 | NULL | NULL | | BTREE | |
| category_part | 1 | f_parent_id | 1 | parent_id | A | 318 | NULL | NULL | | BTREE | |
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.01 sec)
mysql> select count(*) from category;
+----------+
| count(*) |
+----------+
| 2048000 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from category_part;
+----------+
| count(*) |
+----------+
| 2048000 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from parent;
+----------+
| count(*) |
+----------+
| 25 |
+----------+
1 row in set (0.00 sec)
|