Mysql的分区表介绍

在mysql中，大多数同学都知道在同一张表中存储过多的数据的时候，会导致查询变慢。然后基于此的话，目前市面上大多都是分库分表的解决方案。这里我们简述下这几个概念：

分库：

把一个数据库拆分成多个库，也就是数据分散在多个数据库实例上。

分表：

把一张表按照一定的规则，分成多个独立存储空间的实体表。对数据的读写都必须按照规则，路由到相应的实体表操作。

分区表：

把一张表的数据分成多个区块，在逻辑上看还是一张表，但是在物理存储上，数据是存储在不同的区块。

基于上述的理解，我们如果在团队规模小，研发人员比较少的情况下，可以考虑做分区表。对于在mysql中使用分区表，有如下的优点：

1. 提高查询性能：通过分区查询，可以快速定位所需数据，降低查询的响应时间，提高了查询性能。
2. 减少索引大小：对于表中特定的字段，可以创建分区索引，这样就可以减少索引的大小，提高索引查询效率。
3. 易于维护：对于单个分区的维护，不会影响其他分区的数据。这样可以缩短故障修复时间，增加系统的可用性。
4. 数据安全：表分区可以将数据分散存储在不同的存储设备或服务器上，增加数据的安全性。

当然有优点的话，那我们就会有对应的缺点，分区表的缺点如下：

1. 需要额外的存储空间：分区的实现需要额外的存储空间。如果分区数很多，那么就需要更多的存储空间来存储分区索引和元数据信息。
2. 分区策略的选择：分区策略的选择也会影响到查询性能的优化效果。需要根据具体情况进行选择。
3. 更加复杂的管理：管理多个分区表需要更加复杂的管理技巧和工具。如果没有好的管理方法，会增加管理的负担和出错的概率。

以上我们对分区表有一些基本的概念了，下面我们就来介绍下mysql中使用分区表的集中形式：

1）range分区

这里的range分区一般常用的主要是根据时间进行分区，例如我们把数据按周进行分区，按月进行分区，按季度进行分区，按年分区等等，使用的关键词主要是：

partition by range
value less than

例如下面我们演示一条sql，把数据按月进行分区：

CREATE TABLE `wallet_consumer_logs` (
  `id` bigint(19) NOT NULL,
  `order_no` varchar(21) DEFAULT NULL COMMENT '流水号',
  `transaction_id` varchar(40) DEFAULT NULL COMMENT '第三方支付流水号',
  `time_bucket_id` bigint(19) DEFAULT NULL COMMENT '时间段id',
  `device_id` bigint(19) DEFAULT NULL COMMENT '设备id',
  `costs_consume_log_id` bigint(19) DEFAULT NULL COMMENT '消费机消费记录id',
  `wallet_user_id` bigint(19) DEFAULT NULL COMMENT '用户钱包id',
  `wallet_type` int(11) DEFAULT NULL COMMENT '钱包类型，0：余额钱包，1、补贴钱包',
  `wallet_user_balance` decimal(10,2) DEFAULT NULL COMMENT '扣款前的余额',
  `op_type` tinyint(1) DEFAULT NULL COMMENT '1:充值;2:减款;3:消费;4:补贴;5:退款;6补扣;7余额清理',
  `op_user` varchar(30) DEFAULT NULL COMMENT '操作用户',
  `amount` decimal(10,2) DEFAULT NULL COMMENT '本次操作金额',
  `user_relation_id` varchar(19) DEFAULT NULL COMMENT '消费者用户id 云校园 用户表字段',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `school_id` bigint(19) DEFAULT NULL COMMENT '学校id',
  `branch_id` bigint(19) unsigned DEFAULT NULL COMMENT '校区id',
  `dept_id` bigint(19) DEFAULT NULL COMMENT '部门id',
  PRIMARY KEY (`id`,`create_time`) USING BTREE,
  KEY `idx_school_id` (`school_id`) USING BTREE,
  KEY `idx_branch_id` (`branch_id`) USING BTREE,
  KEY `idx_dept_id` (`dept_id`) USING BTREE,
  KEY `idx_user_relation_id` (`user_relation_id`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE,
  KEY `idx_costs_consume_log_id` (`costs_consume_log_id`) USING BTREE,
  KEY `idx_device_id` (`device_id`) USING BTREE,
  KEY `idx_wallet_user_id` (`wallet_user_id`) USING BTREE,
  KEY `idx_op_type` (`op_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='钱包的所有消费记录日志表'
PARTITION BY RANGE (to_days(create_time))
(
     PARTITION p202306 VALUES LESS THAN (to_days('2023-07-01')),
     PARTITION p202307 VALUES LESS THAN (to_days('2023-08-01')),
     PARTITION p202308 VALUES LESS THAN (to_days('2023-09-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

这里我们是把时间精确到天，然后p202306这个名称的分区存放小于2023年7月1日的数据，p202307这个名称的分区存放大于2023年7月1日小于2023年8月报1号的数据。后面的名称依次类推。

在mysql中没有提供动态分区的功能，因此这里我们需要提前建立好所有的分区，如果插入数据的时候找不到对应的分区，那么就会直接抛错，所以一般我们会添加一个less than maxvalue来作为备份，这样子当招不到合适的分区的时候，会把数据主要放到这个pmax这个分区里面去。

2）list分区

这里list分区主要的场景是一些以枚举类为案例的场景，例如我们做智慧城市的话，由于数据量比较大，我们可以按区县进行枚举，使用list进行分区。mysql中使用list分区对应的关键字有：

partition by list
in

下面我们列举一个案例：

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `sex` tinyint(1) DEFAULT NULL COMMENT '性别',
  `address` varchar(255) DEFAULT NULL COMMENT '家庭地址',
  `area` tinyint(2) DEFAULT NULL COMMENT '所属区县',
  PRIMARY KEY (`id`, `area`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION by list(area)
(
partition p1 values in (1),
partition p2 values in (2),
partition p3 values in (3),
partition p4 values in (4),
partition p5 values in (5),
partition p6 values in (6),
partition p7 values in (7),
partition p8 values in (8),
partition p9 values in (9),
partition p10 values in (10)

)

这里的案例我们使用in的话，里面就是area这个区县字段的枚举信息。

3）hash

有时候我们仅仅只是为了分区减少数据的存储分散，没有太多明确的业务，因此这里的话，我们可以使用hash进行分区，使用hash进行分区的话，涉及到的关键词有：

partition by hash
partitions

下面列举个使用hash创建分区的案例：

CREATE TABLE product (
    id INT NOT NULL,
    product_name VARCHAR(64),
    product_type INT
)
PARTITION BY HASH(id)
PARTITIONS 4;

使用hash创建分区的时候比较简单，指定一个需要hash的字段，再指定想要的分区数量即可。

以上就是我们常用的在mysql中使用分区表的常用案例。

备注：

1、MySQL 的分区字段，必须包含在主键字段或者唯一索引列中，也就是分区的字段必须全部都属于主键或者唯一索引列的一部分或者全部，否则创建分区将直接报错。

2、分区也是大表优化的一个可选方案。

3、分区表适合做大量写入，不适合做大量更新操作。