mysql 表分区

通常来说，对于表的优化，除了对表结构的优化，还可以进一步使用分表或者表分区去提高效率。

主要介绍了表分区的几种分区方式和管理操作。（附带操作的SQL）

针对日常开发中我们经常会遇到大数据表，比如存储了百万级乃至千万级条记录的表。这样的表数据过于庞大，导致数据库在增删查改的时候耗时太长，效率低下，如果涉及到联合查询的情况，情况就更糟。所以，分表和表分区的目的也就是为了提高数据表的增删改查的效率。

先说下表分区的概念：

表分区是将一个表中的数据分段划分在多个位置存放，可以是同一块磁盘也可以是不同的磁盘或者不同的设备机器。表分区后，表面上还是一张表，但数据散列到多个位置了。读写的时候操作还是表名字，但是数据库会自动去组织分区的数据。

首先可以使用命令查看是否支持分区：

在5.6之前，查看当前配置是否支持分区：SHOW VARIABLES LIKE '%partition%';

在5.6以后，使用：SHOW PLUGINS;

显示ACTIVE就代表分区支持已激活。

讲表分区之前先说下myisam和Innodb两种存储引擎的数据结构。

myisam：

myisam的存储可以在mysql.ini或mysql.conf文件中查看数据保存。

打开这个目录可以看见有.myd 、.myi 、.frm 、.par文件。其中是.par是表分区的结构文件，如果没有分区则没有这个.par文件。

 

.myd 文件代表 数据信息文件 data文件

.myi 索引信息文件 index文件

.frm 表结构定义文件

db.opt 可以用记事本打开，只有

default-character-set=latin1
default-collation=latin1_swedish_ci

两个属性。
这两个属性是用来记录该库的默认字符集编码和字符集排序规则的。如果你当前创建数据库指定默认字符集和排序规则，那么后续创建的表如果没有指定字符集和排序规则，那么该新建的表将采用db.opt文件中指定的属性。

Innodb:

Innodb的数据信息文件存在此文件中，它是数据和索引整体一起存储的。但是表结构还是会存储到上面路径.frm文件中。

表分区支持的四种方式：

第一种：
HASH 取余分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。

以int字段hash分区

CREATE TABLE test_int_hash (
    id INT (11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR (100) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 PARTITION BY HASH (id) PARTITIONS 7;

以时间函数hash分区

CREATE TABLE test_month_hash (
    id INT (11) NOT NULL AUTO_INCREMENT,
    title VARCHAR (100) NOT NULL,
    create_date datetime DEFAULT NULL COMMENT '创建日期',
    PRIMARY KEY (id, create_date)
) ENGINE = INNODB DEFAULT CHARSET = utf8 PARTITION BY HASH (MONTH(create_date)) PARTITIONS 12;

第二种：

key分区：类似于按hash分区，区别在于key分区只支持计算一列或多列，且mysql服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

CREATE TABLE test_key(
    id INT (11) NOT NULL AUTO_INCREMENT,
    title VARCHAR (100) NOT NULL,
    create_date datetime COMMENT '创建日期',
    PRIMARY KEY (id)
) ENGINE = INNODB DEFAULT CHARSET = utf8 PARTITION BY KEY (id) PARTITIONS 4;

第三种：

range 分区：基于属于一个给定连续区间的列值，把多行分配给分区。如时间，连续的常量值等，按年分区。

CREATE TABLE test_range (
    id INT (11) NOT NULL AUTO_INCREMENT,
    title VARCHAR (100) NOT NULL,
    birthday DATE,
    PRIMARY KEY (id, birthday)
) ENGINE = myisam DEFAULT CHARSET = utf8 PARTITION BY RANGE (YEAR(birthday))(
    PARTITION p_70 VALUES less than (1980),
    PARTITION p_80 VALUES less than (1990),
    PARTITION p_90 VALUES less than (2000),
    PARTITION p_00 VALUES less than MAXVALUE
);

第四种：

list 分区：和range分区相似，区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择。比如按照日期中的四个季节来分区。

CREATE TABLE test_list (
    id INT (11) NOT NULL AUTO_INCREMENT,
    title VARCHAR (100) NOT NULL,
    birthday DATE,
    PRIMARY KEY (id, birthday)
) ENGINE = myisam DEFAULT CHARSET = utf8 PARTITION BY list (month(birthday))(
    PARTITION spring VALUES in(3,4,5),
    PARTITION summer VALUES in(6,7,8),
    PARTITION autumn VALUES in(9,10,11),
    PARTITION winter VALUES in(12,1,2)
);

以上分区的截图，我用了Innodb和myisam两种引擎。

表分区管理：

1、key和hash都属于取余分区，所以操作是一样的。

alter table test_key  add partition partitions N;   --增加分区

alter table test_key_1  COALESCE partition  N; --删减分区

--N代表再加或者再减几个分区

注意：采用取余算法的分区数量的修改，不会导致已有分区数据的丢失，因为执行会重新分配数据到新的分区。

2、range和list都属于条件分区，删除条件算法的分区，会导致分区数据丢失。添加分区则不会。

alter table test_range drop partition p_00;

alter table test_range  add partition (partition p_00 VALUES less than (2010));

最后注意：

在实际业务中，我们会经常碰见平均分配，即按照主键进行key(primary key)。如果平均分配不适合，需要某种业务逻辑分区的话，就选择那种最容易被筛选的字段，如整型。