Mysql 分區(qū)介紹(九) —— 分區(qū)管理

一、分區(qū)操作

1. 將沒有分區(qū)的表改為分區(qū)表

ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;

2. 刪除分區(qū)

# 刪除所有分區(qū), 同時(shí)數(shù)據(jù)丟失
ALTER TABLE es2 REMOVE PARTITIONING;

# 刪除指定分區(qū), 數(shù)據(jù)丟失
ALTER TABLE tr DROP PARTITION p2;

3. SELECT指定分區(qū)查詢

select * from daily_rank_1_1 partition (p2015_04_24) limit 10;

4. 添加分區(qū)

如果設(shè)置了MAXVALUE則無法添加新分區(qū), 會(huì)提示 MAXVALUE can only be used in last partition definition, 這時(shí)可以使用修改分區(qū)來解決

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));

5. 重新劃分分區(qū)

ALTER TABLE table1 REORGANIZE PARTITION 要修改的分區(qū)名(可以多個(gè), 逗號(hào)分隔) INTO (
    PARTITION 新分區(qū)1的名字 VALUES LESS THAN (值),
    PARTITION 新分區(qū)2的名字 VALUES LESS THAN (值)
    ...
)

修改一個(gè)分區(qū)成兩個(gè)分區(qū)

alter table daily_rank_1_1 reorganize partition p2015_04_28 into(
partition p2015_04_28 values less than (to_days('2015-04-28')),
partition pmax values less than(MAXVALUE)
);

重新劃分多個(gè)分區(qū)

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
    PARTITION m0 VALUES LESS THAN (1980),
    PARTITION m1 VALUES LESS THAN (2000)
);

分區(qū)修改的原則:<br />

• 1. 不能與原方案有重疊
• 2. 同時(shí)對(duì)多個(gè)分區(qū)劃分必須是連續(xù)的分區(qū)
• 3. 分區(qū)類型不可以更改, 可以通過ALTER TABLE ... PARTITION BY ...實(shí)現(xiàn)

6. 修改分區(qū)數(shù)量(HASH/Key分區(qū))

ALTER TABLE clients COALESCE PARTITION 4;

二、交換分區(qū)和子分區(qū)

支持交換分區(qū)的條件

• 1. 表自身不是分區(qū)表
• 2. 不是臨時(shí)表
• 3. 兩個(gè)表的結(jié)構(gòu)相同
• 4. 表不包含外鍵
• 5. 表的數(shù)據(jù)沒有出界

如果要執(zhí)行操作, 必須具有DROP權(quán)限<br />

• 1. 執(zhí)行 ALTER TABLE ... EXCHANGE PARTITION不會(huì)在分區(qū)表或交換表上調(diào)用任何觸發(fā)器
• 2. auto_increment會(huì)發(fā)生重置

具體操作:
pt是分區(qū)表, p是分區(qū)或子分區(qū)

1. 與非分區(qū)表交換分區(qū)

CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (50),
        PARTITION p1 VALUES LESS THAN (100),
        PARTITION p2 VALUES LESS THAN (150),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
INSERT INTO e VALUES
    (1669, "Jim", "Smith"),
    (337, "Mary", "Jones"),
    (16, "Frank", "White"),
    (2005, "Linda", "Black");
mysql> CREATE TABLE e2 LIKE e;
Query OK, 0 rows affected (1.34 sec)
mysql> ALTER TABLE e2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.90 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 將p0分區(qū)的數(shù)據(jù)寫入e2
ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;

如果沒有匹配到數(shù)據(jù), 則提示Found row that does not match the partition

2. 交換一個(gè)子分區(qū)到一個(gè)沒有分區(qū)的表

mysql> CREATE TABLE es (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30)
    -> )
    ->     PARTITION BY RANGE (id)
    ->     SUBPARTITION BY KEY (lname)
    ->     SUBPARTITIONS 2 (
    ->         PARTITION p0 VALUES LESS THAN (50),
    ->         PARTITION p1 VALUES LESS THAN (100),
    ->         PARTITION p2 VALUES LESS THAN (150),
    ->         PARTITION p3 VALUES LESS THAN (MAXVALUE)
    ->     );
Query OK, 0 rows affected (2.76 sec)
mysql> INSERT INTO es VALUES
    ->     (1669, "Jim", "Smith"),
    ->     (337, "Mary", "Jones"),
    ->     (16, "Frank", "White"),
    ->     (2005, "Linda", "Black");
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> CREATE TABLE es2 LIKE es;
Query OK, 0 rows affected (1.27 sec)
mysql> ALTER TABLE es2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 將p3sp0的數(shù)據(jù)交換到es2表
mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
Query OK, 0 rows affected (0.29 sec)

如果一個(gè)表擁有子分區(qū), 則不能移動(dòng)這個(gè)父分區(qū)到表中

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

1. 重建分區(qū)
   刪除所有記錄存儲(chǔ)在分區(qū)，然后重新插入它們。整理碎片

   ALTER TABLE t1 REBUILD PARTITION p0, p1;

2. 優(yōu)化分區(qū)
   優(yōu)化分區(qū)來回收未使用的空間和整理的分區(qū)中的數(shù)據(jù)文件

   ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;

3. 分析分區(qū)

ALTER TABLE t1 ANALYZE PARTITION p3;

4. 檢查分區(qū)

   ALTER TABLE trb3 CHECK PARTITION p1;

5. 修復(fù)分區(qū)

   ALTER TABLE t1 REPAIR PARTITION p0,p1;

6. 獲取分區(qū)有效信息

   mysql> SHOW CREATE TABLE trb3\G
   *************************** 1. row ***************************
      Table: trb3
   Create Table: CREATE TABLE `trb3` (
   `id` int(11) default NULL,
   `name` varchar(50) default NULL,
   `purchased` date default NULL
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1
   PARTITION BY RANGE (YEAR(purchased)) (
   PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,
   PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM,
   PARTITION p2 VALUES LESS THAN (2000) ENGINE = MyISAM,
   PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
   )
   1 row in set (0.00 sec)

select *
from INFORMATION_SCHEMA.PARTITIONS

四、分區(qū)修剪

在執(zhí)行sql時(shí), 優(yōu)化器會(huì)自動(dòng)根據(jù)分區(qū)的條件, 進(jìn)行分區(qū)選擇來提高性能。

分區(qū)修剪的條件: <br />

• 1. partition_column = constant
• 2. partition_column IN (constant1, constant2, ..., constantN)
     where條件中包含<，>，< =，> =，和< > 等之間范圍查詢的時(shí)候, 就可以使用分區(qū)修剪
     SELECT、UPDATE和DELETE都可以修剪分區(qū), 但是INSERT無法修剪分區(qū)

五、分區(qū)選擇

在執(zhí)行操作的時(shí)候優(yōu)化器會(huì)根據(jù)語句自動(dòng)進(jìn)行修剪, 但是在有些時(shí)候是不同的:

1. 要檢查的分區(qū)由語句的發(fā)布者指定，與分區(qū)剪枝不同，它是自動(dòng)的。
2. 而分區(qū)修剪僅適用于查詢，分區(qū)明確的選擇是查詢和多個(gè)DML語句支持。
   支持的語句: SELECT、DELETE、INSERT、REPLACE、UPDATE、LOAD DATA.、LOAD XML.

具體的語句:

      PARTITION (partition_names)

      partition_names:
          partition_name, ...

SELECT * FROM employees PARTITION (p1);

mysql> SELECT * FROM employees PARTITION (p0, p2)
    ->     WHERE lname LIKE 'S%';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
|  4 | Jim   | Smith |        2 |             4 |
| 11 | Jill  | Stone |        1 |             4 |
+----+-------+-------+----------+---------------+
2 rows in set (0.00 sec)

mysql> SELECT id, CONCAT(fname, ' ', lname) AS name
    ->     FROM employees PARTITION (p0) ORDER BY lname;
+----+----------------+
| id | name           |
+----+----------------+
|  3 | Ellen Johnson  |
|  4 | Jim Smith      |
|  1 | Bob Taylor     |
|  2 | Frank Williams |
+----+----------------+
4 rows in set (0.06 sec)

mysql> SELECT store_id, COUNT(department_id) AS c
    ->     FROM employees PARTITION (p1,p2,p3)
    ->     GROUP BY store_id HAVING c > 4;
+---+----------+
| c | store_id |
+---+----------+
| 5 |        2 |
| 5 |        3 |
+---+----------+
2 rows in set (0.00 sec)

你也可以使用PARTITION 在INSERT...SELECT語句上

mysql> CREATE TABLE employees_copy LIKE employees;
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO employees_copy
    ->     SELECT * FROM employees PARTITION (p2);
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM employees_copy;
+----+--------+----------+----------+---------------+
| id | fname  | lname    | store_id | department_id |
+----+--------+----------+----------+---------------+
| 10 | Lou    | Waters   |        2 |             4 |
| 11 | Jill   | Stone    |        1 |             4 |
| 12 | Roger  | White    |        3 |             2 |
| 13 | Howard | Andrews  |        1 |             2 |
| 14 | Fred   | Goldberg |        3 |             3 |
+----+--------+----------+----------+---------------+
5 rows in set (0.00 sec)

也可以在聯(lián)表中使用

CREATE TABLE stores (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    city VARCHAR(30) NOT NULL
)
    PARTITION BY HASH(id)
    PARTITIONS 2;

INSERT INTO stores VALUES
    ('', 'Nambucca'), ('', 'Uranga'),
    ('', 'Bellingen'), ('', 'Grafton');

CREATE TABLE departments  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL
)
    PARTITION BY KEY(id)
    PARTITIONS 2;

INSERT INTO departments VALUES
    ('', 'Sales'), ('', 'Customer Service'),
    ('', 'Delivery'), ('', 'Accounting');

mysql> SELECT
    ->     e.id AS 'Employee ID', CONCAT(e.fname, ' ', e.lname) AS Name,
    ->     s.city AS City, d.name AS department
    -> FROM employees AS e
    ->     JOIN stores PARTITION (p1) AS s ON e.store_id=s.id
    ->     JOIN departments PARTITION (p0) AS d ON e.department_id=d.id
    -> ORDER BY e.lname;
+-------------+---------------+-----------+------------+
| Employee ID | Name          | City      | department |
+-------------+---------------+-----------+------------+
|          14 | Fred Goldberg | Bellingen | Delivery   |
|           5 | Mary Jones    | Nambucca  | Sales      |
|          17 | Mark Morgan   | Bellingen | Delivery   |
|           9 | Andy Smith    | Nambucca  | Delivery   |
|           8 | June Wilson   | Bellingen | Sales      |
+-------------+---------------+-----------+------------+
5 rows in set (0.00 sec)

刪除中使用分區(qū)選擇

mysql> SELECT * FROM employees WHERE fname LIKE 'j%';
+----+-------+--------+----------+---------------+
| id | fname | lname  | store_id | department_id |
+----+-------+--------+----------+---------------+
|  4 | Jim   | Smith  |        2 |             4 |
|  8 | June  | Wilson |        3 |             1 |
| 11 | Jill  | Stone  |        1 |             4 |
+----+-------+--------+----------+---------------+
3 rows in set (0.00 sec)

mysql> DELETE FROM employees PARTITION (p0, p1)
    ->     WHERE fname LIKE 'j%';
Query OK, 2 rows affected (0.09 sec)

mysql> SELECT * FROM employees WHERE fname LIKE 'j%';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill  | Stone |        1 |             4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)

更新中使用分區(qū)選擇

mysql> UPDATE employees PARTITION (p0)
    ->     SET store_id = 2 WHERE fname = 'Jill';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SELECT * FROM employees WHERE fname = 'Jill';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill  | Stone |        1 |             4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)

mysql> UPDATE employees PARTITION (p2)
    ->     SET store_id = 2 WHERE fname = 'Jill';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM employees WHERE fname = 'Jill';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill  | Stone |        2 |             4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)

INSERT和REPLACE INTO使用分區(qū)選擇

mysql> INSERT INTO employees PARTITION (p2) VALUES (20, 'Jan', 'Jones', 1, 3);
ERROR 1729 (HY000): Found a row not matching the given partition set
mysql> INSERT INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 1, 3);
Query OK, 1 row affected (0.07 sec)

mysql> REPLACE INTO employees PARTITION (p0) VALUES (20, 'Jan', 'Jones', 3, 2);
ERROR 1729 (HY000): Found a row not matching the given partition set

mysql> REPLACE INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 3, 2);
Query OK, 2 rows affected (0.09 sec)

六、分區(qū)的限制

• 1. 無法使用存儲(chǔ)過程、存儲(chǔ)功能、UDF和插件
• 2. 無法用戶變量或聲明變量
• 3. 不允許位操作

七、性能影響

• 1. 分區(qū)的創(chuàng)建、修改、刪除取決于文件系統(tǒng)。應(yīng)該確保large_files_support啟用，open_files_limit設(shè)置正確
• 2. 在執(zhí)行分區(qū)操作時(shí)需要上寫鎖, 但是不影響查詢, 分區(qū)操作完成后會(huì)立即執(zhí)行插入和更新操作
• 3. 分區(qū)操作， 查詢、更新往往是MYISAM比INNODB更快
• 4. 使用索引可以在非分區(qū)表提高性能, 使用分區(qū)修剪也可以顯著的提高性能
• 5. 加載數(shù)據(jù)使用緩沖來提高性能。您應(yīng)該知道緩沖區(qū)每分區(qū)使用130KB內(nèi)存來實(shí)現(xiàn)這一點(diǎn)。
• 6. Mysql5.6.7之前, 分區(qū)最大數(shù)為1024個(gè), 從5.6.7開始, 分區(qū)表的數(shù)最多是8192個(gè), 包括子分區(qū)
• 7. 分區(qū)表不支持查詢緩存