博文大綱：

創(chuàng)新互聯(lián)專注于青島網(wǎng)站建設(shè)服務(wù)及定制，我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗(yàn)。 熱誠(chéng)為您提供青島營(yíng)銷型網(wǎng)站建設(shè)，青島網(wǎng)站制作、青島網(wǎng)頁(yè)設(shè)計(jì)、青島網(wǎng)站官網(wǎng)定制、微信小程序開發(fā)服務(wù)，打造青島網(wǎng)絡(luò)公司原創(chuàng)品牌,更為您提供青島網(wǎng)站排名全網(wǎng)營(yíng)銷落地服務(wù)。

• 1、為什么要分表？
• 2、MySQL分表
• 3、利用merge存儲(chǔ)引擎實(shí)現(xiàn)分表
• 4、MySQL分區(qū)

1、為什么要分表？

數(shù)據(jù)庫(kù)數(shù)據(jù)越來(lái)越大，隨之而來(lái)的是單個(gè)表中數(shù)據(jù)太多。以至于查詢速度變慢，而且由于表的鎖機(jī)制導(dǎo)致應(yīng)用操作也搜到嚴(yán)重影響，出現(xiàn)了數(shù)據(jù)庫(kù)性能瓶頸。
mysql中有一種機(jī)制是表鎖定和行鎖定，是為了保證數(shù)據(jù)的完整性。表鎖定表示你們都不能對(duì)這張表進(jìn)行操作，必須等我對(duì)表操作完才行。行鎖定也一樣，別的sql必須等我對(duì)這條數(shù)據(jù)操作完了，才能對(duì)這條數(shù)據(jù)進(jìn)行操作。當(dāng)出現(xiàn)這種情況時(shí)，我們可以考慮分表或分區(qū)。

2、MySQL分表

分表是將一個(gè)大表按照一定的規(guī)則分解成多張具有獨(dú)立存儲(chǔ)空間的實(shí)體表，每個(gè)表都對(duì)應(yīng)三個(gè)文件，MYD數(shù)據(jù)文件，.MYI索引文件，.frm表結(jié)構(gòu)文件。這些表可以分布在同一塊磁盤上，也可以在不同的機(jī)器上。app讀寫的時(shí)候根據(jù)事先定義好的規(guī)則得到對(duì)應(yīng)的表名，然后去操作它。
將單個(gè)數(shù)據(jù)庫(kù)表進(jìn)行拆分，拆分成多個(gè)數(shù)據(jù)表，然后用戶訪問(wèn)的時(shí)候，根據(jù)一定的算法（如用hash的方式，也可以用求余（取模）的方式），讓用戶訪問(wèn)不同的表，這樣數(shù)據(jù)分散到多個(gè)數(shù)據(jù)表中，減少了單個(gè)數(shù)據(jù)表的訪問(wèn)壓力。提升了數(shù)據(jù)庫(kù)訪問(wèn)性能。分表的目的就在于此，減小數(shù)據(jù)庫(kù)的負(fù)擔(dān)，縮短查詢時(shí)間。

Mysql分表分為垂直切分和水平切分，具體區(qū)別如下：
垂直切分是指數(shù)據(jù)表列的拆分，把一張列比較多的表拆分為多張表 通常我們按以下原則進(jìn)行垂直拆分: 把不常用的字段單獨(dú)放在一張表; 把text，blob（binary large object，二進(jìn)制大對(duì)象）等大字段拆分出來(lái)放在附表中;
經(jīng)常組合查詢的列放在一張表中; 垂直拆分更多時(shí)候就應(yīng)該在數(shù)據(jù)表設(shè)計(jì)之初就執(zhí)行的步驟，然后查詢的時(shí)候用join關(guān)鍵起來(lái)即可。

水平拆分是指數(shù)據(jù)表行的拆分，把一張的表的數(shù)據(jù)拆成多張表來(lái)存放。 水平拆分原則，通常情況下，我們使用hash、取模等方式來(lái)進(jìn)行表的拆分 比如一張有400W的用戶表users，為提高其查詢效率我們把其分成4張表users1，users2，users3，users4 通過(guò)用ID取模的方法把數(shù)據(jù)分散到四張表內(nèi)Id%4= [0,1,2,3] 然后查詢,更新,刪除也是通過(guò)取模的方法來(lái)查詢 部分業(yè)務(wù)邏輯也可以通過(guò)地區(qū)，年份等字段來(lái)進(jìn)行歸檔拆分; 進(jìn)行拆分后的表，這時(shí)我們就要約束用戶查詢行為。比如我們是按年來(lái)進(jìn)行拆分的,這個(gè)時(shí)候在頁(yè)面設(shè)計(jì)上就約束用戶必須要先選擇年,然后才能進(jìn)行查詢。

3、利用merge存儲(chǔ)引擎實(shí)現(xiàn)分表

注：只有myisam引擎的原表才可以利用merge存儲(chǔ)引擎實(shí)現(xiàn)分表。

merge分表，分為主表和子表，主表類似于一個(gè)殼子，邏輯上封裝了子表，實(shí)際上數(shù)據(jù)都是存儲(chǔ)在子表中的。 我們可以通過(guò)主表插入和查詢數(shù)據(jù)，如果清楚分表規(guī)律，也可以直接操作子表。

舉個(gè)栗子：

1）創(chuàng)建一個(gè)完整表

mysql> create database test;
mysql> use test;
mysql> create table member(
    -> id bigint auto_increment primary key,
    -> name varchar(20),
    -> sex tinyint not null default '0'
    -> )engine=myisam default charset=utf8 auto_increment=1;

mysql> insert into member(name,sex) values('tom1',1);
mysql> insert into member(name,sex) select name,sex from member;

mysql> select count(*) from member;       
+----------+
| count(*) |
+----------+
|     4096 |
+----------+
1 row in set (0.00 sec)

2）對(duì)上面完整的表進(jìn)行分表

分表注意事項(xiàng)：

• 子表和主表的字段定義需要一致，包括數(shù)據(jù)類型，數(shù)據(jù)長(zhǎng)度等；
• 當(dāng)分表完成后，所有的操作（增刪改查）需要對(duì)主表進(jìn)行，雖然主表并不存放實(shí)際的數(shù)據(jù)。

mysql> create table tb_member1 like member;
mysql> create table tb_member2 like member;

mysql> create table tb_member(
    -> id bigint auto_increment primary key,
    -> name varchar(20),
    -> sex tinyint not null default '0'
    -> )engine=merge union=(tb_member1,tb_member2) insert_method=last charset=utf8;

注：在上面創(chuàng)建主表時(shí)，指定的“insert_method=last”有三個(gè)可選參數(shù)，分別是：last：表示插入到最后一張表里面；first：表示插入到第一張表里面；NO：表示該表不能做任何寫入操作，只作為查詢使用。

3）查看剛剛創(chuàng)建的三個(gè)表結(jié)構(gòu)如下：

4）將數(shù)據(jù)分到兩個(gè)表中：

mysql> insert into tb_member1(id,name,sex) select id,name,sex from member where id%2=0;
Query OK, 2048 rows affected (0.01 sec)
Records: 2048  Duplicates: 0  Warnings: 0

mysql> insert into tb_member2(id,name,sex) select id,name,sex from member where id%2=1;
Query OK, 2048 rows affected (0.01 sec)
Records: 2048  Duplicates: 0  Warnings: 0

5）查看主表和兩個(gè)子表中的數(shù)據(jù)

第一個(gè)子表部分?jǐn)?shù)據(jù)如下：

第二個(gè)子表部分?jǐn)?shù)據(jù)如下：

主表部分查詢的部分?jǐn)?shù)據(jù)如下：

數(shù)據(jù)總行數(shù)如下：

注意：總表只是一個(gè)外殼，存取數(shù)據(jù)發(fā)生在一個(gè)一個(gè)的子表里面。 每個(gè)子表都有自已獨(dú)立的相關(guān)表文件，而主表只是一個(gè)殼，并沒(méi)有完整的相關(guān)表文件，當(dāng)確定主表中可以查到的數(shù)據(jù)和分表之前查到的數(shù)據(jù)完全一致時(shí)，就可以將原來(lái)的表刪除了，之后對(duì)表的讀寫操作，都可以對(duì)分表后的主表進(jìn)行。上面三個(gè)表對(duì)應(yīng)的本地文件如下：

可以看出，能夠查詢到所有數(shù)據(jù)的主表的本地?cái)?shù)據(jù)文件是非常小的，這也驗(yàn)證了，數(shù)據(jù)并沒(méi)有存在這個(gè)主表中。

6）對(duì)主表進(jìn)行插入數(shù)據(jù)的操作，如下：

可以看出，新增的兩條數(shù)據(jù)都插入在了第二張表中，因?yàn)樵趧?chuàng)建主表的時(shí)候，指定的“insert_method”是last，也就是所有插入數(shù)據(jù)的操作都是對(duì)最后一張表里進(jìn)行的，可以通過(guò)alter指令修改插入方法，如下：

mysql> alter table tb_member INSERT_METHOD=first;

修改插入方法后，再自行對(duì)表進(jìn)行插入數(shù)據(jù)的操作，可以發(fā)現(xiàn)所有的數(shù)據(jù)都寫入了第一個(gè)表（我這里插入了四條數(shù)據(jù)），查看如下：

上面是新增了四條數(shù)據(jù)，可以發(fā)現(xiàn)都插入到了第一張表。

若將插入方法修改為no，則表示這個(gè)表不能再插入任何數(shù)據(jù)，如下：

。

4、MySQL分區(qū)

1）什么是分區(qū)？

分區(qū)和分表相似，都是按照規(guī)則分解表。不同在于分表將大表分解為若干個(gè)獨(dú)立的實(shí)體表，而分區(qū)是將數(shù)據(jù)分段劃分在多個(gè)位置存放，分區(qū)后，表還是一張表，但數(shù)據(jù)散列到多個(gè)位置了。app讀寫的時(shí)候操作的還是表名字，db自動(dòng)去組織分區(qū)的數(shù)據(jù)。

分區(qū)主要有以下兩種形式：
水平分區(qū)：這種形式分區(qū)是對(duì)表的行進(jìn)行分區(qū)，所有在表中定義的列在每個(gè)數(shù)據(jù)集中都能找到，所以表的特性依然得以保持。
舉個(gè)簡(jiǎn)單例子：一個(gè)包含十年發(fā)票記錄的表可以被分區(qū)為十個(gè)不同的分區(qū)，每個(gè)分區(qū)包含的是其中一年的記錄。
垂直分區(qū)：這種分區(qū)方式一般來(lái)說(shuō)是通過(guò)對(duì)表的垂直劃分來(lái)減少目標(biāo)表的寬度，使某些特定的列被劃分到特定的分區(qū)，每個(gè)分區(qū)都包含了其中的列所對(duì)應(yīng)的行。
舉個(gè)簡(jiǎn)單例子：一個(gè)包含了大text和BLOB列的表，這些text和BLOB列又不經(jīng)常被訪問(wèn)，這時(shí)候就要把這些不經(jīng)常使用的text和BLOB了劃分到另一個(gè)分區(qū)，在保證它們數(shù)據(jù)相關(guān)性的同時(shí)還能提高訪問(wèn)速度。

2）查看當(dāng)前數(shù)據(jù)庫(kù)是否支持分區(qū)

MySQL 5.6之前，使用下面的參數(shù)查看當(dāng)前配置是否支持分區(qū)（如果為yes則表示支持分區(qū)）：

mysql> SHOW VARIABLES LIKE '%partition%';
+-----------------------+---------------+
|Variable_name | Value |
+-----------------------+---------------+
| have_partition_engine | YES |
+-----------------------+------------------+

在5.6及以后采用以下方式查看：

mysql> show plugins;

返回的結(jié)果中，有以下字段（如果status列為“ACTIVE”，則表示支持分區(qū)）：

3）按照范圍（range）方式的表分區(qū)

mysql> create table user(
    -> id int not null auto_increment,
    -> name varchar(30) not null default '',
    -> sex int(1) not null default '0',
    -> primary key(id)
    -> )default charset=utf8 auto_increment=1
    -> partition by range(id)(
    -> partition p0 values less than (3),
    -> partition p1 values less than (6),
    -> partition p2 values less than (9),
    -> partition p3 values less than (12),
    -> partition p4 values less than maxvalue
    -> );

注：在上面創(chuàng)建的表中，當(dāng)id列的值小于3將會(huì)插入到p0分區(qū)，大于3小于6的記錄將會(huì)插入到p1分區(qū)，以此類推，所有id值大于12的記錄都會(huì)插入到p4分區(qū)。

4）利用存儲(chǔ)過(guò)程插入一些數(shù)據(jù)

mysql> delimiter //                

5）到存放數(shù)據(jù)表文件的目錄下看一下：

可以看到數(shù)據(jù)是被分散存到不同的文件中的，本地的文件名都是“user#P#p0...”命名的，其中p0是自定義的分區(qū)名。

6）統(tǒng)計(jì)數(shù)據(jù)行數(shù)

7）從information_schema系統(tǒng)庫(kù)中的partition表中查看分區(qū)信息

8）從分區(qū)中查詢數(shù)據(jù)

9）添加及合并分區(qū)（需要先合并分區(qū)再新增分區(qū)）

1.添加分區(qū)：

注意：由于在創(chuàng)建表的時(shí)候，指定的最后一個(gè)分區(qū)range是maxvalue，所以是無(wú)法直接增加分區(qū)的，如下：

大意是：MAXVALUE只能在最后一個(gè)分區(qū)定義中使用

但也不可以將最后定義了maxvalue的分區(qū)直接刪除，因?yàn)閯h除分區(qū)的話，分區(qū)中的數(shù)據(jù)也會(huì)丟失，所以，如果需要新增分區(qū)的正確做法，應(yīng)該是先合并分區(qū)，再新增分區(qū)，這樣才可以保證數(shù)據(jù)的完整性，如下：

mysql> alter table user  reorganize partition p4 into (partition p03 values less than (15),partition p04 values less than maxvalue );

上述命令的作用就是將最后一個(gè)分區(qū)分為兩個(gè)分區(qū)，一個(gè)是自己所需要的分區(qū)，最后一個(gè)分區(qū)還是maxvalue（也必須是maxvalue），這樣就完成了添加分區(qū)。

本地表文件如下：

查詢新增分區(qū)中的數(shù)據(jù)如下：

2.合并分區(qū)

將p0、p1、p2、p3四個(gè)分區(qū)合并為p02：

mysql> alter table user                                            
    -> reorganize partition p0,p1,p2,p3 into
    -> (partition p02 values less than (12));

可以看到p02將整合了p0,p1,p2,p3三個(gè)分區(qū)的數(shù)據(jù)，如下：

本地文件如下：

10） 刪除分區(qū)

mysql> alter table user drop partition p02;  #刪除分區(qū)p02

注意：分區(qū)被刪除后，分區(qū)中的數(shù)據(jù)也將被刪除，刪除分區(qū)p02的表中所有數(shù)據(jù)如下：

———————— 本文至此結(jié)束，感謝閱讀 ————————