你真的知道怎么優(yōu)化SQL嗎

簡(jiǎn)介

雖然使用Explain不能夠馬上調(diào)優(yōu)我們的SQL，它也不能給予我們一些調(diào)整建議，但是它能夠讓我們了解MySQL 優(yōu)化器是如何執(zhí)行SQL 語(yǔ)句的

通過(guò)Explain，我們可以分析出以下結(jié)果：

• 表的讀取順序
• 數(shù)據(jù)讀取操作的操作類型
• 哪些索引可以使用
• 哪些索引被實(shí)際使用
• 表之間的引用
• 每張表有多少行被優(yōu)化器查詢

Explain命令的用法十分簡(jiǎn)單, 在 select語(yǔ)句前加上 Explain 就可以了, 例如:

explain select * from user;

它的結(jié)果主要包含以下字段

id、select_type、table、partitions、type、possible_keys、key、ref、rows、filtered、extra

接下來(lái)我們來(lái)看一下各個(gè)字段的含義

id 查詢序列號(hào)

加載表的順序

連接查詢各個(gè)表的加載順序是相同的，所以都為1

包含子查詢的時(shí)候，先執(zhí)行子查詢，所以u(píng)ser表的id值最大

select_type 查詢類型

常用取值有:

• SIMPLE：簡(jiǎn)單的select查詢，不包含子查詢和索引
• PRIMARY：查詢中若包含任何子查詢，最外層查詢則為記為PRIMARY
• SUBQUERY：在SELECT或WHERE列表中包含了子查詢
• DERIVED：在FROM列表中包含的子查詢被標(biāo)記為DERIVED（衍生），MySQL會(huì)遞歸執(zhí)行這些子查詢，把結(jié)果放在臨時(shí)表中
• UNION：若第二個(gè)SELECT出現(xiàn)在索引之后，則被標(biāo)記為UNION：若索引包含在FROM子句的子查詢中，外層SELECT將被標(biāo)記為：DERIVED
• UNION RESULT：從索引表獲取結(jié)果的查詢

table查詢涉及的表或衍生表

type查詢類型

通過(guò) type 字段, 我們可以判斷此次查詢是全表掃描還是索引掃描等，type 常用的取值有:

system：表只有一條數(shù)據(jù)

const：針對(duì)主鍵或唯一索引的等值查詢掃描, 簡(jiǎn)單理解為一次讀取就獲取到了數(shù)據(jù)，例如下面這個(gè)主鍵索引的查詢

eq_ref：唯一性索引掃描，對(duì)于每個(gè)索引鍵，表中只有一條記錄與之匹配。常見(jiàn)于主鍵或唯一索引掃描

ref：非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行

range：表示使用索引范圍查詢，例如=、>、>、>=、、=、IS、 NULL、=>、BETWEEN、IN等

index: 所要查詢的數(shù)據(jù)直接在索引樹(shù)中就可以獲取到, 而不需要掃描數(shù)據(jù)，例如:

ALL: 表示全表掃描, 這個(gè)類型的查詢是性能最差的查詢

type 類型的性能比較

通常來(lái)說(shuō), 不同的 type 類型的性能關(guān)系如下:

ALL index range ref eq_ref const system

possible_keys查詢時(shí)能夠使用到的索引.

possible_keys 表示 MySQL 在查詢時(shí), 能夠使用到的索引. 注意并不是一定用，實(shí)際使用是由 由 key 字段決定

key 查詢使用的索引

此字段是 MySQL 在當(dāng)前查詢時(shí)所真正使用到的索引.

key_len使用索引的字節(jié)數(shù)

這個(gè)字段可以評(píng)估組合索引是否完全被使用, 或只有最左部分字段被使用到.

rows MySQL估算要查找到結(jié)果集需要掃描讀取的數(shù)據(jù)行數(shù)

Extra額外的信息

常見(jiàn)的有以下幾種內(nèi)容:

• Using filesort：MySQL 需額外的排序操作, 不能通過(guò)索引順序達(dá)到排序效果. 一般有 Using filesort, 都建議優(yōu)化去掉, 因?yàn)檫@樣的查詢 CPU 資源消耗大.
• Using index：表示查詢?cè)谒饕龢?shù)中就可查找所需數(shù)據(jù), 不用掃描表數(shù)據(jù)文件, 往往說(shuō)明性能不錯(cuò)
• Using temporary：查詢有使用臨時(shí)表, 一般出現(xiàn)于排序, 分組和多表 join 的情況, 查詢效率不高, 建議優(yōu)化.
• Using where：表明使用了where過(guò)濾
• Using join buffer：表明使用了連接緩存,比如說(shuō)在查詢的時(shí)候，多表join的次數(shù)非常多，那么將配置文件中的緩沖區(qū)的join buffer調(diào)大一些
• impossible where：where子句的值總是false，不能用來(lái)獲取任何元組
• select tables optimized away：在沒(méi)有GROUPBY子句的情況下，基于索引優(yōu)化MIN/MAX操作或者對(duì)于MyISAM存儲(chǔ)引擎優(yōu)化COUNT(*)操作，不必等到執(zhí)行階段再進(jìn)行計(jì)算，查詢執(zhí)行計(jì)劃生成的階段即完成優(yōu)化
• distinct：優(yōu)化distinct操作，在找到第一匹配的元組后即停止找同樣值的動(dòng)作

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。