MySQL分區(qū)學(xué)習(xí)

https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html

一、      分區(qū)概述

分區(qū)是一種表設(shè)計(jì)模式，自5.1版本開(kāi)始支持分區(qū)，邏輯上是一張表，物理上可能是多個(gè)對(duì)象。其中MyISAM、INNODB、NDB等存儲(chǔ)引擎都支持分區(qū)，但CSV、MERGE、FEDORATED不支持分區(qū)。

1.      分區(qū)種類

水平分區(qū)：對(duì)表記錄進(jìn)行拆分，同一表的不同行記錄分配到不同的物理文件中。

         Range 分區(qū)：行根據(jù)基于屬于一個(gè)給定連續(xù)區(qū)間的列值放入分區(qū)，自MYSQL5.5開(kāi)始支持列范圍分區(qū)，最常用的分區(qū)。

         List分區(qū)：和Range分區(qū)一樣，只是List分區(qū)是面對(duì)離散值，自MYSQL5.5開(kāi)支支持列List分區(qū)。

         Hash分區(qū)：根據(jù)用戶自定義的表達(dá)式的返回值進(jìn)行分區(qū)，返回值不能為負(fù)數(shù)。

         Key分區(qū)：根據(jù)MYSQL數(shù)據(jù)庫(kù)提供的散列函數(shù)來(lái)進(jìn)行分區(qū)。

垂直分區(qū)：對(duì)表字段進(jìn)程拆分（MYSQL暫不支持），同一表中不同的列分配在不同的物理文件中。

2.      分區(qū)優(yōu)、缺點(diǎn)

優(yōu)點(diǎn)：

         可極大提高查詢效率；

         主要用于數(shù)據(jù)庫(kù)的高可用性，方便管理；

 缺點(diǎn)：

         無(wú)論何種分區(qū)，如果表中存在主鍵或者唯一鍵索引，分區(qū)列必須是唯一索引的一個(gè)組成部分。

二、      分區(qū)類型詳解

1.      Range分區(qū)

 create table t_range(id int)

partition by range(id)

(partition p0 values less than (100),

partition p1 values less than(500),

partition p2 values less than maxvalue );

insert into t_range values(10),(120),(600);

 (root:localhost:Sat Jul  8 20:05:12 2017)[dbtest]> \! ls -lnrth /home/mysql/dbtest

total 320K

-rw-rw---- 1 500 500   61 Mar 17 15:58 db.opt

-rw-rw---- 1 500 500   32 Jul  8 20:04 t_range.par               ##存儲(chǔ)分區(qū)信息

-rw-rw---- 1 500 500 8.4K Jul  8 20:04 t_range.frm

-rw-rw---- 1 500 500  96K Jul  8 20:05 t_range#P#p2.ibd

-rw-rw---- 1 500 500  96K Jul  8 20:05 t_range#P#p1.ibd

-rw-rw---- 1 500 500  96K Jul  8 20:05 t_range#P#p0.ibd

(root:localhost:Sat Jul  8 20:14:13 2017)[(none)]> select * from information_schema.partitions where table_name='t_range' \G

*************************** 1. row ***************************

                TABLE_CATALOG: def

                 TABLE_SCHEMA: dbtest

                   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: 100

                   TABLE_ROWS: 1

               AVG_ROW_LENGTH: 16384

                  DATA_LENGTH: 16384

              MAX_DATA_LENGTH: NULL

                 INDEX_LENGTH: 0

                    DATA_FREE: 0

                  CREATE_TIME: NULL

                  UPDATE_TIME: NULL

                   CHECK_TIME: NULL

                     CHECKSUM: NULL

            PARTITION_COMMENT:

                    NODEGROUP: default

              TABLESPACE_NAME: NULL

*************************** 2. row ***************************

                TABLE_CATALOG: def

                 TABLE_SCHEMA: dbtest

                   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: 500

                   TABLE_ROWS: 1

               AVG_ROW_LENGTH: 16384

                  DATA_LENGTH: 16384

              MAX_DATA_LENGTH: NULL

                 INDEX_LENGTH: 0

                    DATA_FREE: 0

                  CREATE_TIME: NULL

                  UPDATE_TIME: NULL

                   CHECK_TIME: NULL

                     CHECKSUM: NULL

            PARTITION_COMMENT:

                    NODEGROUP: default

              TABLESPACE_NAME: NULL

*************************** 3. row ***************************

                TABLE_CATALOG: def

                 TABLE_SCHEMA: dbtest

                   TABLE_NAME: t_range

               PARTITION_NAME: p2

            SUBPARTITION_NAME: NULL

   PARTITION_ORDINAL_POSITION: 3

SUBPARTITION_ORDINAL_POSITION: NULL

             PARTITION_METHOD: RANGE

          SUBPARTITION_METHOD: NULL

         PARTITION_EXPRESSION: id

      SUBPARTITION_EXPRESSION: NULL

        PARTITION_DESCRIPTION: MAXVALUE

                   TABLE_ROWS: 1

               AVG_ROW_LENGTH: 16384

                  DATA_LENGTH: 16384

              MAX_DATA_LENGTH: NULL

                 INDEX_LENGTH: 0

                    DATA_FREE: 0

                  CREATE_TIME: NULL

                  UPDATE_TIME: NULL

                   CHECK_TIME: NULL

                     CHECKSUM: NULL

            PARTITION_COMMENT:

                    NODEGROUP: default

              TABLESPACE_NAME: NULL

3 rows in set (0.00 sec)

2.      List分區(qū)

-rw-rw---- 1 500 500   28 Jul  8 21:39 t_list.par

-rw-rw---- 1 500 500 8.4K Jul  8 21:39 t_list.frm

-rw-rw---- 1 500 500  96K Jul  8 21:39 t_list#P#p1.ibd

-rw-rw---- 1 500 500  96K Jul  8 21:39 t_list#P#p0.ibd

CREATE TABLE `t_list` (

  `id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY LIST (id)

(PARTITION p0 VALUES IN (1,3,5,7,9) ENGINE = InnoDB,

 PARTITION p1 VALUES IN (2,4,6,8,10) ENGINE = InnoDB) */;

3.      Hash分區(qū)

HASH分區(qū)將數(shù)據(jù)均勻的分布到預(yù)先定義的各個(gè)分區(qū)中，保障各個(gè)分區(qū)的數(shù)據(jù)數(shù)量大致一樣的。在range和list分區(qū)定義時(shí)，必須明確指定分區(qū)的列值或列值集合保存在哪個(gè)分區(qū)中，而hash分區(qū)自動(dòng)完成列值的分配，平均的將數(shù)據(jù)放在不同的分區(qū)。

CREATE TABLE `t_hash` (

  `id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY HASH (id)

PARTITIONS 4 */

-rw-rw---- 1 500 500   32 Jul  8 21:53 t_hash.par

-rw-rw---- 1 500 500 8.4K Jul  8 21:53 t_hash.frm

-rw-rw---- 1 500 500  96K Jul  8 21:54 t_hash#P#p3.ibd

-rw-rw---- 1 500 500  96K Jul  8 21:54 t_hash#P#p2.ibd

-rw-rw---- 1 500 500  96K Jul  8 21:54 t_hash#P#p1.ibd

-rw-rw---- 1 500 500  96K Jul  8 21:54 t_hash#P#p0.ibd

4.      Key分區(qū)

Hash分區(qū)根據(jù)用戶自定義的函數(shù)進(jìn)行分區(qū)，key使用MYSQL數(shù)據(jù)庫(kù)提供的函數(shù)進(jìn)行分區(qū)。

CREATE TABLE `t_key` (

  `id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY KEY (id)

PARTITIONS 4 */;

5.      Columns分區(qū)

Range、List、Hash和Key分區(qū)都是針對(duì)整型分區(qū)，如果不是整型分區(qū)，則需要通過(guò)相關(guān)函數(shù)轉(zhuǎn)換。但通過(guò)columns分區(qū)，不需要轉(zhuǎn)換。

Columns支持int/small int/tinyint/bigint/date/datetime/char/varchar/binary支持，對(duì)float/decimal/blob/text不支持

create table t_range_columns(dtime datetime)

partition by range columns (dtime)

(partition p0 values less than ('2016-01-01'),

partition p1 values less than('2017-01-01'),

partition p2 values less than maxvalue );

6.      子分區(qū)

在分區(qū)的基礎(chǔ)上再進(jìn)行分區(qū)，也稱之為復(fù)合分區(qū)。

三、      分區(qū)維護(hù)管理

Alter table table_name

  | ADD PARTITION (partition_definition)

  | DROP PARTITION partition_names

  | TRUNCATE PARTITION {partition_names | ALL }

  | COALESCE PARTITION number

  | REORGANIZE PARTITION partition_names INTO (partition_definitions)

  | ANALYZE PARTITION  {partition_names | ALL }

  | CHECK PARTITION  {partition_names | ALL }

  | OPTIMIZE PARTITION  {partition_names | ALL }

  | REBUILD PARTITION  {partition_names | ALL }

  | REPAIR PARTITION  {partition_names | ALL }

  | REMOVE PARTITIONING

1.      增加分區(qū)

alter table t_range add partition ( partition p2 values less than  maxvalue) ;

2.      刪除分區(qū)

alter table t_range drop partition p2;

alter table t_list remove partitioning;

3.      查看分區(qū)

information_schema.partitions

4.      清除分區(qū)數(shù)據(jù)

alter table t_range truncate partition p2;

5.      解析分區(qū)

(root:localhost:Sat Jul  8 21:30:03 2017)[dbtest]> explain partitions select * from  t_range \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t_range

   partitions: p0,p1,p2

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 3

        Extra: NULL

1 row in set (0.00 sec)

(root:localhost:Sat Jul  8 21:30:18 2017)[dbtest]> explain partitions select * from  t_range  where id=800\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t_range

   partitions: p2

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 2

        Extra: Using where

1 row in set (0.00 sec)

（只查詢指定的分區(qū)）

6.      交換分區(qū)

MYSQL5.6支持了交換分區(qū)，具體語(yǔ)法如下：

alter table t_range  exchange partition p0 with table t;

將分區(qū)表t_range的P0分區(qū)的數(shù)據(jù)交換到t表中，而t表的數(shù)據(jù)也會(huì)交換到t_range表中，交換是雙向的。