MySQL調優之分割槽表

PHPer技術棧發表於2021-11-09
MySql
系列文章：
MySQL調優之查詢優化 MySQL調優之索引優化 MySQL調優之分割槽表 MySQL調優效能監控之show profile MySQL調優使用者監控之show processlist MySQL調優效能監控之performance schema MySQL調優之索引資料結構詳解與索引優化 MySQL中redo log、undo log、binlog關係以及區別

1、為什麼是用分割槽表?

表非常大以至於無法全部都放在記憶體中，或者只在表的最後部分有熱點資料，其他均是歷史資料,分割槽表是指根據一定規則，將資料庫中的一張表分解成多個更小的，容易管理的部分。從邏輯上看，只有一張表，但是底層卻是由多個物理分割槽組成。

2、使用分割槽表的好處

(1)資料更容易維護

​ 批量刪除大量資料可以使用清除整個分割槽的方式

​ 對一個獨立分割槽進行優化、檢查、修復等操作

(2)高效利用裝置

資料可以分佈在不同的物理裝置上，高效地利用多個硬體裝置,和單個磁碟或者檔案系統相比，可以儲存更多資料

(3)可以使用分割槽表來避免某些特殊的瓶頸

​ innodb的單個索引的互斥訪問(之後補充)

​ ext3檔案系統的inode鎖競爭(之後補充)

(4)優化查詢

在where語句中包含分割槽條件時，可以只掃描一個或多個分割槽表來提高查詢效率；涉及sum和count語句時，也可以在多個分割槽上並行處理，最後彙總結果。

(5）分割槽表更容易維護。

例如：想批量刪除大量資料可以清除整個分割槽,可以備份和恢復獨立的分割槽

1、分割槽數目有限

一個表最多隻能有1024個分割槽,在5.7版本的時候可以支援8196個分割槽。

2、分別表表示式的限制

MySQL5.1中，分割槽表示式必須是整數，或者返回整數的表示式。在MySQL5.5中提供了非整數表示式分割槽的支援。

3、分割槽表對索引的限制

如果分割槽欄位中有主鍵或者唯一索引的列，那麼多有主鍵列和唯一索引列都必須包含進來。即：分割槽欄位要麼不包含主鍵或者索引列，要麼包含全部主鍵和索引列。

4、分割槽表中無法使用外來鍵約束

5、資料與索引同在

MySQL的分割槽適用於一個表的所有資料和索引，不能只對表資料分割槽而不對索引分割槽，也不能只對索引分割槽而不對錶分割槽，也不能只對表的一部分資料分割槽。

分割槽表由多個相關的底層表實現，這個底層表也是由控制程式碼物件標識，我們可以直接訪問各個分割槽。儲存引擎管理分割槽的各個底層表和管理普通表一樣（所有的底層表都必須使用相同的儲存引擎），分割槽表的索引知識在各個底層表上各自加上一個完全相同的索引。從儲存引擎的角度來看，底層表和普通表沒有任何不同，儲存引擎也無須知道這是一個普通表還是一個分割槽表的一部分。

分割槽表的操作按照以下的操作邏輯進行：

1、select查詢

當查詢一個分割槽表的時候，分割槽層先開啟並鎖住所有的底層表，優化器先判斷是否可以過濾部分分割槽，然後再呼叫對應的儲存引擎介面訪問各個分割槽的資料

2、insert操作

當寫入一條記錄的時候，分割槽層先開啟並鎖住所有的底層表，然後確定哪個分割槽接受這條記錄，再將記錄寫入對應底層表

3、delete操作

當刪除一條記錄時，分割槽層先開啟並鎖住所有的底層表，然後確定資料對應的分割槽，最後對相應底層表進行刪除操作

4、update操作

當更新一條記錄時，分割槽層先開啟並鎖住所有的底層表，mysql先確定需要更新的記錄再哪個分割槽，然後取出資料並更新，再判斷更新後的資料應該再哪個分割槽，最後對底層表進行寫入操作，並對源資料所在的底層表進行刪除操作

5、注意

有些操作時支援過濾的，例如，當刪除一條記錄時，MySQL需要先找到這條記錄，如果where條件恰好和分割槽表示式匹配，就可以將所有不包含這條記錄的分割槽都過濾掉，這對update同樣有效。如果是insert操作，則本身就是隻命中一個分割槽，其他分割槽都會被過濾掉。mysql先確定這條記錄屬於哪個分割槽，再將記錄寫入對應得曾分割槽表，無須對任何其他分割槽進行操作。

雖然每個操作都會“先開啟並鎖住所有的底層表”，但這並不是說分割槽表在處理過程中是鎖住全表的，如果儲存引擎能夠自己實現行級鎖，例如innodb，則會在分割槽層釋放對應表鎖。

1、範圍分割槽

根據列值在給定範圍內將行分配給分割槽。

範圍分割槽表的分割槽方式是：每個分割槽都包含行資料且分割槽的表示式在給定的範圍內，分割槽的範圍應該是連續的且不能重疊，可以使用values less than運算子來定義。

(1)、建立普通的表

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
);

(2)、按照store_id來進行分割槽

建立帶分割槽的表，下面建表的語句是按照store_id來進行分割槽的，指定了4個分割槽

CREATE TABLE employees2 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

(3)、less than maxvalue的使用

在當前的建表語句中可以看到，store_id的值在1-5的在p0分割槽，6-10的在p1分割槽，11-15的在p3分割槽，16-20的在p4分割槽，但是如果插入超過20的值就會報錯，因為mysql不知道將資料放在哪個分割槽,可以使用less than maxvalue來避免此種情況。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

–maxvalue表示始終大於等於最大可能整數值的整數值

(4)、根據職務程式碼分割槽

可以使用相同的方式根據員工的職務程式碼對錶進行分割槽

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (job_code) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (10000)
);

(5)、用date型別進行分割槽

可以使用date型別進行分割槽：如虛妄根據每個員工離開公司的年份進行劃分，如year(separated)

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

(6)、用函式來對錶進行分割槽

可以使用函式根據range的值來對錶進行分割槽，如timestampunix_timestamp()

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

–timestamp不允許使用任何其他涉及值的表示式

2、基於時間間隔分割槽

基於時間間隔的分割槽方案，在mysql5.7中，可以基於範圍或事件間隔實現分割槽方案，有兩種選擇

1、基於範圍的分割槽，對於分割槽表示式，可以使用操作函式基於date、time、或者datatime列來返回一個整數值

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

2、基於範圍列的分割槽，使用date或者datatime列作為分割槽列

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
    PARTITION p0 VALUES LESS THAN ('1960-01-01'),
    PARTITION p1 VALUES LESS THAN ('1970-01-01'),
    PARTITION p2 VALUES LESS THAN ('1980-01-01'),
    PARTITION p3 VALUES LESS THAN ('1990-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

3、列表分割槽

類似於按range分割槽，區別在於list分割槽是基於列值匹配一個離散值集合中的某個值來進行選擇

`CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);`

4、列分割槽

mysql從5.5開始支援column分割槽，可以認為i是range和list的升級版，在5.5之後，可以使用column分割槽替代range和list，但是column分割槽只接受普通列不接受表示式

`CREATE TABLE `list_c` (
 `c1` int(11) DEFAULT NULL,
 `c2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(c1)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */
 CREATE TABLE `list_c` (
 `c1` int(11) DEFAULT NULL,
 `c2` int(11) DEFAULT NULL,
 `c3` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(c1,c3)
(PARTITION p0 VALUES LESS THAN (5,'aaa') ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (10,'bbb') ENGINE = InnoDB) */
 CREATE TABLE `list_c` (
 `c1` int(11) DEFAULT NULL,
 `c2` int(11) DEFAULT NULL,
 `c3` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY LIST COLUMNS(c3)
(PARTITION p0 VALUES IN ('aaa') ENGINE = InnoDB,
 PARTITION p1 VALUES IN ('bbb') ENGINE = InnoDB) */

5、hash分割槽

基於使用者定義的表示式的返回值來進行選擇的分割槽，該表示式使用將要插入到表中的這些行的列值進行計算。這個函式可以包含myql中有效的、產生非負整數值的任何表示式

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;
CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LINEAR HASH(YEAR(hired))
PARTITIONS 4;

6、key分割槽

類似於hash分割槽，區別在於key分割槽只支援一列或多列，且mysql伺服器提供其自身的雜湊函式，必須有一列或多列包含整數值

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

7、子分割槽

在分割槽的基礎之上，再進行分割槽後儲存

CREATE TABLE `t_partition_by_subpart`
(
  `id` INT AUTO_INCREMENT,
  `sName` VARCHAR(10) NOT NULL,
  `sAge` INT(2) UNSIGNED ZEROFILL NOT NULL,
  `sAddr` VARCHAR(20) DEFAULT NULL,
  `sGrade` INT(2) NOT NULL,
  `sStuId` INT(8) DEFAULT NULL,
  `sSex` INT(1) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`, `sGrade`)
)  ENGINE = INNODB
PARTITION BY RANGE(id)
SUBPARTITION BY HASH(sGrade) SUBPARTITIONS 2
(
PARTITION p0 VALUES LESS THAN(5),
PARTITION p1 VALUES LESS THAN(10),
PARTITION p2 VALUES LESS THAN(15)
);

如果需要從非常大的表中查詢出某一段時間的記錄，而這張表中包含很多年的歷史資料，資料是按照時間排序的，此時應該如何查詢資料呢？

因為資料量巨大，肯定不能在每次查詢的時候都掃描全表。考慮到索引在空間和維護上的消耗，也不希望使用索引，即使使用索引，會發現會產生大量的碎片，還會產生大量的隨機IO，但是當資料量超大的時候，索引也就無法起作用了，此時可以考慮使用分割槽來進行解決

1、全量掃描資料，不要任何索引

使用簡單的分割槽方式存放表，不要任何索引，根據分割槽規則大致定位需要的資料為止，通過使用where條件將需要的資料限制在少數分割槽中，這種策略適用於以正常的方式訪問大量資料

2、索引資料，並分離熱點

如果資料有明顯的熱點，而且除了這部分資料，其他資料很少被訪問到，那麼可以將這部分熱點資料單獨放在一個分割槽中，讓這個分割槽的資料能夠有機會都快取在記憶體中，這樣查詢就可以只訪問一個很小的分割槽表，能夠使用索引，也能夠有效的使用快取

1、null值會使分割槽過濾無效

2、分割槽列和索引列不匹配，會導致查詢無法進行分割槽過濾

3、選擇分割槽的成本可能很高

4、開啟並鎖住所有底層表的成本可能很高

5、維護分割槽的成本可能很高

來源：www.cnblogs.com/Courage129/p/14200...

本作品採用《CC 協議》，轉載必須註明作者和本文連結
PHPer技術棧

相關文章