索引失效有的11种情况

程序员恰恰 2024-02-28

一、准备

步骤1、建表

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; 

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;
复制

步骤2：设置参数

命令开启：允许创建函数设置：

# 不加global只是当前窗口有效。
set global log_bin_trust_function_creators=1; 
复制

步骤3：创建函数

#随机产生字符串 
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;
复制

步骤4：创建存储过程

#创建往stu表中插入数据的存储过程

#创建往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 ; 

#假如要删除 
#drop PROCEDURE insert_stu;
复制

创建往class表中插入数据的存储过程

#执行存储过程，往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 ; 

#假如要删除 
#drop PROCEDURE insert_class;
复制

步骤5：调用存储过程

class

#执行存储过程，往class表添加1万条数据 
CALL insert_class(10000);
复制

student

#执行存储过程，往stu表添加50万条数据 
CALL insert_stu(100000,500000);
复制

步骤6：删除某表上的索引

创建存储过程

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");
复制

调用测试：

#清空class表的索引,保留主键索引
CALL proc_drop_index("student_test","class");
#清空student表的索引
CALL proc_drop_index("student_test","student");
#查询student表拥有的索引,保留主键索引
show index FROM student
#查询class表拥有的索引
show index FROM class
#清空class表的索引,保留主键索引
CALL proc_drop_index("student_test","class");
#清空student表的索引
CALL proc_drop_index("student_test","student");
复制

二、索引失效案例

MySQL中提高性能
的一个最有效的方式是对数据表设计合理的索引
。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。

使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。
如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。

大多数情况下都（默认）采用B+树
来构建索引。只是空间列类型的索引使用R-树
，并且MEMORY表还支持hash索引
。

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器?基于cost开销

(CostBaseOptimizer)，它不是基于规则(Rule-BasedOptimizer)，也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

2.1 全值匹配我最爱

SELECT SQL_NO_CACHE
复制

用法：用于指示数据库不使用缓存执行该查询。这样可以确保每次执行查询时都会从数据库中获取最新的数据，而不使用缓存中的旧数据。

系统中经常出现的sql语句如下:

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';
复制

查询结果:type是all，表示 MySQL 将对表中的每一行进行全表扫描，即不使用索引，而是逐行检查以满足查询条件。

建立索引前执行:(关注执行时间)

SELECT SQL_NO_CACHE * FROM student WHERE age=30 and classId=4 AND name = 'abcd ';
Empty set,1 warning (0.28 sec)
复制

建立索引:

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 ) ;

复制

建立索引后执行:

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 and classId=4 AND name = 'abcd ';
Empty set,1 warning (0.01 sec)
复制

可以看到，创建索引前的查询时间是0.28秒，创建索引后的查询时间是0.01秒，索引帮助我们极大的提高了查询效率。

2.2 最佳左前缀法则

在MySQL建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

例子1:没有用上索引

show index from student
#索引是age_classid_name

#没有用上索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.name = 'abcd';

复制

例子2:部分用上了索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student. name =' abcd ' ;

复制

结论:MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第1个字段时，多列(或联合)索引不会被使用。

2.3 主键插入顺序

对于一个使用InnoDB
存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和话录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在1~100之间:

如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图：

可这个数据页已经满了，再插进来咋办呢？我们需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的这个页中。

页面分裂和记录移位意味着什么？意味着：性能损耗！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。
所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入，

比如：person_info 表：

CREATE TABLE person_info( 
 id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
 name VARCHAR(100) NOT NULL, 
 birthday DATE NOT NULL, 
 phone_number CHAR(11) NOT NULL, 
 country varchar(100) NOT NULL, 
 PRIMARY KEY (id), 
 KEY idx_name_birthday_phone_number (name(10), 
 birthday, phone_number) 
);

复制

我们自定义的主键列 id 拥有 AUTO_INCREMENT 属性，在插入记录时存储引擎会自动为我们填入自增的
主键值。这样的主键占用空间小，顺序写入，减少页分裂。

2.4 计算、函数、类型转换(自动或手动)导致索引失效

#失效原因:进行+运算,type=All,全表扫描
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
#查询name的前三位为abc的内容进行条件筛选
#失效原因:进行函数运算SUBSTRING(),type=All,全表扫描
EXPLAIN SELECT id, stuno, name FROM student WHERE SUBSTRING(name, 1,3)='abc';
复制

如果对索引进行了表达式计算，索引就失效了。这是因为我们需要把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式，运行时间也会慢很多。

2.5 类型转换导致索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
复制

name=123发生类型转换，索引失效。

结论:设计实体类属性时，一定要与数据库字段类型相对应。否则，就会出现类型转换的情况。

2.6 范围条件右边的列索引失效


#查询索引
show index from student
#删除索引
ALTER TABLE student DROP INDEX idx_name; 
ALTER TABLE student DROP INDEX idx_age; 
ALTER TABLE student DROP INDEX idx_age_classid_name

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE 
student.age=30 AND student.classId>20 AND
student.name = 'abc' ;
复制

新建联合索引

#新建联合索引
create index idx_age_name_classid on student(age,name,classid)
#范围右侧失效,只有age,classId用上了索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age = 30 AND student.classId >20 AND student.name = 'abc' ;
复制

应用开发中范围查询，例如:金额查询，日期查询往往都是范围查询。应将查询条件放置where语句最后。

2.7 不等于(!= 或者<>)索引失效

#查询索引
show index from student
#删除索引
ALTER TABLE student DROP INDEX idx_age_name_classid;

#创建name索引

CREATE INDEX idx_name oN student(NAME);

#查看索引是否失效
#失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;
#失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student. name != 'abc';
复制

2.8 is null可以使用索引，is not null无法使用索引

#创建age索引

CREATE INDEX idx_age oN student(age);

#查看索引是否失效
#不失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
#lS NOT NULL:无法触发索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;
复制

索引失效

没失效

结论:最好在设计数据表的时候就将字段设置为NOT NULL 约束，比如你可以将INT类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串('')。拓展:同理，在查询中使用not like 也无法使用索引，导致全表扫描。

2.9 like以通配符%开头索引失效

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引就不会起作用。只有“%"不在第一个位置，索引才会起作用。

#查看索引是否失效

#没失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE 'ab% ';
#失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE '%ab%' ;
复制

结果：

拓展：Alibaba《Java开发手册》【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

2.10 OR 前后存在非索引的列，索引失效

在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。也就是说，OR前后的两个条件中的列都是索引时，查询中才使用索引。

因为OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。

查询语句使用OR关键字的情况:

此时 age有索引,classid没索引,name有索引,因为classid没索引所以失效

#没使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

#使用到索引 
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR name = 'Abel';
复制