mysql分区怎么实现 mysql分区的数据类型

mysql分表，分区的区别和联系

什么是分表，从表面意思上看呢，就是把一张表分成N多个小表，具体请看mysql分表的3种方法

创新互联建站主要从事网站设计制作、做网站、网页设计、企业做网站、公司建网站等业务。立足成都服务矿区,十余年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:028-86922220

什么是分区，分区呢就是把一张表的数据分成N多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上，具体请参考mysql分区功能详细介绍，以及实例

二，mysql分表和分区有什么区别呢

1，实现方式上

a），mysql的分表是真正的分表，一张表分成很多表后，每一个小表都是完正的一张表，都对应三个文件，一个.MYD数据文件，.MYI索引文件，.frm表结构文件。

Sql代码

[root@BlackGhost test]# ls |grep user

alluser.MRG

alluser.frm

user1.MYD

user1.MYI

user1.frm

user2.MYD

user2.MYI

user2.frm

简单说明一下，上面的分表呢是利用了merge存储引擎（分表的一种），alluser是总表，下面有二个分表，user1，user2。他们二个都是独立的表，取数据的时候，我们可以通过总表来取。这里总表是没有.MYD,.MYI这二个文件的，也就是说，总表他不是一张表，没有数据，数据都放在分表里面。我们来看看.MRG到底是什么东西

Sql代码

[root@BlackGhost test]# cat alluser.MRG |more

user1

user2

#INSERT_METHOD=LAST

从上面我们可以看出，alluser.MRG里面就存了一些分表的关系，以及插入数据的方式。可以把总表理解成一个外壳，或者是联接池。

b），分区不一样，一张大表进行分区后，他还是一张表，不会变成二张表，但是他存放数据的区块变多了。

Sql代码

[root@BlackGhost test]# ls |grep aa

aa#P#p1.MYD

aa#P#p1.MYI

aa#P#p3.MYD

aa#P#p3.MYI

aa.frm

aa.par

从上面我们可以看出，aa这张表，分为二个区，p1和p3，本来是三个区，被我删了一个区。我们都知道一张表对应三个文件.MYD,.MYI,.frm。分区呢根据一定的规则把数据文件和索引文件进行了分割，还多出了一个.par文件，打开.par文件后你可以看出他记录了，这张表的分区信息，根分表中的.MRG有点像。分区后，还是一张，而不是多张表。

mysql 分区指定分区表数据存储路径，可以支持跨磁盘

INNODB引擎只支持DATA DIRECTORY，不支持INDEX DIRECTORY, 另外 目录要空目录权限归属mysql 组mysql 用户，命令

mkdir /data

chown mysql:mysql /data

主键必须包含分区字段，PRIMARY KEY (id,store_id)

CREATE TABLE orders_list2 (

id INT AUTO_INCREMENT,

customer_surname VARCHAR(30),

store_id INT,

salesperson_id INT,

order_date DATE,

note VARCHAR(500),

PRIMARY KEY (id,store_id),

INDEX idx (id)

) ENGINE = INNODB

PARTITION BY LIST(store_id) (

PARTITION p1

VALUES IN (1, 3, 4, 17)

DATA DIRECTORY = '/data/p1',

PARTITION p2

VALUES IN (2, 12, 14)

DATA DIRECTORY = '/data/p2',

PARTITION p3

VALUES IN (6, 8, 20)

DATA DIRECTORY = '/data/p3',

PARTITION p4

VALUES IN (5, 7, 9, 11, 16)

DATA DIRECTORY = '/data/p4',

PARTITION p5

VALUES IN (10, 13, 15, 18)

DATA DIRECTORY = '/data/p5'

);

分区表扩充

ALTER TABLE orders_list2  ADD PARTITION (PARTITION p6 VALUES IN  (21,22,23)

DATA DIRECTORY = '/var/lib/mysql/monitor/orders/p6'  ;

子分区

CREATE TABLE tb_sub_dir (id INT, purchased DATE)

ENGINE='MYISAM'

PARTITION BY RANGE( YEAR(purchased) )

SUBPARTITION BY HASH( TO_DAYS(purchased) ) (

    PARTITION p0 VALUES LESS THAN (1990) 

    (

        SUBPARTITION s0

            DATA DIRECTORY = '/data/data_sub1'

            INDEX DIRECTORY = '/data/idx_sub1',

        SUBPARTITION s1

            DATA DIRECTORY = '/data/data_sub1'

            INDEX DIRECTORY = '/data/idx_sub1'

    ),

    PARTITION p1 VALUES LESS THAN (2000) 

    (

        SUBPARTITION s2

            DATA DIRECTORY = '/data/data_sub2'

            INDEX DIRECTORY = '/data/idx_sub2',

        SUBPARTITION s3

            DATA DIRECTORY = '/data/data_sub2'

            INDEX DIRECTORY = '/data/idx_sub2'

    ),

    PARTITION p2 VALUES LESS THAN MAXVALUE 

    (

        SUBPARTITION s4

            DATA DIRECTORY = '/data/data_sub3'

            INDEX DIRECTORY = '/data/idx_sub3',

        SUBPARTITION s5

            DATA DIRECTORY = '/data/data_sub3'

            INDEX DIRECTORY = '/data/idx_sub3'

    )

);

日期字段区分

CREATE TABLE employees1 (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

hired DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code INT,

store_id INT

)

PARTITION BY RANGE ( YEAR(separated) ) (

PARTITION p0 VALUES LESS THAN (1991),

PARTITION p1 VALUES LESS THAN (1996),

PARTITION p2 VALUES LESS THAN (2001),

PARTITION p3 VALUES LESS THAN MAXVALUE

);

PHP mysql 实现hash分区的问题

当分片索引不是纯整型的字符串时，只接受整型的内置 hash 算法是无法使用的。为此，stringhash 按照用户定义的起点和终点去截取分片索引字段中的部分字符，根据当中每个字符的二进制 unicode 值换算出一个长整型数值，然后就直接调用内置 hash 算法求解分片路由：先求模得到逻辑分片号，再根据逻辑分片号直接映射到物理分片。

用户需要在 rule.xml 中定义 partitionLength[] 和 partitionCount[] 两个数组和 hashSlice 二元组。

在 DBLE 的启动阶段，点乘两个数组得到模数，也是逻辑分片的数量

并且根据两个数组的叉乘，得到各个逻辑分片到物理分片的映射表（物理分片数量由 partitionCount[] 数组的元素值之和）

此外根据 hashSlice 二元组，约定把分片索引值中的第 4 字符到第 5 字符（字符串以 0 开始编号，编号 3 到编号 4 等于第 4 字符到第 5 字符）字符串用于 “字符串-整型”的转换

在 DBLE 的运行过程中，用户访问使用这个算法的表时，WHERE 子句中的分片索引值会被提取出来，取当中的第 4 个字符到第 5 字符，送入下一步

设置一个初始值为 0 的累计值，逐个取字符，把累计值乘以 31，再把这个字符的 unicode 值当成长整型加入到累计值中，如此类推直至处理完截取出来的所有字符，此时的累计值就能够代表用户的分片索引值，完成了 “字符串-整型” 的转换

对上一步的累计值进行求模，得到逻辑分片号

再根据逻辑分片号，查映射表，直接得到物理分片号

与MyCat的类似分片算法对比

请点击输入图片描述

两种算法在string转化为int之后，和 hash 分区算法相同，区别也继承了 hash 算法的区别。

开发注意点

【分片索引】1. 必须是字符串

【分片索引】2. 最大物理分片配置方法是，让 partitionCount[] 数组和等于 2880

例如：

property name="partitionLength"1/propertyproperty name="partitionCount"2880/property

或

property name="partitionLength"1,1/propertyproperty name="partitionCount"1440,1440/property

【分片索引】3. 最小物理分片配置方法是，让 partitionCount[] 数组和等于 1

例如

property name="partitionLength"2880/propertyproperty name="partitionCount"1/property

【分片索引】4. partitionLength 和 partitionCount 被当做两个逗号分隔的一维数组，它们之间的点乘必须在 [1, 2880] 范围内

【分片索引】5. partitionLength 和 partitionCount 的配置对顺序敏感

property name="partitionLength"512,256/propertyproperty name="partitionCount"1,2/property

和

property name="partitionLength"256,512/propertyproperty name="partitionCount"2,1/property

是不同的分片结果

【分片索引】6. 分片索引字段长度小于用户指定的截取长度时，截取长度会安全减少到符合分片索引字段长度

【数据分布】1. 分片索引字段截取越长则越有利于数据均匀分布

【数据分布】2. 分片索引字段的内容重复率越低则越有利于数据均匀分布

运维注意点

【扩容】1. 预先过量分片，并且不改变 partitionCount 和 partitionLength 点乘结果，也不改变截取设置 hashSlice 时，可以避免数据再平衡，只需进行涉及数据的迁移

【扩容】2. 若需要改变 partitionCount 和 partitionLength 点乘结果或改变截取设置 hashSlice 时，需要数据再平衡

【缩容】1. 预先过量分片，并且不改变 partitionCount 和 partitionLength 点乘结果，也不改变截取设置 hashSlice 时，可以避免数据再平衡，只需进行涉及数据的迁移

【缩容】2. 若需要改变 partitionCount 和 partitionLength 点乘结果或改变截取设置 hashSlice 时，需要数据再平衡

配置注意点

【配置项】1. 在 rule.xml 中，可配置项为 property name="partitionLength"  、property name="partitionCount" 和 property name="hashSlice"

【配置项】2.在 rule.xml 中配置 property name="partitionLength" 标签

内容形式为：物理分片持有的虚拟分片数[,物理分片持有的虚拟分片数,...物理分片持有的虚拟分片数]

物理分片持有的虚拟分片数必须是整型，物理分片持有的虚拟分片数从左到右与同顺序的物理分片数对应，partitionLength 和partitionCount 的点乘结果必须在 [1, 2880] 范围内

【配置项】3. 在 rule.xml 中配置 property name="partitionCount" 标签

内容形式为：物理分片数[,物理分片数,...物理分片数]

其中物理分片数必须是整型，物理分片数按从左到右的顺序与同顺序的物理分片持有的虚拟分片数对应，物理分片的编号从左到右连续递进，partitionLength 和 partitionCount 的点乘结果必须在 [1, 2880] 范围内

【配置项】4. partitionLength 和 partitionCount 的语义是：持有partitionLength[i] 个虚拟分片的物理分片有 partitionCount[i] 个

例如

property name="partitionLength"512,256/propertyproperty name="partitionCount"1,2/property

语义是持有 512 个逻辑分片的物理分片有 1 个，紧随其后，持有 256 个逻辑分片的物理分片有 2 个

【配置项】5.partitionLength 和 partitionCount 都对书写顺序敏感，

例如

property name="partitionLength"512,256/propertyproperty name="partitionCount"1,2/property

分片结果是第一个物理分片持有头512个逻辑分片，第二个物理分片持有紧接着的256个逻辑分片，第三个物理分片持有最后256个逻辑分片，相对的

property name="partitionLength"256,512/propertyproperty name="partitionCount"2,1/property

分片结果则是第一个物理分片持有头 256 个逻辑分片，第二个物理分片持有紧接着的 256 个逻辑分片，第三个物理分片持有最后 512 个逻辑分片

【配置项】6.partitionLength[] 的元素全部为 1 时，这时候partitionCount 数组和等于 partitionLength 和 partitionCount 的点乘，物理分片和逻辑分片就会一一对应，该分片算法等效于直接取余

【配置项】7.在 rule.xml 中配置标签，从分片索引字段的第几个字符开始截取到第几个字符：

若希望从首字符开始截取 k 个字符（ k 为正整数），配置的内容形式可以为“ 0 : k ”、“ k ”或“ : k ”；

若希望从末字符开始截取 k 个字符（ k 为正整数），则配置的内容形式可以为“ -k : 0 ”、“ -k ”或“ -k : ”；

若希望从头第 m 个字符起算截取 n 个字符（ m 和 n 都是正整数），则先计算出 i = m - 1 和 j = i + n - 1，配置的内容形式为“ i : j ”；

若希望从尾第 m 个字符起算截取从尾算起的 n 个字符（ m 和 n 都是正整数），则先计算出 i = -m + n - 1，配置的内容形式可以为“ -m : i ”；

若希望不截取，则配置的内容形式可以为“ 0 : 0 ”、“ 0 : ”、“ : 0 ”或 “ : ”

mysql分区表如何实现类似全文索引的效果

1.添加PRIMARY KEY（主键索引）

mysqlALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引)

mysqlALTER TABLE `table_name` ADD UNIQUE (

`column`

)

3.添加INDEX(普通索引)

mysqlALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

mysqlALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引

mysqlALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )