`
mtnt2008
  • 浏览: 369554 次
  • 性别: Icon_minigender_1
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

MySQL分区[转]

MySQL应用服务器数据结构 
阅读更多

一、概述

相信有很多人经常会问同样的一个问题：当 MySQL
的总 记录数超过了100万后，会出现性能的大幅度下降吗？答案是肯定的，但是 性能下降>的比率不一而同，要看系统的架构、应用程序、还有>包括索引、服务器硬件等多种因素而定。当有网友问我这个问题的时候，我最常见的回答>就是：分表，可以根据id区间或者时间先后顺序等多
种规则来分表。分表很容易，然而由此所带来的应用程序甚至是架构方面的改动工作却不>容小觑，还包括将来的扩展性等。

在以前，一种解决方案就是使用 MERGE
类型，这是一个非常方便的做饭。架构和程序基本上不用做改动，不过，它的缺点是显见的：

  • 只能在相同结构的 MyISAM 表上使用
  • 无法享受到 MyISAM 的全部功能，例如无法在 MERGE 类型上执行 FULLTEXT 搜索
  • 它需要使用更多的文件描述符
  • 读取索引更慢

这个时候，MySQL 5.1 中新增的分区(Partition)功能的优势也就很明显了：

  • 与单个磁盘或文件系统分区相比，可以存储更多的数据
  • 很容易就能删除不用或者过时的数据
  • 一些查询可以得到极大的优化
  • 涉及到 SUM()/COUNT() 等聚合函数时，可以并行进行
  • IO吞吐量更大

分区允许可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。

二、分区的类型

  • RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。参见18.2.1节，RANGE分区
  • LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。参见18.2.2节，LIST分区
  • HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包>含MySQL 中有效的、产生非负整数值的任何表达式。参见18.2.3节，HASH分区
  • KEY
    分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含>整数值。
    参见18.2.4节，KEY分区

三、分区例子：

  • RANGE 类型
    CREATE TABLE users (
       uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(30) NOT NULL DEFAULT '',
       email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY RANGE (uid) (
       PARTITION p0 VALUES LESS THAN (3000000)
       DATA DIRECTORY = '/data0/data'
       INDEX DIRECTORY = '/data1/idx',

       PARTITION p1 VALUES LESS THAN (6000000)
       DATA DIRECTORY = '/data2/data'
       INDEX DIRECTORY = '/data3/idx',

       PARTITION p2 VALUES LESS THAN (9000000)
       DATA DIRECTORY = '/data4/data'
       INDEX DIRECTORY = '/data5/idx',

       PARTITION p3 VALUES LESS THAN MAXVALUE       DATA DIRECTORY = '/data6/data' 
       INDEX DIRECTORY = '/data7/idx'
);

    在这里，将用户表分成4个分区，以每300万条记录为界限，每个分区都有自己独立的数据、索引文件的存放目录，与此同时，这些目录所在的>
    物理磁盘分区可能也都是完全独立的，可以多大提高了磁盘IO吞吐量。

  • LIST 类型
    CREATE TABLE category (
       cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY LIST (cid) (
       PARTITION p0 VALUES IN (0,4,8,12)
       DATA DIRECTORY = '/data0/data' 
       INDEX DIRECTORY = '/data1/idx',
       
       PARTITION p1 VALUES IN (1,5,9,13)
       DATA DIRECTORY = '/data2/data'
       INDEX DIRECTORY = '/data3/idx',
       
       PARTITION p2 VALUES IN (2,6,10,14)
       DATA DIRECTORY = '/data4/data'
       INDEX DIRECTORY = '/data5/idx',
       
       PARTITION p3 VALUES IN (3,7,11,15)
       DATA DIRECTORY = '/data6/data'
       INDEX DIRECTORY = '/data7/idx'
);  

    分成4个区，数据文件和索引文件单独存放。

  • HASH 类型
    CREATE TABLE users (
       uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(30) NOT NULL DEFAULT '',
       email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY HASH (uid) PARTITIONS 4 (
       PARTITION p0
       DATA DIRECTORY = '/data0/data'
       INDEX DIRECTORY = '/data1/idx',

       PARTITION p1
       DATA DIRECTORY = '/data2/data'
       INDEX DIRECTORY = '/data3/idx',

       PARTITION p2
       DATA DIRECTORY = '/data4/data'
       INDEX DIRECTORY = '/data5/idx',

       PARTITION p3
       DATA DIRECTORY = '/data6/data'
       INDEX DIRECTORY = '/data7/idx'
);

    分成4个区，数据文件和索引文件单独存放。

  • KEY 类型
    REATE TABLE users (
       uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(30) NOT NULL DEFAULT '',
       email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY KEY (uid) PARTITIONS 4 (
       PARTITION p0
       DATA DIRECTORY = '/data0/data'
       INDEX DIRECTORY = '/data1/idx',
       
       PARTITION p1
       DATA DIRECTORY = '/data2/data' 
       INDEX DIRECTORY = '/data3/idx',
       
       PARTITION p2 
       DATA DIRECTORY = '/data4/data'
       INDEX DIRECTORY = '/data5/idx',
       
       PARTITION p3 
       DATA DIRECTORY = '/data6/data'
       INDEX DIRECTORY = '/data7/idx'
);  

    分成4个区，数据文件和索引文件单独存放。

  • 子分区
    子分区是针对 RANGE/LIST 类型的分区表中每个分区的再次分割。再次分割可以是 HASH/KEY 等类型。例如：

     

    CREATE TABLE users (
       uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(30) NOT NULL DEFAULT '',
       email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2(
       PARTITION p0 VALUES LESS THAN (3000000)
       DATA DIRECTORY = '/data0/data'
       INDEX DIRECTORY = '/data1/idx',

       PARTITION p1 VALUES LESS THAN (6000000)
       DATA DIRECTORY = '/data2/data'
       INDEX DIRECTORY = '/data3/idx'
);

    对 RANGE 分区再次进行子分区划分，子分区采用 HASH 类型。

    或者

    CREATE TABLE users (
       uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(30) NOT NULL DEFAULT '',
       email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2(
       PARTITION p0 VALUES LESS THAN (3000000)
       DATA DIRECTORY = '/data0/data'
       INDEX DIRECTORY = '/data1/idx',

       PARTITION p1 VALUES LESS THAN (6000000)
       DATA DIRECTORY = '/data2/data'
       INDEX DIRECTORY = '/data3/idx'
);

    对 RANGE 分区再次进行子分区划分，子分区采用 KEY 类型。

四、分区管理

  • 删除分区
    ALERT TABLE users DROP PARTITION p0;

    删除分区 p0。

  • 重建分区
    • RANGE 分区重建
      ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));

      将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。

    • LIST 分区重建
      ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));

      将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。

    • HASH/KEY 分区重建
      ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;

      用 REORGANIZE 方式重建分区的数量变成2，在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。

  • 新增分区
    • 新增 RANGE 分区
      ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)
DATA DIRECTORY = '/data8/data'
INDEX DIRECTORY = '/data9/idx');

      新增一个RANGE分区。

    • 新增 HASH/KEY 分区
      ALTER TABLE users ADD PARTITION PARTITIONS 8;

      将分区总数扩展到8个。

    好了，本次体验先到这里，更多详情请看 mysql 手册第18章 。

 

 

 

分享到：
| 6大设计原则
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

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

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

    MySQL 分区

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

    MySQL分区分表方案实践手册

    ### MySQL分区分表方案实践手册知识点详述 #### 一、MySQL分区简介 数据库分区是一项重要的物理数据库设计技术，主要用于优化数据库性能并简化数据管理。MySQL的分区主要包括两种形式：水平分区和垂直分区。 - **...

    创建mysql表分区的方法

    【MySQL 表分区详解】 MySQL 表分区是一种高级的数据组织技术，它允许将大型表分成较小、更易管理和处理的部分。这种技术对于处理海量数据，尤其是超过亿级别的数据集非常有效，可以显著提升查询性能和数据管理效率...

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

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

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

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

    mysql分区文档

    MySQL 分区是数据库管理系统中的一种优化策略，它将大型表的数据分布在不同的物理存储上，以提高查询性能和便于管理大量数据。分区的本质是将一张大表逻辑上分成多个部分，但用户在操作时仍然将其视为单个表。MySQL ...

    mysql表分区

    MySQL表分区是数据库管理系统（DBMS）中用于提高大数据量表的存储性能和管理效率的一种技术。通过对一个大表进行分区，可以将数据分散存储到多个物理区域中，从而改善性能，尤其是在涉及大量读写操作的场景中。MySQL...

    MySQL 5.5.8 分区表性能测试

    MySQL 5.5.8 版本在数据库领域是一个重要的里程碑，它引入了许多性能改进和新特性，尤其是在分区表方面。分区表是大型数据库系统中优化查询性能的一种策略，通过将大表的数据逻辑上划分为更小、更易管理的部分。在...

    MySQL分区实战

    #### 一、MySQL分区概述与应用场景 在当今互联网行业中，MySQL数据库作为最常用的关系型数据库之一，在数据管理方面扮演着极其重要的角色。随着数据量的不断增长，对数据库性能的要求也日益提高。为了提高查询效率...

    Mysql转oracle工具

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

    mysql 数据库表分区

    MySQL 数据库表分区是一种优化大数据查询的技术，尤其适用于存储海量数据的应用场景。通过将大表分成更小、更易管理的部分，分区可以提高查询性能，减少数据处理的时间，并且简化数据管理和备份过程。 1. **分区...

    mysql语句转postgres的工具

    2. **性能评估**：PostgreSQL可能有不同的性能优化策略，迁移后可能需要调整索引、分区、缓存等设置。 3. **应用程序修改**：如果应用程序直接与数据库交互，可能需要对SQL查询进行相应调整，以适应PostgreSQL的...

    Mysql主从与分区技术.pdf

    mysql主从复制与分区技术： 主讲：李健； QQ：89267659； 1、mysql用户授权； 2、mysql bin-log日志； 3、mysql主从复制； 4、mysql分区技术；

    MySQL数据库分区技术

    MySQL数据库分区技术，解决多并发问题，详细描述分区流程及操作步骤

    mysql分区.pdf

    MySQL分区是数据库管理系统MySQL中的一种数据存储管理技术，它允许用户将数据表拆分成多个较小的、更容易管理的片段。这些片段被称为分区。通过这种技术可以提高数据库的性能，便于维护和备份，并且可以对不同分区...

Magicbox
Global site tag (gtag.js) - Google Analytics