MySQL表分区

MySQL中当数据量较大时，查询速度会下降比较严重，采用表分区，可以有效的提升查询速度。

关于表分区的优点和缺点网上很多，根据自己需要选择是采用表分区还是分表都可以，还可以使用现在流行的非关系型数据库。

表分区可以在表创建时，也可以在创建后修改为分区表。

分区关键词和基本语法

已有表分区和创建表时分区语法相同，后面的语法都是partition by ...

partition by 支持 range/hash/list/key这些类型，一般根据range或者hash分区，还可以组合采用子分区。

Range        范围分区根据某个列的值分区，一般采用自增主键字段或者日期字段分区
Hash           对某一个表的列进行hash值后得到的数分到不同的分区。
Key            上面Hash模式的一种扩展，这里的Key是MySQL通过对字段的hash产生的值。
List             通过字段内预定义的会出现的值来对数据进行分区。
Composite  组合使用上面的东西

创建表时分区语法如下：

create table xxxx (

c1 int,

c2 ..

) ..partition by range(c1) partitions 2 (partition part_1 values less than (100),partition part_2 values less than (200)) ..

注意，表分区的分区名称，同一个表内不能重复，比如part_1必须唯一（包含子分区内），否则会提示区块名称错误。

已有表添加分区如下：

alter table xxx partition by range(c1) partitions 2 (partition part_1 values less than (100),partition part_2 values less than (200));

查看表分区情况：

SELECT
    partition_name part,
    partition_expression expr,
    subpartition_expression sub_expr,
    partition_description descr,
    table_rows
FROM
  INFORMATION_SCHEMA.partitions
WHERE
  TABLE_SCHEMA = schema()
  AND TABLE_NAME='表名称';

添加分区：

alter table xxx add partition (partition part_3 values less than (300));

合并分区：

alter table xxx reorganize partition part_1,part_2,part_3 into (partition part_uni values less than (1000));

注意，分区擦除和删除，擦除是去掉分区，数据不变，删除是数据也没了，要谨慎。

擦除用alter table xxx remove partitioning擦除所有分区，alter table xxx drop partition part_1将删除所有在part_1内的数据。

关于子分区的使用也很简单，子分区就是组合使用分区，例如：

partition by range(c1) partitions 3 --主分区3个

subpartition by hash(dayofyear(c2)) subpartitions 2 --每个主分区下子分区，2个（这里c2是日期类型)

(

partition main_par_1 values less than (100) (subpartition sub_par_1, subpartition sub_par_2),

partition main_par_2 values less than (100) (subpartition sub_par_21, subpartition sub_par_22),

partition main_par_3 values less than maxvalue (subpartition sub_par_31, subpartition sub_par_32)

)

下面几个是list key 的小例子：

by list

create table xxx (

c1 int,

c2 int

) partition by list(c2) partitions 2

 ( partition p_1 values in (1,2,3),partition p_2 values in (4,5,6))

by key

create table xxx (

c1 int,

c2 int

) partition by key(c2) partitions 2

 ( partition p_1,partition p_2)

by hash

create table xxx (

c1 int,

c2 int

) partition by hash(c2) partitions 2

 ( partition p_1,partition p_2)

扩展 (mrg_myisam 引擎分表)

mrg_myisam是msyql的一种表引擎，支持将字表联合进行插入和查询操作。该引擎建立主表相当于一个空壳，类似于mysql的federated引擎对应的表。此外字表间数据结构必须与主表相同。

看下面例子:

两张子表：

CREATE TABLE `t_merg_001` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`name`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1
CHECKSUM=0
ROW_FORMAT=DYNAMIC
DELAY_KEY_WRITE=0
;

CREATE TABLE `t_merg_002` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`name`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1
CHECKSUM=0
ROW_FORMAT=DYNAMIC
DELAY_KEY_WRITE=0
;

主表：

CREATE TABLE `t_merg_all` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`name`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=MRG_MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
union = (`t_merg_001`,`t_merg_002`)
insert_method=last
ROW_FORMAT=DYNAMIC
;

对于主表的查询相当于联合两张子表，插入操作，insert_method方法支持no,first,last三个参数，分别是不允许插入，插入到第一张表，插入到最后一张表。

对于查询和插入可以直接通过主表进行，方便一些，但是直接操作主表也就导致分表对于查询的作用不是太大了，因为联合两张表查询效率也不是很高。所以能确定数据归属的情况下，还是直接操作子表效率高一些。