贵阳网站搜索优化,招聘网站维护什么内容,开通微信公众号要钱吗,软文广告发布平台引言
在数据库开发中#xff0c;PreparedStatement#xff08;预处理语句#xff09;是防止SQL注入、提升性能的重要工具。它通过分离SQL结构与参数值#xff0c;不仅增强了安全性#xff0c;还能利用预编译优化执行效率。本文将从Java JDBC驱动和MySQL 8源码的双重视角PreparedStatement预处理语句是防止SQL注入、提升性能的重要工具。它通过分离SQL结构与参数值不仅增强了安全性还能利用预编译优化执行效率。本文将从Java JDBC驱动和MySQL 8源码的双重视角揭示PreparedStatement参数绑定与执行的底层原理帮助开发者更好地理解其工作机制。 一、Java端的PreparedStatement创建
1.1 JDBC驱动的prepareStatement方法
当Java应用调用Connection.prepareStatement(sql)时JDBC驱动以MySQL Connector/J为例根据配置决定使用服务器端预处理还是客户端模拟预处理 // 示例MySQL Connector/J中的prepareStatement实现
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) {if (useServerPrepStmts canServerPrepare(sql)) {return new ServerPreparedStatement(connection, sql); // 服务器端预处理} else {return new ClientPreparedStatement(connection, sql); // 客户端模拟}
}
服务器端预处理ServerPreparedStatement 向MySQL发送COM_STMT_PREPARE命令触发SQL解析与预处理适用于高频重复查询。客户端模拟ClientPreparedStatement 直接将参数拼接到SQL中以普通查询执行适用于低频或复杂语句。
1.2 关键配置参数
useServerPrepStmtstrue启用服务器端预处理。cachePrepStmtstrue缓存预处理语句避免重复解析。 二、MySQL服务器的预处理阶段
2.1 处理COM_STMT_PREPARE命令
当JDBC驱动发送COM_STMT_PREPAREMySQL调用mysqld_stmt_prepare函数 // MySQL源码sql/sql_prepare.cc
void mysqld_stmt_prepare(THD *thd, const char *query, uint length) {Prepared_statement *stmt new Prepared_statement(thd);stmt-prepare(query, length); // 解析SQL并记录占位符thd-session-push_prepared_stmt(stmt); // 缓存预处理语句send_statement_id(thd, stmt-id); // 返回statement_id给客户端
}
2.2 解析SQL与占位符记录
在Prepared_statement::prepare()中
解析SQL生成语法树识别?占位符。为每个?创建Item_param对象记录参数位置和类型。存储参数数量m_param_count和元数据。 // 示例Item_param对象存储参数信息
class Item_param {enum_field_types param_type; // 参数类型如MYSQL_TYPE_STRINGString str_value; // 字符串类型值longlong int_value; // 整型值
}; 三、参数绑定的底层实现
3.1 处理COM_STMT_EXECUTE命令
当Java调用PreparedStatement.execute()时JDBC驱动发送COM_STMT_EXECUTE命令携带statement_id和参数值 // MySQL源码sql/sql_prepare.cc
void mysqld_stmt_execute(THD *thd, Prepared_statement *stmt) {stmt-set_parameters(thd, parameters); // 绑定参数到Item_paramstmt-execute_loop(thd); // 执行预处理语句
}
3.2 参数绑定过程
二进制协议解析从网络包中读取参数值。类型检查与转换确保客户端参数类型与Item_param定义一致。值存储将参数值写入对应的Item_param对象。 // 示例设置参数值
void Item_param::set_param_value(THD *thd, String *value) {if (param_type MYSQL_TYPE_STRING) {str_value.copy(value-ptr(), value-length(), charset);} else if (param_type MYSQL_TYPE_LONG) {int_value parse_int(value);}
} 四、语句执行与结果返回
4.1 执行预处理语句
MySQL调用Prepared_statement::execute_loop()
替换占位符使用Item_param中的值生成完整执行计划。查询优化可能使用二级引擎如HeatWave加速。结果返回通过二进制协议高效传输结果集。
4.2 性能优化点
缓存执行计划避免重复优化相同语句。二进制协议减少网络开销直接传输二进制数据。 五、Java与MySQL的协作全貌
初始化阶段 Java调用prepareStatement() → MySQL解析SQL返回statement_id。执行阶段 Java传递参数 → MySQL绑定参数并执行 → 返回结果集。资源清理 关闭PreparedStatement时发送COM_STMT_CLOSE释放资源。 六、最佳实践与注意事项
优先使用服务器端预处理提升性能并防止SQL注入。合理设置缓存大小平衡内存与性能。避免频繁开关连接复用预处理语句减少解析开销。监控预处理语句缓存命中率优化prepStmtCacheSize参数。 结语
通过深入Java与MySQL源码我们揭示了PreparedStatement从参数绑定到执行的全链路过程。这种底层视角不仅帮助开发者优化数据库操作还能更好地应对高并发场景下的性能挑战。正确使用预处理语句是编写高效、安全数据库应用的关键一步。 ##java调用栈 ##java堆栈
prepareStatement:1583, ConnectionImpl (com.mysql.cj.jdbc)
prepareStatement:1563, ConnectionImpl (com.mysql.cj.jdbc)
prepareStatement:369, DruidPooledConnection (com.alibaba.druid.pool)
invoke0:-1, NativeMethodAccessorImpl (jdk.internal.reflect)
invoke:77, NativeMethodAccessorImpl (jdk.internal.reflect)
invoke:43, DelegatingMethodAccessorImpl (jdk.internal.reflect)
invoke:568, Method (java.lang.reflect)
invoke:55, ConnectionLogger (org.apache.ibatis.logging.jdbc)
prepareStatement:-1, $Proxy83 (jdk.proxy3)
instantiateStatement:86, PreparedStatementHandler (org.apache.ibatis.executor.statement)
prepare:88, BaseStatementHandler (org.apache.ibatis.executor.statement)
prepare:59, RoutingStatementHandler (org.apache.ibatis.executor.statement)
prepareStatement:87, SimpleExecutor (org.apache.ibatis.executor)
doQuery:62, SimpleExecutor (org.apache.ibatis.executor)
queryFromDatabase:325, BaseExecutor (org.apache.ibatis.executor)
query:156, BaseExecutor (org.apache.ibatis.executor)
query:109, CachingExecutor (org.apache.ibatis.executor)
query:89, CachingExecutor (org.apache.ibatis.executor)
selectList:151, DefaultSqlSession (org.apache.ibatis.session.defaults)
selectList:145, DefaultSqlSession (org.apache.ibatis.session.defaults)
selectList:140, DefaultSqlSession (org.apache.ibatis.session.defaults)
invoke0:-1, NativeMethodAccessorImpl (jdk.internal.reflect)
invoke:77, NativeMethodAccessorImpl (jdk.internal.reflect)
invoke:43, DelegatingMethodAccessorImpl (jdk.internal.reflect)
invoke:568, Method (java.lang.reflect)
invoke:427, SqlSessionTemplate$SqlSessionInterceptor (org.mybatis.spring)
selectList:-1, $Proxy57 (jdk.proxy2)
selectList:224, SqlSessionTemplate (org.mybatis.spring)
executeForMany:166, MybatisMapperMethod (com.baomidou.mybatisplus.core.override)
execute:77, MybatisMapperMethod (com.baomidou.mybatisplus.core.override)
invoke:148, MybatisMapperProxy$PlainMethodInvoker (com.baomidou.mybatisplus.core.override)
invoke:89, MybatisMapperProxy (com.baomidou.mybatisplus.core.override)
selectList:-1, $Proxy72 (jdk.proxy2)
selectOne:172, BaseMapper (com.baomidou.mybatisplus.core.mapper)
invokeSpecialIFC:-1, LambdaForm$DMH/0x00000281814b8000 (java.lang.invoke)
invoke:-1, LambdaForm$MH/0x000002818127b000 (java.lang.invoke)
invoke:-1, LambdaForm$MH/0x00000281814b8800 (java.lang.invoke)
invokeExact_MT:-1, LambdaForm$MH/0x00000281814b8c00 (java.lang.invoke)
invokeWithArguments:732, MethodHandle (java.lang.invoke)
invoke:162, MybatisMapperProxy$DefaultMethodInvoker (com.baomidou.mybatisplus.core.override)
invoke:89, MybatisMapperProxy (com.baomidou.mybatisplus.core.override)
selectOne:-1, $Proxy72 (jdk.proxy2)
getOne:202, ServiceImpl (com.baomidou.mybatisplus.extension.service.impl)
getOne:320, IService (com.baomidou.mybatisplus.extension.service)
invoke:-1, IService$$FastClassBySpringCGLIB$$f8525d18 (com.baomidou.mybatisplus.extension.service)
invoke:218, MethodProxy (org.springframework.cglib.proxy)
invokeMethod:386, CglibAopProxy (org.springframework.aop.framework)
access$000:85, CglibAopProxy (org.springframework.aop.framework)
intercept:704, CglibAopProxy$DynamicAdvisedInterceptor (org.springframework.aop.framework)
getOne:-1, SqlVerifyServiceImpl$$EnhancerBySpringCGLIB$$372ce03a (com.jf.equipment.service.impl)
getSqlVerify:24, SqlVerifyController (com.jf.equipment.controller)
invoke0:-1, NativeMethodAccessorImpl (jdk.internal.reflect)
invoke:77, NativeMethodAccessorImpl (jdk.internal.reflect)
invoke:43, DelegatingMethodAccessorImpl (jdk.internal.reflect)
invoke:568, Method (java.lang.reflect)
doInvoke:205, InvocableHandlerMethod (org.springframework.web.method.support)
invokeForRequest:150, InvocableHandlerMethod (org.springframework.web.method.support)
invokeAndHandle:117, ServletInvocableHandlerMethod (org.springframework.web.servlet.mvc.method.annotation)
invokeHandlerMethod:895, RequestMappingHandlerAdapter (org.springframework.web.servlet.mvc.method.annotation)
handleInternal:808, RequestMappingHandlerAdapter (org.springframework.web.servlet.mvc.method.annotation)
handle:87, AbstractHandlerMethodAdapter (org.springframework.web.servlet.mvc.method)
doDispatch:1067, DispatcherServlet (org.springframework.web.servlet)
doService:963, DispatcherServlet (org.springframework.web.servlet)
processRequest:1006, FrameworkServlet (org.springframework.web.servlet)
doGet:898, FrameworkServlet (org.springframework.web.servlet)
service:655, HttpServlet (javax.servlet.http)
service:883, FrameworkServlet (org.springframework.web.servlet)
service:764, HttpServlet (javax.servlet.http)
internalDoFilter:227, ApplicationFilterChain (org.apache.catalina.core)
doFilter:162, ApplicationFilterChain (org.apache.catalina.core)
doFilter:53, WsFilter (org.apache.tomcat.websocket.server)
internalDoFilter:189, ApplicationFilterChain (org.apache.catalina.core)
doFilter:162, ApplicationFilterChain (org.apache.catalina.core)
doFilter:20, CorsFilter (com.jf.equipment.filter)
internalDoFilter:189, ApplicationFilterChain (org.apache.catalina.core)
doFilter:162, ApplicationFilterChain (org.apache.catalina.core)
doFilterInternal:100, RequestContextFilter (org.springframework.web.filter)
doFilter:117, OncePerRequestFilter (org.springframework.web.filter)
internalDoFilter:189, ApplicationFilterChain (org.apache.catalina.core)
doFilter:162, ApplicationFilterChain (org.apache.catalina.core)
doFilterInternal:93, FormContentFilter (org.springframework.web.filter)
doFilter:117, OncePerRequestFilter (org.springframework.web.filter)
internalDoFilter:189, ApplicationFilterChain (org.apache.catalina.core)
doFilter:162, ApplicationFilterChain (org.apache.catalina.core)
doFilterInternal:201, CharacterEncodingFilter (org.springframework.web.filter)
doFilter:117, OncePerRequestFilter (org.springframework.web.filter)
internalDoFilter:189, ApplicationFilterChain (org.apache.catalina.core)
doFilter:162, ApplicationFilterChain (org.apache.catalina.core)
invoke:197, StandardWrapperValve (org.apache.catalina.core)
invoke:97, StandardContextValve (org.apache.catalina.core)
invoke:541, AuthenticatorBase (org.apache.catalina.authenticator)
invoke:135, StandardHostValve (org.apache.catalina.core)
invoke:92, ErrorReportValve (org.apache.catalina.valves)
invoke:78, StandardEngineValve (org.apache.catalina.core)
service:360, CoyoteAdapter (org.apache.catalina.connector)
service:399, Http11Processor (org.apache.coyote.http11)
process:65, AbstractProcessorLight (org.apache.coyote)
process:890, AbstractProtocol$ConnectionHandler (org.apache.coyote)
doRun:1743, NioEndpoint$SocketProcessor (org.apache.tomcat.util.net)
run:49, SocketProcessorBase (org.apache.tomcat.util.net)
runWorker:1191, ThreadPoolExecutor (org.apache.tomcat.util.threads)
run:659, ThreadPoolExecutor$Worker (org.apache.tomcat.util.threads)
run:61, TaskThread$WrappingRunnable (org.apache.tomcat.util.threads)
run:842, Thread (java.lang)
##mysql8源码 mysqld_stmt_prepare方法gdb堆栈
(gdb) bt
#0 mysqld_stmt_prepare (thd0x730d04166ce0, query0x730d04007781 SELECT id,name,age FROM t \n \n WHERE (name ?), length48, stmt0x730d04006880)at /home/yym/mysql8/mysql-8.1.0/sql/sql_prepare.cc:1550
#1 0x000060a668f30cf0 in dispatch_command (thd0x730d04166ce0, com_data0x730df85fe340, commandCOM_STMT_PREPARE) at /home/yym/mysql8/mysql-8.1.0/sql/sql_parse.cc:2035
#2 0x000060a668f2ef77 in do_command (thd0x730d04166ce0) at /home/yym/mysql8/mysql-8.1.0/sql/sql_parse.cc:1459
#3 0x000060a669186835 in handle_connection (arg0x60a68e4b5420) at /home/yym/mysql8/mysql-8.1.0/sql/conn_handler/connection_handler_per_thread.cc:303
#4 0x000060a66b0c5bdc in pfs_spawn_thread (arg0x60a68e246910) at /home/yym/mysql8/mysql-8.1.0/storage/perfschema/pfs.cc:3043
#5 0x0000730e08094ac3 in start_thread (argoptimized out) at ./nptl/pthread_create.c:442
#6 0x0000730e08126850 in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
##mysql8源码 mysqld_stmt_execute 方法gdb堆栈
(gdb) p stmt-m_query_string.str
$1 0x730d04bac5c8 SELECT id,name,age FROM t \n \n WHERE (name ?)
(gdb) bt
#0 mysqld_stmt_execute (thd0x730d04166ce0, stmt0x730d04006880, has_new_typestrue, execute_flags8, parameters0x730d04b6b9a0)at /home/yym/mysql8/mysql-8.1.0/sql/sql_prepare.cc:1888
#1 0x000060a668f309ab in dispatch_command (thd0x730d04166ce0, com_data0x730df85fe340, commandCOM_STMT_EXECUTE) at /home/yym/mysql8/mysql-8.1.0/sql/sql_parse.cc:1993
#2 0x000060a668f2ef77 in do_command (thd0x730d04166ce0) at /home/yym/mysql8/mysql-8.1.0/sql/sql_parse.cc:1459
#3 0x000060a669186835 in handle_connection (arg0x60a68e4b5420) at /home/yym/mysql8/mysql-8.1.0/sql/conn_handler/connection_handler_per_thread.cc:303
#4 0x000060a66b0c5bdc in pfs_spawn_thread (arg0x60a68e246910) at /home/yym/mysql8/mysql-8.1.0/storage/perfschema/pfs.cc:3043
#5 0x0000730e08094ac3 in start_thread (argoptimized out) at ./nptl/pthread_create.c:442
#6 0x0000730e08126850 in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
文章转载自： http://www.morning.frxsl.cn.gov.cn.frxsl.cn http://www.morning.nyqb.cn.gov.cn.nyqb.cn http://www.morning.zxqyd.cn.gov.cn.zxqyd.cn http://www.morning.ptqbt.cn.gov.cn.ptqbt.cn http://www.morning.jsdntd.com.gov.cn.jsdntd.com http://www.morning.lnbyk.cn.gov.cn.lnbyk.cn http://www.morning.jkfyt.cn.gov.cn.jkfyt.cn http://www.morning.rcdmp.cn.gov.cn.rcdmp.cn http://www.morning.mfnjk.cn.gov.cn.mfnjk.cn http://www.morning.bxfy.cn.gov.cn.bxfy.cn http://www.morning.jyznn.cn.gov.cn.jyznn.cn http://www.morning.xsfny.cn.gov.cn.xsfny.cn http://www.morning.dhyqg.cn.gov.cn.dhyqg.cn http://www.morning.lhyhx.cn.gov.cn.lhyhx.cn http://www.morning.ztmkg.cn.gov.cn.ztmkg.cn http://www.morning.ghxzd.cn.gov.cn.ghxzd.cn http://www.morning.c7627.cn.gov.cn.c7627.cn http://www.morning.dwxqf.cn.gov.cn.dwxqf.cn http://www.morning.hclplus.com.gov.cn.hclplus.com http://www.morning.qieistand.com.gov.cn.qieistand.com http://www.morning.bpkqd.cn.gov.cn.bpkqd.cn http://www.morning.zdkzj.cn.gov.cn.zdkzj.cn http://www.morning.jbshh.cn.gov.cn.jbshh.cn http://www.morning.bnpn.cn.gov.cn.bnpn.cn http://www.morning.trrd.cn.gov.cn.trrd.cn http://www.morning.njntp.cn.gov.cn.njntp.cn http://www.morning.zdnrb.cn.gov.cn.zdnrb.cn http://www.morning.pxwjp.cn.gov.cn.pxwjp.cn http://www.morning.qttg.cn.gov.cn.qttg.cn http://www.morning.trfh.cn.gov.cn.trfh.cn http://www.morning.gnbtp.cn.gov.cn.gnbtp.cn http://www.morning.hqbnx.cn.gov.cn.hqbnx.cn http://www.morning.gjmll.cn.gov.cn.gjmll.cn http://www.morning.lzqdd.cn.gov.cn.lzqdd.cn http://www.morning.kmrgl.cn.gov.cn.kmrgl.cn http://www.morning.vaqmq.cn.gov.cn.vaqmq.cn http://www.morning.czqqy.cn.gov.cn.czqqy.cn http://www.morning.qncqd.cn.gov.cn.qncqd.cn http://www.morning.bmpjp.cn.gov.cn.bmpjp.cn http://www.morning.dqkrf.cn.gov.cn.dqkrf.cn http://www.morning.drwpn.cn.gov.cn.drwpn.cn http://www.morning.gbsfs.com.gov.cn.gbsfs.com http://www.morning.mhwtq.cn.gov.cn.mhwtq.cn http://www.morning.snnb.cn.gov.cn.snnb.cn http://www.morning.nhzzn.cn.gov.cn.nhzzn.cn http://www.morning.wqbfd.cn.gov.cn.wqbfd.cn http://www.morning.dnpft.cn.gov.cn.dnpft.cn http://www.morning.dtlqc.cn.gov.cn.dtlqc.cn http://www.morning.zxwqt.cn.gov.cn.zxwqt.cn http://www.morning.rmxwm.cn.gov.cn.rmxwm.cn http://www.morning.fdrwk.cn.gov.cn.fdrwk.cn http://www.morning.zylrk.cn.gov.cn.zylrk.cn http://www.morning.oumong.com.gov.cn.oumong.com http://www.morning.dfbeer.com.gov.cn.dfbeer.com http://www.morning.xwlmr.cn.gov.cn.xwlmr.cn http://www.morning.lwhsp.cn.gov.cn.lwhsp.cn http://www.morning.knscf.cn.gov.cn.knscf.cn http://www.morning.ctfh.cn.gov.cn.ctfh.cn http://www.morning.fnhxp.cn.gov.cn.fnhxp.cn http://www.morning.lqgfm.cn.gov.cn.lqgfm.cn http://www.morning.fmqw.cn.gov.cn.fmqw.cn http://www.morning.wptdg.cn.gov.cn.wptdg.cn http://www.morning.knlgk.cn.gov.cn.knlgk.cn http://www.morning.kycwt.cn.gov.cn.kycwt.cn http://www.morning.jfwrf.cn.gov.cn.jfwrf.cn http://www.morning.ynbyk.cn.gov.cn.ynbyk.cn http://www.morning.jynzb.cn.gov.cn.jynzb.cn http://www.morning.lqlfj.cn.gov.cn.lqlfj.cn http://www.morning.kcnjz.cn.gov.cn.kcnjz.cn http://www.morning.swdnr.cn.gov.cn.swdnr.cn http://www.morning.nkcfh.cn.gov.cn.nkcfh.cn http://www.morning.rqfkh.cn.gov.cn.rqfkh.cn http://www.morning.pjyrl.cn.gov.cn.pjyrl.cn http://www.morning.tqjwx.cn.gov.cn.tqjwx.cn http://www.morning.nylbb.cn.gov.cn.nylbb.cn http://www.morning.rfqk.cn.gov.cn.rfqk.cn http://www.morning.lwxsy.cn.gov.cn.lwxsy.cn http://www.morning.dzgmj.cn.gov.cn.dzgmj.cn http://www.morning.mzcrs.cn.gov.cn.mzcrs.cn http://www.morning.skpdg.cn.gov.cn.skpdg.cn
