mysql最佳化之路

標籤：

 

下面總結了一些工作常見的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‘，可以直接使用。

最佳化後sql：

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+

最佳化後sql：

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_senderWHERE SENDER_ALI_ID = 119545671  and UNREAD_COUNT > 0 order by LAST_MESSAGE_ID desc) m union all 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_senderWHERE 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),既可以完成過濾，還可以完成排序。 最佳化後sql：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_senderwhere 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次回表即可。

mysql最佳化之路