如何優化MySQL大數據及分解存儲

下文主要給大家帶來如何優化MySQL大數據及分解存儲，希望如何優化MySQL大數據及分解存儲能夠帶給大家實際用處，這也是我編輯這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。

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

2）：查詢的數據量過大，造成數據的查詢緩慢，

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

分區和分表：

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

1、分表

什么是分表？

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

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

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

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

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

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

分表的方式：

1）mysql集群

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

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

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

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

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

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

merge存儲引擎：

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

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

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

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

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

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

mysql> create database test；=========>創建test數據庫

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

create table tty(             ==============>創建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字符集，可以自動擴展。

接下來往里面添加點數據：

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

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

第二條語句多執行幾次就有了很多數據

執行之后我們來查詢一下有多少條數據：

mysql> select * from tty;    {有8192條數據}

下面我們進行分表，這里我們把tty分兩個表tb_tty1,tb_tty2。

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

創建tb_tty2表

//創建tb_tty2也可以用下面的語句  create table tb_tty2 like tb_tty1;

創建主表tb_tty

DROP table IF EXISTS tb_tty;

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

查看一下tb_tty表、tb_tty1、tb_tty2的結構:

mysql>desc tb_tty;

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

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

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

注意：總表只是一個外殼，存取數據發生在一個一個的子表里面。

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

2、分區

什么是分區？

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

另外分區也可以分為兩種：

垂直分區和水平分區

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

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

查看當將配置是否支持分區：

mysql> show plugins;

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

之前演示了一個分表的方式，接下來為大家演示一個分區的方式：

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

);

插入些數據

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

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

通過命令：

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

從information_schema系統庫中的partitions表中查看分區信息

從某個分區中查詢數據

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

新增分區

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

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

刪除分區
當刪除了一個分區，也同時刪除了該分區中所有的數據。

分區的合并
下面的SQL，將p1 – p3合并為2個分區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大數據及分解存儲，大家是不是覺得非常有幫助。如果需要了解更多內容，請繼續關注我們的行業資訊，相信你會喜歡上這些內容的。