如何定位及優(yōu)化SQL語句的性能問題？看這篇就完了

lguo001 2019-07-20

展開全文

更多精彩第一時間直達(dá)

本文轉(zhuǎn)自 | Hollis

在現(xiàn)如今的軟件開發(fā)中，關(guān)系型數(shù)據(jù)庫是做數(shù)據(jù)存儲最重要的工具。無論是Oracale還是Mysql，都是需要通過SQL語句來和數(shù)據(jù)庫進(jìn)行交互的，這種交互我們通常稱之為CRUD。在CRUD操作中，最最常用的也就是Read操作了。而對于不同的表結(jié)構(gòu)，采用不同的SQL語句，性能上可能千差萬別。本文，就基于MySql數(shù)據(jù)庫，來介紹一下如何定位SQL語句的性能問題。

對于低性能的SQL語句的定位，最重要也是最有效的方法就是使用執(zhí)行計(jì)劃。

執(zhí)行計(jì)劃

我們知道，不管是哪種數(shù)據(jù)庫，或者是哪種數(shù)據(jù)庫引擎，在對一條SQL語句進(jìn)行執(zhí)行的過程中都會做很多相關(guān)的優(yōu)化，對于查詢語句，最重要的優(yōu)化方式就是使用索引。

而執(zhí)行計(jì)劃，就是顯示數(shù)據(jù)庫引擎對于SQL語句的執(zhí)行的詳細(xì)情況，其中包含了是否使用索引，使用什么索引，使用的索引的相關(guān)信息等。

（https:///post/5a52386d51882573443c852a）

基本語法

explain select ...

mysql的explain 命令可以用來分析select 語句的運(yùn)行效果。

除此之外，explain 的extended 擴(kuò)展能夠在原本explain的基礎(chǔ)上額外的提供一些查詢優(yōu)化的信息，這些信息可以通過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)

另外，對于分區(qū)表的查詢，需要使用partitions命令。

explain partitions select ...

執(zhí)行計(jì)劃包含的信息

不同版本的Mysql和不同的存儲引擎執(zhí)行計(jì)劃不完全相同，但基本信息都差不多。mysql執(zhí)行計(jì)劃主要包含以下信息:

（https:///post/5a52386d51882573443c852a）

id

由一組數(shù)字組成。表示一個查詢中各個子查詢的執(zhí)行順序;

id相同執(zhí)行順序由上至下。

（https:///post/5a52386d51882573443c852a）

id不同，id值越大優(yōu)先級越高，越先被執(zhí)行。

（https:///post/5a52386d51882573443c852a）

id為null時表示一個結(jié)果集，不需要使用它查詢，常出現(xiàn)在包含union等查詢語句中。

（https:///post/5a52386d51882573443c852a）

select_type

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

select_type

description


1	SIMPLE	不包含任何子查詢或union等查詢
2	PRIMARY	包含子查詢最外層查詢就顯示為 `PRIMARY`
3	SUBQUERY	在`select`或 `where`字句中包含的查詢
4	DERIVED	`from`字句中包含的查詢
5	UNION	出現(xiàn)在`union`后的查詢語句中
6	UNION RESULT	從UNION中獲取結(jié)果集，例如上文的第三個例子

table

查詢涉及到的數(shù)據(jù)表。

如果查詢使用了別名，那么這里顯示的是別名，如果不涉及對數(shù)據(jù)表的操作，那么這顯示為null，如果顯示為尖括號括起來的<derived N>就表示這個是臨時表，后邊的N就是執(zhí)行計(jì)劃中的id，表示結(jié)果來自于這個查詢產(chǎn)生。如果是尖括號括起來的<union M,N>，與<derived N>類似，也是一個臨時表，表示這個結(jié)果來自于union查詢的id為M,N的結(jié)果集。

type

訪問類型

ALL 掃描全表數(shù)據(jù)
index 遍歷索引
range 索引范圍查找
index_subquery 在子查詢中使用 ref
unique_subquery 在子查詢中使用 eq_ref
ref_or_null 對Null進(jìn)行索引的優(yōu)化的 ref
fulltext 使用全文索引
ref 使用非唯一索引查找數(shù)據(jù)
eq_ref 在join查詢中使用PRIMARY KEYorUNIQUE NOT NULL索引關(guān)聯(lián)。
const 使用主鍵或者唯一索引，且匹配的結(jié)果只有一條記錄。
system const 連接類型的特例，查詢的表為系統(tǒng)表。

（https:///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只可以用到一個索引。

所以，如果通過執(zhí)行計(jì)劃發(fā)現(xiàn)某張表的查詢語句的type顯示為ALL，那就要考慮添加索引，或者更換查詢方式，使用索引進(jìn)行查詢。

possible_keys

可能使用的索引，注意不一定會使用。查詢涉及到的字段上若存在索引，則該索引將被列出來。當(dāng)該列為 NULL時就要考慮當(dāng)前的SQL是否需要優(yōu)化了。

key

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

TIPS:查詢中若使用了覆蓋索引(覆蓋索引：索引的數(shù)據(jù)覆蓋了需要查詢的所有數(shù)據(jù))，則該索引僅出現(xiàn)在key列表中。

select_type為index_merge時，這里可能出現(xiàn)兩個以上的索引，其他的select_type這里只會出現(xiàn)一個。

key_length

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

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

其他類型索引長度的計(jì)算公式: 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:///post/5a52386d51882573443c852a）

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

（https:///post/5a52386d51882573443c852a）

ref

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

如果是使用的常數(shù)等值查詢，這里會顯示const，如果是連接查詢，被驅(qū)動表的執(zhí)行計(jì)劃這里會顯示驅(qū)動表的關(guān)聯(lián)字段，如果是條件使用了表達(dá)式或者函數(shù)，或者條件列發(fā)生了內(nèi)部隱式轉(zhuǎn)換，這里可能顯示為func

rows

返回估算的結(jié)果集數(shù)目，注意這并不是一個準(zhǔn)確值。

extra

extra的信息非常豐富，常見的有：

Using index 使用覆蓋索引
Using where 使用了用where子句來過濾結(jié)果集
Using filesort 使用文件排序，使用非索引列進(jìn)行排序時出現(xiàn)，非常消耗性能，盡量優(yōu)化。
Using temporary 使用了臨時表。

一些SQL優(yōu)化建議

1、SQL語句不要寫的太復(fù)雜。

一個SQL語句要盡量簡單，不要嵌套太多層。

2、使用『臨時表』緩存中間結(jié)果。

簡化SQL語句的重要方法就是采用臨時表暫存中間結(jié)果，這樣可以避免程序中多次掃描主表，也大大減少了阻塞，提高了并發(fā)性能。

3、使用like的時候要注意是否會導(dǎo)致全表掃

有的時候會需要進(jìn)行一些模糊查詢比如

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

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

4、盡量避免使用!=或<>操作符

在where語句中使用!=或<>，引擎將放棄使用索引而進(jìn)行全表掃描。

5、盡量避免使用 or 來連接條件

在 where 子句中使用 or 來連接條件，引擎將放棄使用索引而進(jìn)行全表掃描。

可以使用

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，引擎將放棄使用索引而進(jìn)行全表掃描。

可以使用

select id from t where num between 10 and 20

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

7、可以考慮強(qiáng)制查詢使用索引

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

select * from table force index(hollis_index) limit 2;(強(qiáng)制使用索引'hollis_index')

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

8、盡量避免使用表達(dá)式、函數(shù)等操作作為查詢條件

9、盡量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力。

10、盡量避免使用游標(biāo)

11、任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。

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

13、盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。

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

15、并不是所有索引對查詢都有效，SQL是根據(jù)表中數(shù)據(jù)來進(jìn)行查詢優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時，SQL查詢可能不會去利用索引

END

本站是提供個人知識管理的網(wǎng)絡(luò)存儲空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自： lguo001 > 《數(shù)據(jù)庫》

舉報/認(rèn)領(lǐng)