mysql集群分區

Mysql分區

本章學習目標

理解分區的概念

了解分區的類型

了解分區管理

Mysql從5.1版本開始支持分區的功能，分區是一種物理數據庫設計技術，其主要目的是在特定的SQL操作中，通過減少數據讀寫的總量來縮減SQL語句的響應時間，同時對于應用來說分區完全是透明的，本章將對MySQL分區詳細講解。

分區概述

分區的概念

MySQL數據庫中的數據是以文件的形式存在磁盤上，默認放在/mysql/data（可以通過my.cnf中的datadir來查看）目錄下面，一張表主要對應著三個文件，一個是.frm文件，用于存放表結構，一個是.myd文件，用于存放表數據，還有一個是.myi文件，用于存放表索引。

如果一張表的數據量過大，那么.myd和.myi文件會很大，查詢數據就會變的很慢，這時可以利用MySQL的分區功能，在物理上將這一張表對應的三個文件，分割成許多個小塊，這樣在查詢一條記錄時，就不需要全表查找了，只需要知道這條記錄在哪一塊，然后在具體數據塊中查詢即可。如果表中數據過大，可能一個磁盤存放不下，這時可以把數據分配到不同的磁盤中去。

分區有兩種方式，分別是橫向分區和縱向分區，接下來舉例說明橫向分區和縱向分區的含義，具體如下所示。

橫向分區：例如一張表有100萬條數據，可以分成十份，第一個10萬條數據放到第一個分區，第二個10萬條數據放到第二個分區，依此類推。也就是把表分成了十份，與水平分表類似。取出一條數據時，這條數據包含了表結構中的所有字段，也就是說橫向分區，并沒有改變表的結構。

縱向分區：例如在設計用戶表的時候，起初沒有考慮周全，把個人的所有信息都放到了一張表中，這樣表中就會有比較大 的字段，如個人簡介，而這些簡介可能不需要經常用到，可以需要用到時再去查詢，可以利用縱向分區將大字段對應的數據進行分塊存放，從而提高磁盤IO，與垂直分表類似。

從MySQL橫向分區和縱向分區的原理來看，這與MySQL水平分表和垂直分表類似，但它們是有區別的，分表注重的是存取數據時，如何提高MySQL的并發能力，而分區注重的是如何突破磁盤的IO能力，從而達到提高MySQL性能的目的，分表會把一張數據表真正地拆分為多個表，而分區是把表的數據文件和索引文件進行分割，達到分而治之的效果。

分區的優點

MySQL分區的優點非常多，這里只強調重要的兩點，具體如下所示。

性能的提升：在掃描操作中，如果MySQL的優化器知道哪個分區中才包含特定查詢中需要的數據，就能直接去掃描具體分區的數據，而不用浪費很多時間掃描不相關的數據。

對數據管理的簡化：MySQL分區技術可以讓DBA對數據的管理能力提升，通過分區，DBA可以簡化特定數據操作的執行方式。另外，分區是由MySQL直接管理的，DBA不需要手動去劃分和維護。

分區類型詳解

在學習分區類型前，首先要查看數據庫是否支持分區，SQL語句如下所示。

mysql> SHOW VARIABLES LIKE '%part%';

+-------------------+-------+

| Variable_name ????| Value |

+-------------------+-------+

| have_partitioning | YES ??|

+-------------------+-------+

1 row in set (0.04 sec)

從以上執行結果可看出，have_partitioning的值為YES，說明當前MySQL數據庫支持分區，并且默認是開啟的狀態。

MySQL提供的分區屬于橫向分區，通過運用不同算法和規則，將數據分配到不同的區塊，MySQL分區類型主要有RANGE分區、LIST分區、HASH分區、KEY分區和子分區，接下來將詳細講解這些類型的分區。

RANGE分區

按照RANGE分區的表是利用取值范圍將數據分區，區間要連續并且不能互相重疊，MySQL中使用VALUES LESS THAN操作符進行分區定義，接下來通過具體案例演示RANGE分區的使用。

創建員工表emp，按照員工工資進行RANGE分區，范圍為1000元以下、1000~2000元和2000元以上，表結構如表12.1所示。

emp表

字段

字段類型

說明

id

int

員工編號

name

varchar(30)

員工姓名

deptno

int

部門編號

birthdate

date

員工生日

salary

int

員工工資

創建emp表并分區，SQL語句如下所示。

mysql> CREATE TABLE emp(

-> ????id INT NOT NULL,

-> ????name VARCHAR(30),

-> ????deptno INT,

-> ????birthdate DATE,

-> ????salary INT

-> )

-> PARTITION BY RANGE(salary)(

-> ????PARTITION p1 VALUES LESS THAN(1000),

-> ????PARTITION p2 VALUES LESS THAN(2000),

-> ????PARTITION p3 VALUES LESS THAN maxvalue

-> );

Query OK, 0 rows affected (0.18 sec)

以上執行結果證明表emp創建完成，使用PARTITION BY RANGE按照員工工資進行了RANGE分區，使用PARTITION將表中數據分為三個分區p1、p2和p3，使用VALUES LESS THAN操作符進行了分區范圍的規定，分為1000元以下、1000~2000元和2000元以上，其中maxvalue表示2000元以上的范圍。

創建員工表emp2，按照員工生日進行RANGE分區，范圍為1980年以前、1980~1990年和1990年以后，SQL語句如下所示。

mysql> CREATE TABLE emp2(

-> ????id INT NOT NULL,

-> ????name VARCHAR(30),

-> ????deptno INT,

-> ????birthdate DATE,

-> ????salary INT

-> )

-> PARTITION BY RANGE(YEAR(birthdate))(

-> ????PARTITION p1 VALUES LESS THAN(1980),

-> ????PARTITION p2 VALUES LESS THAN(1990),

-> ????PARTITION p3 VALUES LESS THAN maxvalue

-> );

Query OK, 0 rows affected (0.2 5 sec)

以上執行結果證明表emp2創建完成，使用PARTION BY RANGE按照員工生日進行了RANGE分區，這里要注意的是，表達式YEAR(birthdate)必須有返回值，使用PARTITION將表中數據分為三個分區p1、p2和p3，使用VALUES LESS THAN操作符進行了分區范圍的規定，分為1980年以前、1980~1990年和1990年以后，其中maxvalue表示1990年以后的范圍。

MySQL5.1支持整數列分區，若想在日期或者字符串類型的列上進行分區，就要使用函數進行轉換，否則無法利用RANGE分區來提高性能。MySQL5.5改進了RANGE分區功能，提供了RANGE COLUMNS分區支持非整數分區，這樣創建日期分區就不需要通過函數進行轉換，接下來通過具體案例演示RANGE COLUMNS分區的使用。

創建員工表emp3，按照員工生日進行RANGE COLUMNS分區，范圍為1980年1月1日以前、1980年1月1日~1990年1月1日和1990年1月1日以后，SQL語句如下所示。

mysql> CREATE TABLE emp3(

-> ????id INT NOT NULL,

-> ????name VARCHAR(30),

-> ????deptno INT,

-> ????birthdate DATE,

-> ????salary INT

-> )

-> PARTITION BY RANGE COLUMNS(birthdate)(

-> ????PARTITION p1 VALUES LESS THAN('1980-01-01'),

-> ????PARTITION p2 VALUES LESS THAN('1990-01-01'),

-> ????PARTITION p3 VALUES LESS THAN maxvalue

-> );

Query OK, 0 rows affected (0.17 sec)

從以上執行結果可看出，創建表emp3并分區成功，SQL中使用PARTITION BY RANGE COLUMNS語句，按照birthdate進行分區，這里birthdate為日期類型，沒有通過函數進行轉換，原因是RANGE COLUMNS分區支持非整數分區。

當需要刪除過期數據時，只需要刪除具體的一個分區即可，這對于大數據量的表來說，刪除分區比逐條刪除數據的效率要高的多，刪除分區的語法格式如下所示。

ALTER TABLE 表名 DROP PARTITION 分區名；

接下來通過具體案例演示刪除分區的實現。

刪除表emp3中的分區p1，SQL語句如下所示。

mysql> ALTER TABLE emp3

-> DROP PARTITION p1;

Query OK, 0 rows affected (0.53 sec)

Records: 0 ?Duplicates: 0 ?Warnings: 0

從以上執行結果可看出，SQL語句執行成功，分區p1被刪除，但0行數據受影響，因為此時表emp3中沒有數據。

LIST分區

LIST分區與RANGE分區類似，區別在于LIST分區是從屬于一個枚舉列表的值的集合，RANGE分區是從屬于一個連續區間值的集合。MySQL中使用PARTITION BY LIST(expr)子句實現LIST分區，expr是某列值或一個基于某列值返回一個整數值的表達式，然后通過VALUES IN(value_list)的方式來定義分區，其中value_list是一個逗號分隔的整數列表，與RANGE分區不同的是，LIST分區不必聲明任何特定的順序。接下來通過具體案例演示LIST分區的使用。

創建員工表emp4，按照部門編號進行LIST分區，范圍為10號部門、20號部門和30號部門，SQL語句如下所示。

mysql> CREATE TABLE emp4(

-> ????id INT NOT NULL,

-> ????name VARCHAR(30),

-> ????deptno INT,

-> ????birthdate DATE,

-> ????salary INT

-> )

-> PARTITION BY LIST(deptno)(

-> ????PARTITION p1 VALUES IN(10),

-> ????PARTITION p2 VALUES IN(20),

-> ????PARTITION p3 VALUES IN(30)

-> );

Query OK, 0 rows affected (0.18 sec)

以上執行結果證明表emp4創建完成，使用PARTITION BY LIST按照部門編號進行了LIST分區，使用PARTITION將表中數據分為三個分區p1、p2和p3，使用VALUES IN操作符指定了分區范圍為10號部門、20號部門和30號部門。

MySQL5.1以前，LIST分區只能匹配整數列表，deptno只能是INT類型，若想在日期或者字符串類型的列上進行分區，就要使用函數進行轉換，否則無法使用LIST分區。MySQL5.5改進了LIST分區功能，提供了LIST COLUMNS分區支持非整數分區，這樣創建日期分區就不需要通過函數進行轉換，接下來通過具體案例演示LIST COLUMNS分區的使用。

創建員工表emp5，按照部門編號進行LIST分區，范圍為5號部門、15號部門和25號部門，其中部門編號deptno為VARCHAR(10)類型，SQL語句如下所示。

mysql> CREATE TABLE emp5(

-> ????id INT NOT NULL,

-> ????name VARCHAR(30),

-> ????deptno VARCHAR(10),

-> ????birthdate DATE,

-> ????salary INT

-> )

-> PARTITION BY LIST COLUMNS(deptno)(

-> ????PARTITION p1 VALUES IN('5'),

-> ????PARTITION p2 VALUES IN('15'),

-> ????PARTITION p3 VALUES IN('25')

-> );

Query OK, 0 rows affected (0.14 sec)

從以上執行結果可看出，表emp5創建成功并進行了分區，根據deptno對表中數據進行了分區，分區范圍為5號部門、15號部門和25號部門，其中部門編號deptno為VARCHAR(10)類型，這里使用了LIST COLUMNS進行分區，無需進行類型轉換，直接使用即可，注意VALUES IN后的枚舉值也必須是字符串類型，否則會報出錯誤。

HASH分區

HASH分區主要用來確保數據在預先確定數目的分區中平均分布，在RANGE和LIST分區中，必須明確指定一個給定的列值或列值集合應該保存在哪個分區中，而在HASH分區中，MySQL會自動完成這些工作，只需基于將要被哈希的列值指定一個列值或表達式，以及指定被分區的表將要被分割成的分區數量即可。

MySQL支持兩種HASH分區，常規HASH分區和線性HASH分區，常規HASH分區使用的是取模算法，線性HASH分區使用的是一個線性的2的冪的運算法則。MySQL中使用PARTITION BY HASH(expr) PARTITIONS num子句對分區類型、分區鍵和分區個數進行定義，其中expr是某列值或一個基于某列值返回一個整數值的表達式，num是一個非負的整數，表示分割成分區的數量，默認為1。接下來通過具體案例演示常規HASH分區的用法。

創建員工表emp6，按照員工生日進行常規HASH分區，分為四個分區，SQL語句如下所示。

mysql> CREATE TABLE emp6(

-> ????id INT NOT NULL,

-> ????name VARCHAR(30),

-> ????deptno VARCHAR(10),

-> ????birthdate DATE,

-> ????salary INT

-> )

-> PARTITION BY HASH(YEAR(birthdate))

-> PARTITIONS 4;

Query OK, 0 rows affected (0.21 sec)

以上執行結果可看出，員工表emp6創建完成，并進行了分區，使用PARTITION BY ?HASH進行了HASH分區，根據員工生日分為了四個分區。其實對于一個表達式expr，即SQL中的YEAR(birthdate)，是可以計算出它會被保存在哪個分區中，假設將要保存記錄的分區編號為N，那么N=MOD(expr,num)，例如本例中emp表有4個分區，向表中插入數據，SQL語句如下所示。

mysql> INSERT INTO emp6

-> VALUES(1,'zs','10','2017-12-01',1000);

Query OK, 1 row affected (0.10 sec)

以上執行結果證明數據插入成功，這條語句中birthdate為2017-12-01，那么YEAR(birthdate)為2017，可以計算出保存該條記錄的分區，具體如下所示。

MOD(2017,4)=1

以上計算是取模運算，運算結果為1，所以該條數據會保存到第一個分區中，常規HASH將數據盡可能平均分布到每個分區，讓每個分區管理的數據減少，提高了查詢效率，但這里還存在著一個隱藏的問題，當需要增加分區或者合并分區時，假設有5個常規HASH分區，新增一個常規HASH分區，那么原來的取模算法是MOD(expr,5)，根據余數0~4分布在5個分區中，增加分區后，取模算法變為了MOD(expr,6)，分區數量增加了，所以之前所有分區中的數據要重新計算分區，這樣的代價太大了，不適合需求多變的實際應用，為了降低分區管理的代價，MySQL提供了線性HASH分區，分區函數是一個線性的2的冪的運算。

線性HASH分區和常規HASH分區的語法區別在PARTITION BY子句，線性HASH需要加上LINEAR關鍵字，接下來通過具體案例演示線性HASH的使用。

創建員工表emp7，按照員工工資進行線性HASH分區，分為三個分區，SQL語句如下所示。

mysql> CREATE TABLE emp7(

-> ????id INT NOT NULL,

-> ????name VARCHAR(30),

-> ????deptno VARCHAR(10),

-> ????birthdate DATE,

-> ????salary INT

-> )

-> PARTITION BY LINEAR HASH(salary)

-> PARTITIONS 3;

Query OK, 0 rows affected (0.26 sec)

從以上執行結果可看出，表emp7創建完成并創建了三個分區，使用PARTITION BY LINEAR HASH創建了線性HASH分區，比前面的常規HASH分區更適合需求多變的應用場景。

KEY分區

KEY分區主要用來確保數據在預先確定數目的分區中平均分布，在RANGE和LIST分區中，必須明確指定一個給定的列值或列值集合應該保存在哪個分區中，而在HASH分區中，MySQL會自動完成這些工作，只需基于將要被哈希的列值指定一個列值或表達式，以及指定被分區的表將要被分割成的分區數量即可。

子分區

MySQL分區處理NULL值的方式

分區管理

RANGE分區和LIST分區管理

HASH分區和KEY分區管理

本章小結

本章首先介紹了數據的備份與還原，這是非常實用且必須的技能，讀者需要掌握，然后介紹了權限管理，權限管理一般由數據庫管理員操作，最后講解了如何實現MySQL集群，以及集群的應用，實現了MySQL主從復制以及雙主互備，對于初學者來說，了解即可。

習題

1.思考題

(1)?請簡述

(2)?請簡述

(3)?請簡述

(4) 請簡述

(5) 請簡述

MySQL SQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。