天天看點
傳回

MySQL性能優化小技巧（必會）

文章目錄

    • 索引管理優化
      • 整合DDL語句
      • 去除重複索引
      • 删除不用的索引
      • 監控無效的索引
    • 索引列的改進
      • 資料類型的優化
        • BIGINT 和INT的差別
        • DATETIME和TIMESTAMP的差別
        • 什麼時候用ENUM
        • 什麼時候使用NULL或NOT NULL
        • 隐含的變換
      • 列的類型
        • IP位址
        • MD5
    • 其他SQL優化
      • 減少SQL語句
        • 删除内容重複的SQL語句
        • 删除重複執行的SQL語句
        • 删除不必要的SQL語句
        • 緩存SQL結果
        • MySQL緩存
        • 應用程式緩存
      • 簡化SQL語句
        • 改進列
        • 改進連接配接操作
        • 重寫子查詢
        • 了解視圖（views）所帶來的影響

除了添加索引帶來的sql性能提升外，我們還可以：

  • 去除重複的索引
  • 找到沒有被使用的或者無效的索引
  • 改進索引
  • 減少SQL語句
  • 簡化SQL語句
  • 緩存選項

索引管理優化

索引的管理過程，即索引建立和維護的方式，能夠影響到SQL語句的性能。

整合DDL語句

在将索引添加到MySQL表的過程中我們知道DDL語句是阻塞性的。

由于ALTER語句的阻塞性影響，執行ALTER語句時需要為表建立一個新的副本。

在ALTER大型表時，這個操作将消耗大量的時間和磁盤存儲空間。

但是，在Mysql5.1及以後版本，innoDB插件及第三方存儲引擎已經幫我們解決了這個問題，因為這時ALTER已經不再執行全表複制操作。

把多條ALTER語句整合成一條SQL語句是一種簡單的優化改進。舉個例子：

現在我們需要添加一個新的索引、修改一個索引以及添加新的一列，可以分别運作下面這些SQL指令：

ALTER TABLE test ADD index(username);
ALTER TABLE test DROP index name,ADD index name(last_name,first_name);
ALTER TABLE test ADD COLUMN last_visit DATE NULL;

也就是說需要執行三條SQL語句，如果我們把它合成一條：

這就是最簡單的優化，隻不過和減少和sql執行數，我們就能夠大幅度提升管理任務的性能。

去除重複索引

重複的索引有兩個主要的影響：

  1. 所有DML語句都會運作得更慢，因為需要做更多工作來保持資料和索引的一緻性。
  2. 資料庫的磁盤占用量将會更大，這将導緻備份和恢複需要的時間增加。

我們知道innoDB下主碼是不需要索引的（主碼就是隐藏索引），我們做一個實驗：

CREATE TABLE test(
id INT  unsigned not null,
first_name varchar(30) not null,
last_name varchar(30) not null,
joined date not null,
primary key(id),
index(id)
);

這張表中id列上定義的索引是一個重複索引，應該被移除。

怎麼快速找出重複索引呢？

當一個給定索引的最左邊部分被包含在其他索引中時就會産生重複索引。

重建立一張表：

CREATE TABLE test(
id INT  unsigned not null,
first_name varchar(30) not null,
last_name varchar(30) not null,
joined date not null,
primary key(id),
index name1(last_name),
index name2(last_name,first_name)
);

name1這個索引是多餘的，因為此索引所在的列已經被包含在索引name2的最左邊部分裡面了。

我們也可以借助Maatkit的mk-duplicate-index-checker指令來檢查重複索引，具體怎麼用我就不在這裡寫了。

删除不用的索引

重複的索引是一定不會被使用到的，除此之外有些索引雖然沒有重複，但也有可能不會被使用，我們要做的就是找出來并删除它。

在Google提供的MySQL更新檔（下載下傳網址：http://code.google.com/p/google-mysql-tools/wiki/Mysql5Patches）中提供了一個指令：show index_statistics ，這個指令可以監控SQL執行情況并找到沒有被使用的索引。

如果你沒有這個更新檔，你就要先收集所有執行過的SQL語句，再使用這些SQL語句為所有SQL語句捕捉和聚合QEP，然後對每個表進行分析，才能得到未使用的索引的資訊。

監控無效的索引

當定義多列索引時，一定要注意确定所指定的每一列是否真的有效。

我們可以通過分析指定表上的所有SQL語句的key_len列（在執行語句前全部加上EXPLAIN）來找到那些可能包含沒有使用到的列的索引，然後更新索引（把沒用到的列剔除掉）。

索引列的改進

我們可以使用特定的資料類型或者列類型來使用更小的磁盤空間（節省大量的磁盤空間）。

當磁盤空間消耗變少時我們就能減少I/O的開銷并且使得更多的索引資料可以被打包裝載進入可用的系統記憶體中。

資料類型的優化

BIGINT 和INT的差別

當一個主碼被定義為BIGINT AUTO_INCREMENT資料類型時，我們知道一個INT UNSIGNED AUTO_INCREMENT資料類型能夠支援的最大值是43億。

也就是說，除非你要存的數可能大于43億，否則請你使用INT，把BIGINT改為INT能為我們降低50%的存儲消耗，即從8位元組變為4位元組。

DATETIME和TIMESTAMP的差別

記錄一個日期或者時間的值時可以用一個紀元值嗎？這關系到我們使用哪個類型，如果存儲的值是紀元值（從1997年1月1日到現在的秒數），我們就用TIMESTAMP,否則我們隻能使用DATETIME，你必須記住：DATETIME占8位元組，而TIMESTAMP占4位元組。

是以，能用TIMESTAMP就用它。

TIMESTAMP是有缺點的：預設值是0，不支援null，是以這個列必須有值！！

什麼時候用ENUM

ENUM适合存儲靜态的代碼值。比如性别：

gender1 varchar(6) not null
gender2 ENUM('male','female') not null

ENUM帶來的優點：

  1. 一個ENUM列利用隐含的檢查限制提供了額外的資料完整性支援。
  2. 一個ENUM列僅使用1位元組來存儲255個不同的值
  3. ENUM列的值更具有可讀性。

如果是MYSQL5.1以前，修改ENUM值的範圍執行的是ALTER DDL語句，會阻塞其他語句的執行，但5.1以後經過了優化則不會阻塞其他語句執行。

什麼時候使用NULL或NOT NULL

除非能夠确定這個列允許出現一個未知的值，否則最好把它聲明為NOT NULL。

如果我們聲明為NOT NULL，那麼這個列在一個索引中時可以占用更小的空間并簡化索引的處理過程（NULL!=NULL你懂的），還能給該列的資料添加額外的完整性限制檢查保證所有行在此列上都有資料。

隐含的變換

當我們做表連接配接時我們要保證正兩張表的連接配接字段資料類型相同，因為如果不相同是允許進行類型轉換的，而類型轉換會為我們帶來額外的開銷。比如INT SIGNED和INT UNSIGEND就會觸發類型轉換。

列的類型

當這些列屬于某個索引時，如果我們能改進這些列的資料類型将會帶來更高效的存儲。

IP位址

我們存儲IPv4位址時往往使用INT UNSIGNED資料類型，而這個類型占4位元組。而不會SQL優化的人則會将列定義為varchar(15)，這樣平均占用12位元組。你會發現我們節約了2/3的列資料占用空間。

為此，我們可以通過INET_ATON()和INET_NTOA()函數進行字元串和數字值之間的轉換。

MySQL性能優化小技巧（必會）
MySQL性能優化小技巧（必會）

這個優化技巧隻适用于IPv4。因為IPv6是128bit（16位元組），我們隻能夠用BINARY(16)來存儲。

MD5

用char(32)來存儲MD5是一個常見的優化技巧。而沒學過優化的一定用的varchar(32)（PS：我就是），varchar(32)會花費額外的不必要的開銷。

我們還可以進一步優化：用BINARY(16)來存儲，對長度為32的MD5使用UNHEX()函數壓縮，HEX()函數解壓。

MySQL性能優化小技巧（必會）

其他SQL優化

對關系型資料庫而言最有效的SQL優化方法就是完全删除不需要執行的SQL語句。

對于一個高度優化的應用程式而言，占總執行時間最大比重的是網絡開銷。

去除SQL語句能夠減少應用程式的處理時間。

減少SQL語句

有一些簡單的技巧可以用來減少SQL語句：

  1. 删除内容重複的SQL語句
  2. 删除重複執行的SQL語句
  3. 删除不必要的SQL語句
  4. 緩存SQL語句的傳回結果

删除内容重複的SQL語句

在業務開發中我們往往會在同一個的DAO方法中發現一些重複的SQL語句，我們可以通過啟動全面查詢日志來分析所有執行的SQL語句并找出必要的，對于不必要的就删除掉。

删除重複執行的SQL語句

N+1問題是外層循環為每一行都生成一個SQL語句導緻的。通過集合處理我們能做到一條SQL語句實作每次一塊，減少重複執行。

MySQL性能優化小技巧（必會）
MySQL性能優化小技巧（必會）
MySQL性能優化小技巧（必會）

使用應用程式架構可能是造成不必要的重複SQL語句的主要原因（比如hibernate，你根本就不知道它是怎麼執行查詢的，因為它已經封裝好了，是以hibernate的優化是很難的，除非你對它的源碼十分熟悉能修改它的源碼才能做到優化）。

N+1問題的複現：

MySQL性能優化小技巧（必會）

優化解決：

MySQL性能優化小技巧（必會）

删除不必要的SQL語句

當應用程式不斷修改和增加功能時就可能會産生不必要的SQL語句，例如：

  • 不再需要的選擇資訊。
  • 僅僅在給定函數的某些路徑上用到的選擇資訊。
  • 可以從之前的SQL語句中選擇的資訊。

緩存SQL結果

當普通資料的變化率相對較低時，緩存SQL結果能夠為你的應用程式帶來性能提升和對資料庫伺服器的可擴充性，比如hibernate的懶加載機制就是這麼做的。

MySQL緩存

MySQL查詢緩存能夠為讀操作頻繁的環境帶來性能提升，且在不需要其他應用程式開銷的情況下就可以實作：

MySQL性能優化小技巧（必會）
MySQL性能優化小技巧（必會）

可以看出第二個查詢減少了很多執行工作。

由于查詢緩存的粗略性，對給定表的任意資料的改變都會導緻所有使用那個表的緩存的SQL語句無效。

是以如果寫操作比讀操作頻繁，最好不要開緩存。

應用程式緩存

給應用程式添加緩存（ehcache或者redis）能夠大幅度減少不必要的SQL語句執行。

我們可以把特定SQL語句緩存在記憶體或者本地檔案中，或者在應用程式對象關系映射（ORM）層引入延遲對象執行個體化（hibernate的懶加載機制）.

簡化SQL語句

把該删的都删了之後，我們還可以簡化SQL語句。

簡化語句時需要考慮一下問題：

  • 查詢中所有的列都是必須的嗎？
  • 表的連接配接操作能被省去嗎？
  • 在給定的函數中，連接配接或where條件限制對其他SQL語句是必要的嗎？

改進列

簡化過程的一個重要要求就是按順序截取指定執行函數中所有SQL語句。

使用采樣過程不能找到所有可能的改進點。

MySQL性能優化小技巧（必會）

改進連接配接操作

MySQL性能優化小技巧（必會）
MySQL性能優化小技巧（必會）

重寫子查詢

在某些環境下，子查詢的性能比直接使用一般的表連接配接要慢得多：

MySQL性能優化小技巧（必會）

了解視圖（views）所帶來的影響

MySQL性能優化小技巧（必會）
SQL優化系列
上一篇: 小威老師腳手架解析七
下一篇: MySQL内置函數中的日期和時間函數詳解