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

發布時間：2020-06-15 14:35:19 來源：網絡閱讀：3172 作者：AndyMac 欄目：MySQL數據庫

一、分區操作

1. 將沒有分區的表改為分區表

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

2. 刪除分區

# 刪除所有分區, 同時數據丟失
ALTER TABLE es2 REMOVE PARTITIONING;

# 刪除指定分區, 數據丟失
ALTER TABLE tr DROP PARTITION p2;

3. SELECT指定分區查詢

select * from daily_rank_1_1 partition (p2015_04_24) limit 10;

4. 添加分區

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

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

5. 重新劃分分區

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

修改一個分區成兩個分區

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)
);

重新劃分多個分區

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

分區修改的原則:<br />

1. 不能與原方案有重疊
1. 同時對多個分區劃分必須是連續的分區
1. 分區類型不可以更改, 可以通過ALTER TABLE ... PARTITION BY ...實現

6. 修改分區數量(HASH/Key分區)

ALTER TABLE clients COALESCE PARTITION 4;

二、交換分區和子分區

支持交換分區的條件

1. 表自身不是分區表
1. 不是臨時表
1. 兩個表的結構相同
1. 表不包含外鍵
1. 表的數據沒有出界

如果要執行操作, 必須具有DROP權限<br />

1. 執行 ALTER TABLE ... EXCHANGE PARTITION不會在分區表或交換表上調用任何觸發器
1. auto_increment會發生重置

具體操作:
pt是分區表, p是分區或子分區

1. 與非分區表交換分區

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分區的數據寫入e2
ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;

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

2. 交換一個子分區到一個沒有分區的表

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的數據交換到es2表
mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
Query OK, 0 rows affected (0.29 sec)

如果一個表擁有子分區, 則不能移動這個父分區到表中

三、分區維護

重建分區
刪除所有記錄存儲在分區，然后重新插入它們。整理碎片
```
ALTER TABLE t1 REBUILD PARTITION p0, p1;
```
優化分區
優化分區來回收未使用的空間和整理的分區中的數據文件
```
ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;
```
分析分區

ALTER TABLE t1 ANALYZE PARTITION p3;

檢查分區
```
ALTER TABLE trb3 CHECK PARTITION p1;
```
修復分區
```
ALTER TABLE t1 REPAIR PARTITION p0,p1;
```

獲取分區有效信息

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

四、分區修剪

在執行sql時, 優化器會自動根據分區的條件, 進行分區選擇來提高性能。

分區修剪的條件: <br />

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

五、分區選擇

在執行操作的時候優化器會根據語句自動進行修剪, 但是在有些時候是不同的:

要檢查的分區由語句的發布者指定，與分區剪枝不同，它是自動的。
而分區修剪僅適用于查詢，分區明確的選擇是查詢和多個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)

也可以在聯表中使用

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)

刪除中使用分區選擇

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)

更新中使用分區選擇

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使用分區選擇

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)

六、分區的限制

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

七、性能影響

1. 分區的創建、修改、刪除取決于文件系統。應該確保large_files_support啟用，open_files_limit設置正確
1. 在執行分區操作時需要上寫鎖, 但是不影響查詢, 分區操作完成后會立即執行插入和更新操作
1. 分區操作，查詢、更新往往是MYISAM比INNODB更快
1. 使用索引可以在非分區表提高性能, 使用分區修剪也可以顯著的提高性能
1. 加載數據使用緩沖來提高性能。您應該知道緩沖區每分區使用130KB內存來實現這一點。
1. Mysql5.6.7之前, 分區最大數為1024個, 從5.6.7開始, 分區表的數最多是8192個, 包括子分區
1. 分區表不支持查詢緩存

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

向AI問一下細節

亚洲激情专区-91九色丨porny丨老师-久久久久久久女国产乱让韩-国产精品午夜小视频观看

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

一、分區操作

1. 將沒有分區的表改為分區表

2. 刪除分區

3. SELECT指定分區查詢

4. 添加分區

5. 重新劃分分區

6. 修改分區數量(HASH/Key分區)

二、交換分區和子分區

1. 與非分區表交換分區

2. 交換一個子分區到一個沒有分區的表

三、分區維護

四、分區修剪

五、分區選擇

六、分區的限制

七、性能影響

猜你喜歡

亚洲激情专区-91九色丨porny丨老师-久久久久久久女国产乱让韩-国产精品午夜小视频观看

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

一、分區操作

1. 將沒有分區的表改為分區表

2. 刪除分區

3. SELECT指定分區查詢

4. 添加分區

5. 重新劃分分區

6. 修改分區數量(HASH/Key分區)

二、交換分區和子分區

1. 與非分區表交換分區

2. 交換一個子分區到一個沒有分區的表

三、分區維護

四、分區修剪

五、分區選擇

六、分區的限制

七、性能影響

猜你喜歡

最新資訊

相關推薦

相關標簽