身為QA，你是否也了解SQL性能優化？

作為一名QA，在對系統進行性能測試/功能性並發處理測試時，應該經常會遇到介面響應慢、並發處理報錯等問題。當你遇到這些問題時，有深究過其原因么，還是只是丟給RD去排查？

GIF

相信作為一名資深QA或正在努力變成資深QA的QA，在遇到此類問題時，至少會自己先去研究一番，嘗試定位問題原因。這樣做，不僅能讓合作的RD對你刮目相看（取得對方更多的信任，讓雙方的合作更加順暢），更重要的還是能增加個人經驗的積累。

引起資料庫性能瓶頸的原因有很多，排除硬體資源原因、程序BUG外，很多時候就是因為SQL處理慢造成的。怎麼知道是SQL處理慢？如何優化？有沒有什麼輔助工具？我們一起來看下吧~

01

怎麼知道SQL處理慢？

現在大部分資料庫都提供了性能分析工具。例如Oracle中自帶的awr工具，在報告中可展示出SQL語句處理時間從高到低的排行。而在MySQL中就要自己開啟慢日誌記錄加以分析，我們一起來看下mysql慢查詢相關操作：

我們可以通過mysql配置文件或SQL命令方式，對慢查詢參數進行設置。

1. 配置文件方式：linux下mysql配置文件默認路徑為/etc/my.cnf，慢查詢相關的參數主要有以下三個：

slow_query_log： 慢查詢開關，默認是關閉的，修改為1表示開啟

slow_query_log_file： 慢查詢日誌存放的位置

long_query_time：查詢超過多少秒才記錄,默認10s

2. 命令行方式查看和修改（如下命令都需要管理員賬戶）：

l 開啟慢查詢命令：set global log_slow_queries = on;

l 查看慢查詢參數：show variables like 『long_query_time』;

l 修改慢查詢時間，即超過指定秒數就會被認為慢查詢。設置命令：set global long_query_time =1;

注意：用命令設置的，會立即生效，不用重啟mysql服務。但重啟mysql服務後就會失效。

l 查看慢查詢日誌存放路徑，命令： show variables like "slow_query_log_file"

l 更改慢查詢日誌的存儲方式，因為默認是將慢查詢日誌記錄到文件類中的，如果你想將這些日誌記錄到資料庫表中，使用命令：set global log_output=』TABLE』。

注意：實際應用中，出於性能考慮，一般都會保存在文件中

以上，我們把MySQL資料庫的慢查詢日誌的的設置給各位簡單介紹了一下。下面我們來簡單模擬一下，使用MySQL資料庫的sleep(N)函數來假裝讓執行語句停留10秒，最後我們可以到記錄慢查詢日誌的數據表中看到這條記錄。

02

二、找到了慢查詢語句，如何優化？

通過慢查詢日誌監控到的SQL，並非只有SELECT語句，對資料庫的CURD操作，只要超過了慢查詢設置的閾值都會記錄到日誌中。只是在實際WEB應用中，查詢操作通常能佔到系統的80%以上。眾所周知，正確使用索引，能大大提高查詢效率，而關於MySQL索引原理是比較枯燥的東西，對於我們QA而言大家只需要有一個感性的認識，並不需要理解得非常透徹和深入。因此，如下主要從索引建立和使用方面著手進行SQL的優化探索：

首先，我們來看看建索引的一些原則（這部分對於QA來說可以只作為了解）：

1. 選擇唯一性索引，唯一性索引的值是唯一的，可以更快速的通過該索引來確定某條記錄。例如，學生表中學號是具有唯一性的欄位。為該欄位建立唯一性索引可以很快的確定某個學生的信息。如果使用姓名的話，可能存在同名現象，從而降低查詢速度。

2. 為經常需要排序、分組和聯合操作的欄位建立索引，經常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的欄位，排序操作會浪費很多時間。如果為其建立索引，可以有效地避免排序操作。

3. 為常作為查詢條件的欄位建立索引，如果某個欄位經常用來做查詢條件，那麼該欄位的查詢速度會影響整個表的查詢速度。因此，為這樣的欄位建立索引，可以提高整個表的查詢速度。

4. 限制索引的數目，索引的數目不是越多越好。每個索引都需要佔用磁碟空間，索引越多，需要的磁碟空間就越大。修改表時，對索引的重構和更新很麻煩。越多的索引，會使更新表變得很浪費時間。

5. 盡量選擇區分度高的列作為索引,區分度的公式是count(distinct col)/count(*)，表示欄位不重複的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別欄位可能在大數據面前區分度就是0，那可能有人會問，這個比例有什麼經驗值嗎？使用場景不同，這個值也很難確定，一般需要join的欄位我們都要求是0.1以上，即平均1條掃描10條記錄

6. 盡量的擴展索引，不要新建索引。比如表中已經有a的索引，現在要加(a,b)的索引，那麼只需要修改原來的索引即可。

此外，掌握常用的索引的使用原則，有助於QA 儘早（在程序設計或單元測試階段）發現代碼中存在的性能隱患。至少在性能測試階段，找到慢查詢語句後，通過利用這些規則進行輔助分析，可協助RD大致定位資料庫性能瓶頸所在。而常用索引使用規則包括：

1. 最左前綴匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到範圍查詢(>、

建立(a,b,c,d)順序的索引，a = 1 and b = 2 and c > 3 and d = 4 如果，d的索引是失效的；如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整；b=1,c=2,d=3，少了a後面的索引都是不生效的；

2. 索引列不參與計算原則，保持列「乾淨」，比如from_unixtime(create_time) = 』2014-05-29』就不能使用到索引，原因很簡單，b+樹中存的都是數據表中的欄位值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(』2014-05-29』);

3. 使用短索引原則，對串列進行索引，如果可能應該指定一個前綴長度。例如，如果有一個CHAR(255)的列，如果在前10個或20個字元內，多數值是唯一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和I/O操作。

4. 索引列排序原則，MySQL查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此資料庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創建聯合索引。

5. like語句操作，一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like 「%aaa%」 不會使用索引而like 「aaa%」可以使用索引。

6. 減少or使用，要想使用or，又想讓索引生效，只能將or條件中的每個列都加上索引

7. 盡量避免數據類型轉換，例如：列類型是字元串，那一定要在條件中將數據使用引號引用起來,否則不使用索引

8. 如果mysql估計使用全表掃描要比使用索引快,則不使用索引，這條是mysql本身的機制。

9. 盡量避免在where子句使用!= 、、is null判斷。

03

有沒有輔助工具？

有！給大家推薦一款SQL查詢優化神器「explain」，為什麼說是神器呢？那是因為他使用方便、功能強大。關於Explain的使用，後續文章進行介紹哦~

結束語

關於慢查詢、索引、explain的每一項，網上有很多精華的資料進行介紹。在此，我僅從個人以往工作經驗角度出發，把這些相關的資料給串聯起來，給大家提供一個學習的思路。希望能對身為QA的你能有所幫助。

Qtest是360旗下的專業測試團隊！

是WEB平台部測試技術平台化、效率化的先鋒力量！

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！