MySQL索引命中與失效代碼實(shí)現(xiàn)
更新時(shí)間：2023年01月16日 11:05:22 作者：Blue?Protocol
這篇文章主要介紹了MySQL索引命中與失效代碼實(shí)現(xiàn)，文章內(nèi)容詳細(xì)，簡(jiǎn)單易懂,需要的朋友可以參考下
創(chuàng)建表
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(5) NOT NULL AUTO_INCREMENT COMMENT '用戶id', `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用戶名', `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用戶密碼', `create_essay` int(5) NOT NULL COMMENT '原創(chuàng)文章', `user_visited` int(10) NOT NULL COMMENT '被訪問量', `user_rank` int(5) NOT NULL COMMENT '用戶排名', `perms` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `nickname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用戶昵稱', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 116856 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1; // 創(chuàng)建組合索引 ALTER TABLE `user` ADD INDEX idx_username_password_user_rank (`username`,`password`,`user_rank`)
這里有一個(gè)組合索引的最左匹配原則：MySQL最左匹配原則
查看MySQL中索引是否命中可以使用explainh執(zhí)行優(yōu)化器來查看
MySQL執(zhí)行優(yōu)化器
執(zhí)行優(yōu)化器，顧名思義，優(yōu)化語句的，準(zhǔn)確來說是優(yōu)化查詢語句。其實(shí)就是在我們寫的select語句前加一個(gè)Explain關(guān)鍵字。
索引的命中與失效情況
第一種情況：針對(duì)聯(lián)合索引，是否遵循最左匹配原則；
建立一個(gè)組合索引
idx_username_password_user_rank(`username`,`password`,`user_rank`)
// 命中索引跟順序無關(guān) explain SELECT * from `user` where username = "liuxiangcheng" and password = "515239" and user_rank = 1 explain SELECT * from `user` where user_rank = 1 and username = "liuxiangcheng" and password = "515239" explain SELECT * from `user` where user_rank = 1 and password = "515239" and username = "liuxiangcheng"
結(jié)果：
第二種情況：去掉大哥，看看索引是否命中；
// 去掉大哥 explain SELECT * from `user` where password = "515239" and user_rank = 1
去掉大哥之后，索引失效，全表掃描。
第三種情況：在索引列上做了函數(shù)操作，會(huì)導(dǎo)致索引失效而導(dǎo)致全表掃描
我們先把那個(gè)聯(lián)合索引刪除掉，然后在username這一列上建立一個(gè)唯一索引：
刪除組合索引
drop index idx_username_password_user_rank on `user`
創(chuàng)建唯一索引
alter table `user` ADD UNIQUE key (`username`)
查看索引
explain SELECT * from `user` where username= 'user110819'
explain SELECT * from `user` where concat(username,'')= 'user110819'
第四種情況：模糊查詢前綴是以%開頭的，索引失效
explain SELECT * from `user` where username like '%user11081'
第五種情況：模糊查詢中后綴是以%，可以命中索引
explain SELECT * from `user` where username like 'user11081%'
第六種情況：使用is not null 會(huì)導(dǎo)致索引失效
explain SELECT * from `user` where username is not null
第六種情況：使用and時(shí)，其中有一個(gè)條件查詢帶有索引而另一個(gè)不帶索引，不會(huì)導(dǎo)致索引失效。而使用or時(shí)，如果條件查詢中其中一個(gè)不帶索引，導(dǎo)致索引失效，必須全部帶有索引。
and情況：
explain SELECT * from `user` where username = "liuxiangcheng" and password = "515239"
or情況：
explain SELECT * from `user` where username = "liuxiangcheng" or user_rank = 1
我們給user_rank加上索引
alter table `user` ADD index (`user_rank`)
再次查詢：
explain SELECT * from `user` where username = "liuxiangcheng" or user_rank = 1
第七種情況：使用不等于(!= 或者<>)的時(shí)候，會(huì)導(dǎo)致索引失效
explain SELECT * from `user` where user_rank != 1 or explain SELECT * from `user` where user_rank <> 1
第八種情況：使用范圍查詢之后索引失效
explain SELECT * from `user` where user_rank >(<,>=,<=) 1
第九種情況：隱式轉(zhuǎn)換可能會(huì)導(dǎo)致我們的索引失效
varchar類型，如果用int類型來查詢，索引失效
數(shù)據(jù)庫user表中我們的password是varchar類型
如果我們?cè)跅l件查詢中使用整形來替代，那么這個(gè)時(shí)候索引就會(huì)失效，where varchar = int 索引失效
// password is varchar type explain SELECT * from `user` where password = 515239 explain SELECT * from `user` where password = '515239' explain SELECT * from `user` where password = "515239"
如果是int類型，我們使用varchar來替代，索引命中
// user_rank is int type explain SELECT * from `user` where user_rank = "1" explain SELECT * from `user` where user_rank = '1' explain SELECT * from `user` where user_rank = 1
總結(jié)隱式轉(zhuǎn)換：
- 當(dāng)操作符左右兩邊的數(shù)據(jù)類型不一致時(shí)，會(huì)發(fā)生隱式轉(zhuǎn)換。
- 當(dāng) where 查詢操作符左邊為數(shù)值類型時(shí)發(fā)生了隱式轉(zhuǎn)換，但是索引會(huì)命中，對(duì)查詢效率影響不大，但還是不推薦這么做。
- 當(dāng) where 查詢操作符左邊為字符類型時(shí)發(fā)生了隱式轉(zhuǎn)換，這樣會(huì)導(dǎo)致索引失效，造成全表掃描。
- 字符串轉(zhuǎn)換為數(shù)值類型時(shí)，非數(shù)字開頭的字符串會(huì)轉(zhuǎn)化為0，以數(shù)字開頭的字符串會(huì)截取從第一個(gè)字符到第一個(gè)非數(shù)字內(nèi)容為止的值為轉(zhuǎn)化結(jié)果。
總結(jié)
MySQL中索引失效的情況
1、組合索引中不遵循最左匹配原則，帶頭大哥不在，導(dǎo)致索引失效，全表掃描。
2、在索引列上做了函數(shù)操作，導(dǎo)致索引失效，全表掃描。
3、模糊查詢前綴是以%開頭的，導(dǎo)致索引失效，全表掃描。
4、使用is not null 會(huì)導(dǎo)致索引失效。
5、使用or時(shí)，如果條件查詢中其中一個(gè)不帶索引，導(dǎo)致索引失效，全表掃描。
6、使用不等于(!= 或者<>)的時(shí)候，會(huì)導(dǎo)致索引失效。
7、使用范圍查詢（>、<、>=、<=）之后索引失效。
8、隱式轉(zhuǎn)換可能會(huì)導(dǎo)致我們的索引失效。
查看MySQL中索引是否命中可以使用explainh執(zhí)行優(yōu)化器來查看。
到此這篇關(guān)于MySQL索引命中與失效代碼實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL索引命中與失效內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL使用UNIQUE實(shí)現(xiàn)數(shù)據(jù)不重復(fù)插入
當(dāng)unique列在一個(gè)UNIQUE鍵上插入包含重復(fù)值的記錄時(shí)，我們可以控制MySQL如何處理這種情況：使用IGNORE關(guān)鍵字或者ON DUPLICATE KEY UPDATE子句跳過INSERT、中斷操作或者更新舊記錄為新值。2017-05-05
這篇文章主要介紹了幾個(gè)比較重要的MySQL變量，具有一定參考價(jià)值，需要的朋友可以了解下。2017-10-10
MySQL服務(wù)器 IO 100%的分析與優(yōu)化方案
這篇文章主要給大家介紹了關(guān)于MySQL服務(wù)器 IO 100%的相關(guān)資料，文中通過示例代碼介紹的介紹非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用mysql具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-10-10
本篇文章主要介紹了mysql 獲取當(dāng)前日期及格式化，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下。2016-12-12
這篇文章主要介紹了MySQL的FreeList機(jī)制的相關(guān)資料，幫助大家更好的理解和使用MySQL 數(shù)據(jù)庫，感興趣的朋友可以了解下2020-11-11
MySQL提示The InnoDB feature is disabled需要開啟InnoDB的解決方法
這篇文章主要介紹了MySQL提示The InnoDB feature is disabled需要開啟InnoDB的解決方法,簡(jiǎn)單分析了MySQL數(shù)據(jù)庫開啟InnoDB引擎的實(shí)現(xiàn)技巧,需要的朋友可以參考下2016-01-01
MySQL帶OR關(guān)鍵字的多條件查詢，與AND關(guān)鍵字不同，OR關(guān)鍵字，只要記錄滿足任意一個(gè)條件，就會(huì)被查詢出來。即AND的優(yōu)先級(jí)高于OR2017-07-07
在本篇文章里小編給大家整理的是一篇關(guān)于MySQL的DML語言操作實(shí)例內(nèi)容，有興趣的朋友們可以學(xué)習(xí)下。2020-02-02
這篇文章主要介紹了MySQL主從復(fù)制原理解析與最佳實(shí)踐過程，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-05-05
這篇文章主要介紹了一千行的MySQL學(xué)習(xí)筆記匯總,實(shí)例總結(jié)了MySQL學(xué)習(xí)中遇到的各類常見問題與實(shí)用技巧,需要的朋友可以參考下2014-10-10