MySQL的分表和分區介紹

標籤：list   key   hash   range   

    在日常開發或維護中經常會遇到大表的情況，所謂的大表是指儲存了百萬級乃至千萬級條記錄的表。這樣的表過於龐大，導致資料庫在查詢和插入的時候耗時太長，效能低下，如果涉及聯集查詢的情況，效能會更加糟糕。分表和表分區的目的就是減少資料庫的負擔，提高資料庫的效率，通常點來講就是提高表的增刪改查效率。

一、什麼是分表：

    分表是將一個大表按照一定的規則分解成多張具有隔離儲存區 (Isolated Storage)空間的實體表，我們可以稱為子表，每個表都對應三個檔案，MYD資料檔案，.MYI索引檔案，.frm表結構檔案。這些子表可以分布在同一塊磁碟上，也可以在不同的機器上。

1、根據分表技術對海量資料的最佳化方式目前有2種方法：

    1、垂直分割：把一個資料量很大的表，根據某個欄位的屬性或使用頻繁程度分類拆分為多個表，一般電商資料庫：使用者表、訂單表、支付系統等

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M01/8B/8A/wKioL1hQ7jmDPCHCAANG5mJz12g690.png-wh_500x0-wm_3-wmp_4-s_123021846.png" title="111.png" alt="wKioL1hQ7jmDPCHCAANG5mJz12g690.png-wh_50" />

    2、水平分割：根據一列或者多列的值把資料行放到多個獨立的表裡，水平分表方式可以通過多個低配置主機整合起來，實現高效能。

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/8B/8E/wKiom1hQ7kqDBxVRAALNDOLRIqc744.png-wh_500x0-wm_3-wmp_4-s_468431345.png" title="2222.png" alt="wKiom1hQ7kqDBxVRAALNDOLRIqc744.png-wh_50" />

    3、兩者的優缺點：

    水平優點：拆分規則抽象好，JION操作基本可以資料庫做，不存在單表大資料、高並發的效能瓶頸，應用端改造較少，提高系統的穩定性和負載能力

    缺點：分區事務一致性難以解決，在MyCAT2.0之前MySQL5.7之前，還是資料弱XA。資料多次擴充難度維護量大，誇庫JOIN效能差

    垂直優點：拆分後業務清晰，拆分規則明確，系統之間整合或者拓展容易，資料庫維護簡單

    缺點：部分業務無法使用JOIN，只能通過介面方式解決，提供系統能夠複雜度，受每種業務不同的限制存在效能瓶頸，不容易資料擴充跟效能提高。

    交易處理複雜，垂直切分後按照業務的分類將表分散到不同的庫，會導致有些業務表過於龐大，存在單庫讀寫與儲存瓶頸。

二、什麼是分區

    分區就是把一張表的資料分成N多個地區，分區後，表面上還是一張表，但資料散列到多個位置根據資料量的大小，結合實際業務

1、分區方式有：

    a、range分區：主要用於時間列分區、值範圍，行資料基於一個給定連續分區的列值放入分區。如銷售類的表，可以根據年來分區存放銷售記錄

    b、list分區：面向離散的值,分區要指定的值，當插入指定的資料到指定分區表去,如指定某些值在特定分區裡。

    c、key分區：類似於按HASH分區，區別在於KEY分區只支援計算一列或多列，且MySQL伺服器提供其自身的雜湊函數。必須有一列或多列包含整數值。

    d、hash分區：基於使用者定義的運算式的傳回值來進行選擇的分區，該運算式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL 中有效、產生非負整數值的任何錶達式。

三、分區執行個體：

建立redundant格式

      如果表中存在主鍵或是唯一索引時，分區列必須是唯一索引的一個組成部分唯一索引      create table t11(      col1 int not null,      col2 date not null,      col3 int not null,      col4 int not null,      unique key (col1,col2))      partition by hash(col1)      partitions 4; 雜湊     create table t121(      col1 int not null,      col2 date not null,      col3 int not null,      col4 int not null,      unique key (col1,col2))      partition by hash(year(col2))      partitions 4;主鍵create table t31(      col1 int not null,      col2 date not null,      col3 int not null,      col4 int not null,      primary key (col1,col2))      partition by hash(col1)      partitions 8;主鍵和索引同時存在：create table t41(      col1 int not null,      col2 date not null,      col3 int not null,      col4 int not null,      unique key(col4),      primary key (col1))      partition by hash(col1)      partitions 5;      唯一索引可以允許是null值，分區列只要是唯一索引的一個組成部分，不需要整個唯一索引列都是分區列create table t223332(col1 int null,col2 date null,col3 int null,col4 int null)partition by hash(col3)partitions 4;    沒有主鍵或唯一索引，可以指定任何一個列為分區列create table t223332(col1 int null,col2 date null,col3 int null,col4 int null,key(col4))partition by hash(col3)partitions 4;    rang 分區：主要用於時間列分區，如銷售類的表，可以根據年來分區存放銷售記錄    定義：行資料基於一個給定連續分區的列值放入分區，       id 是主鍵    create table t3(    id int)engine=innodb    partition by range(id)(    partition p0 values less than (10),    partition p1 values less than (20) );    查看資料檔案    t3.frm t.par    insert into t select 9;    insert into t select 10;    insert into t select 15;    查看分區狀態   use information_schema    select * from PARITIONS where table_schema=‘‘test and table_name=‘t3‘\G;    partition_method代表分區類型      當不滿足分區條件的時候報錯  table has no partition for value 40 alter table t add partition(partition p2 values less than maxalue);    主要用於時間列分區，如銷售類的表，可以根據年來分區存放銷售記錄(year(date))取年的時間    create table sales(    money int not null,date datetime)engine=innodb    partition by range (year(date))(    partition p2008 values less than (2009),     partition p2009 values less than (2010),       partition p2010 values less than (2011)       );       insert into sales select 100,‘2008-01-01‘;       insert into sales select 100.‘2008-02-01‘;       insert into sales select 100.‘2008-01-02‘;       insert into sales select 100,‘2009-03-01‘;       insert into sales select 100,‘2010-01-01‘;       list 分區：面向離散的值,分區要指定的值，當插入指定的資料到指定分區表去，    create table t_list (a int,b int)engine=innodb    partition by list(b)(partition p0 values in(1,3,5,7,9),    partition p1 values in (0,2,4,6,8));          insert into  t4 select 1, 3;      insert into  t4 select 1, 5;      insert into  t4 select 1, 8;      insert into  t4 select 1, 6;      table has no partition for values10      值得注意的是，LIST分區沒有類似如“VALUES LESS THAN MAXVALUE”這樣的包含其他值在內的定義。將要匹配的任何值都必須在值列表中找到。    LIST分區除了能和RANGE分區結合起來產生一個複合的子分區，與HASH和KEY分區結合起來產生複合的子分區也是可能的。              注意：innodb myisam區別  在用insert插入多行資料的過程中遇到分區為定義的值，myisam、innodb儲存引擎的處理完全不同，  myisam 一條不成功，之前的成功值，會進入表中  innodb只要一條不成功，所有都不成功create table t(a int,b int)engine=myisam partition by list(b)(partition p0 values in (1,3,5,7,9),partition p1 values in (0,2,4,6,8));insert into t values (1,2),(2,4),(6,19),(5,3);insert into t values (1,2),(2,4),(6,19),(5,3);ERROR 1526 (HY000): Table has no partition for value 19select * from t;+------+------+| a    | b    |+------+------+|    1 |    2 ||    2 |    4 |+------+------+2 rows in set (0.00 sec)create table tt(a int,b int)engine=innodb partition by list(b)(partition p0 values in (1,3,5,7,9),partition p1 values in (0,2,4,6,8));insert into tt values (1,2),(2,4),(6,19),(5,3);insert into tt values (1,2),(2,4),(6,19),(5,3);ERROR 1526 (HY000): Table has no partition for value 19 select * from tt;Empty set (0.00 sec)      hash 分區：根據使用者的運算式的傳回值來進行分區，傳回值不能是負數      要在create table 語句上添加一個partition by hash(expr)句子，其中expr是一個返回一個整數的運算式，它可以僅僅是數欄位類型為mysql整型的列名字      後面在添加一個partitions num子句，num是一個非負數      create table t_hash(a int,b date)engine=innodb      partition by hash(YEAR(b))      partitions 4;      insert into t_hash select 1,‘2010-04-01‘;           create table tt_hash(a int,b date)engine=innodb      partition by hash (a)      partitions 4;            #######################################      columns分區      區別於其他分區，分區條件必須是整型，如果不是整型也應該需要通過函數將其轉化為整型 columns分時是rang list分區的進化      支援整數型別      日期類型date datetime其餘的日期類型不予支援      字串類型 char varcha binary  varbinary ,blok和text類型的不予支援      create table tt_column_range(a int,b int)engine=innodb partition by range columns(a,b)(      partition p0 values less than (0,10),      partition p1 values less than (10,20),      partition p2 values less than (20,30),      partition p3 values less than (30,40),      partition p4 values less than (40,50)      );                 子分區：MYSQL資料庫允許在rang和list的分區上再進行hask或者key子分區，     create table ts(a int,b date)engine=innodb     partition by range(year(b))     subpartition by hash(to_days(b))     subpartitions 3(     partition p0 values less than (2013),            partition p0 values less than (2014),       partition p1 values less than (2015)  partition p2 values less than maxvalue); create table ts(a int,b datepartition by range(year(b))subpartition by hash(to_days(b))(partition p0 values less than(2014)(subpartition s0,subpartition s1)partition p1 values less than (2015)(subpartition s2,subpartition s3)partition p2 values less than maxvalue(subpartition s4subpartition s5))                  每個子分區必須包含分區的名字。  子分區的名字唯一的。分區中null值create table t3(    id int)engine=innodb    partition by range(id)(    partition p0 values less than (10),    partition p1 values less than (20); )； null值 放最左邊的。

本文出自 “DBSpace” 部落格，請務必保留此出處http://dbspace.blog.51cto.com/6873717/1882681

MySQL的分表和分區介紹