mysql索引不连续 mysql 索引 不等于

索引失效原则

不要再索引上进行任何操作（计算、函数、类型转换），否则索引失效。

• 不要在索引上进行加减乘除计算，例如：where t.tid * 3 = ‘’；乘与3为计算，会导致索引失效。
• 不能使用不等于（!=、<、>、is null、is not null），模糊查询like以百分号%开头（’%参数%’）。

对于复合索引。

• 不要跨列使用或无需使用（满足最佳左前缀）。
• 尽量使用全索引匹配，复合索引中将不用的索引去掉。
• 左边的索引失效会导致右边的索引失效。

1.计算，导致索引失效

# 该查询会导致索引失效
explain select * from book where authorid * 2 = 1 and typeid = 2;

• 由于乘与2，导致左边authorid索引失效，右边的也一起失效。
• 因为是复合索引，左边的失效，右边的也会失效。
• 如果不是复合索引，左边失效了不会导致右边失效。

# 删除book的索引
drop index index_atb on book;

# 添加单值索引
alter table book add index index_a(authorid);
alter table book add index index_t(typeid);

# 索引计算查询
explain select * from book where authorid * 2 = 1 and typeid = 2;

单值索引情况下，右边的索引不会失效。

2.!=计算，导致索引失效

# !=条件查询
explain select * from book where authorid != 1 and typeid = 2;

authorid使用了’!='运算符，导致索引失效。

3.比较符号（> < !=），导致索引失效

因为服务层中存在SQL优化器，可能会影响我们的优化。

# 删除book索引
drop index index_t on book;
drop index index_a on book;

# 添加复合索引
alter table book add index index_at(authorid, typeid);

# 条件查询，索引全部都在使用
explain select * from book where authorid = 1 and typeid = 2;

# 用了>比较，进行条件查询
explain select * from book where authorid > 1 and typeid = 2;

在5.6及以前的数据库版本中：’>‘运算符本身以及右边的索引会失效。

在5.7及以前的数据库版本中：’>'运算符本身会是range级别，而右边的会失效。

# < 1 的情况下本身不失效
explain select * from book where authorid < 1 and typeid = 2;

# < 4 的情况下索引全部失效
explain select * from book where authorid < 4 and typeid = 2;

索引失效可能性是概率事件。

结论：

• 一般的SQL优化在大部分情况下适合使用，但是由于SQL优化器原因，不是100%正确。
• 一般而言，范围查询（>、<、in），本身索引有效，右边的全部会失效。
• 尽量使用覆盖索引（using index），即索引不会失效。

4.索引失效（模糊查询like），导致索引失效

# 给name创建索引
alter table book add index index_name(name);

# 模糊查询
explain select * from book where name like '%1%';

由于使用了模糊查询like，以百分号%开头，会造成索引失效：

# 使用不包含%开头的模糊查询
explain select * from book where name like '1%';

因为模糊查询中不是以备份好%开头，索引不会失效：

5.不要进行类型转换

# 类型转换查询
explain select * from book where name = 123;

因为name为字符串类型，123为整数类型，SQL底层将123转换为’123’，进行了类型转换，因此索引失效。

6.尽量不使用or

# or条件查询
explain select * from book where name = '' or typeid = '';

因为使用了or连接条件，会导致两边的索引全部失效：

为什么会导致索引失效？

• 因为在设置索引的时候就将字段的类型进行存储，如在强转过程中会导致索引失效，是因为强转后该字段的类型与索引中的不匹配，导致全表扫描。
• 在聚合索引中为什么要满足最佳左前缀？因为聚合索引在存储过程中以原本的顺序进行存储，当查询条件顺序与索引顺序不一致也会导致索引失效。