mysql - 索引在mysql中对性能的作用

Question

为什么为 mysql 表定义索引可以提高具有连接的查询的性能？

Answer 1

如果您对书中的特定主题感兴趣，您可以翻到书的后面，在索引中按字母顺序找到它。索引告诉您讨论该主题的页码。然后你直接跳到你感兴趣的页面。比阅读整本书要快得多。

在数据库中也是一样。索引意味着您可以跳转到连接行，而不是扫描表中的每一行来寻找匹配项。

看看聚簇索引是如何工作的(http://msdn.microsoft.com/en-us/library/ms177443.aspx)。您可以在每张 table 上放一张。

这篇文章解释了非聚集索引的工作原理 (http://msdn.microsoft.com/en-us/library/ms177484.aspx)。您可以拥有任意数量的它们。

这两篇文章都是关于 Microsoft Sql Server 的，但索引背后的理论在所有关系数据库管理系统中都是相同的。

索引确实有相关成本。每次对表执行插入/更新时，可能还必须更新受影响的索引。当然，索引会占用空间——但这对我们大多数人来说并不是真正的问题。因此，您需要在更快的连接或过滤的性能优势与插入和更新的成本之间取得平衡。

作为指南，您通常需要一个与连接或 where 子句中包含的每个列相匹配的索引:

SELECT 
    * 
FROM 
    Customer 
WHERE 
    RegistrationDate > @registrationDate 
    AND RegistrationCountry = @registrationCountry;

因此，包含 RegistrationDate 和 RegistrationCountry 列的 Customer 表上的索引将加速此查询。由于我们在查询中使用了“>”，这将是聚集索引的一个很好的候选者(第一篇文章表明聚集索引在物理上按索引顺序排列数据，因此范围查询可以非常快速地隔离索引的范围).

SELECT 
    * 
FROM 
    Customer c
    INNER JOIN Order o
        ON o.CustomerID = c.CustomerID
        AND o.OrderType = @orderType

在这里，我们需要包含 CustomerID 列的 Customer 表的索引。我们需要包含 CustomerID 和 OrderType 列的 Order 表的索引。然后连接的双方都不需要进行表扫描。

通常只有少数几种方法可以从表中查询数据，因此您不会遇到索引过载的情况。很多索引有时表明您的表存在混合问题并且可以规范化。