MySQL分区表

1. 分区概述：分区功能不是存储引擎层的完成的，因此不止InnoDB支持分区，MyISAM、NDB也支持。

    （1）是局部分区索引，一个分区中既存放数据有存放了索引。

    （2）如果表中存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分：

mysql> create table t1(
    -> col1 int not null,
    -> col2 date not null,
    -> col3 int not null,
    -> col4 int not null,
    -> unique key (col1,col2)
    -> )
    -> partition by hash(col3)
    -> partitions 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql>  create table t1(
    ->  col1 int not null,
    ->  col2 date not null,
    ->  col3 int not null,
    ->  col4 int not null,
    ->  unique key (col1,col2,col3,col4)
    ->  )
    ->  partition by hash(col3)
    ->  partitions 4;
Query OK, 0 rows affected (0.17 sec)

mysql>

    （3）如果建表没有指定主键、唯一索引时，可以指定任何一列作为分区列：

mysql>  create table t3(
    ->  col1 int not null,
    ->  col2 date not null,
    ->  col3 int not null,
    ->  col4 int not null
    -> )engine=innodb
    -> partition by hash(col3)
    -> partitions 4;
Query OK, 0 rows affected (0.15 sec)

mysql>

2. RANGE分区

mysql> create table t_range(
    -> id int) engine=innodb
    -> partition by range (id)(
    -> partition p0 values less than (10),
    -> partition p1 values less than (20));
Query OK, 0 rows affected (0.09 sec)

mysql>

      启用分区后，表不在由一个ibd文件组成，而是由每个分区组成一个ibd文件

    查询每个区具体信息

mysql> insert into t_range select 9;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t_range selecct 10;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
 for the right syntax to use near 'selecct 10' at line 1
mysql> insert into t_range select 10;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t_range select 15;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.partitons where table_schema=database() and table_name='t_range'\G
ERROR 1109 (42S02): Unknown table 'PARTITONS' in information_schema
mysql> select * from information_schema.partitions where table_schema=database() and table_name='t_range'\G
*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: db2
                   TABLE_NAME: t_range
               PARTITION_NAME: p0
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: `id`
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 10
                   TABLE_ROWS: 1
               AVG_ROW_LENGTH: 16384
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2019-07-08 16:17:27
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 2. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: db2
                   TABLE_NAME: t_range
               PARTITION_NAME: p1
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: `id`
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 20
                   TABLE_ROWS: 2
               AVG_ROW_LENGTH: 8192
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2019-07-08 16:17:27
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
2 rows in set (0.01 sec)

mysql> insert into t_range select 25;
ERROR 1526 (HY000): Table has no partition for value 25
mysql> alter table t_range add partition (partition p2 values less than maxvalue);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t_range select 25;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>

    使用分区的好处：对于查询相应范围的数据不用全盘扫描，只需要查询相应分区即可

mysql> explain select * from t_range where id >=10 and id<20\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_range
   partitions: p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql>

3. LIST分区：相对于range而言，list分区的值是离散的，而不是range分区是连续的

mysql> create table t_list(
    -> a int,
    -> b int) engine=innodb
    -> partition by list(b)(
    -> partition p0 values in (1,3,5,7,9),
    -> partition p1 values in (0,2,4,6,8)
    -> );
Query OK, 0 rows affected (0.16 sec)

mysql> insert into t_list select 1,1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t_list select 1,2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t_list select 1,3;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t_list select 1,4;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select table_name,partition_name, table_rows from information_schema.partitions
    -> where table_name='t_list' and table_schema=database()\G
*************************** 1. row ***************************
    TABLE_NAME: t_list
PARTITION_NAME: p0
    TABLE_ROWS: 2
*************************** 2. row ***************************
    TABLE_NAME: t_list
PARTITION_NAME: p1
    TABLE_ROWS: 2
2 rows in set (0.00 sec)

mysql> explain select * from t_list where b=2 or b=4\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_list
   partitions: p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 75.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> insert into t_list select 1,10;
ERROR 1526 (HY000): Table has no partition for value 10
mysql>

    注：对于插入不在分区中的值，mysql会抛出异常，但MyISAM和innodb处理方式完全不同，myisam会将之前的行都插入后面的数据不插入，而innodb将其视为一个事务，因此任何数据都不被插入。

3. HASH分区

mysql> create table t_hash (
    -> a int,
    -> b datetime) engine=innodb
    -> partition by hash (year(b))
    -> partitions 4;
Query OK, 0 rows affected (0.20 sec)

mysql> insert into t_hash select 1, '2010-04-01';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select table_name,partition_name,table_rows from information_schema.partitions where
    -> table_schema=database() and table_name='t_hash'\G
*************************** 1. row ***************************
    TABLE_NAME: t_hash
PARTITION_NAME: p0
    TABLE_ROWS: 0
*************************** 2. row ***************************
    TABLE_NAME: t_hash
PARTITION_NAME: p1
    TABLE_ROWS: 0
*************************** 3. row ***************************
    TABLE_NAME: t_hash
PARTITION_NAME: p2
    TABLE_ROWS: 1
*************************** 4. row ***************************
    TABLE_NAME: t_hash
PARTITION_NAME: p3
    TABLE_ROWS: 0
4 rows in set (0.01 sec)

mysql> insert into t_hash select 1, '2016-04-01';
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t_hash select 1, '2017-04-01';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t_hash select 1, '2018-04-01';
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select table_name,partition_name,table_rows from information_schema.partitions where
    -> table_schema=database() and table_name='t_hash'\G
*************************** 1. row ***************************
    TABLE_NAME: t_hash
PARTITION_NAME: p0
    TABLE_ROWS: 1
*************************** 2. row ***************************
    TABLE_NAME: t_hash
PARTITION_NAME: p1
    TABLE_ROWS: 1
*************************** 3. row ***************************
    TABLE_NAME: t_hash
PARTITION_NAME: p2
    TABLE_ROWS: 2
*************************** 4. row ***************************
    TABLE_NAME: t_hash
PARTITION_NAME: p3
    TABLE_ROWS: 0
4 rows in set (0.00 sec)

mysql>

4. linear hash分区：它与hash分区相似，但是它的算法更加复杂：

mysql> create table t_linear_hash(
    -> a int,
    -> b datetime) engine=innodb
    -> partition by linear hash (year(b))
    -> partitions 4;
Query OK, 0 rows affected (0.24 sec)

mysql> insert into t_linear_hash select 1, '2019-07-09';
Query OK, 1 row affected (0.11 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select table_name,partition_name,table_rows from information_schema.partitions where
    -> table_schema=database() and table_name='t_linear_hash'\G
*************************** 1. row ***************************
    TABLE_NAME: t_linear_hash
PARTITION_NAME: p0
    TABLE_ROWS: 0
*************************** 2. row ***************************
    TABLE_NAME: t_linear_hash
PARTITION_NAME: p1
    TABLE_ROWS: 0
*************************** 3. row ***************************
    TABLE_NAME: t_linear_hash
PARTITION_NAME: p2
    TABLE_ROWS: 0
*************************** 4. row ***************************
    TABLE_NAME: t_linear_hash
PARTITION_NAME: p3
    TABLE_ROWS: 1
4 rows in set (0.00 sec)

mysql>

    分区判断：

        （1） v=power(2, ceiling (log (2, num))) 其中num为分区数, log(2, num)求以2为底num的对数，ceiling()为向上取整

        log(2, 4)=2 , ceiling(2)=2, v=power(2, 2)=4

      （2）n=year('2019-07-09') & (v-1) = 2019 & 3 = 3        ，因此在第三分区

    优点：增加、删除、合并和拆分分区快捷

    缺点：数据分区现对于hash不太均衡

5. key分区:

    与hash分区相似，不同在于hash使用用户定义的函数进行分区，而key分区使用mysql数据库提供的函数进行分区

6. columns分区

    特点：分区的条件不需要是整数，这点不同于range、list、hash、key

    支持类型:

        整数：int、smallint、tinyint、bigint

        日期：date、datetime

        字符串：char、varchar、binary、varbinary

mysql> create table t_columns_range(
    -> a int,
    -> b datetime) engine=innodb
    -> partition by range columns (b)(
    -> partition p0 values less than ('2018-01-01'),
    -> partition p1 values less than ('2019-01-01')
    -> );
Query OK, 0 rows affected (0.12 sec)
//按字符串进行分区
mysql> create table customers(
    -> fisrt_name varchar(25),
    -> last_name varchar(25),
    -> street_1 varchar(30),
    -> street_2 varchar(30),
    -> city varchar(15),
    -> renewal date
    -> )
    -> partition by list columns(city) (
    -> partition pregion_1 values in('Oskarshamn','Hogsby','Monsteras'),
    -> partition pregion_2 values in('Bimmerby', 'Hultsfred', 'Vastervik'),
    -> partition pregion_3 values in('Nossjo','Eksjo','Vetland'),
    -> partition pregion_4 values in('Uppvidinge','Alvesta','Vaxjo')
    -> );
Query OK, 0 rows affected (0.19 sec)
//使用多列进行分区
mysql> create table rcx (
    -> a int,
    -> b int,
    -> c char(3),
    -> d int
    -> )
    -> partition by range columns(a,d,c) (
    -> partition p0 values less than (5,10,'ggg'),
    -> partition p1 values less than (10,20,'mmm'),
    -> partition p2 values less than (15,30,'sss'),
    -> partition p3 values less than (maxvalue, maxvalue,maxvalue)
    -> );
Query OK, 0 rows affected (0.21 sec)

mysql>

7. 子分区：在分区的基础上再进行分区

//创建分区表
mysql> create table t_sub (a int, b date) engine=innodb
    -> partition by range (year(b))
    -> subpartition by hash( to_days(b))
    -> subpartitions 2 (
    -> partition p0 values less than (1990),
    -> partition p1 values less than (2000),
    -> partition p2 values less than maxvalue
    -> );
Query OK, 0 rows affected (0.24 sec)

    物理文件如下

//创建分区表时显示指出各子分区的名称
mysql> create table t_sub2 (a int, b date)
    -> partition by range(year(b))
    -> subpartition by hash(to_days(b)) (
    -> partition p0 values less than (1990) (
    -> subpartition s0,
    -> subpartition s1
    -> ),
    -> partition p1 values less than (2000) (
    -> subpartition s2,
    -> subpartition s3
    -> ),
    -> partition p2 values less than maxvalue (
    -> subpartition s4,
    -> subpartition s5
    -> )
    -> );
Query OK, 0 rows affected (0.18 sec)

mysql>

    物理文件如下：

要求：每个子分区的数量必须相同

          每个subpartition子句必须包含子分区的一个名称

          每个子分区内，子分区的名称必须是唯一的

//在创建分区时可以显示指定数据文件的位置，索引位置不支持指定
mysql> create table t_sub3 (a int, b date) engine=innodb
    -> partition by range(year(b))
    -> subpartition by hash(to_days(b)) (
    -> partition p0 values less than (1990) (
    -> subpartition s0 data directory = 'D:\mysql\data' ,
    -> subpartition s1 data directory = 'D:\mysql\data'
    -> ),
    -> partition p1 values less than (2000) (
    -> subpartition s2 data directory = 'D:\mysql\data',
    -> subpartition s3
    ->  data directory = 'D:\mysql\data'
    -> ),
    -> partition p2 values less than maxvalue (
    -> subpartition s4 data directory = 'D:\mysql\data',
    -> subpartition s5 data directory = 'D:\mysql\data'
    -> )
    -> );
Query OK, 0 rows affected (0.19 sec)

mysql>

8. 分区中的null值：

    (1) range分区：将插入的null值放在最左边分区

mysql> create table t_range_null(
    ->  a int,
    ->  b int) engine=innodb
    ->  partition by range(b)(
    -> partition p0 values less than (10),
    -> partition p1 values less than (20),
    ->  partition p2 values less than maxvalue
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql> insert into t_range_null select 1,1;
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t_range_null select 1,null;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t_range_null\G
*************************** 1. row ***************************
a: 1
b: 1
*************************** 2. row ***************************
a: 1
b: NULL
2 rows in set (0.00 sec)

mysql> select table_name,partition_name,table_rows from information_schema.partitions
    -> where table_schema=database() and table_name='t_range_null'\G
*************************** 1. row ***************************
    TABLE_NAME: t_range_null
PARTITION_NAME: p0
    TABLE_ROWS: 2
*************************** 2. row ***************************
    TABLE_NAME: t_range_null
PARTITION_NAME: p1
    TABLE_ROWS: 0
*************************** 3. row ***************************
    TABLE_NAME: t_range_null
PARTITION_NAME: p2
    TABLE_ROWS: 0
3 rows in set (0.00 sec)

mysql> alter table t_range_null drop partition p0;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t_range_null;
Empty set (0.00 sec)
//删除分区，也删了null记录

    (2) list分区：要显示的指出将null值放在哪个分区

    (3) hash和key分区：任何分区都将null值返回为0

 

9. 分区和性能

    (1) 对于olap（在线分析处理）应用采用分区可以很好的提高查询性能

    (2) 而oltp则不一定能提高查询性能