【資料庫】MySQL查詢優化

xbmchina發表於2019-04-21
資料庫MySql優化

歡迎關注公眾號：【愛程式設計】 如果有需要後臺回覆2019贈送1T的學習資料哦！！

【資料庫】MySQL查詢優化

背景

在這個快速發展的時代，時間變得 越來越重要，也流逝得非常得快，有些人長大了，有些人卻變老了。稍不留神，2019已經過完了三分之一。回首這四個月收穫什麼，懂得了什麼？歡迎留言分享給我喲。

言歸正傳： MySQL的查詢怎麼才能更快，更合理？除了加索引還有什麼可以學習的呢？

原理

要想更好地學習某樣東西，從其原理和運作方式入手更容易掌握。道理你們都懂，我就不廢話了。

MySQL傳送查詢請求，到底做了什麼工作？

下圖是MySQL查詢執行流程圖：

查詢執行路徑

  1. 客戶端傳送一條查詢給伺服器。
  2. 伺服器先檢查查詢快取，如果命中了快取，則立刻返回查詢在快取中的結果。否則會進入下一個階段。 3.服務端進行SQL解析、預處理、再由優化器生成對應的執行計劃。 4.MySQL根據優化器生成的執行計劃，呼叫儲存引擎的API來執行查詢。 5.將結果返回給客戶端。

是什麼導致MySQL查詢變慢了？

對於MySQL，最簡單的衡量查詢開銷的三個指標如下：

  • 響應時間
  • 掃描的行數
  • 返回的行數

沒有哪個指標能夠完美地衡量查詢的開銷，但它們大致反映了MySQL在內部執行查詢時需要訪問多少資料，並可以大概推算出查詢執行的時間。

查詢慢的原因基本都是：我們的不合理操作導致查詢的多餘資料太多了。 常見原因有以下：

1.查詢不需要的記錄。 2.多表關聯時返回全部列 3.總是取出全部列

常用優化技巧

1.用索引

最簡單且見效最快的方式就是給你的條件加索引（主鍵索引，普通索引，唯一索引等）。注：索引是要另開闢一塊空間儲存的，所以不能不要錢滴都加索引。

2.關聯子查詢

MySQL的子查詢實現是非常糟糕的。比如下面的

SELECT * FROM book WHERE book_id IN (SELECT book_id FROM author WHERE author_id = 1)
複製程式碼

MySQL對IN()列表中的選項有專門的優化策略，一般會認為MySQL會先執行子查詢返回所有包含author_id 為1的book_id。

或許你想MySQL的執行時這樣子的：

SELECT GROUP_CONCAT(book_id) FROM author WHERE author_id = 1
SELECT * FROM book WHERE book_id IN (1,21,3,45,656,766,213,123)
複製程式碼

但是，MySQL會將相關的外層表壓到子查詢中的，就是下面的樣子：

SELECT * FROM book WHERE EXISTS 
(SELECT * FROM author WHERE author_id = 1 AND book.book_id = author.book_id)
複製程式碼

**原因：**因為子查詢需要book_id ，所以MySQL認為無法先執行這個子查詢，而是先對book 進行全表掃描，然後再根據book_id進行子查詢。具體可以EXPLAIN該SQL進行分析。

建議： 1.使用左外連線（LEFT OUTER JOIN）代替子查詢。

SELECT * from book LEFT OUTER JOIN author USING(book_id) WHERE author.author_id = 1
複製程式碼

影響因素：還有資料表放的位置等，具體應用場景就只能你自己explain該語句對比哪種效能比較好點。

2.確保ON或者USING子句的列上有索引 在建立索引的時候就要考慮到關聯的順序。

3.UNION使用

如果希望UNION的各個子句能根據LIMIT只取部分結果集，或者希望能夠先排好序再合併結果集的話。 第一個例子：會將author 表和user 表兩個表都存放到一個臨時表中，再從臨時表中取出前20條。

(SELECT first_name FROM author ORDER BY last_name)
UNION ALL
(SELECT first_name FROM user ORDER BY last_name)
LIMIT 20
複製程式碼

對比上面的這樣子，就有很大的改善了。

(SELECT first_name FROM author ORDER BY last_name LIMIT 20)
UNION ALL
(SELECT first_name FROM user ORDER BY last_name LIMIT 20)
LIMIT 20
複製程式碼

4.最大值和最小值

比如： 求最小值 第一種方案：

SELECT MIN(id) FROM article WHERE author = 'zero'
複製程式碼

第二種方案：

SELECT id FROM article USE INDEX(PRIMARY) WHERE author = 'zero' LIMIT 1
複製程式碼

和第一種方案的對比，效果其實是一樣的，但是它們的效能略有不同，具體還請自己具體場景分析，擇優選擇。

5.COUNT()查詢

比如如果想統計文章id大於25的數量，可以如下：

EXPLAIN SELECT COUNT(*) FROM article WHERE id >25
複製程式碼

另外一種思路：可以先查詢文章總數，減去小於等於25的數量。僅僅提供思路，具體效果還是你具體情況，自己比較，擇優選擇。

EXPLAIN SELECT (SELECT COUNT(*) FROM article) - COUNT(*) FROM article WHERE id <=25
複製程式碼

題外話： 如果需要區分不同顏色的商品數量時，可以如下做法：

seelct count(color = 'blue' OR NULL) as blue,COUNT(color = 'red' OR NULL)  AS RED FROM items
複製程式碼

6.GROUP BY和DISTINCT

它們的優化最有效的方法就是用索引來。 但是GROUP BY有時候用得不對，索引是會失效的。 比如：把兩個單獨的索引合併成一個組合索引，即把where條件欄位的索引和group by的分組欄位索引組合成一個。

解決方法：參考這篇函式索引

7.limit分頁

下面這條查詢，非常常見。

select film_id,description from film order by title limit 50,5;
複製程式碼

但是如果這個表很大的時候，那麼這個50變成100654這樣子的話，這裡MySQL就要掃描100654+5條資料，然後丟棄100654條，僅僅去最後5條。 一種思路：

select film_id,description from film inner join (select film_id from film order by title limit 50,5) as lim USING(film_id);
複製程式碼

該思路是通過延遲關聯將大大提升查詢效率，它讓MySQL掃描儘可能少的頁面。獲取需要訪問的記錄後，再更加關聯列會原表查詢所需要的所有列。以上並不一定符合你，具體還需explain對比擇優使用。

小結： 總體來說都是圍繞著儘量少全表掃描，儘量使用索引進行優化。 最後往往是要自己在實際場景多用explain分析是否有更好的sql解決方案。

索引會失效的場景

1．隱式轉換導致索引失效. 這一點應當引起重視.也是開發中經常會犯的錯誤. 由於表的欄位tu_mdn定義為varchar2(20),但在查詢時把該欄位作為number型別以where條件傳給Oracle,這樣會導致索引失效.

 錯誤的例子：select * from test where tu_mdn=13333333333;
 正確的例子：select * from test where tu_mdn='13333333333';
複製程式碼

2. 對索引列進行運算導致索引失效 所指的對索引列進行運算**包括(+，-，*，/，! 等)

 錯誤的例子：select * from test where id-1=9;
 正確的例子：select * from test where id=10;
複製程式碼

3. 使用內部函式導致索引失效. 對於這樣情況應當建立基於函式的索引.

// 錯誤的例子：
select * from test where round(id)=10; //說明，此時id的索引已經不起作用了
 //正確的例子：首先建立函式索引
create index test_id_fbi_idx on test(round(id));
//然後 
select * from test where round(id)=10; 
複製程式碼

4. 不要將空的變數值直接與比較運算子（符號）比較。 如果變數可能為空，應使用 IS NULL 或 IS NOT NULL 進行比較，或者使用 ISNULL 函式。

5. 不要在 SQL 程式碼中使用雙引號。 因為字元常量使用單引號。如果沒有必要限定物件名稱，可以使用（非 ANSI SQL 標準）括號將名稱括起來。

6. 以下使用會使索引失效，應避免使用

  • a. 使用 <> 、not in 、not exist、!=
  • b. like "%_" 百分號在前（可採用在建立索引時用reverse(columnName)這種方法處理）
  • c. 單獨引用複合索引裡非第一位置的索引列.應總是使用索引的第一個列，如果索引是建立在多個列上, 只有在它的第一個列被where子句引用時，優化器才會選擇使用該索引。
  • d. 字元型欄位為數字時在where條件裡不新增引號.
  • e. 當變數採用的是times變數，而表的欄位採用的是date變數時.或相反情況。

暫時統計到這麼多，如果有更多的以後再補充。

MySQL的EXPLAIN的使用

EXPLAIN是用來分析SQL執行情況分析的

EXPLAIN 命令的輸出內容大致如下:

mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
複製程式碼

各列的含義如下:

  • id: SELECT 查詢的識別符號. 每個 SELECT 都會自動分配一個唯一的識別符號.
  • select_type: SELECT 查詢的型別.
  • table: 查詢的是哪個表
  • partitions: 匹配的分割槽
  • type: join 型別
  • possible_keys: 此次查詢中可能選用的索引
  • key: 此次查詢中確切使用到的索引.
  • ref: 哪個欄位或常數與 key 一起被使用
  • rows: 顯示此查詢一共掃描了多少行. 這個是一個估計值.
  • filtered: 表示此查詢條件所過濾的資料的百分比
  • extra: 額外的資訊

更詳細的可以參考這篇【效能優化神器 Explain 使用分析】或者【高效能MySQL】

總結

查詢優化目的就是為了快速得到結果，所以每當寫完SQL應該思考以下幾點：

  • 是否需要全表查詢以及返回的資料是否合理。
  • 是否需要索引，索引是否合理。
  • 是否有更好的解決辦法。

最後

如果對 Java、大資料感興趣請長按二維碼關注一波，我會努力帶給你們價值。覺得對你哪怕有一丁點幫助的請幫忙點個贊或者轉發哦。 關注公眾號**【愛編碼】，回覆2019**有相關資料哦。

【資料庫】MySQL查詢優化

相關文章