mysql> CREATE TABLE tradelog (
id int(11) NOT NULL,
tradeid varchar(32) DEFAULT NULL,
operator int(11) DEFAULT NULL,
t_modified datetime DEFAULT NULL,
PRIMARY KEY (id), KEY tradeid (tradeid), KEY t_modified (t_modified))
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

函数计算导致索引失效

在字段上使用计算函数，会导致索引失效。因为计算函数有可能破坏索引的有序性，MySQL优化器干脆就放弃了索引搜索，改成全索引遍历。
select count() from tradelog where month(t_modified)=7;
image.png
可以看到，虽然用上了索引，但是还是全表扫描了。
可以替换为
mysql> select count() from tradelog where
-> (t_modified >= ‘2016-7-1’ and t_modified<’2016-8-1’) or
-> (t_modified >= ‘2017-7-1’ and t_modified<’2017-8-1’) or
-> (t_modified >= ‘2018-7-1’ and t_modified<’2018-8-1’);
这时就会用上索引字段t_modified的范围查询。
同样的case还有select from tradelog where id + 1 = 10000，和select from tradelog where where id = 10000 -1，前者不会走id索引，后者则可以，虽然前者SQL的函数计算并没有破坏索引字段的有序性。

类型转换导致索引失效

对于上面的tradeLog表，执行SQL：select * from tradelog where tradeid=110717;
虽然tradeid字段有索引，但是通过explain还是走了全表扫描。
因为表中tradeid字段是varchar类型的，在MySQL中如果字符串和数字比较，规则是把字符串转化为数字，然后比较数字的大小。所以where tradeid=110717等同于where cast(tradeid as unsign int)=110717，在tradeid字段上用了计算函数，索引自然就失效了。

字符串与数字的比较

select ‘12’ > ‘110’ 返回1
select ‘10’ < ‘9’ 返回1
select ‘1’ = 0 返回0
select ‘a’ = 0 返回1
字符串之间的比较，是从高位到地位逐个比较，比较的方式为按照ascii码比较，所以会有’10’ < ‘9’ ，’12’ > ‘110’。
字符串和数字之间的比较，涉及到字符串转化为数字，这个过程里字符串中无法转化为数字的部分，会被丢弃。cast(‘a’ as signed int)的值是0，cast(‘0a’ as signed int)的值是0，cast(‘1a’ as signed int)的值是1，cast(‘11a’ as signed int)的值是11.

隐私字符编码转换导致索引失效

在已有tracelog表的前提下，我们新增一个交易详情表trade_detail
CREATE TABLE trade_detail (
id int(11) NOT NULL,
tradeid varchar(32) DEFAULT NULL,
trade_step int(11) DEFAULT NULL, /操作步骤/
step_info varchar(32) DEFAULT NULL, /步骤信息/
PRIMARY KEY (id), KEY tradeid (tradeid)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
注意这个表的默认CHARSET=utf8，已有的tradelog表的默认CHARSET=utf8mb4。

如果要查询交易记录表中id=2的全部交易详情信息，我们一般会执行以下SQL
select d. from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
通过explain结果发现：
第一行显示在tradelog表查询id=2的行时，用上了主键索引，扫描行数=1
第二行显示key=NULL，表示在trade_detail表上按照tradeid查询时，没有走索引，进行了全表扫描。
原因在于，tradelog表中的tradeid是utf8mb4编码格式，trade_detail中的tradeid是utf8格式。utf8mb4是utf8的超集，所以在比较时，MySQL内部会是先把utf8字符串转化成utf8mb4字符串，再进行比较。
对于上面的join sql的第二行部分，等价与select d.x from trade_detail d where CONVERT(d.tradeid using utf8mb4)=$l.tradeid.value，相当于又命中了‘对索引字段使用计算函数，会有可能破坏索引的有序性，导致MySQL放弃使用索引树搜索功能’。

总结

对索引字段使用计算函数，会有可能破坏索引的有序性，导致MySQL放弃使用索引树搜索功能
函数操作常见的有三种：1）显示的函数计算操作。2）隐式的字段类型转换。3）隐式的字符集转换。