一文搞懂mysql的分區和分表知識

下面一起來了解下mysql的分區和分表，相信大家看完肯定會受益匪淺，文字在精不在多，希望mysql的分區和分表這篇短內容是你想要的。

mysql分表和分區

1.mysql分表

什么是分表？

分表是將一個大表按照一定的規則分解成多張具有獨立存儲空間的實體表，每個表都對應三個文件，MYD數據文件，MYI索引文件，frm表結構文件。如果是Innodb存儲引擎，索引文件和數據文件存放在同一個位置。這些表可以分布在同一塊磁盤上，也可以在不同的機器上。

app讀寫的時候根據事先定義好的規則得到對應的的表明，然后去操作它。

將單個數據庫表進行拆分，拆分成多個數據表，然后用戶訪問的時候，根據一定的算法（如用hash的方式，也可以用取余的方式），讓用戶訪問不同的表，這樣數據分散到多個數據表中，減少了單個數據表的訪問壓力。提升了數據庫訪問性能。

mysql分表分為垂直切分和水平切分

垂直切分是指數據表列的拆分，把一張列比較多的表拆分為多張表。

通常按一下原則進行垂直切分：

把不常用的字段單獨放在一張表；

把text，blob（binary large object，二進制大對象）等大字段拆分出來放在附表中；

經常組合查詢的列放在一張表中；

水平拆分是指數據表行的拆分，把一張表的數據拆分成多張表來存放。

水平拆分原則

通常情況下，我們使用hash、取模等方式來進行表的拆分

進行拆分后的表，這時我們就要約束用戶查詢行為。

分表的幾種方式：

1）預先估計會出現大數據量并且訪問頻繁的表，將其分為若干個表

2）利用merge存儲引擎來實現分表

創建一個完整表存儲著所有的成員信息（表名為member）

并往里面插入點數據：

mysql> select * from member;

+----+------+-----+

| id | name | sex |

+----+------+-----+

|  1 | tom  |   1 |

|  2 | tom  |   1 |

|  3 | tom  |   1 |

|  4 | tom  |   1 |

|  5 | tom  |   1 |

|  6 | tom  |   1 |

|  7 | tom  |   1 |

|  8 | tom  |   1 |

|  9 | tom  |   1 |

| 10 | tom  |   1 |

| 11 | tom  |   1 |

| 12 | tom  |   1 |

| 13 | tom  |   1 |

| 14 | tom  |   1 |

| 15 | tom  |   1 |

| 16 | tom  |   1 |

+----+------+-----+

下面我們進行分表，這里我們把member分兩個表tb_member1，tb_member2

mysql> use test;

mysql> create table tb_member1(

    -> id bigint primary key,

    -> name varchar(20),

    -> sex tinyint not null default '0'

    -> )engine=myisam default charset=utf8;

用下面命令可以更簡潔的創建出與tb_member1一樣的表：

mysql>create table tb_member2 like tb_member1;        

創建主表tb_member

mysql> create table tb_member(

    -> id bigint primary key,

    -> name varchar(20),

    -> sex tinyint not null default '0'

    -> ) engine=merge union=(tb_member1,tb_member2) insert_method=last charset=utf8;

查看一下tb_member表的結構：

mysql> desc tb_member;                                                                 +-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id    | bigint(20)  | NO   | PRI | NULL    |       |

| name  | varchar(20) | YES  |     | NULL    |       |

| sex   | tinyint(4)  | NO   |     | 0       |       |

+-------+-------------+------+-----+---------+-------+

注：查看子表與主表的字段定義要一致

接下來，把數據分到兩個分表中去：

mysql> insert into tb_member1(id,name,sex) select id,name,sex from member where id%2=0;

mysql> insert into tb_member2(id,name,sex) select id,name,sex from member where id%2=1;

查看兩個子表的數據：

mysql> select * from tb_member1;

+----+------+-----+

| id | name | sex |

+----+------+-----+

|  2 | tom  |   1 |

|  4 | tom  |   1 |

|  6 | tom  |   1 |

|  8 | tom  |   1 |

| 10 | tom  |   1 |

| 12 | tom  |   1 |

| 14 | tom  |   1 |

| 16 | tom  |   1 |

+----+------+-----+

8 rows in set (0.00 sec)

mysql> select * from tb_member2;

+----+------+-----+

| id | name | sex |

+----+------+-----+

|  1 | tom  |   1 |

|  3 | tom  |   1 |

|  5 | tom  |   1 |

|  7 | tom  |   1 |

|  9 | tom  |   1 |

| 11 | tom  |   1 |

| 13 | tom  |   1 |

| 15 | tom  |   1 |

+----+------+-----+

8 rows in set (0.00 sec)

查看一下主表的數據：

mysql> select * from tb_member;

+----+------+-----+

| id | name | sex |

+----+------+-----+

|  2 | tom  |   1 |

|  4 | tom  |   1 |

|  6 | tom  |   1 |

|  8 | tom  |   1 |

| 10 | tom  |   1 |

| 12 | tom  |   1 |

| 14 | tom  |   1 |

| 16 | tom  |   1 |

|  1 | tom  |   1 |

|  3 | tom  |   1 |

|  5 | tom  |   1 |

|  7 | tom  |   1 |

|  9 | tom  |   1 |

| 11 | tom  |   1 |

| 13 | tom  |   1 |

| 15 | tom  |   1 |

+----+------+-----+

16 rows in set (0.00 sec)

總結：每個子表都有自己獨立的表文件，主表只是一個殼，并沒有完整的表文件。

[root@localhost ~]# ls -l /usr/local/mysql/data/test/tb_member*

-rw-r----- 1 mysql mysql 8614 Feb 13 21:44 /usr/local/mysql/data/test/tb_member1.frm

-rw-r----- 1 mysql mysql  160 Feb 13 21:47 /usr/local/mysql/data/test/tb_member1.MYD

-rw-r----- 1 mysql mysql 2048 Feb 13 21:47 /usr/local/mysql/data/test/tb_member1.MYI

-rw-r----- 1 mysql mysql 8614 Feb 13 21:44 /usr/local/mysql/data/test/tb_member2.frm

-rw-r----- 1 mysql mysql  160 Feb 13 21:47 /usr/local/mysql/data/test/tb_member2.MYD

-rw-r----- 1 mysql mysql 2048 Feb 13 21:47 /usr/local/mysql/data/test/tb_member2.MYI

-rw-r----- 1 mysql mysql 8614 Feb 13 21:46 /usr/local/mysql/data/test/tb_member.frm

-rw-r----- 1 mysql mysql   42 Feb 13 21:46 /usr/local/mysql/data/test/tb_member.MRG

2.分區

什么是分區？

分區與分表區別：分表將大表分解為若干個獨立的實體表，而分區是將數據分段劃分在多個位置存放，分區后，表還是一張大表，但數據散列到多個位置了。

app讀寫的時候操作的還是表名字，db自動去組織分區的數據。

分區主要有兩種形式：

水平分區：對表的行進行分區，所有在表中定義的列在每個數據集中都能找到，所以表的特性得以保持。

垂直分區：通過對表的垂直劃分來減少目標表的寬度，使某些特定的列被劃分到特定的分區，每個分區都包含了其中的列所對應的行。

分區技術支持

在5.6之前，使用以下參數查看當前配置是否支持分區

mysql> show variables like '%partition%';

顯示have_partition_engine選項后為YES

在5.6之后，則采用以下方式查看

mysql> show plugins;

顯示結果中，可以看到partition是active的，表示支持分區

下面演示一個按照范圍（range）方式的表分區

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

插入數據：

mysql> insert into user(name,sex) values('tom1','0');

mysql> insert into user(name,sex) values('tom2','1');

mysql> insert into user(name,sex) values('tom3','2');

mysql> insert into user(name,sex) select name,sex from user;（多重復幾遍得到雙倍數據）

到存放數據庫表文件的地方看一下

[root@localhost ~]# ls -l /usr/local/mysql/data/test/user*

-rw-r----- 1 mysql mysql  8614 Feb 13 21:59 /usr/local/mysql/data/test/user.frm

-rw-r----- 1 mysql mysql 98304 Feb 13 22:00 /usr/local/mysql/data/test/user#P#p0.ibd

-rw-r----- 1 mysql mysql 98304 Feb 13 22:00 /usr/local/mysql/data/test/user#P#p1.ibd

-rw-r----- 1 mysql mysql 98304 Feb 13 22:00 /usr/local/mysql/data/test/user#P#p2.ibd

-rw-r----- 1 mysql mysql 98304 Feb 13 22:00 /usr/local/mysql/data/test/user#P#p3.ibd

-rw-r----- 1 mysql mysql 98304 Feb 13 22:00 /usr/local/mysql/data/test/user#P#p4.ibd

從系統數據庫中的partition表中查看分區信息

mysql> select * from information_schema.partitions where table_schema='test' and table_name='user'\G；

合并分區：

Eg：將p1 - p3合并為2個p01 - p02

mysql> alter table user

    -> reorganize partition p1,p2,p3 into

    -> (partition p01 values less than (8),

    -> partition p02 values less than (12)

    -> );

再次查看數據庫表文件：

[root@localhost ~]# ls -l /usr/local/mysql/data/test/user*

-rw-r----- 1 mysql mysql  8614 Feb 13 22:03 /usr/local/mysql/data/test/user.frm

-rw-r----- 1 mysql mysql 98304 Feb 13 22:03 /usr/local/mysql/data/test/user#P#p01.ibd

-rw-r----- 1 mysql mysql 98304 Feb 13 22:03 /usr/local/mysql/data/test/user#P#p02.ibd

-rw-r----- 1 mysql mysql 98304 Feb 13 22:00 /usr/local/mysql/data/test/user#P#p0.ibd

-rw-r----- 1 mysql mysql 98304 Feb 13 22:00 /usr/local/mysql/data/test/user#P#p4.ibd

未分區表和分區表性能測試

創建一個未分區的表

mysql> create table tab2(c1 int,c2 varchar(30),c3 date)

    -> partition by range(year(c3))(partition p0 values less than (1995),

    -> partition p1 values less than (1996),

    -> partition p2 values less than (1997),

    -> partition p3 values less than (1998),

    -> partition p4 values less than (1999),

    -> partition p5 values less than (2000),

    -> partition p6 values less than (2001),

    -> partition p7 values less than (2002),

    -> partition p8 values less than (2003),

    -> partition p9 values less than (2004),

    -> partition p10 values less than (2010),

    -> partition p11 values less than maxvalue);

通過存儲過程插入10萬條數據

創建存儲過程：

mysql> delimiter $$

mysql> create procedure load_part_tab()

    -> begin

    -> declare v int default 0;

    -> while v < 10000

    -> do

    -> insert into tab1

    -> values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));

    -> set v = v + 1;

    -> end while;

    -> end

    -> $$

執行存儲過程：

mysql> call load_part_tab();

向tab2表中插入數據

Insert into tab2 select * from tab1；

測試SQL性能

mysql> select count(*) from tab1 where c3 > '1995-01-01' and c3 < '1995-12-31';

+----------+

| count(*) |

+----------+

|      990 |

+----------+

1 row in set (0.11 sec)

mysql> select count(*) from tab2 where c3 > '1995-01-01' and c3 < '1995-12-31';

+----------+

| count(*) |

+----------+

|        0 |

+----------+

1 row in set (0.03 sec)

分區表比未分區表的執行時間少很多。

創建索引后情況測試

mysql> create index idx_of_c3 on tab1(c3);

Query OK, 0 rows affected (0.28 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_of_c3 on tab2(c3);

Query OK, 0 rows affected (0.22 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(*) from tab1 where c3 > '1996-01-01' and c3 < '1996-12-31';

+----------+

| count(*) |

+----------+

|     1006 |

+----------+

1 row in set (0.11 sec)

重啟mysql服務

mysql> select count(*) from tab1 where c3 > '1996-01-01' and c3 < '1996-12-31';

+----------+

| count(*) |

+----------+

|     1006 |

+----------+

1 row in set (0.00 sec)

創建索引后分區表和未分區表相差不大（數據量越大差別會明顯些）

看完mysql的分區和分表這篇文章后，很多讀者朋友肯定會想要了解更多的相關內容，如需獲取更多的行業信息，可以關注我們的行業資訊欄目。