Ruby on Rails、ActiveRecord、二分搜索

发布于 2024-07-20 06:12:12 字数 223 浏览 8 评论 0原文

如果我有下表。

create_table :my_table, :id => false do |t|
   t.string :key_column
   t.string :value_column
end

我如何确保行以最佳方式存储以通过 :key 字段进行二分搜索？

我如何确保使用二分搜索？

原文

If I had the following table.

create_table :my_table, :id => false do |t|
   t.string :key_column
   t.string :value_column
end

How would I ensure that the rows are optimaly stored for binary search by the field of :key?

And how would I make sure binary search is used?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

那一片橙海， 2024-07-27 06:12:12

对于任何感兴趣的行数，通过键访问单个随机记录的最佳方法（对于“最佳”的大多数定义）是创建索引。

CREATE INDEX my_index ON my_table ( key_column );

或者在 ActiveRecord 迁移中：

add_index(:my_table, :key_column)

数据库索引通常使用二分搜索，使用 B 树或类似，它在存储成本和检索和更新时间之间提供了良好的平衡。

对于单表操作，确保使用索引应该相对简单：

MyTable.find_by_key_column('ABC123')

例如，应该生成类似这样的内容（检查development.log）：

SELECT * FROM my_table WHERE (key_column = 'ABC123')

即使是 MySQL 相对不起眼的优化器也应该在最佳运行时没有问题。

行存储不应该成为单个行检索的问题，这是幸运的，因为无论如何您都无法控制它。对于 MySQL 性能，您可能应该选择 MyISAM 而不是 InnoDB 作为存储引擎，前提是您对“最佳”的定义不包括“最可靠”。

For any interesting number of rows, the optimal way (for most definitions of "optimal") to access a single random record by key is to create an index.

CREATE INDEX my_index ON my_table ( key_column );

or in an ActiveRecord migration:

add_index(:my_table, :key_column)

Database indices typically use binary search, using B-trees or similar, which offers a good balance between storage costs and time for retrieval and update.

Ensuring the index is used should be relatively straightforward for single-table operations:

MyTable.find_by_key_column('ABC123')

for example, should generate something like this (check development.log):

SELECT * FROM my_table WHERE (key_column = 'ABC123')

which even MySQL's relatively unimpressive optimiser should have no problem running optimally.

Row storage should not be a concern for individual row retrieval, which is fortunate as there isn't much you can do to control it anyway. For MySQL performance you should probably choose MyISAM over InnoDB as the storage engine, provided your definition of "optimal" doesn't include "most reliable".

回复收藏 0 原文