男生扒开美女尿口戳戳,新午夜第九电影院理论片

主頁 > 知識庫 > MySQL分區表的最佳實踐指南

MySQL分區表的最佳實踐指南

前言：

分區是一種表的設計模式，通俗地講表分區是將一大表，根據條件分割成若干個小表。但是對于應用程序來講，分區的表和沒有分區的表是一樣的。換句話來講，分區對于應用是透明的，只是數據庫對于數據的重新整理。本篇文章給大家帶來的內容是關于MySQL中分區表的介紹及使用場景，有需要的朋友可以參考一下，希望對你有所幫助。

1.分區的目的及分區類型

MySQL在創建表的時候可以通過使用PARTITION BY子句定義每個分區存放的數據。在執行查詢的時候，優化器根據分區定義過濾那些沒有我們需要的數據的分區，這樣查詢就可以無需掃描所有分區，只需要查找包含需要數據的分區即可。

分區的另一個目的是將數據按照一個較粗的粒度分別存放在不同的表中。這樣做可以將相關的數據存放在一起，另外，當我們想要一次批量刪除整個分區的數據也會變得很方便。

下面簡單介紹下四種常見的分區類型：

RANGE分區：最為常用，基于屬于一個給定連續區間的列值，把多行分配給分區。最常見的是基于時間字段。
LIST分區：LIST分區和RANGE分區類似，區別在于LIST是枚舉值列表的集合，RANGE是連續的區間值的集合。
HASH分區：基于用戶定義的表達式的返回值來進行選擇的分區，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL中有效的、產生非負整數值的任何表達式。
KEY分區：類似于按HASH分區，區別在于KEY分區只支持計算一列或多列，且MySQL服務器提供其自身的哈希函數。必須有一列或多列包含整數值。

上述四種分區類型中，RANGE分區即范圍分區是最常用的。RANGE分區的特點是多個分區的范圍要連續，但是不能重疊，默認情況下使用VALUES LESS THAN屬性，即每個分區不包括指定的那個值。

2.分區操作示例

本節內容以RANGE分區為例，介紹下分區表相關的操作。

# 創建分區表
mysql> CREATE TABLE `tr` (
 -> `id` INT, 
 -> `name` VARCHAR(50), 
 -> `purchased` DATE
 -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
 -> PARTITION BY RANGE( YEAR(purchased) ) (
 -> PARTITION p0 VALUES LESS THAN (1990),
 -> PARTITION p1 VALUES LESS THAN (1995),
 -> PARTITION p2 VALUES LESS THAN (2000),
 -> PARTITION p3 VALUES LESS THAN (2005),
 -> PARTITION p4 VALUES LESS THAN (2010),
 -> PARTITION p5 VALUES LESS THAN (2015)
 -> );
Query OK, 0 rows affected (0.28 sec)

# 插入數據
mysql> INSERT INTO `tr` VALUES
 ->  (1, 'desk organiser', '2003-10-15'),
 ->  (2, 'alarm clock', '1997-11-05'),
 ->  (3, 'chair', '2009-03-10'),
 ->  (4, 'bookcase', '1989-01-10'),
 ->  (5, 'exercise bike', '2014-05-09'),
 ->  (6, 'sofa', '1987-06-05'),
 ->  (7, 'espresso maker', '2011-11-22'),
 ->  (8, 'aquarium', '1992-08-04'),
 ->  (9, 'study desk', '2006-09-16'),
 ->  (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0

創建后可以看到，每個分區都會對應1個ibd文件。上面創建語句還是很好理解的，在此分區表中，通過YEAR函數取出DATE日期中的年份并轉化為整型，年份小于1990的存儲在分區p0中，小于1995的存儲在分區p1中，以此類推。請注意，每個分區的定義順序是從最低到最高。為了防止插入的數據因找不到相應分區而報錯，我們應該及時創建新的分區。下面繼續展示關于分區維護的其他操作。

# 查看某個分區的數據
mysql> SELECT * FROM tr PARTITION (p2);
+------+-------------+------------+
| id | name  | purchased |
+------+-------------+------------+
| 2 | alarm clock | 1997-11-05 |
| 10 | lava lamp | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)

# 增加分區
mysql> alter table tr add partition(
 -> PARTITION p6 VALUES LESS THAN (2020)
 -> );
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

# 拆分分區
mysql> alter table tr reorganize partition p5 into(
 -> partition s0 values less than(2012),
 -> partition s1 values less than(2015)
 -> );
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0

# 合并分區
mysql> alter table tr reorganize partition s0,s1 into ( 
 ->  partition p5 values less than (2015) 
 -> );
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

# 清空某分區的數據
mysql> alter table tr truncate partition p0;
Query OK, 0 rows affected (0.11 sec)

# 刪除分區
mysql> alter table tr drop partition p1;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

# 交換分區
# 先創建與分區表同樣結構的交換表
mysql> CREATE TABLE `tr_archive` (
 -> `id` INT, 
 -> `name` VARCHAR(50), 
 -> `purchased` DATE
 -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.28 sec)
# 執行exchange交換分區 
mysql> alter table tr exchange PARTITION p2 with table tr_archive;
Query OK, 0 rows affected (0.13 sec)

3.分區注意事項及適用場景

其實分區表的使用有很多限制和需要注意的事項，參考官方文檔，簡要總結幾點如下：