MySQL的分表和分區(qū)介紹

    在日常開(kāi)發(fā)或維護(hù)中經(jīng)常會(huì)遇到大表的情況，所謂的大表是指存儲(chǔ)了百萬(wàn)級(jí)乃至千萬(wàn)級(jí)條記錄的表。這樣的表過(guò)于龐大，導(dǎo)致數(shù)據(jù)庫(kù)在查詢(xún)和插入的時(shí)候耗時(shí)太長(zhǎng)，性能低下，如果涉及聯(lián)合查詢(xún)的情況，性能會(huì)更加糟糕。分表和表分區(qū)的目的就是減少數(shù)據(jù)庫(kù)的負(fù)擔(dān)，提高數(shù)據(jù)庫(kù)的效率，通常點(diǎn)來(lái)講就是提高表的增刪改查效率。

一、什么是分表：

    分表是將一個(gè)大表按照一定的規(guī)則分解成多張具有獨(dú)立存儲(chǔ)空間的實(shí)體表，我們可以稱(chēng)為子表，每個(gè)表都對(duì)應(yīng)三個(gè)文件，MYD數(shù)據(jù)文件，.MYI索引文件，.frm表結(jié)構(gòu)文件。這些子表可以分布在同一塊磁盤(pán)上，也可以在不同的機(jī)器上。

1、根據(jù)分表技術(shù)對(duì)海量數(shù)據(jù)的優(yōu)化方式目前有2種方法：

    1、垂直分割：把一個(gè)數(shù)據(jù)量很大的表，根據(jù)某個(gè)字段的屬性或使用頻繁程度分類(lèi)拆分為多個(gè)表，或者把一個(gè)業(yè)務(wù)系統(tǒng)的庫(kù)分到不同的實(shí)例上。

    2、水平分割：根據(jù)一列或者多列的值把數(shù)據(jù)行放到多個(gè)獨(dú)立的表里，水平分表方式可以通過(guò)多個(gè)低配置主機(jī)整合起來(lái)，實(shí)現(xiàn)高性能。

    3、兩者的優(yōu)缺點(diǎn)：

    水平優(yōu)點(diǎn)：拆分規(guī)則抽象好，JION操作基本可以數(shù)據(jù)庫(kù)做，不存在單表大數(shù)據(jù)、高并發(fā)的性能瓶頸，應(yīng)用端改造較少，提高系統(tǒng)的穩(wěn)定性和負(fù)載能力

    缺點(diǎn)：分片事務(wù)一致性難以解決，在MyCAT2.0之前MySQL5.7之前，還是數(shù)據(jù)弱XA。數(shù)據(jù)多次擴(kuò)展難度維護(hù)量大，夸庫(kù)JOIN性能差

    垂直優(yōu)點(diǎn)：拆分后業(yè)務(wù)清晰，拆分規(guī)則明確，系統(tǒng)之間整合或者拓展容易，數(shù)據(jù)庫(kù)維護(hù)簡(jiǎn)單

    缺點(diǎn)：部分業(yè)務(wù)無(wú)法使用JOIN，只能通過(guò)接口方式解決，提供系統(tǒng)能夠復(fù)雜度，受每種業(yè)務(wù)不同的限制存在性能瓶頸，不容易數(shù)據(jù)擴(kuò)展跟性能提高。

    事務(wù)處理復(fù)雜，垂直切分后按照業(yè)務(wù)的分類(lèi)將表分散到不同的庫(kù)，會(huì)導(dǎo)致有些業(yè)務(wù)表過(guò)于龐大，存在單庫(kù)讀寫(xiě)與存儲(chǔ)瓶頸。

二、什么是分區(qū)

    分區(qū)就是把一張表的數(shù)據(jù)分成N多個(gè)區(qū)域，分區(qū)后，表面上還是一張表，但數(shù)據(jù)散列到多個(gè)位置根據(jù)數(shù)據(jù)量的大小，結(jié)合實(shí)際業(yè)務(wù)

1、分區(qū)方式有：

    a、range分區(qū)：主要用于時(shí)間列分區(qū)、值范圍，行數(shù)據(jù)基于一個(gè)給定連續(xù)分區(qū)的列值放入分區(qū)。如銷(xiāo)售類(lèi)的表，可以根據(jù)年來(lái)分區(qū)存放銷(xiāo)售記錄

    b、list分區(qū)：面向離散的值,分區(qū)要指定的值，當(dāng)插入指定的數(shù)據(jù)到指定分區(qū)表去,如指定某些值在特定分區(qū)里。

    c、key分區(qū)：類(lèi)似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。

    d、hash分區(qū)：基于用戶定義的表達(dá)式的返回值來(lái)進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。這個(gè)函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。

三、分區(qū)實(shí)例：

創(chuàng)建redundant格式

      如果表中存在主鍵或是唯一索引時(shí)，分區(qū)列必須是唯一索引的一個(gè)組成部分
唯一索引     
 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;
主鍵和索引同時(shí)存在：
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值，分區(qū)列只要是唯一索引的一個(gè)組成部分，不需要整個(gè)唯一索引列都是分區(qū)列
create table t223332(
col1 int null,
col2 date null,
col3 int null,
col4 int null)
partition by hash(col3)
partitions 4;
    沒(méi)有主鍵或唯一索引，可以指定任何一個(gè)列為分區(qū)列
create table t223332(
col1 int null,
col2 date null,
col3 int null,
col4 int null,
key(col4))
partition by hash(col3)
partitions 4;
    rang 分區(qū)：主要用于時(shí)間列分區(qū)，如銷(xiāo)售類(lèi)的表，可以根據(jù)年來(lái)分區(qū)存放銷(xiāo)售記錄
    定義：行數(shù)據(jù)基于一個(gè)給定連續(xù)分區(qū)的列值放入分區(qū)，
       id 是主鍵
    create table t3(
    id int)engine=innodb
    partition by range(id)(
    partition p0 values less than (10),
    partition p1 values less than (20)
 );
    查看數(shù)據(jù)文件
    t3.frm t.par
    insert into t select 9;
    insert into t select 10;
    insert into t select 15;
    查看分區(qū)狀態(tài)
   use information_schema
    select * from PARITIONS where table_schema=''test and table_name='t3'\G;
    partition_method代表分區(qū)類(lèi)型    
  當(dāng)不滿足分區(qū)條件的時(shí)候報(bào)錯(cuò)
  table has no partition for value 40
 alter table t add partition(partition p2 values less than maxalue);  
  主要用于時(shí)間列分區(qū)，如銷(xiāo)售類(lèi)的表，可以根據(jù)年來(lái)分區(qū)存放銷(xiāo)售記錄(year(date))取年的時(shí)間
    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 分區(qū)：面向離散的值,分區(qū)要指定的值，當(dāng)插入指定的數(shù)據(jù)到指定分區(qū)表去，
    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分區(qū)沒(méi)有類(lèi)似如“VALUES LESS THAN MAXVALUE”這樣的包含其他值在內(nèi)的定義。將要匹配的任何值都必須在值列表中找到。
    LIST分區(qū)除了能和RANGE分區(qū)結(jié)合起來(lái)生成一個(gè)復(fù)合的子分區(qū)，與HASH和KEY分區(qū)結(jié)合起來(lái)生成復(fù)合的子分區(qū)也是可能的。
      
      
  注意：innodb myisam區(qū)別
  在用insert插入多行數(shù)據(jù)的過(guò)程中遇到分區(qū)為定義的值，myisam、innodb存儲(chǔ)引擎的處理完全不同，
  myisam 一條不成功，之前的成功值，會(huì)進(jìn)入表中
  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 19
select * 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 分區(qū)：根據(jù)用戶的表達(dá)式的返回值來(lái)進(jìn)行分區(qū)，返回值不能是負(fù)數(shù)
      要在create table 語(yǔ)句上添加一個(gè)partition by hash(expr)句子，其中expr是一個(gè)返回一個(gè)整數(shù)的表達(dá)式，它可以?xún)H僅是數(shù)字段類(lèi)型為mysql整型的列名字
      后面在添加一個(gè)partitions num子句，num是一個(gè)非負(fù)數(shù)
      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分區(qū)
      區(qū)別于其他分區(qū)，分區(qū)條件必須是整型，如果不是整型也應(yīng)該需要通過(guò)函數(shù)將其轉(zhuǎn)化為整型 columns分時(shí)是rang list分區(qū)的進(jìn)化
      支持整型類(lèi)型
      日期類(lèi)型date datetime其余的日期類(lèi)型不予支持
      字符串類(lèi)型 char varcha binary  varbinary ,blok和text類(lèi)型的不予支持
      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)
      );
      
      
     子分區(qū)：MYSQL數(shù)據(jù)庫(kù)允許在rang和list的分區(qū)上再進(jìn)行hask或者key子分區(qū)，
     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 date
partition 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 s4
subpartition s5
)
)
      
      
      
每個(gè)子分區(qū)必須包含分區(qū)的名字。  
子分區(qū)的名字唯一的。


分區(qū)中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值 放最左邊的。

總結(jié)：了解基礎(chǔ)的分表的原則、方法，實(shí)際還需要根據(jù)業(yè)務(wù)結(jié)合，達(dá)到業(yè)務(wù)架構(gòu)最優(yōu)。

                            如有不妥，歡迎指正！