【引自tty之星的博客】前言：在上一章介紹了MySQL的優(yōu)化以及優(yōu)化的思路，那么如果有一種情況如果數(shù)據(jù)庫已經(jīng)建立好了索引，在使用sql語句索引查詢時;但是在慢查詢?nèi)罩井斨腥稳徽业搅酥暗膕ql語句會有哪幾種情況：

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

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

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

分區(qū)和分表：

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

1、分表

什么是分表?

分表是將一個大表按照一定的規(guī)則分解成多張具有獨立存儲空間的實體表，每個表都對應三個文件，MYD數(shù)據(jù)文件，.MYI索引文件，.frm表結構文件。這些表可以分布在同一塊磁盤上，也可以在不同的機器上。app讀寫的時候根據(jù)事先定義好的規(guī)則得到對應的表名，然后去操作它。主要針對myisam存儲，如果是innodb存儲那么將會是.idb文件和.frm文件

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

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

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

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

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

分表的方式：

1)mysql集群

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

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

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

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

對于DBA來說，如果要把已有的大數(shù)據(jù)量表分開比較痛苦，最痛苦的事就是改代碼，因為程序里面的sql語句已經(jīng)寫好了，用merge存儲引擎來實現(xiàn)分表, 這種方法比較適合。

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

merge存儲引擎：

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

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

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

那么我們來對merge進行一個演示，希望大家對merge有一個更加深刻的了解

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

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

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

mysql> use test; ==========>進入test庫

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

id bigint auto_increment primary key, ============> 將id號設置為主鍵

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

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

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

接下來往里面添加點數(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ù)} 
 
 
 

下面我們進行分表，這里我們把tty分兩個表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的結構:

 
 
 
 
  
  
  
  
mysql>desc tb_tty; 
 
 
 

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

 
 
 
 
  
  
  
  
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;  
 
 
 

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

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

 

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

注意：每個子表都有自已獨立的相關表文件，而主表只是一個殼，并沒有完整的相關表文件

2、分區(qū)

什么是分區(qū)?

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

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

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

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

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

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

 
 
 
 
  
  
  
  
mysql> show plugins; 
 
 
 

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

之前演示了一個分表的方式，接下來為大家演示一個分區(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ù)庫表文件的地方看一下

通過命令：

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

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

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

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

新增分區(qū)

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

使用此命令的時候需要的將p5刪掉之后才可以進行新的增加

刪除分區(qū)

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

分區(qū)的合并

下面的SQL，將p1 – p3合并為2個分區(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) 
  
  
  
  
 
  
  
  
  
-> );   
 
 
 

文章題目：MySQL大數(shù)據(jù)的優(yōu)化以及分解存儲
標題網(wǎng)址：http://uogjgqi.cn/article/cdidesd.html