前言
最近,開發人員需要定期的刪除表裡一定時間以前的數據,SQL如下:
<code>mysql >delete
from
testtable WHERE biz_date <='2017-08-21 00:00:00'
AND status =2
limit500
\G/<code>
前段時間在優化的時候,我們已經在相應的查詢條件上加上了索引,如下:
<code>KEY`idx_bizdate_st`
(`biz_date`
,`status`
)/<code>
但是實際執行的SQL依然非常慢,為什麼呢,我們來一步步分析驗證下。
分析
表上的字段既然都有索引,那麼按照之前的文章分析,是兩個字段都可以走上索引的。
既然能夠利用索引,表的總大小也就是200M左右,那麼為什麼形成了慢查呢?
我們查看執行計劃,去掉limit 後,發現他選擇了走全表掃描。
<code>mysql > descselect
* from testtable WHERE biz_date <='2017-08-21 00:00:00'
; + | id | select_type |table
|type
| possible_keys | key | key_len | ref | rows | Extra | + |1
| SIMPLE | testtable | ALL | idx_bizdate_st | NULL | NULL | NULL |980626
| Using where | +1
rowin
set (0.00
sec) mysql > descselect
* from testtable WHERE biz_date <='2017-08-21 00:00:00'
and
status
=2
; + | id | select_type |table
|type
| possible_keys | key | key_len | ref | rows | Extra | + |1
| SIMPLE | testtable | ALL | idx_bizdate_st | NULL | NULL | NULL |980632
| Using where | +1
rowin
set (0.00
sec) mysql > descselect
* from testtable WHERE biz_date <='2017-08-21 00:00:00'
and
status
=2
limit100
; + | id | select_type |table
|type
| possible_keys | key | key_len | ref | rows | Extra | + |1
| SIMPLE | testtable | range | idx_bizdate_st | idx_bizdate_st |6
| NULL |490319
| Using index condition | +1
rowin
set (0.00
sec) mysql >select
count(*) from testtable WHERE biz_date <='2017-08-21 00:00:00'
and
status
=2
; + | count(*) | + |0
| +1
rowin
set (0.34
sec) mysql >select
count(*) from testtable WHERE biz_date <='2017-08-21 00:00:00'
; + | count(*) | + |970183
| +1
rowin
set (0.33
sec) mysql >select
count(*) from testtable; + | count(*) | + |991421
| +1
rowin
set (0.19
sec) mysql >select
distinct biz_status from testtable; + | biz_status | + |1
| |2
| |4
| +/<code>
通過以上查詢,我們可以發現如下幾點問題:
- 通過 biz_date 預估出來的行數 和 biz_date + status=2 預估出來的行數幾乎一樣,為98w。
- 實際查詢表 biz_date + status=2 一條記錄都沒有。
- 整表數據量達到了99萬,MySQL發現通過索引掃描需要98w行(預估)
因此,MySQL通過統計信息預估的時候,發現需要掃描的索引行數幾乎佔到了整個表,放棄了使用索引,選擇了走全表掃描。
那是不是他的統計信息有問題呢?我們重新收集了下表統計信息,發現執行計劃的預估行數還是一樣,猜測只能根據組合索引的第一個字段進行預估(待確定)。
那我們試下直接強制讓他走索引呢?
<code>mysql >select
*from
testtable WHERE biz_date <='2017-08-21 00:00:00'
and status =2
;Empty
set
(0.79
sec) mysql >select
*from
testtable forceindex
(idx_bizdate_st
) WHERE biz_date <='2017-08-21 00:00:00'
and status =2
;Empty
set
(0.16
sec )/<code>
我們發現,強制指定索引後,查詢耗時和沒有強制索引比較,的確執行速度快了很多,因為沒有強制索引是全表掃描嘛!但是!依然非常慢!
那麼還有什麼辦法去優化這個本來應該很快的查詢呢?
大家應該都聽說過要選擇性好的字段放在組合索引的最前面?
選擇性好的索引在前面並不是對所有的場景都通用的,這個場景可以將status放前面,sql速度會更快。
那,能不能讓他不要掃描索引的那麼多範圍呢?之前的索引模型中也說過,MySQL是通過索引去確定一個掃描範圍,如果能夠定位到儘可能小的範圍,那是不是速度上會快很多呢?
並且業務邏輯上是定期刪除一定日期之前的數據。所以邏輯上來說,每次刪除都是隻刪除一天的數據,直接讓SQL掃描一天的範圍。那麼我們就可以改寫SQL啦!
<code>mysql > select * from testtable WHERE biz_date >='2017-08-20 00:00:00'
and
biz_date <='2017-08-21 00:00:00'
and
status =2
; Empty set (0
.00
sec) mysql > desc select * from testtable WHERE biz_date >='2017-08-20 00:00:00'
and
biz_date <='2017-08-21 00:00:00'
and
status =2
; +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+| id |
select_type| table |
type| possible_keys |
key| key_len |
ref| rows |
Extra| +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ |
1
| SIMPLE |
testtable| range |
idx_bizdate_st| idx_bizdate_st |
6
| NULL |
789
| Using index condition |
+----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+1
rowin
set (0
.00
sec) -- rows降低了很多,乖乖的走了索引 mysql > desc select * from testtable WHERE biz_date >='2017-08-20 00:00:00'
and
biz_date <='2017-08-21 00:00:00'
; +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+| id |
select_type| table |
type| possible_keys |
key| key_len |
ref| rows |
Extra| +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ |
1
| SIMPLE |
testtable| range |
idx_bizdate_st| idx_bizdate_st |
5
| NULL |
1318
| Using index condition |
+----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+1
rowin
set (0
.00
sec) -- 即使沒有status,也是肯定走索引啦/<code>
小結
這個問題,我原本打算用hint,強制讓他走索引,但是實際上強制走索引的執行時間並不能帶來滿意的效果。結合業務邏輯,來優化SQL,是最好的方式,也是終極法寶,一定要好好利用。不瞭解業務的DBA,不是一個好DBA...