MySQL复合索引和运算符BETWEEN [英] MySQL composite indexes and operator BETWEEN

问题描述

我对此查询有疑问：

SELECT * 
  FROM runs 
 WHERE (NOW() BETWEEN began_at 
                  AND finished_at)

你认为创建综合指数是否有意义对于begin_at和finished_at列？
或者仅为begin_at创建索引是有意义的吗？

Do you think it makes sense to create composite index for began_at and finished_at columns? Or it makes sense to create index only for began_at?

推荐答案

你的风格非常罕见。

大多数人可能写 WHERE beginning_at<现在（）和finished_at> NOW（）

但是。我建议在这两个字段上添加一个索引。

However. I would recommend putting an index on both fields.

组合密钥对你没有用，因为它只会加速搜索器的特定日期组合。

A combined key wont be of use to you because you it would only speed up searcher for specific date combinations.

这不完全正确，因为如果你使用betree，一个组合键会帮助你，但不会像你单独索引它们一样好。
如果使用等号（=）运算符搜索字段组合，则组合键非常好。单元字段索引在ragen请求中表现更好。

Well this is not entirely true because if you use betree a combined key will help you but not as good as if you index them seperately. Combined keys are very good if you search combinations of fields with equality (=) operator. SIngle field indexes perform better in ragen requests.

您可以谷歌搜索一下多维范围搜索。

You can google a bit for "multidimensional range search".

原因是一个字段中的所有匹配字段基本上可以在btree中的log（n）时间中找到。
所以你的整体运行时将是O（k * log（n）），即O（log（n））。

The reason is that all matching fields in one field can be basically found in log(n) time in btrees. So your overall runtime will be O(k*log(n)) which is O(log(n)).

多维范围查询具有运行时O（sqrt（n））的更高。然而，也有更好的实现，也是acheav对数运行时。
但是它们并没有在mysql中完全实现，所以根据版本它会更糟或更糟。

Multidimensional Range queries have a runtime of O(sqrt(n)) which is higher. However there are better implementations as well which also acheav logarithmic runtime. However they are not fully implemented in mysql, so it will be worse or awful depending on the version.

所以让我总结一下：

• 单个字段的等式比较：哈希索引（运行时O（1））

• Equality comparisions on single fields: hash index (runtime O(1))

单个字段的范围搜索：单个字段上的btree索引（O（log（n）））

Range search on single fields: btree index on single fields ( O(log(n)) )

多个字段的等式搜索：组合哈希key（运行时O（1））

Equality search on multiple fields: combined hash key (runtime O(1))

这些案例是明确的......

those cases are a clear thing...

• 多个字段的范围搜索：单独的btree索引（O（log（n）））

这是不太清楚的地方。由于上面给出的原因，对于当前版本，它明显更好地单独索引。
对于该用例的完美实现，您可以使用组合键获得更好的性能，但是没有系统知道它支持它。
mysql支持从5.0版开始的松散索引（你需要它），但只是非常有限，而查询优化器仅在极少数情况下才使用它们。不知道5.3或者更新的版本。

this is where its not so clear. with current versions its clearly better to index seperately because of the reasons given above. With a perfect implementation for that use case you could achieve better performance with combined keys but there is no system in know of which supports it. mysql supports loose indexes (which you need for that) since version 5.0, but only very limited and the query optimizer only utilizes them in rare cases afaik. don't know about newer versions like 5.3 or something.

然而，如果使用mysql实现松散索引，那么在你做范围请求或在不同方向排序的字段上组合键变得更多更相关。

however with mysql implementing loose indexes combined keys on fields where you do range requests or sorting in different directions become more and more relevant.

这篇关于MySQL复合索引和运算符BETWEEN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！