資料庫系列：MySQL慢查詢分析和效能最佳化

Brand發表於2023-02-10
資料庫MySql

1 背景

我們的業務服務隨著功能規模擴大，使用者量擴增，流量的不斷的增長，經常會遇到一個問題，就是資料儲存服務響應變慢。
導致資料庫服務變慢的誘因很多，而RD最重要的工作之一就是找到問題並解決問題。
下面以MySQL為例子，我們從幾個角度分析可能產生原因，並討論解決的方案。

2 定位慢查詢的原因並最佳化

2.1 慢查詢的分析

開啟SlowLog，預設是關閉的，由引數slow_query_log決定，在MySQL命令終端中輸入下面的命令：

# 是否開啟，這邊為開啟，預設情況下是off
set global slow_query_log=on;

# 設定慢查詢閾值，單位是 s，預設為10s，這邊的意思是查詢耗時超過0.5s，便會記錄到慢查詢日誌裡面
set global long_query_time=0.5;

# 確定慢查詢日誌的檔名和路徑
mysql> show global variables like 'slow_query_log_file';
+---------------------+-------------------------------------------------------+
| Variable_name       | Value                                                 |
+---------------------+-------------------------------------------------------+
| slow_query_log_file | /usr/local/mysql/data/MacintoshdeMacBook-Pro-slow.log |
+---------------------+-------------------------------------------------------+
1 row in set (0.00 sec)

# 檢查慢查詢的詳細指標，可以看到下面 slow_query_log = ON，long_query_time = 0.5 ，都是因為我們調整過的
mysql> show global variables like '%quer%';
+----------------------------------------+-------------------------------------------------------+
| Variable_name                          | Value                                                 |
+----------------------------------------+-------------------------------------------------------+
| binlog_rows_query_log_events           | OFF                                                   |
| ft_query_expansion_limit               | 20                                                    |
| have_query_cache                       | NO                                                    |
| log_queries_not_using_indexes          | OFF                                                   |
| log_throttle_queries_not_using_indexes | 0                                                     |
| long_query_time                        | 0.500000                                             |
| query_alloc_block_size                 | 8192                                                  |
| query_prealloc_size                    | 8192                                                  |
| slow_query_log                         | ON                                                   |
| slow_query_log_file                    | /usr/local/mysql/data/MacintoshdeMacBook-Pro-slow.log |
+----------------------------------------+-------------------------------------------------------+
10 rows in set (0.01 sec)

配置好之後，就會按照閾值預設把慢查詢日誌收集下來，可以到對應的目錄下分析具體的慢請求原因。

2.2 使用Explain進行查詢語句分析

2.2.1 分析過程舉例

很多時候我們在評審RD同學程式碼和SQL指令碼的時候，上下文和使用環境不瞭解，不能做出很準確的判斷。
這時候使用Explain分析SQL的執行計劃就顯得非常有用，拿到具體環境中Run一下就能看出很多問題。
舉個例子：
模擬一個千萬級別的僱員表，我們在沒有做索引的欄位上做一下查詢看看，在500W資料中查詢一個名叫LsHfFJA的員工，消耗 2.239S ，獲取到一條id為4582071的資料。
image

再看看他的執行計劃，掃描了4952492 條資料才找到該行資料：

mysql> explain select * from emp where empname='LsHfFJA';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL | 4952492 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set

這就是無索引或者索引不合理的結果，這個時候我們就可以根據實際情況進行查詢最佳化了。

2.2.2 Explain需要關注的指標

比較核心要關注的欄位一般有 select_type、type、possible_keys、key、rows、Extra等
我們來一個個說明：

  • select_type：代表表示查詢中每個select子句的型別，是簡單查詢還是聯合查詢還是子查詢，一目瞭然。我們們上面的例子是SIMPLE，代表簡單查詢，其他列舉參考下列表格：
select_type的值 解釋
SIMPLE 簡單查詢（不使用關聯查詢或子查詢）
PRIMARY 如果包含關聯查詢或者子查詢，則最外層的查詢部分標記primary
UNION 聯合查詢（UNION）中第二個及後面的查詢
DEPENDENT UNION UNION中的第二個或後面的SELECT語句，取決於外面的查詢
UNION RESULT UNION的結果，union語句中第二個select開始後面所有select
SUBQUERY 字查詢中的第一個擦訊
DEPENDENT SUBQUERY 子查詢中的第一個查詢，並且依賴外部查詢
DERIVED 派生表的SELECT, FROM子句的子查詢
MATERIALIZED 被物化的子查詢
UNCACHEABLE SUBQUERY 一個子查詢的結果不能被快取，必須重新評估外連結的第一行
  • type：表示MySQL在表中查詢所需資料的方式，也稱“訪問型別”，我們們上面的例子是All，代表全表掃描，是非常差的模式，其他列舉參考下列表格：
type的值 解釋
system 查詢物件表只有一行資料，且只能用於MyISAM和Memory引擎的表，這是最好的情況
const 基於主鍵或唯一索引查詢，最多返回一條結果
eq_ref 類似ref，區別就在使用的索引是唯一索引，對於每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連線中使用primary key或者 unique key作為關聯條件
ref 表示上述表的連線匹配條件，即哪些列或常量被用於查詢索引列上的值
fulltext 全文檢索
ref_or_null 表連線型別是ref，但進行掃描的索引列中可能包含NULL值
index_merge 利用多個索引
unique_subquery 子查詢中使用唯一索引
index_subquery 子查詢中使用普通索引
range 只檢索給定範圍的行，使用一個索引來選擇行
index Full Index Scan，index與ALL區別為index型別只遍歷索引樹
ALL Full Table Scan， MySQL將遍歷全表以找到匹配的行
  • possible_keys：應該或建議使用的索引

表示MySQL能使用哪個索引在表中找到記錄，查詢涉及到的欄位上若存在索引，則該索引將被列出，但不一定被查詢使用。這個趨向於指導性作用。

  • key：實際使用的索引，沒有的情況下為NULL

顯示MySQL在查詢中實際使用的索引，若沒有使用索引，顯示為NULL

  • rows：預估掃描了了多少行，我們們上面的例子 4952492 ，非常不合理。
    表示MySQL根據表統計資訊及索引選用情況，估算的找到所需的記錄所需要讀取的行數。基本表現為實際掃描過的行數。

3 一些使用上的規範

3.1 分析是否有不合理的查詢

★ 以下是我們團隊的准入規範，也是CodeReview 標準。

  • 儘量避免使用select *，join語句使用select * 可能導致只需要訪問索引即可完成的查詢需要回表取數。
    一種是可能取出很多不需要的資料，對於寬表來說，這是災難；一種是儘可能避免回表，因為取一些根本不需要的資料而回表導致效能低下，是很不合算。
  • 嚴禁使用select * from t_name，不加任何where條件，道理一樣，這樣會變成全表全欄位掃描。
  • MySQL中的text型別欄位儲存：
    • 不與其他普通欄位存放在一起,因為讀取效率低，也會影響其他輕量欄位存取效率。大寬表、大欄位表，整體效能也不好。
    • 如果不需要text型別欄位，又使用了select *，會讓該執行消耗大量io，效率也很低下
  • 在取出欄位上可以使用相關函式，但應儘可能避免出現 now() , rand() , sysdate() 等不確定結果的函式，在Where條件中的過濾條件欄位上嚴禁使用任何函式，包括資料型別轉換函式。大量的計算和轉換會造成效率低下，這個在索引那邊也描述過了。
  • 分頁查詢語句全部都需要帶有排序條件 , 否則很容易引起亂序
  • 用in()/union替換or，效率會好一些，並注意in的個數小於300
  • 嚴禁使用%字首進行模糊字首查詢。
-- 如下，這種查詢會導致掃描表：
select a,b,c from t_name where a like '%name';
-- 可以使用%模糊字尾查詢如：
select a,b from t_name where a like 'name%';
  • 儘量避免使用子查詢，可以把子查詢最佳化為join操作，通常子查詢在in子句中，且子查詢中為簡單SQL(不包含union、group by、order by、limit從句)時，才可以把子查詢轉化為關聯查詢進行最佳化。子查詢效能差的原因：
    • 子查詢的結果集無法使用索引，通常子查詢的結果集會被儲存到臨時表中，不論是記憶體臨時表還是磁碟臨時表都不會存在索引，所以查詢效能會受到一定的影響；
    • 特別是對於返回結果集比較大的子查詢，其對查詢效能的影響也就越大；
    • 由於子查詢會產生大量的臨時表也沒有索引，所以會消耗過多的CPU和IO資源，產生大量的慢查詢。
  • 在多表join中，儘量選取結果集較小的表作為驅動表，來join其他表。
  • 分頁查詢，當limit起點較高時，可先用過濾條件進行過濾，如下。原理參考這篇
-- 如 
select a,b,c from t1 limit 10000,20;
-- 最佳化為:
select a,b,c from t1 where id>10000 limit 20;

3.2 檢查是否有不合理的索引使用

建議參考筆者這篇《構建高效能索引（策略篇）》，比較完整

  • 索引區分度（> 0.2）

索引必須建立在索引選擇性（區分度）較高的列上，選擇性的計算方式為:

selecttivity = count(distinct c_name)/count(*) ;

如果區分度結果小於0.2，則不建議在此列上建立索引，否則大機率會拖慢SQL執行

  • 遵循最左字首，將索引區分度最高的放在左邊

對於確定需要組成組合索引的多個欄位，設計時建議將選擇性高的欄位靠前放。使用時，組合索引的首欄位，必須在where條件中，且需要按照最左字首規則去匹配。
正確理解和計算索引欄位的區分度，文中有計算規則，區分度高的索引，可以快速得定位資料，區分度太低，無法有效的利用索引，可能需要掃描大量資料頁，和不使用索引沒什麼差別。

  • 禁止使用外來鍵，可以在程式級別來約束完整性

  • varchar、text型別欄位如果需要建立索引，必須使用字首索引。

字首索引計算公式如下，calcul_len 是數字，長度為1 ~ c_name欄位的最長值，可以逐一比較，對比區分度最高的出來
正確理解和計算字首索引的欄位長度，文中有判斷規則，合適的長度要保證高的區分度和最恰當的索引儲存容量，只有達到最佳狀態，才是保證高效率的索引。

select count(distinct left(`c_name`,calcul_len))/count(*) from t_name;
  • 單張表的索引數量理論上應控制在5個以內。經常有大批次插入、更新操作表，應儘量少建索引，索引建立的原則理論上是多讀少寫的場景。
  • ORDER BY，GROUP BY，DISTINCT的欄位需要新增在索引的後面，形成覆蓋索引
  • 聯合索引注意最左匹配原則：查詢時必須按照從左到右的順序匹配，MySQL會一直向右匹配索引直到遇到範圍查詢(>、<、between、like)然後停止匹配。如：
-- 如果建立(depno,empname,job)順序的索引，job是用不到索引的。
depno=1 and empname>'' and job=1
  • 應需而取策略，查詢記錄的時候，不要一上來就使用*，只取需要的資料，可能的話儘量只利用索引覆蓋，可以減少回表操作，提升效率。
  • 正確判斷是否使用聯合索引，應避免索引下推（IPC），減少回表操作，提升效率。
  • 避免索引失效的原則：禁止對索引欄位使用函式、運算子操作，會使索引失效。這是實際上就是需要保證索引所對應欄位的”乾淨度“。
  • 避免非必要的型別轉換，字串欄位使用數值進行比較的時候會導致索引無效。
  • 模糊查詢'%value%'會使索引無效，變為全表掃描，因為無法判斷掃描的區間，但是'value%'是可以有效利用索引。
  • 索引覆蓋排序欄位，這樣可以減少排序步驟，提升查詢效率
  • 儘量的擴充套件索引，非必要不新建索引。比如表中已經有a的索引，現在要加(a,b)的索引，那麼只需要修改原來的索引即可。
    舉例子：比如一個品牌表，建立的的索引如下，一個主鍵索引，一個唯一索引
PRIMARYKEY (`id`),
UNIQUEKEY `uni_brand_define` (`app_id`,`define_id`)

實際場景中，建議程式碼交叉評審，當你同事業務程式碼中的檢索語句如下的時候，應建議調整：

select brand_id,brand_name from ds_brand_system where status=? and define_id=? and app_id=?

建議改成如下：

select brand_id,brand_name from ds_brand_system where app_id=? and define_id=? and status=?

雖然說 MySQL的查詢最佳化器會根據實際索引情況進行順序最佳化，所以這邊不做強制。但是同等條件下還是按照順序進行排列，比較清晰，並且節省查詢最佳化器的處理。

4 總結

這邊僅僅是從查詢語句的角度進行分析，實際上快取服務變慢的可能性很多，不僅僅是慢查詢怎麼分析（Slow Log、Explain命令）。還應該全面的分析原因，並給出處理方案，如 分析SQL指令碼合理性、建立索引或最佳化索引、讀寫分離、垂直+水平分割槽）、多讀少寫/冷資料 做快取、最佳化資料庫的鎖競爭、資料庫配置調優、硬體資源升級 等等，後面幾篇我們慢慢說。

相關文章