[MySQL 5.1 体验]MySQL 分区

一、概述

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

• 与单个磁盘或文件系统分区相比，可以存储更多的数据
• 很容易就能删除不用或者过时的数据
• 一些查询可以得到极大的优化
• 涉及到 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个。
  • 
    
• 本文转自叶金荣51CTO博客，原文链接：http://blog.51cto.com/imysql/308320，如需转载请自行联系原作者