SQL性能优化－upper函数
背景
最近对⽣产上最为耗时的⼏条sql语句进⾏分析优化，发现最为耗时的⼏条sql中都出现了upper函数，通过分析它们的执⾏计划初步估计是由于upper函数的使⽤导致的性能低下，所以选其中⼀句⽐较简单的sql进⾏了测试和分析。
sql语句如下：
SELECT CIFSTATE,CIFSEQ,CIFNAME,
EMAIL,MOBILEPHONE1,CIFNO
from
<_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，其主要主要字段如下：
sql优化的几种方式
测试表
其中id为主键，idno为⾝份证号，在idno上建⽴索引。
2.向表user中插⼊随机⽣成的500万条记录；
3.使⽤uppper()函数来执⾏查，sql如下：
select*fromuserwhereupper(idno)=upper(*******************')；执⾏结果如下:
upper
结果显⽰总共花费的时间为2.066秒。
4.不使⽤uppper()函数来执⾏查，sql如下：
select*fromuserwhereidno=*******************';
执⾏结果如下:
不使⽤upper
结果显⽰总共花费的时间为0.00042秒
结果分析：
结果对⽐
数据显⽰使⽤uppder()函数的耗时是不使⽤uppder()的4919倍，所以upper函数的使⽤会严重影响sql的执⾏效率。
优化⽅案
⽅案⼀
将现在数据库表中IDNO字段数据全部转为⼤写，然后执⾏sql之前，将参数转为⼤写，最后在sql中⽤“=”来执⾏查询。
⽅案⼆
数据库中数据维持现状，然后执⾏sql之前，将⾝份证号参数分别转为⼤⼩写两个参数，最后在sql中⽤“=”和“or”进⾏组合执⾏查询。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。