背景
因为公司业务查询订单表的数据太慢,需要把查询订单的功能迁移到查询es上,方式是通过自己写代码的方式,通过订单数据为主表,依次查询其余六张表的数据,把结果取出来组装为es中的一份数据模板。其中就设计到大量的mysql数据库查询,所以做了一下对比,目的是找出查询数据库方便的同时更快的方式:
实验
查询数据库: crm_order_payment
数据库数据条数: select count(1) from crm_order_payment; -- 163902
查询条件不含有索引: show index from crm_order_payment;
@Test
public void testOnceJdbcAndMybatis() throws InterruptedException {
String paymentSql = String.format("select order_id as id from crm_order_payment where update_time >= '%s' and update_time < '%s' limit %s, %s",
"2019-01-01 00:00:00", "2021-07-19 00:00:00", "0", "1000");
long start = System.currentTimeMillis();
List<Map<String, Object>> maps = jdbcTemplate.queryForList(paymentSql);
// 模拟自己组装对象耗时,其实要不到1s
// TimeUnit.SECONDS.sleep(1);
for (Map<String, Object> map : maps) {
changeOrderToEsData(map);
}
long end = System.currentTimeMillis();
long start1 = System.currentTimeMillis();
QueryWrapper<CrmOrderPayment> queryWrapper = Wrappers.query();
queryWrapper.ge("update_time", "2019-01-01 00:00:00").lt("update_time", "2021-07-19 00:00:00");
IPage page = new Page(1, 1000);
IPage page1 = iCrmOrderPaymentService.page(page, queryWrapper);
long end1 = System.currentTimeMillis();
System.out.println("---> mybatis 耗时:" + (end1 - start1));
System.out.println("---> jdbc 耗时:" + (end - start));
log.warn("jdbc 耗时:" + (end - start));
}
单次查询数据:(ms)
mybatis : 432 397 379 412 400
jdbcTemplate: 164 167 163 174 178
多次执行查询:
@Test
public void testMultipleJdbcAndMybatis() throws InterruptedException {
String paymentSql = String.format("select order_id as id from crm_order_payment where update_time >= '%s' and update_time < '%s' limit %s, %s",
"2019-01-01 00:00:00", "2021-07-19 00:00:00", "0", "1000");
long start = System.currentTimeMillis();
for (int i = 0; i< 100; i++) {
List<Map<String, Object>> maps = jdbcTemplate.queryForList(paymentSql);
for (Map<String, Object> map : maps) {
// 模拟自己组装对象耗时,其实要不到1s
changeOrderToEsData(map);
}
}
long end = System.currentTimeMillis();
long start1 = System.currentTimeMillis();
for (int i = 0; i< 100; i++) {
QueryWrapper<CrmOrderPayment> queryWrapper = Wrappers.query();
queryWrapper.ge("update_time", "2019-01-01 00:00:00").lt("update_time", "2021-07-19 00:00:00");
IPage page = new Page(1, 1000);
IPage page1 = iCrmOrderPaymentService.page(page, queryWrapper);
}
long end1 = System.currentTimeMillis();
System.out.println("---> mybatis 耗时:" + (end1 - start1));
System.out.println("---> jdbc 耗时:" + (end - start));
}
实验数据 ms:
mybatis: 10161 11168 10854 9977 10077
jdbcTemplate : 1071 1038 833 811 806
结论
jdbcTemplate 查询比mybatis plus 快,性能上更好,其他方便不做比较