mysql非整型分区_mysql分区

mysql分区的优点：

1、和单个磁盘或者文件系统分区相比，可以储存更多的数据。

2、优化查询，在where字句中包含分区条件时，可以只扫描必要的一个或多个分区来提高查询效率；同时在涉及sum()和count()这类聚合函数查询的时候，可以容易的在每个分区上并行处理，最终只需要汇总所有分区得到的结果。

3、对于已过期或不需要保存的文件，可以通过删除与这些数据有关的分区来快速的删除数据。

4、跨多个磁盘分散数据查询，以获得更大的查询吞吐量。

mysql5.6以后的版本通过一下命令查询当前mysql是否支持分区。(该查询会显示所有插件，去查找partition插件对应的信息)

show plugins;

Mysql支持使用大部分存储引擎(比如 myisam,innodb,memory等存储引擎)创建分区表；Mysql不支持使用MERGE或是CSV存储引擎来创建分区表；同一个分区表的所有分区必须使用同一个存储引擎，不同的分区表可以使用不同的存储引擎。

mysql分区的类型：

1、range分区2、lsit分区3、hash分区4、key分区

5、colunms分区(5.5引入的分区类型)

注:mysql5.1开始支持分区的功能，5.1中range、list、hash分区都要求分区键都是int类型，也就是5.1仅仅支持整数分区。唯一例外的就是key分区，可以使用其他类型的列(blob或text列类型除外)作为分区键；5.5以上版本已支持非整数的range、list分区了

无论是那种分区，要么分区表上没有主键/唯一键，要么分区表的主键/唯一键必须包含分区，也就是说不能使用主键/唯一键字段外的其他字段分区(分区字段必须是主键)

mysql命名是大小写敏感的，但是对于分区的命名大小写确是不敏感的。例如，创建分区时使用下面语句就会报错，因为它认为下面的分区名一致而导致分区名重复。

partition mypart values in (1,3,5),

partition MyPart values in (2,4,6)

一、range分区

create table temp(

idnot null,

enamevarchar(30),

jobvarchar(30),

store_idint not null)

partitionbyrange (store_id) (

partition p0values less than (10),

partition p1values less than (20),

partition p2values less than (30)

);

上述DDL语句中，如果将id作为主键来创建表的话，那么就会报错: a primary key must include all columns in the table's partitioning function。(这是问题针对对所有分区类型)；

按照这个分区方案，当store_id大于等于30的时候，数据就会插入失败，因为mysql不知道该把数据插入到哪个磁盘分区下。

这个时候就需要用到下面语句

alter table temp addpartition (

partition p3valuesless than maxvalue

);

range分区中，分区键如果是null值就会被当做一个最小值来处理。

非整数分区，5.5后支持，提供了range columns 分区来支持非整数分区，例如字段中有一些字段类型创建日期create_time，这样创建日期时候就不需要通过函数进行转换。

partition byrange columns (create_time) (

partition p0values less than ('19940813'),

partition p1values less than ('20120101'),

partition p2values less than ('20180101')

);

日期处理的函数还有year()，to_days()，to_seconds()--把日期转化成秒。

删除分区：

alter table temp drop partition p0; #删除p0分区的数据

经常运用分区查询，可以很快的确定只有某一个或某些分区需要扫描，例如检索商店id大于25的记录，只需要扫描p2分区即可。

explain partition select count(1) from temp where store_id>=25;

二、list分区

list分区建立在离散的值列告诉数据库指定的值属于哪个分区，list分区在很多方面类似range分区

create tableexpenses(

expenses_date datenot null,

categoryint,

amountdecimal (10,3)

)

partitionbylist(category)(

partition p0values in (3,5),

partition p1values in (1,10),

partition p2values in (4,9),

partition p3values in (2)

);

5.5后支持非整数分区

create tableexpenses(

expense_date datenot null,

categoryvarchar(30),

amountdecimal (10,3)

)

partitionbylist columns (category) (

partition p0values in ('lodging','food'),

partition p1values in ('flights','ground transportation'),

partition p2values in ('leisure','customer entertainment')

);

三、columns分区

columns是在mysql5.5引入的分区类型，是为了解决mysql5.5版本之前只支持非整型分区，从而需要通过额外的函数计算得到整型或者通过额外的转换表来转换为整数在分区的问题。columns可以细分为range columns分区和list columns分区。range columns和list columns分区都支持整数、日期时间、字符串三大类型数据。

1、所有整数类型：tinyint 、smallint、mediumint、int和bigint；其他数值类型都不支持，例如不支持decimal和float；2、日期时间类型：date和datetime；3、字符类型：char、varchar、binary和varbinary；不支持text和blob类型作为分区键；

columns分区的亮点除了支持数据类型增加以外，另外一大亮点就是columns分区还支持多列分区。

create tablerc3(

aint,

bint)

partitionbyrange columns(a,b) (

partition p01values less than (0,10),

partition p02values less than (10,10),

partition p03values less than (10,20),

partition p04values less than (10,35),

partition p05values less than (10,maxvalue),

partition p06valuesless than (maxvalue,maxvalue)

);

四、hash分区

hash分区主要是用分散热点读，确保数据在预算确定个数的分区中尽可能平均分布，mysql支持两种分区，常规HASH分区、线性HASH分区(LINEAR HASH分区)；常规hash使用的是取模算法。线性hash分区使用的是一个线性的2的幂的运算法则。

下面创建一个常规hash分区的散列表emp，使用partition by partitions hash(expr) partitions num,对分区类型、分区键和分区个数进行定义。其中expr是某列值或一个基于某列值返回的一个整数值的表达式，num是一个非负的整数，标识分隔成分区的数量，某人num为1。

下面创建一个基于store_id列hash分区的表，表被分成了4个分区。

create tableemp (

idint not null,

enamevarchar(30),

hired datenot null default '1970-01-01',

separated datenot null default '9999-12-31',

jobvarchar(30) not null,

store_idint not null)

partitionby hash (store_id) partitions 4;

对于一个表达式expr，我们可以计算出它将会保存到那个分区。将要保存的分区编号为N，N=MOD(expr,num)。所以下面记录中我们可以发现将会保存到那个分区中

insert into emp values (1,'tom','2010-10-10','9999-12-31','cleck',234);

根据上面的公示就可以发现N=MOD(234,4)#取模，求余#，结果为2。所以被分配到第二分区。

'expr'可以是mysql中有效的任何函数或其他表达式，只要他们返回一个即非常也非随机数的整数。只是每当插入/更新/删除一行数据时，这个表达式都需要运算一次，这就意味着非常复杂的表达式可能会引起性能问题。mysql也不推荐使用涉及多列的哈希表达式。

常规的hash分区的方式通过取模的方式使数据尽可能分配到每个分区中，让每个分区中的数据都减少，从而提高查询的效率；可能当我们需要新增分区或者合并分区的时候，问题就会出现了。假设原来是5个常规分区，现在新增为6个。取模算法由原来的mod(exp,5)变成了mod(exp,6)。原来5个分区中的数据大部分需要从新通过从新计算从新分区。所以常规的hash分区在分区管理上带来的代价太大了，不适合需要灵活变动分区的需求。

所以为了降低管理分区上的代价，mysql提供了线性分区，分区函数是一个线性的2的幕运算。

线性hash分区和常规hash分区在语法上唯一的区别就是在partition by 字句中添加 linear 关键字，例如：

create tableemp (

idint not null,

enamevarchar(30),

hired datenot null default '1970-01-01',

separated datenot null default '9999-12-31',

jobvarchar(30) not null,

store_idint not null)

partitionby linear hash (store_id) partitions 4;

线性hash分区的优点是，在分区维护(包含新增、删除、合并、拆分分区)时，mysql能够处理得更加迅速；缺点是，对比常规hash分区(取模)的时候，线性hash各个分区之间数据的分布不太平衡。

五、key分区

4，key分区

key分区和hash分区的区别：

1)hash分区允许用户自定义的表达式，而key分区不允许使用用户自定义的表达式。

2)hash分区只支持整数分区，key分区支持除了blob或text类型之外的其他数据类型分区。

3)与hash分区不同，创建key分区表的时候，可以不指定分区键，默认会选择使用主键/唯一键作为分区键，没有主键/唯一键,必须指定分区键。

在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂(powers-of-two)算法得到，而不是通过模数算法

CREATE TABLEemployees (

idINT NOT NULL,

first_nameVARCHAR(30),

last_nameVARCHAR(30),

store_idINT NOT NULL,

emailVARCHAR(30) NOT NULL,

create_time DATENOT NULL DEFAULT '9999-12-31',

)

PARTITIONBY LINEAR Key(email) PARTITIONS 4;