mysql技术分享--表分区实现

客官°小女子只卖身不卖艺 2022-10-08 15:24 122阅读 0赞

#### 分区表 ####

##### 分区概念 #####

*  分区功能并不是在存储引擎层完成的，因此不止有InnoDB存储引擎支持分区，常见的存储引擎MyISAM，NDB等都支持。但是也并不是所有存储引擎都支持，比如CSV，FEDERATED，MERGE等就不支持，在使用分区功能之前应该先了解选择的存储引擎是否支持分区操作。
 *  MySQL是在5.1版本之后添加了对于分区的支持。功能是将表或者索引分解成更小的多个，更可管理的部分。就访问数据库的应用而言，逻辑上还是一个表或者一个索引，但是物理上这个表或者索引可能由数十个物理分区组成，每个分区都是独立的对象，可以独立出来，也可以作为一个更大的对象的一部分进行处理。
 *  MySQL数据库支持的分区类型是水平分区（指同一个表中不同行的记录分配到不同的物理文件），不支持垂直分区（指将同一个表中不同的列分配到不同的物理文件）
 *  MySQL数据库的分区是局部分区索引，一个分区中即存放了数据又存放了索引。
 *  如下命令查看是否开启分区功能：

show variables like '%partition%'
    
    show plugins;

![在这里插入图片描述][20210622121524295.png]

##### 分区功能 #####

*  我们分区的作用一般是为了让数据库变得更快，但是启用分区后，并不一定会变快。通过经验来看分区对某些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 table's 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
    )ENGINE=INNODB
    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数据库  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpYW9qaWFtaW4wMTAy_size_16_color_FFFFFF_t_70]

select * from information_schema.PARTITIONS where TABLE_SCHEMA=DATABASE() and TABLE_NAME = 't';

![在这里插入图片描述][20210622153201843.png]

*  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
    )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 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_SCHEMA=DATABASE() and TABLE_NAME = 'sales';

![在这里插入图片描述][20210622155055184.png]

*  如上创建好，可以对sales这张表按时间处理账本，如果我们需要删除18年的数据，就不需要去delete from sales where date >=‘2008-01-01’ and date < ‘2009-01-01’ ,我们可以直接删掉p2008分区即可，如下操作

alter table sales drop PARTITION p2008;

![在这里插入图片描述][20210622155147921.png]

*  分区操作还有另外一个好处，可以加快某些查询的操作，如果我们只需要查询19年的销售额，如下：

EXPLAIN partitions select *  from sales where date >='2009-01-01' and date < '2009-12-31'

![在这里插入图片描述][20210622155653127.png]’

*  如上通过explain partitions 命令，上述语句sql优化器只需要搜索p2009分区，不用去全表扫描数据，大大提高执行速度，但是如上语句则不同：

EXPLAIN partitions select *  from sales where date >='2009-01-01' and date < '2020-01-01'

![在这里插入图片描述][20210622155848172.png]

*  条件修改成了< 2020-01-01，这个时候优化器选择p2009,p2010两个分区，这是不希望看到的。因此对于启用分区应该更具分区的特点编写sql语句
 *  如果我们需要更细分，按月来分区，如下分区方式第查询来说是无效的，如下：

create table sales(
    money int UNSIGNED not null,
    date datetime
    )ENGINE=INNODB
    PARTITION by range(YEAR(date)*100+MONTH(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'

![在这里插入图片描述][202106221652144.png]

*  如上查询语句扫描的全分区，但是之前的逻辑应该只扫p2020001分区，这个问题原因在于RANGE分区的查询，优化器只能对YEAR(),TO\_DAYS(),TO\_SECONDS(),UNIX\_TIMESTAMP()这类函数进行优化选择，因此对于上述要求，需要将分区函数改为TO\_DAY，如下:

create table sales(
    money int UNSIGNED not null,
    date datetime
    )ENGINE=INNODB
    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'

![在这里插入图片描述][20210622165630265.png]

*  此时查询只对p201001 分区进行扫描

#### LIST分区 ####

*  LIST分区和RANGE分区类似，只是分区列的值是离散的，不连续，如下：

create table t(
    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 t values(1,1),
    (1,2),
    (1,3),
    (1,4)
    
    select * from information_schema.PARTITIONS where TABLE_SCHEMA=DATABASE() and TABLE_NAME = 't';

![在这里插入图片描述][20210622172437890.png]

*  如上，按奇数偶数分区，如果插入的值不值分区的定义，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
    )engine=MyISAM
    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_SCHEMA=DATABASE() and TABLE_NAME = 't';

![在这里插入图片描述][20210622172905689.png]

*  可以看到，插入的1,10，记录是没有成功的，之前的2，3,4都是已经成功insert。

#### HASH分区 ####

*  HASH分区目的是将数据均由的分布的预定的各个分区中，保证个分区的数据大致一样。在RANGE和LIST分区中，必须明确知道一个列值或者列值的集合。而在HASH分区中MySQL自动完成了这些工作，你需要做的是基于要被hash的列值指定一个列值或者表达式，以及指定备份区的表要被分割的分区数量，如下案例

create table t_hash(
    a int,
    b datetime
    )ENGINE=INNODB
    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_SCHEMA=DATABASE() and TABLE_NAME = 't_hash';

![在这里插入图片描述][20210622183431741.png]

*  如上数据落在了P2分区
 *  MySQL数据库还支持一种LINEAR HASH的分区，更负责的分区算法，

create table t_hash(
    a int,
    b datetime
    )ENGINE=INNODB
    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技术分享-- 视图是什么][mysql_--]

[20210622121524295.png]: /images/20221005/54b43108e24544dcad149f49522f257b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpYW9qaWFtaW4wMTAy_size_16_color_FFFFFF_t_70]: /images/20221005/93915c81d24a4887bdb3b54478b7f65d.png
[20210622153201843.png]: /images/20221005/93665152db7940d5883d465321ac6d94.png
[20210622155055184.png]: /images/20221005/f76037b8159d4b6cbdc50f0727e7afa0.png
[20210622155147921.png]: /images/20221005/a2d5f6d1cdfa4279b1abf57b4a947d40.png
[20210622155653127.png]: /images/20221005/8e4b9d1763bb455ca54e0d6b9bf3afba.png
[20210622155848172.png]: /images/20221005/3b62357f04b5411ca0c0d54167927c0a.png
[202106221652144.png]: /images/20221005/c5797caed05341a1bc487192779a634b.png
[20210622165630265.png]: /images/20221005/b06c2314b0fe46e2b1b9e98062ab24e1.png
[20210622172437890.png]: /images/20221005/f5dff3fabf994a0eb0abe70c212b33ed.png
[20210622172905689.png]: /images/20221005/fc407ecdcda543baa474c9bfe68934a3.png
[20210622183431741.png]: /images/20221005/fb1789078f004e30b76f08ecaad444a2.png
[mysql_--]: https://blog.csdn.net/liaojiamin0102/article/details/118089940