linux mysql 分区表_15.mysql的分区表

最新推荐文章于 2021-05-04 07:24:16 发布

陆帅全

最新推荐文章于 2021-05-04 07:24:16 发布

阅读量113

点赞数

文章标签： linux mysql 分区表

本文链接：https://blog.csdn.net/weixin_31881743/article/details/114321088

版权

mysql的分区功能不是在存储引擎层完成的，因此不会只有innodb存储引擎支持分区，常见的存储引擎有MYISM、NDB

mysql的分区是在5.1版本时才支持的，分区的过程是将一个表或索引分成多个更小、更可管理的部分，就访问的数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的，可以独自处理。

mysql数据库支持的分区类型为水平分区，并不支持垂直分区。且分区是局部分区索引，一个分区中既存放了数据又存放了索引，而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。

一.查看当前数据库是否启动了分区功能：

root@localhost:(none)>show plugins;+----------------------------+----------+--------------------+---------+---------+

+----------------------------+----------+--------------------+---------+---------+

+----------------------------+----------+--------------------+---------+---------+

二.当前mysql支持的分区类型

range分区：行数据基于属于一个连续空间的列值被放入分区，mysql5.5开始支持range columns的分区

list分区：这种是定义列表的值对应的行数据，mysql5.5开始支持list columns的分区

hash分区：根据用户自动以的表达式的返回值来进行分区，返回值不能为负数

key分区：根据mysql数据库提供的哈希函数来进行分区

注意：不论哪种类型分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分

情况一：usetest;CREATE TABLEt1 (

co11INT NOT NULL,

co12 dateNOT NULL,

co13INT NOT NULL,

co14INT NOT NULL,UNIQUE KEY ( co11, co12 )) PARTITION BY HASH ( co13 ) PARTITIONS 4;1503 - A PRIMARY KEY must include all columns in the table's partitioning function, Time: 0.005000s

情况二：

CREATE TABLE t1 (

co11 INT NOT NULL,

co12 date NOT NULL,

co13 INT NOT NULL,

co14 INT NOT NULL,

UNIQUE KEY ( co11, co12,co13,co14 )) PARTITION BY HASH ( co13 ) PARTITIONS 4;

OK, Time: 0.072000s

如果建表时没有指定主键，唯一索引，可以指定任何一个列为分区列，因此下面两句创建分区的SQL语句都是可以运行的

CREATE TABLEt2 (

co11INT NULL,

co12 dateNULL,

co13INT NULL,

co14INT NULL) ENGINE= INNODB PARTITION BY HASH ( co13 ) PARTITIONS 4;CREATE TABLEt3 (

co11INT NULL,

co12 dateNULL,

co13INT NULL,

co14INT NULL,KEY(co14)

) ENGINE= INNODB PARTITION BY HASH ( co13 ) PARTITIONS 4;

在linux系统中显示情况如下：

[root@node01 data]# ls

auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql performance_schema slow.logsys test[root@node01 data]# cd test[root@node01 test]# ls

db.opt t1#P#p1.ibd t2.frm t2#P#p2.ibd t3#P#p0.ibd t3#P#p3.ibd test2.frm userinfo.ibd

t1.frm t1#P#p2.ibd t2#P#p0.ibd t2#P#p3.ibd t3#P#p1.ibd test1.frm test2.ibd

t1#P#p0.ibd t1#P#p3.ibd t2#P#p1.ibd t3.frm t3#P#p2.ibd test1.ibd userinfo.frm

其中，其中每个表的有一个.frm文件，4个数据文件组成

三.range 分区

range 分区是分区类型中最常见的分区的，下面的create table 语句创建了一个id列的区间分区表。当id小于10时，数据插入到p0分区，当id大于10小于20时，数据插入p1分区。

#range 分区：

CREATE TABLE t ( id INT ) ENGINE = INNODB PARTITION BY RANGE ( id )(

PARTITION p0

VALUES

less than ( 10 ),

PARTITION p1

VALUES

less than ( 20 ));

show tables;

#插入数据

insert into t VALUES(1),(2),(3),(10),(15);

[root@node01 test]# ls

db.opt test1.frm test1.ibd test2.frm test2.ibd t.frm t#P#p0.ibd t#P#p1.ibd userinfo.frm userinfo.ibd

这里的表是根据id键进行分区的，因此，这里我们可以通过以下命令来查看每个分区的具体信息；

root@localhost:test>select * from information_schema.partitions where table_schema=database() and table_name='t'\G:

*************************** 1. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: t

PARTITION_NAME: p0

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 1

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: 10

TABLE_ROWS: 3

AVG_ROW_LENGTH: 5461

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: 2021-02-05 00:17:00

UPDATE_TIME: 2021-02-05 00:19:56

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

*************************** 2. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: t

PARTITION_NAME: p1

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 2

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: 20

TABLE_ROWS: 2

AVG_ROW_LENGTH: 8192

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: 2021-02-05 00:17:00

UPDATE_TIME: 2021-02-05 00:19:56

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

2 rows in set (0.00 sec)

其中：table_rows列反映了每个分区中记录的数量，这里要注意因为是按照id进行了分配，所以如果我们插入的数值不再这些范围中，那么数据就会抛出一个ERROR 1526:table has no partition for value 30,

对于以上的问题，我们可以设置一个maxvalue值得分区，maxvalue可以理解为一个正无穷，因此所有大于等于20且小于maxvalue的值都会放在p2分区。

ALTER TABLE t ADDPARTITION (

PARTITION p2VALUESless than MAXVALUE

);

用途：range 分区主要用于日期列分区，例如对于销售类的表，可以根据年来分区存放销售记录，如下面的分区表sales:

create table sales(money int unsigned not null,date datetime)engine=innodb 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 select 100,'2008-02-01';INSERT into sales select 100,'2009-02-01';INSERT into sales select 100,'2010-02-01';

# 这样创建的好处是，对于sales这张表管理，如果我们要删除2008年的数据，不需要执行delete from sales where date>'2008-02-01' and date

alter table sales drop partition p2008;

#第二，这里可以在某些情况下加快查询速度(这个看情况)，这里看优化器在选择的时候是选择了那几个分区进行查询。

四.LIst分区

list 分区和range分区非常相似，只是分区列的值是离散的，而非连续的。

CREATE TABLE t1 ( a INT, b INT ) ENGINE = INNODB PARTITION BYlist ( b )(

PARTITION p0VALUES

IN ( 1, 3, 5, 7, 9),

PARTITION p1VALUES

IN ( 0, 2, 4, 6, 8));

然后再插入数据

insert into t1 select 1,1;insert into t1 select 1,2;insert into t1 select 1,3;insert into t1 select 1,4;insert into t1 select 1,5;

#注意对于不同的存储引擎来说，myism和innodb,其中myISM遇到对插入超过范围的值时，后面的数据无论是否合理都不能插入，但是前面合理的数据都是能进行插入的，但是对于innodb来说，只要有一条数据不合理，所有的数据都不能插入进去。

五.HASH分区

hash分区的目的是将数据均匀分布到预先定义的各个分区中，保证各分区的数据数量大致上是一样的，而在range和list分区中，必须要指定一个给定的列值或列值集合应该保存在哪个分区中，而在hash分区中，mysql自动完成这些工作，用户所需要做只是将要进行哈希分区的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

要使用hash分区来分割一个表，要在create table语句上添加一个’partition by hash(expr)'子句，其中‘expr'是一个返回一个整数的表达式。它可以仅仅字段类型为mysql整型的列名，此外用户很可能需要在后面再添加一个’partition num'子句，其中num是一个非负的整数，它表示将要被分割成分区的数量，如果没有包括一个partitions子句，那么分区的数量将默认为1.

创建一个hash分区的表t,分区按日期b进行：create table t_hash(a int,b datetime)engine=innodb partition by hash(year(b)) partitions 4;

这里如果插入一个列b为2010-04-01的记录到表t_hash中，那么保存该条记录的分区如下：

MOD(year('2010-04-01'),4)=MOD(2010,4)=2

....

六.分区和性能

数据库的应用分为两类:一类是OLTP(在线事务处理)，如Blog、电子商务、网络游戏等；另一类是OLAP(在线分析处理)，如数据仓库、数据集中。

因此，对于OLAP的应用，分区的确是可以很好地提高查询的性能，因为OLAP应用大多数查询需要频繁地扫描一张很大的表。例如有一张1亿行的表，其中有一个时间戳属性列。用户的查询需要从这张表中获取一年的数据，如果按照时间戳进行分区，则只需要扫描相应的分区即可，这就是前面的Patition pruning技术。对于OLTP的应用，分区应该小心，在这中应用下，通过不可能会获取一张大表中10%的数据，大部分都是通过索引返回几条记录即可。而根据B+树原理可知，对于一张大表，一般的B+树需要2~3次磁盘IO 因此B+树可以很好地完成操作，不需要分区的帮助，并且设计不好，分区可能会带来严重的性能问题。