目錄

SQL優化

23什么是慢SQL

如何優化呢?

如何利于覆蓋索引

如何使用聯合索引

如何進行分頁優化

Join代替子查詢

為什么要小表驅動大表?

為什么避免join太多的表?

如何進行排序優化

什么是filesort

全字段排序和rowid排序

條件下推

索引

索引為什么能提高MySQL的效率呢?

索引的分類

普通索引和唯一索引的區別

創建索引需要注意哪些

索引失效的情況

索引不適合哪些場景

什么適合創建索引

索引優化思路

---

SQL優化

23什么是慢SQL

MySQL 中有一個叫 long_query_time 的參數，原則上執行時間超過該參數值的 SQL 就是慢 SQL，會被記錄到慢查詢日志中。

如何優化呢?

首先要找到慢sql可以通過慢sql日志進行查詢.然后可以用Explain + sql語句 看有沒有用索引,大部分慢sql都是因為這個

1.避免不需要的列 避免select *

2.分頁優化

3.索引優化

4.Join優化

如何利于覆蓋索引

覆蓋索引的核心是“查詢所需的字段都在同一個索引里”，這樣 MySQL 就不需要回表，直接從索引中返回結果。

如何使用聯合索引

使用聯合索引最重要的一條是遵守最左前綴原則，也就是查詢條件需要從索引的左側字段開始。

如何進行分頁優化

分頁優化的核心是避免深度偏移帶來的全表掃描，可以通過兩種方式來優化：延遲關聯和添加書簽。分頁查詢的效率問題主要是由于 OFFSET 的存在，OFFSET 會導致 MySQL 必須掃描和跳過 offset + limit 條數據，這個過程是非常耗時的。

Join代替子查詢

第一，JOIN 的 ON 條件能更直接地觸發索引，而子查詢可能因嵌套導致索引失效。

第二，JOIN 的一次連接操作替代了子查詢的多次重復執行，尤其在大數據量的情況下性能差異明顯。

為什么要小表驅動大表?

第一，如果大表的 JOIN 字段有索引，那么小表的每一行都可以通過索引快速匹配大表。

時間復雜度為小表行數 N 乘以大表索引查找復雜度 log(大表行數 M)，總復雜度為 N*log(M)。

顯然小表做驅動表比大表做驅動表的時間復雜度 M*log(N) 更低。

1. 當使用 left join 時，左表是驅動表，右表是被驅動表。
2. 當使用 right join 時，剛好相反。
3. 當使用 join 時，MySQL 會選擇數據量比較小的表作為驅動表，大表作為被驅動表。

為什么避免join太多的表?

第一，多表 JOIN 的執行路徑會隨著表的數量呈現指數級增長，優化器需要估算所有路徑的成本，有可能會導致出現大表驅動小表的情況。

第二，多表 JOIN 需要緩存中間結果集，可能超出 join_buffer_size，這種情況下內存臨時表就會轉為磁盤臨時表，性能也會急劇下降。

如何進行排序優化

第一，對 ORDER BY 涉及的字段創建索引，避免 filesort。

如果是多個字段，聯合索引需要保證 ORDER BY 的列是索引的最左前綴。

第二，可以適當調整排序參數，如增大 sort_buffer_size、max_length_for_sort_data 等，讓排序在內存中完成。

第三，可以通過 where 和 limit 限制待排序的數據量，減少排序的開銷。

什么是filesort

當不能使用索引生成排序結果的時候，MySQL 需要自己進行排序，如果數據量比較小，會在內存中進行；如果數據量比較大就需要寫臨時文件到磁盤再排序，我們將這個過程稱為文件排序。

全字段排序和rowid排序

當排序字段是索引字段且滿足最左前綴原則時，MySQL 可以直接利用索引的有序性完成排序。

當無法使用索引排序時，MySQL 需要在內存或磁盤中進行排序操作，分為全字段排序和 rowid 排序兩種算法。

全字段排序會一次性取出滿足條件行的所有字段，然后在 sort buffer 中進行排序，排序后直接返回結果，無需回表。

以?SELECT * FROM user WHERE name = "王二" ORDER BY age?為例：

• 從 name 索引中找到第一個滿足?name='張三'?的主鍵 id；
• 根據主鍵 id 取出整行所有的字段，存入 sort buffer；
• 重復上述過程直到處理完所有滿足條件的行
• 對 sort buffer 中的數據按 age 排序，返回結果。

優點是僅需要一次磁盤 IO，缺點是內存占用大，如果數量超過 sort buffer 的話，需要分片讀取并借助臨時文件合并排序，IO 次數反而會增加。

也無法處理包含 text 和 blob 類型的字段。

rowid 排序分為兩個階段：

• 第一階段：根據查詢條件取出排序字段和主鍵 ID，存入 sort buffer 進行排序；
• 第二階段：根據排序后的主鍵 ID 回表取出其他需要的字段。

同樣以?SELECT * FROM user WHERE name = "王二" ORDER BY age?為例：

• 從 name 索引中找到第一個滿足?name='張三'?的主鍵 id；
• 根據主鍵 id 取出排序字段 age，連同主鍵 id 一起存入 sort buffer；
• 重復上述過程直到處理完所有滿足條件的行
• 對 sort buffer 中的數據按 age 排序；
• 遍歷排序后的主鍵 id，回表取出其他所需字段，返回結果。

優點是內存占用較少，適合字段多或者數據量大的場景，缺點是需要兩次磁盤 IO。

條件下推

條件下推的核心思想是將外層的過濾條件，比如說 where、join 等，盡可能地下推到查詢計劃的更底層，比如說子查詢、連接操作之前，從而減少中間結果的數據量。就是盡量早點做過濾動作

索引

索引為什么能提高MySQL的效率呢?

索引就像一本書的目錄，能讓 MySQL 快速定位數據，避免全表掃描。

除了查得快，索引還能加速排序、分組、連接等操作。

MySQL索引的底層是B+樹 他比較矮壯 那么IO的次數就會少,效率就會比較高

索引的分類

從功能上分類的話，有主鍵索引、唯一索引、全文索引；從數據結構上分類的話，有 B+ 樹索引、哈希索引；從存儲內容上分類的話，有聚簇索引、非聚簇索引。

主鍵索引用于唯一標識表中的每條記錄，其列值必須唯一且非空。創建主鍵時，MySQL 會自動生成對應的唯一索引。

主鍵索引=唯一索引+非空。每個表只能有一個主鍵索引，但可以有多個唯一索引

主鍵索引不允許插入 NULL 值，嘗試插入 NULL 會報錯；唯一索引允許插入多個 NULL 值。\

普通索引和唯一索引的區別

普通索引僅用于加速查詢，不限制字段值的唯一性；適用于高頻寫入的字段、范圍查詢的字段。

唯一索引強制字段值的唯一性，插入或更新時會觸發唯一性檢查；適用于業務唯一性約束的字段、防止數據重復插入的字段。

全文索引是 MySQL 一種優化文本數據檢索的特殊類型索引，適用于 CHAR、VARCHAR 和 TEXT 等字段

創建索引需要注意哪些

第一，選擇合適的字段

第二，要控制索引的數量，避免過度索引，每個索引都要占用存儲空間，單表的索引數量不建議超過 5 個。

第三，聯合索引的時候要遵循最左前綴原則，即在查詢條件中使用聯合索引的第一個字段，才能充分利用索引。

索引失效的情況

1. 未遵循最左前綴原則
2. 在索引列上使用函數或運算
3. 使用不等于操作符(!=, <>)
4. 使用NOT IN或NOT EXISTS
5. 使用LIKE以通配符開頭
6. 隱式類型轉換
7. OR條件使用不當
8. 使用IS NULL或IS NOT NULL
9. 數據分布不均勻
10. 索引列參與計算
11. 使用ORDER BY不當
12. 索引選擇性過低
13. 統計信息過時
14. 索引未被維護
15. 查詢返回過多數據
16. 使用全表掃描提示
17. 多表連接條件不當
18. 子查詢處理不當
19. 使用UNION而非UNION ALL
20. 索引列使用表達式

索引不適合哪些場景

第一，區分度低的列，可以和其他高區分度的列組成聯合索引。

第二，頻繁更新的列，索引會增加更新的成本。

第三，TEXT、BLOB 等大對象類型的字段，可以使用前綴索引、全文索引替代。

第四，當表的數據量很小的時候，不超過 1000 行，全表掃描可能比使用索引更快。

什么是區分度

區分度是衡量一個字段在 MySQL 表中唯一值的比例。

區分度 = 字段的唯一值數量 / 字段的總記錄數；越接近 1，就越適合作為索引。因為索引可以更有效地縮小查詢范圍。一個表中有 1000 條記錄，其中性別字段只有兩個值（男、女），那么性別字段的區分度只有 0.002，就不適合建立索引。

什么適合創建索引

主鍵、唯一鍵、以及經常作為查詢條件的字段最適合加索引。除此之外，字段的區分度要高，這樣索引才能起到過濾作用；如果字段經常用于表連接、排序或分組，也建議加索引。同時如果多個字段經常一起出現在查詢條件中，也可以建立聯合索引來提升性能。

索引優化思路

先通過慢查詢日志找出性能瓶頸，然后用 EXPLAIN 分析執行計劃，判斷是否走了索引、是否回表、是否排序。接著根據字段特性設計合適的索引，如選擇區分度高的字段，使用聯合索引和覆蓋索引，避免索引失效的寫法，最后通過實測來驗證優化效果。