MySQL_17_分区表
@
分区表
分区表：表中数据按照指定方式存储于磁盘的多个位置（可跨文件系统）
1）作用：将数据按照较粗的粒度进行归类，并提高硬件的效率；
2）适用场景：表中数据过多和只有部分热点数据时；
3）分区表的底层表由句柄对象再次封装；
//不能仅对表中部分数据进行分区
句柄对象（Handler Object）：每个底层表在硬件中的标识
1）对分区表的所有请求都会通过句柄对象转化成对存储引擎接口的调用；
2）分区表中所有的所有分区都需使用相同类型的存储引擎；
3）删除分区时会同时删除表中属于该分区的数据
//删除大量数据时，可直接删除该分区
分区表的索引须知：
1）若分区字段中含有主键或唯一索引的列，则需包含所有主键列和唯一索引列
2）分区表的索引只是在各个底层表上各自加上个完全相同的索引
分区须知
分区表须知：
1）NULL值会使分区过滤失效；
2）分区表的所有底层表都需使用相同的存储引擎；
3）每个表最多有1024个分区（建议限制100以内）；
4）分区列和索引列不匹配时，会导致无法进行分区过滤；
5）不同分区类型实现分区的方式不同，其维护成本也不相同；
6）对所有底层表上锁后才会过滤分区（可通过批量操作改善）；
使用分区表时应遵循以下其中一个策略：
（1）全量扫描数据，舍弃所有索引
1）说明：将表中数据以普通分区方式存储，并不使用任何索引
2）分区数量需限制在很小的数量，同时所有数据假设不能完全加载至内存
（2）使用索引，并分离热点数据
1）说明：将热点数据分配于特殊的分区中，并配有索引
2）该特殊的分区会被始终加载至内存，同时保证有效的使用缓存
执行流程
分区表中进行各种操作的流程：
（1）SELECT查询
1）分区层打开并锁住所有底层表；
2）优化器根据查询条件过滤部分分区；
3）调用对应存储引擎接口访问各个分区的数据；
//底层表的锁类型并非是表锁，视存储引擎而决定（InnoDB为行级锁）
//优化器会根据分区的定义在查询时过滤部分分区（需指定WHERE语句）
（2）INSERT插入
1）分区层打开并锁住所有底层表；
2）根据分区函数确定该条数据属于的分区；
3）将数据插入分区对应的底层表；
//底层表的锁类型并非是表锁，视存储引擎而决定（InnoDB为行级锁）
（3）DELETE删除
1）分区层打开并锁住所有底层表；
2）根据分区函数确定该条数据属于的分区
3）将数据从分区对应的底层表中删除；
//底层表的锁类型并非是表锁，视存储引擎而决定（InnoDB为行级锁）
（4）UPDATE更新
1）分区层打开并锁住所有底层表；
2）根据分区函数确定该条数据属于的新旧分区；
3）将更新的数据写入新分区对应的底层表，同时删除旧分区的原数据；
//底层表的锁类型并非是表锁，视存储引擎而决定（InnoDB为行级锁）
分区类型
分区类型：通过分区表达式限定分区表如何规划每个分区
1）分区类型：RANGE、LIST、HASH、KEY、COLUMNS
2）创建表时同时创建分区的标准格式：
CREATE TABLE 表名 (
...
...
...
) 其他约束 ENGINE=引擎类型 PARTITION BY 分区类型 (
分区配置1,
分区配置2,
分区配置N
);
分区表达式：用于区分不同分区的判定
1）分区表达式的限制：返回值值必须是整数、不能是常数、不能包含查询
2）分区表达式可以是：列、含列的表达式（使用函数）
分区函数：通过分区表达式的返回值确定分区编号以选择分区
1）无论何种类型的分区在被创建时都会获得个整数编号（从0开始）
2）在操作数据时也会调用分区函数以确定分区编号
RANGE
RANGE（范围）：基于分区表达式和数值范围规定每行数据所属分区
1）分区表达式的值需位于给定的所有数值范围之中；
2）每个分区指定的数值需连续，且不能重叠；
3）最后的“MAXVALUE”用于存储特殊值；
PARTITION BY RANGE(表达式) (
PARTITION 分区名1 VALUES LESS THAN (数值1),
PARTITION 分区名2 VALUES LESS THAN (数值2),
PARTITION 分区名N VALUES LESS THAN MAXVALUE
);
LIST
LIST（列表）：基于分区表达式和数值集合规定每行数据所属分区
1）分区表达式的值需位于给定的所有数值集合之中；
2）每个分区指定的数值无须按照特定顺序，但不可重叠；
3）若操作数据不属于任何分区的数值集合，则执行失败；
PARTITION BY LIST(表达式) (
PARTITION 分区名1 VALUES IN (数值, 数值),
PARTITION 分区名N VALUES IN (数值, 数值)
);
HASH
HASH（哈希）：基于分区表达式和Hash函数规定每行数据所属分区
1）Hash的分区表达式应简易，且涉及较少的列（每次数据操作都需计算）；
2）由系统确保数据会平均分配到预定数目的分区中；
3）不可指定分区名，只能预定分区数目；
PARTITION BY HASH(表达式)
PARTITION 数目N;
LINER HASH（线性哈希）：在HASH的基础上使用线性的2的幂运算
1）优点：增加、删除、合并和拆分分区时更快捷；
2）缺点：数据分布于各个区间的均衡性较差；
PARTITION BY LINER HASH(表达式)
PARTITION 数目N;
KEY
KEY：在HASH的基础上使用系统提供的Hash函数
1）分区表达式只能是单个或多个列的列名
2）不同存储引擎提供的Hash函数不同；
PARTITION BY KEY(表达式)
PARTITION 数目N;
COLUMNS
COLUMNS：基于分区表达式和列规定每行数据所属分区
1）分区表达式只能是单个列名；
2）其支持整型、date和datetime类型；
PARTITION BY RANGE COLUMNS(列名) (
PARTITION 区名1 VALUES LESS THAN (值1),
PARTITION 区名N VALUES LESS THAN (值N)
);
管理分区
创建分区：
ALTER TABLE 表名
PARTITION BY 分区类型 (分区配置);
增加分区：
1）添加分区时需注意是否会与已有分区发生冲突（导致失败）；
ALTER TABLE 表名
ADD PARTITION (分区配置);
拆分/合并分区：
1）分区名指定的是已存在分区则进行的拆分操作（反之是合并）；
ALTER TABLE 表名
REORGANIZE PARTITION 分区名 INTO (
分区配置
)
删除分区：
ALTER TABLE 表名
DROP PARTITION 分区名;
分区表易于维护，可单独对分区进行检查、修复、优化和删除等操作（但分区的维护成本可能很高，涉及到大量IO）
(1)检查分区
ALTER TABLE 表名
CHECK PARTITION (分区);
(2)重建分区
ALTER TABLE 表名
REBUILD PARTITION (分区1, 分区N);
(3)优化分区
ALTER TABLE 表名
OPTIMIZE PARTITION (分区1, 分区N);
(4)修复分区
ALTER TABLE 表名
REPAIR PARTITION (分区1, 分区N);
