如何定位及優(yōu)化SQL語句的性能問題？
點(diǎn)擊關(guān)注上方“SQL數(shù)據(jù)庫(kù)開發(fā)”，
設(shè)為“置頂或星標(biāo)”，第一時(shí)間送達(dá)干貨
來自：Hollis（微信號(hào)：hollischuang)
在現(xiàn)如今的軟件開發(fā)中，關(guān)系型數(shù)據(jù)庫(kù)是做數(shù)據(jù)存儲(chǔ)最重要的工具。無論是Oracale還是Mysql，都是需要通過SQL語句來和數(shù)據(jù)庫(kù)進(jìn)行交互的，這種交互我們通常稱之為CRUD。在CRUD操作中，最最常用的也就是Read操作了。而對(duì)于不同的表結(jié)構(gòu)，采用不同的SQL語句，性能上可能千差萬別。本文，就基于MySql數(shù)據(jù)庫(kù)，來介紹一下如何定位SQL語句的性能問題。
對(duì)于低性能的SQL語句的定位，最重要也是最有效的方法就是使用執(zhí)行計(jì)劃。
執(zhí)行計(jì)劃
我們知道，不管是哪種數(shù)據(jù)庫(kù)，或者是哪種數(shù)據(jù)庫(kù)引擎，在對(duì)一條SQL語句進(jìn)行執(zhí)行的過程中都會(huì)做很多相關(guān)的優(yōu)化，對(duì)于查詢語句，最重要的優(yōu)化方式就是使用索引。
而執(zhí)行計(jì)劃，就是顯示數(shù)據(jù)庫(kù)引擎對(duì)于SQL語句的執(zhí)行的詳細(xì)情況，其中包含了是否使用索引，使用什么索引，使用的索引的相關(guān)信息等。
基本語法
explain select ...mysql的explain 命令可以用來分析select 語句的運(yùn)行效果。
mysql> explain extended select * from account;******** 1. row ***************************id: 1select_type: SIMPLEtable: accounttype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 1filtered: 100.00Extra:1 row in set, 1 warning (0.00 sec)mysql> show warnings;*************1. row ***************************Level: NoteCode: 1003Message: select `dbunit`.`account`.`id` AS `id`,`dbunit`.`account`.`name` AS `name` from `dbunit`.`account`1?row?in?set?(0.00?sec)
(提示：可以左右滑動(dòng)代碼)
另外，對(duì)于分區(qū)表的查詢，需要使用partitions命令。
explain partitions select ...執(zhí)行計(jì)劃包含的信息
不同版本的Mysql和不同的存儲(chǔ)引擎執(zhí)行計(jì)劃不完全相同，但基本信息都差不多。mysql執(zhí)行計(jì)劃主要包含以下信息:
id
由一組數(shù)字組成。表示一個(gè)查詢中各個(gè)子查詢的執(zhí)行順序;
id相同執(zhí)行順序由上至下。
id不同，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行。
id為
null時(shí)表示一個(gè)結(jié)果集，不需要使用它查詢，常出現(xiàn)在包含union等查詢語句中。
select_type
每個(gè)子查詢的查詢類型，一些常見的查詢類型。
| id | 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é)果集，例如上文的第三個(gè)例子 |
table
查詢涉及到的數(shù)據(jù)表。
type
訪問類型
ALL? 掃描全表數(shù)據(jù)index遍歷索引range索引范圍查找index_subquery在子查詢中使用 refunique_subquery在子查詢中使用 eq_refref_or_null對(duì)Null進(jìn)行索引的優(yōu)化的 reffulltext使用全文索引ref? 使用非唯一索引查找數(shù)據(jù)eq_ref在join查詢中使用PRIMARY KEYorUNIQUE NOT NULL索引關(guān)聯(lián)。const使用主鍵或者唯一索引，且匹配的結(jié)果只有一條記錄。system const連接類型的特例，查詢的表為系統(tǒng)表。
性能從好到差依次為：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了ALL之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一個(gè)索引。
所以，如果通過執(zhí)行計(jì)劃發(fā)現(xiàn)某張表的查詢語句的type顯示為ALL，那就要考慮添加索引，或者更換查詢方式，使用索引進(jìn)行查詢。
possible_keys
可能使用的索引，注意不一定會(huì)使用。查詢涉及到的字段上若存在索引，則該索引將被列出來。當(dāng)該列為 NULL時(shí)就要考慮當(dāng)前的SQL是否需要優(yōu)化了。
key
顯示MySQL在查詢中實(shí)際使用的索引，若沒有使用索引，顯示為NULL。
TIPS:查詢中若使用了覆蓋索引(覆蓋索引：索引的數(shù)據(jù)覆蓋了需要查詢的所有數(shù)據(jù))，則該索引僅出現(xiàn)在key列表中。
select_type為index_merge時(shí)，這里可能出現(xiàn)兩個(gè)以上的索引，其他的select_type這里只會(huì)出現(xiàn)一個(gè)。
key_length
索引長(zhǎng)度char()、varchar()索引長(zhǎng)度的計(jì)算公式：
(Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列長(zhǎng)度 + 1(允許null) + 2(變長(zhǎng)列)其他類型索引長(zhǎng)度的計(jì)算公式:
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;
NULL,字段類型為varchar(128)。key_length = 128 * 4 + 0 + 2 = 514;age 索引長(zhǎng)度：int類型占4位，允許null,索引長(zhǎng)度為5。
ref
rows
extra
extra的信息非常豐富，常見的有：?Using index 使用覆蓋索引 Using where 使用了用where子句來過濾結(jié)果集 Using filesort 使用文件排序，使用非索引列進(jìn)行排序時(shí)出現(xiàn)，非常消耗性能，盡量?jī)?yōu)化。 Using temporary 使用了臨時(shí)表。
select id from table where username like ‘%sql_road%’關(guān)鍵詞%sql_road%，由于sql_road前面用到了“%”，因此該查詢會(huì)使用全表掃描，除非必要，否則不要在關(guān)鍵詞前加%，
4、盡量避免使用!=或<>操作符
在where語句中使用!=或<>，引擎將放棄使用索引而進(jìn)行全表掃描。
5、盡量避免使用?or?來連接條件
在 where 子句中使用 or 來連接條件，引擎將放棄使用索引而進(jìn)行全表掃描。
可以用select id from t where num=10union allselect id from t where num=20替代select id from t where num=10 or num=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ì)為字符型，這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開銷。
14、索引并不是越多越好，索引固然可以提高相應(yīng)的?select?的效率，但同時(shí)也降低了?insert?及?update?的效率
15、并不是所有索引對(duì)查詢都有效，SQL是根據(jù)表中數(shù)據(jù)來進(jìn)行查詢優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí)，SQL查詢可能不會(huì)去利用索引
——End——
后臺(tái)回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨 后臺(tái)回復(fù)關(guān)鍵字：進(jìn)群，帶你進(jìn)入高手如云的交流群。 推薦閱讀
為什么阿里巴巴禁止使用存儲(chǔ)過程？ 一份非常完整的 MySQL 規(guī)范，速速收藏！ 數(shù)據(jù)庫(kù)中為什么不推薦使用外鍵約束 阿里規(guī)定超過3張表，禁止JOIN，為何？ MySQL 常用命令手冊(cè)
這是一個(gè)能學(xué)到技術(shù)的公眾號(hào)，歡迎關(guān)注
點(diǎn)擊「閱讀原文」了解SQL訓(xùn)練營(yíng)