MySQL 索引失效

1、索引文件具有 BTree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

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`)

) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE INDEX id_age_classId_name on student(age,classId,name);
# 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.name='abcd';

虽然建立了索引，但是该索引最左边的是 age，查询语句中不包含 age，根据最左前缀匹配特性，这时候索引失效。

2、主键插入顺序。

如果此时再插入一条主键值为 6 的记录，那它插入的位置就如下图：

可这个数据页已经满了，如果再插进数据需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的这个页中。这个过程称为页面分裂和记录记录移位，该操作意味着： 性能损耗 ！所以如果想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增 ，这样就不会发生这样的性能损耗了。

所以尽量让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是手动插入 ，比如： person_info 表：我们自定义的主键列 id 拥有 AUTO_INCREMENT 属性，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。

CREATE TABLE person_info(
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL,
 birthday DATE NOT NULL,
 phone_number CHAR(11) NOT NULL,
 country varchar(100) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

自定义的主键列 id 拥有 AUTO_INCREMENT 属性，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。

3、计算、函数、类型转换(自动或手动)导致索引失效。

CREATE TABLE person_info(
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL,
 birthday DATE NOT NULL,
 phone_number CHAR(11) NOT NULL,
 country varchar(100) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);
CREATE INDEX idx_name ON student(NAME);
# 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
# 函数使索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

# 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';
# 类型转换使索引失效（name 是 VARCHAR，并不是 INT）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;

CREATE INDEX idx_sno ON student(stuno);
# 索引生效
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;
# 计算使索引失效
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

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

CREATE TABLE person_info(
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL,
 birthday DATE NOT NULL,
 phone_number CHAR(11) NOT NULL,
 country varchar(100) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);
CREATE INDEX id_age_classId_name on student(age,classId,name);
# 只有 age 和 classId 生效, name 失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student
 WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

CREATE INDEX id_age_name_classId on student(age,name,classId);
# age 、 classId、name 生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student
 WHERE student.age=30  AND student.name = 'abc' AND student.classId>20;

5、不等于(!= 或者<>)索引失效。

CREATE TABLE person_info(
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL,
 birthday DATE NOT NULL,
 phone_number CHAR(11) NOT NULL,
 country varchar(100) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);
CREATE INDEX id_name on student(name);
# name 失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student
 WHERE student.name != 'abc' ;
EXPLAIN SELECT SQL_NO_CACHE * FROM student
 WHERE student.name <> 'abc' ;

6、like 以通配符%开头索引失效。

CREATE TABLE person_info(
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL,
 birthday DATE NOT NULL,
 phone_number CHAR(11) NOT NULL,
 country varchar(100) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);
CREATE INDEX id_name on student(name);
# name 失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE '%abc%';

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

7、is null可以使用索引，is not null无法使用索引。

CREATE TABLE person_info(
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL,
 birthday DATE NOT NULL,
 phone_number CHAR(11) NOT NULL,
 country varchar(100) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);
CREATE INDEX id_age on student(age);
# age 生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
# age 失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

8、OR 前后存在非索引的列，索引失效。

CREATE TABLE person_info(
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL,
 birthday DATE NOT NULL,
 phone_number CHAR(11) NOT NULL,
 country varchar(100) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);
CREATE INDEX id_age on student(age);
# age 生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

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

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