公司訂單系統每日訂單量龐大，有很多表數據超千萬。公司SQL優化這塊做的很不好，可以說是沒有做，所以導致查詢很慢。 正題 節選某個功能中的一句SQL EXPLAIN 查看執行計劃 EXPLAIN + SQL 查看SQL執行計劃 一個索引沒用到，受影響行接近2000萬，難怪會慢。 原來的SQL列印出來估 ...
公司訂單系統每日訂單量龐大，有很多表數據超千萬。公司SQL優化這塊做的很不好，可以說是沒有做，所以導致查詢很慢。
正題
節選某個功能中的一句SQL EXPLAIN 查看執行計劃
EXPLAIN + SQL 查看SQL執行計劃
一個索引沒用到，受影響行接近2000萬，難怪會慢。
原來的SQL列印出來估計有好幾張A4紙，我發個整理後的簡版。
SELECT COUNT(t.w_order_id) lineCount, SUM(ROUND(t.feel_total_money / 100, 2)) AS lineTotalFee, SUM(ROUND(t.feel_fact_money / 100, 2)) AS lineFactFee FROM w_orders_his t WHERE 1=1 AND DATE_FORMAT(t.create_time, '%Y-%m-%d') >= STR_TO_DATE(#{beginTime},'%Y-%m-%d') AND DATE_FORMAT(t.create_time, '%Y-%m-%d') <= STR_TO_DATE(#{endTime},'%Y-%m-%d') AND t.pay_state = #{payState} AND t.store_id LIKE '%#{storeId}%' limit 0,10
這條sql需求是在兩千萬的表中撈出指定時間和條件的訂單進行總數總金額彙總處理。
優化sql需要根據公司的業務，技術的架構等，且針對不同業務每條SQL的優化都是有差異的。
優化點1：
AND DATE_FORMAT(t.create_time, '%Y-%m-%d') >= STR_TO_DATE(#{beginTime},'%Y-%m-%d')
AND DATE_FORMAT(t.create_time, '%Y-%m-%d') <= STR_TO_DATE(#{endTime},'%Y-%m-%d')
我們知道sql中絕對要減少函數的使用，像左邊DATE_FORMAT(t.create_time, '%Y-%m-%d') 是絕對禁止使用的，如果資料庫有一百萬數據那麼就會執行一百萬次函數，非常非常影響效率。右邊STR_TO_DATE(#{beginTime},'%Y-%m-%d')的函數會執行一次，但還是不建議使用函數。所以去掉函數直接使用 >=,<= 或BETWEEN AND速度就會快很多，但有的資料庫設計時間欄位只有日期沒有時間，所以需要在日期後面拼接時間如："2017-01-01" + " 00:00:00"。
更好的辦法是用時間戳，資料庫中存時間戳，然後拿時間戳去比較，如：BETWEEN '開始時間時間戳' AND '結束時間時間戳'
優化點2：
AND t.store_id LIKE '%#{storeId}%'
這句使用了LIKE並且前後匹配，前後匹配會導致索引失效，一般情況下避免使用，應該改成 AND t.store_id LIKE '#{storeId}%'
優化點3：
一般利用好索引，根據主鍵、唯一索引查詢某一條記錄，就算上億數據查詢也是非常快的。但這條sql需要查詢數據統計需要用到COUNT和SUM，所以可以建立聯合索引。
聯合索引有一點需要註意：key index (a,b,c)可以支持a | a,b| a,b,c 3種組合進行查找，但不支持 b,c進行查找 ，當最左側欄位是常量引用時，索引就十分有效。
所以把必要欄位排放在左邊key index(create_time,w_order_id,feel_total_money,feel_fact_money,payState,storeId)
結果
優化之前大概幾分鐘，現在是毫秒級。其實改的東西也不多，避免在語句上踩雷，善用EXPLAIN查詢SQL效率。
有時間我會舉點別的SQL優化的例子
說幾點平常可以優化的地方
- JOIN 後的的條件必須是索引，最好是唯一索引，否則數據一旦很多會直接卡死
- 一般禁止使用UNIION ON，除非UNION ON 前後的記錄數很少
- 禁止使用OR
- 查總數使用COUNT(*)就可以，不需要COUNT(ID)，MYSQL會自動優化
- 資料庫欄位設置 NOT NULL，欄位類型 INT > VARCHAR 越小越好
- 禁止SELECT * ，需要確定到使用的欄位
- 一般情況不在SQL中進行數值計算
- SQL要寫的簡潔明瞭
參考
EXPLAIN type（從上到下，性能從差到好）
- all 全表查詢
- index 索引全掃描
- range 索引範圍掃描
- ref 使用非唯一或唯一索引的首碼掃描,返回相同值的記錄
- eq_ref 使用唯一索引，只返回一條記錄
- const,system 單表中最多只有一行匹配,根據唯一索引或主鍵進行查詢
- null 不訪問表或索引就可以直接得到結果
MYSQL 五大引擎
- ISAM ：讀取快,不占用記憶體和存儲資源。 不支持事物,不能容錯。
- MyISAM ：讀取塊，擴展多。
- HEAP ：駐留在記憶體里的臨時表格,比ISAM和MyISAM都快。數據是不穩定的,關機沒保存,數據都會丟失。
- InnoDB ：支持事物和外鍵，速度不如前面的引擎塊。
- Berkley（BDB） ：支持事物和外鍵，速度不如前面的引擎塊。
一般需要事物的設為InnoDB，其他設為MyISAM
