优秀的编程知识分享平台

网站首页 > 技术文章 正文

什么情况会导致 MySQL 索引失效?(mysql什么情况下会导致索引失效)

nanyue 2024-10-28 16:40:16 技术文章 4 ℃

为了验证在哪些情况下会导致索引失效,这里我们借助了一个分析关键字 explain来进行分析。explain的用法是在执行SQL语句之前加上这个关键字即可。如下所示。

explain SELECT * FROM `sys_oper_log`

并且其查询结果列如下

  • id:选择标识符,id越大优先级越高,越先被执行。
  • select_type:表示查询的类型
  • table:输出结构的表
  • partitions:匹配分区
  • type:表示表的连接类型
  • possible_keys:表示查询的时候可能用到的索引
  • key:表示实际使用索引
  • key_len:索引字段的长度
  • ref:列与索引的比较
  • rows:大概估计的执行行数
  • filtered:按照条件过滤表的百分比
  • Extra:执行情况描述说明

其中比较重要的字段就是type,type有以下的一些值

  • all:表示全表扫描
  • index:表示索引遍历
  • range:表示范围查找
  • index_subquery:在子查询中使用了ref
  • unique_subquery:在子查询中使用了eq_ref
  • ref_or_null 对null进行了索引优化的ref
  • fulltext 使用全文索引
  • ref 使用了非唯一性索引查找
  • eq_ref:在join中使用了主键或者是唯一键关联
  • const:将一个主键放到了where的后面作为查询条件。

索引失效情况一:非最左匹配

所谓的最左匹配是指以最左边的起点字进行查询可以使用联合索引,否则就不能使用联合索引。

这里我们假设使用索引的字段顺序是A+B+C,如果是最左匹配那么如下的一些索引都会被使用到到

  • A+B+C
  • A+B
  • A+C

但是对于B+C的操作则不能使用到联合索引,这就是最左匹配原则。

索引失效情况二:错误的使用了模糊查询

我们常用的模糊查询的匹配方式有如下几种

  • like '张%'
  • like '%张'
  • like '%张三%'

这三种情况只有第一种情况会用到索引,其他的则不会使用到索引

索引失效情况三:进行了列运算

如果在执行SQL的过程中使用索引列使用了运算,那么就会导致索引失效。

例如在执行使用id查询的时候执行了如下的操作。

select * operation_log where id+1=2

索引失效情况四:使用了函数

在进行查询的时候,如果查询列使用了任意的MySQL函数都会导致索引失效,例如,在有些判断中使用了count函数。

索引失效情况五:进行了类型转换

如果在查询的时候对索引列进行了类型的转换那么也不会使用索引,例如我们将一个string类型的数据转换成了一个int类型的数据,则不会触发索引。

索引失效情况六:使用 is not null

当在一个查询中出现了 is not null 的时候也会导致使用的索引失效的情况发生。例如在我们查询某些数据的时候会使用到 is not null 来判断数据字段是否为空。

最近发表
标签列表