mysql分表情境分析與簡單分表操作

標籤：

為什麼要分表

首先要知道什麼情況下,才需要分表個人覺得單表記錄條數達到百萬到千萬層級時就要使用分表了,分表的目的就在於此，減小資料庫的負擔，縮短查詢時間.

表分割有兩種方式：　　

1水平分割：根據一列或多列資料的值把資料行放到兩個獨立的表中。

水平分割通常在下面的情況下使用:

表很大，分割後可以降低在查詢時需要讀的資料和索引的頁數，同時也降低了索引的層數，提高查詢速度。

表中的資料本來就有獨立性，例如表中分別記錄各個地區的資料或不同時期的資料，特別是有些資料常用，而另外一些資料不常用。

需要把資料存放到多個介質上。

水平分割會給應用增加複雜度，它通常在查詢時需要多個表名，查詢所有資料需要union操作。在許多資料庫應用中，這種複雜性會超過它帶來的優點，因為只要索引關鍵字不大，則在索引用於查詢時，表中增加兩到三倍資料量，查詢時也就增加讀一個索引層的磁碟次數。　　

2垂直分割：把主碼和一些列放到一個表，然後把主碼和另外的列放到另一個表中。

如果一個表中某些列常用，而另外一些列不常用，則可以採用垂直分割，另外垂直分割可以使得資料行變小，一個資料頁就能存放更多的資料，在查詢時就會減少I/O次數。 其缺點是需要管理冗餘列，查詢所有資料需要join操作。　 

情境案例:

部落格系統

垂直分割:

文章標題，作者，分類，建立時間等，是變化頻率慢，查詢次數多，而且最好有很好的即時性的資料，我們把它叫做冷資料。

而部落格的瀏覽量，回複數等，類似的統計資訊，或者別的變化頻率比較高的資料，我們把它叫做活躍資料。

所以，在進行資料庫結構設計的時候，就應該考慮分表，首先是縱向分表的處理。

這樣縱向分表後：

首先儲存引擎的使用不同，冷資料使用MyIsam 可以有更好的查詢資料。活躍資料，可以使用Innodb ,可以有更好的更新速度。

其次，對冷資料進行更多的從庫配置，因為更多的操作是查詢，這樣來加快查詢速度。對熱資料，可以相對有更多的主庫的橫向分表處理。

其實，對於一些特殊的活躍資料，也可以考慮使用memcache ,redis之類的緩衝，等累計到一定量再去更新資料庫.

水平分割:

當部落格的量達到很大時候，就應該採取橫向分割來降低每個單表的壓力，來提升效能。

例如部落格的冷資料表，假如分為100個表，當同時有100萬個使用者在瀏覽時，如果是單表的話，會進行100萬次請求，而現在分表後，就可能是每個表進行1萬個資料的請求（因為，不可能絕對的平均，只是假設），這樣壓力就降低了很多很多。

使用Merge儲存引擎展示水平分表執行個體：

查看mysql的儲存引擎

mysql> show engines  \G;

現實情境類比

第一步: 建立表member

DROP table IF EXISTS member;

create table member(

    id bigint auto_increment primary key,

    name varchar(20),

    sex tinyint not null default ‘0‘

)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; 

第二步:建立預存程序,插入百萬資料

#如果存在已定義的預存程序inserts,刪除掉

drop procedure IF EXISTS inserts;

#自訂結束符

delimiter //

#建立預存程序

create procedure inserts()

begin

    DECLARE i int;

    set i = 1;

        WHILE(i <= 10) DO  

               insert into member(name,sex) values(concat(‘name‘,i),i%2);

             SET i = i+1;

           END WHILE;

end;

#使用自訂結束符結束預存程序定義

//

#還原結束符為;

delimiter ;

#調用預存程序

call inserts();

MySQL的文法預設使用分號";"作為一條SQL語句結束的標誌.可以使用delimiter命令將其修改成其他符號，如："delimiter //" 表示以//作為提交符號.

為了示範分表,所以執行個體中插入10條資料類比.

第三步:建立分表

#分表1#

DROP table IF EXISTS tb_member1;

create table tb_member1(

    id bigint primary key auto_increment ,

    name varchar(20),

    sex tinyint not null default ‘0‘

)ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  

#分表2#

DROP table IF EXISTS tb_member2;

#複製表1

create table tb_member2 like tb_member1;

第四步:建立主表,這裡主表的定義與要分的目標表有不同

#主表#

DROP table IF EXISTS tb_member;

create table tb_member(

    id bigint auto_increment ,

    name varchar(20),

    sex tinyint not null default ‘0‘,

    INDEX(id)

)ENGINE=MERGE UNION=(tb_member1,tb_member2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ; 

查詢tb_member表的索引資訊

mysql> show index from tb_member \G;

第五步:將目標表資料分到兩個分表中去

INSERT INTO tb_member1(tb_member1.id,tb_member1.name,tb_member1.sex) 

    SELECT member.id,member.name,member.sex

    FROM member where member.id%2=0 ;

INSERT INTO tb_member2(tb_member2.id,tb_member2.name,tb_member2.sex)

    SELECT member.id,member.name,member.sex

    FROM member where member.id%2=1 ;

當然實際情境根據需要進行唯一標識操作,取hash啊什麼的等等,這裡只使用簡單去求模分表.

第六步: 查看分表資料

 

第七步: 查看總表資料

 

這樣就把表member中的資料分開了, 分成的表組為 tb_member為主表,tb_member1與tb_member2為分表.分表後，資料都是存放在分表裡，總表只是一個外殼，存取資料發生在一個一個的分表裡面。

對於merge表，需要注意的是  

1. 每個子表的結構必須一致，主表和子表的結構需要一致，

2. 每個子表的索引在merge表中都會存在，所以在merge表中不能根據該索引進行唯一性檢索。

3. 子表需要是MyISAM引擎

4. REPLACE在merge表中不會工作

5. AUTO_INCREMENT 不會按照你所期望的方式工作

建立Mysql Merge表的參數 INSERT_METHOD有幾個參數 。

  LAST  如果你執行insert 指令來操作merge表時，插入操作會把資料添加到最後一個子表中。

  FIRST  同理，執行插入資料時會把資料添加到第一個子表中。

mysql分表情境分析與簡單分表操作