sql - 根据使用的 where 子句对正确的列建立索引

Question

我有一个包含消息的表。所以我有列 id、from、to 等。如果我想为特定用户显示收件箱，我会编写一个选择语句

where to = 'username' order by id desc limit 0,20;

这将显示该用户的前 20 条消息。显然我应该将索引放在 to 列上，并且 id 列上已经有一个索引，因为它是主键，但是将索引设置在 (to >, id) 在一起？

Answer 1

不幸的是，答案超出了SO的适当范围。人们已经写过这方面的书籍。

简单地说，在 (to, id DESC) 上建立索引将是解决该查询的最佳选择。索引中的第一个字段确保数据易于搜索，并且所有感兴趣的记录都位于一个连续的 block 中。索引中的第二个字段确保连续 block 已预先排序，从而轻松查找前 20 条记录。

但是维持该索引也是一个问题。这样的索引可能很容易出现碎片。是否有能力在夜间维护工作中重建索引？索引越多，磁盘空间开销就越大。您是否有磁盘空间来为可能需要的每个查询创建新索引？额外的索引会增加写入开销。表的写入频率如何？最小延迟有多重要？除了要过滤/搜索/连接的字段之外，添加要查询的字段也意味着您只需要读取索引，而不需要“连接”到基表。这种好处是否值得进一步增加拥有更多和更广泛索引的管理费用？

这是一个很好的问题，有着非常广泛的答案，我在这里只触及了表面。