首先,针对您提出的具体问题进行处理:
我目前在ID和Lookup上都有单独的索引,以及一个B-Tree索引。基于我的WHERE子句,哈希索引是否适用于优化技术?
根据
CREATE INDEX Syntax记录:
+----------------+--------------------------------+
| 存储引擎 | 可允许的索引类型 |
+----------------+--------------------------------+
| MyISAM | BTREE |
| InnoDB | BTREE |
| MEMORY/HEAP | HASH、BTREE |
| NDB | BTREE、HASH(见文本中的注释)|
+----------------+--------------------------------+
因此,在考虑哈希索引之前,应该知道它仅适用于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
检查查询执行计划以进一步研究这些问题。
a inner join ... b
中,a 和 b 之间不应该有一些关系吗?看起来非常像是在创建笛卡尔积,这可以很好地解释“永远”的问题。我认为没有必要玩弄索引类型。 - Guntram Blohmtablea
和tableb
之间搞混吗?查询计划显示在m
上进行了一次完整的表扫描,涉及约45,000行(你说这是tablea
的大小)--但是possible_keys
与你给出的tableb
相符,而在表c
中使用的ref
也似乎来自tableb
? - eggyal