【引自tty之星的博客】前言：在上一章介紹了MySQL的優(yōu)化以及優(yōu)化的思路，那么如果有一種情況如果數(shù)據(jù)庫(kù)已經(jīng)建立好了索引，在使用sql語句索引查詢時(shí);但是在慢查詢?nèi)罩井?dāng)中任然找到了之前的sql語句會(huì)有哪幾種情況：

1)：sql語句的索引沒有起到效果，

2)：查詢的數(shù)據(jù)量過大，造成數(shù)據(jù)的查詢緩慢，

在工作當(dāng)中每個(gè)數(shù)據(jù)庫(kù)都會(huì)存在龐大的數(shù)據(jù)量，比如說訪問量等等都會(huì)造成數(shù)據(jù)的查詢緩慢，那么如何解決這個(gè)問題，接下來往下看：

分區(qū)和分表：

我們的數(shù)據(jù)庫(kù)數(shù)據(jù)越來越大，隨之而來的是單個(gè)表中數(shù)據(jù)太多。以至于查詢書讀變慢，而且由于表的鎖機(jī)制導(dǎo)致應(yīng)用操作也搜到嚴(yán)重影響，出現(xiàn)了數(shù)據(jù)庫(kù)性能瓶頸。

1、分表

什么是分表?

分表是將一個(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)的表名，然后去操作它。主要針對(duì)myisam存儲(chǔ)，如果是innodb存儲(chǔ)那么將會(huì)是.idb文件和.frm文件

將單個(gè)數(shù)據(jù)庫(kù)表進(jìn)行拆分，拆分成多個(gè)數(shù)據(jù)表，然后用戶訪問的時(shí)候，根據(jù)一定的算法(如用hash的方式，也可以用求余(取模)的方式)，讓用戶訪問不同的表，這樣數(shù)據(jù)分散到多個(gè)數(shù)據(jù)表中，減少了單個(gè)數(shù)據(jù)表的訪問壓力。提升了數(shù)據(jù)庫(kù)訪問性能。分表的目的就在于此，減小數(shù)據(jù)庫(kù)的負(fù)擔(dān)，縮短查詢時(shí)間。

注：客戶端訪問的時(shí)候根本不知道表已經(jīng)被分開了，任然屬于一個(gè)邏輯的整體對(duì)于客戶端來說，客戶端主要關(guān)心的是查詢的內(nèi)容以及查詢的速度效率，但是作為一名DBA必須要了解這些;只有這樣才能夠滿足客戶的要求。

另外在分表的時(shí)候分為兩種;垂直分割和水平分割：

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

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

分表的方式：

1)mysql集群

它并不是分表，但起到了和分表相同的作用。集群可分擔(dān)數(shù)據(jù)庫(kù)的操作次數(shù)，將任務(wù)分擔(dān)到多臺(tái)數(shù)據(jù)庫(kù)上。集群可以讀寫分離，減少讀寫壓力。從而提升數(shù)據(jù)庫(kù)性能。

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

比如說娛樂新聞的app可以通過每一分鐘的訪問量，推算出每個(gè)小時(shí)，以及每一天的大概訪問情況，如果是這樣的話，那么我們就以分表存儲(chǔ)這些數(shù)據(jù)，例如創(chuàng)建10000張表，設(shè)定好閾值，當(dāng)一定的數(shù)據(jù)量達(dá)到預(yù)先設(shè)定的值得時(shí)候就想下一個(gè)表當(dāng)中存儲(chǔ)內(nèi)容，保證數(shù)據(jù)庫(kù)的性能。

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

對(duì)于DBA來說，如果要把已有的大數(shù)據(jù)量表分開比較痛苦，最痛苦的事就是改代碼，因?yàn)槌绦蚶锩娴膕ql語句已經(jīng)寫好了，用merge存儲(chǔ)引擎來實(shí)現(xiàn)分表, 這種方法比較適合。

那么我們來介紹下merge的用法以及功能：

merge存儲(chǔ)引擎：

merge分表，分為主表和子表，主表類似于一個(gè)殼子，邏輯上封裝了子表，實(shí)際上數(shù)據(jù)都是存儲(chǔ)在子表中的。

注：字表是用來存放真實(shí)數(shù)據(jù)的地方是不能在進(jìn)行細(xì)分的，但是可以合并，如果要?jiǎng)?chuàng)建多個(gè)字表，就在開始創(chuàng)建的時(shí)候多創(chuàng)建幾個(gè)，進(jìn)行估算大概需要幾個(gè)。

我們可以通過主表插入和查詢數(shù)據(jù)，如果清楚分表規(guī)律，也可以直接操作子表。

那么我們來對(duì)merge進(jìn)行一個(gè)演示，希望大家對(duì)merge有一個(gè)更加深刻的了解

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

mysql> drop database IF EXISTS test; =======>如果test存在那么就刪掉它

mysql> create database test;=========>創(chuàng)建test數(shù)據(jù)庫(kù)

mysql> use test; ==========>進(jìn)入test庫(kù)

create table tty( ==============>創(chuàng)建tty表

id bigint auto_increment primary key, ============> 將id號(hào)設(shè)置為主鍵

name varchar(20), =============>name的字符類型

sextinyint not nulldefault '0' ==========>性別的字符類型

)engine=myisam default charset=utf8 auto_increment=1; ===========> 存儲(chǔ)引擎為myisam，utf-8字符集，可以自動(dòng)擴(kuò)展。

接下來往里面添加點(diǎn)數(shù)據(jù)：

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

第二條語句多執(zhí)行幾次就有了很多數(shù)據(jù)

執(zhí)行之后我們來查詢一下有多少條數(shù)據(jù)：

mysql> select * from tty; {有8192條數(shù)據(jù)} 

下面我們進(jìn)行分表，這里我們把tty分兩個(gè)表tb_tty1,tb_tty2。

創(chuàng)建tb_tty1表：

mysql> use test; 
 
DROP table IF EXISTS tb_tty1; 
 
create table tb_tty1( 
 
id bigint primary key , 
 
name varchar(20), 
 
sex tinyint not null default '0' 
 
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ; 

DROP table IF EXISTS tb_tty2; 
 
create table tb_tty2( 
 
id bigint primary key, 
 
name varchar(20), 
 
sex tinyint not null default '0' 
 
)ENGINE=MyISAM DEFAULT CHARSET=utf8;  

創(chuàng)建tb_tty2表 

//創(chuàng)建tb_tty2也可以用下面的語句 create table tb_tty2 like tb_tty1;

創(chuàng)建主表tb_tty

DROP table IF EXISTS tb_tty; 

注：INSERT_METHOD,此參數(shù)INSERT_METHOD = NO 表示該表不能做任何寫入操作只作為查詢使用,INSERT_METHOD = LAST表示插入到最后的一張表里面。INSERT_METHOD = first表示插入到第一張表里面。

查看一下tb_tty表、tb_tty1、tb_tty2的結(jié)構(gòu):

mysql>desc tb_tty; 

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

mysql> insert into tb_tty1(id,name,sex) select id,name,sex from tty where id%2=0; 
 
mysql> insert into tb_tty2(id,name,sex) select id,name,sex from tty where id%2=1;  

如果要是分為三個(gè)表的情況可以使用ID%3=0、ID%3=1、id%=2

查看兩個(gè)子表的數(shù)據(jù)：{前面說過共有8192條數(shù)據(jù)}

注意：總表只是一個(gè)外殼，存取數(shù)據(jù)發(fā)生在一個(gè)一個(gè)的子表里面。

注意：每個(gè)子表都有自已獨(dú)立的相關(guān)表文件，而主表只是一個(gè)殼，并沒有完整的相關(guān)表文件

2、分區(qū)

什么是分區(qū)?

分區(qū)和分表相似，都是按照規(guī)則分解表。不同在于分表將大表分解為若干個(gè)獨(dú)立的實(shí)體表，而分區(qū)是將數(shù)據(jù)分段劃分在多個(gè)位置存放，分區(qū)后，表還是一張表，但數(shù)據(jù)散列到多個(gè)位置。

另外分區(qū)也可以分為兩種：

垂直分區(qū)和水平分區(qū)

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

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

查看當(dāng)將配置是否支持分區(qū)：

mysql> show plugins; 

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

之前演示了一個(gè)分表的方式，接下來為大家演示一個(gè)分區(qū)的方式：

mysql> create database test2; 
 
mysql> use test2; 
 
mysql> create table if not exists 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 test2.user(name,sex)values ('tom1','0'); 
 
mysql> insert into test2.user(name,sex)values ('tom2','1'); 
 
mysql> insert into test2.user(name,sex)values ('tom3','1'); 
 
mysql> insert into test2.user(name,sex)values ('tom4','0'); 
 
mysql> insert into test2.user(name,sex)values ('tom5','0'); 
 
mysql> insert into test2.user(name,sex)values ('tom6','1'); 
 
mysql> insert into test2.user(name,sex)values ('tom7','1'); 
 
mysql> insert into test2.user(name,sex)values ('tom8','1'); 
 
mysql> insert into test2.user(name,sex)values ('tom9','1'); 
 
mysql> insert into test2.user(name,sex)values ('tom10','1'); 
 
mysql> insert into test2.user(name,sex)values ('tom11','1'); 
 
mysql> insert into test2.user(name,sex)values ('tom12','1'); 
 
mysql> insert into test2.user(name,sex)values ('tom13','1'); 
 
mysql> insert into test2.user(name,sex)values ('tom14','1');  

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

通過命令：

mysql> select count(id) as count from user; 

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

從某個(gè)分區(qū)中查詢數(shù)據(jù)

mysql> select * from test2.user partition(p0);  

新增分區(qū)

mysql> alter table test2.user add partition (partition partionname values less than (n)); 

使用此命令的時(shí)候需要的將p5刪掉之后才可以進(jìn)行新的增加

刪除分區(qū)

當(dāng)刪除了一個(gè)分區(qū)，也同時(shí)刪除了該分區(qū)中所有的數(shù)據(jù)。 

分區(qū)的合并

下面的SQL，將p1 – p3合并為2個(gè)分區(qū)p01– p02

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