當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL之SQL优化详解（二）

發布時間：2025/4/16 数据库 16 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL之SQL优化详解（二）小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

MySQL之SQL優化詳解（二）
- 1. SQL的執行順序
  - 1.1 手寫順序
  - 1.2 機讀順序
- 2. 七種join
- 3. 索引
  - 3.1 索引初探
  - 3.2 索引分類
  - 3.3 建與不建
- 4. 性能分析Explain

MySQL之SQL優化詳解（二）

1. SQL的執行順序

1.1 手寫順序

1.2 機讀順序

2. 七種join

3. 索引

3.1 索引初探

是什么： 排好序的快速查找數據結構
兩個主要的索引結構： B+tree 索引和哈希索引。
如何建： 1. ALTER TABLE table_name ADD INDEX index_name (column_list); 2. CREATE INDEX index_name ON table_name (column_list);

優點: 類似大學圖書館建書目索引，提高了檢索效率，降低了數據庫IO，同時還可以通過索引進行排序，降低數據排序的成本，降低了CPU的消耗

缺點: 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行 insert、update和delete，因為更新表時不僅要保存數據，還要保存一下索引文件每次更新添加了索引列的字段。

3.2 索引分類

1.主鍵索引：主鍵是一種唯一性索引，但它必須指定為PRIMARY KEY，每個表只能有一個主鍵

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

2.唯一索引：索引列的所有值都只能出現一次，即必須唯一，值可以為空

ALTER TABLE table_name ADD UNIQUE (column_list)

3.普通索引：基本的索引類型，值可以為空，沒有唯一性的限制

ALTER TABLE table_name ADD INDEX index_name (column_list);

4.全文索引：全文索引的索引類型為 FULLTEXT，全文索引只能創建在CHAR、VARCHAR、TEXT類型的字段上。查詢數據量較大的字符串類型字段時，使用全文索引可以提高查詢速度

ALTER TABLE table_name ADD FULLTEXT INDEX index_name(column_list);

3.3 建與不建

對于MySQL的索引創建，我們經常有疑慮，那么什么時候該建什么時候不該建呢？

哪些情況需要創建索引

主鍵自動創建唯一索引

頻繁作為查詢條件的字段應該創建索引

查詢中與其它表關聯的字段，外鍵關系建立索引

查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度

查詢中統計或者分組字段

哪些情況不需要建索引

頻繁更新的字段不適合創建索引

where 條件用不到的字段不適合創建索引

注意，如果某個數據列包含許多重復的內容，為它建立索引就沒有太大的實際效果

4. 性能分析Explain

Explain簡稱執行計劃，使用Explain關鍵字可以模擬優化器執行SQL查詢語句

用法：explain + SQL

1. id：select查詢的序列號,包含一組數字，表示查詢中執行select子句或操作表的順序

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

② id 不同，如果是子查詢，id的序號會遞增，id值越大優先級越高，越先被執行

③ id相同不相同，不相同

注：id如果相同，可以認為是一組，從上往下順序執行；在所有組中，id值越大，優先級越高，越先執行

2. select_type：查詢的類型，主要是用于區分普通查詢、聯合查詢、子查詢等的復雜查詢

simple：簡單的select 查詢,查詢中不包含子查詢或者 union
primary：查詢中若包含任何復雜的子部分，最外層查詢則被標記為
subquery：在select或where列表中包含了子查詢
derived：在from 列表中包含的子查詢被標記為 derived(衍生)
union：若第二個select出現在之后，則被標記為 union（若union 包含from 子句的子查詢中,外層select將被標記為：derived）
union result：從union 表獲取結果的 select

3. table：顯示這一行的數據是關于哪張表的

4. type：訪問類型排列，顯示查詢使用了何種類型

從好到壞，system > const > eq_ref > ref > range > index > all

system：表只有一行記錄（等于系統表），這是const 類型的特列，平時不會出現，這個也可以忽略不計
const：表示通過索引一次就找到了，const 用于比較 primary key或者unique索引
eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描
ref：非唯一性索引掃描，返回匹配某個單獨值的所有行，本質上也是一種索引訪問
range：只檢索給定范圍的行,使用一個索引來選擇行。key 列顯示使用了哪個索引，一般就是在你的where語句中出現了 between、<、>、in等的查詢
index：Full Index Scan，index與 all 區別為 index 類型只遍歷索引樹
all：Full Table Scan，將遍歷全表以找到匹配的行

5. possible_key：顯示可能應用在這張表的索引，一個或多個。(但不一定被實際應用)

6. key：實際使用的索引，如果為null，則沒有使用索引。

查詢中若使用了覆蓋索引，則該索引與查詢的select字段重疊

7. key_len：表示索引中使用的字節數

8. ref：顯示索引的哪一列被使用，如果可能的話，是一個常數，哪些列或常量被用于查找索引列上的值

注：由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一個常量，即 'ac'

9. rows：根據表統計信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數

10. extra：包含不適合在其他列中顯示但十分重要的額外信息

Using filesort (劣)： mysql 會對數據使用一個外部的索引排序（文件排序），而不是照表內的索引順序進行讀取

Using temporary (劣)：使了用臨時表保存中間結果,MySQL在對查詢結果排序時使用臨時表。常見于排序 order by 和分組查詢 group by

Using index (優)：表示相應的select操作中使用了覆蓋索引(Covering Index)，避免訪問了表的數據行，效率不錯！
Using where：表明使用了where 過濾
Using join buffer：表明使用了連接緩存
impossible where：where子句的值總是false，不能用來獲取任何數據

select tables optimized away：select操作已經優化到不能再優化了（MySQL根本沒有遍歷表或索引就返回數據了
distinct：在select部分使用了distinc關鍵字

心法：

針對explain命令生成的執行計劃，這里有一個查看心法。我們可以先從查詢類型type列開始查看，如果出現all關鍵字，后面的內容就都可以不用看了，代表全表掃描。再看key列，看是否使用了索引,null代表沒有使用索引。然后看rows列，該列用來表示在SQL執行過程中被掃描的行數，該數值越大，意味著需要掃描的行數越多，相應的耗時越長，最后看Extra列，在這列中要觀察是否有Using filesort 或者Using temporary 這樣的關鍵字出現，這些是很影響數據庫性能的。

轉載于:https://www.cnblogs.com/dwlovelife/p/11110215.html

總結

以上是生活随笔為你收集整理的MySQL之SQL优化详解（二）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： C# 操作自定义config文件
下一篇：如何优雅的完成长截图？