Mysql中between...and引起的索引失效問題及解決
發(fā)生場景
在查詢學(xué)生表的時候，需要支持根據(jù)創(chuàng)建時間來篩選出某段時間內(nèi)入學(xué)的學(xué)生總數(shù)，因此在創(chuàng)建時間上加了索引，但是最終發(fā)現(xiàn)還是會走全量查詢。
實驗過程
CREATE TABLE `t_user` ( `id` bigint(11) unsigned NOT NULL COMMENT '學(xué)生id', `name` varchar(24) NOT NULL COMMENT '學(xué)生名稱', `createTime` dat NOT NULL COMMENT '創(chuàng)建時間', PRIMARY KEY (`id`), KEY `index_updateTime` (`createTime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
select count(1) from t_user where createTime between '2022-08-30' and '2022-08-31';
使用insert語句插入3萬多條數(shù)據(jù)后，其中30號至31號之前有9千多條數(shù)據(jù)，發(fā)現(xiàn)查詢的時候并沒有走到索引。
結(jié)果如下：
縮小查詢區(qū)間，只查31號的（4千多條數(shù)據(jù)），卻發(fā)現(xiàn)可以走到索引了，
結(jié)果如下：
select count(1) from t_user where createTime between '2022-08-31' and '2022-08-31';
再找一個數(shù)據(jù)量為5千的區(qū)間來試一下，也是會走到索引的，
結(jié)果如下：
select count(1) from t_user where createTime between '2022-08-23' and '2022-08-25';
沒走到索引是因為between…and引起的嗎？如果改為>=和<=呢。
結(jié)果如下：
select count(1) from t_user where createTime >= '2022-08-30' and createTime <= '2022-08-31';
結(jié)論
經(jīng)過實驗發(fā)現(xiàn)，當(dāng)查詢的數(shù)據(jù)量達(dá)到6千（占比20%左右），就不走索引了。
引起原因：
表的數(shù)據(jù)量太大，會讓數(shù)據(jù)庫中的優(yōu)化器進(jìn)行處理。
優(yōu)化器是在表里面有多個索引的時候，決定使用哪個索引，查詢的量太大，導(dǎo)致優(yōu)化器認(rèn)為走全表查詢時間效率更佳。
但是如果一定要用到區(qū)間查詢，這個問題該如何解決呢？
方案一
select count(1) from t_user where createTime between '2022-08-30' and '2022-08-31' limit 1;
方案二
select count(1) from t_user FORCE INDEX(index_updateTime) where createTime between '2022-08-30' and '2022-08-31';
思考：
當(dāng)查詢數(shù)據(jù)量達(dá)到一定量的時候會導(dǎo)致between…and索引失效，那分頁查詢的時候呢？
select id,name, createTime from t_user where createTime between '2022-08-30' and '2022-08-31' limit 1,500;
可見，查詢第一頁的時候索引有效，但是隨著頁碼越來越大的時候，索引卻失效了，
select id,name, createTime from t_user where createTime between '2022-08-30' and '2022-08-31' limit 100000,500;
因此，遇到這種因查詢數(shù)據(jù)量過大而導(dǎo)致的索引失效的問題，需要對其功能做相應(yīng)限制處理。
總結(jié)
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關(guān)文章
利用Shell腳本實現(xiàn)遠(yuǎn)程MySQL自動查詢
本篇文章是對利用Shell腳本實現(xiàn)遠(yuǎn)程MySQL自動查詢的方法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
MySQL表字段設(shè)置默認(rèn)值(圖文教程及注意細(xì)節(jié))
默認(rèn)值的設(shè)置很重要，比如在插入的時候一些字段是可以省略的，這會帶來很多的方便，接下來將要介紹MySQL表字段設(shè)置默認(rèn)值感興趣的你可以千萬不要走開啊，希望本文對你有所幫助2013-01-01
這篇文章主要介紹了MySQL中的max()函數(shù)使用教程,是學(xué)習(xí)MySQL入門的基礎(chǔ)知識,需要的朋友可以參考下2015-05-05
這篇文章主要給大家介紹了關(guān)于MySQL中一些鮮為人知的排序方式，文中介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-03-03
這篇文章主要為大家詳細(xì)介紹了navicat連接mysql報錯1251的解決方法，文中解決方法介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-07-07
在MySQL中binlog（二進(jìn)制日志）是一種記錄數(shù)據(jù)庫操作的日志文件,它記錄了數(shù)據(jù)庫更改的所有操作,這篇文章主要介紹了MySQL中Binlog文件占用空間比較大該如何清理的相關(guān)資料,需要的朋友可以參考下2025-09-09
本文給大家介紹Mac 安裝和卸載 Mysql5.7.11 的方法，本文介紹的非常詳細(xì)，具有參考借鑒價值，感興趣的朋友一起學(xué)習(xí)吧2016-03-03
MySQL處理和消除重復(fù)數(shù)據(jù)的多種方法
在數(shù)據(jù)庫管理中,數(shù)據(jù)重復(fù)是一個常見的問題,重復(fù)數(shù)據(jù)不僅浪費存儲空間,還可能導(dǎo)致數(shù)據(jù)分析的偏差和錯誤,ySQL作為一種流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng),提供了多種方法來處理和消除重復(fù)數(shù)據(jù),本文將詳細(xì)介紹MySQL處理重復(fù)數(shù)據(jù)的方法,需要的朋友可以參考下2025-12-12
MySQL數(shù)據(jù)庫之聯(lián)合查詢?union
這篇文章主要介紹了MySQL數(shù)據(jù)庫之聯(lián)合查詢?union，聯(lián)合查詢就是將多個查詢結(jié)果的結(jié)果集合并到一起，字段數(shù)不變，多個查詢結(jié)果的記錄數(shù)合并，下文詳細(xì)介紹需要的小伙伴可以參考一下2022-06-06
這篇文章主要介紹了MySQL explain獲取查詢指令信息原理及實例,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2020-05-05