索引失效情况合集

java_小白
116 阅读4分钟

索引语法

  • 创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;
  • 查看索引
SHOW INDEX FROM table_name ;
  • 删除索引
DROP INDEX index_name ON table_name

最左前缀法则

联合索引

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始， 并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

思考题： 存在一个联合索引顺序为（profession，age，status）

当执行SQL语句:

explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程'；

时，是否满足最左前缀法则，走不走上述的联合索引，索引长度？

是完全满足最左前缀法则的，索引长度54，联合索引是生效的。 注意 ： 最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是 第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。

当执行以下SQL语句时：

explain select * from tb_user where age = 31 
explain select * from tb_user where status = '0'
explain select * from tb_user where age = 31 and status = '0'

索引都会失效

当执行以下SQL语句时：

explain select * from tb_user where  profession = '软件工程'
explain select * from tb_user where  profession = '软件工程' and status = '0'
explain select * from tb_user where  profession = '软件工程' and profession = '软件工程'

部分索引生效

范围查询

联合索引

  • 联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。
  • 但是出现范围查询（>= , <=），联合索引不会失效
  • 所以在业务允许的情况下，尽量使用>=或者<=
CREATE INDEX test_01 ON test(age,status);
explain select * from test where age > 20 and status = 1;

索引长度为5,age生效，右侧的status失效了

image.png

CREATE INDEX test_01 ON test(age,status);
explain select * from test where age >= 20 and status = 1;

索引长度为10,age与status都生效

image.png

!= 或者 <>

应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

null值判断

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。

注意：并不是说使用了is null或者 is not null、!= 、 <>o 就会不走索引了，这个跟mysql版本以及查询成本都有关； 如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null经常被认为让索引失效； 其实是因为一般情况下，查询的成本高，优化器自动放弃索引的；

索引列运算

单列索引（phone）

explain select * from test where age / 2 = 10;

image.png

可以看到type为ALL，走的是全表扫描，并且没有使用索引。

字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。

explain select * from tb_user where phone = '17799990015';

索引生效

explain select * from tb_user where phone = 17799990015;

索引失效

模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

explain select * from tb_user where profession like '软件%';

索引生效

explain select * from tb_user where profession like '%工程';

索引失效

explain select * from tb_user where profession like '%工%';

索引失效

or连接条件

使用or可能会使索引失效，从而全表扫描；

  1. 两个列都是唯一索引，执行计划会走两个索引。

  2. 两个列一个有唯一索引，另一个没有索引，不会走索引，因为整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定；

  3. 两个列都有索引但是不是唯一索引，看查询条件占数据的情况，如果查询条件都是唯一或者说很少的情况，会走两个索引，有一个不是（例如查询性别=‘男’，而‘男’占大多数，就不会走索引），这种情况就是全表扫描，原因和3相同；

联合索引的情况，分两种

  1. select 联合索引的两个列 from 表 where name = 'ew' or age = 12; 这种的执行计划是index，显示索引树扫描二级索引。
  2. select * from 表where name = 'ew' or age = 12; 这种执行计划就是all，因为or会导致索引失效。

数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。 因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。

avatar
文章
阅读
粉丝