mysql int索引无效,MySQL索引失效常见情景

建表

创建索引

索引失效 几种常见情景案例

索引失效针对的组合索引？

若一个字段上有多种索引呢？某一索引失效，可以继续使用其他索引不影响。

全值匹配

索引  idx_staffs_nameAgePos 建立索引时 以 name ， age ，pos 的顺序建立的。全值匹配表示按顺序匹配的

最佳左前缀法则

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

1.当使用覆盖索引的方式时，(select name/age/id from staffs where age=10 (后面没有其他没有索引的字段条件))，即使不是以 name 开头，也会使用 idx_nameAge 索引。

既 select后的字段有索引，where 后的字段也有索引，则无关执行顺序。

2.除开上述条件 才满足最左前缀法则。

没有车头.png

image.png

满足最左前缀法则.png

不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描

image.png

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

[图片上传失败...(image-7aee9-1614697987989)]

失效

范围条件失效.png

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

[图片上传失败...(image-538088-1614697987990)]

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

image.png

is not null 也无法使用索引,但是is null是可以使用索引的

image.png

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

问题：解决like '%字符串%'时索引不被使用的方法？

用staffs表做案例,索引：

image.png

执行以下操作：

查询全部

image.png

id查询条件

image.png

NAME查询条件

image.png

id,name查询条件

image.png

name,age查询条件

image.png

id,NAME,age查询条件

image.png

id,NAME,age,add_time查询条件

image.png

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

没有单引号情况，索引失效

image.png

有单引号用到了索引

image.png

少用or,用它来连接时会索引失效

image.png

小总结

假设index(a,b,c)

序号7 左边定值，所以索引都能用到

序号8 左边值不确定

序列号

where语句

索引是否被引用

1

where a=3

Y，使用到a

2

where a=3 and b=5

Y，使用到a，b

3

where a=3 and b=5 and c=4

Y，使用到a，b,，c

4

where b=3 或者 where b=4 and c=5 或者 where c=5

N

5

where a=3 and c=5

使用到a，但是c不可以，b中间断了

6

where a=3 and b>4 and c=5

使用到a,b   c不能作用在范围之后，b断了

7

where a=3 and b like 'kk%' and c=5

a能用，b能用，c能用

8

where a=3 and b like '%kk' and c=5

Y，只用到a

9

where a=3 and b like '%kk%' and c=5

Y，只用到a

10

where a=3 and b like 'k%kk%' and c=5

Y，a,b,c

【优化总结口诀】

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用；