勿对索引字段做函数操作

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能

但并不意味着放弃走索引，可能是全索引扫描，没有快速定位

条件字段函数操作

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;

• 统计发生在所有年份中7月份的交易记录总数

select count(*) from tradelog where month(t_modified)=7;

执行时间会特别久

• 为什么条件是where t_modified='2018-7-1'的时候可以用上索引，而改成where month(t_modified)=7的时候就不行了？

如上图，引擎会按照绿色箭头快速定位到 t_modified='2018-7-1’, 主要依赖于B+树同一层兄弟节点的有序性。

而对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能

查看一下SQL的执行计划：

可以看到，key是走索引的，Extrausing index表明用了覆盖索引，但注意rows扫描了10w多行。说明扫描了整个索引的所有值。

改成下面的写法，优化器就能用上t_modified索引的快速定位能力了：

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');

所以，由于加了month()函数操作，MySQL无法再使用索引快速定位功能，而只能使用全索引扫描。

• 在个问题上优化器确实有“偷懒”行为，只要对索引做了函数计算，即使是对于不改变有序性的函数，也不会考虑使用索引。

对于select * from tradelog where id + 1 = 10000这个SQL语句，这个加1操作并不会改变有序性，但是MySQL优化器还是不能用id索引快速定位到9999这一行。所以，需要你在写SQL语句的时候，手动改写成 where id = 10000 -1才可以。

隐式类型转换

select * from tradelog where tradeid=110717;

注意tradeid是有索引的，但是执行计划显示，走的却是全表扫描。

注意到其类型是varchar(32)，而输入的参数是整型。所以这里需要做类型转换。

• 数据类型转换的规则是什么？ mysql这里是将字符串转换成数字
• 为什么有数据类型转换，就需要走全索引扫描？

对于优化器来说，相当于：

select * from tradelog where CAST(tradid AS signed int) = 110717;

也就是说，这条语句触发了我们上面说到的规则：对索引字段做函数操作，优化器会放弃走树搜索功能。

隐式字符编码转换

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;

insert into tradelog values(1, 'aaaaaaaa', 1000, now());
insert into tradelog values(2, 'aaaaaaab', 1000, now());
insert into tradelog values(3, 'aaaaaaac', 1000, now());

insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');
insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

• 查询id=2的交易的所有操作步骤信息

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*语句Q1*/

1. 第一行，在tradelog上查到id=2的行，用上了主键索引，rows=1表示只扫描了一行
2. 第二行，key=NULL，表示没有用上trade_detail的tradeid索引，进行了全表扫描

这里，第二行不符合预期。因为表trade_detail里tradeid字段上是有索引的，我们本来是希望通过使用tradeid索引能够快速定位到等值的行。但，这里并没有。

经检查，发现这两个表的字符集不同:

tradelog L	trade_detail d
utf8mb4	utf8

• 为什么字符集不同就用不上索引呢？

Mysql 会将utf8转为utf8mb4再进行比较

而上面的SQL是先通过L表查到tradeid(utf8mb4)，然后去join d表的tradeid(utf8)。类似：

select * from trade_detail  where CONVERT(traideid USING utf8mb4)=L.tradeid.value;

这里就出现了对索引进行函数操作的现象。

再分析select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

会发现用上了索引，tradeid。为什么？

这里是先通过d表查到tradeid(utf8)，然后去join L表的tradeid(utf8mb4)。类似：

select operator from tradelog  where traideid =CONVERT(d.tradeid.value USING utf8mb4);

这里函数操作发生在条件入参上，并未作用于主键

• 解决方案（两种）

alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;

select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;