18 | 为啥SQL逻辑相同，性能差异大

第一种：隐式类型转换

1. 如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是 1；
2. 如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是 0。

情况一：

此时cg_bidid的类型是bigint 在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。

EXPLAIN SELECT * FROM cg_bid WHERE id ="104";
EXPLAIN SELECT * FROM cg_bid WHERE id =104;

这两句都一样的性能，所以说where的值是整型，右侧的值无论是字符串还是整型，不会出现隐式类型转换。

情况二：

此时cg_bid的id 的类型为varchar

where条件的值是字符串类型，右侧的值是整型，会进行类型转换，会进行全盘扫描，无法使用索引。

EXPLAIN SELECT * FROM cg_bid WHERE id =104;

explain结果：

看到这里全盘扫描了（rows），而且key为NULL，证明没有走索引。

第一种：条件字段函数操作

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;

这种不会走索引

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

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

果你的 SQL 语句条件用的是 where t_modified='2018-7-1’的话，引擎就会按照上面绿色箭头的路线，快速定位到 t_modified='2018-7-1’需要的结果。 为了能够用上索引的快速定位能力，我们就要把 SQL 语句改成基于字段本身的范围查询。按照下面这个写法，优化器就能按照我们预期的，用上 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');

不过优化器在个问题上确实有“偷懒”行为，即使是对于不改变有序性的函数，也不会考虑使用索引。比如，对于 select * from tradelog where id + 1 = 10000 这个 SQL 语句，这个加 1 操作并不会改变有序性，但是 MySQL 优化器还是不能用 id 索引快速定位到 9999 这一行。所以，需要你在写 SQL 语句的时候，手动改写成 where id = 10000 -1 才可以。

第三种：隐式字符编码转换

连表，字符集不一样也会不走索引。

像这种：

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

CONVERT() 函数，在这里的意思是把输入的字符串转成 utf8mb4 字符集。 连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。

假如表的数据较大，这种情况可以使用以下的方法优化：移到右侧

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

课程后面看到有一个有意思的问答：

老师，有道面试题困扰了很久，求指教！题目是这样的，a表有100条记录，b表有10000条记录，两张表做关联查询时，是将a表放前面效率高，还是b表放前面效率高？网上各种答案，但感觉都没有十分的说服力，期待老师的指点！ 作者回复: (这题目改成100万禾10000万比较好) 如果是考察语句写法，这两个表谁放前面都一样，优化器会调整顺序选择合适的驱动表；

老师答案：

如果是考察优化器怎么实现的，你可以这么想，每次在树搜索里面做一次查找都是log(n), 所以对比的是100log(10000)和 10000log(100)哪个小，显然是前者，所以结论应该是让小表驱动大表。

总结：

索引字段不能进行函数操作，但是索引字段的参数可以玩函数。

 SELECT 2=1,"2"=1,1=1,"1"=1,"a"=1,"b">-1;

有点类似于true or false ，字符串能转换为整型就会转换，如何进行比较，如果不能转换，则把字符串当成0处理。