MySQL哈希索引优化

Question

MySQL哈希索引优化

mysqlhashindexingb-tree

7

也许这很初级，但我正在处理几个表格。

我有大约45,000条记录的A表

我有大约1.5百万条记录的B表

我有一个查询：

update
    schema1.tablea a
    inner join (
        SELECT DISTINCT
            ID, Lookup,
            IDpart1, IDpart2
        FROM
            schema1.tableb
        WHERE
            IDpart1 is not NULL
        AND
            Lookup is not NULL
        ORDER BY
            ID,Lookup
    ) b Using(ID,Lookup)
set 
    a.Elg_IDpart1 = b.IDpart1, 
    a.Elg_IDpart2 = b.IDpart2
where
    a.ID is NOT NULL
AND
    a.Elg_IDpart1 is NULL

所以，我强制使用ID、Lookup索引。每个表都有这些列的索引，但由于子查询，我强制使用了它。

运行起来非常慢，实际上应该只需要不到5分钟...

我的问题与索引有关，而不是查询本身。

我知道在有序索引中不能使用哈希索引。

目前，我分别对ID、Lookup建立索引，并且作为一个索引，使用的是B-Tree索引。根据我的WHERE条件，哈希索引是否适合作为一种优化技术？

我可以只有一个哈希索引，其余的索引是B-Tree索引吗？

这不是主键字段。

我想发布我的解释，但我更改了这些表的名称。基本上它仅用于ID...而不是使用ID、Lookup，我想强制使用两者或至少将其转换为不同类型的索引，看看是否有所帮助？

现在我知道MySQL足够聪明，可以确定哪个索引最合适，所以它是在做什么呢？

Lookup字段映射ID的第一部分和第二部分...

感谢您提供任何帮助或见解。

- Hituptony

在你的 a inner join ... b 中，a 和 b 之间不应该有一些关系吗？看起来非常像是在创建笛卡尔积，这可以很好地解释“永远”的问题。我认为没有必要玩弄索引类型。 - Guntram Blohm

1

是的，使用（ID，查找） - Hituptony

你确定你没有在tablea和tableb之间搞混吗？查询计划显示在m上进行了一次完整的表扫描，涉及约45,000行（你说这是tablea的大小）--但是possible_keys与你给出的tableb相符，而在表c中使用的ref也似乎来自tableb？ - eggyal

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- eggyal · Accepted Answer

首先，针对您提出的具体问题进行处理：

我目前在ID和Lookup上都有单独的索引，以及一个B-Tree索引。基于我的WHERE子句，哈希索引是否适用于优化技术？

因此，在考虑哈希索引之前，应该知道它仅适用于MEMORY和NDB存储引擎：因此可能对您不可用。

此外，请注意，仅基于ID和Lookup的组合索引可能不是最佳选择，因为您的WHERE谓词还过滤了tablea.Elg_IDpart1和tableb.IDpart1 - 您可能从对这些列进行索引中受益。

我可以有一个单一的哈希索引，其余的索引是B-Tree索引吗？

只要所需的索引类型由存储引擎支持，您可以随意混合使用它们。

我想强制使用ID和Lookup，或者至少将其转换为不同的索引类型并查看是否有帮助？

您可以使用index hint来强制MySQL使用与优化器选择的不同索引。

现在我知道MySQL足够聪明，可以确定最合适的索引，那么它正在这样做吗？

通常情况下，它足够聪明，但并非总是如此。但在这种情况下，它可能已确定索引的基数使得使用它所选择的更好。

根据您使用的MySQL版本，源自子查询的表可能没有任何索引可用于进一步处理：因此，与b的连接可能需要对该派生表进行全面扫描（根据您的问题中不足的信息无法确定这可能是多大的问题，但是schema1.tableb有150万条记录表明它可能是一个重要因素）。

有关更多信息，请参见Subquery Optimization。

因此，应尽量避免使用派生表。在这种情况下，似乎没有任何目的使用您的派生表，因为可以直接连接schema1.tablea和schema1.tableb：

UPDATE   schema1.tablea a
    JOIN schema1.tableb b USING (ID, Lookup)
SET      a.Elg_IDpart1 = b.IDpart1, 
         a.Elg_IDpart2 = b.IDpart2
WHERE    a.Elg_IDpart1 IS     NULL
     AND a.ID          IS NOT NULL
     AND b.IDpart1     IS NOT NULL
     AND b.Lookup      IS NOT NULL
ORDER BY ID, Lookup

唯一丢失的是对于DISTINCT记录的筛选器，但重复记录将简单地（尝试）用相同的值再次覆盖更新后的值 - 这将没有影响，但可能非常昂贵（特别是在该表中有如此多的记录时）。

派生表中使用ORDER BY是无意义的，因为不能依赖它来实现任何特定的UPDATE顺序，而在这个修订版中，它将确保任何覆盖以前更新的更新按指定顺序进行：但这是否必要？也许可以将其删除并节省任何排序操作。

应该检查WHERE子句中的谓词：它们都是必需的吗（例如，在a.ID和b.Lookup上的NOT NULL检查是多余的，因为任何这样的NULL记录都将被JOIN谓词消除）？

总之，这使我们得到：

UPDATE   schema1.tablea a
    JOIN schema1.tableb b USING (ID, Lookup)
SET      a.Elg_IDpart1 = b.IDpart1, 
         a.Elg_IDpart2 = b.IDpart2
WHERE    a.Elg_IDpart1 IS     NULL
     AND b.IDpart1     IS NOT NULL

只有当性能仍然不令人满意时，才应进一步查看索引。是否对相关列（即在JOIN和WHERE谓词中使用的列）进行了索引？MySQL是否选择使用这些索引（请记住，它每个表只能使用一个索引进行查找：用于测试JOIN谓词和过滤器谓词：也许您需要一个适当的复合索引）？通过使用EXPLAIN检查查询执行计划以进一步研究这些问题。