mysql - 索引的速度是否大于 MySQL 中的比较？

Question

我的数据库中有一个事件表，其中包括 start_date 和 end_date 列。

我经常运行类似的查询

where start_date > 'some starting date' and end_date < 'some end date'

向 start_date 和 end_date 列添加索引会受益吗？我发现这不是 = 比较，但也许无论如何。

Answer 1

MySQL 优化器将在它认为合适的地方使用索引:

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators.

...

Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.)

来源:Comparison of B-Tree and Hash Indexes

您可能会发现这些很有趣:

How MySQL Uses Indexes

和this answer和 this answer至 Why does MySQL not use an index for a greater than comparison? .