22.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数据库提供的哈希函数来进行分区
注意：不论哪种类型分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分
情况一：use test; CREATE TABLE t1 ( co11 INT NOT NULL, co12 date NOT NULL, co13 INT NOT NULL, co14 INT 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 TABLE t2 ( co11 INT NULL, co12 date NULL, co13 INT NULL, co14 INT NULL ) ENGINE = INNODB PARTITION BY HASH ( co13 ) PARTITIONS 4; CREATE TABLE t3 ( co11 INT NULL, co12 date NULL, co13 INT NULL, co14 INT 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.log sys 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 ADD PARTITION ( PARTITION p2 VALUES less 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<'2009-02-01',只需要删除2008年数据所在的分区即可。
alter table sales drop partition p2008;
#第二，这里可以在某些情况下加快查询速度(这个看情况)，这里看优化器在选择的时候是选择了那几个分区进行查询。
四.LIst分区
list 分区和range分区非常相似，只是分区列的值是离散的，而非连续的。
CREATE TABLE t1 ( a INT, b INT ) ENGINE = INNODB PARTITION BY list ( b )( PARTITION p0 VALUES IN ( 1, 3, 5, 7, 9 ), PARTITION p1 VALUES 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+树可以很好地完成操作，不需要分区的帮助，并且设计不好，分区可能会带来严重的性能问题。
七.使用分区表主要事项
- 结合业务场景选择分区键，避免跨分区查询
- 对分区表进行查询最好在WHERE从句中包含分区键
- 具有主键或唯一索引的表，主键或唯一索引必须是分区键的一部分
