`
jiaguwen123
  • 浏览: 415177 次
  • 性别: Icon_minigender_2
  • 来自: 深圳
社区版块
存档分类
最新评论

将mysql非分区表转换为分区表

 
阅读更多
查看表的分布状况
mysql> select count(*) from  justin;
+----------+
| count(*) |
+----------+
|  5845246 |
+----------+
1 row in set (0.00 sec)
mysql> select month(create_time),count(*) from justin group by  month(create_time);
+-----------------------+----------+
| month(create_time) | count(*) |
+-----------------------+----------+
|                     1 |  1128520 |
|                    11 |  1574965 |
|                    12 |  3141750 |
+-----------------------+----------+
3 rows in set (6.93 sec)
考虑以create_time为分区键建立分区表
第一步  创建中间表,以主键id和分区列为联合主键
CREATE TABLE `temp_justin` (
  `id` bigint(1) NOT NULL AUTO_INCREMENT COMMENT '流水号,自增',
  `create_time` datetime DEFAULT NULL COMMENT '订单日志创建时间(建立索引)',
  PRIMARY KEY (`id`,`create_time`),
) ENGINE=MyISAM AUTO_INCREMENT=6000000 DEFAULT CHARSET=utf8;
表已经存在580多万记录并且不断在增长,因此中间表初始的id值设置成6000000
增加分区,以月为单位
alter table temp_justin partition by range(to_days(create_time))
(
partition p1012 values less than (to_days('2011-01-01')),
partition p1101 values less than (to_days('2011-02-01')),
partition p1102 values less than (to_days('2011-03-01')),
partition p1103 values less than (to_days('2011-04-01')),
partition p1104 values less than (to_days('2011-05-01')),
partition p1105 values less than (to_days('2011-06-01')),
partition p1106 values less than (to_days('2011-07-01')),
partition p1107 values less than (to_days('2011-08-01')),
partition p1108 values less than (to_days('2011-09-01')),
partition p1109 values less than (to_days('2011-10-01')),
partition p11010 values less than (to_days('2011-11-01')),
partition p11011 values less than (to_days('2011-12-01')),
partition p11012 values less than (to_days('2012-01-01'))
);
第二步 重命名表
Alter table justin rename to justin_bak_110113;
Alter table temp_justin rename to justin;
第三步 同步数据
Insert into justin select * from temp_justin;
表里已经存在将近600万条记录,如此批量导入数据会对数据库性能影响很大。
每一万条提交一次,sleep 2s ,53万数据总耗时2 min 39.67 sec。
mysql> create procedure cp_data()
    -> begin
    -> declare i int;
    -> set i=0;
    -> while i<60 do
    -> insert into justin
    -> select * from justin_bak_110113
    -> where id >= i*10000 and  id <(i+1)*10000;
    -> set i=i+1;
    -> select sleep(2);
    -> end while;
    -> end||
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> delete from justin;
    -> ||
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call cp_data();
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2 min 39.67 sec)
Query OK, 0 rows affected (2 min 39.67 sec)
mysql> select count(*) from justin;
+----------+
| count(*) |
+----------+
|   525031 |
+----------+
1 row in set (0.00 sec)
查看执行计划,使用了分区扫描
mysql> explain
    -> select count(*) from justin where create_time
    -> <='2011-01-13' and create_time>'2011-01-04';
+----+-------------+--------------------+-------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table              | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    |
+----+-------------+--------------------+-------+---------------+---------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | justin | index | NULL          | PRIMARY | 16      | NULL | 525031 | Using where; Using index |
+----+-------------+--------------------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)
mysql> explain
    -> partitions
    -> select count(*) from justin where create_time
    -> <='2011-01-13' and create_time>'2011-01-04';
+----+-------------+--------------------+-------------+-------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table              | partitions  | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    |
+----+-------------+--------------------+-------------+-------+---------------+---------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | justin | p1012,p1101 | index | NULL          | PRIMARY | 16      | NULL | 525031 | Using where; Using index |
+----+-------------+--------------------+-------------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)
分享到:
评论

相关推荐

    MySQL分区表自动创建及删除存储过程

    MySQL分区表是一种优化大型数据表查询效率的技术,它将一个大表分成多个逻辑上相连但物理上独立的部分,每个部分称为一个分区。分区可以按照不同的策略进行,如范围、哈希、列表或复合分区。这样做有助于提高数据...

    mysql数据库转换成oracle

    总的来说,将MySQL数据库转换为Oracle涉及到多个步骤,包括数据备份、导出、格式转换、导入和测试。使用合适的工具可以简化这一过程,但理解两者之间的差异和Oracle的最佳实践仍然是至关重要的。在实际操作中,应...

    Mysql的表对象Sql语句转换单表,转换成Oracle创建表sql

    MySQL和Oracle都是广泛使用的数据库管理系统,但它们的SQL语法略有不同。当需要将MySQL的表结构迁移到Oracle数据库时,必须进行适当的...对于简单场景,如描述中的单表转换,一个简单的文本处理脚本或工具就足够了。

    Oracle的表结构转成Mysql的表结构

    本文介绍了一种将Oracle数据库中的表结构转换为MySQL数据库表结构的方法。通过编写一个PL/SQL函数`fnc_table_to_mysql`来实现这一目标。该函数可以接受四个参数,并生成对应的MySQL创建表语句(DDL)。 #### 参数...

    Mysql转oracle工具

    在转换过程中,可能需要将MySQL的数据分布策略转换为Oracle的表空间或分区策略。 5. **索引和约束**: MySQL与Oracle的索引类型和约束定义(如主键、外键)也有所不同,转换时需要注意。 6. **存储过程和函数**:...

    MySQL分区表的基本入门教程

    前言 在最近的项目中,我们需要保存大量的数据,而且这些数据是有有效期的,为了提供查询... Hash分区:基于给定的分区个数,将数据分配到不同的分区,HASH分区只能针对整数进行HASH,对于非整形的字段只能通过表达式

    mysql转换为sqlserver

    5. 规范化和重构:在将数据从MySQL迁移到SQL Server时,你可能需要对数据库进行规范化,遵循SQL Server的最佳实践,例如,使用更严格的事务隔离级别,利用分区表提升大表的查询性能。 6. 触发器和存储过程:MySQL和...

    数据库转换工具MySQL 转为SqlServer 脚本

    本主题聚焦于如何使用特定工具或脚本将MySQL数据库转换为SQL Server,这一过程通常称为“数据库迁移”。以下是关于这个过程的一些关键知识点: 1. **数据库迁移的目的**:迁移可能出于多种原因,如业务增长、性能...

    Oracle数据库sql转换mysql数据库工具

    3. **数据库架构差异**:比如分区表、物质化视图在MySQL中可能有不同的实现方式,需要调整转换策略。 4. **性能优化**:MySQL可能需要不同的索引策略和查询优化方法,转换后可能需要进一步优化。 5. **事务与并发...

    MySQL分区表的正确使用方法

    MySQL分区表是一种优化大数据量表性能的技术,尤其适用于存储历史数据和执行大量删除操作的场景。分区表在逻辑上表现为单个表,但在物理存储上,数据分散在多个文件中,这有助于提高查询效率和数据清理速度。 1. **...

    mysql语句转postgres的工具

    在这个过程中,`MysqlToPostgresUtil`工具扮演着重要的角色,它能帮助我们将MySQL的DDL(Data Definition Language)语句转换为PostgreSQL兼容的格式,从而简化迁移过程。 MySQL和PostgreSQL虽然都是关系型数据库...

    mysql-oracle数据转换工具

    这时,“mysql-oracle数据转换工具”就显得尤为关键。 该工具的核心功能是实现数据的高效、准确转换,确保在两个不同数据库系统间的数据一致性。以下是一些关于MySQL和Oracle数据库以及数据转换的关键知识点: 1. ...

    基于Mysql的表转HBase小Demo

    首先,创建或检查HBase表是否存在,然后根据设计的HBase表结构,将Mysql数据转化为HBase的Put对象,并提交到表中。 4. **单元测试**:提供的单元测试文件用于验证数据转换的正确性。通常会模拟一些基本操作,如插入...

    mysql、posrgresql转成clickhouse db工具sync-master

    - **性能优化**:ClickHouse的设计倾向于处理大量数据的分析,而非事务处理,所以在设计表结构时,应考虑到ClickHouse的特性,如分区策略、索引优化等。 - **权限管理**:确保迁移过程中对源数据库和目标数据库的...

    总结MySQL的分区

    MySQL分区技术是一种将大表水平切分成多个小表的技术,有助于提高性能、方便管理和维护。分区可以让逻辑上的表数据分布于不同的物理存储介质上,从而提升查询效率和方便数据的归档删除。MySQL从5.7版本开始对分区表...

    Mysql建表语句转化为postgre语句

    本篇文章将详细探讨如何将MySQL的建表语句转换为PostgreSQL兼容的语句。 MySQL和PostgreSQL之间的主要语法差异体现在以下几个方面: 1. 数据类型: - MySQL中的`TINYINT`在PostgreSQL中可以使用`SMALLINT`或`...

    MySQL、Teradata和PySpark代码互转表和数据转换代码.docx

    - 分区和桶:Teradata支持分区和桶,而MySQL和PySpark也有类似的概念,但具体语法和使用方式有所不同。 - 并行处理:PySpark提供了并行处理能力,而MySQL和Teradata可能需要通过优化查询来提升性能。 - 兼容性:...

    数据库转换mysql to oracle

    Oracle支持更多高级特性,如物化视图、分区表和高级索引,可能需要对MySQL的设计进行调整。 2. 数据迁移工具:本案例中提供的“mysq2oracn.exe”可能是用于执行实际转换的工具。这类工具通常会自动检测源数据库结构...

    mysql数据抽取,自动生成hive建表语句

    4. **处理分区**:如果需要在Hive中创建分区表,可以分析MySQL表中的时间戳或分类字段,将其设置为Hive的分区字段。 5. **生成Hive建表语句**:基于上述信息,构造Hive的CREATE TABLE语句,包括表名、字段定义、...

Global site tag (gtag.js) - Google Analytics