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