索引失效案例

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器？基于cost开销

(CostBaset0 ptimizer),它不是基于规则(Rule-Based0 ptimizer),也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

1 全值匹配我最爱

系统中经常出现的sql语句如下：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND name = 'abcd';

建立索引前执行：（关注执行时间）

SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND name = 'abcd';
Empty set, 1 warning (0.15 sec)

建立索引

mysql> alter table student add index idx_age(age);
Query OK, 0 rows affected (1.56 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student add index idx_age_classid(age,classId);
Query OK, 0 rows affected (1.83 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student add index idx_age_classid_name(age,classId,name);
Query OK, 0 rows affected (2.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

建立索引后执行：

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND name = 'abcd';
Empty set, 1 warning (0.00 sec)

可以看到，创建索引前的查询时间是0.15秒，创建索引后的查询时间是不到0.1秒，索引帮助我们极大的提高了查

询效率。

2 最佳左前缀法则

在MySQL建立联合索引时会遵守最佳左前缀原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

举例1：

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

举例2：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=1 AND 
student.name = 'abcd';

可以看到没有使用索引

举例3：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=4 AND 
student.age=30 AND student.name = 'abcd';

使用了索引，优化器优化了查询条件顺序可以使用索引

举例4：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

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

索引长度是5，虽然可以正常使用，但是只有部分被使用到了。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=1 AND 
student.name = 'abcd';

完全没有使用上索引。

结论：MySQL可以为多个字段创建索引，一个索引可以包含16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果 查询条件中没有用这些字段中第一个字段时，多列（或联合）索引不会被使用。

3 主键插入顺序

对于一个使用InnoDB存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在1-100之间：

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

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

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

1. 这两条sql哪种写法更好

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

2.创建索引

CREATE INDEX idx_name ON student(NAME);

3. 第一种：索引生效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 
'abc%';

查询时间：小于0.1秒

SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 
'abc';

4. 索引优化失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE 
LEFT(student.name,3) = 'abc';

查询时间：0.14秒， 查询效率较之前低

5 类型转换导致索引失效

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

# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;

# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

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

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

1. 系统经常出现的sql如下：

# 删除索引
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' ;

可以看到name列没有使用索引( **<font style="background-color:#FBDE28;">范围右边的列不能使用。比如：(<) (<=) (>) (>=) 和 between 等 ** )，这里的是指索引里列的右边 （创建的联合索引中，务必把范围涉及到的字段写在最后） 。

2. 如果这种sql出现较多，应该建立这样的所以你进行优化：

alter table student add index idx_age_name_classid(age,name,classId);

这样mysql优化器会将上面的sql修改成如下格式：

# 原sql
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

# 优化器改写后sql
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.name = 'abc' AND student.classId>20;

可以看到索引已经生效，所有列也都生效。

使用到的索引长度为73：

age：int类型 4 + 是否为空 1 = 5

classId： int类型 4 + 是否为空 1 = 5

name：varchar(20) 类型 utf-8mb3 3 * 30 + 是否为空 1 + 变长类型的长度 2 = 60

则使用的索引长度为73

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

为name字段创建索引 ， 查看索引是否失效

CREATE INDEX idx_name ON student(NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc';

可以看到索引失效

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

• IS NULL: 可以触发索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;

• IS NOT NULL: 无法触发索引

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

结论：最好在设计数据库的时候就将 字段设置为 NOT NULL 约束，比如你可以将 INT 类型的字 段，默认值设置为0。将字符类型的默认值设置为空字符串('')。 扩展：同理，在查询中使用 not like 也无法使用索引，导致全表扫描。

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

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为'%'，索引就不会起作用。只 有'%'不在第一个位置，索引才会起作用。

• 使用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE 'ab%';

• 未使用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE '%ab%';

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

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

在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。也就是说，OR前后的两个条件中的列都是索引时，查询中才使用索引。

因为OR的含义就是两个只要满足一个即可，因此 只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此所以的条件列也会失效。

查询语句使用OR关键字的情况：

# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

因为classId字段上没有索引，所以上述查询语句没有使用索引。

#使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR name = 'Abel';

因为age字段和name字段上都有索引，所以查询中使用了索引。你能看到这里使用到了 index_merge ，简单来说index_merge就是对age和name分别进行了扫描，然后将这两个结果集进行 了合并。这样做的好处就是 避免了全表扫描。

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

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

12 一般性建议

举例：假设：index(a,b,c)

• 对于单列索引，尽量选择针对当前query过滤性更好的索引
• 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 在选择组合索引的时候，尽量选择能够当前query中where子句中更多的索引。
• 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。