通过更改评估顺序来减少SQL查询的执行时间

问题描述：

我正在研究需要运行SQL查询以查看某些位置是否属于由多边形定义的辖区的应用程序。这些司法管辖区每季度更新一次，这意味着我也需要根据我检查的日期来检查它们。我使用mySQL函数MBRContains来帮助缩小我正在查看的辖区的范围，使我的应用程序更容易检查给定的点是否落入其中的任何一个。现在，我的疑问是这样的：通过更改评估顺序来减少SQL查询的执行时间

SELECT DISTINCT t0.id FROM jurisdiction t0 
WHERE t0.beginDate <= '2017-08-05' AND t0.endDate >= '2017-08-05' 
AND MBRContains(t0.geometry,GeomFromText("POINT(48.0 -120.0)"));

我相当肯定MBRContains是一个更昂贵的操作比检查日期，但我还没有与SQL多少工作，我不能确定如何使因此，管辖权将首先按日期过滤，然后针对MBRContains进行检查。我怎样才能做到这一点？在使用此查询获得相同结果时，我可以做其他优化吗？

不确定它是否是一个错字，但这些都是不好的日期。对于一个日期字段应该是'DATE'数据类型;换另一种方法，即使你坚持使用字符串值，字符串也需要进行格式化，使得这些比较实际上是有意义的（'06-08-2001'的结束日期'05-08-2017'之后，类似地'04 -08-2020'的开始日期'05-08-2017'之前） – Uueerdo

@Uererdo日期和点是例子。我已经检查过它传递的是正确的日期，并且正确地过滤了结果，只是查询时间过长。 – user3726962

如果您在beginDate，endDate和geometry上使用索引，它将加速您的访问。 – hackela

答

我会在（beginDate，endDate）上添加一个索引。一个索引，在两个字段上，不是单独的索引。

此外，如果提供的日期始终是单一日期，则将条件的日期部分更改为'2017-08-05' BETWEEN t0.beginDate AND t0.endDate可能会进一步提供帮助（但可能不会）。

另外，在过去有类似的问题，我建议使用附加条件，使用更简单的边界检查，也可以从索引中获益;但那些通常涉及可以使用较大边界框的情况。伪条件就像“在边界框和边界区域中的位置”; “在边界框中”使用索引消除了远点，减少了更复杂的“边界区域”检查的点数。

答

如果MBRContains是GeomFromText是确定性的功能，而不是被标记为这样的，那么这样做可能会有所帮助。

确定性函数总是返回相同的输入值。 UPPER（）是确定性的，因为它始终为相同的输入提供相同的输出。这意味着如果优化器知道中间值永远不会改变，则优化器可以创建快捷方式。

更多关于确定功能：

（现在我搜索周围，我看到这些功能作为数据库的一部分提供，而不是那些你所以你不能控制它们。但是，我将这里的答案作为一个例子来说明如何加快调用函数的SQL。）

编号'DATE（col）'是确定性的，但优化器将不会为'WHERE DATE（col）= CURDATE（）'使用'INDEX（col）'。也没有任何其他捷径。它踢。（好吧，它只会评估“CURDATE”一次。） –

答

检查一个范围就像你正在做的事情一样，不能很好地进行优化。你能得到的最好的是扫描一半的桌子。这是有问题的。

实际上，你被困在扫描。那么WHERE什么部分首先评估的问题就会变得很小。这是因为取出一行比起WHERE条款中的几乎所有函数要昂贵得多。

您是否试过geometry的SPATIAL索引？可能显着帮助。如果是这样，这个答案的其余部分是没有意义的。

前面已经提到的，“边界框”是一个很好的尝试：

WHERE x BETWEEN ... 
    AND y BETWEEN ... 

INDEX(x), 
INDEX(y)

（没有，INDEX(x,y)不起作用任何更好）

如果你谈论的是一个巨大的数据集，那么你可能需要一个more complex solution。

通过更改评估顺序来减少SQL查询的执行时间

相关推荐