13.为什么有些sql语句逻辑相同，效率相差巨大？2022-03-01

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

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

     //建表语句
     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;

//sql语句
mysql> select count(*) from tradelog where month(t_modified)=7;

对于 select * from tradelog where id + 1 = 10000 这个语句，这个加1的操作并不会改变有序性，所以在写SQL的时候，手动改成where id = 10000 -1才可以。

案例二：隐式类型转换

mysql> select * from tradelog where tradeid=110717;

mysql的转化规则，在Mysql中，字符串和数字做比较的话，是将字符串转化为数字。
所以上述sql对于优化器来说，相当于

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

因为有函数操作，所以会放弃树搜索。

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

两个字符集不同的表做关联字段查询时不能用索引。
两个表的字符集不同，一个是 utf8mb4，一个是utf8 ，所以做表连接查询的时候用不上关联字段的索引。
*原本的语句：注：tradelog字符集为utf8mb4，trade_detail 字符集为utf8 **
在这个执行计划里，是从 tradelog 表中取 tradeid 字段，再去 trade_detail 表里查询匹配字段。因此，我们把 tradelog 称为驱动表，把 trade_detail 称为被驱动表，把 tradeid 称为关联字段。

 mysql> select d.* from tradelog l, trade_detail d where 
 d.tradeid=l.tradeid and l.id=2; /*语句Q1*/
 <==>等价于
select * from trade_detail  where CONVERT(traideid USING 
utf8mb4)=$L2.tradeid.value;

1.优化方案

比较常见的优化方法是，把 trade_detail 表上的 tradeid 字段的字符集也改成 utf8mb4，这样就没有字符集转换的问题了。

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

2.优化方案二

如果能够修改字段的字符集的话，是最好不过了。但如果数据量比较大，或者业务上暂时不能做这个 DDL 的话，那就只能采用修改 SQL 语句的方法了。

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