一、概述

• mysql的性能優化可以從機器硬件，如磁盤，内存等；MySQL服務器配置，如線程數，查詢緩存等；MySQL的主從分離和分庫分表等；SQL語句優化等。其中SQL語句優化是與日常開發密切相關的，而且也是MySQL優化中最重要的一個環節，因為MySQL服務器，機器等的資源是一定的，故當出現性能瓶頸時，首先需要排除是否為SQL執行問題，如通過開啟MySQL慢日志統計執行慢的SQL，或者使用profile功能統計SQL執行涉及的CPU，内存，IO等資源開銷。
• 定位到存在性能問題的SQL之後，則可以通過explain命令來分析該SQL的執行情況，如索引使用，排序等，然後是針對該SQL進行優化，優化主要從查詢涉及的表，WHERE條件與是否使用和高效使用了索引，以及是否存在子查詢等方面展開。
• 要進行SQL優化，首先需要理解SQL的執行過程，具體可以參考：SQL解析順序與MySQL底層實現原理
• 以下分析以用戶表t_user和用戶訂單表t_order來分析：在t_order表的user_id列是引用t_user的id列的外鍵。訂單表和訂單清單條目表t_order_item，通過order_id來建立外鍵約束。

二、拼表優化：FROM和JOIN

• 請參考：SQL優化（一）：MySQL多表查詢FROM和JOIN的用法與性能優化

三、WHERE查詢優化

• WHERE查詢條件優化是SQL語句優化最重要的一個環節，WHERE子句的優化主要從索引的利用和條件的順序兩個方面。

1. 索引的利用

• 通過給查詢列增加索引可以避免全表掃描加快數據檢索速度，同時覆蓋索引還可以避免回表查詢，隻通過索引即可返回所需要的數據，索引相關的内容可以參考：MySQL學習（七）：Innodb存儲引擎索引的實現原理
• 如果WHERE查詢條件中的索引列使用方法不當，則會導緻索引失效，從而進行全表掃描，以下來分析索引失效的情況：SQL優化（二）：MySQL索引失效的六種場景與優化方法

2. 查詢條件的順序

• WHERE的查詢條件的順序主要是針對聯合索引而言，即聯合索引遵循最左前戳匹配規則，故需要保證在where中列從左到右，如聯合索引(a,b,c)，則需要保證where a=xx and b=xx（注意，如果是where b=xx and a=xx，也可以繼續使用該聯合索引），而如果是where b=xx，則無法使用索引。如下對t_order_item表建立了聯合索引idx_product_id_buy_date：當同時包含product_id和num或者隻包含product_id時，可以使用該聯合索引，如果隻包含num則無法使用聯合索引。

• 針對單列索引的情況，如果where的and條件中的列都包含索引或者某些沒有索引，都是由MySQL自行選擇使用其中一個MySQL優化器認為效率最高的索引，如下：user_id，order_id, buy_date均包含索引，則MySQL選擇使用了order_id這個主鍵索引：

mysql> explain select * from t_order where user_id=1 and order_id>2 and buy_date=curdate();

explain的結果如下：主要關注possible_keys和key列，如下說明使用主鍵PRIMARY最高效。

四、子查詢的使用和優化

• 請參考：SQL優化（三）：子查詢和IN，EXISTS用法和優化方法

五、ORDER BY 排序優化

• ORDER BY的排序優化主要是利用索引的有序性來進行排序，從而減少MySQL服務器的排序操作，因為在服務端進行排序通常需要額外的内存空間，通常通過sort_buffer_size來控制每個MySQL服務器線程的sort buffer的大小，如果内存空間不夠，則需要通過磁盤文件來輔助。所以如果能利用索引的有序性來完成排序而可以提高性能。
• 所以ORDER BY的優化就轉變為避免索引失效的優化了，即盡可能使用主鍵進行排序；如果不能使用主鍵來排序，則對于order by的列加上索引，并且如果可以使用覆蓋索引，則通過建立聯合索引來實現直接從索引返回數據；對于聯合索引需要注意最左前戳匹配規則。如下，查詢某個用戶的所有訂單并且根據購買日期排序，由執行計劃可知，使用了聯合索引idx_user_id_buy_date。

• 如果存在聯合索引，但是不遵循最左前戳規則，則無法使用索引來排序，如下将user_id和buy_date反過來則無法使用聯合索引idx_user_id_buy_date了，由Using filesort可知需要在MySQL服務器進行排序。

mysql> explain select * from t_order where buy_date=curdate() order by user_id;

explain的結果如下：

六、LIMIT分頁優化

• LIMIT分頁查詢優化主要是針對LIMIT index, count形式的SQL，即從index下标開始的count條記錄，如 LIMIT 10000, 50，取出第10000到10050這50條記錄，但是對于MySQL來說需要掃描前面的10000條記錄。所以可以基于以下思路來優化：

1. 記錄上一頁的有序的最大ID

• 記錄上一頁的最大ID，通常為遞增的主鍵值，或者遞增的索引列，則可以利用索引來進行過濾，主要是基于B 樹索引的特性來快速過濾掉大部分數據，如下：普通的limit index, count為全表掃描，使用order_id列則可以使用主鍵索引。

2. 子查詢優化

• 可以通過子查詢來對表的索引進行查找，獲取這個範圍的id，從而避免對數據表進行掃描，然後在數據表中取出匹配的數據行，如下：由于一頁數據通常較少，故子查詢

mysql> select * from t_order inner join (select order_id from t_order limit 10000, 10) as b on t_order.order_id=b.order_id; Empty set (0.02 sec) mysql> explain select * from t_order inner join (select order_id from t_order limit 10000, 10) as b on t_order.order_id=b.order_id;

explain的結果如下：

七、GROUP BY 分組優化

• GROUP BY操作通常會進行排序操作，而通過GROUP BY一般是與聚集函數，如SUM，COUNT，MAX等來結合使用從而完成統計任務，故一般不需要進行排序，如下：統計每個用戶今天的訂單總金額：

mysql> select user_id, SUM(cost) from t_order where buy_date=curdate() group by user_id; --------- ----------- | user_id | SUM(cost) | --------- ----------- | 2 | 1000 | --------- ----------- 1 row in set (0.01 sec)

• 執行計劃如下：由extra的 Using filesort 可知，需要在MySQL服務器進行排序，但是此時并不需要該排序操作。

mysql> explain select user_id, SUM(cost) from t_order where buy_date=curdate() group by user_id;

explain的結果如下：

• 所以可以通過 ORDER BY NULL 來禁止排序操作，如下：extra不再包含Using filesort信息，故在MySQL服務器不再進行排序操作。

mysql> explain select user_id, SUM(cost) from t_order where buy_date=curdate() group by user_id order by null;

explain的結果如下：

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!