mysql的partition分区

前言:当一个表里面存储的数据特别多的时候,比如单个.myd数据都已经达到10G了的话,必然导致读取的效率很低,这个时候我们可以采用把数据分到几张表里面来解决问题。
方式一:通过业务逻辑根据数据的大小通过id%10这种来分成 user1,user2,user3等这样的,但是这样会有很多问题我们需要维护这样一个hash关系,

而且每次读取数据和写入数据的时候还要去判断取那张表,这个是我们通过程序去识别写表和读表的。
方式二:mysql可以通过partition进行分区,这种分区显示给我们的数据依然都是在一个数据表里面的,不影响我们读取查询数据,

而是mysql内部的文件机制实现了将数据存储在不同的数据文件里,这样的好处是mysql自动将对应的数据分到的不同的.myd文件里面去

了大大降低了文件的大小,将数据分摊了,很好的提高了效率。

 

今天分析的是方式二利用mysql的partition进行分区

该列子数据库名:test
mysql安装路径:/data/local/mysql/
mysql的partition分区又分为两种:按照范围分区(range),按照散列分区(list)

一:按照范围实现分区

mysql> create table topic(

    -> tid int(4) primary key auto_increment,

    -> title char(20) not null default '')

    -> engine=myisam default charset=utf8

    -> partition by range(tid)(

    -> partition test0 values less than(10),

    -> partition test1 values less than(20),

    -> partition test2 values less than(maxvalue));

Query OK, 0 rows affected (0.05 sec)


进入test数据库的文件目录查看生成的文件
ls  -all  /data/local/mysql/data/test/
特别需要注意文件类型为.MYD的这里存储的是数据

-rw-rw---- 1 mysql mysql  8590 May 10 01:27 topic.frm

-rw-rw---- 1 mysql mysql    40 May 10 01:27 topic.par

-rw-rw---- 1 mysql mysql     0 May 10 20:13 topic#P#test0.MYD

-rw-rw---- 1 mysql mysql  1024 May 10 20:13 topic#P#test0.MYI

-rw-rw---- 1 mysql mysql     0 May 10 20:13 topic#P#test1.MYD

-rw-rw---- 1 mysql mysql  1024 May 10 20:13 topic#P#test1.MYI

-rw-rw---- 1 mysql mysql     0 May 10 20:13 topic#P#test2.MYD

-rw-rw---- 1 mysql mysql  1024 May 10 20:13 topic#P#test2.MYI

我们加入一些数据看看效果

mysql> insert into topic(`title`) values('a');

Query OK, 1 row affected (0.00 sec)


-rw-rw---- 1 mysql mysql  8660 May  6 23:52 t.frm

-rw-rw---- 1 mysql mysql   192 May  6 23:52 t.MYD

-rw-rw---- 1 mysql mysql  2048 May  6 23:52 t.MYI

-rw-rw---- 1 mysql mysql  8590 May 10 01:27 topic.frm

-rw-rw---- 1 mysql mysql    40 May 10 01:27 topic.par

-rw-rw---- 1 mysql mysql    65 May 10 20:16 topic#P#test0.MYD

-rw-rw---- 1 mysql mysql  2048 May 10 20:16 topic#P#test0.MYI

-rw-rw---- 1 mysql mysql     0 May 10 20:13 topic#P#test1.MYD

-rw-rw---- 1 mysql mysql  1024 May 10 20:13 topic#P#test1.MYI

-rw-rw---- 1 mysql mysql     0 May 10 20:13 topic#P#test2.MYD

-rw-rw---- 1 mysql mysql  1024 May 10 20:13 topic#P#test2.MYI

发现 topic#P#test0.MYD文件大小增加了说明数据写入到了 topic#P#test0.MYD也就是写入到了分区我们之前创建的test0分区里面去了

我们现在再插入一条数据看下情况

mysql> insert into topic(`tid`,`title`) values(11,'h');

Query OK, 1 row affected (0.00 sec)

-rw-rw---- 1 mysql mysql  8590 May 10 01:27 topic.frm

-rw-rw---- 1 mysql mysql    40 May 10 01:27 topic.par

-rw-rw---- 1 mysql mysql    65 May 10 20:16 topic#P#test0.MYD

-rw-rw---- 1 mysql mysql  2048 May 10 20:16 topic#P#test0.MYI

-rw-rw---- 1 mysql mysql    65 May 10 20:18 topic#P#test1.MYD

-rw-rw---- 1 mysql mysql  2048 May 10 20:18 topic#P#test1.MYI

-rw-rw---- 1 mysql mysql     0 May 10 20:13 topic#P#test2.MYD

-rw-rw---- 1 mysql mysql  1024 May 10 20:13 topic#P#test2.MYI
插入了一条id为11的数据发现topic#P#test1.MYD的文件大小增加了说明数据写入到了分区为test1里面去了

我们查询下数据表里的数据看一下

 

mysql> select * from topic;

+-----+-------+

| tid | title |

+-----+-------+

|   1 | a     |

|  11 | h     |

+-----+-------+

2 rows in set (0.00 sec)

说明:mysql的partition按照范围(range)分区,是以某个字段的id(为int类型)的 范围来写入到对应范围的分区里面去的。

 

二:按照散列的点进行分区
    

mysql> create table area(

    -> uid int(10),

    -> uname char(6),

    -> aid int)

    -> engine=myisam charset utf8

    -> partition by list(aid)(

    -> partition hb values in (1),

    -> partition hn values in (2),

    -> partition gd values in (3),

    -> partition gx values in (4));

Query OK, 0 rows affected (0.01 sec)

查看生成的文件信息
ls  -all  /data/local/mysql/data/test/

 

-rw-rw---- 1 mysql mysql  8618 May 10 19:52 area.frm

-rw-rw---- 1 mysql mysql    32 May 10 19:52 area.par

-rw-rw---- 1 mysql mysql     0 May 10 19:52 area#P#gd.MYD

-rw-rw---- 1 mysql mysql  1024 May 10 19:52 area#P#gd.MYI

-rw-rw---- 1 mysql mysql     0 May 10 19:52 area#P#gx.MYD

-rw-rw---- 1 mysql mysql  1024 May 10 19:52 area#P#gx.MYI

-rw-rw---- 1 mysql mysql     0 May 10 19:52 area#P#hb.MYD

-rw-rw---- 1 mysql mysql  1024 May 10 19:52 area#P#hb.MYI

-rw-rw---- 1 mysql mysql     0 May 10 19:52 area#P#hn.MYD

-rw-rw---- 1 mysql mysql  1024 May 10 19:52 area#P#hn.MYI

我们插入一条来自湖北的用户信息

mysql> insert into `area` (`uname`,`aid`) values('东子',1);

Query OK, 1 row affected (0.00 sec)

我们再来查看信息发现area#P#hb.MYD的文件写入了数据

-rw-rw---- 1 mysql mysql  8618 May 10 19:52 area.frm

-rw-rw---- 1 mysql mysql    32 May 10 19:52 area.par

-rw-rw---- 1 mysql mysql     0 May 10 19:52 area#P#gd.MYD

-rw-rw---- 1 mysql mysql  1024 May 10 19:52 area#P#gd.MYI

-rw-rw---- 1 mysql mysql     0 May 10 19:52 area#P#gx.MYD

-rw-rw---- 1 mysql mysql  1024 May 10 19:52 area#P#gx.MYI

-rw-rw---- 1 mysql mysql    27 May 10 20:03 area#P#hb.MYD

-rw-rw---- 1 mysql mysql  1024 May 10 20:03 area#P#hb.MYI

-rw-rw---- 1 mysql mysql     0 May 10 19:52 area#P#hn.MYD

-rw-rw---- 1 mysql mysql  1024 May 10 19:52 area#P#hn.MYI

我们再插入一条湖南人的信息

mysql> insert into `area` (`uname`,`aid`) values('lxm',2);

Query OK, 1 row affected (0.00 sec)

 

插入后我们发现area#P#hn.MYD文件的大小增加了说明写入了数据

-rw-rw---- 1 mysql mysql  8618 May 10 19:52 area.frm

-rw-rw---- 1 mysql mysql    32 May 10 19:52 area.par

-rw-rw---- 1 mysql mysql     0 May 10 19:52 area#P#gd.MYD

-rw-rw---- 1 mysql mysql  1024 May 10 19:52 area#P#gd.MYI

-rw-rw---- 1 mysql mysql     0 May 10 19:52 area#P#gx.MYD

-rw-rw---- 1 mysql mysql  1024 May 10 19:52 area#P#gx.MYI

-rw-rw---- 1 mysql mysql    27 May 10 20:03 area#P#hb.MYD

-rw-rw---- 1 mysql mysql  1024 May 10 20:03 area#P#hb.MYI

-rw-rw---- 1 mysql mysql    27 May 10 20:06 area#P#hn.MYD

-rw-rw---- 1 mysql mysql  1024 May 10 20:06 area#P#hn.MYI

查看一下表里面的数据

mysql> select * from area;

+------+--------+------+

| uid  | uname  | aid  |

+------+--------+------+

| NULL | 东子   |    1 |

| NULL | lxm    |    2 |

+------+--------+------+

2 rows in set (0.00 sec)
说明:按照散列的点进行分区,是根据插入信息的关联的一个id字段来自动写入对应的分区数据文件的,从而实现了表的分区。

 

posted @ 2016-07-06 19:22  巴八灵  阅读(6213)  评论(1编辑  收藏  举报