mysql表分区案例
2023-09-27 14:29: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'));
相关文章
- MySQL触发器
- 【MySQL】MySQL能使用索引的情况以及创建索引注意事项
- 实践案例丨Pt-osc工具连接rds for mysql 数据库失败
- Mysql-linux下密码修改,忘记密码修改,超级管理用户修改
- Mysql将日期转为字符串
- Linux 忘记了mysql 密码
- MySQL 死锁是怎么产生的?6 个案例分享!
- 使用JDBC连接MySQL数据库--典型案例分析(七)----批量插入员工信息
- 使用JDBC连接MySQL数据库--典型案例分析(四)----更新和插入员工数据
- 使用JDBC连接MySQL数据库--典型案例分析(三)----使用Apach DBCP连接池
- MySQL 数据库如何实现 XA 规范?
- mysql 多实例案例实战
- Mysql综合案例
- MySQL【四】---案例实战{拆分多表、外键创建等}
- MySQL存储过程中in、out、inout参数使用实际案例
- jsp连接mysql案例
- Mysql的索引和慢查询优化
- mysql 5.7 新特性之 json 类型 创建索引
- HIVE 安装系列(3)配置HIVE 使用Mysql作为元数据的数据库
- 第114课:SparkStreaming+Kafka+Spark SQL+TopN+Mysql+KafkaOffsetMonitor电商广告点击综合案例实战(详细内幕版本)
- MySQL性能优化浅析及线上案例
- 最新版本号MYSQL官网下载地址可是必需要注冊后才干下载
- Mysql优化案例一:低效的SQL
- 大数据NiFi(二十):实时同步MySQL数据到Hive
- Mysql悲观锁以及乐观锁案例说明
- Java连接mysql中遇到的一些问题及解决方法
- [unity3d]链接mysql的Project build出错
- 【mysql】数据库表文档一键构建