MySQL性能優化及分區、分表的詳細步驟

本文主要給大家簡單講講MySQL性能優化及分區、分表的詳細步驟，相關專業術語大家可以上網查查或者找一些相關書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望MySQL性能優化及分區、分表的詳細步驟這篇文章可以給大家帶來一些實際幫助。

一、 分表

  1. 分表簡介

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

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

  2. 分表類型

 分為垂直切分和水平切分

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

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

  3. 分表的方式

   1） Mysql集群

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

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

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

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

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

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

  4. merge存儲引擎分表

     創建子表

     創建主表

    開始分表

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

     驗證

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

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

二、 分區

  1. 什么是分區

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

  2. 分區形式

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

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

   3. 查看mysql是否支持分區技術

     本例是5.7

     首先創建表并為其創建分區，并插入數據，以作測試

     下圖是插入的數據

     去查看物理文件

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

     從某個分區中查詢數據

     添加一個分區

     合并分區

  4. 對比創建分區和沒創建分區的性能

     首先創建一個表tab1不做分區

     創建表tab2按日期的年份拆分

     創建一個存儲過程，循環向表中存入足夠大的數據，用于測試

     執行存儲過程

     將tab1的數據插入到tab2中

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

     通過explain語句分析執行情況

     未分區的表

     已分區的表

    為兩個表創建索引

     再次進行測試

三、 Mysql的分區類型

   在創建表的時候使用：partition by  類型（字段），定義分區類型及其使用的字段

  1. Range

l  基于一個連續區間的列值，把多行分配給分區，區間不能重疊

l  使用values  less  than操作符定義分區

l  一般最后要創建一個maxvalue分區，用于存放和之前區間不匹配的值

  2. List分區

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

l  使用：Values  in，操作符定義分區

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

  3. Hash分區

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

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

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

  4. Key分區

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

  5. Columns分區

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

lCOLUMNS分區支持以下數據類型：

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

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

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

  COLUMNS可以使用多個列進行分區。

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

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

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

    比如分成4個分區

四、 Mysql分區與分表的區別

  1. 實現方式

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

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

  2. 數據處理

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

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

  3. 提高性能

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

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

  4. 實現難易

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

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

五、 分區和分表的聯系

• 都能提高mysql的性高，在高并發狀態下都有一個良好的表現

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

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

• 表分區相對于分表，操作方便，不需要創建子表。

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