mysql數據庫的分區
mysql數據庫分區也叫mysql數據庫分表，即當一個表中數據庫非常大時，查詢速度非常慢影響效率，同時也增加了io的壓力，這樣我們就可以用分表的形式將一個表根據一定的規則將他分成多個表，主要的分表類型有range，list，hash，key等，詳細說明可以參考mysql5.1以上版本參考手冊
mysql分區支持主要在5.1以上版本，如果你的mysql是5.1以上版本，查看是否支持分區的方式是：
mysql> show variables like 'h%';
+-------------------------+-----------------------+
| Variable_name | Value |
+-------------------------+-----------------------+
| have_community_features | YES |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | YES |
| have_geometry | YES |
| have_innodb | NO |
| have_ndbcluster | NO |
| have_openssl | NO |
| have_partitioning | YES | -->>此項為yes說明支持表分區功能，如果為No則需要重新編譯
| have_query_cache | YES |
| have_rtree_keys | YES |
| have_ssl | NO |
| have_symlink | YES |
| hostname | localhost.localdomain |
+-------------------------+-----------------------+
15 rows in set (0.02 sec)
如果不支持，則需要下載mysql5.1及以上版本，重新編譯安裝，這里以mysql-5.1.30.tar.gz來編譯安裝
1.下載mysql-5.1.30.tar.gz源碼，
2.解壓
tar xvzf mysql-5.1.30.tar.gz
cd mysql-5.1.30
./configure --prefix=/usr/local/mysql \
--localstatedir=/data \
--datadir=/data \
--with-plugins=partition //添加分區功能
make && make install
cd /usr/local/mysql/bin
./mysql_install_db
./mysqld_safe &
3.安裝成功，則時mysql支持分區功能
4.分區簡單實例，以range分區類型為例：
create table users00 (
uid int unsigned not null auto_increment primary key,
name varchar(30) not null default "",
email varchar(30) not null default ""
)partition by range (uid)
( partition p0 values less than (10000) data directory = "/data00/" index directory = "/data00/",
partition p1 values less than (20000) data directory = "/data00/" index directory = "/data00/",
partition p2 values less than (30000) data directory = "/data00/" index directory = "/data00/",
partition p3 values less than maxvalue data directory = "/data00/" index directory = "/data00/"
);
該表分區類型為range，分為4個區，uid為0~10000在p0區，10001~20000在p1區 ...
查看users00表所在的數據目錄：
....
-rw-rw---- 1 root root 8620 Nov 22 14:47 users00.frm
-rw-rw---- 1 root root 32 Nov 22 14:47 users00.par
lrwxrwxrwx 1 root root 24 Nov 22 14:47 users00#P#p0.MYD -> /data00/users00#P#p0.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:47 users00#P#p0.MYI -> /data00/users00#P#p0.MYI
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p1.MYD -> /data00/users00#P#p1.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p1.MYI -> /data00/users00#P#p1.MYI
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p2.MYD -> /data00/users00#P#p2.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p2.MYI -> /data00/users00#P#p2.MYI
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p3.MYD -> /data00/users00#P#p3.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p3.MYI -> /data00/users00#P#p3.MYI
....
此時表數據已經達到了物理分區，這樣可以減少同一目錄的IO，同時由單表的3w記錄下降到了單表1w的數據量，這樣查詢起來效率更高
5.分區的修改和合并
以下是將p0分區再分割成2分區s0,s1的例子：
mysql> alter table users00 reorganize partition p0 into( partition s0 values less than (3999) data directory='/data00/' index directory='/data00/', partition s1 values less than (10000) data directory='/data00/' index directory='/data00/' );
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
分割成功，查看/data00/數據目錄：
.....
-rw-rw---- 1 root root 8620 Nov 22 15:01 users00.frm
-rw-rw---- 1 root root 40 Nov 22 15:01 users00.par
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p1.MYD -> /data00/users00#P#p1.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p1.MYI -> /data00/users00#P#p1.MYI
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p2.MYD -> /data00/users00#P#p2.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p2.MYI -> /data00/users00#P#p2.MYI
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p3.MYD -> /data00/users00#P#p3.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p3.MYI -> /data00/users00#P#p3.MYI
lrwxrwxrwx 1 root root 24 Nov 22 15:01 users00#P#s0.MYD -> /data00/users00#P#s0.MYD
lrwxrwxrwx 1 root root 24 Nov 22 15:01 users00#P#s0.MYI -> /data00/users00#P#s0.MYI
lrwxrwxrwx 1 root root 24 Nov 22 15:01 users00#P#s1.MYD -> /data00/users00#P#s1.MYD
lrwxrwxrwx 1 root root 24 Nov 22 15:01 users00#P#s1.MYI -> /data00/users00#P#s1.MYI
......
以下是合并s0，s1分區為p0分區
mysql> alter table users00 reorganize partition s0,s1 into( partition p0 values less than(10000) data directory="/data00/" index directory="/data00/");
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
此時s0，s1又合并成了p0分區
....
-rw-rw---- 1 root root 8620 Nov 22 15:04 users00.frm
-rw-rw---- 1 root root 32 Nov 22 15:04 users00.par
lrwxrwxrwx 1 root root 24 Nov 22 15:04 users00#P#p0.MYD -> /data00/users00#P#p0.MYD
lrwxrwxrwx 1 root root 24 Nov 22 15:04 users00#P#p0.MYI -> /data00/users00#P#p0.MYI
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p1.MYD -> /data00/users00#P#p1.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p1.MYI -> /data00/users00#P#p1.MYI
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p2.MYD -> /data00/users00#P#p2.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p2.MYI -> /data00/users00#P#p2.MYI
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p3.MYD -> /data00/users00#P#p3.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p3.MYI -> /data00/users00#P#p3.MYI
....
詳細的分區信息及功能介紹請參考mysql參考手冊，里面有非常詳細的介紹，我這里只是簡單的實現，很多地方都不夠詳細，只作為數據庫分表及數據分離的參考