MySQL(九)：分区，mybatis 架构设计层次
通过 EXPLAIN PARTITION 命令我们可以发现，上述语句中，SQL 优化器只需要检索 p2008 这个分区，而不会去搜索所有的分区——称为 Partition Pruning(分区修剪)，故查询的速度得到了大幅度的提升。但需要注意的是，如果执行下列语句，结果是一样的，但是优化器的选择会出现不同
EXPLAIN SELECT * FROM sales WHERE cur >= "2008-01-01" AND cur < "2009-01-01";
可以看到 partitions 列只有 p2009 这个分区，证明了只查找了 p2009 这个分区
EXPLAIN SELECT * FROM sales WHERE cur >= "2009-01-01" AND cur < "2010-01-02";
可以看到现在就查询了两个分区。
LIST 分区
LIST 分区和 RANGE 分区非常相似，只是分区列的值是离散的，而非连续的，例如
CREATE TABLE haha(
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)
);
两句 SQL 的不同之处，仅仅在于 RANGE 分区使用的是 LESS THAN，而 LIST 分区使用的是 IN，因为每个区的值是离散的，因此只能定义值。例如向表中插入下面的一些数据
INSERT INTO haha SELECT 1,1;
INSERT INTO haha SELECT 1,2;
INSERT INTO haha SELECT 1,3;
INSERT INTO haha SELECT 1,4;
下面我们来看一下表里面的情况
SELECT table_name,partition_name,table_rows FROM information_schema.PARTITIONS WHERE table_name = "haha" AND table_schema = DATABASE();
同理，如果插入的值不在分区上，同样会抛出异常
INSERT INTO haha SELECT 1,11;
注意
在执行 Insert 操作插入多个行数据的过程中如果遇到分区未定义的值，MyISAM 和 InnoDB 存储引擎的处理会完全不同，MyISAM 会将之前的行数据都插入，但之后的数据不会被插入，而 InnoDB 则会将整个插入过程视为一个业务，最终的结果是会进行回滚到正常状态。
HASH 分区
HASH 分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据数量大致是一样的。在 Range 和 LIST 分区中，必须明确指定一个给定的列值或列值范围应该保存在哪个分区中；而在 HASH 分区中，MySQL 会自动完成这些工作(不需要自己去规划分区，就是不需要像 LIST 和 RANGE 一样，还要去设置范围和名字)，用户所要做的只是基于将要被散列的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
要使用 HASH 分区来分隔一个表，要在 CREATE TABLE 语句上添加一个 PARTITION BY HASH（expr）子句，其中"expr"就是表达式，要返回一个整形的表达式，expr 可以仅仅是字段类型为 MySQL 整形的列名，此外，用户很可能需要在后面再添加一个 PARTITIONS num 子句，其中 num 是一个非负的整数，它表示表将要被分割成分区的数量，如果没有这个子句的话，那么默认为 1。
CREATE TABLE t_hash(
a INT,
b DATETIME
)ENGINE=INNODB
PARTITION BY HASH(YEAR(b))
PARTITIONS 4; //分成 4 个区
它的散列算法使用的是下面的算法
M O D ( Y E A R ( 日 期 ) , 分 区 数 量 ) MOD(YEAR(日期),分区数量) MOD(YEAR(日期),分区数量)，即取日期的年份然后余上分区数量
分区是按
《一线大厂 Java 面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》
【docs.qq.com/doc/DSmxTbFJ1cmN1R2dB】 完整内容开源分享
照 YEAR 函数进行的，而这个值本身是离散的，可能会不太均匀，但如果对连续的值进行 HASH 分区，比如自增长的主键，则可以较好地将数据进行平均分布。
MySQL 数据库还支持一种称为 LINEAR HASH 的分区，它使用一个更加复杂的算法来确定新行插入到已经分区的表中的位置，它的语法和 HASH 分区的语法相似，只是将关键字 HASH 改为 LINEAR HASH。下面创建一个 LINEAR HASH 的分区表 t_linear_hash
CREATE TABLE t_linear_hash(
a INT,
b DATETIME
)ENGINE=INNODB
PARTITION BY LINEAR HASH(YEAR(b))
PARTITIONS 4;
它的算法就比较复杂一点
首先取大于或等于分区数量的下一个 2 的幂值 V = POWER(2,CEILING(LOG(2,num))) = 4 ,num 为分区数量
所在分区 N = YEAR(‘日期’) & (V-1)
CEILING 函数是对参数进行向上取整，LOG（a,b）是进行求 l o g a = b log_a = b loga?=b，其实就是通过向上取整，来找到比分区数量大或者相等的下一个 2 的幂值（ 2 l o g 2 4 = 4 2^{log_24} = 4 2log2?4=4，所以 POWER 的幂为 LOG 时，其实就是等于 num，只不过这里使用向上取整然他变大）
现在来谈谈 &运算，这个是关于二进制的运算，比如 a&b，其实就是将 a 和 b 都化成二进制，然后进行与运算，相同位并且都为 1 才为 1，其余都为 0，那这里为什么要跟 V-1 进行与运算呢？因为跟 V-1 进行与运算的话，那么得到的最大值顶多就是 V-1，不可能大于 V-1（分区从 0 开始算起）。
LINEAR HASH 分区的优点在于增加、删除、合并和拆分分区将会变得更加快捷，这有利于处理含有大量数据的表，但 LINEAR HASH 分区的缺点在于与使用 HASH 分区得到的数据分布相比，各个分区间数据的分布可能不太均匀，也就是散列程度会比 HASH 分区低（因为幂值 V 是固定的，所以 V-1 也是固定的，所以只要后面位数 V-1 化成二进制的位数对应相同，那么就会在同一个分区）
KEY 分区
KEY 分区和 HASH 分区相似，不同之处在于 HASH 分区通过用户定义的函数进行分区（其实只是可以自己规定，比如使用 YEAR），KEY 分区使用 MySQL 数据库提供的函数进行分区，NDB Cluster 引擎使用 MD5 函数来分区，对于其他存储引擎，MySQL 数据库使用其内部的散列函数来分区，这些函数基于与 PASSWORD()一样的运算法则。
当在 KEY 中使用关键字 Linear 时，效果跟 HASH 一样，不是通过余数方法得到分区值，而是通过求幂，然后进行与运算得到。
COLUMNS 分区
在前面介绍的 4 种分区，都有一个共同点，就是分区的条件必须是一个整形，如果不是整形，那么就需要通过 MySQL 内置函数来将它化成整形，比如 YEAR()、TO_DAYS()、MONTH()等函数。不过从 MySQL5.5 开始支持 COLUMNS 分区，可以将它当成是 RANGE 分区和 LIST 分区的一种进化。COLUMNS 分区可以使用非整形来进行分区，分区根据类型直接比较得到，不需要转化为整形，此外 RANGE COLUMNS 分区可以对多个列的值进行分区。
COLUMNS 分区支持以下数据类型：
所有的整数类型，如 INT、SMALLINT、TINYINT 和 BIGINT。对于 FLOAT 和 DECIMAL(浮点数类型)则不予支持。
日期类型，如 DATE 和 DATETIME，对其余的日期类型不予支持
字符串类型，如 CHAR、VARCHAR、BINARY 和 VARBINARY。对 BLOB 和 TEXT 类型不予支持（即对超大的 VARCHAR 和超大的 BINARY 不支持）
对于日期类型的分期，不再需要使用 YEAR()和 TO_DAYES()等函数来将它化成整形，可以直接使用 COLUMNS。
举个栗子（这里使用 RANGE COLUMNS）
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")
);
同样地，可以使用字符串进行分区
举个栗子（这里使用 LIST COLUMNS）
CREATE TABLE customer(
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(25),
street_2 VARCHAR(25),
city VARCHAR(15),
renewal DATE
)ENGINE=INNODB
PARTITION BY LIST COLUMNS(city)(
PARTITION pRegion_1 VALUES IN("shanghai","foshan"),
PARTITION pRegion_2 VALUES IN("guangzhou","beijing")
) ;
对于 RANGE COLUMNS 分区，可以使用多个列进行分区
CREATE TABLE rcx(
a INT,
b INT,
c CHAR(3),
d INT
)ENGINE=INNODB
PARTITION BY RANGE COLUMNS(a,d,c)(
PARTITION p0 VALUES LESS THAN(5,10,'a'),
PARTITION p1 VALUES LESS THAN(10,20,'b'),
PARTITION p2 VALUES LESS THAN(maxvalue,maxvalue,maxvalue)
);
MySQL5.5 开始支持 COLUMNS 分区，对于之前的 RANGE 和 LIST，最好都使用 RANGE COLUMNS 和 LIST COLUMNS 来进行代替。
子分区
子分区其实是在分区的基础上再进行分区，有时也称这种分区为复合分区，MySQL 数据库允许在 RANGE 和 LIST 的分区上再进行 HASH 或 KEY 的子分区。
举个栗子
CREATE TABLE ts(
a INT,
b DATE
)ENGINE=INNODB
PARTITION BY RANGE(YEAR(b))
SUBPARTITION BY HASH(TO_DAYS(b))
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN maxvalue
);
子分区使用的是 SUBPARTITIONS 关键字，这里是首先对 YEAR（b）进行 RANGE 分区，分成了三个（1990，2000，maxvalue），然后子分区使用 HASH 进行分区，又分成了 2 分，所以总的来说，有 3*2=6 个分区。
我们也可以为每个子分区来定义它的名字。
CREATE TABLE ts2(
a INT,
b DATE
)ENGINE=INNODB
PARTITION BY RANGE(YEAR(b))
SUBPARTITION BY HASH(TO_DAYS(b))(
PARTITION p0 VALUES LESS THAN (1990)(
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000)(
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN maxvalue(
SUBPARTITION s4,
SUBPARTITION s5
)
);
子分区的建立需要注意以下几个问题
每个子分区的数量必须相同
如果在一个分区表的任何分区上使用 SUBPARTITION 来明确定义任何子分区，那么就必须定义所以的子分区，也就是说不可以在一个分区上详细定义了子分区，然后另一个分区不详细定义。
//下面的 SQL 是错的
CREATE TABLE ts(
a int,
b date
)ENGINE=INNODB
PARTITION BY RANGE(YEAR(b))
SUBPARTITION BY HASH(TO_DAYS(b))(
PARTITION P0 VALUES LESS THAN(2019)(
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN(2020)(
SUBPARTITION S2,
SUBPARTITION S3
),
PARTITION p2 VALUES LESS THAN maxvalue
);
每个 SUBPARTITION 子句必须包括子分区的一个名字
子分区的名字必须是唯一的，不可以出现重复
子分区可以用于特别大的表，在多个磁盘间分别分配数据和索引，假设有 3 个磁盘，为/disk0、/disk1、/disk2 等
那么可以使用下面 SQL 进行分配嘛？
CREATE TABLE ts(
a INT,
b DATE
)ENGINE=INNODB
PARTITION BY RANGE(YEAR(b))
SUBPARTITION BY HASH(TO_DAYS(b))(
PARTITION p0 VALUES LESS THAN(2000)(
SUBPARTITION s0,
DATE DIRECTORY '/disk0/data',
INDEX DIRECTORY '/disk0/idx'
),
PARTITION p1 VALUES LESS THAN(2001)(
SUBPARTITION s1,
DATE DIRECTORY '/disk1/data',
INDEX DIRECTORY '/disk1/idx'
),
PARTITION p2 VALUES LESS THAN maxvalue(
SUBPARTITION s2,
DATE DIRECTORY '/disk2/data',
INDEX DIRECTORY '/disk2/idx'
)
)
这句 SQL 是没用的，由于 InnoDB 存储引擎使用表空间自动地进行数据和索引的管理，会忽略 DATA DIRECTORY 和 INDEX DIRECTOR 语法，因此上诉分区表的数据和索引文件分开放置对 InnoDB 存储引擎表是无效的，并不会按照写的代码去进行写入
分区中的 NULL 值
MySQL 数据库允许对 NULL 值进行分区，但是处理方法可能与其他数据库完全不同，MySQL 数据库的分区总是把 NULL 值视为比任何一个非 NULL 值小，对于不同得分区类型，MySQL 数据库对 NULL 值处理也是各不相同的。
对于分区中的 NULL 值，如果向分区列插入了 NULL 值，那么 MySQL 会将它分到最左边的分区，也就是 LESS THAN 最小的那个分区
首先先建立一张表
CREATE TABLE dododi(
a INT,
b INT
)ENGINE=INNODB
PARTITION BY RANGE(b)(
PARTITION p0 VALUES LESS THAN(10),
PARTITION p1 VALUES LESS THAN(20),
PARTITION p2 VALUES LESS THAN maxvalue
);
//插入一些数据
INSERT INTO dododi SELECT 1,1;
INSERT INTO dododi SELECT 1,NULL;
SELECT * FROM dododi;
//查看分区情况
SELECT table_name,partition_name,table_rows FROM information_schema.PARTITIONS WHERE table_schema = DATABASE() AND table_name = "dododi";
可以看到两条数据都被分正在 p0 区上，也就证明了在 RANGE 分区下，NULL 值会被放在最左分区中。因此，在删除 p0 分区时，要记得 NULL 的数据也会被删除。
在 LIST 分区中使用 NULL 时，因为 LIST 是离散型的，所以要在分区中规定 NULL，否则插入时会报错，举个栗子
//创建这个表
CREATE TABLE list_no_null(
a INT,
b INT
)ENGINE=INNODB
PARTITION BY LIST(b)(
PARTITION p0 VALUES IN(1,2,3,4),
PARTITION p1 VALUES IN(5,6,7,8)
);
//进行插入
INSERT INTO list_no_null SELECT 1,NULL;
报错如下
//现在给表加上我们的 NULL 分区
ALTER TABLE list_no_null ADD PARTITION(PARTITION p3 VALUES IN(9,10,NULL));
//再进行插入
INSERT INTO list_no_null SELECT 1,NULL;
//查询分区情况
SELECT table_name,partition_name,table_rows FROM information_schema.PARTITIONS WHERE table_schema = DATABASE() AND table_name = "list_no_null";
可以看到插入进了 p3 分区
HASH 和 KEY 分区对于 NULL 的处理方式与 RANGE 与 LIST 分区都不一样，任何分区函数都会将含有 NULL 值得记录记为 0，即 NULL == 0
//创建表
CREATE TABLE hash_null(
a INT,
b INT
)ENGINE=INNODB
PARTITION BY HASH(b)
PARTITIONS 4;
//插入下面的数据
INSERT INTO hash_null SELECT 1,0;
INSERT INTO hash_null SELECT 2,0;
INSERT INTO hash_null SELECT 1,NULL;
//看看分区情况
SELECT table_name,partition_name,table_rows FROM information_schema.PARTITIONS WHERE table_schema = DATABASE() AND table_name = "hash_null";
可以看到 3 条数据都被放在了 p0 分区上，所以可以得出 NULL == 0
分区与性能
数据库的应用分为两类，一类是 OLTP(联机事务处理)，比如 Blog、电子商务、银行这些与事务挂钩的系统，另一种是 OLAP(联机分析处理)，比如数据仓库、数据集市等这些数据量很大很大的系统。
下面举个栗子再来说明两者的不同
比如在网络游戏中，玩家操作的游戏数据库，比如装备什么的，就是 OLTP，但是游戏厂商可能需要对游戏产生的日志来进行分析，通过分析大量日志的结果，来进行优化，提高游戏体验，这就是 OLAP。
如果对于 OLAP 这种，需要查询大量数据的话，分区的确可以明显地提高查询的性能，因为 OLAP 应用的大多数查询需要频繁地扫描一张很大的表，假设有一张 1 亿多行的表，其中有一个时间戳属性列，需要获取某一年的数据，那么如果使用了分区，按照年来进行分区，那么只需要扫描对应年的分区即可。
但对于 OLTP，就不应该滥用分区，因为对于 OLTP 应用，每个用户的在表中的数据量可能不超过整张表的 10%，大部分都是通过索引来返回几条记录而已，根据 B+树索引原理，高度也就几层，进行 IO 操作大概就 2~3 次即可，因此使用 B+树可以很好地完成对大表的查询操作，根本不需要分区的帮忙，而且，如果分区设置不好，会带来严重的性能问题。
在表和分区间进行交换数据
从 MySQL5.6 开始就支持分区或子分区与另一个非分区的表进行交换数据，如果非分区表的数据为空，那么相当于将分区中的数据剪切到了非分区的表，若分区表为空，就相当于将外部非分区的数据导入进了分区表中。
语句为：
ALTER TABLE 数据来源表 EXCHANGE PARTITION 分区名 WITH TABLE 要移进数据的表;
要使用的话，需要满足下面条件
要交换的表须与分区表有相同的表结构，但是表不能含有分区
非分区表中的数据必须在分区内有定义
被交换的表中不能含有外键，或者其他表中不能含有对该表的外键引用
Auto_increment 的列会被重置
使用该语句时，不会触发交换表和被交换表上的触发器（Intriger）
操作的用户要有 ALTER、INSERT、CREATR 和 DROP 权限
//先建立一个表
CREATE TABLE a(
number INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30)
)ENGINE=INNODB
PARTITION BY RANGE(number)(
PARTITION p0 VALUES LESS THAN(100),
PARTITION p1 VALUES LESS THAN(200),
PARTITION p2 VALUES LESS THAN maxvalue
);
//插入一些数据
最后
由于文案过于长，在此就不一一介绍了，这份 Java 后端架构进阶笔记内容包括：Java 集合，JVM、Java 并发、微服务、SpringNetty 与 RPC 、网络、日志 、Zookeeper 、Kafka 、RabbitMQ 、Hbase 、MongoDB、Cassandra 、Java 基础、负载均衡、数据库、一致性算法、Java 算法、数据结构、分布式缓存等等知识详解。
本知识体系适合于所有 Java 程序员学习，关于以上目录中的知识点都有详细的讲解及介绍，掌握该知识点的所有内容对你会有一个质的提升，其中也总结了很多面试过程中遇到的题目以及有对应的视频解析总结。
还未添加个人签名 2021.11.12 加入
还未添加个人简介
评论