创建SQL索引的最佳方法是什么？

Question

在 SQL 数据库中创建索引的最佳方法是什么？

CREATE INDEX idx ON sometable (col1, col2, col3);

或者

CREATE INDEX idx1 ON sometable (col1);
CREATE INDEX idx2 ON sometable (col2);
CREATE INDEX idx3 ON sometable (col3);

这两种方法有什么区别？是否取决于SQL实现？（SQLite，MySQL，MSSQL等） 在使用这两种方法时是否存在任何效率考虑？

- banx

Answer 1

CREATE INDEX idx ON sometable (col1, col2, col3);

这将在三个字段上创建一个单一的索引。如果您的查询大部分时间都使用这三个字段作为WHERE子句，那么这是完美的：

SELECT (fields) FROM Table WHERE col1 = 1 AND col2 = 2 AND col3 = 3

当您仅查询 col1 和 col2 时，这个复合索引也会被使用：

SELECT (fields) FROM Table WHERE col1 = 1 AND col2 = 2 

但是如果你分别查询 col2 或 col3，或同时查询 col2 和 col3，则无法使用该方法：

SELECT (fields) FROM Table WHERE col2 = 2 AND col3 = 3  -- will *NOT* use index
SELECT (fields) FROM Table WHERE col2 = 2               -- will *NOT* use index
SELECT (fields) FROM Table WHERE col3 = 3               -- will *NOT* use index

另一方面，如果您使用以下代码：

CREATE INDEX idx1 ON sometable (col1);
CREATE INDEX idx2 ON sometable (col2);
CREATE INDEX idx3 ON sometable (col3);

然后，您需要在每个字段上创建三个单独的索引。在这种情况下，使用任何一个列的查询都将从该列的索引中受益：

SELECT (fields) FROM Table WHERE col1 = 1   -- will use index on col1
SELECT (fields) FROM Table WHERE col2 = 2   -- will use index on col2
SELECT (fields) FROM Table WHERE col3 = 3   -- will use index on col3

然而，如果您在单个查询中同时查询两个或三个列：

SELECT (fields) FROM Table WHERE col1 = 1 AND col2 = 2 AND col3 = 3

如果只创建其中一个索引，则使用的可能是效率较低的索引，而不是(col1、col2、col3)的组合索引。

因此，在创建这些索引时肯定存在差异，但没有一种单一的“最佳方法”来进行索引。真正取决于您如何查询数据，以确定哪个选项在您的情况下更好。

- marc_s

---

这个东西适用于所有的SQL数据库吗（如SQLite、MySQL等）？ 您能提供一些关于这个问题的进一步阅读参考资料吗？ - banx

只要关系数据库管理系统支持复合索引，我认为它们都是一样的。我精通SQL Server，所以对其他系统没有太多链接 - 但我相信Google先生或Bing女士会很乐意帮助您 - 搜索“复合索引”和您喜爱的RDBMS。 - marc_s

Answer 2

第一个创建了一个由3个列组成的复合索引，而第二个则在每个列上创建了一个简单索引。请查看MSDN文章中的解释和示例。

（先查看示例部分，然后再去阅读每种类型索引的定义，否则我感觉这篇文章会让人望而生畏）

- Hari Menon