Mysql第八天 分區與分表

標籤：添加   最好   匹配   order by   時間   自增   成本   資料   number   

分區表

主要提供例如以下的特性，或者適合如此情境：

• 資料量非常大， 或者僅僅有表中最後的部分有熱點資料。其它均為曆史資料
• 分區表資料更easy維護，能夠對獨立的分區刪除等操作
• 分區表的資料能夠分布在不同的物理裝置上。從而高效地利用多個硬體裝置。
• 能夠避免一些特殊瓶頸。比方InnoDB的單個索引的相互排斥訪問
• 能夠備份和恢複獨立的分區

建立分區表

通常有這麼幾種分法。由於主鍵或者是唯一約束鍵必須有一部分包括在分區鍵中，所以一般要不無主鍵，要不就依照自增主鍵的id進行定界分割，要不就把分區欄位和主鍵一起作為聯合主鍵。
另一些其它的限制，比方分區鍵的運算結果必須為整數

Range分區

CREATE TABLE biz_order(id bigint(20) NOT NULL AUTO_INCREMENT,created DATETIME NOT NULL COMMENT ‘建立時間‘,PRIMARY KEY (id, created)) ENGINE=InnoDB PARTITION BY RANGE(YEAR(created))(  PARTITION p_2010 VALUES LESS THAN (2015),  PARTITION p_latest VALUES LESS THAN MAXVALUE);

這樣的分區。最新的那個區顯然會有最多的熱點資料。 能夠再使用Hash子分區來降低競爭
- 除了使用YEAR, TO_DAY等日期函數外。還能夠使用其數學函數。比方模數，按7模數是周幾等

List分區

是用IN來做列值匹配的集合。

比方能夠依照地區來分為東西南北幾個區：

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

這樣的假設插入語句不在IN中。則會插入失敗

Hash分區

PARTITIONS為分區的數量。 即會依據分區鍵的值計算出一個hash值，然後以4為模進行儲存。優點是。不用再又一次建分區了。

PARTITION BY HASH(store_id)PARTITIONS 4;

還有Key分區，用的太少，不說了

操作分區表

添加刪除分區等語句看這裡

分區表由多個底層表構成，底層表跟普通表沒什麼差別，其索引也是分別在各個表中的索引。 分區表僅僅是會在一個非常粗的粒度上決定一下去哪個底層表繼續查詢。

• SELECT 鎖住底層表。最佳化器先推斷能夠過濾部分的分期，然後再調用儲存引擎介面訪問各個分區
• INSERT DELETE同行
• UPDATE 操作會須要設計更新後推斷在哪個分區，假設插入到了新分區。那麼則刪除原分區中的資料。
• 使用WHERE語句最好能夠明白用到分區的關鍵字，這樣能夠非常好的命中分區
• 鎖住底層表不一定是表鎖。會用到儲存引擎自己的行級鎖

怎樣使用

使用分區表肯定是由於資料量非常大。這個時候索引已經不能非常好的起作用了。
能夠不使用索引，而用粗粒度的命中分區表，然後全表掃描。

或者是針對熱點資料。單獨使用一個區讓這個區都能夠放到緩衝中，這樣就會有一個熱點的非常小的分區，能夠對其使用索引。

另外一些可能的問題：

• NULL值。由於TO_DAY等方法NULL值為無效入參，會把值放在第一個分區，這個時候SELECT的時候可能會須要尋找第一個和命中的分區這樣兩個分區。這樣可能會有非常多的效能損耗。解決的方法是使第一個分區儘可能的小。第二個辦法是直接使用RANGE COLUMNS()而不使用函數
• 分區列和索引列應該用同一個列。假設不是，會導致無法過濾的問題
• 尋找分區的成本可能會比較高
• 維護分區的成本，比方alter等語句改變分區個數，或者其它涉及資料移轉的操作

分區表的查詢

要在WHERE後面帶分區列，且不能是運算式
使用EXPLAIN PARTITIONS SELECT來推斷是否進行了分區過濾

分表

分區表還是一張表，是一種邏輯上的實現，主要解決的是單表資料過大。索引效率低的問題，非常適合大量曆史資料，少量活躍資料的情境。

把資料儲存在不同的地區。

分表是真的有多張表。基於分表還能夠做分庫，能夠提升並發效能。以及磁碟I/O的效能。

二者能夠配合使用。

使用叢集的方式

要配合複製使用。僅僅是把查詢請求進行了分攤。
可是這樣不會影響代碼層。

使用商務邏輯劃分

可一個依據使用者id來分，每一個使用者一張表，這樣須要每有新的使用者都建表了。

還有經常使用的做法是預先設計好比方100張表，然後對資料的一個欄位做hash，然後對100模數。

又或者依據時間來進行切割，這樣的的優點是，假設依據時間做統計的時候能夠不用UNION

上面的分表方式都不能解決依據server壓力進行選擇的問你，而且也不能比較均勻的儲存資料。

分表之後要考慮這樣幾個操作以後可能會帶來的問題：

• 分頁， 主要看分頁情況下排序的欄位是什麼，假設是時間，那麼依照時間段分表是比較好的， 假設會涉及到多個表的UNION，那麼就會比較耗費效能。
• 插入。 更新。 主要是更新的時候的主鍵的問題。由於分表之後主鍵不唯一了，因此須要用分表列和自增列做聯合主鍵。
• 分組。統計。 這個跟分頁考慮的情況差點兒相同，也是主要涉及排序的問題。比方假設每次都是須要依照使用者統計資訊的話，那麼依照使用者分表的選擇是沒錯的。
• 表的分發跟業務非常比較大的關係。要盡量考慮比較多的因素和情境。
• 通用一些的解決的方法是。對分分頁欄位使用搜尋引擎
• 或者對分頁和排序欄位單獨列一張表不分，作為查詢的索引。

使用merge儲存引擎

基本表:CREATE TABLE TEST_MERGE_1(ID INT(5) NOT NULL,VALUE VARCHAR(100) NOT NULL,PRIMARY KEY(ID));CREATE TABLE TEST_MERGE_2(ID INT(5) NOT NULL,VALUE VARCHAR(100) NOT NULL,PRIMARY KEY(ID));MERGE表：CREATE TABLE TEST_MERGE(ID INT(5) NOT NULL,VALUE VARCHAR(100) NOT NULL,PRIMARY KEY(ID)) TYPE=MRG_MyISAM INSERT_METHOD=LAST UNION=(TEST_MERGE_1,TEST_MERGE_2);

基本表必須是MYISAM類型的。
基本表的資料結構必須一致。
order by等語句，我想的是由於Merge表裡有基本表共同的索引，所以，排序的時候應該是，都先比較第一個，然後再。。。有點像經常使用的大檔案分成多個小檔案，然後分別排序。最後merge的過程。

主要是能夠提供比較好的編碼介面。

Mysql第八天 分區與分表