`
jsczxy2
  • 浏览: 1277447 次
  • 性别: Icon_minigender_1
  • 来自: 常州
文章分类
社区版块
存档分类
最新评论

用mysql表分区来优化大数据量的表

阅读更多

根据公司数据库实际情况,订单表有可能会比预想中扩张速度快,这里可能需要预先准备下优化方案,传统方案是分表或者分库,不过目前最好的方案是使用mysql的表分区来优化。不过需要注意的是在表分区建立后mysql查询缓存会失效,那么可以说暂时分表带来的好处在于更新、删除以及锁处理的时间会减少,但是如果查询并非针对表分区字段进行,那么查询的时间由于查询缓存失效反而会增加,这点需要取舍。

第一步:由于表分区必须在表建立的时候创建规则,而已经存在的没有创建过表分区规则的表需要重新做导入处理。方法如下:

#这里使用HASH表分区,mysql会根据HASH字段来自动分配数据到不同的表分区,这种情况适用于没有表分区规则但是有需要分表来进行查询优化的情况。这里根据id字段hash规则创建2个表分区
CREATE TABLE `creater_bak` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH(id) PARTITIONS 2

 创建完成后开始导入原表数据:

insert into creater_bak select * from creater;

 导入以后的新表数据就是分布在不同的2个表分区中了。

如果数据量非常大,觉得预设的表分区数量太少,那么可以新增表分区,mysql会自动重新分配:

#这里新增8个表分区,加上新建表时候的2个,一共10个表分区了
ALTER TABLE `creater_bak` ADD PARTITION PARTITIONS 8;

 最后修改表名为原表名即可。

PS:下面是使用RANGE形式表分区,其中一些注意点HASH表分区也一样要注意:

1.如果使用RANGE形式进行表分区,必须设定规则,例如:

CREATE TABLE `creater_bak` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(id) (
    PARTITION p0 VALUES LESS THAN (500),
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
)

 2.如果想修改有规则的表分区,注意只能新增,不要随意删除,这里删除表分区会造成该表分区内部数据也一起被删除掉,千万注意。另外如果设定了MAXVALUE那么是不能新增的,虽然删除MAXVALUE那条表分区后可以新增,但是依然注意删除的MAXVALUE分区是否有数据,如果有则不能随意删除,最好的办法依然是重建一张新表,表在创建时候重新制定规则后把旧表导入新表,这样能保证不会丢失数据。虽然最好不要删除分区,但是依然下面介绍如何删除表分区以及新增表分区:

#删除上面的MAXVALUE规则表分区(如果该表分区有数据,请勿随便使用此操作)
ALTER TABLE `creater_bak` drop PARTITION p2;
#新增规则表分区,注意按规则步长来新增,否则会报错,这里步长为500
ALTER TABLE `creater_bak` add PARTITION(PARTITION p2 VALUES LESS THAN (1500))
ALTER TABLE `creater_bak` add PARTITION(PARTITION p3 VALUES LESS THAN MAXVALUE)

 最后使用下面的语句可以查看分区搜索情况:

EXPLAIN PARTITIONS select * from `creater_bak` b1 where b1.`id`=11

 最后附上官方中文文档:

 http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html

深入了解MySQL 5.5分区功能增强

分享到:
评论

相关推荐

    创建mysql表分区的方法

    3. **查询加速**:分区能够减少查询扫描的数据量,提高查询速度,尤其对于有特定WHERE条件的查询。 4. **并行处理**:涉及聚合函数(如SUM()、COUNT())的查询可以并行处理,提高执行效率。 5. **数据组织**:根据...

    mysql表分区

    MySQL表分区是数据库管理系统...在对数据库进行分区之前,需要评估数据的使用模式和表的大小,以及是否需要通过分区来实现数据的快速删除和查询优化。此外,选择合适的存储引擎和文件组织方式也是实现高效分区的关键。

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

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

    互联网公司为啥不使用mysql分区表

    在互联网公司中,mysql分区表是一个比较少用的技术,这是因为分区表存在一些缺陷和限制,使得互联网公司更多地选择自己分库分表来水平扩展数据库。 首先,分区表的设计需要考虑到分区键的设计,不同的分区键设计会...

    mysql 数据库表分区

    - 分区有助于减少扫描的数据量,因为查询仅需涉及满足条件的分区,而不是整个表。 - 使用IN、BETWEEN或其他条件的查询,如果条件与分区键匹配,查询性能会有显著提升。 - 注意,不当的分区设计可能导致全表扫描,...

    mysql大数据量优化

    MySQL数据库在处理大数据量时,性能优化至关重要。以下是一些关键的优化策略和技术: 1. **连接查询与子查询优化**: - **连接查询**(JOIN):当需要从多个表中获取关联数据时,使用连接查询通常比子查询更有效率...

    MySQL 5.5.8 分区表性能测试

    总之,MySQL 5.5.8的分区表功能在性能、维护和数据管理方面都有显著提升,对于需要处理大量数据的企业级应用来说,是值得考虑的一个重要优化手段。同时,结合具体的测试数据,我们可以进一步优化数据库架构,以适应...

    MySQL的表分区详解

    然而,值得注意的是,尽管MySQL分区提供了许多优势,但在实际应用中,需要根据具体业务需求和查询模式来决定是否使用分区,以及如何设计分区策略。在创建分区表后,可以通过调整分区方案来优化常见查询的性能。此外...

    大数据量测试数据(MySQL)

    2. **分区表**:对于大数据量,考虑使用分区表可以提高查询性能,比如按日期或ID范围进行分区。 3. **存储引擎选择**:InnoDB是默认的事务安全引擎,但对于读密集型应用,MyISAM可能更快。根据需求评估使用哪种引擎...

    MySQL分区分表方案实践手册

    水平分区的主要目的是通过减少SQL操作中需要处理的数据量来缩短响应时间。这种方式能够保持原表的结构不变,每个分区都包含表的所有列。 - **垂直分区(Vertical Partitioning)**:与水平分区不同,垂直分区是...

    MySQL大表性能优化方案 和 MySQL高性能表设计规范

    在数据库管理领域,MySQL是广泛使用的开源关系型数据库系统,尤其在处理大数据量时,其性能优化和高效表设计显得尤为重要。本篇文章将深入探讨针对MySQL大表的性能优化策略以及如何遵循高性能的表设计规范。 一、...

    mysql实现自动创建与删除分区

    首先,我们需要理解MySQL分区的概念。分区是将一个大表逻辑上划分为多个较小的部分,每个部分称为一个分区。对于时间序列数据,如日志记录,根据日期或时间进行分区可以显著提高查询速度,因为查询通常针对特定时间...

    MySQL 分区

    MySQL分区是一种数据库优化技术,它将大型表分成更小、更易管理的部分,每个部分称为一个分区。这种技术有助于提高查询性能,特别是对于那些需要处理大量数据或执行复杂查询的应用程序。MySQL支持多种分区类型,包括...

    MySQL中创建数据表Range分区.pdf

    通过以上分析,可以看出Range分区是一种实用的数据表组织方式,在大数据量操作时可以显著提高性能和管理效率。文档内容通过实例介绍了创建Range分区的方法,同时揭示了在实际应用中应该注意的要点,帮助数据库管理员...

    mysql 千万数据表 t_order.zip

    在大数据量下,索引是提升查询速度的关键。为经常用于查询的字段建立索引,例如,如果常常按用户ID或订单时间进行筛选,那么user_id和order_time字段就应创建索引。然而,过多的索引会影响写操作性能,因此需平衡...

    MySQL千万级大表深度分页为什么慢,以及优化的方法、原理

    【MySQL千万级大表深度分页慢的原因及优化方法】 在MySQL中,处理千万级大表的深度分页查询时,通常会遇到性能问题。这是因为MySQL的查询优化器在面对大量数据的分页请求时,可能选择全表扫描而不是利用索引来提高...

    MySQL百万级数据量分页查询方法及其优化建议

    - **使用分区表**:根据业务需求对大表进行分区,降低单个分区的数据量。 - **考虑数据缓存**:使用缓存系统如Redis,存储热点数据,减轻数据库压力。 总的来说,处理百万级数据量的分页查询,关键在于合理利用索引...

Global site tag (gtag.js) - Google Analytics