range 分區

create table range(

id int(11),

money int(11) unsigned not null,

date datetime

)partition by range(year(date))(

partition p2007 values less than (2008),

partition p2008 values less than (2009),

partition p2009 values less than (2010)

partition p2010 values less than maxvalue

)；

list 分區

create table list(

a int(11),

b int(11)

)(partition by list (b)

partition p0 values in (1,3,5,7,9),

partition p1 values in (2,4,6,8,0)

);

hash 分區

create table hash(

a int(11),

b datetime

)partition by hash (YEAR(b) )

partitions 4;

key 分區

create table t_key(

a int(11),

b datetime

)

partition by key (b)

partitions 4;

-- 新增分區

ALTER TABLE sale_data

ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));

1.分區表的原理

分區表是由多個相關的底層表實現，這些底層表也是由句柄對象表示，所以我們也可以直接訪問各個分區，存儲引擎管理分區的各個底層表和管理普通表一樣(所有的底層表都必須使用相同的存儲引擎)，分區表的索引只是在各個底層表上各自加上一個相同的索引，從存儲引擎的角度來看，底層表和一個普通表沒有任何不同，存儲引擎也無須知道這是一個普通表還是一個分區表的一部分。

在分區表上的操作按照下面的操作邏輯進行：

select查詢：

當查詢一個分區表的時候，分區層先打開并鎖住所有的底層表，優化器判斷是否可以過濾部分分區，然后再調用對應的存儲引擎接口訪問各個分區的數據

insert操作：

當寫入一條記錄時，分區層打開并鎖住所有的底層表，然后確定哪個分區接受這條記錄，再將記錄寫入對應的底層表

delete操作：

當刪除一條記錄時，分區層先打開并鎖住所有的底層表，然后確定數據對應的分區，最后對相應底層表進行刪除操作

update操作：

當更新一條數據時，分區層先打開并鎖住所有的底層表，mysql先確定需要更新的記錄在哪個分區，然后取出數據并更新，再判斷更新后的數據應該放在哪個分區，然后對底層表進行寫入操作，并對原數據所在的底層表進行刪除操作

雖然每個操作都會打開并鎖住所有的底層表，但這并不是說分區表在處理過程中是鎖住全表的，如果存儲引擎能夠自己實現行級鎖，如：innodb，則會在分區層釋放對應的表鎖，這個加鎖和解鎖過程與普通Innodb上的查詢類似。

2.在下面的場景中，分區可以起到非常大的作用：

A：表非常大以至于無法全部都放在內存中，或者只在表的最后部分有熱點數據，其他都是歷史數據

B：分區表的數據更容易維護，如：想批量刪除大量數據可以使用清除整個分區的方式。另外，還可以對一個獨立分區進行優化、檢查、修復等操作

C：分區表的數據可以分布在不同的物理設備上，從而高效地利用多個硬件設備

D：可以使用分區表來避免某些特殊的瓶頸，如：innodb的單個索引的互斥訪問，ext3文件系統的inode鎖競爭等

E：如果需要，還可以備份和恢復獨立的分區，這在非常大的數據集的場景下效果非常好

F：優化查詢，在where字句中包含分區列時，可以只使用必要的分區來提高查詢效率，同時在涉及sum()和count()這類聚合函數的查詢時，可以在每個分區上面并行處理，最終只需要匯總所有分區得到的結果。

3.分區本身也有一些限制：

A：一個表最多只能有1024個分區(mysql5.6之后支持8192個分區)

B：在mysql5.1中分區表達式必須是整數，或者是返回整數的表達式，在5.5之后，某些場景可以直接使用字符串列和日期類型列來進行分區(使用varchar字符串類型列時，一般還是字符串的日期作為分區)。

C：如果分區字段中有主鍵或者唯一索引列，那么所有主鍵列和唯一索引列都必須包含進來，如果表中有主鍵或唯一索引，那么分區鍵必須是主鍵或唯一索引

D：分區表中無法使用外鍵約束

E：mysql數據庫支持的分區類型為水平分區，并不支持垂直分區，因此，mysql數據庫的分區中索引是局部分區索引，一個分區中既存放了數據又存放了索引，而全局分區是指的數據庫放在各個分區中，但是所有的數據的索引放在另外一個對象中

F：目前mysql不支持空間類型和臨時表類型進行分區。不支持全文索引

4.子分區的建立需要注意以下幾個問題：

A：每個子分區的數量必須相同

B：只要在一個分區表的任何分區上使用subpartition來明確定義任何子分區，就必須在所有分區上定義子分區，不能漏掉一些分區不進行子分區。

C：每個subpartition子句必須包括子分區的一個名字

D：子分區的名字必須是唯一的，不能在一張表中出現重名的子分區

E：mysql數據庫的分區總是把null當作比任何非null更小的值，這和數據庫中處理null值的order by操作是一樣的，升序排序時null總是在最前面，因此對于不同的分區類型，mysql數據庫對于null的處理也各不相同。對于range分區，如果向分區列插入了null，則mysql數據庫會將該值放入最左邊的分區，注意，如果刪除分區，分區下的所有內容都從磁盤中刪掉了，null所在分區被刪除，null值也就跟著被刪除了。在list分區下要使用null，則必須顯式地定義在分區的散列值中，否則插入null時會報錯。hash和key分區對于null的處理方式和range,list分區不一樣，任何分區函數都會將null返回為0.

總結

以上是生活随笔為你收集整理的mysql 表分区 排序_mysql 表分区的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。