一、索引失效（应该避免）

一、索引失效（应该避免）

CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
age INT NOT NULL DEFAULT 0 COMMENT '年龄',
pos VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
)CHARSET utf8 COMMENT '员工记录表';
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW());
SELECT * FROM staffs; h
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos); # 新建复合索引
show index from staffs

(2)最佳左前缀法则

explain select * from staffs WHERE NAME='July'

explain select * from staffs WHERE NAME='July' and age = 25 and pos='dev'

explain select * from staffs WHERE age = 25 and pos='dev'

全表扫描并且没有用到索引

建立的索引NameAgePos，如果前面没有了Name，会发现没有用到索引。

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

explain select * from staffs WHERE NAME='July'  and pos='dev'

这个跳过了中间列了，key_len 变小了，rows 应该为2

(1)全值匹配我最爱

（3）不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July'

EXPLAIN SELECT * FROM staffs WHERE LEFT(NAME,4) = 'July'

(4)存储引擎不能使用索引中范围右边的列

https://blog.csdn.net/m0_45406092/article/details/112196476

（5）尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *

(6)mysql 在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

EXPLAIN SELECT * FROM staffs WHERE NAME != 'July'

（7）is null , is not null 无法使用索引

EXPLAIN SELECT * FROM staffs WHERE NAME is null

EXPLAIN SELECT * FROM staffs WHERE NAME is not null

(8) like 以通配符开头（’%abc..’）mysql索引失效会变成全表扫描的操作

E--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------XPLAIN select * from staffs WHERE name like '%July%'

EXPLAIN select * from staffs WHERE name like '%July'

EXPLAIN select * from staffs WHERE name like 'July%'

可以看到右边的百分号可以是range。

问题解决like ‘%字符串%’时索引不被使用的方法？

CREATE TABLE `tbl_user` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `NAME` VARCHAR(20) DEFAULT NULL,
 `age` INT(11) DEFAULT NULL,
 email VARCHAR(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_user(NAME,age,email) VALUES('1aa1',21,'b@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('2aa2',222,'a@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('3aa3',265,'c@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('4aa4',21,'d@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('aa',121,'e@163.com');

like ‘%abc%’ type 类型会变成 all
like ‘abc%’ type 类型为 range ，算是范围，可以使用索引
解决like ‘%字符串%’时索引不被使用的方法：使用覆盖索引

(9)字符串不加单引号会导致索引失效

（10）少用or，用它来连接时会索引失效

（11）小总结

————————————————• 索引列上使用函数

索引失效：

• where id +1=4
• 字符串不加引号, 出现隐式转换
• 索引类型为varchar时，使用int类型查询索引失效，使用string类型查询才能生效
• 索引字段是int时，使用Int类型查询和string类型都生效
• like 条件中前面加 %
• 负向查询
• not like 不可以
• or条件查询，所有的字段都简历索引才能命中索引

MySQL

索引优化1