单列索引和复合索引有什么区别？

Question

在任何关系型数据库中，我们都可以创建索引以提高查询速度。但是创建更多的索引可能会损害更新/插入速度，因为数据库系统在新数据到来时（插入、更新、合并等）必须更新每个索引。

我们举一个例子。 我们可以创建一个名为index1的索引 ADD INDEX index1 (order_id ASC, buyer_id ASC) 或者我们可以创建两个索引，index2和index3 ADD INDEX index2 (order_id ASC) ADD INDEX index3 (buyer_id ASC)

在这样的查询中 select * from tablename where order_id>100 and buyer_id>100

哪一个更快？使用Index1还是index2和index3？

另一方面，在插入或更新时，我认为只使用一个索引比使用2个更快，但我还没有对MySql或MSSQL服务器进行测试，所以我不能确定。如果有人在这方面有经验，请分享。

最后一件事是关于int类型的值，我认为为int类型的列创建索引不可能或不相关，因为它不会提高查询时间，这是真的吗？

- shawhu

---

还要注意的是，Sql Server和MySql中的索引是不同的，因此将这个问题标记为两者都有可能会导致问题，因为它们可能会有不同的答案。 - Vanlightly

我本意是要链接这个网址 https://dev59.com/1nVC5IYBdhLWcg3wz0h9 - Pரதீப்

我的问题更关注于同时提高读取和破坏写入效率，而另一个问题更专注于只读操作。 - shawhu

虽然我同意你的观点，即MySql在查询中使用索引确实有很大的区别，但总的来说，我想回答一个更普遍的问题。从一般层面上讲，创建更多的索引会影响写入速度（更新/插入），这对mysql和mssql都是正确的。即使不是这样，我认为许多人正在使用mssql和mysql进行不同的项目，他们应该有机会比较并了解差异。至少我是这样的。我不知道是否只有我同时使用mssql和mysql？ - shawhu

index2和index3——是出于一个奇怪的原因而最优的。WHERE语句的两个部分都是“范围”。因此，它不能同时使用复合键(order_id, buyer_id)的两个部分。相反，优化器可以决定order_id>100是否更具选择性（并使用INDEX(order_id)），或者另一个更好。 - Rick James

Answer 1

对于您提到的确切查询，我个人会选择index1（您将同时进行两个条件的搜索操作）。即使您仅按order_id过滤，同样的索引也可以胜任工作（因为order id是索引的第一列，所以相同的BTREE结构即使省略了买家，仍然有帮助）。

同时，如果您仅按buyer_id过滤，则index1不会有太大帮助（因为BTREE首先按照缺失的order_id进行结构化，如索引创建语句所述）。在这种情况下，使用单独的索引仍然有效（应该期望在index3上进行搜索操作）。

- Dimitar Kyuchukov

---

由于两个部分都是“范围”，组合索引并没有提供任何优势。 - Rick James

嗯...仔细想想，我实际上倾向于同意你的说法。 - Dimitar Kyuchukov

Answer 2

索引的性能与其选择性有关，使用两个索引或复合索引必须在应用或查询的上下文中进行评估，特别是在性能方面，因为可能会减少要处理（并放入连接）的行数，仅仅因为它们在字段上的位置。

在您的情况下，由于一个订单通常只有一个买家，所以order_id、buyer_id不是非常具有选择性的索引（尽管对于连接操作很有用），相反，buyer_id、order_id更适合于方便查找买家的订单。

- ScaisEdge