京東二面：日常工作中，你是如何優化SQL的？
大家好，我是田螺。
我們去面試的時候，經常被問到，日常工作中，是如何優化SQL的。今天跟大家再聊聊哈。
這里應該如何去回答呢？可以從各種不同維度的，今天我再換個角度。
- 加索引
- 避免常見的索引不生效場景
- 避免返回不必要的數據
- 減少不必要的邏輯
- 分批量進行思想
- 讀寫分離
- 優化sql結構
- 分庫分表
- 性能優化分析神器—explain
- 慢SQL排查思路
1. 加索引
很多時候，我們的慢查詢，都是因為歷史原因沒有加索引，或者忘記加索引導致的。如果沒有加索引的話，會導致全表掃描的。因此，應考慮在where的條件列，建立索引，盡量避免全表掃描。
反例：
select * from user_info where name ='撿田螺的小男孩公眾號' ;正例：
//添加索引
alter table user_info add index idx_name (name);2. 避免常見的索引不生效場景
我之前整理了常見的十種索引不生效的場景，大家可以看看：
- 隱式的類型轉換，索引失效
- 查詢條件包含or，可能導致索引失效
- like通配符可能導致索引失效
- 查詢條件不滿足聯合索引的最左匹配原則
- 在索引列上使用mysql的內置函數
- 對索引進行列運算(如，+、-、*、/)
- 索引字段上使用(!=或者<>)，索引可能失效
- 索引字段上使用is null，is not null，索引可能失效
- 左右連接，關聯的字段編碼格式不一樣
- 優化器選錯了索引
3. 避免返回不必要的數據
這個點，我在昨天的文章，其實就提到一個點，包括盡量使用limit，避免不必要的返回。
其實這不僅僅是一個點，而是一種思想，就是要什么查什么，而不是返回一些不必要的數據。還有：查詢SQL盡量不要使用select *，而是select具體字段。也是這種思想。
反例子：
select * from employee;正例子：
select id，name, age from employee;- select具體字段，節省資源、減少網絡開銷。
- select * 進行查詢時，很可能就不會使用到覆蓋索引了，就會造成回表查詢。
4. 減少不必要的邏輯
其實，盡量用 union all 替換 union，就是這種思想。
如果我們明知道，檢索結果中不會有重復的記錄，推薦union all 替換 union。
因為：
如果使用union，不管檢索結果有沒有重復，都會嘗試進行合并，然后在輸出最終結果前進行排序。如果已知檢索結果沒有重復記錄，使用union all 代替union，這樣會提高效率。
5. 分批量進行思想
我們更推薦批量查詢、插入、刪除。
反例：
for(User u :list){
INSERT into user(name,age) values(#name#,#age#)
}正例：
//一次500批量插入，分批進行
insert into user(name,age) values
<foreach collectinotallow="list" item="item" index="index" separator=",">
(#{item.name},#{item.age})
</foreach>理由：
- 批量插入性能好，更加省時間
- 打個比喻: 假如你需要搬一萬塊磚到樓頂,你有一個電梯,電梯一次可以放適量的磚（最多放500）,你可以選擇一次運送一塊磚,也可以一次運送500,你覺得哪個時間消耗大?
6. 讀寫分離
一般情況我們的數據庫架構，都要做主從的，然后進行讀寫分離。主庫主要負責寫，和一些實時性比較高的讀。而從庫就負責讀實時性要求不高的請求。
圖片
這樣的話，我們不用所有請求都到主庫，大大降低了主庫的壓力。你試想一下，如果所有讀請求都到主庫，查詢壓力肯定很大，處理也會相對慢一點。
7. 優化sql結構、邏輯
有些時候，優化SQL結構，都能有一些預想不到的優化效果。
假設我們有個客戶表和一個訂單表。其中訂單表有10萬記錄，客戶表只有1000行記錄。
現在要查詢下單過的客戶信息，可以這樣寫：
SELECT * FROM customers
WHERE id IN (
SELECT customer_id FROM orders
);
in 查詢會先執行內部查詢部分 SELECT customer_id FROM orders，獲得 orders 表（大表）中的所有 customer_id，然后在 customers 表（小表）中查找匹配的 id。
也可以這樣實現：
SELECT * FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);EXISTS 會逐行掃描 customers 表（即小表），對每一行 c.id，在 orders 表（大表）中檢查是否有 customer_id = c.id 的記錄。
因為orders表的數據量比較大，因此這里用exists效果會相對更好一點。其實這就是小表驅動大表的思想。我們也只是調整SQL結構，用exists去替換in，優化效果也是比較明顯的。
8. 分庫分表
如果單表的數據量很大，達到百萬甚至千萬級別，我們這種時候，就是加了索引，可能效果也不是很明顯。這時候我們可以考慮分庫分表啦~~
分庫分表一般都是依賴客戶號、用戶Id、或者時間來拆分。但是需要注意一下，分庫分表存在的一些一些問題：
- 事務問題
- 跨庫關聯JOIN
- 排序問題
- 分頁問題
- 分布式ID選擇
9. 性能優化分析神器—explain
之前我寫SQL習慣的時候，有提到explain，就是每次寫完查詢SQL，都用explain看一下它的執行計劃。
有些面試官會單獨問這個，我們可以走面試官的路，讓面試官無路可走。在回答SQL優化的時候，就把這個回答了。
一般在使用explain的時候，我們要關注:type、rows、filtered、extra、key。
9.1 type
type表示連接類型，查看索引執行情況的一個重要指標。以下性能從好到壞依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
- system：這種類型要求數據庫表中只有一條數據，是const類型的一個特例，一般情況下是不會出現的。
- const：通過一次索引就能找到數據，一般用于主鍵或唯一索引作為條件，這類掃描效率極高，，速度非常快。
- eq_ref：常用于主鍵或唯一索引掃描，一般指使用主鍵的關聯查詢
- ref : 常用于非主鍵和唯一索引掃描。
- ref_or_null：這種連接類型類似于ref，區別在于MySQL會額外搜索包含NULL值的行
- index_merge：使用了索引合并優化方法，查詢使用了兩個以上的索引。
- unique_subquery：類似于eq_ref，條件用了in子查詢
- index_subquery：區別于unique_subquery，用于非唯一索引，可以返回重復值。
- range：常用于范圍查詢，比如：between ... and 或 In 等操作
- index：全索引掃描
- ALL：全表掃描
9.2 rows
該列表示MySQL估算要找到我們所需的記錄，需要讀取的行數。對于InnoDB表，此數字是估計值，并非一定是個準確值。
9.3 filtered
該列是一個百分比的值，表里符合條件的記錄數的百分比。簡單點說，這個字段表示存儲引擎返回的數據在經過過濾后，剩下滿足條件的記錄數量的比例。
9.4 extra
該字段包含有關MySQL如何解析查詢的其他信息，它一般會出現這幾個值：
- Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情況才會出現。一般見于order by語句
- Using index ：表示是否用了覆蓋索引。
- Using temporary: 表示是否使用了臨時表,性能特別差，需要重點優化。一般多見于group by語句，或者union語句。
- Using where : 表示使用了where條件過濾.
- Using index condition：MySQL5.6之后新增的索引下推。在存儲引擎層進行數據過濾，而不是在服務層過濾，利用索引現有的數據減少回表的數據。
9.5 key
該列表示實際用到的索引。一般配合possible_keys列一起看。
10. 慢SQL排查思路
如果大家平時有優化過生產的慢SQL，有自己的一套排查那一套最好哈。如果沒有的話，可以在自己搞個深分頁，或者因為數據量、或者因為沒加索引等原因，導致的慢SQL，然后按照這個思路去排查一遍。
- 查看慢查詢日志記錄，分析慢SQL
- explain分析SQL的執行計劃
- profile 分析執行耗時
- Optimizer Trace分析詳情
- 確定問題并采用相應的措施
如果不熟悉的話，可以多操作幾遍，盡量熟悉操作流程，在面試的時候，講一下這個主要流程。
