MySql 5.7.1 分区的实践

Syspan

已于 2024-08-08 15:52:01 修改

阅读量429

点赞数 5

文章标签： mysql 数据库

于 2024-08-08 15:46:32 首次发布

本文链接：https://blog.csdn.net/qq_40729514/article/details/141027326

版权

在性能优化中，Mysql 进行分区，能有效提高查询效率，因此开始百度了起来。但是结果总不是那么一番风顺的。如今使用 uuid 作为主键的情况已是主流，因此在给表增加分区时，出现了如下错误：

错误： A PRIMARY KEY must include all columns in the table's partitioning function

PRIMARY KEY必须包含表分区函数中的所有列

这表明 MySQL 需要分区列成为主键的一部分。我们需要将 uid 和 create_time 组合成一个复合主键。对没看错，复合主键，也就是两字段确定唯一（这对我们的业务来说不是很友好，看到这里其实已经可以不用考虑了，毕竟生产环境的表结构和业务是不能这样改的。本着好奇和学习的心态，我还是进行了测试）。

因此我们创建表的代码如下：根据创建时间月份创建为12 个分区。

CREATE TABLE t_test (
    uid VARCHAR(32) NOT NULL,
    capital_uid VARCHAR(32) NOT NULL,
    type VARCHAR(32) NOT NULL,
    amount DECIMAL(12,4) NOT NULL,
    explain2 VARCHAR(255) NOT NULL,
    source VARCHAR(32) NOT NULL,
    source_type VARCHAR(32) NOT NULL,
    source_uid VARCHAR(32) NOT NULL,
    source_extra VARCHAR(255) DEFAULT NULL,
    settlement_uid VARCHAR(32) NOT NULL,
    liquidation_state VARCHAR(32) NOT NULL,
    verify VARCHAR(32) NOT NULL,
    create_time DATETIME NOT NULL,
    update_time DATETIME DEFAULT NULL,
    remark VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (uid, create_time)
)
PARTITION BY HASH (MONTH(create_time))
PARTITIONS 12;

创建成功后我们可以通过下面的语句查看创建的分区（PS：这段sql代码是百度来的）

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
FROM information_schema.`PARTITIONS` WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='这里是你的表名'

执行后就能看到分区的内容，和每个分区的数据。（具体的本人也灭有去研究过）

当然如果你的表已经存在可以通过下面的方式去修改主键，然后创建复合主键（请注意：在这里操作前记得备份）

alter table test_order DROP PRIMARY KEY, ADD PRIMARY key(uid,create_time);

这个时候对应的索引，和唯一键也需要相关的调整。具体根据业务来定；重建复合主键后我们就可以对现有的表和数据进行分区操作

ALTER TABLE t_test
PARTITION BY HASH (MONTH(create_time))
PARTITIONS 12;

然后分区就完成了。最后我简单的进行了测试，40w+ 数据其实分区了以后查询效果提升也不是很明显。还是索引来的实在，而且对于这种复合主键的方式，我个人认为对业务是存在风险的，也是不推荐的。（分区以后数据底层查询的原理可以去百度了解下，这里就不做过多的阐述了）

当然如果你的表结构主键为 long、int 且自增，那么可以考虑进行分区。不会受到这个错误的影响，也不需要创建复合主键，直接使用主键进行分区，下面是一个简单的示例：

-- 创建分区表
create table t_Aa (

	ID int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键' PRIMARY KEY,
    NAME varchar(255) NOT NULL COMMENT '姓名',
    IDCARD varchar(255) NOT NULL COMMENT '身份证号码',
    PHONE varchar(255) DEFAULT NULL COMMENT '手机号码',
    CREATE_TIME datetime NOT NULL COMMENT '创建时间',
    MOD_TIME datetime DEFAULT NULL COMMENT '修改时间'

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
partition by range columns (ID) (
    
    -- 按id 进行区分，10 条数据为一个分区
	partition `P0` values less than (100000),
    partition `P1` values less than (200000),
    partition `P2` values less than (300000),
    partition `P3` values less than (400000),
    partition `P4` values less than (500000),
	partition `P5` values less than MAXVALUE
)

最后关于分区，怎么分区，通过什么字段去分区还是要根据实际场景和业务去决定，不然我觉得是没有太大意义的，物极必反。

对于分区这就是我自己的一点总结，写这篇文章的内容仅是为了记录，也希望他能帮到你。如果有什么不对的地方或者更好的分区方式，欢迎指出留言！