背景

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

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

原理

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

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

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

•客戶端發送一條查詢給服務器。•服務器先檢查查詢緩存，如果命中了緩存，則立刻返回查詢在緩存中的結果。否則會進入下一個階段。•服務端進行SQL解析、預處理、再由優化器生成對應的執行計劃。•MySQL根據優化器生成的執行計劃，調用存儲引擎的API來執行查詢。•將結果返回給客戶端。

是什麼導致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的分組字段索引組合成一個。

解決方法：參考這篇函數索引[1]

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 使用分析[2]】或者【高性能MySQL[3]】

總結

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

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