mysql数据库partition分区的sql怎么写

发布网友 发布时间：2022-04-09 05:31

我来回答

共2个回答

懂视网 时间：2022-04-09 09:52

前言：当一个表里面存储的数据特别多的时候，比如单个.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字段来自动写入对应的分区数据文件的，从而实现了表的分区。

mysql的partition分区

标签：

热心网友 时间：2022-04-09 07:00