SQL索引失效的情況有哪些

這篇文章主要介紹了SQL索引失效的情況有哪些的相關知識，內容詳細易懂，操作簡單快捷，具有一定借鑒價值，相信大家閱讀完這篇SQL索引失效的情況有哪些文章都會有所收獲，下面我們一起來看看吧。

數據庫調優的大致方向：

• 索引失效，沒有充分利用到索引——建立索引

• 關聯查詢太多join——sql優化

• 服務器調優及各個參數設置——my.cnf

• 數據過多——分庫分表

sql查詢優化技術有很多，大體分為物理查詢優化和邏輯查詢優化:

• 物理查詢優化：通過索引和表連接方式等技術進行優化

• 邏輯查詢優化：通過SQL等價變換提升查詢效率，就是換一種sql寫法

數據準備：

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只是當前窗口有效。

#隨機產生字符串
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 ;
#假如要刪除
#drop function rand_string;

#用于隨機產生多少到多少的編號
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 ;
#假如要刪除
#drop function rand_num;

#創建往stu表中插入數據的存儲過程
DELIMITER //
CREATE PROCEDURE insert_stu( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #設置手動提交事務
REPEAT #循環
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; #提交事務
END //
DELIMITER ;

#執行存儲過程，往class表添加隨機數據
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 ;

#執行存儲過程，往class表添加1萬條數據
CALL insert_class(10000);

#執行存儲過程，往stu表添加50萬條數據
CALL insert_stu(100000,500000);

SELECT COUNT(*) FROM class;
SELECT COUNT(*) FROM student;

############################### 刪除索引的存儲過程 ########################
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' ;
#每個游標必須使用不同的declare continue handler for not found set done=1來控制游標的結束
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=2 ;
#若沒有數據返回,程序繼續,并將變量done設為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 ;
# 執行存儲過程
CALL proc_drop_index("dbname","tablename");

索引失效案例

【1】. 全值匹配

# 【1】. 全值匹配
# student表,主鍵id,此時無索引,耗時大
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);	
# 此時第三條查詢語句默認使用最后一條索引,而不是前兩個

【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，
#但現在沒有這樣的索引，idx_age_classid_name的字段順序是先找age，所以不符合，所以此時不能用索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE classid = 4 AND student.age = 30 AND student.name = 'abcd';	
#idx_age_classid_name 聯合索引中所有字段均出現，可以使用該索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age = 30 AND student.name = 'abcd';
# 現在，刪除idx_age和idx_age_classid，發現用到idx_age_classid_name，而key_len=5,即只用到age字段，int(4)+null(1)
#因為索引完age后沒有classid了，不能再查找到name

【3】. 主鍵插入順序

在定義表時，讓主鍵auto_increment，否則，插入一條數據時可能會移動大量數據。

如，往 1 5 8 10 15 … 100 中插9，會放在8 10 中間，因為索引默認升序排列。那么10往后的數據都要挪動，頁不夠時又要放到下一頁，每插一條數據都這樣挪一次，開銷很大

我們自定義的主鍵列id 擁有AUTO_INCREMENT 屬性，在插入記錄時存儲引擎會自動為我們填入自增的主鍵值。這樣的主鍵占用空間小，順序寫入，減少頁分裂。

【4】. 計算、函數、類型轉換(自動或手動)導致索引失效

# 【4】. 計算、函數、類型轉換(自動或手動)導致索引失效
##### 例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):截取左側n個字符
# type = all 全表的訪問
# 該語句的執行過程：針對每一條數據，一個一個取出，先作用一遍函數，再拿函數結果與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 需要做運算，無法直接用索引找值

EXPLAIN SELECT SQL_NO_CACHE id,stuno,NAME FROM student WHERE stuno = 900000; 	# type = ref

【5】. 類型轉換導致索引失效

# 【5】. 類型轉換導致索引失效
# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME=123;	# 這里使用了隱式轉換
# 使用到索引
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';	# 這三個and先寫誰無所謂，優化器會調優
# key_len = 10, age=5,classId=5,name用不上。classId 是范圍，索引右側的name用不上

# 改寫索引：
CREATE INDEX idx_age_name_cid ON student(age,NAME,classId); 	#把需要排序的classid放到最后
# 此時在執行上面的語句，就使用了這個索引，key_len=73

創建的聯合索引中，必須把涉及到范圍的字段寫在最后。

【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 相當于等于某個值
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;	# 索引失效 相當于不等于

【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 索引失效

頁面搜索嚴禁左模糊或者全模糊，如果需要請走搜索引擎來解決。

【10】. OR 前后存在非索引的列，索引失效

# 【10】. OR 前后存在非索引的列，索引失效 

CALL proc_drop_index('atguigudb2','student');
SHOW INDEX FROM student;
# 創建一個age的索引
CREATE INDEX idx_age ON student(age);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;	# 未使用索引，索引+全表掃描->全表掃描
# 再加一個字段的單獨索引
CREATE INDEX idx_cid ON student(classid);
# 再執行上條語句，此時 type = index_merge ，key = idx_age,idx_cid。

【11】. 數據庫和表的字符集統一使用utf8mb4

統一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，統一字符集可以避免由于字符集轉換產生的亂碼。不同的字符集進行比較前需要進行轉換會造成索引失效。

關于“SQL索引失效的情況有哪些”這篇文章的內容就介紹到這里，感謝各位的閱讀！相信大家對“SQL索引失效的情況有哪些”知識都有一定的了解，大家如果還想學習更多知識，歡迎關注億速云行業資訊頻道。