`

MySQL分区(mysql partition)

阅读更多

《 http://hi.baidu.com/jackbillow/blog/item/26effe7e637ca0300ed7da66.html》

一、概述

相信有很多人经常会问同样的一个问题:当 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章

分享到:
评论

相关推荐

    MYSQL 通过分区(Partition)提升MySQL性能

    #### MySQL分区类型 MySQL支持多种类型的分区方式,主要包括: - **水平分区(Horizontal Partitioning)**:根据行数据进行划分,将不同的行分配到不同的分区中。例如,可以通过某一列值的范围来决定行数据属于...

    MySQL 分区

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

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

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

    MySQL分区分表方案实践手册

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

    创建mysql表分区的方法

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

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

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

    mysql分区文档

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

    MySQL分区管理器MySQLPartitionManager.zip

    MySQL Partition Manager 是雅虎开源的 MySQL 分区管理器。它可以帮助你以最小的配置自动创建、维护、清除分区。 标签:MySQL

    mysql表分区

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

    MySQL分区实战

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

    mysql如何进行分区-mysql分区有哪些方法.docx

    通过 `SHOW VARIABLES LIKE '%partition%'` 命令,可以检查 MySQL 服务器是否支持分区功能。 使用分区能有效管理和优化大数据量的表,但需要根据具体的应用场景和需求来选择合适的分区策略,同时也要考虑到对应用...

    mysql分区.pdf

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

    mysql如何进行分区-mysql分区有哪些方法.pdf

    MySQL 数据库是一个广泛使用的开源关系型数据库管理系统,尤其在 PHP Web 开发中极其...在确认 MySQL 服务器支持分区功能后(通过 `SHOW VARIABLES LIKE '%partition%'` 查询),可以进一步考虑分区的具体实现方式。

    mysql分区资源整理

    ### MySQL分区资源整理 #### 一、分区概念与优势 MySQL中的分区技术是数据库优化的重要手段之一,尤其适用于处理大规模数据集。通过分区,数据库能够将一个庞大的表拆分为若干个小部分,使得管理和操作变得更加...

    MySQL分区管理工具

    MySQL分区管理工具是一种优化数据库性能的技术,它将大型表分解为更小、更易管理的部分,以提高查询效率和数据管理的便利性。在MySQL中,分区主要基于表的数据,根据预定义的规则将数据分布到不同的物理部分。这样...

    (mysql面试题)MySQL中的分区表的概念及其作用及代码展示.txt

    ### MySQL中的分区表概念及其作用 #### 一、分区表定义 在MySQL中,分区表是一种高级技术,它允许用户将一个大型表物理地分割成若干个较小的部分,即分区。这种分割方式不仅可以显著提高查询效率,还可以简化数据...

    MySQL分区表:万字详解与实践指南

    ### MySQL分区表:万字详解与实践指南 #### 一、引言 在现代数据库管理中,随着数据量的不断增长,如何高效管理和查询数据成为了一个重要的课题。MySQL作为广泛使用的开源关系型数据库管理系统之一,提供了多种强大...

    MySql数据分区操作之新增分区操作

    首先,尝试直接通过`ALTER TABLE`语句添加分区到非分区表会导致错误,MySQL会返回`ERROR 1505 <HY000>: Partition management on a not partitioned table is not possible`。正确的方法是创建一个新的具有相同结构...

Global site tag (gtag.js) - Google Analytics