MySQL生产实战索引失效问题

今天遇到一个索引失效问题很有意思，也有一个小小的知识点以前没有注意，特写此文记录一下

我们常规遇到索引失效有可能下面几种情况
1、左’%'查询
2、隐式转换
3、使用联合索引不符合最左匹配
4、字符集编码不一致
5、or 条件中有字段没有索引
6、在索引列上使用函数或运算符表过式
7、表太小
8、过滤性差，还需要回表
9、有hint强制全表扫

但我今天遇到的索引失效都不是以上情况

我们来问题复现一下
创建测试数据

DROP FUNCTION IF EXISTS fun1;
CREATE FUNCTION fun1(time1 DATETIME) RETURNS INT READS SQL DATA
RETURN year(time1);

    create table test_fun
    ( id int ,
      id1 int,
      id2 int,
      primary key(id),
      key idx_id1(id2)
    ) engine = InnoDB;

insert into test_fun
select help_topic_id,help_topic_id,help_topic_id from mysql.help_topic;
复制

我们来看以下三个语句

语句一

    explain 
    select * from test_fun where id2 = 2013;
复制

语句一 走了索引

语句二

    explain 
    select * from test_fun where id2 = year(now()); 
复制

语句二 也走了索引

语句三

explain 
    select * from test_fun where id2 = fun1(now()); 
复制

语句三 索引失效，走的是全表

通过上面的函数定义可以看到函数非常简单 直接把函数内的表达式写在where条件处，如语句二也是可以走索引的。

遇到问题第一个反应就是翻官方文档

A routine is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise. If neither DETERMINISTIC nor NOT DETERMINISTIC is given in the routine definition, the default is NOT DETERMINISTIC. To declare that a function is deterministic, you must specify DETERMINISTIC explicitly.
Assessment of the nature of a routine is based on the “honesty” of the creator: MySQL does not check that a routine declared DETERMINISTIC is free of statements that produce nondeterministic results. However, misdeclaring a routine might affect results or affect performance. Declaring a nondeterministic routine as DETERMINISTIC might lead to unexpected results by causing the optimizer to make incorrect execution plan choices. Declaring a deterministic routine as NONDETERMINISTIC might diminish performance by causing available optimizations not to be used.
If binary logging is enabled, the DETERMINISTIC characteristic affects which routine definitions MySQL accepts. See Section 25.7, “Stored Program Binary Logging”.

上面两个关键信息
1、函数默认就是声明为不确定性函数
2、不确定性函数可能会导致不使用可用的优化

现在将函数声明为确定性函数

DROP FUNCTION IF EXISTS fun2;
CREATE FUNCTION fun2(time1 DATETIME) RETURNS INT DETERMINISTIC
    RETURN year(time1);
复制

执行语句

explain 
    select * from test_fun where id2 = fun2(now()); 
复制

good!! 已正确走了索引

这篇文章的小小知识点
1、函数要通过关键字DETERMINISTIC 显示声明为确定性函数
2、在等号右侧使用不确定性函数也会让索引失效