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)BEGINDECLARE chars_str VARCHAR(100) DEFAULT'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';DECLARE return_str VARCHAR(255) DEFAULT '';DECLARE i INT DEFAULT 0;WHILE i < n DOSET 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)BEGINDECLARE 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 )BEGINDECLARE 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_numEND REPEAT;COMMIT; #提交事务END //DELIMITER ;#执行存储过程，往class表添加随机数据DELIMITER //CREATE PROCEDURE `insert_class`( max_num INT )BEGINDECLARE i INT DEFAULT 0;SET autocommit = 0;REPEATSET i = i + 1;INSERT INTO class ( classname,address,monitor ) VALUES(rand_string(8),rand_string(10),rand_num(1,100000));UNTIL i = max_numEND 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))BEGINDECLARE done INT DEFAULT 0;DECLARE ct INT DEFAULT 0;DECLARE _index VARCHAR(200) DEFAULT '';DECLARE _cur CURSOR FOR SELECT index_name FROMinfORMation_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename ANDseq_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设为2OPEN _cur;FETCH _cur INTO _index;WHILE _index<>'' DOSET @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 studentWHERE 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 studentWHERE 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索引失效的情况有哪些”知识都有一定的了解，大家如果还想学习更多知识，欢迎关注编程网精选频道。