詳細介紹mysql中的分區

4612

概述

?之前，看到分區，我捏個去，好高大上喲。昨天終于知道了分區是個啥玩意，也不過如此，今天總結一下，好記性不如爛筆頭嘛。
mysql從5.1開始支持分區功能。分區一句話就是：把一張表按照某種規則（range/list/hash/key等）分成多個區域（頁/文件）保存。對mysql應用開發來說，分區與不分區是沒區別的（即對應用是透明的）。如同突圍戰中的“化整為零”。mysql支持大部分的存儲引擎（如：myisam、innodb、memory等）創建分區，不支持merge和csv來創建分區。同一個分區表中的所有分區必須是同一個存儲引擎。做一個引例：

#創建一個5個hash分區的myisam表  CREATE?TABLE?`test`.`partition_t1`(??  ??`id`?INT?UNSIGNED?NOT?NULL,  ??`username`?VARCHAR(30)?NOT?NULL,  ??`email`?VARCHAR(30)?NOT?NULL,  ??`birth_date`?DATE?NOT?NULL  )?ENGINE=MYISAM  PARTITION?BY?HASH(MONTH(birth_date))  PARTITIONS?5;

詳細介紹mysql中的分區

引例結果

分區作用

  • 可以存儲更多的數據（系統單個文件最大限制）

  • 優化查詢，在mysql中，如果包含分區條件，只需要掃描一個或部分分區來提高查詢效率。在涉及sum()這類mysql時候， 可以在分區上并行處理，最后匯總結果。

  • 對于過期或不需要的數據，可以mysql相關分區來快速刪除數據。

  • 跨多個磁盤來分散數據查詢，單表的并發能力提高了，磁盤I/O性能也提高了。

分區類型

分為4種：

  • range分區：基于一個給定的連續區間范圍，把數據分配到不同的分區中。

  • list分區：類似range分區，區別在于list是基于枚舉出的值列表分區，range是根據范圍來分區的。

  • hash分區：基于給定的分區個數，把數據分配到不同分區（取模/線性）

  • key分區：類似于hash分區。

MySQL5.1中range,list,hash分區要求分區鍵必須是int。MySQL5.5及以上，支持非整型的range和list分區，即：range columns 和 list columns。
注意：無論哪種分區，要么分區表上沒有主鍵/唯一鍵，要么分區鍵必須有一個是主鍵/唯一鍵。

1.range分區

range分區是利用取值范圍（區間）劃分分區，區間要連續并且不能互相重疊，使用values less thanmysql進行分區定義。

例一：

CREATE?TABLE?`test`.`partition_t2`(??  ??`id`?INT?UNSIGNED?NOT?NULL,  ??`username`?VARCHAR(30)?NOT?NULL,  ??`email`?VARCHAR(30)?NOT?NULL,  ??`birth_date`?DATE?NOT?NULL  )?ENGINE=MYISAM  PARTITION?BY?RANGE(id)(  ???PARTITION?t21?VALUES?LESS?THAN?(10),  ???PARTITION?t22?VALUES?LESS?THAN?(20),  ???PARTITION?t23?VALUES?LESS?THAN?MAXVALUE  );

上例中定義了一個包含3個分區（t21,t22,t23）的range分區表，這個有點類似與高級語言中的mysql。解釋如下：當idid>=10的時候，在t22分區；當id>=20時候，在t23分區。

例二：

CREATE?TABLE?`test`.`partition_t3`(??  ??`id`?INT?UNSIGNED?NOT?NULL,  ??`username`?VARCHAR(30)?NOT?NULL,  ??`email`?VARCHAR(30)?NOT?NULL,  ??`birth_date`?DATE?NOT?NULL  )?ENGINE=MYISAM  PARTITION?BY?RANGE?COLUMNS(birth_date)(  ???PARTITION?t31?VALUES?LESS?THAN?('1996-01-01'),  ???PARTITION?t32?VALUES?LESS?THAN?('2006-01-01'),  ???PARTITION?t33?VALUES?LESS?THAN?('2038-01-01')  );

MySQL5.5改進range分區，提供range columns分區支持非mysql分區。

2.list分區

list分區創建離散的值列表（類似mysql中的enum類型數據）來劃分分區，使用values in操作符來分區。list分區不必要聲明任何特定的順序的。list有很多方面類似于range。

CREATE?TABLE?`test`.`partition_t4`(??  ??`id`?INT?UNSIGNED?NOT?NULL,  ??`username`?VARCHAR(30)?NOT?NULL,  ??`email`?VARCHAR(30)?NOT?NULL,  ??`birth_date`?DATE?NOT?NULL  )?ENGINE=MYISAM  PARTITION?BY?LIST(id)(  ???PARTITION?t41?VALUES?IN?(1,2),  ???PARTITION?t42?VALUES?IN?(3,6),  ???PARTITION?t43?VALUES?IN?(5,4),  ???PARTITION?t44?VALUES?IN?(7,8)  );

上面的例子是，當id為1或2，在t41分區；當id為3或6，在t42分區，以此類推…

3.hash分區

hash分區主要用來分散熱點讀取，確保數據在預定確定個數分區中盡可能的平均分布。一個表執行hash分區，mysql會對分區鍵應用一個散列函數，以此確定數據應該放在n個分區中的哪一個分區。hash分區支持兩種散列函數（分區方式）：取模算法（默認hash分區方式）和線性的2的冪的運算法則（liner hash 分區）。

常規hash分區

#頂部引例就是常規hash分區

  • mysql不推薦使用涉及多列的hashmysql。

  • 常規hash在分區管理上帶來的代價太大了，不適合靈活變動的分區的需求。參見：一致性哈希算法

  • 因為常規hash分區在管理上的問題，所有mysql引入線性hash分區。

    線性hash分區

    CREATE?TABLE?`test`.`partition_t5`(??  `id`?INT?UNSIGNED?NOT?NULL,  `username`?VARCHAR(30)?NOT?NULL,  `email`?VARCHAR(30)?NOT?NULL,  `birth_date`?DATE?NOT?NULL  )?ENGINE=MYISAM  PARTITION?BY?LINEAR?HASH(id)  PARTITIONS?5;

    上例中，創建一個5個分區的線性hash分區。

  • 線性hash分區優點：在分區維護上，mysql能夠處理更加迅速；

  • 線性hash分區缺點：分區各個分區之間數據分布不太均衡。

4.key分區

  • hash分區允許用戶自定義的表達式，而key分區不允許使用用戶自定義的表達式。

  • hash分區只支持整數分區，key分區支持除了blob或text類型之外的其他mysql分區。

  • 與hash分區不同，創建key分區表的時候，可以不指定分區鍵，默認會選擇使用主鍵/唯一鍵作為分區鍵，沒有主鍵/唯一鍵,必須指定分區鍵。

CREATE?TABLE?`test`.`partition_t6`(??  ??`id`?INT?UNSIGNED?NOT?NULL,  ??`username`?VARCHAR(30)?NOT?NULL,  ??`email`?VARCHAR(30)?NOT?NULL,  ??`birth_date`?DATE?NOT?NULL  )?ENGINE=MYISAM  PARTITION?BY?LINEAR?KEY(email)  PARTITIONS?5;

columns 與子分區

1.columns分區

columns 包括range columns與list columns 支持非整型的分區鍵。columns分區支持多列分區。

CREATE?TABLE?`test`.`partition_t7`(??  ??`a`?INT?UNSIGNED?NOT?NULL,  ??`b`?INT?UNSIGNED?NOT?NULL  )  PARTITION?BY?RANGE?COLUMNS(a,b)(  ????PARTITION?p0?VALUES?LESS?THAN?(0,10),  ????PARTITION?p1?VALUES?LESS?THAN?(10,10),  ????PARTITION?p2?VALUES?LESS?THAN?(10,20),  ????PARTITION?p3?VALUES?LESS?THAN?(10,35),  ????PARTITION?p4?VALUES?LESS?THAN?(10,MAXVALUE),  ????PARTITION?p5?VALUES?LESS?THAN?(MAXVALUE,MAXVALUE)  );

判斷依據：（a

2.子分區

子分區是分區表中對每一個分區的再次分割，又被稱為復合分區。MySQL從MySQL5.1開始支持對通過range和list的表再進行子分區，子分區即可以hash分區，也可以使用key分區。子分區適合保存非常大量的數據記錄。

CREATE?TABLE?partition_t8(id?INT,purchased?DATE)  PARTITION?BY?RANGE(YEAR(purchased))  SUBPARTITION?BY?HASH(TO_DAYS(purchased))  SUBPARTITIONS?2(  ????PARTITION?p0?VALUES?LESS?THAN?(1990),  ????PARTITION?p1?VALUES?LESS?THAN?(2000),  ????PARTITION?p2?VALUES?LESS?THAN?MAXVALUE  );

詳細介紹mysql中的分區

mysql子分區

分區管理

MySQL5.1提供添加、刪除、重定義、合并、拆分分區命令。

1.range或list分區

#刪除分區  alter?table?partition_t8?drop?partition?p2;  #添加一個分區  alter?table?partition_t8?add?partition(  ????partition?p4?values?less?than?(2030)  ????)  #重定義一個分區  alter?table?partition_t8?reorganize?partition?p3?into(  ????partition?p2?values?less?than?(2005),  ????partition?p3?values?less?than?(2015)  );

  • 只能從range分區列表最大端增加分區。

  • 增加list分區，不能添加一個包含現有分區值列表中的任意值分區，也就是說對一個固定的分區鍵值，必須指定并且只能指定一個唯一的分區。

  • 重新定義range分區，只能夠重新定義相鄰的分區，同時重新定義的分區區間必須和原分區區間覆蓋相同的區間。

2.hash或key分區

#減少分區數,(如將分區數減少到2)  alter?table?partition_t8?coalesce?partition?2;  #增加分區數(如：為分區數增加了8)  alter?table?partiton_t8?add?partition?partitions?8;

  • coalesce不能用來增加分區數量。

【相關推薦】

1.?mysql

2.?mysql

3.mysql

? 版權聲明
THE END
數據庫
# 區別
喜歡就支持一下吧
點贊12 分享