mysql表分区案例详解数据库
2023-06-13 09:20:11 时间
0、整理表空间碎片
optimize table tablename
1、表分区按年分区,季度子分区
alter table key_part partition by range(year(create_time)) subpartition by hash(quarter(create_time)) subpartitions 4 ( partition p0 values less than (2015), partition p2015 values less than (2016), partition p2016 values less than (2017), partition p2017 values less than (2018), partition p2018 values less than (2019), partition p1 values less than maxvalue );
总共产生24个分区,1年4个季度,6年24个季度。
2、按照天分区月表
create_time支持如下日期格式:
%Y-%c-%d
%Y-%c-%d %h:%i:%s
alter table aa partition by range (to_days(create_time)) ( partition p01 values less than (to_days(2018-04-01)) engine = innodb, partition p02 values less than (to_days(2018-04-02)) engine = innodb, partition p03 values less than (to_days(2018-04-03)) engine = innodb, partition p04 values less than (to_days(2018-04-04)) engine = innodb, partition p05 values less than (to_days(2018-04-05)) engine = innodb, partition p06 values less than (to_days(2018-04-06)) engine = innodb, partition p07 values less than (to_days(2018-04-07)) engine = innodb, partition p08 values less than (to_days(2018-04-08)) engine = innodb, partition p09 values less than (to_days(2018-04-09)) engine = innodb, partition p10 values less than MAXVALUE engine = innodb );
explain partitions select * from key_part where create_time = 2018-04-12 and create_time = 2018-04-15
可以看到只遍历了4个分区表,只扫描了4行,而不是扫描所有的行。
explain partitions select * from key_part where create_time = 2018-04-27
可以看到只遍历了5个分区表,只扫描了5行,而不是扫描所有的行。
3、按照年分区表
alter table user partition by linear hash(year(create_time)) partitions 12;
在5.7版本之前,对于data和datetime类型的列,如果要实现分区裁剪,只能使用year() 和to_days()函数,在5.7版本中,又新增了to_seconds()函数。
4、MYSQL的分区字段,必须包含在主键字段内或唯一索引内
这是因为表分区时,分区字段必须包含在主键字段内或唯一索引内,一张表只能有一个主键或一个唯一索引,主键和唯一索引不能同时存在
CREATE TABLE `key_part` ( `news_id` int(11) NOT NULL COMMENT 新闻id, `content` varchar(1000) NOT NULL DEFAULT COMMENT 新闻内容, `u_id` varchar(25) NOT NULL DEFAULT COMMENT 来源ip, `create_time` datetime NOT NULL COMMENT 时间, PRIMARY KEY (`u_id`,`create_time`), KEY `create_time` (`create_time`) USING BTREE, KEY `aa` (`news_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50500 PARTITION BY RANGE COLUMNS(create_time) (PARTITION p01 VALUES LESS THAN (2018-01-01) ENGINE = InnoDB, PARTITION p02 VALUES LESS THAN (2018-03-01) ENGINE = InnoDB, PARTITION p03 VALUES LESS THAN (2018-05-01) ENGINE = InnoDB, PARTITION p04 VALUES LESS THAN (2018-07-01) ENGINE = InnoDB, PARTITION p05 VALUES LESS THAN (2018-09-01) ENGINE = InnoDB, PARTITION p06 VALUES LESS THAN (2018-11-01) ENGINE = InnoDB, PARTITION p07 VALUES LESS THAN (2019-01-01) ENGINE = InnoDB, PARTITION p08 VALUES LESS THAN (2020-01-01) ENGINE = InnoDB, PARTITION p09 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;
5、Columns分区
alter table key_part partition by range columns(create_time) ( partition p01 values less than (2018-01-01) engine = innodb, partition p02 values less than (2018-03-01) engine = innodb, partition p03 values less than (2018-05-01) engine = innodb, partition p04 values less than (2018-07-01) engine = innodb, partition p05 values less than (2018-09-01) engine = innodb, partition p06 values less than (2018-11-01) engine = innodb, partition p07 values less than (2019-01-01) engine = innodb, partition p08 values less than (2020-01-01) engine = innodb, partition p09 values less than maxvalue engine = innodb );
6、添加表分区
-- 对表重新表分区 alter table lot_order_aa partition by range columns(create_time)(partition p03 values less than (2018-03-01)); -- 在已有分区的表上,添加表分区 alter table lot_order_aa add partition (partition p05 values less than (2018-05-01));
原创文章,作者:Maggie-Hunter,如若转载,请注明出处:https://blog.ytso.com/4847.html
mysql相关文章
- [MySQL]解决mysql数据库连接报错:is not allowed to connect to this mysql server
- 从MySQL 到C 语言:构建数据源桥梁(c数据源mysql)
- MySQL字段改名指南(mysql字段改名)
- MySQL数据库主从复制:确保数据安全性(mysql数据库主从复制)
- 优化MySQL系统优化:提升性能的最高境界(mysql系统)
- MySQL中处理时间的技巧:以秒为单位计算(mysql时间秒数)
- 操作MySQL数据库在Mac上的管理操作(mysql数据库mac)
- 构建MySQL外部数据库连接:简单易行(mysql连接外部数据库)
- MySQL: 完全掌握所有数据库命令(mysql所有数据库命令)
- MySQL词法:深入了解MySQL数据库中的语法规则。(mysql词法)
- 掌握MySQL指令,解决数据库问题(mysql.exe指令)
- MySQL静态编译深度剖析(mysql静态编译)
- MySQL实现高效并发写入技术(mysql并发写入)
- MySQL导入导出工具:快速高效实现数据迁移和备份(导入导出工具mysql)
- MySQL 数据库表设计:利用之道(mysql 数据库表设计)
- MySQL修改数据类型的实践指南(mysql修改类型)
- MySQL导出数据库遭遇乱码困扰(mysql导出数据库乱码)
- 数据库CRT快速帮你创建MySQL数据库(CRT怎么创建mysql)
- MySQL数据查询的C语言实现(c mysql数据查询)
- 库ASP实现MySQL数据库的轻松访问(asp读取mysql数据)
- MySQL一行大小的配置及优化技巧(mysql一行大小)
- MySQL路径无法自定义必知问题(mysql不能自定义路径)
- MySQL实现上传和管理图片文件(mysql上传图片个文件)
- Redis 与 MySQL 联动多种策略实现宏观数据优化(redis落地mysql)