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: 0MySQL>
分割成功,查看/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