本文總結了一些工作常見的sql優化例子,雖然比較簡單,但很實用,希望對大家有所幫助。sql優化一般分為兩類,一類是sql本身的優化,如何走到合适的索引,如何減少排序,減少邏輯讀;另一類是sql本身沒有優化餘地,需要結合業務場景進行優化。即在滿足業務需求的情況下對sql進行改造,已提高sql執行速度,減少響應時間。
例子1:
SELECT ID FROM SENDLOG WHERE TO_DAYS(NOW())-TO_DAYS(GMT_CREATE) > 7;
問題:對索引列GMT_CREATE進行了運算,無法使用索引
優化後sql:
select id from sendlog where gmt_create < now() - 7
例子2:
SELECT * FROM SENDLOG where result = 1 and gmt_create > '2013-10-29 12:40:44' limit 2000;
問題:result列含有索引,但mysql執行計劃沒有用到。
分析:result類型為char,傳遞的值1為整型,資料類型不一緻,導緻沒法用索引,對于時間類型gmt_create > '2013-10-29 12:40:44',可以直接使用。
SELECT * FROM SENDLOG where result = '1' and gmt_create > '2013-10-29 12:40:44' limit 2000;
例子3:
場景:擷取某個賣家未讀的消息。
select count(*) from mc_msg where receiver='sun098' and status='UNREAD' and title is not null;
問題:有時候db負載飙高,sql響應時間變慢。
分析:導緻db負載飙高的原因是多個大賣家并發查詢的時,cpu和邏輯讀增加,load飙高。由于receiver,status已有索引,sql本身已經沒有優化空間,了解業務後發現其實業務不需要精确值,如果大于99條,頁面就直接顯示為99+
select count(*) from (select id from mc_msg where receiver='sun098' and status='UNREAD' and title is not null limit 100) a;
例子4:
場景:檢視曆史訂單留言記錄,未讀留言的放在前面,已讀的放在後面,并且按時間遞減排序
select * from(
select ID,GMT_CREATE,GMT_MODIFIED,SENDER_ALI_ID,RECEIVER_ALI_ID,UNREAD_COUNT,STATUS,LAST_MESSAGE_ID,RELATION_ID,SELLER_ADMIN_SEQ,IS_READ
from message_relation_sender
WHERE SENDER_ALI_ID = 119545671 and UNREAD_COUNT > 0
order by LAST_MESSAGE_ID desc) m
union all
select ID,GMT_CREATE,GMT_MODIFIED,SENDER_ALI_ID,RECEIVER_ALI_ID,UNREAD_COUNT,STATUS,LAST_MESSAGE_ID,RELATION_ID,SELLER_ADMIN_SEQ,IS_READ from message_relation_sender
WHERE SENDER_ALI_ID = 119545671 and UNREAD_COUNT = 0
order by LAST_MESSAGE_ID desc) n limit 5000,15;
分析:
(1)unread_count表示未讀的訂單留言記錄數目;
(2)第一個子查詢擷取未讀留言記錄,第二子查詢擷取已讀留言記錄;
(3)LAST_MESSAGE_ID 遞增,最新的訂單留言,LAST_MESSAGE_ID最大。
(4)limit 5000,15是分頁查詢
這裡使用union all,主要特點在于union all 不對結果集排序,直接合并,達到了“未讀留言的放在前面,已讀的放在後面”的效果,但同時也造成了兩次掃描索引的結果,每個子查詢都需要排序;而且union all還會産生臨時表,執行代價會更大。
優化:
這裡看到unread_count實際值對這個查詢沒有實際意義,我們隻需要區分已讀和未讀即可。由于sql本身已經沒有優化餘地,考慮對表結構進行修改,加一個字段is_read,表示已讀和未讀。is_read=2表示未讀;is_read=1表示已讀。通過組合索引(SENDER_ALI_ID,is_read, LAST_MESSAGE_ID),既可以完成過濾,還可以完成排序。
where SENDER_ALI_ID = 119545671 order by is_read desc, LAST_MESSAGE_ID desc limit 5000,15
第二次優化:
由于索引不包含所有的傳回字段,是以需要回表,而mysql對于limit 5000,15的查詢卻需要傳回5015次,這種無效的傳回很影響查詢效率。
分頁的優化寫法:
select t1.ID,GMT_CREATE,GMT_MODIFIED,SENDER_ALI_ID,RECEIVER_ALI_ID,UNREAD_COUNT,STATUS,LAST_MESSAGE_ID,RELATION_ID,SELLER_ADMIN_SEQ,IS_READ from message_relation_sender t1,
(select id
from message_relation_sender
where SENDER_ALI_ID = 119545671 order by is_read desc, LAST_MESSAGE_ID desc limit 5000,15
)t2 where t1.id = t2.id
分析:由于id是主鍵,不需要回表,通過連接配接查詢,最終隻需要15次回表即可。