mysql update upper_SQL性能优化－upper函数

背景

最近对生产上最为耗时的几条sql语句进行分析优化，发现最为耗时的几条sql中都出现了upper函数，通过分析它们的执行计划初步估计是由于upper函数的使用导致的性能低下，所以选其中一句比较简单的sql进行了测试和分析。

sql语句如下：

SELECT CIFSTATE,CIFSEQ,CIFNAME,

EMAIL,MOBILEPHONE1,CIFNO

from

PER.zx_pcif

where upper(IDNO)=upper(?)

这条sql的功能是从pcif表中通过IDNO身份证号来查询客户个人信息，整个sql比较简单，但是执行的平均耗时却达到了3.69秒， 其他耗时3.7秒和3.8秒的sql也是简单的select查询操作。

性能分析

初步分析

通过分析这些sql语句的执行计划，如下图所示：

执行计划

发现sql整个执行计划中，第一步通过索引来查询记录最为消耗性能，占比达到99%。而sql中是通过设置 where upper(IDNO)=upper(?) 来进行查找，所以初步推断是由于upper()函数的使用导致了性能的下降。为此搭建了测试环境对upper()函数性能进行了验证。

测试验证

实验环境：

系统：MacBook Pro 处理器：2.2GHZ Intel Core i7内存：16G

数据库：MySql 5.7.13 数据量：500万

测试流程：

1.建立表user，其主要主要字段如下：

测试表

其中id为主键，idno为身份证号，在idno上建立索引。

2.向表user中插入随机生成的500万条记录；

3.使用uppper()函数来执行查找，sql如下：

select * from user where upper(idno) = upper('12010419870329934X');

执行结果如下:

upper

结果显示总共花费的时间为2.066秒。

4.不使用uppper()函数来执行查找，sql如下：

select * from user where idno = '52262319560201341X';

执行结果如下:

不使用upper

结果显示总共花费的时间为0.00042秒

结果分析：

结果对比

数据显示使用uppder()函数的耗时是不使用uppder()的4919倍，所以upper函数的使用会严重影响sql的执行效率。

优化方案

方案一

将现在数据库表中IDNO字段数据全部转为大写，然后执行sql之前，将参数转为大写，最后在sql中用“=”来执行查询。

方案二

数据库中数据维持现状，然后执行sql之前，将身份证号参数分别转为大小写两个参数，最后在sql中用“=”和“or”进行组合执行查询。