概述

需求：取出內部員工組（data_type=0）下的用戶編號（user_id），用戶名稱（user_name）、用戶密碼（ PASSWORD_ENCRYPTED） ， 並 按 照 轉正日期 的 時間（regul_date）來進行倒序排列，取出前 20條。

這裡大家可以考慮下如果是你，會怎麼去實現？

方案選擇

方案1：

SELECT
 s.user_id,
 s.user_name,
 s.PASSWORD_ENCRYPTED 
FROM
 hr_employee h,\tsys_user s 
WHERE h.data_type = 0 AND h.EMPLOYEE_CODE = s.EMPLOYEE_CODE 
ORDER BY\th.regul_date DESC 
 LIMIT 1000,\t20

方案2：

SELECT
 s.user_id,
 s.user_name,
 s.PASSWORD_ENCRYPTED 
FROM
 ( SELECT EMPLOYEE_CODE FROM hr_employee WHERE data_type = 0 ORDER BY regul_date DESC LIMIT 1000, 20 ) h,
 sys_user s 
WHERE
 h.EMPLOYEE_CODE = s.EMPLOYEE_CODE

執行計劃對比分析

方案1：

解決方案一中的執行計劃顯示 MySQL 在對其中一個參與 Join 的表（sys_user）利用到了索引，hr_employee表走了全表掃描（其實也可以弄索引），在參與 Join 前 hr_employee表通過 Where 過濾後的結果集與 user 表進行 Join，最後通過排序取出Join 後結果的「limit 1000,20」條結果返回。

方案2：

解決方案二的 SQL 語句利用到了子查詢，所以執行計劃會稍微複雜一些，首先可以看到sys_user表和解決方案1一樣都利用到了索引（所使用的索引也完全一樣），執行計劃顯示該子查詢以hr_employee表為驅動，也就是先通過employee_code（工號）進行過濾並馬上進行這一輪的結果集排序，然後取得了 SQL 中的「limit 1000,20」條結果，最後與sys_user 表進行 Join，得到相應的數據。

通過比較兩個解決方案的執行計劃，可以看到解決方案一中需要和sys_user表參與Join的記錄 數， MySQL 通過統計數據估算出來是8506，也就是通過 hr_employee表返回的所有滿足data_type=0的記錄數（系統中的實際數據是20000）。而第二種解決方案的執行計劃中，sys_user表參與Join的數據就只有850條，可以看到兩者相差很大，所以第二種解決方案明顯優於第一種解決方案。

SQL 實際執行的profile對比

打開 profiling 功能，然後分別執行兩個解決方案的 SQL 語句，由 於SQL語句執行所消耗的最大兩部分資源就是IO和CPU，所以這裡僅列出BLOCK IO和 CPU兩項 profile 信息，涉及sql如下

--開啟profiling功能及歷史條數
SET PROFILING=1;
SET PROFILING_HISTORY_SIZE=1000;
SELECT s.user_id,s.user_name,s.PASSWORD_ENCRYPTED FROM\thr_employee h,\tsys_user s WHERE h.data_type = 0 \t
AND h.EMPLOYEE_CODE = s.EMPLOYEE_CODE ORDER BY h.regul_date DESC \tLIMIT 1000,\t20;
SELECT\ts.user_id,\ts.user_name,\ts.PASSWORD_ENCRYPTED FROM\t( SELECT EMPLOYEE_CODE FROM hr_employee WHERE data_type = 0 
ORDER BY regul_date DESC LIMIT 1000, 20 ) h,\tsys_user s WHERE\th.EMPLOYEE_CODE = s.EMPLOYEE_CODE;
--查看profile信息
show profiles;
show profile CPU,BLOCK IO for query 329;
show profile CPU,BLOCK IO for query 336;

觀察兩條 SQL 執行中的 IO 消耗，兩者區別就在於「Sorting result」，從前面執行計劃的對比可以知道兩個解決方案的排序過濾數據的時機不一樣，排序後需要取得的數據量一個是8506，一個是850，正好和這裡的 profile 信息吻合，第一種解決方案的 「Sorting result」的 IO 值是第二種解決方案的將近3倍。

結論：

通過上面兩條功能完全相同的 SQL 語句的執行計劃分析，以及通過實際執行後的profile 數據的驗證，都證明了第二種解決方案優於第一種解決方案。

覺得有用的朋友多幫忙轉發哦！後面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注下~