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ù)量達到6千(占比20%左右),就不走索引了。
引起原因:
表的數(shù)據(jù)量太大,會讓數(shù)據(jù)庫中的優(yōu)化器進行處理。
優(yōu)化器是在表里面有多個索引的時候,決定使用哪個索引,查詢的量太大,導(dǎo)致優(yōu)化器認為走全表查詢時間效率更佳。
但是如果一定要用到區(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ǎ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)文章
mysql中復(fù)制表結(jié)構(gòu)的方法小結(jié)
這篇文章主要介紹了mysql中復(fù)制表結(jié)構(gòu)的方法,需要的朋友可以參考下2014-07-07Mysql 導(dǎo)入導(dǎo)出csv 中文亂碼問題的解決方法
這篇文章介紹了Mysql 導(dǎo)入導(dǎo)出csv 中文亂碼問題的解決方法,有需要的朋友可以參考一下2013-09-09MySQL創(chuàng)建數(shù)據(jù)表時設(shè)定引擎MyISAM/InnoDB操作
這篇文章主要介紹了MySQL創(chuàng)建數(shù)據(jù)表時設(shè)定引擎MyISAM/InnoDB操作,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2020-08-08解決創(chuàng)建主鍵報錯:Incorrect column specifier for
這篇文章主要介紹了解決創(chuàng)建主鍵報錯:Incorrect column specifier for column‘id‘問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-08-08MySQL中使用replace、regexp進行正則表達式替換的用法分析
這篇文章主要介紹了MySQL中使用replace、regexp進行正則表達式替換的用法,結(jié)合具體實例形式分析了replace、regexp正則替換的使用技巧與相關(guān)注意事項,需要的朋友可以參考下2017-03-03mysql如何按字段查詢重復(fù)的數(shù)據(jù)
這篇文章主要介紹了mysql如何按字段查詢重復(fù)的數(shù)據(jù)問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-05-05