关于JDBC与MySQL临时表空间的深入解析

　　


　　

　　

临时表空间用来管理数据库排序操作以及用于存储临时表,中间排序结果等临时对象,相信大家在开发中经常会遇到相关的需求,下面本文将给大家详细JDBC与MySQL临时表空间的相关内容,分享出来供大家参考学习、下面话不多说了,来一起看看详细的介绍吧

　　

应用JDBC连接参数采用<代码> useCursorFetch=true> 　　 　　 　　innodb_temp_data_file_path=ibtmp1:12M: autoextend:马克斯:2 g 　　 　　


　　

　　

在限制了临时表空间后,当应用仍按以前的方式访问时,ibtmp1文件达到2 g后,程序一直等待直到超时断开连接。显示PROCESSLIST显示程序的连接线程为睡眠状态,状态和信息信息为空。这个对应用开发来说不太友好,程序等待超时之后要分析原因也缺少提示信息。

　　


　　

　　

为了分析问题,我们进行了以下测试
　　

　　

测试环境:
　　

　　

mysql: 5.7.16
　　

　　

java: 1.8 u162
　　

　　

jdbc驱动:5.1.36
　　

　　

操作系统:Red Hat 6.4

　　

<强> 1。手工模拟临时表超过最大限制的场景
　　

　　

模拟以下环境:
　　

　　

ibtmp1:12M: autoextend:马克斯:30 m
　　

　　500年

将一张万行的sbt表的k字段索引删除

　　

运行一条group by的查询,产生的临时表大小超过限制后,会直接报错:

　　
　　

选择和从sbtest1 group by k (k);
　　错误1114 (HY000):桌子上的/tmp/# sql_60f1_0充满

　　 　　

2。检查驱动对mysql的设置
　　

　　

我们上一步看到,sql手工执行会返回错误,但是jdbc不返回错误,导致连接一直睡眠,怀疑是mysql驱动做了特殊设置,驱动连接mysql,通过general_log查看做了哪些设置。未发现做特殊设置。

　　

3。测试JDBC连接
　　

　　

问题的背景中有对JDBC做特殊配置:useCursorFetch=true,不知道是否与隐藏报错有关,接下来进行测试:

　　

关于JDBC与MySQL临时表空间的深入解析

　　

<>强发现以下现象:

　　

·加参数useCursorFetch=true时,做同样的查询确实不会报错
　　

　　

这个参数是为了防止返回结果集过大而采用分段读取的方式,即程序下发一个sql给mysql后,会等mysql可以读结果的反馈,由于mysql在执行sql时,返回结果达到ibtmp上限后报错,但没有关闭该线程,该线程处理睡眠状态,程序得不到反馈,会一直等,没有报的错。如果杀死这个线程,程序则会报错。
　　

　　

·不加参数useCursorFetch=true时,做同样的查询则会报错

　　

关于JDBC与MySQL临时表空间的深入解析

　　

<强>结论
　　

　　

1。正常情况下,sql执行过程中临时表大小达到ibtmp上限后会报错;
　　

　　

2。当JDBC设置<代码> useCursorFetch=true> 　　


　　

　　

进一步了解到使用<代码> useCursorFetch=true> 　　

　　

但是使用<代码> useCursorFetch=true> 　　

　　

临时表空间过大的解决方案是限制ibtmp1的大小,然而<代码> useCursorFetch=true> 　　

　　

所以需要使用其它方法来达到相同的效果,且sql报错后程序也要相应的报的错。除了useCursorFetch=true这种段读取的方式外,还可以使用流读取的方式。流读取程序详见附件部分。

　　

<>强·报错对比
　　

　　

·段读取方式,sql报错后,程序不报错
　　

　　

·流读取方式,sql报错后,程序会报错

　　

<>强·内存占用对比
　　

　　

这里对比了普通读取,段读取,流读取三种方式,初始内存占用28米左右:
　　

　　

·普通读取后,内存占用100多
　　

　　

·段读取后,内存占用60米左右
　　

　　

·流读取后,内存占用60米左右

　　

关于JDBC与MySQL临时表空间的深入解析