索引失效-B

程序员文章站 2022-05-03 13:06:08

...

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

索引失效-B

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

索引失效-B

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

索引失效-B

注意null/not null对索引的可能影响

索引失效-B

建议新建一个库，试试测试脚本

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());
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(NAME, age, pos);
ALTER TABLE staffs ADD INDEX idx_staffs_name(NAME);
EXPLAIN SELECT * FROM staffs WHERE NAME IS NULL;
EXPLAIN SELECT * FROM staffs WHERE NAME IS NOT NULL;

索引失效-B

测试脚本如下：

CREATE TABLE staffs2 (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (24),
  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 staffs2(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
ALTER TABLE staffs2 ADD INDEX idx_staffs2_nameAgePos(NAME, age, pos);
ALTER TABLE staffs2 ADD INDEX idx_staffs2_name(NAME);
 
EXPLAIN SELECT * FROM staffs2 WHERE NAME IS NULL;
EXPLAIN SELECT * FROM staffs2 WHERE NAME IS NOT NULL;

官网说明

索引失效-B

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

索引失效-B

问题：解决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;
 
#drop table tbl_user
 
INSERT INTO tbl_user(NAME,age,email) VALUES('1aa1',21,'aaa@qq.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('2aa2',222,'aaa@qq.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('3aa3',265,'aaa@qq.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('4aa4',21,'aaa@qq.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('aa',121,'aaa@qq.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 id,NAME,age 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_user_nameAge ON tbl_user(NAME,age);
 
#DROP INDEX idx_user_nameAge ON tbl_user
 
#after index
 
EXPLAIN SELECT * FROM tbl_user WHERE NAME =800 AND age = 33;

字符串不加单引号索引失效

索引失效-B