MySQL深度解析---函数对性能的影响

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

一、条件字段函数操作

交易记录表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;

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

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

1.索引结构分析

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

• 如果你的SQL语句条件用的是where t_modified='2018-7-1’的话，引擎就会按照上面绿色箭头的路线，快速定位到 t_modified='2018-7-1’需要的结果。
  实际上，B+树提供的这个快速定位能力，来源于同一层兄弟节点的有序性。
• 如果计算month()函数的话，你会看到传入7的时候，在树的第一层就不知道该怎么办了。

2.解决方法

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

PS：优化器并不是完全放弃索引，只是放弃走索引高效的树搜索能力。
在放弃条件字段的索引后，优化器会选择一个遍历效率高的索引（索引空间小的）来进行全索引扫描。

所以尽量不要对带有索引的条件字段来使用函数，而是把SQL语句改成基于字段本身范围的查询：

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里的转换规则：
在MySQL中，字符串和数字做比较的话，是将字符串转换成数字。
CAST(VarChar AS signed int)

1.字段为VarChar&参数为int

有下面这样一条SQL

mysql> select * from tradelog where tradeid=110717;

交易编号tradeid这个字段上，本来就有索引，但是explain的结果却显示，这条语句需要走全表扫描。
原因是：tradeid的字段类型是varchar(32)，而输入的参数却是整型，所以需要做类型转换。
根据MySQL中的转换规则，对于优化器来说，这个语句相当于：

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

由于对查询条件执行了函数操作，所以索引失效。

2.字段为int&参数为VarChar

有下面这样一条SQL：

select * from tradelog where id="83126";

根据MySQL中的转换规则，对于优化器来说，这个语句相当于：

mysql> select * from tradelog where id = CAST("83126" AS signed int);

经测试，当字段不进行类型转换，只有参数进行类型转换时，仍然会走索引的树搜索方法。

三、隐式字符编码转换

MySQL中存在字符编码的概念，在同一个数据库的不同表可能会使用不同的字符编码集。
当不同字符编码的表进行连接时，往往会导致连接字段的索引失效。

MySQL中字符编码转换规则：
在MySQL里面，做自动类型转换的时候，为了避免数据在转换过程中由于截断导致数据错误，也都是“按数据长度增加的方向”进行转换的。
比如：字符集utf8mb4是utf8的超集，当这两个类型的字符串在做比较的时候，MySQL内部的操作是，先把utf8字符串转成utf8mb4字符集，再做比较。

1.问题产生

有下面这样一条SQL语句，假设tradelog 的字符编码为utf8mb4，trade_detail 的字符编码为 utf8（字符集utf8mb4是utf8的超集）

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

在连接过程中，先从一个表中取所有的连接字段，然后拿这些字段的值分别在另一个表的连接字段索引中查找符合的记录。在第二个表中寻找符合记录的过程，相当于以下SQL：

mysql> select * from trade_detail where tradeid=$L2.tradeid.value; 

由自动类型转换的原理可知，优化器看到的SQL是这样的：

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

第二个表中的连接字段被隐式的执行了类型转换函数，导致索引失效，进行了全表扫描。

2.解决方法

1. 统一字符集，这样就没有字符集转换的问题了

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

2. 修改SQL语句，显示的转换范围小的字符集（如果数据量比较大，或者业务上暂时不能做这个DDL可以使用）

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