优化 Rails 数据库搜索
我正在构建一个 Rails 项目，并且我有一个包含一组表的数据库..每个表包含 500k 到 1M 行，并且我不断创建新行。
根据项目的性质，在每次创建之前，我必须在表中搜索重复项（对于一个字段），因此我不会两次创建同一行。不幸的是，随着我的桌子越来越大，这花费的时间越来越长。
我想我可以通过向我正在搜索的特定字符串字段添加索引来优化搜索。但我听说添加索引会增加创建时间。
所以我的问题如下： 查找和创建包含索引字段的行有何权衡？我知道向字段添加索引将使我的程序使用 Model.find_by_name 更快。但是它会使我的行创建速度慢多少？
I'm building a rails project, and I have a database with a set of tables.. each holding between 500k and 1M rows, and i am constantly creating new rows.
By the nature of the project, before each creation, I have to search through the table for duplicates (for one field), so i don't create the same row twice. Unfortunately, as my table is growing, this is taking longer and longer.
I was thinking that I could optimize the search by adding indexes to the specific String fields through which i am searching.. but I have heard that adding indexes increases the creation time.
So my question is as follows:
What is the trade off with finding and creating rows which contain fields that are indexed? I know adding indexes to the fields will cause my program to be faster with the Model.find_by_name.. but how much slower will it make my row creation?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
索引会减慢条目的插入速度，因为它需要将条目添加到索引中，并且需要一些资源，但一旦添加，它们就会加速您的选择查询，就像您所说的但是也许b树不是您的正确选择！因为 B 树索引了索引主题的前 X 个单元。当你有整数但文本搜索很棘手时，这很好。当您执行这样的查询时
，它会加快选择速度，但是当您使用这样的查询时：
它不会帮助您，因为您必须搜索可能超过数百个字符的整个字符串，然后拥有前 8 个单元并不是一个改进250 个字符长的字符串已建立索引！所以这是一件事。但还有另一个......
您应该添加一个唯一索引，因为数据库比 ruby 更能找到重复项！它针对排序进行了优化，并且绝对是解决此问题的更短、更干净的方法！当然，您还应该向相关模型添加验证，但这并不是让事情随数据库而行的原因。
// 关于索引速度
http://dev.mysql.com/ doc/refman/5.0/en/insert-speed.html
您没有大量选项。我认为当你只需要一个索引时，插入速度损失不会那么大！但选择速度会成比例增加！
Indexing slows down insertation of entries because its required to add the entry to the index and that needs some ressources but once added they speed up your select queries, thats like you said BUT maybe the b-tree isnt the right choice for you! Because the B-Tree indexes the first X units of the indexed subject. Thats great when you have integers but text search is tricky. When you do queries like
it will speed up selection but when you use queries like this:
it wont help you because you have to search the whole string which can be longer than some hundred chars and then its not an improvement to have the first 8 units of a 250 char long string indexed! So thats one thing. But theres another....
You should add a UNIQUE INDEX because the database is better in finding duplicates then ruby is! Its optimized for sorting and its definitifly the shorter and cleaner way to deal with this problem! Of cause you should also add a validation to the relevant model but thats not a reason to let things lide with the database.
// about index speed
http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html
You dont have a large set of options. I dont think the insert speed loss will be that great when you only need one index! But the select speed will increase propotionall!