小徐带你了解为何Mysql中索引会失效(全网独到见解2020最新版)

避免索引失效的十种常用策略(小口诀)

创建表和索引的语句

#创建staffs表
CREATE TABLE staffs (
	id INT PRIMARY KEY auto_increment,
	NAME VARCHAR ( 25 ) 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
	(
		'z1',
		20,
		'manager',
	NOW()),
	(
		'z2',
		21,
		'employee',
	NOW()),
	(
		'z3',
		22,
		'dev',
	NOW());
	#查询表中的数据
SELECT
	* 
FROM
	staffs;
	#创建复合索引
ALTER TABLE staffs ADD INDEX idx_rangeNameAgePos ( NAME, age, pos );
#查看表中的索引
show index from staffs;

一：全值匹配我最爱

全职匹配我最爱解释：在我们创建的复合索引中，要使用到创建的索引，并且使用的顺序和我们创建索引的顺序保持一致

案例：

创建了包含三个字段的复合索引例子：

alter	table	staffs	add 	index	idx_staffs_nameAgePos(name,age,pos);

二：最佳左前缀法则：

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

简言之：带头大哥不能死，中间兄弟不能断

三：不在索引列上做任何操作(计算，函数，(自动或者手动)类型转换)，会导致索引失效转向全表扫描

四：存储引擎不能使用索引中范围条件右边的列

五：尽量使用覆盖索引(只访问索引的查询(查询列和查询列一致))，减少select *

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

七：is null， is not null也无法使用索引

八：like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作 like % 加右边

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

使用覆盖索引解决

#创建表
CREATE TABLE tbl_user (
	id INT 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' ),
	( '2aa2', 22, 'a@163.com' ),
	( '3aa3', 23, 'c@163.com' ),
	( '4aa4', 24, 'd@163.com' );
#before index  没有使用索引之前
explain select name,age from tbl_user where name like '%aa%';
explain select id from tbl_user where name like '%aa%';
explain select name from tbl_user where name like '%aa%';
explain select age from tbl_user where name like '%aa%';
explain select id,name from tbl_user where name like '%aa%';
explain select name,age from tbl_user where name like '%aa%';
explain select * from tbl_user where name like '%aa%';
explain select id,name,age,email from tbl_user where name like '%aa%';
#创建索引之后
#create index
create index idx_name_age on tbl_user(name,age);

explain select name,age from tbl_user where name like '%aa%';
explain select id from tbl_user where name like '%aa%';
explain select name from tbl_user where name like '%aa%';
explain select age from tbl_user where name like '%aa%';
explain select id,name from tbl_user where name like '%aa%';
explain select name,age from tbl_user where name like '%aa%';
explain select * from tbl_user where name like '%aa%';
explain select id,name,age,email from tbl_user where name like '%aa%';
	

九：字符串不加单引号索引会失效，字符串里有引号

注意：varchar类型的数据别忘记单引号，否则会引起隐式类型转换，索引失效

十：少用or，用它来连接时会索引失效

十一：小总结

优化总结小口诀

全值匹配我最爱，最左前缀要遵守

带头大哥不能死，中间兄弟不能断

索引列上少计算，范围之后全失效

Like百分写最右，覆盖索引不能写星

不等空值还有or，索引失效要少用

VAR引号不能丢，SQL高级也不难