MySQL分区表使用方法

共计 4502 个字符，预计需要花费 12 分钟才能阅读完成。

1. 确认 MySQL 服务器是否支持分区表

命令：

show plugins;

2. MySQL 分区表的特点

• 在逻辑上为一个表，在物理上存储在多个文件中

HASH 分区（HASH）

HASH 分区的特点

• 根据 MOD(分区键，分区数)的值把数据行存储到表的不同分区中
• 数据可以平均的分布在各个分区中
• HASH 分区的键值必须是一个 INT 类型的值，或是通过函数可以转为 INT 类型

如何建立 HASH 分区表

以 INT 类型字段 customer_id 为分区键

CREATE TABLE `customer_login_log` (`customer_id` int(10) unsigned NOT NULL COMMENT '登录用户 ID',
  `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录 IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0 未成功 1 成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户登录日志表'

PARTITION BY HASH(customer_id)  PARTITIONS 4;

以非 INT 类型字段 login_time 为分区键(需要先转换成 INT 类型)

CREATE TABLE `customer_login_log` (`customer_id` int(10) unsigned NOT NULL COMMENT '登录用户 ID',
  `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录 IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0 未成功 1 成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户登录日志表'

PARTITION BY HASH(UNIX_TIMESTAMP(login_time))  PARTITIONS 4;

customer_login_log 表如果不分区，在物理磁盘上文件为

customer_login_log.frm    # 存储表原数据信息
customer_login_log.ibd    # Innodb 数据文件

如果按上面的建 HASH 分区表，则有五个文件

customer_login_log.frm    
customer_login_log#P#p0.ibd
customer_login_log#P#p1.ibd
customer_login_log#P#p2.ibd
customer_login_log#P#p3.ibd

演示

使用起来和不分区是一样的，看起来只有一个数据库，其实有多个分区文件，比如我们要插入一条数据，不需要指定分区，MySQL 会自动帮我们处理

查询

范围分区（RANGE）

RANGE 分区特点

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

如何建立 RANGE 分区

如果没有定义 p3 分区，当插入的 customer_id 大于 29999 时会报错，定义了则超过的数据都存入 p3 中

RANGE 分区的适用场景

• 分区键为日期或是时间类型（可以使得各个分区表的数据比较均衡，如果按上面的例子中以整型 id 为分区键，假如活跃用户集中在 10000-19999 之间，则 p1 中的数据量就会比其他分区的数据量大很多，这就失去了分区的意义；而且按时间类型分区，如果要按时间顺序进行数据的归档，则只需要对某一个分区进行归档就可以了）
• 所有查询中都包括分区键（避免跨分区查询）
• 定期按分区范围清理历史数据

LIST 分区

LIST 分区的特点

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

如何建立 LIST 分区

如果插入一条 login_type 为 10 的数据行，则会报错

3. 如何为登录日志表（customer_login_log）分区

业务场景

• 用户每次登录都会记录 customer_login_log 日志
• 用户登录日志保存一年，1 年后可以删除或者归档

登录日志表的分区类型及分区键

• 使用 RANGE 分区
• 以 login_time 为分区键

分区后的用户登录日志表

按年份分区存储，所以用 YEAR 函数进行了转化

CREATE TABLE `customer_login_log` (`customer_id` int(10) unsigned NOT NULL COMMENT '登录用户 ID',
  `login_time` DATETIME NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录 IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0 未成功 1 成功'
) ENGINE=InnoDB 
PARTITION BY RANGE (YEAR(login_time))(PARTITION p0 VALUES LESS THAN (2017),
PARTITION p1 VALUES LESS THAN (2018),
PARTITION p2 VALUES LESS THAN (2019)
) 

插入并查询数据

查询指定表中的分区数据情况

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

再插入 2 条 18 年的日志，会存入 p2 表中

之前说过建立分区表时，最好建立一个 MAXVALUE 的分区，这里之所以没有建立，是为了数据维护的方便，如果我们建立了 MAXVALUE 分区，很容易忽视一个问题，当我们 2019 年有的数据插入时，会自动存入那个 MAXVALUE 分区中，之后在做数据维护时会不方便，所以没有建立 MAXVALUE 分区
而是通过计划任务的方式，在每年年底的时候增加这个分区，比如我们现在在 2018 年年底，我们需要在日志表中为 2019 年建立日志分区，否则 2019 年的日志都会插入失败

我们可以通过下面语句

增加分区

ALTER TABLE customer_login_log ADD PARTITION (PARTITION p3 VALUES LESS THAN(2020))

增加分区，并插入数据

删除分区

假如我们现在要删除 2016 年到 2017 年间一年的数据，因为我们已经做了分区，所以只需要通过一条语句，删除 p0 分区即可

ALTER TABLE customer_login_log DROP PARTITION p0;

可以发现 p0 分区已被删除，且 2016 年的日志全部被清除了

归档分区历史数据

我们可能有另一种需求对数据进行归档

Mysql 版本 >=5.7，归档分区历史数据非常方便，提供了一个交换分区的方法

分区数据归档迁移条件：

1. MySQL>=5.7
2. 结构相同
3. 归档到的数据表一定要是非分区表
4. 非临时表；不能有外键约束
5. 归档引擎要是：archive

建表并交换分区

CREATE TABLE `arch_customer_login_log` (
  `customer_id` INT unsigned NOT NULL COMMENT '登录用户 ID',
  `login_time` DATETIME NOT NULL COMMENT '用户登录时间',
  `login_ip` INT unsigned NOT NULL COMMENT '登录 IP',
  `login_type` TINYINT NOT NULL COMMENT '登录类型:0 未成功 1 成功'
) ENGINE=InnoDB ;

ALTER TABLE customer_login_log 
    exchange  PARTITION p1 WITH TABLE arch_customer_login_log;

可以发现，原 customer_login_log 表中的 2017 年的数据（p1 分区中的数据）已转移到了 arch_customer_login_log 表中，但是 p1 分区未删除，只是数据转移了, 所以我们还需要执行 DROP 命令删除分区，以免有数据插入其中

将归档数据的存储引擎改为归档引擎

最后我们将归档数据的存储引擎改为归档引擎，命令为

ALTER TABLE customer_login_log  ENGINE=ARCHIVE;

使用归档引擎的好处是：它比 Innodb 所占用的空间更少，但是归档引擎只能进行查询操作，不能进行写操作

4. 使用分区表的主要事项

• 结合业务场景选择分区键，避免跨分区查询
• 对分区表进行查询最好在 WHERE 从句中包含分区键
• 具有主键或唯一索引的表，主键或唯一索引必须是分区键的一部分（这也是为什么我们上面分区时去掉了主键登录日志 id(login_id)的原因，不然就无法按照上面的按年份进行分区，所以分区表其实更适合在 MyISAM 引擎中）

关于 MyISAM 和 Innodb 的索引区别

1. 关于自动增长

myisam 引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。

innodb 引擎的自动增长咧必须是索引，如果是组合索引也必须是组合索引的第一列。

2. 关于主键

myisam 允许没有任何索引和主键的表存在，

myisam 的索引都是保存行的地址。

innodb 引擎如果没有设定主键或者非空唯一索引，就会自动生成一个 6 字节的主键(用户不可见)

innodb 的数据是主索引的一部分，附加索引保存的是主索引的值。

3. 关于 count()函数

myisam 保存有表的总行数，如果 select count(*) from table; 会直接取出出该值

innodb 没有保存表的总行数，如果使用 select count(*) from table；就会遍历整个表，消耗相当大，但是在加了 wehre 条件后，myisam 和 innodb 处理的方式都一样。

4. 全文索引

myisam 支持 FULLTEXT 类型的全文索引

innodb 不支持 FULLTEXT 类型的全文索引，但是 innodb 可以使用 sphinx 插件支持全文索引，并且效果更好。（sphinx 是一个开源软件，提供多种语言的 API 接口，可以优化 mysql 的各种查询）

5.delete from table

使用这条命令时，innodb 不会从新建立表，而是一条一条的删除数据，在 innodb 上如果要清空保存有大量数据的表，最 好不要使用这个命令。(推荐使用 truncate table，不过需要用户有 drop 此表的权限)

6. 索引保存位置

myisam 的索引以表名 +.MYI 文件分别保存。

innodb 的索引和数据一起保存在表空间里。

：