《高性能MySQL》第五章设计数据库分区表

一、数据库设计三范式

第一范式：数据库表中的字段都是单一属性，不可再分。
第二范式：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖。
第三范式：数据表中不能存在非关键字段对任一候选关键字段的传递函数依赖。

翻译过来即：

第一范式：表必须有主键，且每个字段原子性不可再分。
第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖
第三范式：所有非主键字段和主键字段之间不能产生传递依赖

二、MySQL 分区表

1. 检查 MySQL 是否支持分区功能

-- 查看MySQL 中的所有插件，如果存在 Name=partition；Status=ACTIVE，则表示支持分区功能
SHOW PLUGINS;
分区表的特点：在逻辑上为一个表，在物理上存储在多个文件中。

2. 分区表的使用方法

注：PARTITION BY 必须是主键HASH之后，而不能是其它属性。

在插入和查询时，与正常使用一致。

3. 分区的几种方式

1. HASH 分区

根据 MOD(分区键、分区数) 的值把数据行存储到表的不同分区中
数据可以平均的分布在各个分区中
HASH 分区的键值必须是一个 INT 类型的值，或是通过函数可以转为 INT 类型
1. 如果属性为整型，则可以 eg: PARTITION BY HASH(customer_id)
2. 如果属性为日期，则转换为整型后进行HASH：eg: PARTITION BY HASH(UNIX_TIMESTAMP(login_time))

eg: 对用户登录日志表进行分区，分区规则为 customer_id 的 hash 值，并分为4个区

CREATE TABLE customer_login_log(
 customer_id INT UNSIGNED NOT NULL COMMENT '登录用户ID',
 login_time TIMESTAMP NOT NULL COMMENT '用户登录时间',
 login_ip INT UNSIGNED NOT NULL COMMENT '登录IP',
 login_type TINYINT NOT NULL COMMENT '登录类型：0 未成功，1 成功'
) engine = innodb DEFAULT CHARSET=utf8 COMMENT '用户登录日志表'
PARTITION BY HASH(customer_id)
PARTITIONS 4;

创建 HASH 分区表时可以使用到的函数

ABS()	CEILING()	DAY()
DAYOFMONTH()	DAYOFWEEK()	DAYOFYEAR()
DATEDIFF()	EXTRACT()	FLOOR()
HOUR()	MICROSECOND()	MINUTE()
MOD()	MONTH()	QUARTER()
SECOND()	TIME_TO_SEC()	TO_DAYS()
TO_SECONDS()	UNIX_TIMESTAMP()	WEEKDAY()
YEAR()		YEARWEEK()

2. RANGE 分区

按照范围分区，特点：

根据分区键值的范围把数据行存储到表的不同分区中
多个分区的范围要连续，但是不能重叠
默认情况下使用 VALUES LESS THAN 属性，即每个分区不包含指定的那个值

eg: 对用户id 进行分区，p0分区为 0~~9999，依次类推，p3分区为 3000~~max

CREATE TABLE customer_login_log(
 customer_id INT UNSIGNED NOT NULL COMMENT '登录用户ID',
 login_time TIMESTAMP NOT NULL COMMENT '用户登录时间',
 login_ip INT UNSIGNED NOT NULL COMMENT '登录IP',
 login_type TINYINT NOT NULL COMMENT '登录类型：0 未成功，1 成功'
) engine = innodb DEFAULT CHARSET=utf8 COMMENT '用户登录日志表'
PARTITION BY RANGE (customer_id) (
    PARTITION p0 VALUES LESS THAN (10000),
    PARTITION p1 VALUES LESS THAN (20000),
    PARTITION p2 VALUES LESS THAN (30000),
    PARTITION p3 VALUES LESS THAN MAXVALUE
)

RANGE 分区的适用场景：

分区键为日期或是时间类型
1. 时间归档
所有查询中都包括分区键
定期按分区范围清理数据

3. List 分区

List 分区的特点：

按分区键取值的列表进行分区
同范围分区一样，各分区的列表值不能重复
每一行数据必须能找到对应的分区列表，否则数据插入失败

如何建立 LIST 分区：

CREATE TABLE customer_login_log(
 customer_id INT UNSIGNED NOT NULL COMMENT '登录用户ID',
 login_time TIMESTAMP NOT NULL COMMENT '用户登录时间',
 login_ip INT UNSIGNED NOT NULL COMMENT '登录IP',
 login_type TINYINT NOT NULL COMMENT '登录类型：0 未成功，1 成功'
) engine = innodb DEFAULT CHARSET=utf8 COMMENT '用户登录日志表'
PARTITION BY LIST (login_type) (
  PARTITION p0 VALUES IN (1,3,5,7),
  PARTITION p1 VALUES IN (2,4,6,8)
)

4. 分区表的常见操作

查询分区信息表

SELECT 
 table_name,
 partition_name,
 partition_description,
 table_rows
FROM 
 information_schema.`PARTITIONS`
WHERE table_name = 'customer_login_log';

新增分区

ALTER TABLE customer_login_log ADD PARTITION (PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2021-01-01 00:00:00')))

删除分区及其中的数据

ALTER TABLE customer_login_log DROP PARTITION p0;

分区数据归档迁移：
1. 条件：
  1. MySQL >= 5.7
  2. 结构相同
  3. 归档到的数据表一定要是非分区表
  4. 非临时表；不能有外键约束
  5. 归档引擎要是：archive
    1. 无法从该表中删除数据，仅能添加数据
    2. 占用的空间更小
2. 创建数据归档表，用于存储用户登录日志的归档数据
  1. ```
  CREATE TABLE arch_customer_login_log(
   customer_id INT UNSIGNED NOT NULL COMMENT '登录用户ID',
   login_time TIMESTAMP NOT NULL COMMENT '用户登录时间',
   login_ip INT UNSIGNED NOT NULL COMMENT '登录IP',
   login_type TINYINT NOT NULL COMMENT '登录类型：0 未成功，1 成功'
  ) ENGINE = ARCHIVE COMMENT '用户登录日志表归档'
```
3. 将 p1 分区(2018年用户的登录日志) 迁移到归档表中
  1. ```
  ALTER TABLE customer_login_log 
  EXCHANGE PARTITION p1 
  WITH TABLE arch_customer_login_log;
```
4. 此时再查看 customer_login_log，已不存在 p1 分区中的数据了，全部转移到了 arch_customer_login_log 中。
5. 迁移完成，删除 customer_login_log 中的 p1 分区
  1. ```
  ALTER TABLE customer_login_log DROP PARTITION p1;
```

一、数据库设计三范式

二、MySQL 分区表

1. 检查 MySQL 是否支持 分区功能

2. 分区表的使用方法

3. 分区的几种方式

1. HASH 分区

2. RANGE 分区

按照范围分区，特点：

RANGE 分区的适用场景：

3. List 分区

List 分区的特点：

如何建立 LIST 分区：

4. 分区表的常见操作

《高性能MySQL》目录

1. 检查 MySQL 是否支持分区功能