Sql优化-为什么SQL语句逻辑相同，性能却差异巨大？

根据极客时间学习的资料思考整理，有三个案例，我们根据案例了解一下为什么性能好或不好，希望下次我们再写SQL的时候能够注意，能够写出一个比较完美的SQL！

案例一：条件字段函数操作

假设现在有一个表是交易系统，其中交易记录表tradelog包括交易流水号（tradeid）、交易员id（operator）、交易时间（t_modified）等字段，建表语句如下：

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;


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 `tradelog` VALUES (4, 'aaaaaaad', 1000, '2022-07-01 12:05:23');
INSERT INTO `tradelog` VALUES (5, 'aaaaaaaf', 1000, '2021-07-22 12:06:08');
INSERT INTO `tradelog` VALUES (6, 'aaaaaaae', 1000, '2023-04-01 12:08:30');

我们的数据有2021~2023的数据，假设让你统计所有年份中7月份的交易记录总数，你的SQL大致会这么写：

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

由于t_modified上有索引，所以你觉得可以，但如果你的数据量很多，你会发现这个SQL语句执行很久，才返回结果。为什么呢？

你大概已经猜到了，对字段做了函数计算就用不上索引了，这是MySql的规定。

但是你试了下如下的sql语句发现走索引了，为什么这样行，上面就不行呢

select count(*) from tradelog where t_modified='2021-07-22 12:06:08'

下面是 t_modified索引的示意图，方框上面的数字就是montg()函数对应的值。

​​​​​​​

 

如果你的SQL语句是t_modified='2021-07-22 12:06:08'的话，它就会按上面的卢瑟箭头走，快速定位结果。B+tree的快速搜索能力来源于同一层兄弟节点的有序性，如果计算month传入7，在树的第一层就不知道怎么办了。

也就是说，对索引做函数操作，可能会破坏索引值的有序性，因此优化器就放弃了走树搜索功能。

但没有放弃使用这个索引，我们看下执行计划：

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

看下下面的执行计划，我们的KEY索引走的是t_modified，但是总共数据6条，就扫描了6次，

 因为添加了month()函数导致全索引扫描，我们可以用如下语句优化下，为了让其能够使用预期的索引：

select count(*) from tradelog where (t_modified>='2021-07-01' and t_modified<='2021-08-01')
				or  (t_modified>='2022-07-01' and t_modified<='2022-08-01')
				or  (t_modified>='2023-07-01' and t_modified<='2023-08-01')

看下执行计划：

我们这个sql语句最后统计5条数据，所以rows扫描了5条数据，而不是6条数据，看下type是采用的range代表扫描给定范围的索引数据

 不过引申出来其他的优化器“偷懒”行为，比如不改变有序的函数，也不会使用索引，如下SQL语句：

#全表扫描，不使用索引
EXPLAIN select * from tradelog where id+1=6

执行计划如下：代表了没有使用索引，全表扫描

 我们改成如下的SQL语句就可以使用上索引：

# 索引查找
EXPLAIN select * from tradelog where id=6-1

 看执行计划，还是最好的使用索引方式，只扫描一行数据

 案例二：隐式类型转换

我们看下下面这个语句，我们知道tradeid的类型是字符串类型，然后我们查询条件给的是int类型，看下会发生什么？

EXPLAIN select * from tradelog where tradeid=122222

 发现索引失效了，没有用到索引而是全表扫描，正常应该是用到tradeid的索引的，

 出现上面的原因是因为进行了类型转换，需要把数字转换为字符串，这时候需要内置函数处理的，因为使用了内置函数转换，所以导致了索引失效。这个语句就相当于 select * from tradelog where CAST(tradeid as signed int)=122222;这个语句，使用了CAST函数进行转换。

再给你一个SQL语句，查询条件是id，我们知道id是int，查询条件是字符串，那么这个语句会进行全表扫描吗？

EXPLAIN	select * from tradelog where id='1'

答案是不会，我们看执行计划就知道，走了索引，为什么这样的就不会索引失效呢？

 在这里是字符串转数字，在MySql中，字符串隐式的转换为数字，因为不用使用内置函数，所以不会导致索引失效。

案例三：隐式字符编码转换

我们在添加一个表，叫记录交易细节表把，表名叫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;


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的交易所有操作步骤信息：

EXPLAIN select d.* from tradelog l,trade_detail d where d.tradeid=l.tradeid and l.id=2

查看下执行计划：

发现tradelog表执行了索引操作，而trade_detail则没有索引进行了全表扫描，按道理应该使用的tradeid索引的。

 这个执行的步骤是：

• 第一步，根据id在tradelog表里找到id=2的这一行数据。
• 第二步，从id=2中取出tradeid字段的值。
• 第三步，根据tradeid值到trade_detail表中查找条件匹配的行，执行计划中第二行的KEY等于空表示的就是通过遍历主键索引的方式，一个一个地判断tradeid地值是否匹配。

所以很明显第三步没有达到我们地预期目的使用tradeid索引操作。那么究其原因，就是我们两个表地字符集不一样，tradelog为utf8mb4，而trade_detail表则是utf8字符集，因为utf8mb4是utf8字符集地超集，做自动类型转换时避免截取数据错误，按照“数据长度增加”方向进行转换地。

因此在处理时需要把trade_detail字段一个一个地转换为utf8mb4，再跟tradelog对应数据做比较，实际上这个语句等通过下面这个写法

select * from trade_detail where CONVERT(tradeid USING uft8mb4)=$tradelog.tradeid.value;

也就是说使用了转换函数，又是这个原因，对字段做函数操作，放弃走树搜索功能。

除了使用函数导致索引失效，用函数也是有方法的，上面的失效原因是因为trade_detail是utf8，默认的话肯定是要按utf8mb4转换，所以，trade_detail字段本身就要转换成utf8mb4，这样索引就会失效。（此处不懂可以往下看，下面的示例结合你慢慢品品就应该懂了）。

那假如下面这个SQL语句呢？查找 "trade_detail表里id=4的操作，对应操作者是谁？"看下SQL语句和执行计划：

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

我们发现全都使用了索引，这是怎么回事呢？

 我们执行这个语句是先到trade_detail里通过主键索引查询到一行id为4的数据，然后取出tradeid的值，然后拿这个值去tradelog中跟根据tradeid进行匹配，到这里也就是第三步，我们单独看第三步其实就是如下SQL语句。

select operator from trade_log where tradeid=$trade_detail.tradeid.value

而trade_detail字符集是utf8，要转换成utf8mb4，所以过程如下：

select operator from trade_log where tradeid=CONVERT($trade_detail.tradeid.value USING utf8mb4)

因为这里的CONVERT的函数时输入参数上的，所以可以使用tradeid索引，而如果查询条件里在字段上使用函数操作时则索引会失效，尽量优化到在函数结果上创建。

你可以多品品，在最后一步变为了trade_log表，这个表本身的数据集就是utf8mb4，所以它的条件本身不需要变动，而trade_detail表是utf8，需要变动，所以只需要在tradeid=（进行转换变动）这种情况下可以使用索引。

咱们了解以后，对下面这个语句做怎样的优化呢？

select d.* from tradelog l,trade_detail d where d.tradeid=l.tradeid and l.id=2

• 能够修改字段的字符集，那就可以使用这种，对于数据量大，或者业务暂时不允许改动字符集的话也就是DDL，那就只能采用修改SQL语句的方法了

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

  执行计划：我主动把l.tradeid转成了utf8，这样就避免了trade_detail的字符编码转换了。

• 第二个就是我们可以把trade_detail表上的字符集也改成utf8mb4，这样就没用字符集转换的问题了。

  alter table trade_detail modify tradeid varchar(32) CHARACTER set utf8mb4 DEFAULT null;

   执行计划：再执行下SQL，这样就没有字符集的问题了。

今天的内容就到这里了，拜拜！