MySQL按字符串hash分区_mysql分区理论「建议收藏」

查看mysql安装的引擎

mysql>show engines;

查看mysql安装的插件(这里用于查看当前mysql是否支持partition)

mysql>show plugins;

不同分区对比

分区类型 优点 缺点 共性

Range 适合与日期类型，支持复合分区 有限的分区 一般只针对某一列

List 适合与有固定取值的列，支持复合分区 有限的分区，插入记录在这一列的值不在List中，则数据丢失 一般只针对某一列

Hash 线性Hash使得增加、删除和合并更快捷 线性Hash的数据分布不均匀，而一般Hash的数据分布较均匀 一般只针对某一列

Key 列可以为字符型等其他非Int类型 效率较之前低，因为函数复制的程度，(如。MD5或SHA函数) 一般只针对某一列

海量数据优化2种方法

1、大表拆小表，分表、分区，物理的操作

2、sql语句的优化，通过增加索引来调整，但是数据量增大将会导致索引的维护代价增大，逻辑层面提升

大表拆小表

垂直分表，拆列字段，缺点：破坏表关系，表关联

水平分表，拆数据行，缺点：php代码量维护，逻辑层面困难增加

mysql分区

有点类似水平分表，但是它是基于逻辑层面，而不是物理层面，对于程序而言分区表还是一张表

mysql5.1的4种分区类型

range分区：基于属于一个给点连续区间的列值，把多行分配给分区

list分区：类似按range分区，区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择，列值是固定值的时候，例如枚举的时候

hash分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算，这个函数可以包含mysql中有效的，产生非负整数值得任何表达式，测试使用

key分区：类似按hash分区，区别在于key分区只支持计算一列或多列，且mysql服务器提供自身的哈希函数

range分区sql

create table emp(

int 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 not null,

store_id int not null 商店ID

)–根据商店ID时间来设置分区

partition by range(store_id)(

partition p0 values less than (6),

partition p1 values less than (11),

partition p2 values less than (16),

partition p3 values less than (21),

)

===========

create table emp(

int 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 not null,

store_id int not null

)–根据离职时间来设置分区

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,

)

emp.frm 表结构

emp.par 分区表(分了4个分区，每一个分区有一个分区数据文件和一个分区索引文件)

emp.#P#p0.MYD MYData分区数据文件

emp.#P#p0.MYI MYIndex分区索引文件

emp.#P#p1.MYD

emp.#P#p1.MYI

emp.#P#p2.MYD

emp.#P#p2.MYI

emp.#P#p3.MYD

emp.#P#p3.MYI

list分区sql

create table emp(

int 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 not null,

store_id int not null 商店ID

)–根据商店ID时间来设置分区，枚举类型，固定的值

partition by list(store_id)(

partition pNorth values in (3,5,6,9,17),

partition pEast values in (1,2,10,11,19,20),

partition pWest values in (4,12,13,14,18),

partition pCenter values l in (7,8,15,16)

)

emp.frm 表结构

emp.par 分区表(分了4个分区，每一个分区有一个分区数据文件和一个分区索引文件)

emp.#P#pCenteral.MYData分区数据文件

emp.#P#pCenteral.MYI MYIndex分区索引文件

emp.#P#pEast.MYD

emp.#P#pEast.MYI

emp.#P#North.MYD

emp.#P#North.MYI

emp.#P#pWest.MYD

emp.#P#pWest.MYI

hash分区sql

create table emp(

int 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 not null,

store_id int not null 商店ID

)–根据入职时间分区，入职时间不同， hash算法平均分配到不同的分区内

partition by hash(YEAH(hired))

partitions 4;

key分区

与hash分区类似，但它的key可以不是整数，如可以是字符串类型，mysql簇(Cluster)使用函数MD5(来)实现key分区；对于使用其他存储引擎的表，服务器使用其自己内部的哈希函数，这些函数与password()一样的运算法则