java - Hibernate custom dialect for DATE function 加速查询

Question

我的应用程序的数据库有很多记录(2 百万)，我有几个查询使用 DATE() 函数在日期字段上使用 WHERE 条件。示例:

SELECT * from table WHERE DATE(column1)>=DATE(:parameter1) and DATE(column1)<=DATE(:parameter2)

这些查询花费的时间太多:大约 150/170 毫秒。经过一些分析后，我发现问题出在 DATE() 函数上。将查询转换为:

SELECT * from table WHERE column1>=:parameter1 and column1<=:parameter2

其中parameter1为00:00:00时间的日期，parameter2为23:59:59时间的日期，查询仅需1/2ms。

所以我应该翻译我的应用程序中的所有 HQL 查询，但我想尝试使用 Hibernate 方言中的自定义函数来完成这项工作。

例如，创建一个 DATE_START() 函数在查询中放入时间为 00:00:00 的日期和 DATE_END() 函数在查询中放入时间为 23:59:59 的日期。

对于以“最佳实践”方式执行此操作，您有什么好的建议吗？

---

需要一个 sql 解决方案，因为在没有 Java 实现的情况下，它在带有 @Query 注释的 Spring 服务的情况下也更好:

@Query(value = "SELECT * from table WHERE DATE(column1)>=DATE(?1) and DATE(column1)<=DATE(?2)")
 public List<MyObject> findByDate(Date date1,Date date2);

在这些情况下，我不能使用 Java 解决方案。

Answer 1

我建议您使用下面的 Java 代码创建一个方法，并在您想要为查询定义初始和最终时间的任何时间重复使用:

    Calendar initialCalendar = GregorianCalendar.getInstance();
    initialCalendar.setTime(dataInicial);
    initialCalendar.set(Calendar.HOUR_OF_DAY, 0);
    initialCalendar.set(Calendar.MINUTE, 0);
    initialCalendar.set(Calendar.SECOND, 0);
    initialCalendar.set(Calendar.MILLISECOND, 0);

    Calendar finalCalendar = GregorianCalendar.getInstance();
    finalCalendar.setTime(dataFinal);
    finalCalendar.set(Calendar.HOUR_OF_DAY, 23);
    finalCalendar.set(Calendar.MINUTE, 59);
    finalCalendar.set(Calendar.SECOND, 59);
    finalCalendar.set(Calendar.MILLISECOND, 999);

    Timestamp inicialDate = new Timestamp(initialCalendar.getTimeInMillis());
    Timestamp finalDate = new Timestamp(finalCalendar.getTimeInMillis());