快捷導(dǎo)航

mysql中datetime字段建立索引并比較大小詳解

更新時(shí)間：2022年12月15日 08:54:08 作者：太上碼農(nóng)

MySQL數(shù)據(jù)庫(kù)常用的時(shí)間類型有timestamp和datetime,兩者主要區(qū)別是占用存儲(chǔ)空間長(zhǎng)度不一致、可存儲(chǔ)的時(shí)間也有限制,下面這篇文章主要給大家介紹了關(guān)于mysql中datetime字段建立索引并比較大小的相關(guān)資料,需要的朋友可以參考下

1、問題背景

最近測(cè)試庫(kù)查詢一個(gè)表的數(shù)據(jù)，需要用到唯一的一個(gè)日期類型字段作為 where 的子查詢（查詢當(dāng)天的數(shù)據(jù)），就正常寫了個(gè)這樣的 SQL，具體的表名我就不寫了：

# create_time 是 datetime 類型
select * from ${tablename} where date(create_time)='20220919' limit 20;

其中字段的值樣本如下：

我知道我寫的這條 SQL 即使在 create_time 這個(gè)列有索引的情況下也不會(huì)走索引，但是執(zhí)行了以后就 wc 了：

NM！20條記錄你查詢需要 8 s！

這種問題要不優(yōu)化都不敢說自己是農(nóng)民工！

2、優(yōu)化過程

1）操作索引

首先我看了下這個(gè)表的索引：

show index from ${tablename};

一看，有一個(gè)列對(duì)應(yīng)了 2 個(gè)索引字段，也就是 create_time 沒有索引，我就干掉其中一個(gè)重復(fù)的并在 create_time 上建了一個(gè)（表名我就不寫了）：

#刪除索引
drop index index_create_time on ${tablename};
#添加索引
create index index_create_time on {tablename}(create_time);

2）是否走索引判斷

有索引了關(guān)鍵是得讓它走索引啊，先驗(yàn)證一下，我用執(zhí)行計(jì)劃繼續(xù)執(zhí)行了上面問題背景中的那個(gè)sql：

explain select * from ${tablename} where date(create_time)='20220919' limit 20;

結(jié)果如下，一點(diǎn)都不意外，照樣全表掃描：

3）datetime使用索引查詢

既然不能操作索引列，我又想查詢某一天的數(shù)據(jù)，就只能用范圍了，范圍的就那個(gè)幾個(gè)，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，這不用說，絕壁走了索引，一個(gè)查詢 20 條花費(fèi) 8s，一個(gè)小 2 千記錄花費(fèi)半秒，沒有可比性。

下面驗(yàn)證這個(gè)查詢，同樣在 sql 前加上 explain 后執(zhí)行：

沒毛病，索引類型 range，between…and 走的就是這種范圍索引，這種范圍類型的索引結(jié)構(gòu)開銷是有點(diǎn)大的，大到一定程度就不走索引了，比如在性別字段上建索引！

補(bǔ)充：Mysql 時(shí)間Datetime 索引不生效問題

今天發(fā)現(xiàn)之前在使用日期索引時(shí)，通過explain發(fā)現(xiàn)一直不走日期索引，在網(wǎng)上查詢了下，發(fā)現(xiàn)使用過程中要注意以下情況：

1、在查詢數(shù)據(jù)條數(shù)約占總條數(shù)五分之一以下時(shí)能夠使用到索引，但超過五分之一時(shí)，則使用全表掃描了。

2、查詢條件有日期索引和其他條件的話，只有所有條件都有索引的情況下，才會(huì)走日期索引，

例如：

我建立了復(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字段的索引才能走，暫時(shí)沒發(fā)現(xiàn)為啥會(huì)出現(xiàn)此問題，有知道可以分享下哦