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

創(chuàng)新互聯(lián)建站長(zhǎng)期為千余家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開(kāi)放共贏平臺(tái)，與合作伙伴共同營(yíng)造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為汕頭企業(yè)提供專(zhuān)業(yè)的網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計(jì)，汕頭網(wǎng)站改版等技術(shù)服務(wù)。擁有10余年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開(kāi)發(fā)。

一、什么是分表：

    分表是將一個(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á)式，它可以僅僅是數(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)。

                            如有不妥，歡迎指正！

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無(wú)理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專(zhuān)為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場(chǎng)景需求。

網(wǎng)站標(biāo)題：MySQL的分表和分區(qū)介紹-創(chuàng)新互聯(lián)
文章源于：http://m.2m8n56k.cn/article28/dccjjp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站制作、服務(wù)器托管、小程序開(kāi)發(fā)、商城網(wǎng)站、做網(wǎng)站、面包屑導(dǎo)航

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：[email protected]。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)