MySQL分割槽學習
https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html
一、 分割槽概述
分割槽是一種表設計模式，自5.1版本開始支援分割槽，邏輯上是一張表，物理上可能是多個物件。其中MyISAM、INNODB、NDB等儲存引擎都支援分割槽，但CSV、MERGE、FEDORATED不支援分割槽。
1. 分割槽種類
水平分割槽：對錶記錄進行拆分，同一表的不同行記錄分配到不同的物理檔案中。
Range 分割槽：行根據基於屬於一個給定連續區間的列值放入分割槽，自MYSQL5.5開始支援列範圍分割槽，最常用的分割槽。
List分割槽：和Range分割槽一樣，只是List分割槽是面對離散值，自MYSQL5.5開支支援列List分割槽。
Hash分割槽：根據使用者自定義的表示式的返回值進行分割槽，返回值不能為負數。
Key分割槽：根據MYSQL資料庫提供的雜湊函式來進行分割槽。
垂直分割槽：對錶欄位程式拆分（MYSQL暫不支援），同一表中不同的列分配在不同的物理檔案中。
2. 分割槽優、缺點
優點：
可極大提高查詢效率；
主要用於資料庫的高可用性，方便管理；
缺點：
無論何種分割槽，如果表中存在主鍵或者唯一鍵索引，分割槽列必須是唯一索引的一個組成部分。
二、 分割槽型別詳解
1. Range分割槽
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 ##儲存分割槽資訊
-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分割槽
-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分割槽
HASH分割槽將資料均勻的分佈到預先定義的各個分割槽中，保障各個分割槽的資料數量大致一樣的。在range和list分割槽定義時，必須明確指定分割槽的列值或列值集合儲存在哪個分割槽中，而hash分割槽自動完成列值的分配，平均的將資料放在不同的分割槽。
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分割槽
Hash分割槽根據使用者自定義的函式進行分割槽，key使用MYSQL資料庫提供的函式進行分割槽。
CREATE TABLE `t_key` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY KEY (id)
PARTITIONS 4 */;
5. Columns分割槽
Range、List、Hash和Key分割槽都是針對整型分割槽，如果不是整型分割槽，則需要透過相關函式轉換。但透過columns分割槽，不需要轉換。
Columns支援int/small int/tinyint/bigint/date/datetime/char/varchar/binary支援，對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. 子分割槽
在分割槽的基礎上再進行分割槽，也稱之為複合分割槽。
三、 分割槽維護管理
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. 增加分割槽
alter table t_range add partition ( partition p2 values less than maxvalue) ;
2. 刪除分割槽
alter table t_range drop partition p2;
alter table t_list remove partitioning;
3. 檢視分割槽
information_schema.partitions
4. 清除分割槽資料
alter table t_range truncate partition p2;
5. 解析分割槽
(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)
（只查詢指定的分割槽）
6. 交換分割槽
MYSQL5.6支援了交換分割槽，具體語法如下：
alter table t_range exchange partition p0 with table t;
將分割槽表t_range的P0分割槽的資料交換到t表中，而t表的資料也會交換到t_range表中，交換是雙向的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27067062/viewspace-2141773/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 全面學習分割槽表及分割槽索引(10)--交換分割槽索引
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引
- 深入學習Oracle分割槽表及分割槽索引Oracle索引
- 全面學習分割槽表及分割槽索引(1)索引
- 深入學習分割槽表及分割槽索引(1)索引
- 全面學習分割槽表及分割槽索引(9)--刪除表分割槽索引
- 全面學習分割槽表及分割槽索引(11)--合併表分割槽索引
- 全面學習分割槽表及分割槽索引(12)--修改list表分割槽索引
- 學習筆記】分割槽表和分割槽索引——新增表分割槽（二）筆記索引
- 【學習筆記】分割槽表和分割槽索引——管理索引分割槽（四）筆記索引
- 全面學習分割槽表及分割槽索引(17)--其它索引分割槽管理操作索引
- mysql 分割槽MySql
- MySQL分割槽MySql
- 全面學習分割槽表及分割槽索引(8)--增加和收縮表分割槽索引
- 全面學習分割槽表及分割槽索引(16)--增加和刪除索引分割槽索引
- 【學習筆記】分割槽表和分割槽索引——分割槽表的其他管理（三）筆記索引
- Spark學習——分割槽Partition數Spark
- 分割槽索引學習筆記索引筆記
- oracle分割槽表學習（四）Oracle
- oracle分割槽表學習（三）Oracle
- oracle分割槽表學習（二）Oracle
- oracle分割槽表學習（一）Oracle
- 分割槽表學習之三
- 分割槽表學習之二
- 分割槽表學習之一
- 分割槽表學習筆記筆記
- 全面學習分割槽表及分割槽索引(6)--建立range-list組合分割槽索引
- 全面學習分割槽表及分割槽索引(15)--修改表分割槽屬性和模板索引
- 全面學習分割槽表及分割槽索引(7)--怎樣管理索引
- 【學習筆記】分割槽表和分割槽索引——概念部分（一）筆記索引
- 深入學習分割槽表及分割槽索引(5)--建立range-hash組合分割槽(續)索引
- 理解MySQL分割槽MySql
- 搞懂MySQL分割槽MySql
- 【MYSQL】 分割槽表MySql
- MySql建立分割槽MySql
- MySQL 子分割槽MySql
- MySQL KEY分割槽MySql
- MySQL HASH分割槽MySql