Java数据库编程实战：JDBC、连接池、事务与SQL优化

我的Java数据库实战笔记最近后台有不少朋友私信我问的都是同一个问题Java后端到底要怎么系统地学数据库从基础的JDBC操作到连接池怎么配再到分库分表和读写分离中间隔着一大段模糊地带。我自己从刚开始写DriverManager.getConnection()都会报错到后来能独立压测出连接池的最佳参数中间踩了不少坑也总结了不少经验。这篇就把整个Java数据库编程的脉络整理出来围绕面试题里最高频的几个点把原理、代码、踩坑和调优经验一次说透。不管你是在校生准备秋招还是已经工作一两年的后端开发想补基础这篇文章都能帮你把脑子里那些零散的知识点串成一条线。文章不涉及某个特定框架的教程式罗列而是从底层JDBC开始一层层往上走最终让你看到一个Java后端工程师面对数据库问题时完整的思考框架。1. 驱动加载与第一个JDBC连接的坑1.1 从Class.forName到DriverManager的演变很多初学者搞不清楚为什么要写Class.forName(com.mysql.cj.jdbc.Driver)。我第一次学的时候也很懵这句话看起来什么都没做去掉它程序也能跑啊。先说结论Class.forName()的作用是触发类的静态初始化块。早期的JDBC驱动需要在类加载时主动向DriverManager注册自己你手动执行Class.forName()就是把这个注册动作提前触发。后来JDBC 4.0引入了SPI机制驱动jar包里的META-INF/services/java.sql.Driver文件会让DriverManager在初始化时自动加载驱动类所以MySQL驱动从5.x版本开始不写Class.forName()也能正常工作。但为什么要懂这个因为面试官可能会问你SPI机制是什么也可能让你排查明明引入了驱动jar包为什么报No suitable driver这类问题。另外在自定义类加载器场景下SPI自动加载可能会失效这时候手动Class.forName()反而更可靠。我就在一个使用热部署插件的项目里遇到过这个问题最后就是靠手动加载驱动解决的。1.2 连接URL和参数的细节MySQL的连接URL有非常多的参数可以配置很多人只写jdbc:mysql://localhost:3306/test就完了但生产环境这么写大概率会出问题。一个完整的URL长这样jdbc:mysql://127.0.0.1:3306/test_db?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/ShanghairewriteBatchedStatementstrueallowMultiQueriestrue逐个说说关键参数serverTimezone从MySQL Connector/J 8.x开始如果服务器时区和客户端不一致连接时会直接报Server returns invalid timezone的错误。国内服务器一般都要设成Asia/Shanghai。rewriteBatchedStatements这个参数默认是false。如果不开addBatch()提交的批量SQL会被驱动拆成单条逐条执行性能没有任何提升。开了之后驱动会把多条INSERT自动拼成INSERT INTO ... VALUES (...), (...), (...)这样的复合语句。我之前测试过同样的批量插入开与不开能差出3到5倍的时间。useSSL本地开发设false没有安全问题生产环境如果有专门的数据库安全链路则另说。不显式设置的话新版驱动会打一堆警告日志非常烦人。1.3 手写一个最小可用的JDBC工具类不管之后用什么ORM框架JDBC的基础不能丢。下面这个是我早期项目里用的工具类后来也一直是面试时候手写代码的底稿public class JdbcUtil { private static final String URL jdbc:mysql://127.0.0.1:3306/test_db?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/Shanghai; private static final String USER root; private static final String PASSWORD your_password; static { try { Class.forName(com.mysql.cj.jdbc.Driver); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public static Connection getConnection() throws SQLException { return DriverManager.getConnection(URL, USER, PASSWORD); } public static void close(ResultSet rs, Statement stmt, Connection conn) { if (rs ! null) { try { rs.close(); } catch (SQLException e) { /* ignore */ } } if (stmt ! null) { try { stmt.close(); } catch (SQLException e) { /* ignore */ } } if (conn ! null) { try { conn.close(); } catch (SQLException e) { /* ignore */ } } } }注意几个细节关闭资源的顺序是ResultSet→Statement→Connection反了会出问题。资源关闭要放在finally里或者使用Java 7的try-with-resources语法否则遇到异常时连接会泄漏。Connection接口的close()方法语义上其实是归还如果底层是连接池它不会真的断开网络连接只是把连接标记为空闲。这套代码虽然粗糙但它清晰展示了JDBC编程的完整生命周期加载驱动、获取连接、创建Statement、执行SQL、遍历结果集、关闭资源。理解了这几步后面用任何ORM都只是在这个生命周期外面包了一层壳。2. 连接池的使用逻辑为什么HikariCP就是比Druid快一点2.1 数据库连接的昂贵之处一次数据库连接涉及的完整链路是TCP三次握手 → MySQL权限校验用户名密码 客户端地址→ 获取连接资源 → 初始化会话上下文字符集、事务隔离级别、autocommit配置等。这一套下来一般耗时在几十到几百毫秒。如果你的业务接口本身只执行了5毫秒的SQL但连接创建花掉100毫秒性能直接崩掉。连接池的本质就是连接复用把建立好的连接保存在池子里谁用谁取用完归还避免重复握手和认证。2.2 HikariCP为什么快关于连接池选型国内Druid用的人确实多因为它自带监控面板和SQL防注入功能对中文社区也友好。但从纯性能指标来看HikariCP是Spring Boot 2.x之后官方的默认推荐它的设计理念是极简到极致。HikariCP的快来自几个具体设计字节码精简除了依赖SLF4J没有任何多余的第三方库。代码量少方法调用链短。并发集合优化自己实现了一个ConcurrentBag替代了常规的LinkedBlockingQueue。这个集合的读取没有加锁只有在连接不够且需要创建时才走同步路径所以取连接的速度很快。FastList替换ArrayListJDBC中每次Connection.close()都要把Statement从集合里移除ArrayList的remove是O(n)的FastList改成逆序删除把O(n)降到了O(1)虽然是常数级优化但在高频场景下还是有意义。2.3 参数调优的经验值连接池的配置不是越大越好。很多人以为maximumPoolSize200很猛实际上这是灾难。每个连接在MySQL端都是一个OS线程200个连接意味着200个线程随时待命光上下文切换就能把CPU拖垮。我一般参考PostgreSQL的推荐公式思路是通用的连接数 ((核心数 * 2) 有效磁盘数)一个4核8线程的普通单机应用连接池设置成10左右就差不多了。如果SQL都是秒级以内的快查询10 ~ 20完全够用。即使你的应用是核心支付系统Tomcat的线程数可能才200但数据库连接池core size我很少见超过50的。一个合理的HikariCP配置spring: datasource: hikari: pool-name: MyHikariPool minimum-idle: 5 maximum-pool-size: 20 idle-timeout: 600000 max-lifetime: 1800000 connection-timeout: 30000 connection-test-query: SELECT 1调参的经验是minimumIdle不要等于maximumPoolSize否则连接不够时无法动态扩容。maxLifetime必须小于数据库服务器端的wait_timeout避免连接被数据库主动断开后客户端还不知道。connectionTimeout设30秒超过这个时间直接给业务报错不让请求无限排队等连接。2.4 连接池泄漏排查实录这里分享一个真实的排错经历。一个线上服务运行几天后某接口响应越来越慢最后完全卡死重启后又能撑几天周而复始。一开始我怀疑是不是内存泄漏抓了堆dump也没发现明显异常。后来把HikariCP的监控打开发现活跃连接数在持续上升直到顶满20。这说明是业务代码拿连接之后没有归还。查日志终于在一个老模块里发现有个分支路径异常抛出后没有进finally块执行connection.close()。这个问题的教训是连接池没有真正的调用连接自动回收机制漏一条就少一条池子总有一天被捞干。彻底修复后我在所有获取连接的代码上写了统一规范要么用try-with-resources要么在finally里关闭没有第三条路。3. 事务边界与并发问题Spring管理事务的正确姿势3.1 Transactional的失效场景这是Java面试题里出现率极高的一题也是实际开发中最容易埋雷的地方。我统计了一下遇到的失效场景主要有下面这几种方法非publicSpring的声明式事务基于CGLIB或JDK动态代理实现代理只拦截public方法。private、protected、package方法上是不会织入事务通知的。同类内部调用同一个类里methodA()调methodB()Transactional加在methodB()上不生效。因为Spring注入的是代理对象走内部this.methodB()时没有经过代理类事务自然不会被开启。异常被吞事务方法里catch住了异常没往外抛。Spring默认只在抛出RuntimeException或Error时才回滚自定义异常如果不指定rollbackFor事务一样不生效。数据库引擎不支持事务MySQL的MyISAM引擎是不支持事务的换InnoDB后才能正常工作。这个看起来很基础但真有人在这上面卡一整天。正确打开方式是这个写法Transactional(rollbackFor Exception.class) public void transfer(String fromAccount, String toAccount, BigDecimal amount) { // 扣款... // 加款... }rollbackForException.class保证所有异常都触发回滚而不是只默认回滚运行时异常。3.2 隔离级别和传播行为怎么选隔离级别面试必考实际用起来很多人却是一知半解。MySQL的RR可重复读是默认隔离级别InnoDB通过MVCC解决了部分幻读问题。理解这个知识点的关键是记住数据隔离级别脏读不可重复读幻读读未提交 READ_UNCOMMITTED可能可能可能读已提交 READ_COMMITTED不会可能可能可重复读 REPEATABLE_READ不会不会可能InnoDB部分解决串行化 SERIALIZABLE不会不会不会传播行为里最常用的是REQUIRED如果当前有事务就加入没有就新建。但真实的业务里一个方法可能同时被同步接口和MQ异步消息调用这时候REQUIRES_NEW和REQUIRED的选择就要想清楚。我自己踩过的坑是大事务拆小事务。如果一个事务里处理了几千条数据还调用了外部HTTP接口整个事务的持锁时间会非常长很容易死锁。正确做法是只让真正需要原子性的写操作进入事务大循环和远程调用全部放在事务外面。3.3 分布式事务的思路微服务架构下一个用户注册可能涉及用户服务、积分服务、消息服务三张表跨库跨服务本地事务管不了。分布式事务的常用方案2PC两阶段提交XA协议强一致性但性能差协调者本身是单点。用Atomikos或Seata AT模式的时候要小心业务量一大会有很大的性能损耗。TCC补偿事务Try-Confirm-Cancel三段式。Try阶段冻结资源Confirm阶段确认Cancel阶段释放。适合资金类高一致性场景但业务侵入性很强每段都要写补偿逻辑。本地消息表 最终一致性大厂用的最多的方案。业务操作和消息写入本地同一个事务然后由任务轮询把消息发给MQ消费方处理完成后再回调确认。最终一致性的窗口期通常是秒级到分钟级。我刚入行的时候觉得分布式事务离自己很远后来做分层订单系统才发现本地事务MQ消息表定时任务对账这套组合已经足够覆盖绝大多数业务场景远比动不动就上Seata要稳妥。4. 从JDBC到MyBatis再到JPAORM框架的取舍与底层逻辑4.1 为什么说MyBatis是国内Java的主流选择MyBatis在国内的流行程度远高于国外这背后有几层原因。一方面历史原因。早期国内项目大量使用iBatis程序员习惯了手写SQL的掌控感对Hibernate这种全自动ORM反而有畏惧感——Hibernate的HQL一旦性能不好你根本不知道底层生成了什么SQL只能靠show_sql慢慢分析。另一方面业务特征决定。国内互联网业务的数据模型非常复杂动不动就是几十个表的关联查询、多条件动态过滤、按需排序分页。MyBatis的where、foreach标签可以灵活拼SQL这种半自动的定位恰好匹配了国内业务的复杂度。4.2 MyBatis批量操作的正确姿势MyBatis的批量插入如果直接写一个foreach标签把5000条数据一次性拼成一条巨型INSERT容易超过max_allowed_packet限制默认4MB你插入的文本一多很容易爆掉。最佳实践是使用ExecutorType.BATCH或者用分批拼接。给你一个分批插入的示例public void batchInsert(ListUser userList) { SqlSession sqlSession sqlSessionFactory.openSession(ExecutorType.BATCH, false); UserMapper userMapper sqlSession.getMapper(UserMapper.class); try { for (int i 0; i userList.size(); i) { userMapper.insert(userList.get(i)); if (i % 500 0) { sqlSession.flushStatements(); // 每500条刷一次 } } sqlSession.commit(); } catch (Exception e) { sqlSession.rollback(); throw e; } finally { sqlSession.close(); } }注意批量模式必须手动commit否则全部回滚flushStatements的时机选500是生产验证过比较稳妥的分批大小。4.3 JPA、Hibernate最容易翻车的N1查询Spring Data JPA用起来确实爽简单CRUD几乎零SQL。但在关联查询上非常容易掉进N1陷阱。所谓N1就是先查1条主记录然后为了获取关联对象又发了N条查询结果产生几十上百条SQL。一个典型场景是部门和员工的一对多关系Entity public class Department { OneToMany(mappedBy department) private ListEmployee employees; }当你遍历所有部门、读取employees的时候Hibernate会给每个部门各发一条查员工的SQL。部门有100个就会多出100条查询总耗时自然膨胀得非常难看。解决方式有几种把OneToMany的fetch改成FetchType.LAZY按需加载。用EntityGraph指定join fetch让Hibernate生成一条join查询。或者直接使用自定义JPQL里加join fetch。如果逻辑太复杂直接供MyBatis手写SQL反而更清晰。我的原则是简单查询、单表操作用JPA涉及多表动态查询、复杂报表的用MyBatis。在同一个项目里两者可以共存不要被框架绑定。4.4 一二级缓存到底开不开MyBatis一级缓存默认开启作用域是SqlSession也就是同一个会话里相同SQL只查一次。二级缓存默认关闭因为分布式的多节点环境下节点间缓存不一致的问题非常难处理。我给出的建议是生产环境直接关闭二级缓存。数据库本来就是用来保障数据一致性的你在中间加一层本地缓存一旦数据更新没及时清掉缓存线上出问题的概率比查询性能提升带来的收益大得多。真要加速查询用Redis做缓存或者上后端的查询专用缓存组件数据一致的代价更可控。5. SQL性能分析与索引调优慢查询到底慢在哪5.1 打开慢查询日志线上SQL性能问题第一步不是看代码而是先看慢查询日志。MySQL的配置slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ONlong_query_time1表示执行时间超过1秒的SQL都会被记录log_queries_not_using_indexes会把没走索引的查询也记录下来。很多DBA会直接开这个参数因为没走索引的SQL比慢SQL更有排查价值。定位到慢SQL之后用EXPLAIN查看执行计划重点关注下面几列列名关键判断type从const到range再到ALLALL就是全表扫描必须优化key实际用到的索引为NULL说明没走索引rows预估扫描行数这个数要尽量小Extra出现Using filesort或Using temporary说明排序/分组没走索引值得警惕5.2 索引失效的经典场景这块是面试必问也是实际开发中优化SQL首先要排查的。对索引列使用函数WHERE DATE(create_time) 2024-01-01会让create_time索引失效。正确写法是WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00。隐式类型转换手机号字段是varchar查询用WHERE phone 13800138000数字类型隐式转字符串后索引失效。改成字符串常量即可。模糊查询左通配LIKE %关键字无法利用索引LIKE 关键字%则能走前缀索引。OR连接非索引列WHERE name 张三 OR age 25如果name有索引而age没有整个查询可能走全表扫描。改为UNION或两个查询合并。最讽刺的是有时候你给字段建了索引但由于数据量太小优化器觉得全表扫描比走索引更快也不走索引。这时候不用慌强制索引FORCE INDEX(idx_name)可以解决但更好的做法是实测判断优化器的选择是否正确。5.3 分页查询的性能陷阱经典的LIMIT 100000, 20写法MySQL会先扫描前100020条扔掉前100000条再返回20条。数据量一大这就是纯耗时操作。优化方案有两种第一种延迟关联SELECT a.* FROM orders a INNER JOIN ( SELECT id FROM orders ORDER BY create_time DESC LIMIT 100000, 20 ) b ON a.id b.id;子查询只查主键索引覆盖扫描很快然后再按主键回表查数据。实测在百万级数据下这个方法可以把分页从几百毫秒降到几十毫秒。第二种游标式分页SELECT * FROM orders WHERE create_time 2024-01-01 00:00:00 ORDER BY create_time DESC LIMIT 20;下次查询带上上一次的最后一条记录的create_time作为条件这样不会走深偏移适合App信息流这种不停往下翻的场景。6. Java与数据库交互的隐藏细节时区、注入与批量优化6.1 时区问题引发的时间错乱MySQL连接串里忘记配置serverTimezoneAsia/Shanghai时驱动会使用JVM默认时区去解释数据库返回的DATETIME值。如果你的Docker容器和数据库服务器时区不一致从库里查出来的时间可能整体差8个小时。这个问题的排查链路非常绕。当时线上有用户反馈订单创建时间不对我看了前端代码、后端日志、数据库记录三个地方都对不上。最后定位到是容器时区是UTC应用JVM默认时区跟着容器走而数据库里存的是东八区时间驱动一转换全错了。解决方案连接串显式加serverTimezone同时容器环境变量设置TZAsia/Shanghai。两个都做才彻底解决。6.2 SQL注入的最后一层防线关于SQL注入最核心的原则只有一条永远不要拼接SQL字符串。用PreparedStatement的参数占位符?让SQL结构先定死参数值由驱动转义并传给数据库从根本上杜绝注入。但即便是MyBatis有一个地方仍然存在风险${}和#{}的区别。${}是直接拼接参数内容#{}是预编译占位符。按惯例排序字段、动态表名这类无法用占位符的必须维护白名单。比如排序字段只能在sortedBy的set集合里选不允许用户直接传任意字段名。如果用户传了id; DROP TABLE users;--这样的值被${}拼进去就是事故。6.3 批量插入的实测对比我有一次优化数据同步任务一口气从文件导入十万条记录到MySQL。刚开始直接逐条INSERT花了两分多钟。改用MyBatis批量模式之后时间缩到40秒。再把连接串加上rewriteBatchedStatementstrue直接降到8秒。这组数据能很直观地体现每个优化层级的价值方式十万条耗时逐条INSERT约130秒MyBatis ExecutorType.BATCH约40秒BATCH rewriteBatchedStatementstrue约8秒最后一层的原理就是前面提到的把批量的INSERT语句重写成多VALUES语句一次网络RPC把数据发过去这个传输时间省掉的不是一点点。6.4 连接池与数据库的事务冲突这个场景特别容易被忽略一个HTTP请求里你用连接池拿了一个连接开启事务然后调用一个需要新连接的方法去查询。如果连接池最大连接数是20而Tomcat最大线程数是200当200个请求同时进来其中有180个请求卡在等待获取新连接就非常容易把连接池占满其他请求全部都拿不到连接造成服务整体假死。解决思路是事务方法里不要拿新连接所有数据库操作必须使用同一个事务连接。Spring里就是Transactional注解保证方法内所有MyBatis操作用同一个连接。本质上这是线程与连接一对一绑定的问题理解这个机制后你就能明白为什么有些团队会把连接池最大连接数设置成与Tomcat最大线程数相等就是为了避免这种等待链路的出现。7. 我的个人体会做了这么多年的Java开发一个很深的体会是数据库编程从来不是会写代码就行的事它是代码、配置、SQL、运维的结合体。你觉得你写好了Transactional其实还可能栽在连接池参数上你优化了SQL却可能被服务器时区坑一把。所以我现在接手任何项目一定会先花时间把数据库连接串、连接池参数、慢查询配置、事务边界这四个点全部梳理一遍再去看业务代码。这套基本功扎实了后面面对任何ORM框架或者新的数据库中间件学习成本都会降到很低。JDBC和事务这些底层知识是不会过时的值得反复嚼透。