索引失效案例

• 1.全值匹配我最爱，最左前缀要遵守。
• 2.带头大哥不能死，中间兄弟不能段。
• 3.索引列上少计算，范围之后全失效。
• 4.like 百分写最右，覆盖索引不写*。
• 5.不等空值还有or,索引失效要少用。
• 6.字符串引号不可丢，SQL高级也不难。

全值匹配我最爱

全职匹配我最爱指的是，查询的字段按照顺序在索引中都可以匹配到

最佳左前缀法则

使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

主键插入顺序 

 

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

可这个数据页已经满了，再插进来咋办呢？我们需要把当前 页面分裂 成两个页面，把本页中的一些记录 移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着： 性能损耗 ！所以如果我们想尽量 避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增 ，这样就不会发生这样的性能损耗了。 所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入 ， 比如： person_info 表： 

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 属性，在插入记录时存储引擎会自动为我们填入自增的 主键值。这样的主键占用空间小，顺序写入，减少页分裂。  

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

创建索引

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';

类型转换导致索引失效 

下列哪个sql语句可以用到索引。（假设name字段上设置有索引）

# 未使用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123; 

# 使用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123'

name=123发生类型转换，索引失效。 

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

ALTER TABLE student DROP INDEX idx_name;

ALTER TABLE student DROP INDEX idx_age;

ALTER TABLE student DROP INDEX idx_age_classid;

EXPLAIN SELECT SQL_NO_CACHE * FROM student 

WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' 

 

create index idx_age_name_classid on student(age,name,classid);  

将范围查询条件放置语句最后： 

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 

'abc' AND student.classId>20 ;

 

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

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

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL; 

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

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

拓展：Alibaba《Java开发手册》

【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

 

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

# 未使用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

#使用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR name = 'Abel';

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

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