一、概述

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

二、拼表優(yōu)化：FROM和JOIN

• 請參考：SQL優(yōu)化(一)：MySQL多表查詢FROM和JOIN的用法與性能優(yōu)化

三、WHERE查詢優(yōu)化

• WHERE查詢條件優(yōu)化是SQL語句優(yōu)化最重要的一個(gè)環(huán)節(jié)，WHERE子句的優(yōu)化主要從索引的利用和條件的順序兩個(gè)方面。

1. 索引的利用

• 通過給查詢列增加索引可以避免全表掃描加快數(shù)據(jù)檢索速度，同時(shí)覆蓋索引還可以避免回表查詢，只通過索引即可返回所需要的數(shù)據(jù)，索引相關(guān)的內(nèi)容可以參考：MySQL學(xué)習(xí)(七)：Innodb存儲(chǔ)引擎索引的實(shí)現(xiàn)原理
• 如果WHERE查詢條件中的索引列使用方法不當(dāng)，則會(huì)導(dǎo)致索引失效，從而進(jìn)行全表掃描，以下來分析索引失效的情況：SQL優(yōu)化(二)：MySQL索引失效的六種場景與優(yōu)化方法

2. 查詢條件的順序

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

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

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

explain的結(jié)果如下：主要關(guān)注possible_keys和key列，如下說明使用主鍵PRIMARY最高效。

四、子查詢的使用和優(yōu)化

• 請參考：SQL優(yōu)化(三)：子查詢和IN，EXISTS用法和優(yōu)化方法

五、ORDER BY 排序優(yōu)化

• ORDER BY的排序優(yōu)化主要是利用索引的有序性來進(jìn)行排序，從而減少M(fèi)ySQL服務(wù)器的排序操作，因?yàn)樵诜?wù)端進(jìn)行排序通常需要額外的內(nèi)存空間，通常通過sort_buffer_size來控制每個(gè)MySQL服務(wù)器線程的sort buffer的大小，如果內(nèi)存空間不夠，則需要通過磁盤文件來輔助。所以如果能利用索引的有序性來完成排序而可以提高性能。
• 所以O(shè)RDER BY的優(yōu)化就轉(zhuǎn)變?yōu)楸苊馑饕У膬?yōu)化了，即盡可能使用主鍵進(jìn)行排序；如果不能使用主鍵來排序，則對于order by的列加上索引，并且如果可以使用覆蓋索引，則通過建立聯(lián)合索引來實(shí)現(xiàn)直接從索引返回?cái)?shù)據(jù)；對于聯(lián)合索引需要注意最左前戳匹配規(guī)則。如下，查詢某個(gè)用戶的所有訂單并且根據(jù)購買日期排序，由執(zhí)行計(jì)劃可知，使用了聯(lián)合索引idx_user_id_buy_date。

• 如果存在聯(lián)合索引，但是不遵循最左前戳規(guī)則，則無法使用索引來排序，如下將user_id和buy_date反過來則無法使用聯(lián)合索引idx_user_id_buy_date了，由Using filesort可知需要在MySQL服務(wù)器進(jìn)行排序。

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

explain的結(jié)果如下：

六、LIMIT分頁優(yōu)化

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

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

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

2. 子查詢優(yōu)化

• 可以通過子查詢來對表的索引進(jìn)行查找，獲取這個(gè)范圍的id，從而避免對數(shù)據(jù)表進(jìn)行掃描，然后在數(shù)據(jù)表中取出匹配的數(shù)據(jù)行，如下：由于一頁數(shù)據(jù)通常較少，故子查詢

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的結(jié)果如下：

七、GROUP BY 分組優(yōu)化

• GROUP BY操作通常會(huì)進(jìn)行排序操作，而通過GROUP BY一般是與聚集函數(shù)，如SUM，COUNT，MAX等來結(jié)合使用從而完成統(tǒng)計(jì)任務(wù)，故一般不需要進(jìn)行排序，如下：統(tǒng)計(jì)每個(gè)用戶今天的訂單總金額：

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)

• 執(zhí)行計(jì)劃如下：由extra的 Using filesort 可知，需要在MySQL服務(wù)器進(jìn)行排序，但是此時(shí)并不需要該排序操作。

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

explain的結(jié)果如下：

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

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

explain的結(jié)果如下：

總結(jié)

以上是生活随笔為你收集整理的db2分页查询sql语句_MySQL学习（八）：SQL查询语句的用法和优化的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。