教你如何定位及最佳化SQL陳述句的效能問題-知識星球

來自：Hollis（微訊號：hollischuang)

部分內容參考掘金-Hodu：https://juejin.im/post/5a52386d51882573443c852a

在現如今的軟體開發中，關係型資料庫是做資料儲存最重要的工具。無論是Oracale還是Mysql，都是需要透過SQL陳述句來和資料庫進行互動的，這種互動我們通常稱之為CRUD。在CRUD操作中，最最常用的也就是Read操作了。而對於不同的表結構，採用不同的SQL陳述句，效能上可能千差萬別。本文，就基於MySql資料庫，來介紹一下如何定位SQL陳述句的效能問題。

對於低效能的SQL陳述句的定位，最重要也是最有效的方法就是使用執行計劃。

執行計劃

我們知道，不管是哪種資料庫，或者是哪種資料庫引擎，在對一條SQL陳述句進行執行的過程中都會做很多相關的最佳化，對於查詢陳述句，最重要的最佳化方式就是使用索引。

而執行計劃，就是顯示資料庫引擎對於SQL陳述句的執行的詳細情況，其中包含了是否使用索引，使用什麼索引，使用的索引的相關資訊等。

（https://juejin.im/post/5a52386d51882573443c852a）

基本語法

explain select ...

mysql的explain 命令可以用來分析select 陳述句的執行效果。

除此之外，explain 的extended 擴充套件能夠在原本explain的基礎上額外的提供一些查詢最佳化的資訊，這些資訊可以透過mysql的show warnings命令得到。

mysql> explain extended select * from account;
******** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: account
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra:
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
*************1. row ***************************
Level: Note
   Code: 1003
Message: select `dbunit`.`account`.`id` AS `id`,`dbunit`.`account`.`name` AS `name` from `dbunit`.`account`
1 row in set (0.00 sec)

另外，對於分割槽表的查詢，需要使用partitions命令。

explain partitions select ...

執行計劃包含的資訊

不同版本的Mysql和不同的儲存引擎執行計劃不完全相同，但基本資訊都差不多。mysql執行計劃主要包含以下資訊:

（https://juejin.im/post/5a52386d51882573443c852a）

id

由一組數字組成。表示一個查詢中各個子查詢的執行順序;

id相同執行順序由上至下。

（https://juejin.im/post/5a52386d51882573443c852a）

id不同，id值越大優先順序越高，越先被執行。

（https://juejin.im/post/5a52386d51882573443c852a）

id為null時表示一個結果集，不需要使用它查詢，常出現在包含union等查詢陳述句中。

（https://juejin.im/post/5a52386d51882573443c852a）

select_type

每個子查詢的查詢型別，一些常見的查詢型別。

id	select_type	description
1	SIMPLE	不包含任何子查詢或union等查詢
2	PRIMARY	包含子查詢最外層查詢就顯示為 `PRIMARY`
3	SUBQUERY	在`select`或 `where`字句中包含的查詢
4	DERIVED	`from`字句中包含的查詢
5	UNION	出現在`union`後的查詢陳述句中
6	UNION RESULT	從UNION中獲取結果集，例如上文的第三個例子

table

查詢涉及到的資料表。

如果查詢使用了別名，那麼這裡顯示的是別名，如果不涉及對資料表的操作，那麼這顯示為null，如果顯示為尖括號括起來的就表示這個是臨時表，後邊的N就是執行計劃中的id，表示結果來自於這個查詢產生。如果是尖括號括起來的，與類似，也是一個臨時表，表示這個結果來自於union查詢的id為M,N的結果集。

type

訪問型別

ALL 掃描全表資料
index 遍歷索引
range 索引範圍查詢
index_subquery 在子查詢中使用 ref
unique_subquery 在子查詢中使用 eq_ref
ref_or_null 對Null進行索引的最佳化的 ref
fulltext 使用全文索引
ref 使用非唯一索引查詢資料
eq_ref 在join查詢中使用PRIMARY KEYorUNIQUE NOT NULL索引關聯。
const 使用主鍵或者唯一索引，且匹配的結果只有一條記錄。
system const 連線型別的特例，查詢的表為系統表。

（https://juejin.im/post/5a52386d51882573443c852a）

效能從好到差依次為：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了ALL之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一個索引。

所以，如果透過執行計劃發現某張表的查詢陳述句的type顯示為ALL，那就要考慮新增索引，或者更換查詢方式，使用索引進行查詢。

possible_keys

可能使用的索引，註意不一定會使用。查詢涉及到的欄位上若存在索引，則該索引將被列出來。當該列為 NULL時就要考慮當前的SQL是否需要優化了。

key

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

TIPS:查詢中若使用了改寫索引(改寫索引：索引的資料改寫了需要查詢的所有資料)，則該索引僅出現在key串列中。

select_type為index_merge時，這裡可能出現兩個以上的索引，其他的select_type這裡只會出現一個。

key_length

索引長度char()、varchar()索引長度的計算公式：

(Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列長度 + 1(允許null) + 2(變長列)

其他型別索引長度的計算公式:ex:

CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL DEFAULT '',
  `age` int(11),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx` (`name`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

name 索引長度為：編碼為utf8mb4,列長為128,不允許為NULL,欄位型別為varchar(128)。key_length = 128 * 4 + 0 + 2 = 514;

（https://juejin.im/post/5a52386d51882573443c852a）

age 索引長度：int型別佔4位，允許null,索引長度為5。

（https://juejin.im/post/5a52386d51882573443c852a）

ref

表示上述表的連線匹配條件，即哪些列或常量被用於查詢索引列上的值

如果是使用的常數等值查詢，這裡會顯示const，如果是連線查詢，被驅動表的執行計劃這裡會顯示驅動表的關聯欄位，如果是條件使用了運算式或者函式，或者條件列發生了內部隱式轉換，這裡可能顯示為func

rows

傳回估算的結果集數目，註意這並不是一個準確值。

extra

extra的資訊非常豐富，常見的有：

Using index 使用改寫索引
Using where 使用了用where子句來過濾結果集
Using filesort 使用檔案排序，使用非索引列進行排序時出現，非常消耗效能，儘量最佳化。
Using temporary 使用了臨時表。

一些SQL最佳化建議

1、SQL陳述句不要寫的太複雜。

一個SQL陳述句要儘量簡單，不要巢狀太多層。

2、使用『臨時表』快取中間結果。

簡化SQL陳述句的重要方法就是採用臨時表暫存中間結果，這樣可以避免程式中多次掃描主表，也大大減少了阻塞，提高了併發效能。

3、使用like的時候要註意是否會導致全表掃

有的時候會需要進行一些模糊查詢比如

select id from table where username like ‘%hollis%’

關鍵詞%hollis%，由於hollis前面用到了“%”，因此該查詢會使用全表掃描，除非必要，否則不要在關鍵詞前加%，

4、儘量避免使用!=或<>運運算元

在where陳述句中使用!=或<>，引擎將放棄使用索引而進行全表掃描。

5、儘量避免使用 or 來連線條件

在 where 子句中使用 or 來連線條件，引擎將放棄使用索引而進行全表掃描。

可以使用

select id from t where num=10
union all
select id from t where num=20

替代
select id from t where num=10 or num=20

6、儘量避免使用in和not in

在 where 子句中使用 in和not in，引擎將放棄使用索引而進行全表掃描。

可以使用

select id from t where num between 10 and 20

替代
select id from t where num in (10,20)

7、可以考慮強制查詢使用索引

select * from table force index(PRI) limit 2;(強制使用主鍵)

select * from table force index(hollis_index) limit 2;(強制使用索引"hollis_index")

select * from table force index(PRI,hollis_index) limit 2;(強制使用索引"PRI和hollis_index")

8、儘量避免使用運算式、函式等操作作為查詢條件

9、儘量避免大事務操作，提高系統併發能力。

10、儘量避免使用遊標

11、任何地方都不要使用 select * from t ，用具體的欄位串列代替“*”，不要傳回用不到的任何欄位。

12、盡可能的使用 varchar/nvarchar 代替 char/nchar

13、儘量使用數字型欄位，若只含數值資訊的欄位儘量不要設計為字元型，這會降低查詢和連線的效能，並會增加儲存開銷。

14、索引並不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率

15、並不是所有索引對查詢都有效，SQL是根據表中資料來進行查詢最佳化的，當索引列有大量資料重覆時，SQL查詢可能不會去利用索引

●編號412，輸入編號直達本文

●輸入m獲取文章目錄

推薦↓↓↓

Web開發

更多推薦《18個技術類微信公眾號》

涵蓋：程式人生、演演算法與資料結構、駭客技術與網路安全、大資料技術、前端開發、Java、Python、Web開發、安卓開發、iOS開發、C/C++、.NET、Linux、資料庫、運維等。

教你如何定位及最佳化SQL陳述句的效能問題

id

select_type

table

type

possible_keys

key

key_length

ref

rows

extra

相關推薦

熱門標籤

熱門文章

分享創造快樂