数据库缓存失效有以下六种情况:
1.使用or操作符
当where语句中使用or
操作符并且or
两边的条件涉及到至少两个字段时,MySQL无法使用索引,会转向全表扫描。因此,应尽量避免使用or操作符。
原因是MySQL中的索引是根据某个字段进行排序建立的,使用操作符or时,说明or两端任何一个条件满足都可以被查询出来,所以这是索引可能就不是判断成立的唯一标准,可能会用到全表扫描。
EXPLAIN SELECT * FROM books WHERE id > 8 OR NAME = 'Lovey Day'
EXPLAIN SELECT * FROM books WHERE id > 8 OR id >1
2.like查询中以’%'开头
原因:字符串的排序规则为按字母字典序排序,模糊查询时开头’%'时,则认为什么字符都可以,会无法使用索引。
EXPLAIN SELECT * FROM books WHERE NAME LIKE '%o'
3. 隐式转换
关于隐式转换:官方文档: 12.2 Type Conversion in Expression Evaluationopen in new window
当操作符与不同类型的操作数一起使用时,会发生类型转换以使操作数兼容。某些转换是隐式发生的。例如,MySQL 会根据需要自动将字符串转换为数字,反之亦然。以下规则描述了比较操作的转换方式:
- 两个参数至少有一个是
NULL
时,比较的结果也是NULL
,特殊的情况是使用<=>
对两个NULL
做比较时会返回1
,这两种情况都不需要做类型转换 - 两个参数都是字符串,会按照字符串来比较,不做类型转换
- 两个参数都是整数,按照整数来比较,不做类型转换
- 十六进制的值和非数字做比较时,会被当做二进制串
- 有一个参数是
TIMESTAMP
或DATETIME
,并且另外一个参数是常量,常量会被转换为timestamp
- 有一个参数是
decimal
类型,如果另外一个参数是decimal
或者整数,会将整数转换为decimal
后进行比较,如果另外一个参数是浮点数,则会把decimal
转换为浮点数进行比较 - 所有其他情况下,两个参数都会被转换为浮点数再进行比较
**隐式类型转换会导致索引失效,比如当字段类型为字符串且建有索引,而查询条件类型为数值时,会将字符串类型隐式转换为浮点型,此时索引会失效。
原因:字符串类型转换为浮点数会使用cast函数,此时索引列上使用函数,导致索引失效。
4.索引上使用函数
原因:因为索引保存的是索引字段的原始值,而不是经过函数计算后的值,自然就没办法走索引了。
EXPLAIN SELECT * FROM books WHERE length(NAME) = 2
5. 索引上使用表达式计算
原因同上
EXPLAIN SELECT * FROM books WHERE id+1 = 2
6.复合索引失效
如果使用了复合索引,但查询时未使用索引的第一列,索引也会失效。
eg:
(t1,t2)建立了复合索引,但查询时没用到t1
EXPLAIN SELECT * FROM books WHERE t2 = 2