避免索引失效的十种常用策略(小口诀)
创建表和索引的语句
#创建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高级也不难