MySQL性能優(yōu)化及分區(qū)、分表的詳細步驟

本文主要給大家簡單講講MySQL性能優(yōu)化及分區(qū)、分表的詳細步驟，相關專業(yè)術語大家可以上網查查或者找一些相關書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望MySQL性能優(yōu)化及分區(qū)、分表的詳細步驟這篇文章可以給大家?guī)硪恍嶋H幫助。

一、 分表

  1. 分表簡介

• 分表是將一個大表按照一定的規(guī)則分解成多張具有獨立存儲空間的實體表。

• 如果正在使用的表需要進行分區(qū)，就需要同時修改app的規(guī)則，使mysql可以得知用戶查詢的數據在哪。

  2. 分表類型

 分為垂直切分和水平切分

• 垂直切分：將某些列分到另一個表

• 水平切分：將某些行分到另一個表

  3. 分表的方式

   1） Mysql集群

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

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

• 根據一定的算法（如用hash的方式，也可以用求余（取模）的方式）讓用戶訪問不同的表

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

• 使用merge存儲引擎分表，就不用再修改app規(guī)則

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

  4. merge存儲引擎分表

     創(chuàng)建子表

     創(chuàng)建主表

    開始分表

     通過ID取模的方式過濾出分別要插入到子表的數據

     驗證

     子表2只有奇數數據，而主表都會有

     每個子表都會有自己獨立的表文件

二、 分區(qū)

  1. 什么是分區(qū)

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

  2. 分區(qū)形式

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

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

   3. 查看mysql是否支持分區(qū)技術

     本例是5.7

     首先創(chuàng)建表并為其創(chuàng)建分區(qū)，并插入數據，以作測試

     下圖是插入的數據

     去查看物理文件

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

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

     添加一個分區(qū)

     合并分區(qū)

  4. 對比創(chuàng)建分區(qū)和沒創(chuàng)建分區(qū)的性能

     首先創(chuàng)建一個表tab1不做分區(qū)

     創(chuàng)建表tab2按日期的年份拆分

     創(chuàng)建一個存儲過程，循環(huán)向表中存入足夠大的數據，用于測試

     執(zhí)行存儲過程

     將tab1的數據插入到tab2中

     執(zhí)行查詢語句測試性能，執(zhí)行時間短的性能就好

     通過explain語句分析執(zhí)行情況

     未分區(qū)的表

     已分區(qū)的表

    為兩個表創(chuàng)建索引

     再次進行測試

三、 Mysql的分區(qū)類型

   在創(chuàng)建表的時候使用：partition by  類型（字段），定義分區(qū)類型及其使用的字段

  1. Range

l  基于一個連續(xù)區(qū)間的列值，把多行分配給分區(qū)，區(qū)間不能重疊

l  使用values  less  than操作符定義分區(qū)

l  一般最后要創(chuàng)建一個maxvalue分區(qū)，用于存放和之前區(qū)間不匹配的值

  2. List分區(qū)

l  基于列值匹配一個離散值（不連續(xù)的值）集合中的某個值來進行分區(qū)

l  使用：Values  in，操作符定義分區(qū)

l  List沒有range中類似于maxvalue的定義符存在，所以要匹配的值都必須手動創(chuàng)建

  3. Hash分區(qū)

l對表的一個或多個列的hashkey進行計算，最后通過這個Hash碼不同數值對應的數據區(qū)域進行自動分區(qū)，但需要指定分區(qū)數量。

lList和range必須指定一個給定的列值或列值集合應該保存在哪個分區(qū)中，而在HASH分區(qū)中，MYSQL自動完成這些工作。

lHash分區(qū)的目的是將數據均勻的分布到預先定義的各個分區(qū)中

  4. Key分區(qū)

l與hash相似，不同在于hash分區(qū)是用戶自定義函數進行分區(qū)，而key分區(qū)使用mysql數據庫提供的函數進行分區(qū)，不同的存儲引擎，使用的函數可能不同

  5. Columns分區(qū)

l從mysql5.5開始支持，可以視為range和list分區(qū)的進化，columns分區(qū)可以直接使用非×××數據進行分區(qū)

lCOLUMNS分區(qū)支持以下數據類型：

  所有×××，如INTSMALLINT TINYINT BIGINT。FLOAT和DECIMAL則不支持。

  日期類型，如DATE和DATETIME。其余日期類型不支持。

  字符串類型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT類型不支持。

  COLUMNS可以使用多個列進行分區(qū)。

  6. Mysql分區(qū)是可以指定存儲位置的，通過查看幫助，我們可以得到更多關于創(chuàng)建表的參數

例：創(chuàng)建分區(qū)表，并且將數據文件存放到其他位置，innodb存儲引擎，數據文件和索引文件是在一起的，所以只需要指定數據文件的位置

  7. 其實hash和key也是根據取余進行分區(qū)運算

    比如分成4個分區(qū)

四、 Mysql分區(qū)與分表的區(qū)別

  1. 實現(xiàn)方式

• 分表是真的將一個表分成多個小表

• 分區(qū)只是將數據分區(qū)，而分區(qū)后的表還是一張表，數據處理還是由自己完成

  2. 數據處理

• 分表后，數據都是存放在分表里，總表只是一個外殼，存取數據發(fā)生在一個一個的分表里面。

• 分區(qū)，不存在分表的概念，分區(qū)只不過把存放數據的文件分成了許多小塊，分區(qū)后的表呢，還是一張表，數據處理還是由自己來完成。

  3. 提高性能

• 分表后，單表的并發(fā)能力提高了，磁盤I/O性能也提高了，重點是存取數據時，如何提高mysql并發(fā)能力。

• 分區(qū)，主要是想突破磁盤I/O瓶頸，想提高磁盤的讀寫能力，來增加mysql性能。

  4. 實現(xiàn)難易

• 分表除了用marge存儲引擎分表對程序代碼是透明的外，其他的大部分都不是

• 分區(qū)實現(xiàn)是比較簡單的，建立分區(qū)表，根建平常的表沒什么區(qū)別，并且對開代碼端來說是透明的。

五、 分區(qū)和分表的聯(lián)系

• 都能提高mysql的性高，在高并發(fā)狀態(tài)下都有一個良好的表現(xiàn)

• 分表和分區(qū)不矛盾，可以相互配合的，對于那些大訪問量，并且表數據比較多的表，我們可以采取分表和分區(qū)結合的方式，訪問量不大，但是表數據很多的表，我們可以采取分區(qū)的方式等

• 分表技術是比較麻煩的，需要手動去創(chuàng)建子表，app服務端讀寫時候需要計算子表名。采用merge好一些，但也要創(chuàng)建子表和配置子表間的union關系。

• 表分區(qū)相對于分表，操作方便，不需要創(chuàng)建子表。

MySQL性能優(yōu)化及分區(qū)、分表的詳細步驟就先給大家講到這里，對于其它相關問題大家想要了解的可以持續(xù)關注我們的行業(yè)資訊。我們的板塊內容每天都會捕捉一些行業(yè)新聞及專業(yè)知識分享給大家的。