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

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

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

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

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

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

| Name | Status | Type | Library | License |

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

| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |

| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |

| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |

| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |

| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |

| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |

| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |

| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |

| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |

| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |

| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |

| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |

| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |

| ngram | ACTIVE | FTPARSER | NULL | GPL |

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

二.当前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+树可以很好地完成操作,不需要分区的帮助,并且设计不好,分区可能会带来严重的性能问题。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值