Open in app

Sign in

Write

Sign in

透過索引優化來提高SQL效能

Nick Zhang
13 min readJun 15, 2019

--

透過explain來分析SQL的效能

explain select sum(amount) from customer a, payment b where 1=1 and a.customer_id = b.customer_id and email = 'JANE@mail.com'id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: PRIMARY
key: null
key_len: NULL
ref: NULL
rows: 583
Extra: Using where
  • id: select 查詢的序列號，包含一組數字，表示查詢中執行select子句或操作表的順序
▹id相同: 執行順序由上而下▹id不同: 如果是子查詢，id的序號會遞增，值愈大代表級別愈高，愈先執行▹id相同或不同: id相同可以認為是一組，從上往下順序執行；在所有組中，值愈大，愈先執行
  • select_type: 表示SELECT的類型
▹ SIMPLE: 簡單的select查詢，查詢中不包含子查詢或UNION▹ PRIMARY: 查詢中包含任何的子查詢的最外層查詢▹ SUBQUERY: 在select或where列表中包含了子查詢▹ DERIVED: 在FROM列表中包含的子查詢被標記為DERIVED(衍生)，MySQL會遞歸執行這些子查詢，把結果放在臨時表裡面▹ UNION: 若第二個SELECT出現在UNION之後，則被標記為UNION；若UNION包含在FROM子句的子查詢中，外層的SELECT則被標記為DERIVED▹ UNION RESULT: 從UNION表獲取結果的SELECT
  • table:輸出結果集的表
  • type: 表示MySQL在表中找到所需行的方式，或者叫連結類型，常見類型有
+-----+-------+-------+-----+--------+---------------+------+
| ALL | index | range | ref | eq_ref | const, system | NULL |
+-----+-------+-------+-----+--------+---------------+------+最好至最差依序是 system > const > eq_ref > ref > range > index > ALL
▹ system: 表只有一行紀錄(等於系統表)，是const類型的特例，平常不會出現可以忽略▹ const: 表示通過索引一次就找到，const用於比較primary key或unique index，因為只匹配一行數據，所以很快，如將primary key至於where列表中，MySQL就能將該查詢轉換為一個常量▹ eq_ref: 唯一性索引掃描，對於每個索引鍵值，表中只有一條紀錄匹配，常見於主鍵或唯一索引掃描，就是多表連接中使用primary key或者unique index來作為關聯條件▹ ref: 非唯一性索引掃描，返回匹配某個單獨值的所有行，本質上也是一種索引訪問，它返回所有匹配某個單獨值的行，然而它可能會找到多個符合條件的行，所以應該屬於查找和掃描的混合體▹ range: 只檢索給定範圍的行，使用一個索引來選擇行，key列顯示使用了哪個索引，一般就是在where子句出現了between，>，<，in等查詢，這種範圍掃描索引比全表掃描好，因為只需要開始於索引的某一點而結束於另一點，不用掃描全部索引▹ index: 索引全掃描，與ALL的區別為index類型只遍歷索引樹，通常比ALL快，因為索引文件通常比數據文件小，也就是說雖然ALL和index都是讀全表，但index是從索引中讀取，而ALL是從硬碟中讀取▹ ALL: 全資料表掃描，MySQL遍歷全表找到匹配的行
一般來說得保證查詢至少到range級別，最好能到req級別
  • possible_key: 表示查詢時可能使用的索引，查詢字段上若存在索引，則該索引將被列出，但不一定被查詢實際使用
  • key: 表示實際使用的索引
  • key_len:表示索引中使用到的字節數，在不損失精確性的情況下，長度愈短愈好，Key_len顯示的值為索引字段的最大可能長度，非實際使用長度
  • ref: 顯示索引的哪一列被使用了，如果可能的話是一個常數，哪些列或常量被用於查找列上的值
  • rows:根據索引選用狀況，估算出找到所需的紀錄需要讀取的行數
  • Extra:執行情況的說明和描述，包含不適合在其他列中顯示，但是對執行計畫是非常重要的額外資訊
▹ Using filesort: 說明MySQL會對數據使用一個外部的索引排序，而不是照表內的索引順序進行讀取，MySQL無法利用索引完成的排序操作稱為文件排序▹ Using temporary: 使用臨時表保存中間結果，MySQL對查詢排序時使用臨時表，常見於排序order by和分組查詢group by▹ Using index: 表示相應的select操作中使用了覆蓋索引(Covering Index)，避免訪問了表的數據行；如果同時出現Using where，表明索引被用來執行索引鍵值的查找；如果沒有同時出現Using where，表明索引用來讀取數據而非執行查找動作▹ Using where: 使用where過濾▹ Using join buffer: 使用了連接快取▹ impossible where: where子句的值總是false，不能用來獲取任何元組

索引優化提示

  • LEFT JOINT 條件用於確認如何從右表搜尋行，所以索引需建立在右表，RIGHT JOINT 反之
  • 全值匹配效果最好
  • 最佳左前綴法則
如果索引了多列，要遵守最佳左前綴法則，指的是查詢要從索引的最左前列開始並且不跳過中間的索引
  • 不在索引列上做任何操作
計算，函數，自動或手動類型操作，會導致索引失效而轉向全表掃描
  • 儲存引擎不能使用索引中範圍條件右邊的列
  • 盡量使用覆蓋索引
只訪問索引的查詢，索引列和查詢列一至，減少使用 SELETE *
  • MySQL在使用不等於判斷(!= or <>)的時候無法使用索引，會導致變成全表掃描
  • is null, is not null 也無法使用索引
  • like 以通配符開頭(%abc….)會導致索引失效，變成全表掃描
如果查詢條件需要使用 %abc% ，那就在 select 項目中使用覆蓋索引
  • 字符串不加單引號，索引會失效
  • 少用 or, 用它來連接時會導致索引失效
  • 提高 Order By速度:
▹ orderby 時，變免使用 select *，因為當query的字段大小總和小於max_length_for_sort_data而且排序字段不是TEXT|BLOB類型時，會用改進後的算法(單路排序)，反之會使用多路排序，兩種算法都有可能超出sort_buffer的容量，超出之後，會創建tmp文件進行合併排序，導致多次I/O，但是用單路排序風險會更大，所以要提高sort_buffer_size▹ 嘗試提高sort_buffer_size: 提高這個參數都會提高效率，當然要根據系統的能力去提高，因為這個參數是針對每個進程▹ 嘗試提高max_length_for_sort_data: 提高這過參數會增加改進算法的概率，但是如果設的太高，數據總容量超出sort_buffer_size的概率就會增加Key a_b_c(a, b, c):
▹ order by 能使用索引最左前綴:
- ORDERY BY a
- ORDERY BY a, b
- ORDERY BY a, b, c
- ORDERY BY a DESC, b DESC, c DESC▹ 如果where使用索引的最左前綴定義為常量，則order by能使用索引:
- WHERE a = const ORDER BY b, c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const AND b > const ORDER BY b, c▹ 不能使用索引進行排序:
- ORDER BY a ASC, b DESC, c DESC (排序不一致)
- WHERE g = const ORDER BY b, c (丟失a索引)
- WHERE a = const ORDER BY c (丟失b索引)
- WHERE a = const ORDER BY a, d (d不是索引的一部分)
- WHERE a in (...) ORDER BY b, c (對於排序來說，多個相等條件也是範圍查詢)

開啟慢查詢日誌

  • 查看系統是否開啟慢查詢日誌
SHOW VARIABLES LIKE '%slow_query_log%';+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/eacabaad3945-slow.log |
+---------------------+--------------------------------------+
  • 開啟慢查詢
SET GLOBAL slow_query_log=1;
  • 查看慢查詢閥值
SHOW VARIABLES LIKE '%long_query_time%';+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
  • 設定慢查詢閥值
SET GLOBAL long_query_time=3;

以上設定只能在當前的系統狀態下就改變，如果需要參數永久改變設定，需要在 my.cnf 做以下設定

slow_query_log=1
slow_query_log_file=/var/lib/mysql/eacabaad3945-slow.log
long_query_time=3
log_output=FILE
  • 顯示當前慢查詢LOG紀錄數
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
  • mysqldumpslow的幫助訊息:
▹ s: 表示按照何種方式排序
▹ c: 訪問次數
▹ l: 鎖定時間
▹ r: 返回紀錄
▹ t: 查詢時間
▹ al: 平均鎖定時間
▹ ar: 平均返回紀錄數
▹ at: 平均查詢時間
▹ t: 返回前面多少條的數據
▹ g: 後面搭配一個正則表達式，大小寫不敏感
  • mysqldumpslow 範例:
▹ mysqldumpslow -s r -t 10 slow.log
得到返回紀錄集最多的10個SQL▹ mysqldumpslow -s c -t 10 slow.log
得到訪問次數最多的10個SQL▹ mysqldumpslow -s t -t -g "left join" slow.log
得到按照時間排序的前10條裡面含有左連接的查詢語句▹ mysqldumpslow -s r -t 10 slow.log | more
建議在使用這些語句命令時，與more做使用，避免數據過多

使用 show profile

mysql 提供可以用來分析當前會話中語句執行的資源消耗情況，可以用於SQL的調優測量，預設條件下，參數是關閉狀態，且保存最近15次的運行結果

  • 查看 profiling 狀態
SHOW VARIABLES LIKE 'profiling';
  • 開啟 profiling
SET profiling=on;
  • 查看 show profiles 分析
show profiles;
  • 診斷單條SQL分析
show profile cpu, block io for query {id(show profiles table id)}type類型:
▹ all: 顯示所有信息
▹ block io: 顯示I/O信息
▹ context switches: 上下文切換相關信息
▹ cpu: CPU相關信息
▹ ipc: 發送和接收相關信息
▹ memory: 內存相關信息
▹ page faults: 頁面錯誤相關信息
▹ source: source_funciton, source_file, source_line相關信息
▹ swapes: 交換次數相關信息如果出現以下關鍵字，代表慢查詢會伴隨出現:
▹ converting HEAP to MyISAM: 查詢結果太大，內存不夠用
▹ creating tmp table: 創建臨時表
▹ copying to tmp table on disk: 把內存中臨時表複製到硬碟
▹ locked: 鎖表

查看鎖表情況

  • 查看有哪些表被加鎖
SHOW OPEN TABLES;
  • 分析表鎖定
SHOW STATUS LIKE 'table%';+------------------------+-----------+
| Variable_name          | Value     |
+------------------------+-----------+
| Table_locks_immediate  | 360       |
+------------------------+-----------+
| Table_locks_waited.    | 0         |
+------------------------+-----------+▹ Table_locks_immediate: 產生表級鎖定的次數，表示可以立即獲取鎖的查詢次數，每次加1▹ Table_locks_waited: 出現表級鎖定爭用而發生等待的次數(不能立即獲取鎖的次數，每等待一次鎖值加1)，此值高則說明存在著較嚴重的表級鎖爭用情況
  • 分析行鎖定
SHOW STATUS LIKE 'innodb_row_lock%';+--------------------------------+-----------+
| Variable_name                  | Value     |
+--------------------------------+-----------+
| Innodb_row_lock_current_waits  | 0         |
+--------------------------------+-----------+
| Innodb_row_lock_time           | 0         |
+--------------------------------+-----------+
| Innodb_row_lock_time_avg       | 0         |
+--------------------------------+-----------+
| Innodb_row_lock_time_max       | 0         |
+--------------------------------+-----------+
| Innodb_row_lock_waits          | 0         |
+--------------------------------+-----------+▹ Innodb_row_lock_current_waits: 當前正在等待鎖定的數量▹ Innodb_row_lock_time: 從系統啟動到現在鎖定總時間長度▹ Innodb_row_lock_time_avg: 每次等待所花平均時間▹ Innodb_row_lock_time_max: 從系統啟動到現在等待最長的一次所花的時間▹ Innodb_row_lock_waits: 從系統啟動到現在總共等待的次數
  • 加鎖優化建議
▹ 盡可能讓所有數據檢索都透過索引來完成，避免無索引行鎖變表鎖▹ 合理設計索引，盡量縮小鎖的範圍▹ 盡可能減少檢索條件，避免間隙鎖▹ 盡量控制事務大小，減少鎖定資源量和時間長度▹ 盡可能低級別事務隔離
MySQL

--

--

Nick Zhang
Nick Zhang

Written by Nick Zhang

19 Followers
9 Following

programmer

No responses yet

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams