[MySQL 5.1 体验]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章

技术相关:

评论

为什么不能在分区的表上支持Merge呢,期待MySQL的后续版本。

作者说"每个分区都有自己独立的数据、索引文件的存放目录,与此同时,这些目录所在的>
物理磁盘分区可能也都是完全独立的,可以多大提高了磁盘IO吞吐量。"
真的是这样的吗?我用INNODB实验的结果是整个表分了一个.FRA文件和一个.PAR文件,并没有每个分区分开,而且更没有按照我写的磁盘来分布.是不是另外还要修改什么参数?

对innodb而言,需要先指定多个tablespace,然后才会在这些tablespace中自动分区,如果只有一个tablespace的话,自然无法再次分区了。

MySQL方案、培训、支持

给你的祝福,要让你招架不住!

分区数量是否有限制啊?
比如分50个分区也没问题吧?

50个是肯定没问题了,没找到哪里有提到这个上限,可以看下源码,或者自己实验一下吧。

MySQL方案、培训、支持
MySQL 用户组

单个表的分区数量最大为1024,但是有可能受其他全局的条件影响,例如open_files_limit这个变量.

相关信息: http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html

你提到:
对innodb而言,需要先指定多个tablespace,然后才会在这些tablespace中自动分区
请问如何 指定多个tablespace,请明白点说
谢谢

看手册的 15.2.5. 创建InnoDB表空间, http://imysql.cn/docs/MySQL_51_zh/mysql_51.html

MySQL方案、培训、支持
MySQL 用户组

有个关于innodb表空间的问题?
如果我在my.cnf中指定如下数据文件
innodb_data_file_path = /disk1/ibdata1:2000M;/disk2/ibdata2:2000M;/disk3/ibdata3:100M:autoextend
那么我创建的如下的分区表
create table t1(id int,name char(8)) ENGINE=InnoDB
PARTITION BY HASH(`ID`)(
PARTITION p0
DATA DIRECTORY = '/disk1/ibdata1',
PARTITION p1
DATA DIRECTORY = '/disk2/ibdata2'
);
那么t1中的数据是否是分布在这2个数据文件上的呢?另外数据中的其他表(非分区表)是使用的那个数据文件呢?还有就是如果ibdata1满了的话,应该如何增加p0的空间呢?期待中...... 谢谢

又查询了一下Mysql手册12.1.10:有如下介绍
DATA DIRECTORY and INDEX DIRECTORY behave in the same way as in the CREATE TABLE statement's table_option clause as used for MyISAM tables.

如果我没有理解错的话,这个参数只能用于MyISAM而不能用于Innodb。
所以我个人的理解是Innodb分区的存放无法手工指定,各个分区是被自动分配在各个数据文件中的。

如果不对,希望大家指教!

innodb也是可以支持多个文件分区的,前提是使用独立表空间:
innodb_file_per_table = 1
重启mysqld,就可以支持了。

MySQL方案、培训、支持
MySQL 用户组

同意,不过Innodb并不支持DATA DIRECTORY and INDEX DIRECTORY参数。
将分区的不同文件部署到不同磁盘上,似乎只能使用link的方式

可以参见http://forums.mysql.com/read.php?106,155255,155255#msg-155255

新增分区

* 新增 RANGE 分区

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

新增一个RANGE分区。

搞错了,这是新增一个LIST分区吧????