前言
最近，開(kāi)發(fā)人員需要定期的刪除表里一定時(shí)間以前的數據，SQL如下：
mysql > delete from testtable WHERE biz_date <= '2017-08-21 00:00:00' AND status = 2 limit 500\G前段時(shí)間在優(yōu)化的時(shí)候，我們已經(jīng)在相應的查詢(xún)條件上加上了索引，如下：
KEY `idx_bizdate_st` (`biz_date`,`status`)表上的字段既然都有索引，那么按照之前的文章分析，是兩個(gè)字段都可以走上索引的。
既然能夠利用索引，表的總大小也就是200M左右，那么為什么形成了慢查呢？
mysql > desc select * 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 row in set (0.00 sec)
-- 只查詢(xún)biz_date-- 關(guān)鍵點(diǎn)：rows:980626;type:ALL
mysql > desc select * 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 row in set (0.00 sec)
-- 查詢(xún)biz_date status -- 關(guān)鍵點(diǎn)：rows:980632;type:ALL
mysql > desc select * from testtable WHERE biz_date <= '2017-08-21 00:00:00' and status = 2 limit 100; ---- ------------- ----------- ------- ---------------- ---------------- --------- ------ -------- ----------------------- | 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 row in set (0.00 sec)
-- 查詢(xún)biz_date status limit -- 關(guān)鍵點(diǎn)：rows:490319;
mysql > select count(*) from testtable WHERE biz_date <= '2017-08-21 00:00:00' and status = 2; ---------- | count(*) | ---------- | 0 | ---------- 1 row in set (0.34 sec)
mysql > select count(*) from testtable WHERE biz_date <= '2017-08-21 00:00:00'; ---------- | count(*) | ---------- | 970183 | ---------- 1 row in set (0.33 sec)
mysql > select count(*) from testtable; ---------- | count(*) | ---------- | 991421 | ---------- 1 row in set (0.19 sec)
mysql > select distinct biz_status from testtable; ------------ | biz_status | ------------ | 1 || 2 || 4 | ------------ 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 force index(idx_bizdate_st) WHERE biz_date <= '2017-08-21 00:00:00' and status = 2;Empty set (0.16 sec)
我們發(fā)現，強制指定索引后，查詢(xún)耗時(shí)和沒(méi)有強制索引比較，的確執行速度快了很多，因為沒(méi)有強制索引是全表掃描嘛！但是！依然非常慢！
并且業(yè)務(wù)邏輯上是定期刪除一定日期之前的數據。所以邏輯上來(lái)說(shuō)，每次刪除都是只刪除一天的數據，直接讓SQL掃描一天的范圍。那么我們就可以改寫(xiě)SQL啦！
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 row in 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 row in set (0.00 sec)
-- 即使沒(méi)有status，也是肯定走索引啦這個(gè)問(wèn)題，我原本打算用hint，強制讓他走索引，但是實(shí)際上強制走索引的執行時(shí)間并不能帶來(lái)滿(mǎn)意的效果。結合業(yè)務(wù)邏輯，來(lái)優(yōu)化SQL，是最好的方式，也是終極法寶，一定要好好利用。不了解業(yè)務(wù)的DBA，不是一個(gè)好DBA...
聯(lián)系客服
