如何優化SQL

這篇文章主要介紹了如何優化SQL，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

簡介

雖然使用Explain不能夠馬上調優我們的SQL，它也不能給予我們一些調整建議，但是它能夠讓我們了解MySQL 優化器是如何執行SQL 語句的

通過Explain，我們可以分析出以下結果：

• 表的讀取順序

• 數據讀取操作的操作類型

• 哪些索引可以使用

• 哪些索引被實際使用

• 表之間的引用

• 每張表有多少行被優化器查詢

Explain命令的用法十分簡單, 在 select語句前加上 Explain 就可以了, 例如:

explain select * from user;

它的結果主要包含以下字段

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

接下來我們來看一下各個字段的含義

id 查詢序列號

加載表的順序

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

包含子查詢的時候，先執行子查詢，所以user表的id值最大

select_type 查詢類型

常用取值有:

• SIMPLE：簡單的select查詢，不包含子查詢和索引

• PRIMARY：查詢中若包含任何子查詢，最外層查詢則為記為PRIMARY

• SUBQUERY：在SELECT或WHERE列表中包含了子查詢

• DERIVED：在FROM列表中包含的子查詢被標記為DERIVED（衍生），MySQL會遞歸執行這些子查詢，把結果放在臨時表中

• UNION：若第二個SELECT出現在索引之后，則被標記為UNION：若索引包含在FROM子句的子查詢中，外層SELECT將被標記為：DERIVED

• UNION RESULT：從索引表獲取結果的查詢

table查詢涉及的表或衍生表

type查詢類型

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

system：表只有一條數據

const：針對主鍵或唯一索引的等值查詢掃描, 簡單理解為一次讀取就獲取到了數據，例如下面這個主鍵索引的查詢

eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描

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

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

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

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

type 類型的性能比較

通常來說, 不同的 type 類型的性能關系如下:

ALL < index < range < ref < eq_ref < const < system

possible_keys查詢時能夠使用到的索引.

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

key 查詢使用的索引

此字段是 MySQL 在當前查詢時所真正使用到的索引.

key_len使用索引的字節數

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

rows MySQL估算要查找到結果集需要掃描讀取的數據行數

Extra額外的信息

常見的有以下幾種內容:

• Using filesort：MySQL 需額外的排序操作, 不能通過索引順序達到排序效果. 一般有 Using filesort, 都建議優化去掉, 因為這樣的查詢 CPU 資源消耗大.

• Using index：表示查詢在索引樹中就可查找所需數據, 不用掃描表數據文件, 往往說明性能不錯

• Using temporary：查詢有使用臨時表, 一般出現于排序, 分組和多表 join 的情況, 查詢效率不高, 建議優化.

• Using where：表明使用了where過濾

• Using join buffer：表明使用了連接緩存,比如說在查詢的時候，多表join的次數非常多，那么將配置文件中的緩沖區的join buffer調大一些

• impossible where：where子句的值總是false，不能用來獲取任何元組

• select tables optimized away：在沒有GROUPBY子句的情況下，基于索引優化MIN/MAX操作或者對于MyISAM存儲引擎優化COUNT(*)操作，不必等到執行階段再進行計算，查詢執行計劃生成的階段即完成優化

• distinct：優化distinct操作，在找到第一匹配的元組后即停止找同樣值的動作

感謝你能夠認真閱讀完這篇文章，希望小編分享的“如何優化SQL”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!