MySQL分區分表

博文大綱：

• 1、為什么要分表？
• 2、MySQL分表
• 3、利用merge存儲引擎實現分表
• 4、MySQL分區

1、為什么要分表？

數據庫數據越來越大，隨之而來的是單個表中數據太多。以至于查詢速度變慢，而且由于表的鎖機制導致應用操作也搜到嚴重影響，出現了數據庫性能瓶頸。
mysql中有一種機制是表鎖定和行鎖定，是為了保證數據的完整性。表鎖定表示你們都不能對這張表進行操作，必須等我對表操作完才行。行鎖定也一樣，別的sql必須等我對這條數據操作完了，才能對這條數據進行操作。當出現這種情況時，我們可以考慮分表或分區。

2、MySQL分表

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

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

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

3、利用merge存儲引擎實現分表

注：只有myisam引擎的原表才可以利用merge存儲引擎實現分表。

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

舉個栗子：

1）創建一個完整表

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;       <!--我這里插入了4096條數據-->
+----------+
| count(*) |
+----------+
|     4096 |
+----------+
1 row in set (0.00 sec)

2）對上面完整的表進行分表

分表注意事項：

• 子表和主表的字段定義需要一致，包括數據類型，數據長度等；
• 當分表完成后，所有的操作（增刪改查）需要對主表進行，雖然主表并不存放實際的數據。

<!--創建兩個分表，表結構必須和上面完整的表結構一致-->
mysql> create table tb_member1 like member;
mysql> create table tb_member2 like member;
<!--創建merge引擎的表作為主表，并關聯上面的兩個分表-->
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;

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

3）查看剛剛創建的三個表結構如下：

4）將數據分到兩個表中：

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）查看主表和兩個子表中的數據

第一個子表部分數據如下：

第二個子表部分數據如下：

主表部分查詢的部分數據如下：

數據總行數如下：

注意：總表只是一個外殼，存取數據發生在一個一個的子表里面。 每個子表都有自已獨立的相關表文件，而主表只是一個殼，并沒有完整的相關表文件，當確定主表中可以查到的數據和分表之前查到的數據完全一致時，就可以將原來的表刪除了，之后對表的讀寫操作，都可以對分表后的主表進行。上面三個表對應的本地文件如下：

可以看出，能夠查詢到所有數據的主表的本地數據文件是非常小的，這也驗證了，數據并沒有存在這個主表中。

6）對主表進行插入數據的操作，如下：

可以看出，新增的兩條數據都插入在了第二張表中，因為在創建主表的時候，指定的“insert_method”是last，也就是所有插入數據的操作都是對最后一張表里進行的，可以通過alter指令修改插入方法，如下：

mysql> alter table tb_member INSERT_METHOD=first;

修改插入方法后，再自行對表進行插入數據的操作，可以發現所有的數據都寫入了第一個表（我這里插入了四條數據），查看如下：

上面是新增了四條數據，可以發現都插入到了第一張表。

若將插入方法修改為no，則表示這個表不能再插入任何數據，如下：

。

4、MySQL分區

1）什么是分區？

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

分區主要有以下兩種形式：
水平分區：這種形式分區是對表的行進行分區，所有在表中定義的列在每個數據集中都能找到，所以表的特性依然得以保持。
舉個簡單例子：一個包含十年發票記錄的表可以被分區為十個不同的分區，每個分區包含的是其中一年的記錄。
垂直分區：這種分區方式一般來說是通過對表的垂直劃分來減少目標表的寬度，使某些特定的列被劃分到特定的分區，每個分區都包含了其中的列所對應的行。
舉個簡單例子：一個包含了大text和BLOB列的表，這些text和BLOB列又不經常被訪問，這時候就要把這些不經常使用的text和BLOB了劃分到另一個分區，在保證它們數據相關性的同時還能提高訪問速度。

2）查看當前數據庫是否支持分區

MySQL 5.6之前，使用下面的參數查看當前配置是否支持分區（如果為yes則表示支持分區）：

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

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

mysql> show plugins;

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

3）按照范圍（range）方式的表分區

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
    -> );

注：在上面創建的表中，當id列的值小于3將會插入到p0分區，大于3小于6的記錄將會插入到p1分區，以此類推，所有id值大于12的記錄都會插入到p4分區。

4）利用存儲過程插入一些數據

mysql> delimiter //                <!--改變默認的截斷符為“//”-->

5）到存放數據表文件的目錄下看一下：

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

6）統計數據行數

7）從information_schema系統庫中的partition表中查看分區信息

8）從分區中查詢數據

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

1.添加分區：

注意：由于在創建表的時候，指定的最后一個分區range是maxvalue，所以是無法直接增加分區的，如下：

大意是：MAXVALUE只能在最后一個分區定義中使用

但也不可以將最后定義了maxvalue的分區直接刪除，因為刪除分區的話，分區中的數據也會丟失，所以，如果需要新增分區的正確做法，應該是先合并分區，再新增分區，這樣才可以保證數據的完整性，如下：

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

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

本地表文件如下：

查詢新增分區中的數據如下：

2.合并分區

將p0、p1、p2、p3四個分區合并為p02：

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

可以看到p02將整合了p0,p1,p2,p3三個分區的數據，如下：

本地文件如下：

10） 刪除分區

mysql> alter table user drop partition p02;  #刪除分區p02

注意：分區被刪除后，分區中的數據也將被刪除，刪除分區p02的表中所有數據如下：

———————— 本文至此結束，感謝閱讀 ————————