MySQL水平分区表实际操作总结
来源:互联网 发布:linux 用户权限设置 编辑:程序博客网 时间:2024/06/10 12:18
转载自:http://shuailan.iteye.com/blog/1056247
本文总结个这段时间研究MySQL水平分区表总结,列举分区表的相关操作.
在网上看了很多文章,都太过于概念,注意集中在介绍分区表的优点,而不注重时间操作,跟大学教授论文似的,唯一由于的一片文章和大家分享一下吧http://fanqiang.chinaunix.net/db/mysql/2006-05-08/4135.shtml
MySQL分区表操作代码 (本案例按月分区):
1. 创建分区表
- CREATE TABLE `表名` (
- `EQUIPMENTID` char(17) NOT NULL,
- `ATTRIBUTEID` char(4) NOT NULL,
- `VALUE` varchar(20) NOT NULL,
- `COLLECTTIME` datetime NOT NULL
- )
- ENGINE=InnoDB (适用大部分引擎,可根据需要调整)
- DEFAULT CHARSET=latin1(编码可根据需要修改)
- PARTITION BY RANGE (to_days(COLLECTTIME))
- (PARTITION pmin VALUES LESS THAN (to_days('2010-01-01')),
- PARTITION p201001 VALUES LESS THAN (to_days('2010-02-01')) ,
- PARTITION p201002 VALUES LESS THAN (to_days('2010-03-01')) ,
- PARTITION p201003 VALUES LESS THAN (to_days('2010-04-01')) ,
- PARTITION p201004 VALUES LESS THAN (to_days('2010-05-01')) ,
- PARTITION p201005 VALUES LESS THAN (to_days('2010-06-01')) ,
- PARTITION p201006 VALUES LESS THAN (to_days('2010-07-01')) ,
- PARTITION p201007 VALUES LESS THAN (to_days('2010-08-01')) ,
- PARTITION p201008 VALUES LESS THAN (to_days('2010-09-01')) ,
- PARTITION p201009 VALUES LESS THAN (to_days('2010-10-01')) ,
- PARTITION p201010 VALUES LESS THAN (to_days('2010-11-01')),
- PARTITION p201011 VALUES LESS THAN (to_days('2010-12-01')),
- PARTITION p201012 VALUES LESS THAN (to_days('2011-01-01')),
- PARTITION p201101 VALUES LESS THAN (to_days('2011-02-01')),
- PARTITION p201102 VALUES LESS THAN (to_days('2011-03-01')),
- PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')),
- PARTITION p201104 VALUES LESS THAN (to_days('2011-05-01')),
- PARTITION p201105 VALUES LESS THAN (to_days('2011-06-01')),
- PARTITION p201106 VALUES LESS THAN (to_days('2011-07-01')),
- PARTITION p201107 VALUES LESS THAN (to_days('2011-08-01')),
- PARTITION p201108 VALUES LESS THAN (to_days('2011-09-01')),
- PARTITION p201109 VALUES LESS THAN (to_days('2011-10-01')),
- PARTITION p201110 VALUES LESS THAN (to_days('2011-11-01')),
- PARTITION p201111 VALUES LESS THAN (to_days('2011-12-01')),
- PARTITION p201112 VALUES LESS THAN (to_days('2012-01-01')),
- PARTITION pmax VALUES LESS THAN MAXVALUE );
2. 为现有表创建分区
- alter table 表名
- PARTITION BY RANGE (to_days(COLLECTTIME))
- (PARTITION pmin VALUES LESS THAN (to_days('2010-01-01')),
- PARTITION p201001 VALUES LESS THAN (to_days('2010-02-01')) ,
- PARTITION p201002 VALUES LESS THAN (to_days('2010-03-01')) ,
- PARTITION p201003 VALUES LESS THAN (to_days('2010-04-01')) ,
- PARTITION p201004 VALUES LESS THAN (to_days('2010-05-01')) ,
- PARTITION p201005 VALUES LESS THAN (to_days('2010-06-01')) ,
- PARTITION p201006 VALUES LESS THAN (to_days('2010-07-01')) ,
- PARTITION p201007 VALUES LESS THAN (to_days('2010-08-01')) ,
- PARTITION p201008 VALUES LESS THAN (to_days('2010-09-01')) ,
- PARTITION p201009 VALUES LESS THAN (to_days('2010-10-01')) ,
- PARTITION p201010 VALUES LESS THAN (to_days('2010-11-01')),
- PARTITION p201011 VALUES LESS THAN (to_days('2010-12-01')),
- PARTITION p201012 VALUES LESS THAN (to_days('2011-01-01')),
- PARTITION p201101 VALUES LESS THAN (to_days('2011-02-01')),
- PARTITION p201102 VALUES LESS THAN (to_days('2011-03-01')),
- PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')),
- PARTITION p201104 VALUES LESS THAN (to_days('2011-05-01')),
- PARTITION p201105 VALUES LESS THAN (to_days('2011-06-01')),
- PARTITION p201106 VALUES LESS THAN (to_days('2011-07-01')),
- PARTITION p201107 VALUES LESS THAN (to_days('2011-08-01')),
- PARTITION p201108 VALUES LESS THAN (to_days('2011-09-01')),
- PARTITION p201109 VALUES LESS THAN (to_days('2011-10-01')),
- PARTITION p201110 VALUES LESS THAN (to_days('2011-11-01')),
- PARTITION p201111 VALUES LESS THAN (to_days('2011-12-01')),
- PARTITION p201112 VALUES LESS THAN (to_days('2012-01-01')),
- PARTITION pmax VALUES LESS THAN MAXVALUE );
如果表中已有数据,分区时会自动进行分区存储,不必担心数据丢失或者手动分类数据.
3. 删除表中的指定分区
- ALTER TABLE 表名 DROP PARTITION 分区名;
删除分区会导致分区数据丢失,建议先备份.
4. 追加表分区
- ALTER TABLE 表名 DROP PARTITION pmax;
- ALTER TABLE 表名
- ADD PARTITION (
- PARTITION p201201 VALUES LESS THAN (to_days('2012-2-1')),
- PARTITION pmax VALUES LESS THAN MAXVALUE);
需要先删除MAXVALUE分区后增加分区后再重建MAXVALUE分区,删除前需要先备份MAXVALUE分区数据.
5. 查看标分区信息
- SELECT
- partition_name part,
- partition_expression expr,
- partition_description descr,
- table_rows
- FROM
- INFORMATION_SCHEMA.partitions
- WHERE
- TABLE_SCHEMA = schema()
- AND TABLE_NAME='表名';
6. 查看查询语句涉及分区信息
- explain partitions
- select … from 表名 where …;
该操作只查看查询语句相关的分区信息,不会返回查询结果.
以上是我对MySQL的初体验总结,没啥心得体会,只有一点点成就感,希望和大家分享.
另外分区表尚存在问题:
1,是否可将分区表设置在不同硬盘,innodb可行?
2,是否可根据多条件进行水平分区,类似group by 列1,列2...
3,是否能将分区设置成不同引擎,例如当前使用中的分区为innodb,老的分区使用MyISAM.
7.分区的合并
下面的SQL,将p201001 - p201009 合并为3个分区p2010Q1 - p2010Q3
mysql> ALTER TABLE sale_data
-> REORGANIZE PARTITION p201001,p201002,p201003,
-> p201004,p201005,p201006,
-> p201007,p201008,p201009 INTO
-> (
-> PARTITION p2010Q1 VALUES LESS THAN (201004),
-> PARTITION p2010Q2 VALUES LESS THAN (201007),
-> PARTITION p2010Q3 VALUES LESS THAN (201010)
-> );
Query OK, 0 rows affected (1.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
- MySQL水平分区表实际操作总结
- MySQL水平分区表实际操作总结
- MySQL水平分区表实际操作总结
- MySQL水平分区表初体验总结
- Mysql分区表局限性总结
- Mysql分区表局限性总结
- MySQL分区表(总结)
- Mysql分区表局限性总结
- mysql实际操作
- 不同类型的分区表的创建以及实际操作
- 不同类型的分区表的创建以及实际操作
- 3000fps 实际操作总结
- Mysql中分区表的使用总结
- MySQL实际操作正与误
- MySQL show的实际操作用法
- MySQL show的实际操作用法
- git的实际操作指令总结
- mysql分区表
- Android官方技术文档翻译——清单合并
- mdk在使用stlinlk进行仿真时不实时更新解决方法
- 获取request里的所有参数及参数名
- 吴治辉:分布式数据库MyCAT之前世今生
- Esri官方测试数据下载
- MySQL水平分区表实际操作总结
- 解析Android消息处理机制:Handler/Thread/Looper & MessageQueue
- Shell脚本之鸟哥私房菜第13章sh11
- java学习day19
- Eclipse CDT环境下编写、调试、运行C++程序详谈
- 高并发服务器的设计--内存池的设计
- DVSDK 4G-TF卡分区失败
- windows命令
- 复习题解析