MySQL表分区

分区原理

• 根据一定规则将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看只有一张表，但是底层确实由多个物理分区组成。
• 分区后，表面上还是一张表，但数据散列到多个位置。应用程序读读写的时候操作的还是大表名字，MySQL自动去组织分区的数据。

分区类型

Range分区

mysql> CREATE TABLE range_subarea(id INT, name CHAR(10)) 
    -> PARTITION BY RANGE(id) (
    -> PARTITION p1 VALUES LESS THAN (3),
    -> PARTITION p2 VALUES LESS THAN (5),
    -> PARTITION p3 VALUES LESS THAN (7),
    -> PARTITION p4 VALUES LESS THAN (9),
    -> PARTITION p5 VALUES LESS THAN (11),
    -> PARTITION p6 VALUES LESS THAN MAXVALUE);

插入数据

mysql> INSERT INTO range_subarea VALUES(1, 'aaa'),(2, 'bbb'),(3, 'ccc'),(4, 'ddd'),(5, 'eee'),(6, 'fff'),(7, 'ggg'),(8, 'hhh'),(9, 'iii'),(10, 'jjj');
    -> INSERT INTO range_subarea SELECT * FROM range_subarea;    #反复执行，让数据上千万

我们比较一下拥有同样数据的两张表，一张带分区一张不带分区，查询速度的比较

对比结果显而易见，有分区的表查询速度比没有分区的表查询速度快很多。

我们再来看看SQL的执行计划：

注：我的版本是5.6，在MySQL5.7版以下的版本都需要加PARTITIONS才能看见分区信息

这里可以看出我的执行计划的分区是p3。

List分区

mysql> CREATE TABLE list_subarea(id INT, name CHAR(10)) 
    -> PARTITION BY LIST(id)
    -> (PARTITION p1 VALUES IN (1,3,5,7,9),
    -> PARTITION p2 VALUES IN (2,4,6,8,10));

list分区是指定id进行分区的。

Hash分区

普通hash

mysql> CREATE TABLE hash_subarea(id INT, name CHAR(10)) 
    -> PARTITION BY HASH(id) PARTITIONS 10;

普通hash是就根据id的取模的方式分配数据的。

线性hash

mysql> CREATE TABLE linear_hash_subarea(id INT, name CHAR(10)) 
    -> PARTITION BY LINEAR HASH(id) PARTITIONS 10;

可扩展的hash。

Key分区

mysql> CREATE TABLE key_subarea(id INT, name CHAR(10)) 
    -> PARTITION BY LINEAR KEY(id) PARTITIONS 10;

查看表分区

mysql> SELECT partition_name part, 
    -> partition_expression expr, 
    -> partition_description descr, 
    -> table_rows 
    -> FROM INFORMATION_SCHEMA.partitions 
    -> WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='table_name';     

 水平分表与表分区

　　水平分表与表分区区别在于前者是将创建多张表分散存放数据，而后者是逻辑上一张表，但是物理上分为个存储文件。这里就水平分表与表分区的优缺点做以概括：

• 由于表分区是一张逻辑表，表名只有一个，所以数据的查询、排序、分页等等功能不会有分表的问题。
• 一张逻辑表那么请求的入口只有一个，对读写I/O的压力较大
• 综上所述，表分区利于读而不利于写