mysql集群分区
本章學(xué)習(xí)目標(biāo)
MySQL從5.1版本開始支持分區(qū)的功能，分區(qū)是一種物理數(shù)據(jù)庫(kù)設(shè)計(jì)技術(shù)，其主要目的是在特定的SQL操作中，通過減少數(shù)據(jù)讀寫的總量來縮減SQL語句的響應(yīng)時(shí)間，同時(shí)對(duì)于應(yīng)用來說分區(qū)完全是透明的，本章將對(duì)MySQL分區(qū)詳細(xì)講解。
MySQL數(shù)據(jù)庫(kù)中的數(shù)據(jù)是以文件的形式存在磁盤上，默認(rèn)放在/mysql/data（可以通過my.cnf中的datadir來查看）目錄下面，一張表主要對(duì)應(yīng)著三個(gè)文件，一個(gè)是.frm文件，用于存放表結(jié)構(gòu)，一個(gè)是.myd文件，用于存放表數(shù)據(jù)，還有一個(gè)是.myi文件，用于存放表索引。
如果一張表的數(shù)據(jù)量過大，那么.myd和.myi文件會(huì)很大，查詢數(shù)據(jù)就會(huì)變的很慢，這時(shí)可以利用MySQL的分區(qū)功能，在物理上將這一張表對(duì)應(yīng)的三個(gè)文件，分割成許多個(gè)小塊，這樣在查詢一條記錄時(shí)，就不需要全表查找了，只需要知道這條記錄在哪一塊，然后在具體數(shù)據(jù)塊中查詢即可。如果表中數(shù)據(jù)過大，可能一個(gè)磁盤存放不下，這時(shí)可以把數(shù)據(jù)分配到不同的磁盤中去。
分區(qū)有兩種方式，分別是橫向分區(qū)和縱向分區(qū)，接下來舉例說明橫向分區(qū)和縱向分區(qū)的含義，具體如下所示。
從MySQL橫向分區(qū)和縱向分區(qū)的原理來看，這與MySQL水平分表和垂直分表類似，但它們是有區(qū)別的，分表注重的是存取數(shù)據(jù)時(shí)，如何提高M(jìn)ySQL的并發(fā)能力，而分區(qū)注重的是如何突破磁盤的IO能力，從而達(dá)到提高M(jìn)ySQL性能的目的，分表會(huì)把一張數(shù)據(jù)表真正地拆分為多個(gè)表，而分區(qū)是把表的數(shù)據(jù)文件和索引文件進(jìn)行分割，達(dá)到分而治之的效果。
MySQL分區(qū)的優(yōu)點(diǎn)非常多，這里只強(qiáng)調(diào)重要的兩點(diǎn)，具體如下所示。
????在學(xué)習(xí)分區(qū)類型前，首先要查看數(shù)據(jù)庫(kù)是否支持分區(qū)，SQL語句如下所示。
mysql> SHOW VARIABLES LIKE '%part%';
+-------------------+-------+
| Variable_name ????| Value |
+-------------------+-------+
| have_partitioning | YES ??|
+-------------------+-------+
1 row in set (0.04 sec)
從以上執(zhí)行結(jié)果可看出，have_partitioning的值為YES，說明當(dāng)前MySQL數(shù)據(jù)庫(kù)支持分區(qū)，并且默認(rèn)是開啟的狀態(tài)。
MySQL提供的分區(qū)屬于橫向分區(qū)，通過運(yùn)用不同算法和規(guī)則，將數(shù)據(jù)分配到不同的區(qū)塊，MySQL分區(qū)類型主要有RANGE分區(qū)、LIST分區(qū)、HASH分區(qū)、KEY分區(qū)和子分區(qū)，接下來將詳細(xì)講解這些類型的分區(qū)。
按照RANGE分區(qū)的表是利用取值范圍將數(shù)據(jù)分區(qū)，區(qū)間要連續(xù)并且不能互相重疊，MySQL中使用VALUES LESS THAN操作符進(jìn)行分區(qū)定義，接下來通過具體案例演示RANGE分區(qū)的使用。
| 字段 | 字段類型 | 說明 |
| id | int | 員工編號(hào) |
| name | varchar(30) | 員工姓名 |
| deptno | int | 部門編號(hào) |
| birthdate | date | 員工生日 |
| salary | int | 員工工資 |
創(chuàng)建emp表并分區(qū)，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)
以上執(zhí)行結(jié)果證明表emp創(chuàng)建完成，使用PARTITION BY RANGE按照員工工資進(jìn)行了RANGE分區(qū)，使用PARTITION將表中數(shù)據(jù)分為三個(gè)分區(qū)p1、p2和p3，使用VALUES LESS THAN操作符進(jìn)行了分區(qū)范圍的規(guī)定，分為1000元以下、1000~2000元和2000元以上，其中maxvalue表示2000元以上的范圍。
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)
以上執(zhí)行結(jié)果證明表emp2創(chuàng)建完成，使用PARTION BY RANGE按照員工生日進(jìn)行了RANGE分區(qū)，這里要注意的是，表達(dá)式Y(jié)EAR(birthdate)必須有返回值，使用PARTITION將表中數(shù)據(jù)分為三個(gè)分區(qū)p1、p2和p3，使用VALUES LESS THAN操作符進(jìn)行了分區(qū)范圍的規(guī)定，分為1980年以前、1980~1990年和1990年以后，其中maxvalue表示1990年以后的范圍。
MySQL5.1支持整數(shù)列分區(qū)，若想在日期或者字符串類型的列上進(jìn)行分區(qū)，就要使用函數(shù)進(jìn)行轉(zhuǎn)換，否則無法利用RANGE分區(qū)來提高性能。MySQL5.5改進(jìn)了RANGE分區(qū)功能，提供了RANGE COLUMNS分區(qū)支持非整數(shù)分區(qū)，這樣創(chuàng)建日期分區(qū)就不需要通過函數(shù)進(jìn)行轉(zhuǎn)換，接下來通過具體案例演示RANGE COLUMNS分區(qū)的使用。
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)
從以上執(zhí)行結(jié)果可看出，創(chuàng)建表emp3并分區(qū)成功，SQL中使用PARTITION BY RANGE COLUMNS語句，按照birthdate進(jìn)行分區(qū)，這里birthdate為日期類型，沒有通過函數(shù)進(jìn)行轉(zhuǎn)換，原因是RANGE COLUMNS分區(qū)支持非整數(shù)分區(qū)。
當(dāng)需要?jiǎng)h除過期數(shù)據(jù)時(shí)，只需要?jiǎng)h除具體的一個(gè)分區(qū)即可，這對(duì)于大數(shù)據(jù)量的表來說，刪除分區(qū)比逐條刪除數(shù)據(jù)的效率要高的多，刪除分區(qū)的語法格式如下所示。
ALTER TABLE 表名 DROP PARTITION 分區(qū)名；
接下來通過具體案例演示刪除分區(qū)的實(shí)現(xiàn)。
mysql> ALTER TABLE emp3
????-> DROP PARTITION p1;
Query OK, 0 rows affected (0.53 sec)
Records: 0 ?Duplicates: 0 ?Warnings: 0
從以上執(zhí)行結(jié)果可看出，SQL語句執(zhí)行成功，分區(qū)p1被刪除，但0行數(shù)據(jù)受影響，因?yàn)榇藭r(shí)表emp3中沒有數(shù)據(jù)。
LIST分區(qū)與RANGE分區(qū)類似，區(qū)別在于LIST分區(qū)是從屬于一個(gè)枚舉列表的值的集合，RANGE分區(qū)是從屬于一個(gè)連續(xù)區(qū)間值的集合。MySQL中使用PARTITION BY LIST(expr)子句實(shí)現(xiàn)LIST分區(qū)，expr是某列值或一個(gè)基于某列值返回一個(gè)整數(shù)值的表達(dá)式，然后通過VALUES IN(value_list)的方式來定義分區(qū)，其中value_list是一個(gè)逗號(hào)分隔的整數(shù)列表，與RANGE分區(qū)不同的是，LIST分區(qū)不必聲明任何特定的順序。接下來通過具體案例演示LIST分區(qū)的使用。
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)
以上執(zhí)行結(jié)果證明表emp4創(chuàng)建完成，使用PARTITION BY LIST按照部門編號(hào)進(jìn)行了LIST分區(qū)，使用PARTITION將表中數(shù)據(jù)分為三個(gè)分區(qū)p1、p2和p3，使用VALUES IN操作符指定了分區(qū)范圍為10號(hào)部門、20號(hào)部門和30號(hào)部門。
MySQL5.1以前，LIST分區(qū)只能匹配整數(shù)列表，deptno只能是INT類型，若想在日期或者字符串類型的列上進(jìn)行分區(qū)，就要使用函數(shù)進(jìn)行轉(zhuǎn)換，否則無法使用LIST分區(qū)。MySQL5.5改進(jìn)了LIST分區(qū)功能，提供了LIST COLUMNS分區(qū)支持非整數(shù)分區(qū)，這樣創(chuàng)建日期分區(qū)就不需要通過函數(shù)進(jìn)行轉(zhuǎn)換，接下來通過具體案例演示LIST COLUMNS分區(qū)的使用。
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)
從以上執(zhí)行結(jié)果可看出，表emp5創(chuàng)建成功并進(jìn)行了分區(qū)，根據(jù)deptno對(duì)表中數(shù)據(jù)進(jìn)行了分區(qū)，分區(qū)范圍為5號(hào)部門、15號(hào)部門和25號(hào)部門，其中部門編號(hào)deptno為VARCHAR(10)類型，這里使用了LIST COLUMNS進(jìn)行分區(qū)，無需進(jìn)行類型轉(zhuǎn)換，直接使用即可，注意VALUES IN后的枚舉值也必須是字符串類型，否則會(huì)報(bào)出錯(cuò)誤。
HASH分區(qū)主要用來確保數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)中平均分布，在RANGE和LIST分區(qū)中，必須明確指定一個(gè)給定的列值或列值集合應(yīng)該保存在哪個(gè)分區(qū)中，而在HASH分區(qū)中，MySQL會(huì)自動(dòng)完成這些工作，只需基于將要被哈希的列值指定一個(gè)列值或表達(dá)式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量即可。
MySQL支持兩種HASH分區(qū)，常規(guī)HASH分區(qū)和線性HASH分區(qū)，常規(guī)HASH分區(qū)使用的是取模算法，線性HASH分區(qū)使用的是一個(gè)線性的2的冪的運(yùn)算法則。MySQL中使用PARTITION BY HASH(expr) PARTITIONS num子句對(duì)分區(qū)類型、分區(qū)鍵和分區(qū)個(gè)數(shù)進(jìn)行定義，其中expr是某列值或一個(gè)基于某列值返回一個(gè)整數(shù)值的表達(dá)式，num是一個(gè)非負(fù)的整數(shù)，表示分割成分區(qū)的數(shù)量，默認(rèn)為1。接下來通過具體案例演示常規(guī)HASH分區(qū)的用法。
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)
以上執(zhí)行結(jié)果可看出，員工表emp6創(chuàng)建完成，并進(jìn)行了分區(qū)，使用PARTITION BY ?HASH進(jìn)行了HASH分區(qū)，根據(jù)員工生日分為了四個(gè)分區(qū)。其實(shí)對(duì)于一個(gè)表達(dá)式expr，即SQL中的YEAR(birthdate)，是可以計(jì)算出它會(huì)被保存在哪個(gè)分區(qū)中，假設(shè)將要保存記錄的分區(qū)編號(hào)為N，那么N=MOD(expr,num)，例如本例中emp表有4個(gè)分區(qū)，向表中插入數(shù)據(jù)，SQL語句如下所示。
mysql> INSERT INTO emp6
????-> VALUES(1,'zs','10','2017-12-01',1000);
Query OK, 1 row affected (0.10 sec)
以上執(zhí)行結(jié)果證明數(shù)據(jù)插入成功，這條語句中birthdate為2017-12-01，那么YEAR(birthdate)為2017，可以計(jì)算出保存該條記錄的分區(qū)，具體如下所示。
MOD(2017,4)=1
以上計(jì)算是取模運(yùn)算，運(yùn)算結(jié)果為1，所以該條數(shù)據(jù)會(huì)保存到第一個(gè)分區(qū)中，常規(guī)HASH將數(shù)據(jù)盡可能平均分布到每個(gè)分區(qū)，讓每個(gè)分區(qū)管理的數(shù)據(jù)減少，提高了查詢效率，但這里還存在著一個(gè)隱藏的問題，當(dāng)需要增加分區(qū)或者合并分區(qū)時(shí)，假設(shè)有5個(gè)常規(guī)HASH分區(qū)，新增一個(gè)常規(guī)HASH分區(qū)，那么原來的取模算法是MOD(expr,5)，根據(jù)余數(shù)0~4分布在5個(gè)分區(qū)中，增加分區(qū)后，取模算法變?yōu)榱薓OD(expr,6)，分區(qū)數(shù)量增加了，所以之前所有分區(qū)中的數(shù)據(jù)要重新計(jì)算分區(qū)，這樣的代價(jià)太大了，不適合需求多變的實(shí)際應(yīng)用，為了降低分區(qū)管理的代價(jià)，MySQL提供了線性HASH分區(qū)，分區(qū)函數(shù)是一個(gè)線性的2的冪的運(yùn)算。
線性HASH分區(qū)和常規(guī)HASH分區(qū)的語法區(qū)別在PARTITION BY子句，線性HASH需要加上LINEAR關(guān)鍵字，接下來通過具體案例演示線性HASH的使用。
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)
從以上執(zhí)行結(jié)果可看出，表emp7創(chuàng)建完成并創(chuàng)建了三個(gè)分區(qū)，使用PARTITION BY LINEAR HASH創(chuàng)建了線性HASH分區(qū)，比前面的常規(guī)HASH分區(qū)更適合需求多變的應(yīng)用場(chǎng)景。
KEY分區(qū)主要用來確保數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)中平均分布，在RANGE和LIST分區(qū)中，必須明確指定一個(gè)給定的列值或列值集合應(yīng)該保存在哪個(gè)分區(qū)中，而在HASH分區(qū)中，MySQL會(huì)自動(dòng)完成這些工作，只需基于將要被哈希的列值指定一個(gè)列值或表達(dá)式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量即可。
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
本章首先介紹了數(shù)據(jù)的備份與還原，這是非常實(shí)用且必須的技能，讀者需要掌握，然后介紹了權(quán)限管理，權(quán)限管理一般由數(shù)據(jù)庫(kù)管理員操作，最后講解了如何實(shí)現(xiàn)MySQL集群，以及集群的應(yīng)用，實(shí)現(xiàn)了MySQL主從復(fù)制以及雙主互備，對(duì)于初學(xué)者來說，了解即可。
1.思考題
(1)?請(qǐng)簡(jiǎn)述
(2)?請(qǐng)簡(jiǎn)述
(3)?請(qǐng)簡(jiǎn)述
(4) 請(qǐng)簡(jiǎn)述
(5) 請(qǐng)簡(jiǎn)述
總結(jié)
- 上一篇： 今日热点如何卸载
- 下一篇： 如何打开.mmap查看的问题