《Mysql必讀MySQL中表分區技術詳細解析》要點：
本文介紹了Mysql必讀MySQL中表分區技術詳細解析，希望對您有用。如果有疑問，可以聯系我們。
MYSQL學習MySQL 分區技術(是mysql 5.1以版本后開始用->是甲骨文mysql技術團隊維護人員以插件形式插入到mysql里面的技術)
MYSQL學習1、概述
MYSQL學習數據庫單表到達必定量后,性能會有衰減,像mysql\sql server等猶為明顯,所以需要把這些數據進行分區處理.同時有時候可能出現數據剝離什么的,分區表就更有用處了!
MYSQL學習MySQL 5.1 中新增的分區(Partition)功能就開始增加,優勢也越來越明顯了：
MYSQL學習2、分區技術支持
MYSQL學習在5.6之前,使用這個參數查看當將配置是否支持分區：
MYSQL學習
mysql> SHOW VARIABLES LIKE '%partition%';
+-----------------------+-------+
|Variable_name | Value |
+-----------------------+-------+
| have_partition_engine | YES |
+-----------------------+-------+
MYSQL學習如果是yes表現你當前的配置支持分區. 在5.6及以采用后,則采用如下方式進行查看：
MYSQL學習
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
..................................................................................
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+----------------------------+----------+--------------------+---------+---------+
MYSQL學習42 rows in set (0.00 sec) 最后一行,可以看到partition是ACTIVE的,表現支持分區.
MYSQL學習3、分區類型及舉例
MYSQL學習3.1范圍分區
MYSQL學習RANGE 分區：基于屬于一個給定連續區間的列值,把多行分配給分區.如時間,連續的常量值等 --按年分區
MYSQL學習
mysql> use mytest;
Database changed
mysql> create table range_p(
-> perid int(11),
-> pername char(12) not null,
-> monsalary DECIMAL(10,2),
-> credate datetime
-> ) partition by range(year(credate))(
-> partition p2011 values less than (2011),
-> partition p2012 values less than (2012),
-> partition p2013 values less than (2013),
-> partition p2014 values less than (2014),
-> partition p2015 values less than maxvalue
-> );
Query OK, 0 rows affected (0.12 sec)
MYSQL學習3.2列舉分區
MYSQL學習LIST 分區：類似于按RANGE分區,區別在于LIST分區是基于列值匹配一個離散值集合中的某個值來進行選擇.好比說類似性別(1,2)等屬性值.
MYSQL學習
mysql> create table list_p( ? -> perid int(11), ? -> pername char(12) not null,? -> sex int(1) not null,? -> monsalary DECIMAL(10,2),? -> credate datetime ? -> ) partition by list(sex) (? -> partition psex1 values in(1),? -> partition psex2 values in(2));?Query OK, 0 rows affected (0.06 sec)
MYSQL學習注意,list只能是數字,使用字符會報錯ERROR 1697 (HY000): VALUES value for partition 'psex1' must have type INT.
MYSQL學習3.3離散分區
MYSQL學習HASH分區：基于用戶定義的表達式的返回值來進行選擇的分區,該表達式使用將要插入到表中的這些行的列值進行計算.這個函數可以包>含MySQL中有效的、產生非負整數值的任何表達式.
MYSQL學習--以int字段hash分區
MYSQL學習
create table hash_p(
perid int(11),
pername char(12) not null,
sex int(1) not null,
monsalary DECIMAL(10,2),
credate datetime
) partition by hash (perid)
partitions 8;
MYSQL學習--以時間函數hash分區
MYSQL學習
mysql> create table hash_p(
-> perid int(11),
-> pername char(12) not null,
-> sex int(1) not null,
-> monsalary DECIMAL(10,2),
-> credate datetime
-> ) partition by hash (year(credate))
-> partitions 8;
Query OK, 0 rows affected (0.11 sec)
MYSQL學習3.4鍵值分區
MYSQL學習KEY分區：類似于按HASH分區,區別在于KEY分區只支持計算一列或多列,且MySQL服務器提供其自身的哈希函數.必須有一列或多列包含>整數值. 其分區辦法與hash很相似：
MYSQL學習
mysql> create table key_p(
-> perid int(11),
-> pername char(12) not null,
-> sex int(1) not null,
-> monsalary DECIMAL(10,2),
-> credate datetime
-> ) partition by key (perid)
-> partitions 8;
Query OK, 0 rows affected (0.12 sec)
MYSQL學習3.5其它說明
MYSQL學習mysql-5.5開始支持COLUMNS分區,可視為RANGE和LIST分區的進化,COLUMNS分區可以直接使用非整形數據進行分區.COLUMNS分區支持以下數據類型： 所有整形,如INT SMALLINT TINYINT BIGINT.FLOAT和DECIMAL則不支持. 日期類型,如DATE和DATETIME.別的日期類型不支持. 字符串類型,如CHAR、VARCHAR、BINARY和VARBINARY.BLOB和TEXT類型不支持. COLUMNS可以使用多個列進行分區.
MYSQL學習
mysql> create table range_p(
-> perid int(11),
-> pername char(12) not null,
-> monsalary DECIMAL(10,2),
-> credate datetime
-> ) PARTITION BY RANGE COLUMNS (credate)(
-> partition p20151 values less than ('2015-04-01'),
-> partition p20152 values less than ('2015-07-01'),
-> partition p20153 values less than ('2015-10-01'),
-> partition p20154 values less than ('2016-01-01'),
-> partition p20161 values less than ('2016-04-01'),
-> partition partlog values less than maxvalue
-> );
Query OK, 0 rows affected (0.12 sec)
MYSQL學習總結：
MYSQL學習分區表是在MySQL5.1中新增的的功能,截止到MySQL5.1.22-rc,分區技術并不很成熟,很多分區的維護和管理功能未實現.如,分區內數據存儲空間的回收、分區的修復、分區的優化等,MySQL的分區可以用在可以按分區刪除的表中,且對數據庫的修改操作不大,且頻繁依照分區字段進行查詢的表中(如惡意代碼中的統計表按天分區,經常依照時間進行查詢、分組等,且可以按天刪除分區).此外,由于MySQL無全局索引只有分區索引,當一張有2個唯一索引[z5] 的時候,不能將此表分區,分區列中必須包含主鍵.否則MySQL會報錯.
MYSQL學習總之,MySQL對于分區的限制很多,且個人認為hash和key的分區實際意義不是太大.
MYSQL學習分區引入了一種新的優化查詢的方式(當然,也有相應的缺點).優化器可以使用分區函數修整分區,或者把分區從查詢中完全移除掉.它通過推斷是否可以在特定的分區上找到數據來達成這種優化.因此在最好的情況下,修整可以讓查詢拜訪更少的數據.重要的是要在WHERE子句中定義分區鍵,即使它看上去像是多余的.通過分區鍵,優化器就可以去掉不用的分區,否則的話,執行引擎就會像合并表那樣拜訪表的所有分區,這在大表上會非常慢.分區數據比非分區數據更好維護,并且可以通過刪除分區來移除老的數據.分區數據可以被分布到不同的物理位置,這樣服務器可以更有效地使用多個硬盤驅動器.
MYSQL學習[z1]分區函數的返回值必須是整數,新增分區的分區函數返回值應大于任何一個現有分區的分區函數的返回值.
?[z2]對于有主鍵的表錯誤提示：#1503
?A PRIMARY KEY MUST INCLUDE ALL COLUMNS INTHE TABLE'S PARTITIONING FUNCTION,沒有主鍵的則無此約束
?[z3]注意：對于通過RANGE分區的表,只可以使用ADD PARTITION添加新的分區到分區列表的高端.即不能添加比這個分區的范圍小的分區.
?[z4] 對于依照RANGE分區的表,只能重新組織相鄰的分區；不能跳過RANGE分區.不能使用REORGANIZEPARTITION來改變表的分區類型；也就是說,例如,不能把RANGE分區變為HASH分區,反之亦然.也不能使用該命令來改變分區表達式或列.
?[z5]注意主鍵和唯一索引的區別
MYSQL學習官方材料：https://dev.mysql.com/doc/refman/5.5/en/partitioning.html
《Mysql必讀MySQL中表分區技術詳細解析》是否對您有啟發，歡迎查看更多與《Mysql必讀MySQL中表分區技術詳細解析》相關教程，學精學透。維易PHP學院為您提供精彩教程。
