拥有多个索引有什么缺点？
最近，我通过为 SQLite 提供一个良好的索引来将复杂的查询速度提高一个数量级。 这样的结果让我想知道是否应该为 JOIN 或 ORDER BY 子句常用的许多其他字段建立索引。 但我不想过于热心而适得其反：我认为一定有一些原因不创建索引，或者默认情况下每个字段都会被索引。
在本例中我使用 SQLite，但当然也欢迎与 DBMS 无关的建议。
I recently sped up a complicated query by an order of magnitude by giving SQLite a good index to work with. Results like this make me wonder if I should index a lot of other fields that are commonly used for JOINs or ORDER BY clauses. But I don't want to get overzealous and have it backfire on me: I assume there must be some reasons not to create indices, or every field would be indexed by default.
I'm using SQLite in this case, but of course DBMS-agnostic advice is welcome as well.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
索引会减慢插入和更新速度（这可能成为锁定的一个非常严重的问题）并消耗磁盘空间。 差不多就这样了。
Indexes slow down inserts and updates (which can become a really serious issue with locking) and cost disk space. That's pretty much it.
索引会占用磁盘空间来存储，并且需要时间来创建和维护。 未使用的不会带来任何好处。 如果查询有很多候选索引，则让服务器为查询选择“错误”的索引可能会减慢查询速度。
使用这些因素来决定是否需要索引。
通常可以创建永远不会使用的索引 - 例如，在只有两个可能值的（非空）字段上建立索引几乎肯定是无用的。
您需要解释自己的应用程序的查询，以确保频繁执行的查询在可能的情况下使用合理的索引，并且创建的索引数量不会超过所需的索引数量。
Indexes use up disc space to store, and take time to create and maintain. Unused ones don't give any benefit. If there are lots of candidate indexes for a query, the query may be slowed down by having the server choose the "wrong" one for the query.
Use those factors to decide whether you need an index.
It is usually possible to create indexes which will NEVER be used - for example, and index on a (not null) field with only two possible values, is almost certainly going to be useless.
You need to explain your own application's queries to make sure that the frequently-performed ones are using sensible indexes if possible, and create no more indexes than required to do that.
为了测试您的特定应用程序，您可以将“EXPLAIN QUERY PLAN”放在您运行的任何查询前面并检查结果。 它会告诉你哪里使用了索引，哪里没有使用索引。
这样您就可以确定在哪里可以使用更多索引以及在哪里它们不会产生影响。
Sqlite Explain
我使用 SqliteSpy 来手动测试似乎引起问题的查询。
In order to test your particular application you can put "EXPLAIN QUERY PLAN" in front of any query you run and check the results. It will show you where it is or is not using indexes.
That way you can determine where you could use more indexes and where they would not make a difference.
Sqlite Explain
I use SqliteSpy to hand test query's that seem to be causing trouble.
磁盘空间中索引的成本通常是微不足道的。 当表更改时更新索引的额外写入的成本通常是适中的。 额外锁定的成本可能会很高。
它取决于表上的读取与写入比率，以及索引实际用于加速查询的频率。
The cost of an index in disk space is generally trivial. The cost of additional writes to update the index when the table changes is often moderate. The cost in additional locking can be severe.
It depends on the read vs write ratio on the table, and on how often the index is actually used to speed up a query.