mysql數(shù)據(jù)庫的分區(qū)
mysql數(shù)據(jù)庫分區(qū)也叫mysql數(shù)據(jù)庫分表，即當(dāng)一個(gè)表中數(shù)據(jù)庫非常大時(shí)，查詢速度非常慢影響效率，同時(shí)也增加了io的壓力，這樣我們就可以用分表的形式將一個(gè)表根據(jù)一定的規(guī)則將他分成多個(gè)表，主要的分表類型有range，list，hash，key等，詳細(xì)說明可以參考mysql5.1以上版本參考手冊(cè)
mysql分區(qū)支持主要在5.1以上版本，如果你的mysql是5.1以上版本，查看是否支持分區(qū)的方式是：
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 | -->>此項(xiàng)為yes說明支持表分區(qū)功能，如果為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 //添加分區(qū)功能
make && make install
cd /usr/local/mysql/bin
./mysql_install_db
./mysqld_safe &
3.安裝成功，則時(shí)mysql支持分區(qū)功能
4.分區(qū)簡單實(shí)例，以range分區(qū)類型為例：
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/"
);
該表分區(qū)類型為range，分為4個(gè)區(qū)，uid為0~10000在p0區(qū)，10001~20000在p1區(qū) ...
查看users00表所在的數(shù)據(jù)目錄：
....
-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
....
此時(shí)表數(shù)據(jù)已經(jīng)達(dá)到了物理分區(qū)，這樣可以減少同一目錄的IO，同時(shí)由單表的3w記錄下降到了單表1w的數(shù)據(jù)量，這樣查詢起來效率更高
5.分區(qū)的修改和合并
以下是將p0分區(qū)再分割成2分區(qū)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/數(shù)據(jù)目錄：
.....
-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分區(qū)為p0分區(qū)
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
此時(shí)s0，s1又合并成了p0分區(qū)
....
-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
....
詳細(xì)的分區(qū)信息及功能介紹請(qǐng)參考mysql參考手冊(cè)，里面有非常詳細(xì)的介紹，我這里只是簡單的實(shí)現(xiàn)，很多地方都不夠詳細(xì)，只作為數(shù)據(jù)庫分表及數(shù)據(jù)分離的參考