如何優(yōu)化MySQL大數(shù)據(jù)及分解存儲(chǔ)

下文主要給大家?guī)?lái)如何優(yōu)化MySQL大數(shù)據(jù)及分解存儲(chǔ)，希望如何優(yōu)化MySQL大數(shù)據(jù)及分解存儲(chǔ)能夠帶給大家實(shí)際用處，這也是我編輯這篇文章的主要目的。好了，廢話不多說(shuō)，大家直接看下文吧。

1）：sql語(yǔ)句的索引沒(méi)有起到效果，

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

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

分區(qū)和分表：

我們的數(shù)據(jù)庫(kù)數(shù)據(jù)越來(lái)越大，隨之而來(lái)的是單個(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ù)表，然后用戶訪問(wèn)的時(shí)候，根據(jù)一定的算法（如用hash的方式，也可以用求余（取模）的方式），讓用戶訪問(wèn)不同的表，這樣數(shù)據(jù)分散到多個(gè)數(shù)據(jù)表中，減少了單個(gè)數(shù)據(jù)表的訪問(wèn)壓力。提升了數(shù)據(jù)庫(kù)訪問(wèn)性能。分表的目的就在于此，減小數(shù)據(jù)庫(kù)的負(fù)擔(dān)，縮短查詢時(shí)間。

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

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

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

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

分表的方式：

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ù)量并且訪問(wèn)頻繁的表，將其分為若干個(gè)表

比如說(shuō)娛樂(lè)新聞的app可以通過(guò)每一分鐘的訪問(wèn)量，推算出每個(gè)小時(shí)，以及每一天的大概訪問(wèn)情況，如果是這樣的話，那么我們就以分表存儲(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ǔ)引擎來(lái)實(shí)現(xiàn)分表

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

那么我們來(lái)介紹下merge的用法以及功能：

merge存儲(chǔ)引擎：

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

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

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

那么我們來(lái)對(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ò)展。

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

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

mysql> insert into tty(name,sex) select name,sex from tty;

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

執(zhí)行之后我們來(lái)查詢一下有多少條數(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也可以用下面的語(yǔ)句  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;

接下來(lái)，我們把數(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ù)：{前面說(shuō)過(guò)共有8192條數(shù)據(jù)}

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

注意：每個(gè)子表都有自已獨(dú)立的相關(guān)表文件，而主表只是一個(gè)殼，并沒(méi)有完整的相關(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ū)方式一般來(lái)說(shuō)是通過(guò)對(duì)表的垂直劃分來(lái)減少目標(biāo)表的寬度，使某些特定的列被劃分到特定的分區(qū)，每個(gè)分區(qū)都包含了其中的列所對(duì)應(yīng)的行。

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

mysql> show plugins;

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

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

通過(guò)命令：

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)

    -> );

對(duì)于以上關(guān)于如何優(yōu)化MySQL大數(shù)據(jù)及分解存儲(chǔ)，大家是不是覺(jué)得非常有幫助。如果需要了解更多內(nèi)容，請(qǐng)繼續(xù)關(guān)注我們的行業(yè)資訊，相信你會(huì)喜歡上這些內(nèi)容的。