order by limit 引發的思考

MairaDBA發表於2017-02-17
MIT

SQL語句如下：

EXPLAIN 
SELECT id,PushData 
FROM UserCardPushlog 
WHERE status = 0 
  AND HANDleLock = 0 
ORDER BY CreateTime 
LIMIT 2000

行數：2200W

status、CreateTime 均有索引

執行計劃如下：
type key rows extra
index ix_UserCardPushlog_CreateTime 88469 Using where
執行時間：33s

當去掉order by、limit時執行計劃如下：
type key rows extra
ref ix_UserCardPushlog_status 350140 Using where
執行時間：1s

Q1：status、CreateTime上都有索引為何執行時間相差這麼多？

Q2：status 欄位上的索引為什麼沒有被使用？

  • 單路排序與雙路排序

1) 雙路排序:是首先根據相應的條件取出相應的排序欄位和可以直接定位行資料的行指標資訊，然後在sort buffer 中進行排序。

2) 單路排序:是一次性取出滿足條件行的所有欄位，然後在sort buffer中進行排序。

所有欄位長度總數小於max_length_for_sort_data，則使用單路排序，否則使用雙路排序。

當前伺服器max_length_for_sort_data配置為1024，而表UserCardPushlog所有欄位長度總數大於max_length_for_sort_data，也就是說當前SQL使用的是單路排序。

可以看到type為index，說明掃描了CreateTime欄位的所有資料然後進行排序。所以很慢。

Q2 是否可以理解為如果SQL查詢的是單表並且包含order by且有索引，那麼就將會使用order by 欄位後的索引進行排序。最後才使用where條件進行過濾？

優化方案：使用status過濾資料後再進行排序。

  1. 使用子查詢過濾資料後進行排序，如下SQL仍然沒有使用status的索引。

    EXPLAIN 
SELECT id,PushData 
FROM
  (SELECT 
    id,
    PushData,
    CreateTime as s
  FROM
    UserCardPushlog 
  WHERE status = 0 
    AND HANDleLock = 0) as t 
ORDER BY t.s 
LIMIT 2000 ;

  2. 最終解決方案：強制使用索引FORCE INDEX

    EXPLAIN 
SELECT id,PushData 
FROM UserCardPushlog 
FORCE INDEX(ix_UserCardPushlog_status)
WHERE status = 0 
  AND HANDleLock = 0 
ORDER BY CreateTime 
LIMIT 2000

  3. 另一種解決方案可以參考一下：where 條件後面加上CreateTime的過濾條件，這樣index就會變成range，時間也只需要15s左右。SQL如下：

    EXPLAIN 
SELECT id,PushData 
FROM UserCardPushlog 
WHERE status = 0 
  AND HANDleLock = 0 
 AND CreateTime >=`2017-01-01`
ORDER BY CreateTime 
LIMIT 2000

微訊號：MariaDBA
QQ：3543400
未經允許禁止轉載

相關文章