mysql中datetime欄位建立索引並比較大小詳解

2022-12-17 14:01:36

1、問題背景

最近測試庫查詢一個表的資料，需要用到唯一的一個日期型別欄位作為 where 的子查詢（查詢當天的資料），就正常寫了個這樣的 SQL，具體的表名我就不寫了：

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

其中欄位的值樣本如下：

我知道我寫的這條 SQL 即使在 create_time 這個列有索引的情況下也不會走索引，但是執行了以後就 wc 了：

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

這種問題要不優化都不敢說自己是農民工！

1）操作索引

首先我看了下這個表的索引：

show index from ${tablename};

一看，有一個列對應了 2 個索引欄位，也就是 create_time 沒有索引，我就幹掉其中一個重複的並在 create_time 上建了一個（表名我就不寫了）：

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

2）是否走索引判斷

有索引了關鍵是得讓它走索引啊，先驗證一下，我用執行計劃繼續執行了上面問題背景中的那個sql：

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

結果如下，一點都不意外，照樣全表掃描：

3）datetime使用索引查詢

既然不能操作索引列，我又想查詢某一天的資料，就只能用範圍了，範圍的就那個幾個，between…and 、> 、<
於是，我寫了下面查詢 SQL：

select * from ${tablename} where create_time between '2022-09-19 00:00:00' and '2022-09-19 23:59:59';

果然不辜負我的小操作，查詢結果如下：

查詢 1850 條資料平均在 0.5 s，這不用說，絕壁走了索引，一個查詢 20 條花費 8s，一個小 2 千記錄花費半秒，沒有可比性。

下面驗證這個查詢，同樣在 sql 前加上 explain 後執行：

沒毛病，索引型別 range，between…and 走的就是這種範圍索引，這種範圍型別的索引結構開銷是有點大的，大到一定程度就不走索引了，比如在性別欄位上建索引！

今天發現之前在使用日期索引時，通過explain發現一直不走日期索引，在網上查詢了下，發現使用過程中要注意以下情況：

1、在查詢資料條數約佔總條數五分之一以下時能夠使用到索引，但超過五分之一時，則使用全表掃描了。

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

例如：

我建立了複合索引car_date_index: date_time,car_plate_no.

當我是用查詢條件： WHERE car_plate_no=‘冀E8888’ and date_time<=‘2019-05-01 00:00:00’，如果想走car_date_index的索引，表中必須有car_plate_no欄位的索引才能走，暫時沒發現為啥會出現此問題，有知道可以分享下哦

到此這篇關於mysql中datetime欄位建立索引並比較大小的文章就介紹到這了,更多相關mysql datetime欄位建立索引內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！