复合索引需要加快连接查询？

Question

一位同事问我如何解释索引(索引？)提高绩效；我试着这样做，但我自己感到困惑。

我使用下面的模型来解释(一个错误/诊断日志数据库)。它由三个表格组成：

• 业务系统列表，包含其名称的表"System“
• 列出不同类型的跟踪、表"TraceTypes"，定义可以记录哪些类型的错误消息
• 实际跟踪消息，具有来自System和TraceTypes表的外键

我在演示中使用了MySQL，但是我不记得我使用的表类型。我想是InnoDB干的。

 System                                TraceTypes
-----------------------------         ------------------------------------------
| ID          | Name        |         | ID    | Code   | Description           |
-----------------------------         ------------------------------------------
| 1           | billing     |         | 1     | Info   | Informational mesage  |
| 2           | hr          |         | 2     | Warning| Warning only          |
-----------------------------         | 3     | Error  | Failure               |
           |                          ------------------------------------------
           |                ------------|
 Traces    |                |            
 --------------------------------------------------
 | ID | System_ID | TraceTypes_ID | Message       |
 --------------------------------------------------
 | 1  |  1        |  1            | Job starting  |
 | 2  |  1        |  3            | System.nullr..|
 --------------------------------------------------

首先，我向所有表添加了一些记录，并演示了下面的查询在0.005秒内执行：

select count(*) from Traces 
  inner join System on Traces.System_ID = System.ID
  inner join TraceTypes on Traces.TraceTypes_ID = TraceTypes.ID
where 
  System.Name='billing' and TraceTypes.Code = 'Info'

然后我生成了更多的数据(还没有索引)

• “系统”包含大约100个条目。
• "TraceTypes“包含了大约50条条目
• “痕迹”包含了1000万条记录。

现在，前面的查询花了8-10秒。

我在Traces.System_ID列和Traces.TraceTypes_ID列上创建了索引。现在，这个查询以毫秒为单位执行：

select count(*) from Traces where System_id=1 and TraceTypes_ID=1;

这也是快速的：

select count(*) from Traces 
  inner join System on Traces.System_ID = System.ID
where System.Name='billing' and TraceTypes_ID=1;

但是前面的查询--连接了所有三个表--仍然需要8-10秒的时间才能完成。

只有当我创建复合索引(包括索引中的System_ID列和TraceTypes_ID列)时，速度才下降到毫秒。

我之前教过的基本语句是“所有用于联接的列都必须被索引”。

但是，在我的场景中，System_ID和TraceTypes_ID都有索引，但是MySQL没有使用它们。问题是-为什么？我的赌注是-项目计数比100:10,000,000:50使得单列索引太大，无法使用。但这是真的吗？

Answer 1

首先，分析慢速SQL语句的正确和最简单的方法是做解释。找出优化器如何选择它的计划，并思考为什么和如何改进它。我建议只使用两个单独的索引来研究解释结果，看看mysql是如何执行语句的。

我对MySQL不是很熟悉，但似乎MySQL 4的限制是在查询中每个表只使用一个索引。自MySQL 5 (索引合并)以来，这方面似乎有了改进，但我不确定它是否适用于您的情况。再说一遍，解释应该告诉你真相。

即使每个表允许使用2个索引(MySQL 5)，但使用2个单独的索引通常比复合索引慢。使用两个单独的索引需要索引合并步骤，而不是使用复合索引的一次传递。

多列索引与索引合并可能会有帮助，它使用MySQL 5.4.2。

Answer 2

决定优化器是否使用索引的不是索引的大小，而是选择性。

Answer 3

我的猜测是，它将使用索引，然后它可能使用传统的查找移动到另一个索引，然后过滤掉。请检查执行计划。因此，简而言之，您可能在嵌套循环中遍历两个索引。根据我的理解。我们应该尝试在过滤或连接中的列上创建一个复合索引，然后对select中的列使用Include子句。我从未在MySql工作过，所以我的理解是基于Server 2005的。