网站制作的方法,在那里做网站,电商开发网站公司,wordpress添加产品图分区表
分区概念
分区功能并不是在存储引擎层完成的#xff0c;因此不止有InnoDB存储引擎支持分区#xff0c;常见的存储引擎MyISAM#xff0c;NDB等都支持。但是也并不是所有存储引擎都支持#xff0c;比如CSV#xff0c;FEDERATED#xff0c;MERGE等就不支持#xf…分区表
分区概念
分区功能并不是在存储引擎层完成的因此不止有InnoDB存储引擎支持分区常见的存储引擎MyISAMNDB等都支持。但是也并不是所有存储引擎都支持比如CSVFEDERATEDMERGE等就不支持在使用分区功能之前应该先了解选择的存储引擎是否支持分区操作。MySQL是在5.1版本之后添加了对于分区的支持。功能是将表或者索引分解成更小的多个更可管理的部分。就访问数据库的应用而言逻辑上还是一个表或者一个索引但是物理上这个表或者索引可能由数十个物理分区组成每个分区都是独立的对象可以独立出来也可以作为一个更大的对象的一部分进行处理。MySQL数据库支持的分区类型是水平分区指同一个表中不同行的记录分配到不同的物理文件不支持垂直分区指将同一个表中不同的列分配到不同的物理文件MySQL数据库的分区是局部分区索引一个分区中即存放了数据又存放了索引。如下命令查看是否开启分区功能
show variables like %partition%show plugins;分区功能
我们分区的作用一般是为了让数据库变得更快但是启用分区后并不一定会变快。通过经验来看分区对某些SQL语句的性能会带来提高但是分区主要用于高可用更利于数据库的管理。在OLTP应用中对于分区的使用应该更小心。因为OLTP类型的数据库对性能要求特别高也行分表分区能够对某一批查询效率提升但是可能对其他某些查询是致命的导致他需要遍历N个库的全量数据大大增加了IO次数。先了解当前MySQL数据库支持的以下几种类型的分区RANGE分区行数据基于属于一个给定连续区间的值放入分区。MySQL数据库5.5开始支持RANGE COLUMNS的分区LIST分区和RANGE分区类似只是LIST分区面向的是离散的值。同样5.5开始支持LIST COLUMNS的分区HASH分区根据用户自定义的表达式的返回值来进行分区返回值不能为负数。KEY分区更具MySQL数据库提供的哈希函数进行分区
错误案例
注意不论任何类型分区如果表中存在主键或者唯一索引时候分区必须是唯一索引的一个组成部分因此如下案例会报错
create table t1(
coll1 int not null,
coll2 date not null,
coll3 int not null,
coll4 int not null,
UNIQUE key(coll1, coll2)
)
PARTITION by Hash(coll3)
PARTITIONS 4;[Err] 1503 - A PRIMARY KEY must include all columns in the tables partitioning function将PARTITION by Hash(coll3) 修改成PARTITION by Hash(coll1)或者coll2 就能正确创建
唯一索引情况
唯一索引可以允许是NULL值的并且分区列只要是唯一索引的一个组成部分不需要整个唯一索引列都是分区列
create table t1(
coll1 int null,
coll2 date null,
coll3 int null,
coll4 int null,
UNIQUE key(coll1, coll2,coll3, coll4)
)
PARTITION by Hash(coll3)
PARTITIONS 4;没有主键或唯一索引情况
当建表时没有指定主键唯一索引可以指定任何一个列为分区列如下两种情况都可以正确创建案例
create table t1(
coll1 int not null,
coll2 date not null,
coll3 int not null,
coll4 int not null
)
PARTITION by Hash(coll3)
PARTITIONS 4;create table t1(
coll1 int not null,
coll2 date not null,
coll3 int not null,
coll4 int not null,
key (coll4)
)
PARTITION by Hash(coll3)
PARTITIONS 4;RANGE分区
RANGE分区是常用的一种分区类型。如下案例我们create table语句创建了一个id列的区间分区表。当id小于10 时候数据插入P0分区。当大于等于10 小于等于20 插入p1
create table t(
id int
)ENGINEINNODB
PARTITION by RANGE(id)(
PARTITION p0 VALUES less than(10),
PARTITION p1 VALUES less than(20)
);
insert into t values(9),(10),(15)插入数据后因为t根据id进行分区因此数据是根据id列的值的范围存放在不同的物理文件中可以通过查information_schema 架构在的 PARTITIONS 表来查看每个分区的具体信息在数据库中有一个 information_schema数据库
select * from information_schema.PARTITIONS where TABLE_SCHEMADATABASE() and TABLE_NAME t;TABLE_ROWS列说明了每个分区记录中记录的数量由于之前项表中插入了9,10,15三条记录因此可以看到当前分区P0有一条记录分区p1 有2条记录PARTITION_METHOD 标识分区的类型这里显示的是RANGE对于表t因为我们定义了分区因此对于插入的值都严格遵循分区的定义当插入不属于分区的数据时候会抛出异常
[SQL]
insert INTO t select 30;
[Err] 1526 - Table has no partition for value 30应该添加新的范围使得数据库分区能够兼容所有的数据清空如下
alter table t add PARTITION(PARTITION p2 values less than MAXVALUE);
insert INTO t select 30;RANGE日期分区
RANGE分区主要用于日期列的分区如对于销售类的表可以根据年份来分区存储销售数据如下案例
create table sales(
money int UNSIGNED not null,
date datetime
)ENGINEINNODB
PARTITION by range(YEAR(date))(
PARTITION p2008 values less than(2009),
PARTITION p2009 values less than(2010),
PARTITION p2010 values less than(2011)
);
insert into sales values(100, 2008-01-01),
(100, 2008-02-01),
(200, 2008-01-02),
(100, 2009-03-01),
(200, 2010-03-01)select * from information_schema.PARTITIONS where TABLE_SCHEMADATABASE() and TABLE_NAME sales;如上创建好可以对sales这张表按时间处理账本如果我们需要删除18年的数据就不需要去delete from sales where date ‘2008-01-01’ and date ‘2009-01-01’ ,我们可以直接删掉p2008分区即可如下操作
alter table sales drop PARTITION p2008;分区操作还有另外一个好处可以加快某些查询的操作如果我们只需要查询19年的销售额如下
EXPLAIN partitions select * from sales where date 2009-01-01 and date 2009-12-31 ’
如上通过explain partitions 命令上述语句sql优化器只需要搜索p2009分区不用去全表扫描数据大大提高执行速度但是如上语句则不同
EXPLAIN partitions select * from sales where date 2009-01-01 and date 2020-01-01 条件修改成了 2020-01-01这个时候优化器选择p2009,p2010两个分区这是不希望看到的。因此对于启用分区应该更具分区的特点编写sql语句 如果我们需要更细分按月来分区如下分区方式第查询来说是无效的如下
create table sales(
money int UNSIGNED not null,
date datetime
)ENGINEINNODB
PARTITION by range(YEAR(date)*100MONTH(date))(
PARTITION p201001 values less than(201002),
PARTITION p201002 values less than(201003),
PARTITION p201003 values less than(201004)
);
insert into sales values(100, 2008-01-01),
(100, 2008-02-01),
(200, 2008-01-02),
(100, 2009-03-01),
(200, 2010-03-01)EXPLAIN partitions select * from sales where date 2010-01-01 and date 2010-01-31 如上查询语句扫描的全分区但是之前的逻辑应该只扫p2020001分区这个问题原因在于RANGE分区的查询优化器只能对YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这类函数进行优化选择因此对于上述要求需要将分区函数改为TO_DAY如下:
create table sales(
money int UNSIGNED not null,
date datetime
)ENGINEINNODB
PARTITION by range(TO_DAYS(date))(
PARTITION p201001 values less than(TO_DAYS(2010-02-01)),
PARTITION p201002 values less than(TO_DAYS(2010-03-01)),
PARTITION p201003 values less than(TO_DAYS(2010-04-01))
);EXPLAIN partitions select * from sales where date 2010-01-01 and date 2010-01-31 此时查询只对p201001 分区进行扫描
LIST分区
LIST分区和RANGE分区类似只是分区列的值是离散的不连续如下
create table t(
a int,
b int
)engineINNODB
PARTITION by list(b)(
PARTITION p0 values in (1,3,5,7,9),
PARTITION p1 values in (0,2,4,6,8)
);insert into t values(1,1),
(1,2),
(1,3),
(1,4)select * from information_schema.PARTITIONS where TABLE_SCHEMADATABASE() and TABLE_NAME t;如上按奇数偶数分区如果插入的值不值分区的定义MySQL会抛出异常
[SQL]insert into t values(1,100)[Err] 1526 - Table has no partition for value 100而且List有一个特殊地方在insert插入多行时候遇到未分区定义的值时候MyISAM和InnoDB存储引擎的处理完全不同MyISAM会将之前的行都insert之后的不成功InnoDB存储引擎会将他看成是一个事务因此都会失败
[SQL]insert into t values(1,1),
(1,2),
(1,3),
(1,4),
(1,100)[Err] 1526 - Table has no partition for value 100
如上是InnoDB存储引擎返回的是没有对应的分区值并且数据全失败
create table t(
a int,
b int
)engineMyISAM
PARTITION by list(b)(
PARTITION p0 values in (1,3,5,7,9),
PARTITION p1 values in (0,2,4,6,8)
);
insert into t values(1,1),
(1,2),
(1,3),
(1,4),
(1,100)
select * from information_schema.PARTITIONS where TABLE_SCHEMADATABASE() and TABLE_NAME t;可以看到插入的1,10记录是没有成功的之前的23,4都是已经成功insert。
HASH分区
HASH分区目的是将数据均由的分布的预定的各个分区中保证个分区的数据大致一样。在RANGE和LIST分区中必须明确知道一个列值或者列值的集合。而在HASH分区中MySQL自动完成了这些工作你需要做的是基于要被hash的列值指定一个列值或者表达式以及指定备份区的表要被分割的分区数量如下案例
create table t_hash(
a int,
b datetime
)ENGINEINNODB
PARTITION by hash(YEAR(b))
PARTITIONS 4;
如上hash规则如果b列的值2010-04-01那么这个记录在t_hash中保存的确认值如下
MOD(YEAR(2010-04-01), 4) MOD(2010,4) 2如上计算应该放到2分区中
insert into t_hash values (1, 2010-04-01)select * from information_schema.PARTITIONS where TABLE_SCHEMADATABASE() and TABLE_NAME t_hash;如上数据落在了P2分区MySQL数据库还支持一种LINEAR HASH的分区更负责的分区算法
create table t_hash(
a int,
b datetime
)ENGINEINNODB
PARTITION by LINEAR HASH (YEAR(b))
PARTITIONS 4;
MySQL数据库用一下方式判断分区 取大于分区数量4 的下一个2 的幂值V, V POWRE(2, CEILING(LOG(2,num))) 4所在分区N YEAR(‘2010-04-01’) (V-1) 2 得到的分区还是2但是HASH算法与之前的完全不同LINEAR HASH分区的有点在于增加删除合并和拆分分区变得更加快速这有利于处理含有大量数据的表缺点在于与使用HASH分区得到的数据分布相比各个分区数据的分布不均匀。
KEY分区
上一篇mysql技术分享-- 视图是什么
