像 SO 这样的站点的数据访问策略 - 排序的 SQL 查询和影响排序的同步更新？
我正在开发一个 Grails Web 应用程序，该应用程序的访问模式与 StackOverflow 或 MyLifeIsAverage 类似 - 用户可以对条目进行投票，他们的投票用于根据投票数对条目列表进行排序。可以在执行排序选择查询时进行投票。
由于选择将锁定表的很大一部分，因此正常的事务锁定似乎会导致更新永远进行（给定足够的流量）。有没有人开发过具有此类数据访问模式的应用程序，如果是，您是否找到了一种方法来允许这些更新和选择或多或少同时发生？有谁知道像SO这样的网站如何处理这个问题？
我的想法是使排序选择脏读，因为如果它们不始终完全最新，这是可以接受的。这是我可能提高这些选择和更新性能的唯一想法，但我认为有人可能知道更好的方法。
I'm working on a Grails web app that would be similar in access patterns to StackOverflow or MyLifeIsAverage - users can vote on entries, and their votes are used to sort a list of entries based on the number of votes. Votes can be placed while the sorted select queries are being performed.
Since the selects would lock a large portion of the table, it seems that normal transaction locking would cause updates to take forever (given enough traffic). Has anyone worked on an app with a data access pattern such as this, and if so, did you find a way to allow these updates and selects to happen more or less concurrently? Does anyone know how sites like SO approach this?
My thought was to make the sorted selects dirty reads, since it is acceptable if they're not completely up to date all of the time. This is my only idea for possibly improving performance of these selects and updates, but I thought someone might know a better way.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
不要过多地推断您的数据库 - 数据库是极其复杂的动物，并且几乎总是不会按照您想象的方式工作。 MVCC 数据库是任何现代数据库（即不是 myisam 表），将执行表扫描而无需大量锁定。 MVCC 的整体概念是写入不会阻止读取，反之亦然。但这里不会执行表扫描。为了有效地回答查询，可能有一个投票索引。该索引将用于 1) 限制从表中检索的行数 2) 按排序顺序检索它们（即不执行排序。）
Don't infer too much about your database - database's are extremely complicated animals and will almost always NOT work the way you would think. An MVCC database, which is any modern database (i.e. not myisam tables), will perform a table scan without locking heavily. The whole concept of MVCC is that writes don't block reads and visa versa. But no table scan will be performed here. To efficiently answer the query there is probably an index on votes. That index will be used to 1) limit the number of rows retrieved from the table 2) retrieve them in sorted order (i.e. without performing a sort.)