背景
h2 数据是个短小精悍的嵌入式数据库,纯 Java 实现,且非常小。
我们有一个比较底层的应用中就是用了 h2 数据库来存储应用的基础信息,这个数据库说起来比较容易。
本文总结实际项目中涉及到的 h2 的相关技术及问题。
控制台工具用法
网络策略比较严格的环境下,h2 没有开启对外的浏览器访问工具时,怎么连接 h2 数据库进行数据操作呢?
h2 数据库提供了命令行工具类 org.h2.tools.Shell
,可以用它连接数据库进行操作,使用方法为:
java -cp h2*.jar org.h2.tools.Shell
命令输出要求你输入连接 需要的配置信息:
Welcome to H2 Shell xxx (xxx)
Exit with Ctrl+C
[Enter] jdbc:h2:tcp://XXX:xxx///xxx/dt
URL
[Enter] org.h2.Driver
Driver org.h2.Driver
[Enter] sa
User
[Enter] Hide
Password
按要求输入h2连接目标数据库的信息后,就可以操作数据库了。
未授权漏洞封堵
h2 数据库的的 console 浏览器访问工具,它有两种比较危险的未授权漏洞:
- 默认创建不存在的数据库
- Preferences 未授权问题
H2 Database Console未授权访问
H2 Database Console未授权访问,默认情况下自动创建不存在的数据库,从而导致未授权访问。启动参数添加 -ifExists
,它的含义:
[-ifExists] Only existing databases may be opened (all servers)
应用出厂时先创建好数据库文件后,修改启动脚本,添加该参数:
dir=$(dirname "$0")
nohup java -cp "$dir/h2-2.x.xx.jar:$H2DRIVERS:$CLASSPATH" org.h2.tools.Server -tcpAllowOthers -webAllowOthers -tcpPort -ifExists "$@" &
这样启动 h2 后首次访问时会因为 test 数据库不存在而无法连接:
只有输入正确的出厂数据库路径、帐号和密码,才能连接到数据库操作页面。
Preferences 未授权问题
上面只能封堵针对数据库操作的未授权访问,未登录时 Preferences 这个操作页面的 “shutdown” 按钮可以直接将 h2 服务停止,比前面的未授权更严重。
解决办法是升级到 2.x 版本,它自带了控制台管理员密码 webAdminPassord
配置,必须输入密码才能进入可选项配置页面。
数据导入导出工具
在有些情况下需要用到数据库的导入导出文件,比如应用老版本的数据库 A 和新版本的数据库 B 直接升级补丁语句跨度过多,升级操作比数据迁移更复杂时,对于数据库中表结构一致的表,可以使用 h2 的导入工具「INSERT INTO xxx SELECT * FROM CSVREAD
」 和导出工具「call CSVWRITE
」来完成。
第一步,从旧数据库中整理需要导出的表,然后使用导出工具编写导出脚本:
call CSVWRITE ('/mydata/table_a.csv', 'SELECT * FROM table_a');
call CSVWRITE ('/mydata/table_b.csv', 'SELECT * FROM table_b');
call CSVWRITE ('/mydata/table_c.csv', 'SELECT * FROM table_c');
第二步,连接旧数据库,执行导出脚本,注意导出脚本执行后会导出到客户端所在的机器上。比如,用浏览器连接,就在本机;用工具连接,就在目标服务器上。
第三步,连接新数据库,使用导入工具编辑导入脚本:
INSERT INTO table_a SELECT * FROM CSVREAD('/mydata/table_a.csv');
INSERT INTO table_b SELECT * FROM CSVREAD('/mydata/table_b.csv');
INSERT INTO table_c SELECT * FROM CSVREAD('/mydata/table_c.csv');
commit;
第四步,执行导入脚本,就能直接完成数据迁移了。
数据库文件备份
h2 数据库是基于文件的数据库,目标数据库就一以数据库名称命名的 .mv.db
文件。
生产环境下可以定期对该文件进行备份,当应用出现异常或需要迁移数据库时,直接拷贝数据库文件,相当方便。
缺点及适用场景
h2 数据库只适用于数据量比较小、且不需要一次全量查询的业务场景。
如果一个表有超过1万条数据,而且需要全量加载到内存中时,JDBC 查询操作可能会出现超时异常:「Statement was canceled or the session timed out
」。
什么是Statement Timeout?
statement timeout用来限制statement的执行时长,timeout的值通过调用JDBC的java.sql.Statement.setQueryTimeout(int timeout) API进行设置。不过现在开发者已经很少直接在代码中设置,而多是通过框架来进行设置。
原生的 JDBC Statement
类提供了超时时间设置方法 setQueryTimeout
,一万条数据 h2 数据库查询耗时40秒,设置1分钟就可以解决这个异常了。
改为用原生 JDBC 查询,先查询总数,再以总数创建 List,后查询列表添加到 List 中:
// TODO 先查询总数
String countSql = "SELECT count(*) FROM xx WHERE R_ID=?";
if (totalCount == 0) {return Collections.emptyList();}// TODO 查询记录列表
data = new ArrayList<>((int) totalCount);
String sql = "SELECT a,b from xx R_ID=?";stmt = conn.prepareStatement(sql);// 设置连接查询的超时时间,解决过滤规则过大时、规则查询 java.sql.SQLException: Statement was canceled or the session timed out; SQL statement:
stmt.setQueryTimeout(100);
stmt.setObject(1, id);
rs = stmt.executeQuery();// TODO 处理数据
结论:查询语句的超时时间是关键因素,配置 fetchSize
对超时没有影响,但是它影响一次加载的数据量,配置的话可以降低内存、但是增加了查询时间。
与 SQLite 对比
想到之前有一个简单的应用监控程序,直接用了 SQLite 数据库。那么,h2 Database 和 SQLite都是开源的嵌入式文件数据库,它俩有什么区别呢?
特点 | h2 Database | SQLite |
---|---|---|
开发语言 | Java | C |
运行模式 | 嵌入式模式、服务器模式、混合模式 | 嵌入式模式 |
连接方式 | 嵌入式:JDBC ;服务器模式:JDBC、ODBC、TCP/IP ;混合模式:前面两者之和 | 与开发语言一致,支持 JDBC、C++、Python、Perl、PHP |
存储方式 | 内存存储:应用退出数据消失;文件存储:持久化到磁盘 | 文件存储:持久化到磁盘 |
SQL支持情况 | 支持SQL92标准的绝大对数功能另,可兼容大多数主流数据库:MySQL/Postgre/Oracle/DB2 | 支持SQL92标准的大多数功能无兼容性扩展 |
事务 | 支持一般事务、支持MVCC | 支持一般事务 |
数据库锁 | 共享锁/排它锁 | 共享锁/排它锁 |
CPU和内存 | 以插入100W数据为例,CPU平均占用60%,且波动频率较大,内存占用随着数据存储数量呈线性增长 | 以插入100W数据为例,CPU平均占用45%,且波动平缓,内存占用随着数据存储数量呈线性增长。 |
性能 | 单连接:随数据量读写时间呈线性增长;多连接:随数据量读写时间呈线性增长 | 单连接:读数据时间不随数据量增长;写数据时间随数据量增长多连接:性能较差 |
启示录
可能是我们的项目比较简单,用到的语法也比较简单,没有涉及到特别高级的用法,比如事务、多连接之类的。
总结一下,作为网络笔记吧,省的下次排查问题又需要翻找了!