如何处理耗时的SQL？

Question

我们有一个包含600万条记录的表，然后我们有一个需要大约7分钟来查询结果的SQL。我认为SQL不能再优化了。

查询时间导致我们的weblogic抛出最大阻塞线程异常。

对我处理这个问题有什么建议吗？

以下是查询，但我很难更改它，

SELECT * FROM  table1 
WHERE trim(StudentID) IN ('354354','0') 
AND concat(concat(substr(table1.LogDate,7,10),'/'),substr(table1.LogDate,1,5)) 
       BETWEEN '2009/02/02' AND '2009/03/02' 
AND TerminalType='1' 
AND RecStatus='0' ORDER BY StudentID, LogDate DESC, LogTime

然而，我知道使用字符串比较日期很耗时，但之前有人写道，我无法改变表结构……

LogDate被定义为一个字符串，格式是mm/dd/yyyy，所以我们需要子串并连接它，这样我们就可以在...还有..。我认为在这里很难进行优化。

Answer 1

这个查询很可能是在执行全文件扫描，因为您的条件不太可能利用任何索引。

LogDate是日期字段还是文本字段？如果它是一个日期字段，那么不要做substr's和concat's。只要说“'2009-02-02‘和'2009-02-03’之间的LOGDATE‘2009-02-03’或任何日期范围。如果它被定义为一个文本字段，你应该认真考虑将它重新定义为一个日期字段。(如果你的日期真的是文本，写成mm/dd/yyyy，那么你的ORDER BY ... LOGDATE DESC如果日期跨度超过一年，就不会给出有用的结果。)

是否有必要对StudentID进行修剪？在将数据放入数据库之前清理数据要好得多，而每次检索数据时都要试着清理数据。

如果LogDate被定义为日期，并且您可以在输入上修剪studentid，那么在一个或两个字段上创建索引，查询时间应该会显著减少。

或者，如果您想要一个快速而糟糕的解决方案，可以在"trim(studentid)“上创建一个索引。

如果这还不能解决问题，请给我们更多关于表布局和索引的信息。

Answer 2

SELECT * ... WHERE trim(StudentID) IN ('354354','0')

如果这是正常构造，那么您需要一个function based index。因为如果没有它，就会强制DB服务器执行全表扫描。

根据经验，您应该尽可能避免在WHERE子句中使用函数。trim(StundentID)、substr(table1.LogDate,7,10)阻止DB服务器使用任何索引或对查询应用任何优化。尝试尽可能多地使用原生数据类型，例如，对于LogDate，使用DATE而不是VARCHAR。还应在客户端软件中正确管理StudentID，例如，在INSERT/UPDATE之前修剪数据。

Answer 3

如果您的数据库支持它，您可能希望尝试materialized view。

如果不是这样，可能值得考虑自己实现一些类似的东西，让一个计划好的作业运行一个查询，这个查询执行昂贵的修剪和连接，并用结果刷新表，这样您就可以对更好的表运行查询，而避免昂贵的东西。或者使用触发器来维护这样的表。