《MYSQL教程order by limit 引發(fā)的思考》要點(diǎn)：
本文介紹了MYSQL教程order by limit 引發(fā)的思考，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

SQL語句如下：

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

行數(shù)：2200W

status、CreateTime 均有索引

執(zhí)行計(jì)劃如下：
type key rows extra
index ix_UserCardPushlog_CreateTime 88469 Using where
運(yùn)行時(shí)間：33s

當(dāng)去掉order by、limit時(shí)執(zhí)行計(jì)劃如下：
type key rows extra
ref ix_UserCardPushlog_status 350140 Using where
運(yùn)行時(shí)間：1s

Q1：status、CreateTime上都有索引為何執(zhí)行時(shí)間相差這么多?

Q2：status 字段上的索引為什么沒有被使用?

單路排序與雙路排序
1) 雙路排序:是首先根據(jù)相應(yīng)的條件取出相應(yīng)的排序字段和可以直接定位行數(shù)據(jù)的行指針信息,然后在sort buffer 中進(jìn)行排序.

2) 單路排序:是一次性取出滿足條件行的所有字段,然后在sort buffer中進(jìn)行排序.

所有字段長(zhǎng)度總數(shù)小于max_length_for_sort_data,則使用單路排序,否則使用雙路排序.

當(dāng)前服務(wù)器max_length_for_sort_data配置為1024,而表UserCardPushlog所有字段長(zhǎng)度總數(shù)大于max_length_for_sort_data,也就是說當(dāng)前SQL使用的是單路排序.

可以看到type為index,說明掃描了CreateTime字段的所有數(shù)據(jù)然后進(jìn)行排序.所以很慢.

Q2 是否可以理解為如果SQL查詢的是單表并且包含order by且有索引,那么就將會(huì)使用order by 字段后的索引進(jìn)行排序.最后才使用where條件進(jìn)行過濾?

優(yōu)化方案：使用status過濾數(shù)據(jù)后再進(jìn)行排序.

使用子查詢過濾數(shù)據(jù)后進(jìn)行排序,如下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 ;
最終解決方案：強(qiáng)制使用索引FORCE INDEX
EXPLAIN
SELECT id,PushData
FROM UserCardPushlog
FORCE INDEX(ix_UserCardPushlog_status)
WHERE status = 0
? AND HANDleLock = 0
ORDER BY CreateTime
LIMIT 2000
另一種解決方案可以參考一下：where 條件后面加上CreateTime的過濾條件,這樣index就會(huì)變成range,時(shí)間也只需要15s左右.SQL如下：
EXPLAIN
SELECT id,PushData
FROM UserCardPushlog
WHERE status = 0
? AND HANDleLock = 0
AND CreateTime >='2017-01-01'
ORDER BY CreateTime
LIMIT 2000?

轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/5768.html