mysql中datetime字段建立索引并比較大小詳解
1、問題背景
最近測試庫查詢一個表的數(shù)據(jù),需要用到唯一的一個日期類型字段作為 where 的子查詢(查詢當(dāng)天的數(shù)據(jù)),就正常寫了個這樣的 SQL,具體的表名我就不寫了:
# create_time 是 datetime 類型 select * from ${tablename} where date(create_time)='20220919' limit 20;
其中字段的值樣本如下:
我知道我寫的這條 SQL 即使在 create_time 這個列有索引的情況下也不會走索引,但是執(zhí)行了以后就 wc 了:
NM!20條記錄你查詢需要 8 s!
這種問題要不優(yōu)化都不敢說自己是農(nóng)民工!
2、優(yōu)化過程
1) 操作索引
首先我看了下這個表的索引:
show index from ${tablename};
一看,有一個列對應(yīng)了 2 個索引字段,也就是 create_time 沒有索引,我就干掉其中一個重復(fù)的并在 create_time 上建了一個(表名我就不寫了):
#刪除索引 drop index index_create_time on ${tablename}; #添加索引 create index index_create_time on {tablename}(create_time);
2)是否走索引判斷
有索引了關(guān)鍵是得讓它走索引啊,先驗證一下,我用執(zhí)行計劃繼續(xù)執(zhí)行了上面問題背景中的那個sql:
explain select * from ${tablename} where date(create_time)='20220919' limit 20;
結(jié)果如下,一點(diǎn)都不意外,照樣全表掃描:
3)datetime使用索引查詢
既然不能操作索引列,我又想查詢某一天的數(shù)據(jù),就只能用范圍了,范圍的就那個幾個,between…and 、> 、<
于是,我寫了下面查詢 SQL:
select * from ${tablename} where create_time between '2022-09-19 00:00:00' and '2022-09-19 23:59:59';
果然不辜負(fù)我的小操作,查詢結(jié)果如下:
查詢 1850 條數(shù)據(jù)平均在 0.5 s,這不用說,絕壁走了索引,一個查詢 20 條花費(fèi) 8s,一個小 2 千記錄花費(fèi)半秒,沒有可比性。
下面驗證這個查詢,同樣在 sql 前加上 explain 后執(zhí)行:
沒毛病,索引類型 range,between…and 走的就是這種范圍索引,這種范圍類型的索引結(jié)構(gòu)開銷是有點(diǎn)大的,大到一定程度就不走索引了,比如在性別字段上建索引!
補(bǔ)充:Mysql 時間Datetime 索引不生效問題
今天發(fā)現(xiàn)之前在使用日期索引時,通過explain發(fā)現(xiàn)一直不走日期索引,在網(wǎng)上查詢了下,發(fā)現(xiàn)使用過程中要注意以下情況:
1、在查詢數(shù)據(jù)條數(shù)約占總條數(shù)五分之一以下時能夠使用到索引,但超過五分之一時,則使用全表掃描了。
2、查詢條件有日期索引和其他條件的話,只有所有條件都有索引的情況下,才會走日期索引,
例如:
我建立了復(fù)合索引car_date_index: date_time,car_plate_no.
當(dāng)我是用查詢條件: WHERE car_plate_no=‘冀E8888’ and date_time<=‘2019-05-01 00:00:00’,如果想走car_date_index的索引,表中必須有car_plate_no字段的索引才能走,暫時沒發(fā)現(xiàn)為啥會出現(xiàn)此問題,有知道可以分享下哦
總結(jié)
到此這篇關(guān)于mysql中datetime字段建立索引并比較大小的文章就介紹到這了,更多相關(guān)mysql datetime字段建立索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Mysql中關(guān)于Incorrect string value的解決方案
在對mysql數(shù)據(jù)庫中插入數(shù)據(jù)的時候,直接插入中文是沒有問題的!但是用預(yù)編譯語句時,用流對數(shù)據(jù)進(jìn)行處理總報incorrect string value這個異常。本篇文章教給你解決方法2021-09-09mysql使用insert into select插入查出的數(shù)據(jù)
這篇文章主要介紹了mysql使用insert into select插入查出的數(shù)據(jù)方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-12-12MySQL單表多關(guān)鍵字模糊查詢的實現(xiàn)方法
MySQL單表多關(guān)鍵字模糊查詢實現(xiàn)方法是很多人都非常想知道的一個問題,那么,究竟采用什么方法,才能實現(xiàn)MySQL單表多關(guān)鍵字模糊查詢呢?2011-03-03MySQL運(yùn)算符!=和<>及=和<=>的使用區(qū)別
本文主要介紹了MySQL運(yùn)算符!=和<>及=和<=>的使用區(qū)別,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-05-05MySQL數(shù)據(jù)庫中varchar類型的數(shù)字比較大小的方法
varchar類型的數(shù)據(jù)是不能直接比較大小的,那么MySQL數(shù)據(jù)庫中varchar類型如何進(jìn)行數(shù)字比較大小的,本文就詳細(xì)的介紹一下2021-11-11