SQL索引失效的11種情況詳析
更新時(shí)間：2023年03月09日 14:36:37 作者：MojitO_o
索引并不是時(shí)時(shí)都會(huì)生效的,遇到一些情況將導(dǎo)致索引失效,下面這篇文章主要給大家介紹了關(guān)于SQL索引失效的11種情況,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下
數(shù)據(jù)庫調(diào)優(yōu)的大致方向：
- 索引失效，沒有充分利用到索引——建立索引
- 關(guān)聯(lián)查詢太多join——sql優(yōu)化
- 服務(wù)器調(diào)優(yōu)及各個(gè)參數(shù)設(shè)置——my.cnf
- 數(shù)據(jù)過多——分庫分表
sql查詢優(yōu)化技術(shù)有很多，大體分為物理查詢優(yōu)化和邏輯查詢優(yōu)化:
- 物理查詢優(yōu)化：通過索引和表連接方式等技術(shù)進(jìn)行優(yōu)化
- 邏輯查詢優(yōu)化：通過SQL等價(jià)變換提升查詢效率，就是換一種sql寫法
數(shù)據(jù)準(zhǔn)備：
CREATE DATABASE atguigudb2;
USE atguigudb2;
############# class 表 #################
CREATE TABLE `class` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
############# student 表 #################
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
#################################
SET GLOBAL log_bin_trust_function_creators=1; # 不加global只是當(dāng)前窗口有效。
#隨機(jī)產(chǎn)生字符串
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;
#假如要?jiǎng)h除
#drop function rand_string;
#用于隨機(jī)產(chǎn)生多少到多少的編號
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;
#假如要?jiǎng)h除
#drop function rand_num;
#創(chuàng)建往stu表中插入數(shù)據(jù)的存儲(chǔ)過程
DELIMITER //
CREATE PROCEDURE insert_stu( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #設(shè)置手動(dòng)提交事務(wù)
REPEAT #循環(huán)
SET i = i + 1; #賦值
INSERT INTO student (stuno, NAME ,age ,classId ) VALUES
((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事務(wù)
END //
DELIMITER ;
#執(zhí)行存儲(chǔ)過程，往class表添加隨機(jī)數(shù)據(jù)
DELIMITER //
CREATE PROCEDURE `insert_class`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO class ( classname,address,monitor ) VALUES
(rand_string(8),rand_string(10),rand_num(1,100000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
#執(zhí)行存儲(chǔ)過程，往class表添加1萬條數(shù)據(jù)
CALL insert_class(10000);
#執(zhí)行存儲(chǔ)過程，往stu表添加50萬條數(shù)據(jù)
CALL insert_stu(100000,500000);
SELECT COUNT(*) FROM class;
SELECT COUNT(*) FROM student;
############################### 刪除索引的存儲(chǔ)過程 ########################
DELIMITER //
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT '';
DECLARE _cur CURSOR FOR SELECT index_name FROM
information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND
seq_in_index=1 AND index_name <>'PRIMARY' ;
#每個(gè)游標(biāo)必須使用不同的declare continue handler for not found set done=1來控制游標(biāo)的結(jié)束
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=2 ;
#若沒有數(shù)據(jù)返回,程序繼續(xù),并將變量done設(shè)為2
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>'' DO
SET @str = CONCAT("drop index " , _index , " on " , tablename );
PREPARE sql_str FROM @str ;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index='';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END //
DELIMITER ;
# 執(zhí)行存儲(chǔ)過程
CALL proc_drop_index("dbname","tablename");
索引失效案例
【1】. 全值匹配
# 【1】. 全值匹配 # student表,主鍵id,此時(shí)無索引,耗時(shí)大 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30; EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4; EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4 AND NAME = 'abcd'; # 注：SQL_NO_CACHE 不使用查詢緩存 # 建立索引 CREATE INDEX idx_age ON student(age); CREATE INDEX idx_age_classid ON student(age,classId); CREATE INDEX idx_age_classid_name ON student(age,classId,NAME); # 此時(shí)第三條查詢語句默認(rèn)使用最后一條索引,而不是前兩個(gè)
【2】. 最佳左前綴法則
# 【2】. 最佳左前綴法則 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age = 30 AND student.name = 'abcd'; # 查age&name，用age的索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid = 1 AND student.name = 'abcd'; # 查classid&name，classid在前，有索引的話先找classid相同的，再找name， #但現(xiàn)在沒有這樣的索引，idx_age_classid_name的字段順序是先找age，所以不符合，所以此時(shí)不能用索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid = 4 AND student.age = 30 AND student.name = 'abcd'; #idx_age_classid_name 聯(lián)合索引中所有字段均出現(xiàn)，可以使用該索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age = 30 AND student.name = 'abcd'; # 現(xiàn)在，刪除idx_age和idx_age_classid，發(fā)現(xiàn)用到idx_age_classid_name，而key_len=5,即只用到age字段，int(4)+null(1) #因?yàn)樗饕阛ge后沒有classid了，不能再查找到name
【3】. 主鍵插入順序
在定義表時(shí)，讓主鍵auto_increment，否則，插入一條數(shù)據(jù)時(shí)可能會(huì)移動(dòng)大量數(shù)據(jù)。
如，往 1 5 8 10 15 … 100 中插9，會(huì)放在8 10 中間，因?yàn)樗饕J(rèn)升序排列。那么10往后的數(shù)據(jù)都要挪動(dòng)，頁不夠時(shí)又要放到下一頁，每插一條數(shù)據(jù)都這樣挪一次，開銷很大
我們自定義的主鍵列id 擁有AUTO_INCREMENT 屬性，在插入記錄時(shí)存儲(chǔ)引擎會(huì)自動(dòng)為我們填入自增的主鍵值。這樣的主鍵占用空間小，順序?qū)懭?，減少頁分裂。
【4】. 計(jì)算、函數(shù)、類型轉(zhuǎn)換(自動(dòng)或手動(dòng))導(dǎo)致索引失效
# 【4】. 計(jì)算、函數(shù)、類型轉(zhuǎn)換(自動(dòng)或手動(dòng))導(dǎo)致索引失效 ##### 例1： EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%'; #更好，能夠使用上索引 # type=range 使用了索引中的排序 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc'; # left(text,num_chars):截取左側(cè)n個(gè)字符 # type = all 全表的訪問 # 該語句的執(zhí)行過程：針對每一條數(shù)據(jù)，一個(gè)一個(gè)取出，先作用一遍函數(shù)，再拿函數(shù)結(jié)果與abc對比，用不上b+樹 CREATE INDEX idx_name ON student(NAME); ##### 例2： CREATE INDEX idx_sno ON student(stuno); EXPLAIN SELECT SQL_NO_CACHE id,stuno,NAME FROM student WHERE stuno+1 = 900001; # type = all 需要做運(yùn)算，無法直接用索引找值 EXPLAIN SELECT SQL_NO_CACHE id,stuno,NAME FROM student WHERE stuno = 900000; # type = ref
【5】. 類型轉(zhuǎn)換導(dǎo)致索引失效
# 【5】. 類型轉(zhuǎn)換導(dǎo)致索引失效 # 未使用到索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME=123; # 這里使用了隱式轉(zhuǎn)換 # 使用到索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME='123'; # name本身就是字符串類型
【6】. 范圍條件右邊的列索引失效
# 【6】. 范圍條件右邊的列索引失效 （ > < >= <= between 等）
SHOW INDEX FROM student;
CALL proc_drop_index('atguigudb2','student');
CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age = 30 AND student.classId > 20 AND student.name = 'abc'; # 這三個(gè)and先寫誰無所謂，優(yōu)化器會(huì)調(diào)優(yōu)
# key_len = 10, age=5,classId=5,name用不上。classId 是范圍，索引右側(cè)的name用不上
# 改寫索引：
CREATE INDEX idx_age_name_cid ON student(age,NAME,classId); #把需要排序的classid放到最后
# 此時(shí)在執(zhí)行上面的語句，就使用了這個(gè)索引，key_len=73創(chuàng)建的聯(lián)合索引中，必須把涉及到范圍的字段寫在最后。
【7】. 不等于(!= 或者<>)索引失效
# 【7】. 不等于(!= 或者<>)索引失效 CREATE INDEX idx_name ON student(NAME); EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc'; # 索引失效 索引查的是等于
【8】. is null可以使用索引，is not null無法使用索引
# 【8】. is null可以使用索引，is not null無法使用索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL; # type=ref 相當(dāng)于等于某個(gè)值 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL; # 索引失效 相當(dāng)于不等于
【9】. like以通配符%開頭索引失效
# 【9】. like以通配符%開頭索引失效 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%'; # 可用索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab'; # type = all 索引失效
頁面搜索嚴(yán)禁左模糊或者全模糊，如果需要請走搜索引擎來解決。
【10】. OR 前后存在非索引的列，索引失效
# 【10】. OR 前后存在非索引的列，索引失效
CALL proc_drop_index('atguigudb2','student');
SHOW INDEX FROM student;
# 創(chuàng)建一個(gè)age的索引
CREATE INDEX idx_age ON student(age);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100; # 未使用索引，索引+全表掃描->全表掃描
# 再加一個(gè)字段的單獨(dú)索引
CREATE INDEX idx_cid ON student(classid);
# 再執(zhí)行上條語句，此時(shí) type = index_merge ，key = idx_age,idx_cid。【11】. 數(shù)據(jù)庫和表的字符集統(tǒng)一使用utf8mb4
統(tǒng)一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，統(tǒng)一字符集可以避免由于字符集轉(zhuǎn)換產(chǎn)生的亂碼。不同的字符集進(jìn)行比較前需要進(jìn)行轉(zhuǎn)換會(huì)造成索引失效。
總結(jié)
到此這篇關(guān)于SQL索引失效的11種情況詳析的文章就介紹到這了,更多相關(guān)SQL索引失效內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
一文總結(jié)使用MySQL時(shí)遇到null值的坑
這篇文章給大家總結(jié)了日常使用MySQL時(shí),容易遇到NULL值的坑有哪些,文章通過代碼示例給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下2024-01-01
SQL Server索引設(shè)計(jì)基礎(chǔ)知識詳解使用
為了使索引的使用效率更高，在創(chuàng)建索引時(shí)，必須考慮在哪些字段上創(chuàng)建索引和創(chuàng)建什么類型的索引。索引設(shè)計(jì)不合理或者缺少索引都會(huì)對數(shù)據(jù)庫和應(yīng)用程序的性能造成障礙。高效的索引對于獲得良好的性能非常重要。設(shè)計(jì)索引時(shí)，應(yīng)該考慮相應(yīng)準(zhǔn)則2023-04-04
本文通過圖文并茂的形式給大家介紹了mysql 5.5.27的安裝教程，非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友參考下吧2017-09-09
Mysql?InnoDB聚簇索引二級索引聯(lián)合索引特點(diǎn)
這篇文章主要為大家介紹了Mysql?InnoDB聚簇索引二級索引聯(lián)合索引特點(diǎn)詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2022-05-05
連接(join)查詢是將兩個(gè)查詢的結(jié)果以“橫向?qū)印钡姆绞胶喜⑵饋淼慕Y(jié)果,這篇文章主要給大家介紹了關(guān)于MYSQL連接查詢的相關(guān)資料,需要的朋友可以參考下2021-06-06
JMeter對MySQL數(shù)據(jù)庫進(jìn)行壓力測試的實(shí)現(xiàn)步驟
本文主要介紹了JMeter對MySQL數(shù)據(jù)庫進(jìn)行壓力測試的實(shí)現(xiàn)步驟，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-01-01
Mysql實(shí)現(xiàn)企業(yè)級日志管理、備份與恢復(fù)的實(shí)戰(zhàn)教程
下面小編就為大家分享一篇Mysql實(shí)現(xiàn)企業(yè)級日志管理、備份與恢復(fù)的實(shí)戰(zhàn)教程，具有很好的參考價(jià)值，希望對大家有所幫助。一起跟隨小編過來看看吧2017-12-12
SQL中實(shí)現(xiàn)SPLIT函數(shù)幾種方法總結(jié)(必看篇)
下面小編就為大家?guī)硪黄猄QL中實(shí)現(xiàn)SPLIT函數(shù)幾種方法總結(jié)(必看篇)。小編覺得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧2016-09-09
分組函數(shù)用作統(tǒng)計(jì)使用，又稱聚合函數(shù)，統(tǒng)計(jì)函數(shù)，組函數(shù)，這篇文章主要介紹了mysql分組函數(shù)分組查詢的操作代碼，本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2024-01-01
這篇文章主要為大家詳細(xì)介紹了CentOS6.8使用cmake安裝MySQL5.7.18的相關(guān)資料，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-04-04