为什么这些SQL语句逻辑相同，性能却差异巨大

前言

在一次开发中，我们需要对数据进行按时统计，所以想到了使用 sql 的 date_format 函数

select date_format(create_time,'%Y-%m-%d %H')  as hour, count(*) count from table_name group by date_format(create_time, '%Y-%m-%d %H ');

后来发现这条 sql 执行效率非常的慢，但是看了一下，我对 create_time 加了索引，但是为什么还是这么慢呢？
原来由于使用了 date_format 函数，导致全表扫描。

那么还有那些情况会有这些问题呢？

条件字段函数操作

假设你现在维护了一个交易系统，其中交易记录表 tradelog 包含交易流水号（tradeid）、交易员 id（operator）、交易时间（t_modified）等字段。为了便于描述，我们先忽略其他字段。这个表的建表语句如下：

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;

我们可以这样改：

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

这种情况和我遇到的那种情况类型

隐式类型转换

看下这条语句

mysql> select * from tradelog where tradeid=110717;

tradeid 的字段类型是 varchar(32)，但是参数是整型，所以需要类型转换，所以优化器就会对参数的类型进行转换，就相当于：

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

所以也是用了函数操作，不能走索引树。

隐式字符编码转换

还有这种跨表查询，很难察觉。

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

注意两个表之间的编码
执行以下sql：

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

我们会发现 l.id 是走了索引的，但是 tradeid 并没有走索引树。

这是因为这两个表的字符集不同，一个是 utf8，一个是 utf8mb4，所以做表连接查询的时候用不上关联字段的索引。这个回答，也是通常你搜索这个问题时会得到的答案。

如果要优化：

1. 更改字符集
2. 修改sql

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