mysql優化案例分析

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;

<div> 
 <p align="left"><span>場景：獲取某個賣家未讀的消息。</span></p>

 <p align="left"><span lang="EN-US">select count(*) from mc_msg where receiver='sun098' and status='UNREAD' and title is not null;</span></p>

 <p align="left"><span lang="EN-US"><span>問題：有時候db負載飆高，sql響應時間變慢。</span></span></p>

 <p align="left"><span>分析：導致db負載飆高的原因是多個大賣家并發查詢的時，</span><span lang="EN-US">cpu</span><span>和邏輯讀增加，</span><span lang="EN-US">load</span><span>飆高。由于receiver,status已有索引，sql本身已經沒有優化空間，</span><span>了解業務后發現其實業務不需要精確值，如果大于</span><span lang="EN-US">99</span><span>條，頁面就直接顯示為</span><span lang="EN-US">99+</span></p>

 <p align="left"><span>優化后</span><span lang="EN-US">sql</span><span>：</span></p>

 <p align="left"></p>

select count(*) from (select id from mc_msg where receiver='sun098' and status='UNREAD' and title is not null limit 100) a;

 <p align="left"><span style="color:#000000;" lang="EN-US">例子4：</span></p>

 <p align="left">場景：查看歷史訂單留言記錄，未讀留言的放在前面，已讀的放在后面，并且按時間遞減排序</p>

 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 * 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) n limit 5000,15; 

 <div>
  分析：
 </div>

 <div>
  (1)unread_count表示未讀的訂單留言記錄數目；
 </div>

 <div>
  (2)第一個子查詢獲取未讀留言記錄，第二子查詢獲取已讀留言記錄；
 </div>

 <div>
  (3)LAST_MESSAGE_ID 遞增，最新的訂單留言，LAST_MESSAGE_ID最大。
 </div>

 <div>
  (4)limit 5000,15是分頁查詢
 </div>

 <div>
  這里使用union all，主要特點在于union all 不對結果集排序，直接合并，達到了“未讀留言的放在前面，已讀的放在后面”的效果，但同時也造成了兩次掃描索引的結果，每個子查詢都需要排序；而且union all還會產生臨時表，執行代價會更大。
 </div>

 <div>
   
 </div>

 <div>
  優化：
 </div>

 <div>
  這里看到unread_count實際值對這個查詢沒有實際意義，我們只需要區分已讀和未讀即可。由于sql本身已經沒有優化余地，考慮對表結 構進行修改，加一個字段is_read，表示已讀和未讀。is_read=2表示未讀；is_read=1表示已讀。通過組合索引 (SENDER_ALI_ID,is_read, LAST_MESSAGE_ID),既可以完成過濾，還可以完成排序。
 </div>

 <div>
   
 </div>

 <div>
  優化后sql：
 </div>

 <div><pre class="brush:sql; toolbar: true; auto-links: false;"> 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 order by is_read desc, LAST_MESSAGE_ID desc limit 5000,15 </pre>

  <div>
   由于索引不包含所有的返回字段，因此需要回表，而mysql對于limit 5000,15的查詢卻需要返回5015次，這種無效的返回很影響查詢效率。
  </div>

 </div>

 <p align="left">分頁的優化寫法：</p>

</div>

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
  

</div>

</div>