前些天拿到一个表,将近有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分区概述与应用场景 在当今互联网行业中,MySQL数据库作为最常用的关系型数据库之一,在数据管理方面扮演着极其重要的角色。随着数据量的不断增长,对数据库性能的要求也日益提高。为了提高查询效率...
### MySQL OCP 实战整理题库知识点解析 #### 题目一: **题目内容**: Which MySQL utility program should you use to process and sort the slow query log based on query time or average query time? A. ...
#### 一致性(Consistency)、可用性(Availability)、分区容错性(Partition Tolerance) CAP定理阐述了在网络分区的情况下,分布式系统只能同时满足一致性、可用性和分区容错性中的两个特性。对于MySQL分布式...
Hive的元数据(如表结构、分区等)存储在MySQL或其它数据库中,用于管理和跟踪数据的位置。 在处理"chbVideoOut"这样的数据时,我们首先需要创建一个Hive表来定义数据结构。假设这个文件包含用户ID(userId),视频...
- 分区类型: 基本分区(Primary Partition)、扩展分区(Extended Partition)和逻辑分区(Logical Partition)。 - 数量限制: 基本分区和扩展分区加起来最多四个;逻辑分区的数量没有明确限制。 - 使用限制: ...
如果有 `PARTITION BY` 子句,那么每个分区都会从1重新开始编号。对于 `ORDER BY` 子句中具有相同值的行,其编号是非确定性的,也就是说这些行可能会被赋予不同的编号。 **示例代码**: ```sql SELECT id, name, ROW...
TiDB采用基于范围的分区策略,即Range-Based Partition。这种方式适合SQL工作负载,可以有效避免哈希分区方式在全表扫描时效率低下问题。分区大小的选择非常重要,既不能太小导致过多的分区,也不能太大影响查询性能...
3. 分区(PARTITION BY):在SQL窗口函数中,`PARTITION BY`用于将数据分割成多个分区,然后对每个分区独立进行计算。 4. 视图(VIEW):视图是虚拟表,基于一个或多个表的查询结果。它提供了一种简化复杂查询和...
- 分区表 (Partition) 2. **Hive 存储模式** - **数据存储**: - Hive 中的所有数据最终都存储在 HDFS 中。 - 每个表在 HDFS 中都有一个相应的目录。 - 例如,表 `pvs` 在 HDFS 中的路径为 `/wh/pvs`。 3. **...
2. 分区处理:对于大数据集,Saber可以进行分区操作,提高处理效率。 ```python partitions = saber_df.partition_by('date_column', num_parts=5) for part in partitions: process(part) ``` 3. 数据流处理:...