MySQL分区

MYSQL 分区

分区的优点

1. 存储更多数据
2. 优化查询，只扫描必要的一个或者多个分区，针对 count()和 sum()只要对分区统计再汇总
3. 对于过期或不需要保存的数据，操作分区更快
4. 跨多个磁盘来分散数据查询，以获得更大的查询吞吐量

分区概述

分区键的引入。

查询是否支持分区

mysql> show variables like '%partition%';
Empty set (0.01 sec)

mysql> show variables like '%partition%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| innodb_adaptive_hash_index_partitions | 1     |
+---------------------------------------+-------+

MySQL 支持创建分区的引擎：MyISam、InnoDB、Memory，不支持分区：MERGE、CSV

在 MySQL5.1 中，同一个分区表的所有分区必须使用同一个存储引擎，但是在同一个 MySQL 服务器中或者同一个数据库中、对于不同的分区表可以使用不同的存储引擎。

MySQL 的分区适用于一个表的所有数据和索引。

设置引擎 ENGINE 必须在 CREATE TABLE 语句中的其他任何部分之前

mysql> create table emp(empid int,salay decimal(7,2),birth_date date) 
engine=innodb 
partition by hash(month(birth_date)) 
partitions 6;
Query OK, 0 rows affected (0.06 sec)

分区类型

1. RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。
2. LIST 分区：类似于 RANGE 分区，区别在于 LIST 分区是基于枚举出的值列表分区，RANGE 是基于给定的连续区间范围分区
3. HASH 分区：基于给定的分区个数，把数据分配到不同的分区
4. KEY分区：类似于 RANGE 分区

在 mysql5.1 中：range、list、hash 分区键必须是 int 类型，key 还可以使用 blog、text。在 mysql5.5 中已经支持非整数类型做分区键

分区时注意

1. 要么分区表上没有主键 / 唯一键，要么分区表主键 / 唯一键必须包含分区键。（否则会报错）
2. 分区的名字不区分大小写

RANGE 分区

利用取值范围将数据分成分区，区间要连续且不能互相重叠。

RANGE 分区中，分区键如果是 NULL 值会被当作一个最小值来处理。

mysql> create table emp_date(
    id int not null,
    ename varchar(30),
    hired date not null default '1970-01-01',
    separated date not null default '9999-12-31',
    job varchar(30) not null,
    store_id int not null
) 
    partition by range (year(separated)) (partition p0 values less than (1995), 
        partition p1 values less than (2000), 
        partition p2 values less than (2005) 
    );
Query OK, 0 rows affected (0.04 sec)

超出最大分区范围会报错，要是有个最大值 maxvalue 兜底就好了！你想要的都给你！

mysql> alter table emp_date add partition (partition p3 values less than maxvalue);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

前面说了 RANGE 只支持 int 做分区键，太没有人性了，现实业务场景那么多，MySQL5.5 起改进了这个问题，新增 RANGE COLUMNS 分区支持非整型分区，这样创建日期分区就不用通过函数多此一举了。no code no bb!

mysql> drop  table `emp_date`;
Query OK, 0 rows affected (0.04 sec)

mysql> create table emp_date(
    ->     id int not null,
    ->     ename varchar(30),
    ->     hired date not null default '1970-01-01',
    ->     separated date not null default '9999-12-31',
    ->     job varchar(30) not null,
    ->     store_id int not null
    -> ) 
    ->     partition by range columns (separated) (->         partition p0 values less than ('1995-01-01'), 
    ->         partition p1 values less than ('2000-01-01'), 
    ->         partition p2 values less than ('2005-01-01') 
    ->     );
Query OK, 0 rows affected (0.04 sec)

这种操作还不够常用，经常要按天分区怎么搞？

MySQL5.1：分区日期处理函数只有 year() 和to_days()

MySQL5.5：增加了to_seconds()，把日期转换成秒。

说了那么多，RANGE 分区功能特别适用哪些情况？

1. 当需要删除过期的数据，比如某个分区的数据已经完全没有意义了，请执行 alter table emp_date drop partition p0 删除分区。对动辄成千上万的数据，比运行 delete 要高效的多！
2. 经常运行包含分区键的查询，MySQL 很快能找到对应的分区，并且在对应的分区扫描。

mysql> insert into emp_date (id,ename,hired,separated,job,store_id) values('7934','miller','1995-01-01','1995-01-01','care',50);
Query OK, 1 row affected (0.01 sec)

mysql> explain partitions select count(1) from emp_date where store_id >=20\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp_date
   partitions: p0,p1,p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 2 warnings (0.00 sec)

ERROR: 
No query specified

LIST 分区

特点：一个枚举列表的值的集合。RANGE 是连续区间值的集合

mysql> CREATE TABLE expenses ( 
    expense_date date NOT NULL, 
    category INT, amount DECIMAL (10, 3) 
) 
    PARTITION BY list (category) (-> PARTITION p0 VALUES IN ( 3, 5),
    -> PARTITION p1 VALUES IN (1, 11),
    -> PARTITION p2 VALUES IN (4, 9),
    -> PARTITION p3 VALUES IN (2) 
    -> );
Query OK, 0 rows affected (0.07 sec)

前面有说过，LIST 也是仅支持整型，如果你是 MySQL5.1，还得单独建个表。

MYSQL5.5 中支持非整型分区，真贴心！

mysql> CREATE TABLE expensess ( 
    -> expense_date date NOT NULL, 
    -> category varchar (30), 
    -> amount DECIMAL (10, 3) 
    -> ) 
    -> PARTITION BY list columns (category) (-> PARTITION p0 VALUES IN ('loading','food'),
    -> PARTITION p1 VALUES IN ('ear', 'frist'),
    -> PARTITION p2 VALUES IN ('hire','horse'),
    -> PARTITION p3 VALUES IN ('fees') 
    -> );
Query OK, 0 rows affected (0.06 sec)

LIST 分区，整型是 list（expr），字符串是 list columns (expr)