前些天拿到一个表,将近有4000w数据,没有任何索引,主键。(建这表的绝对是个人才)
这是一个日志表,记录了游戏中物品的产出与消耗,原先有一个后台对这个表进行统计。。。。。(这要用超级计算机才能统计得出来吧),只能帮前人填坑了。。。。
数据太大,决定用分区来重构。
如果你发现是empty,说明你的mysql版本不够,分区至少要5.1
下面针对业务查询,决定用时间来做range分区(还有list,hash等类型),一个月一个区.
按照RANGE分区的表是通过如下一种方式进行分区的,每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠,使用VALUES LESS THAN操作符来进行定义。
新建一个表:
CREATE TABLE `xxxxxxxx` ( `crttm` int(11) NOT NULL, `srvid` int(11) NOT NULL, `evtid` int(11) NOT NULL, `aid` int(11) NOT NULL, `rid` int(11) NOT NULL, `itmid` int(11) NOT NULL, `itmnum` int(11) NOT NULL, `gdtype` int(11) NOT NULL, `gdnum` int(11) NOT NULL, `islmt` int(11) NOT NULL, KEY `crttm` (`crttm`), KEY `itemid` (`itmid`), KEY `srvid` (`srvid`), KEY `gdtype` (`gdtype`) ) ENGINE=myisam DEFAULT CHARSET=utf8 PARTITION BY RANGE (crttm) ( PARTITION p201303 VALUES LESS THAN (unix_timestamp('2013-04-01')), PARTITION p201304 VALUES LESS THAN (unix_timestamp('2013-05-01')), PARTITION p201305 VALUES LESS THAN (unix_timestamp('2013-06-01')), PARTITION p201306 VALUES LESS THAN (unix_timestamp('2013-07-01')), PARTITION p201307 VALUES LESS THAN (unix_timestamp('2013-08-01')), PARTITION p201308 VALUES LESS THAN (unix_timestamp('2013-09-01')), PARTITION p201309 VALUES LESS THAN (unix_timestamp('2013-10-01')), PARTITION p201310 VALUES LESS THAN (unix_timestamp('2013-11-01')), PARTITION p201311 VALUES LESS THAN (unix_timestamp('2013-12-01')), PARTITION p201312 VALUES LESS THAN (unix_timestamp('2014-01-01')), PARTITION p201401 VALUES LESS THAN (unix_timestamp('2014-02-01')) );
注意:
1. primary key和unique key必须包含在分区key的一部分,否则在创建primary key和unique index时会报”ERROR 1503 (HY000)“
mysql> create unique index idx_employees1_job_code on employees1(job_code);
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
或
mysql> ALTER TABLE `skate`.`employees1` ADD PRIMARY KEY (`id`) ;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
2. 范围分区添加分区只能在最大值后面追加分区
3. 所有分区的engine必须一样
4. 范围分区分区字段:integer、数值表达式、日期列,日期函数表达式(如year(),to_days(),to_seconds(),unix_timestamp())
将旧的表数据导入到新表后,看到新表的数据都分布到不同的区了!
维护命令:
添加分区
alter table xxxxxxx add partition (partition p0 values less than(1991)); //只能添加大于分区键的分区
删除分区
alter table xxxxxxx drop partition p0; //可以删除任意分区
删除分区数据
alter table xxxxxx truncate partition p1,p2; alter table xxxxxx truncate partition all; 或 delete from xxxxxx where separated < '2006-01-01' or (separated >= '2006-01-01' and separated<'2011-01-01');
重定义分区(包括重命名分区,伴随移动数据;合并分区)
alter table xxxxx reorganize partition p1,p3,p4 into (partition pm1 values less than(2006), partition pm2 values less than(2011));
rebuild重建分区
alter table xxxxxx rebuild partition pm1/all; //相当于drop所有记录,然后再reinsert;可以解决磁盘碎片
优化表
alter table tt2 optimize partition pm1; //在大量delete表数据后,可以回收空间和碎片整理。但在5.5.30后支持。在5.5.30之前可以通过recreate+analyze来替代,如果用rebuild+analyze速度慢
analzye表
alter table xxxxxx analyze partition pm1/all;
check表
alter table xxxxxx check partition pm1/all;
show create table employees2; //查看分区表的定义 show table status like 'employees2'\G; //查看表时候是分区表 如“Create_options: partitioned” select * from information_schema.KEY_COLUMN_USAGE where table_name='employees2'; //查看索引 SELECT * FROM information_schema.partitions WHERE table_name='employees2' //查看分区表 explain partitions select * from employees2 where separated < '1990-01-01' or separated > '2016-01-01'; //查看分区是否被select使用
相关推荐
MYSQL 通过分区(Partition)提升MySQL性能,看清楚,不要浪费分数,是MYSQL,不是SQL SERVER2000,有需要的可以下载.
MySQL Partition Manager 是雅虎开源的 MySQL 分区管理器。它可以帮助你以最小的配置自动创建、维护、清除分区。 标签:MySQL
表分区是最近才知道的哦 ,以前自己做都是分表来实现上亿级别的数据了,下面我来给大家介绍一下mysql表分区创建与使用吧,希望对各位同学会有所帮助。表分区的测试使用,主要内容来自于其他博客文章以及mysql5.1的...
windows平台超好用的磁盘分区工具,可在系统运行时运行使用。
如果想在已经建好的表上进行分区,如果使用alter添加分区的话,mysql会提示错误: 代码如下: ERROR 1505 <HY000> Partition management on a not partitioned table is not possible 正确的方法是新建一个具有分区...
17、MapReduce的分区Partition介绍 网址:https://blog.csdn.net/chenwewi520feng/article/details/130454574 本文介绍MR的分区Partition。 本文分为2个部分,即介绍与示例。 前提依赖:hadoop环境可正常使用。
mysql 分区 partition 当进行分区操作,了解对性能所产生的影响是非常有帮助的: 1.创建分区表比无分区的正规表要稍微慢些; 2.通过alter table….drop partition语句进行删除比delete语句要快些; 3.在range或list...
Oracle9i通过引入列表分区(List Partition),使得当前共有4种分区数据的方法,文中分别介绍了这四种分区方法:范围分区、Hash分区、复合分区、列表分区。
mysql表分区的建立,索引的建立,原理说明,还有就是实例的演示
MySQL交换分区的实例详解 前言 在介绍交换分区之前,我们先了解一下 mysql 分区。 数据库的分区有两种:水平分区和垂直分区。而MySQL暂时不支持垂直分区,因此接下来说的都是水平分区。水平分区即:以行为单位对表...
EASEUS Partition Master分区大师
AOMEI Partition Assistant 是一个简单易用且免费的磁盘分区管理软件,在它的帮助下,你可以无损数据地执行调整分区大小,移动分区位置,复制分区,复制磁盘,合并分区,切割分区、恢复分区、迁移操作系统等操作,是...
A40-T3的sys_partition.fex分区表说明
MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时候,优化器根据分区定义过滤那些没有我们需要的数据的分区,这样查询就可以无需扫描所有分区,只需要查找包含需要数据的...
MySQL分区检查前置条件-- 查看分区插件是否激活 partition -> active-- 查看分区插件是否激活方法2,plugin_status ->
真正实用的win7下的磁盘分区工具,本人亲测,不像分区魔术师,Acronis Disk Director Suite等存在系统兼容的问题。 虽然是英文的,但界面和我们所熟悉的界面基本一致,所以操作还是很简单的。
IM-Magic Partition Resizer Pro 是一款功能强大的硬盘磁盘无损分区软件,它能帮助你快速安全地调整硬盘分区的大小。譬如笔记本使用起来发现C盘空间快要满了,IM-Magic Partition Resizer 可以帮助您从其他分区调...
Partition 磁盘 分区 工具 支持多格式分区
Paragon Partition Manager是一个类似于PQ PartitionMagic的磁盘分区工具集,是一套磁盘管理软件,PartitionMagic的最佳替代品!有着直觉的图形使用介面和支持鼠标操作。 【软件功能】 主要功能包括:能够不损失...
Oracle Partition分区详细总结.pdf