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

一、分區(qū)操作

1. 將沒有分區(qū)的表改為分區(qū)表
ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;
2. 刪除分區(qū)
# 刪除所有分區(qū), 同時數(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ū), 會提示 MAXVALUE can only be used in last partition definition, 這時可以使用修改分區(qū)來解決

主要從事網(wǎng)頁設(shè)計、PC網(wǎng)站建設(shè)（電腦版網(wǎng)站建設(shè)）、wap網(wǎng)站建設(shè)（手機(jī)版網(wǎng)站建設(shè)）、自適應(yīng)網(wǎng)站建設(shè)、程序開發(fā)、微網(wǎng)站、小程序開發(fā)等，憑借多年來在互聯(lián)網(wǎng)的打拼，我們在互聯(lián)網(wǎng)網(wǎng)站建設(shè)行業(yè)積累了豐富的網(wǎng)站設(shè)計、成都做網(wǎng)站、網(wǎng)絡(luò)營銷經(jīng)驗，集策劃、開發(fā)、設(shè)計、營銷、管理等多方位專業(yè)化運作于一體，具備承接不同規(guī)模與類型的建設(shè)項目的能力。

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));
5. 重新劃分分區(qū)
ALTER TABLE table1 REORGANIZE PARTITION 要修改的分區(qū)名(可以多個, 逗號分隔) INTO (
    PARTITION 新分區(qū)1的名字 VALUES LESS THAN (值),
    PARTITION 新分區(qū)2的名字 VALUES LESS THAN (值)
    ...
)

修改一個分區(qū)成兩個分區(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)
);

重新劃分多個分區(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. 不能與原方案有重疊
    1. 同時對多個分區(qū)劃分必須是連續(xù)的分區(qū)
    1. 分區(qū)類型不可以更改, 可以通過ALTER TABLE ... PARTITION BY ...實現(xiàn)
6. 修改分區(qū)數(shù)量(HASH/Key分區(qū))
ALTER TABLE clients COALESCE PARTITION 4;

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

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

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

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

    1. 執(zhí)行 ALTER TABLE ... EXCHANGE PARTITION不會在分區(qū)表或交換表上調(diào)用任何觸發(fā)器
    1. auto_increment會發(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. 交換一個子分區(qū)到一個沒有分區(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)

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

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

  1. 重建分區(qū)
    刪除所有記錄存儲在分區(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;

  1. 檢查分區(qū)

    ALTER TABLE trb3 CHECK PARTITION p1;

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

    ALTER TABLE t1 REPAIR PARTITION p0,p1;
  3. 獲取分區(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時, 優(yōu)化器會自動根據(jù)分區(qū)的條件, 進(jìn)行分區(qū)選擇來提高性能。

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

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

五、分區(qū)選擇

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

  1. 要檢查的分區(qū)由語句的發(fā)布者指定，與分區(qū)剪枝不同，它是自動的。
  2. 而分區(qū)修剪僅適用于查詢，分區(qū)明確的選擇是查詢和多個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. 無法使用存儲過程、存儲功能、UDF和插件
    1. 無法用戶變量或聲明變量
    1. 不允許位操作

七、性能影響

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

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

標(biāo)題名稱：Mysql分區(qū)介紹(九)——分區(qū)管理
轉(zhuǎn)載源于：http://www.muchs.cn/article2/geeeoc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供手機(jī)網(wǎng)站建設(shè)、App開發(fā)、網(wǎng)站維護(hù)、關(guān)鍵詞優(yōu)化、網(wǎng)站內(nèi)鏈、移動網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

h5響應(yīng)式網(wǎng)站建設(shè)