慢速MySQL SELECT查询

Question

我有一个很慢的MySQL SELECT查询，我似乎无法解决这个问题。

这很简单，在一个有大约600,000条记录的表上。

SELECT * 
FROM  `civicrm_contact` contact
WHERE contact.external_identifier =123456

Select查询耗时3-6秒，这使得导入另外600,000条依赖于该查询的记录是完全不切实际的。

表的索引如下图所示：

​

如果我基于contact.id=123456进行搜索，那么查询时间将降至0.004s左右。contact.id是表中的主键。external_identifier是唯一的索引。

Answer 1

我知道这是一个古老的话题，但由于它与CiviCRM有关，我想我应该推开我的想法。修复实际上并不是最佳实践，因为您已经更改了一个核心封装的表，以使您的查询运行得更快。虽然这对你来说可能是可以的，但我绝对不会推荐给每个人。

但是，您的解决方案可能已经突出了查询的问题，您似乎是在告诉查询，您期望的是一个数字，但实际上数据存储为VARCHAR。所以我认为简单地用单引号将值引起来就可以了？

SELECT * FROM civicrm_contact contact WHERE contact.external_identifier = '123456‘

如果没有这一点，我非常确定(使用Oracle多年)将发生隐式数据类型转换，因此查询将不能使用索引。

解释计划应该证明这一理论。

谢谢

Parvez

Answer 2

您似乎使用了BTREE索引。如果不对该列执行任何范围查询(使用<、>、<=或>=)，则可能需要使用基于散列的索引。

详细信息请参见Comparison of B-Tree and Hash Indexes。

并参阅here精确语法。

Answer 3

我更改了结构，使external_identifier的类型为INT而不是VARCHAR。速度提高到0.006s

我还不确定这是否会有更广泛的影响