一文搞懂mysql的分區(qū)和分表知識(shí)

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

創(chuàng)新互聯(lián)建站，為您提供網(wǎng)站建設(shè)、網(wǎng)站制作公司、網(wǎng)站營銷推廣、網(wǎng)站開發(fā)設(shè)計(jì)，對(duì)服務(wù)社區(qū)文化墻等多個(gè)行業(yè)擁有豐富的網(wǎng)站建設(shè)及推廣經(jīng)驗(yàn)。創(chuàng)新互聯(lián)建站網(wǎng)站建設(shè)公司成立于2013年,提供專業(yè)網(wǎng)站制作報(bào)價(jià)服務(wù),我們深知市場(chǎng)的競(jìng)爭(zhēng)激烈，認(rèn)真對(duì)待每位客戶，為客戶提供賞心悅目的作品。 與客戶共同發(fā)展進(jìn)步，是我們永遠(yuǎn)的責(zé)任！

mysql分表和分區(qū)

1.mysql分表

什么是分表？

分表是將一個(gè)大表按照一定的規(guī)則分解成多張具有獨(dú)立存儲(chǔ)空間的實(shí)體表，每個(gè)表都對(duì)應(yīng)三個(gè)文件，MYD數(shù)據(jù)文件，MYI索引文件，frm表結(jié)構(gòu)文件。如果是Innodb存儲(chǔ)引擎，索引文件和數(shù)據(jù)文件存放在同一個(gè)位置。這些表可以分布在同一塊磁盤上，也可以在不同的機(jī)器上。

app讀寫的時(shí)候根據(jù)事先定義好的規(guī)則得到對(duì)應(yīng)的的表明，然后去操作它。

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

 

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

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

通常按一下原則進(jìn)行垂直切分：

把不常用的字段單獨(dú)放在一張表；

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

經(jīng)常組合查詢的列放在一張表中；

 

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

水平拆分原則

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

進(jìn)行拆分后的表，這時(shí)我們就要約束用戶查詢行為。

 

分表的幾種方式：

1）預(yù)先估計(jì)會(huì)出現(xiàn)大數(shù)據(jù)量并且訪問頻繁的表，將其分為若干個(gè)表

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

創(chuàng)建一個(gè)完整表存儲(chǔ)著所有的成員信息（表名為member）

并往里面插入點(diǎn)數(shù)據(jù)：

 

 

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 |

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

下面我們進(jìn)行分表，這里我們把member分兩個(gè)表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;

用下面命令可以更簡(jiǎn)潔的創(chuàng)建出與tb_member1一樣的表：

mysql>create table tb_member2 like tb_member1;        

創(chuàng)建主表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表的結(jié)構(gòu)：

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

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

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

接下來，把數(shù)據(jù)分到兩個(gè)分表中去：

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;

查看兩個(gè)子表的數(shù)據(jù)：

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)

查看一下主表的數(shù)據(jù)：

 

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)

 

總結(jié)：每個(gè)子表都有自己獨(dú)立的表文件，主表只是一個(gè)殼，并沒有完整的表文件。

 

[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.分區(qū)

什么是分區(qū)？

分區(qū)與分表區(qū)別：分表將大表分解為若干個(gè)獨(dú)立的實(shí)體表，而分區(qū)是將數(shù)據(jù)分段劃分在多個(gè)位置存放，分區(qū)后，表還是一張大表，但數(shù)據(jù)散列到多個(gè)位置了。

app讀寫的時(shí)候操作的還是表名字，db自動(dòng)去組織分區(qū)的數(shù)據(jù)。

 

分區(qū)主要有兩種形式：

水平分區(qū)：對(duì)表的行進(jìn)行分區(qū)，所有在表中定義的列在每個(gè)數(shù)據(jù)集中都能找到，所以表的特性得以保持。

垂直分區(qū)：通過對(duì)表的垂直劃分來減少目標(biāo)表的寬度，使某些特定的列被劃分到特定的分區(qū)，每個(gè)分區(qū)都包含了其中的列所對(duì)應(yīng)的行。

 

分區(qū)技術(shù)支持

在5.6之前，使用以下參數(shù)查看當(dāng)前配置是否支持分區(qū)

mysql> show variables like '%partition%';

顯示have_partition_engine選項(xiàng)后為YES

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

mysql> show plugins;

顯示結(jié)果中，可以看到partition是active的，表示支持分區(qū)

  

下面演示一個(gè)按照范圍（range）方式的表分區(qū)

創(chuàng)建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);

插入數(shù)據(jù)：

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;（多重復(fù)幾遍得到雙倍數(shù)據(jù)）

 

到存放數(shù)據(jù)庫表文件的地方看一下

[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

 

從系統(tǒng)數(shù)據(jù)庫中的partition表中查看分區(qū)信息

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

合并分區(qū)：

Eg：將p1 - p3合并為2個(gè)p01 - p02

mysql> alter table user

    -> reorganize partition p1,p2,p3 into

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

    -> partition p02 values less than (12)

    -> );

再次查看數(shù)據(jù)庫表文件：

[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

 

未分區(qū)表和分區(qū)表性能測(cè)試

創(chuàng)建一個(gè)未分區(qū)的表

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

通過存儲(chǔ)過程插入10萬條數(shù)據(jù)

創(chuàng)建存儲(chǔ)過程：

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

    -> $$

執(zhí)行存儲(chǔ)過程：

mysql> call load_part_tab();

向tab2表中插入數(shù)據(jù)

Insert into tab2 select * from tab1；

測(cè)試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)

 

分區(qū)表比未分區(qū)表的執(zhí)行時(shí)間少很多。

 

創(chuàng)建索引后情況測(cè)試

 

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服務(wù)

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

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

| count(*) |

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

|     1006 |

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

1 row in set (0.00 sec)

創(chuàng)建索引后分區(qū)表和未分區(qū)表相差不大（數(shù)據(jù)量越大差別會(huì)明顯些）

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

網(wǎng)頁標(biāo)題：一文搞懂mysql的分區(qū)和分表知識(shí)
本文網(wǎng)址：http://muchs.cn/article22/isjjcc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供服務(wù)器托管、靜態(tài)網(wǎng)站、網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計(jì)公司、標(biāo)簽優(yōu)化、關(guān)鍵詞優(yōu)化

廣告

聲明：本網(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；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)