mysql 表分区

最新推荐文章于 2024-02-27 14:50:09 发布

zqinghai

最新推荐文章于 2024-02-27 14:50:09 发布

阅读量615

点赞数

文章标签： mysql

本文链接：https://blog.youkuaiyun.com/zqinghai/article/details/55050016

版权

本文介绍了在MySQL中对大型中间表进行优化的一种方法——表分区，特别是时间分区。通过创建按月分区的表，以提高数据统计效率。文章详细讲述了如何在已有表的基础上添加分区，包括建表时添加分区和后期追加分区的步骤，并提到了新增、删除分区的操作。此外，还分享了查询分区表的相关命令，以及分区带来的快速删除数据等优势。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据统计：中间表太大优化的出路分区

按照时间分区，一个月一个区，关键是前期数据已经跑了2年多了。要重新创建带有分区的表。

总结一下里面的坑：

1、建表：

分区必须根表一根创建；如果表已经建好，那就麻烦一点。

（1）分区和表同时创建

CREATE TABLE IF NOT EXISTS `table_data_range` (
`dataid` int(11) NOT NULL,
`product_sk` int(11) NOT NULL,
`deviceos_sk` int(11) NOT NULL,
`deviceresolution_sk` int(11) NOT NULL,
`devicelanguage_sk` int(11) NOT NULL,
`devicebrand_sk` int(11) NOT NULL,
`devicesupplier_sk` int(11) NOT NULL,
`location_sk` int(11) NOT NULL,
`date_sk` int(11) NOT NULL,
`deviceidentifier` varchar(256) NOT NULL,
`clientdataid` int(11) NOT NULL,
`network_sk` int(11) NOT NULL,
`hour_sk` int(11) NOT NULL,
`isnew` tinyint(4) NOT NULL DEFAULT '1',
`isnew_channel` tinyint(4) NOT NULL DEFAULT '1',
`useridentifier` varchar(256) DEFAULT NULL,
   PRIMARY KEY(dataid, date_sk)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE(date_sk) (
    PARTITION p_2014 VALUES LESS THAN (20150101),
    PARTITION p_201506 VALUES LESS THAN (20150701),
    PARTITION p_201512 VALUES LESS THAN (20160101)

);

备注：分区字段必须在主键范围内

（2）如果表已经创建，后期再添加分区，操作方式：

（备注：如果想在已经建好的表上进行分区，如果使用alter添加分区的话，mysql会提示错误：
ERROR 1505 <HY000> Partition management on a not partitioned table is not possible ）

步骤1：创建和之前表结构一致但带有分区表的数据表；

步骤2：然后用insert into 分区表 select * from 原始表;

2、新增分区，删除分区
alter table table_name add partition(partition p_2016 values less than (20170101)); ---新增分区
alter table table_name DROP partition p_2016; ----删除分区

MAXVALUE,创建分区时若添加MAXVALUE，后面就不可新增分区；

新增分区的范围只能往后添加；

添加失败：alter table table_name add partition(partition p_2016 values less than (20140101)); ---新增分区

3、查询语句

show plugins; ---在mysql控制台中执行，检查数据是否支持分区

Show create tabe table_name;      //表详细结构;
show table status;     //表的各种参数状态;
explain partitions select * from table_name ;   // 通过此语句来显示扫描哪些分区，及他们是如何使用的;

select count(*) from table_data_range PARTITION(p_2014)

分区的优点：即需求
（1）删除分区速度快，删除数据快速