MySQL学习笔记：基础模型二 分区

环境

MacBook Pro

序言

MySQL 技术内幕 InnoDB 存储引擎 第二版

分区表

MySQL只支持局部分区索引，一个分区中即存放了数据又存放了索引。

全局分区：数据存放在各个分区中，但是所有的数据的索引放在一个对象中。

mysql目前只支持局部分区

分区类型

支持 range分区，list分区，hash分区, linear hash, key分区, columns 类型分区；

无论何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。

Range 分区

分区策略：根据范围值进行分区

如：

create table t (id int
)engine=inndb
partition by range (id)
partition po values less than (10),
partition p1 values less than (20);

上面的意思是，根据字段ID来进行分区；策略：
1、值小于10的，在p0分区，
2、值小于20大于10的在p1分区。
3、值大于20的情况，MySQL数据库会抛出一个异常。

启用分区后，表不再由一个ibd文件组成，而是各个分区ibd文件组成；
如：t#P#p0.ibd， t#P#p1.ibd 类似这样的格式；

List 分区

分区策略：根据提前定义好的离散值来进行分区.

create table t (a int,b int
)engine = innodb
partition by list(b)(
partition by po values in (1, 3, 5, 7, 9),
partition by p1 values in (0, 2, 4, 6, 8)
);

可以看出，上面是根据字段b进行分区，分区策略就是根据上面指定的值来分区；
对于不在范围中的值，MySQL数据库会抛出异常。

HASH 分区

分区策略：利用哈希函数将数据均匀分布到预先定义的各个分区中，保证各分区的数据量大致一样。

实际操作就是：定义一个哈希函数和需要分区的数量；
剩下的MySQL自动完成工作。

create table t_hash(a int,b datetime
)engine=innoDB
partition by hash (year(b))
partitions 4;

partition by hash (expr) , expr是一个返回整数的表达式。
其可以是字段类型为MySQL整型的列名。
上面的4 就表示分区数量为4

hash分区算是range和list分区的一个综合；range和list都需要提前给出列值和列值集合。
而hash分区自动帮你弄好；

linear hash 分区

这个和hash分区是类似的，只不过，其算法复杂些；
也就是partition by hash (expr)中的expr 要复杂些；
策略：

• 取大于分区数量4的下一个2的幂值V，
• 所在分区N=YEAR（‘2020-04-01’）&（V-1）=2

上面的年月日是举例

优点：增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有大量数据的表。
缺点：与hash分区相比，数据分布的不是那么均匀；

key 分区

这个和hash分区也是类似的，不同之处在于hash分区使用用户定义的函数进行分区，key分区使用MySQL数据库提供的函数进行分区。使用的Hash函数是存储引擎提供的。

create table t_key(
a int,
b datetime) engine=innodb
partition by key(b)
partitions 4;

可以使用关键字Linear和在Hash分区中具有同样的效果，分区的编号是通过2的幂算法得到，而不是通过模式算法。

columns 分区

在前面的分区中，分区的列值都必须是整数，而columns分区可以不是整数。

前面分区中，如果不是整数，那么得通过函数将其转为整数。如year(), to_days(), month()等。
MySQL5.5版本开始支持columns分区。
columns分区根据类型直接比较而得，不需要转化为整型。

支持的数据类型有：

• 所有整型类型：int、smallint、tinyint 、bigint。float和decimal不支持
• 日期类型。如 date、datetime 其余日期不支持
• 字符串类型。char、varchar，binary，varchar。blob和text不支持

直接使用日期分区，不再需要year()和to_days()；

create table t_columns_range(a int,b datetime
)engine=innodb
partition by range columns (b)(
partition p0 values less than ('2009-01-01'),
partition p1 values less than ('2010-01-01')
);

使用字符串分区：

create table t_columns_str(a varchar(25),b varchar(25)
)engine=innodb
partition by list columns (b)(
partition pRegion_1 values in ('yutao', 'bobo'),
partition pRegion_2 values in ('asdfasdf', 'rtjrghj'),
);

支持多列分区：

create table t_columns_str(a varchar(25),b varchar(25),c int
)engine=innodb
partition by range columns (a, b, c)(
partition pRegion_1 values less than ('yutao', 'bobo', 40),
partition pRegion_2 values less than ('asdfasdf', 'rtjrghj', 60),
partition pRegion_3 values less than ('d', 'dfs', 80),
partition pRegion_4 values less than (maxvalue,maxvalue,maxvalue)
);

说明：

比如：('yutao', 'bobo', 40) 第一个yutao，对应列名a，第二个参数bobo，对应列名b，第三个参数40，对应列名c

子分区

在分区的基础上在进行分区；但是有限制条件；
MySQL数据库只允许range和list分区上再进行hash或key的子分区；

分区NULL的处理：
如果是range分区，那么null值一定是在最左边的分区，也就是p0分区；

如果是list分区，那么必须在分区中指定哪个分区允许分区，否则报错；

如果是hash和key分区：任何分区函数都会将null值的记录返回为0；

分区小节：

Q:分区一定能提高性能吗？
A：不一定，有时还会降低性能，在OLAP（在线分析处理）场景中，能提高性能，但是在OLTP（在线事务处理）中，可能适得其反
比如 1000W的B+树的高度为3，100W的B+树的高度是2，那么按照主键分区的索引可以避免1次IO，从而提高效率。
但是如果不是主键查询，而是其他列的查询，这时的查询需要扫描所有的10个分区，即使每个分区的查询开销是2次IO，则一共需要20次IO。而对于原来的单表的设计，对于KEY的查询只需要2~3次IO。

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！