建站
咨詢
售后
建站咨詢
新聞中心
一、前言
10年積累的網(wǎng)站制作、成都做網(wǎng)站經(jīng)驗(yàn)，可以快速應(yīng)對(duì)客戶對(duì)網(wǎng)站的新想法和需求。提供各種問題對(duì)應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識(shí)你，你也不認(rèn)識(shí)我。但先建設(shè)網(wǎng)站后付款的網(wǎng)站建設(shè)流程，更有瀘水免費(fèi)網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。
開發(fā)需要定期的刪除表里一定時(shí)間以前的數(shù)據(jù)，SQL 如下
- mysql > delete from testtable WHERE biz_date <= '2017-08-21 00:00:00' AND status = 2 limit 500\G
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)在相應(yīng)的查詢條件上加上了索引
- KEY `idx_bizdate_st` (`biz_date`,`status`)
但是實(shí)際執(zhí)行的 SQL 依然非常慢，為什么呢，我們來一步步分析驗(yàn)證下
二、分析
表上的字段既然都有索引，那么按照之前的文章分析，是兩個(gè)字段都可以走上索引的。如果有疑問，請參考文章 10 分鐘讓你明白 MySQL 是如何利用索引的
既然能夠利用索引，表的總大小也就是 200M 左右，那么為什么形成了慢查呢?
我們查看執(zhí)行計(jì)劃，去掉 limit 后，發(fā)現(xiàn)他選擇了走全表掃描。
- 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)
- -- 只查詢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)
- -- 查詢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)
- -- 查詢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 whwtestbuffer;
- +------------+
- | biz_status | +-
- -----------+
- | 1 |
- | 2 |
- | 4 |
- +------------+
通過以上查詢，我們可以發(fā)現(xiàn)如下幾點(diǎn)問題：
通過 biz_date 預(yù)估出來的行數(shù) 和 biz_date + status=2 預(yù)估出來的行數(shù)幾乎一樣，為 98w。
實(shí)際查詢表 biz_date + status=2 一條記錄都沒有。
整表數(shù)據(jù)量達(dá)到了99萬，MySQL 發(fā)現(xiàn)通過索引掃描需要98w行(預(yù)估)
因此，MySQL 通過統(tǒng)計(jì)信息預(yù)估的時(shí)候，發(fā)現(xiàn)需要掃描的索引行數(shù)幾乎占到了整個(gè)表，放棄了使用索引，選擇了走全表掃描。
那是不是他的統(tǒng)計(jì)信息有問題呢?我們重新收集了下表統(tǒng)計(jì)信息，發(fā)現(xiàn)執(zhí)行計(jì)劃的預(yù)估行數(shù)還是一樣，猜測只能根據(jù)組合索引的***個(gè)字段進(jìn)行預(yù)估(待確定)
那我們試下直接強(qiáng)制讓他走索引呢?
- 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ā)現(xiàn)，強(qiáng)制指定索引后，查詢耗時(shí)和沒有強(qiáng)制索引比較，的確執(zhí)行速度快了很多，因?yàn)闆]有強(qiáng)制索引是全表掃描嘛!但是!依然非常慢!
那么還有什么辦法去優(yōu)化這個(gè)本來應(yīng)該很快的查詢呢?
大家應(yīng)該都聽說過要選擇性好的字段放在組合索引的最前面?
是的，相對(duì)于 status 字段，biz_date 的選擇性更加不錯(cuò)，那組合索引本身已經(jīng)沒有好調(diào)整了
那，能不能讓他不要掃描索引的那么多范圍呢?之前的索引模型中也說過，MySQL 是通過索引去確定一個(gè)掃描范圍，如果能夠定位到盡可能小的范圍，那是不是速度上會(huì)快很多呢?
并且業(yè)務(wù)邏輯上是定期刪除一定日期之前的數(shù)據(jù)。所以邏輯上來說，每次刪除都是只刪除一天的數(shù)據(jù)，直接讓 SQL 掃描一天的范圍。那么我們就可以改寫 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 = ;
- 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)
- -- 即使沒有status，也是肯定走索引啦
三、小結(jié)
這個(gè)問題，我原本打算用 hint，強(qiáng)制讓他走索引，但是實(shí)際上強(qiáng)制走索引的執(zhí)行時(shí)間并不能帶來滿意的效果。結(jié)合業(yè)務(wù)邏輯，來優(yōu)化 SQL，是***的方式，也是***法寶，一定要好好利用。不了解業(yè)務(wù)的 DBA，不是一個(gè)好 DBA... 繼續(xù)去補(bǔ)業(yè)務(wù)知識(shí)去了。
分享標(biāo)題：一個(gè)不可思議的MySQL慢查分析與解決
轉(zhuǎn)載來于：http://www.dlmjj.cn/article/cdpsddo.html
