MySQL分区表及MySQL5.7对其的改进

基础认识

分区是一种表的设计模式。正确的分区可以极大的提升数据库的查询效率，完成更高质量的SQL编程。但是如果错误的使用分区，那么分区反而是一个累赘。

分区类型

range分区

行数据基于一个给定的连续区间的列值放入分区。

CREATE TABLE `test_11` (
  `id` int(11) NOT NULL,
  `t` date NOT NULL,
  PRIMARY KEY (`id`,`t`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 PARTITION BY RANGE (to_days(t))
(PARTITION p20170801 VALUES LESS THAN (736907) ENGINE = InnoDB,
 PARTITION p20170901 VALUES LESS THAN (736938) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN maxvalue ENGINE = InnoDB);

然后插入4条数据：

insert into test_11 values (1,"20170722"),(2,"20170822"),(3,"20170823"),(4,"20170824");

然后查看information下partitions对分区别信息的统计：

select PARTITION_NAME as "分区",TABLE_ROWS as "行数" from information_schema.partitions where table_schema="mysql_test" and table_name="test_11";
+-----------+--------+
| 分区      | 行数   |
+-----------+--------+
| p20170801 |      1 |
| p20170901 |      3 |
+-----------+--------+
2 rows in set (0.00 sec)

可以看出分区p20170801插入1行数据，p20170901插入的3行数据。
可以是用year、to_days、unix_timestamp等函数对相应的时间字段进行转换，然后分区。

list分区

和range分区一样，只是list分区面向的是离散的值

mysql> CREATE TABLE h2 (
    ->   c1 INT,
    ->   c2 INT
    -> )
    -> PARTITION BY LIST(c1) (
    ->   PARTITION p0 VALUES IN (1, 4, 7),
    ->   PARTITION p1 VALUES IN (2, 5, 8)
    -> );
Query OK, 0 rows affected (0.11 sec)

与RANGE分区的情况不同，没有“catch-all”，如MAXVALUE; 分区表达式的所有预期值应在PARTITION … VALUES IN（…）子句中涵盖。 包含不匹配的分区列值的INSERT语句失败并显示错误，如此示例所示：

mysql> INSERT INTO h2 VALUES (3, 5);
ERROR<