[MySQL管理] mysql数据库的分表（简单实例） -

zyzzsky

浏览: 127439 次

最近访客更多访客>>

jauncehome

msj_0529

yuxinxuhui

zackzhang

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

[MySQL管理] mysql数据库的分表（简单实例）

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参考手册，里面有非常详细的介绍，
我这里只是简单的实现，很多地方都不够详细，只作为数据库分表及数据分离的参考

分享到：