索引失效问题

数据准备

学生表插50万条， 班级表插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`)
i) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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

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

创建函数

#随机产生字符串
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

随机产生班级编号

#用于随机产生多少到多少的编号
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;
1
2
3
4
5
6
7
8
9
10
11
12

创建存储过程

#创建往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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

创建往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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

调用存储过程

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

#执行存储过程，往stu表添加50万条数据
CALL insert_stu(100000,500000);
1
2
3
4
5

删除某表上的索引

因为后面的演示过程中，会创建/删除很多索引

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 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

正确的索引打开方式

全值匹配

意思是创建联合索引多个索引同时生效。
建立索引前，耗时 0.524s

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 20 AND classId = 3 AND NAME='abcd';
SELECT SQL_NO_CACHE * FROM student WHERE age = 20 AND classId = 3 AND NAME='abcd'; 
1
2

依次建立索引age->classid->name

CREATE INDEX idx_age ON student(age);
SELECT SQL_NO_CACHE * FROM student WHERE age = 20 AND classId = 3 AND NAME='abcd';

CREATE INDEX idx_age_cid ON student(age, classId);
SELECT SQL_NO_CACHE * FROM student WHERE age = 20 AND classId = 3 AND NAME='abcd';

CREATE INDEX idx_age_cid_name ON student(age, classId, NAME);
SELECT SQL_NO_CACHE * FROM student WHERE age = 20 AND classId = 3 AND NAME='abcd';
1
2
3
4
5
6
7
8

执行时间依次递减 0.171s , 0.041s , 0.010s
说明在联合索引中尽可能的多的使用到索引，可以提高效率

最佳左前缀法则

在MySQL建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
将idx_age和idx_age_cid两个索引删除，只剩idx_age_cid_name

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

没用到name索引，是因为idx_age_cid的顺序是age,classId,name,需要先建立classid的索引才能建立name的索引。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.name = 'abcd' ;
1

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

主键按顺序插入

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

数据页已经满了，再插进来需要进行页分裂成两个页面，把本页中的一些记录移动到新创建的页中。页面分裂和记录移位意味着： 性能损耗 ！所以如果我们想尽量 避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增 ，这样就不会发生这样的性能损耗了。 所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入 。

错误的索引打开方式

计算/函数/类型转换导致索引失效

计算导致类型失效

CREATE INDEX idex_stuno ON student(stuno);
1

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE stuno+1 = 10000;
1

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE stuno = 9999;
1

方式1进行计算，需要对全表的stuno进行+1操作，再和10000进行比较，无法使用索引。

使用函数导致索引失效

create index idx_name on student(name);
1

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'abc%';
1

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name, 3) = 'abc';
1

我们发现第二种方式的type是all，表示使用全表扫描，没有用到索引。而第一种方式使用到了索引。两个查询语句的差别，就是方式2使用了函数left，需要全表扫描出name的前3个字符，再和’abc’匹配，无法使用索引。而方式1直接在B+数上查找前3个字符是’abc’的，可以使用索引。

类型转换导致索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123;
1

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = '123';
1

方式1没使用到索引，而方式2使用到索引。这是因为方式1发生了类型转换，需要将全表的name字段都转换为int，在和123比较

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

除了idx_age_cid_name索引以为的索引都删除，避免干扰实验。

ALTER TABLE student DROP INDEX idx_name;
ALTER TABLE student DROP INDEX idx_stuno;

explain select sql_no_cache * from student 
where age = 30 and classId > 20 and name = 'abc';
1
2
3
4
5

因为classId使用范围查找，导致后面的索引失效。

因为范围条件导致的索引失效，可以考虑把确定的索引放在前面。
例如上面这个例子
:::tips
create index idx_age_name_cid on student(age, name, classId);
:::
这里name 放在了范围查找 classId前面 索引就能生效了。

哪些属于范围？

1. 大于等于，大于，小于等于，小于
2. between

应用开发中范围查询，例如： 金额查询，日期查询往往都是范围查询。创建联合索引时考虑放在后面。

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

create index idx_name on student(name);
1

explain select sql_no_cache * from student where name != 'abc';
1

并没有使用索引，因为索引只能查找确定的东西

is null 可以使用索引，is not null 不能使用索引

explain select sql_no_cache * from student where age is null; 
1

explain select sql_no_cache * from student where age is not null;
1

还是那句话，索引只能查找确定的东西。

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

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

explain select * from student where name like '%abc'
1

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

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

or前后存在非索引的列，导致索引失效

将idx_age_cid索引删除，避免影响到后续实验。

EXPLAIN SELECT * FROM student WHERE NAME = 'abc' OR age = 30;
1

name有索引，age没有索引，使用or连接后，name字段的索引失效。因为or是取并集，因此只有一个条件列进行索引是没有意义的，只要有条件列没有索引，就会进行全表扫描，因此有索引的条件列也会失效。

数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行** 转换 **会造成索引失效。

练习

设存在索引index(a,b,c)

一般性建议:

• 对于单列索引,l尽量选择针对当前query过滤性更好的索引
• 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
• 在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引。
• 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。