使用索引优化mysql查询

Question

我对这个查询有一个问题：

SELECT DISTINCT s.city, pc.start, pc.end 
FROM postal_codes pc LEFT JOIN suspects s ON (s.postalcode BETWEEN pc.start AND      pc.end) 
WHERE pc.user_id = "username" 
ORDER BY pc.start

可疑表有大约 340 000 个条目，在邮政编码上有一个索引，我有几个用户，但是这个单独的查询大约需要 0.5 秒，当我用解释运行这个 SQL 时，我得到这样的结果：http://my.jetscreenshot.com/7536/20111225-myhj-41kb.jpg - 这些 NULL 是否意味着查询未使用索引？该索引是一个 BTREE，所以我认为它应该运行得更快一点。

你能帮我解决这个问题吗？如果需要任何其他信息，请告诉我。

编辑：我在 Suspects.postalcode、postal_codes.start、postal_codes.end、postal_codes.user_id 上有索引。

基本上我想要实现的目标是：我有一个表，其中每个用户 ID 都分配了多个邮政编码范围，所以它看起来像：

user_id | start | end

比我有一个嫌疑人表，其中每个嫌疑人都有一个地址（其中包含邮政编码），所以在这个查询我试图获取邮政编码范围 - 开始和结束以及该范围内的城市名称。

希望这有帮助。

Answer 1

每当使用左连接时，都会选取第一个表的所有记录，而不是基于索引进行选择。我建议使用内部联接。就像下面的查询一样。

select distinct 
  s.city, 
  pc.start, 
  pc.end 
from postal_codes pc, suspect s 
where 
  s.postalcode between (select pc1.start, pc1.end from postal_code pc1 where pc1.user_id = "username" ) 
  and pc.user_id = "username"
order by pc.start

Answer 2

它仅使用一个索引，而不是连接中涉及的字段。尝试为开始和结束字段创建索引，或使用 >= 和 <= 而不是 BETWEEN

Answer 3

不是100%确定，但是这可能是相关的：

有时 MySQL 不使用索引，即使有可用的索引。发生这种情况的一种情况是优化器估计使用索引将需要 MySQL 访问表中很大比例的行。 （在这种情况下，表扫描可能会更快，因为它需要更少的查找。）但是，如果这样的查询使用 LIMIT 只检索某些行，MySQL 无论如何都会使用索引，因为它可以更快地找到结果中返回的几行。

因此，尝试使用 LIMIT 进行测试，如果它使用索引，那么您就找到了原因。

Answer 4

我不得不说我对你的表命名约定有点困惑，我希望“可疑”表有一个 user_id 而不是 postal_code，但你必须有你的理由。如果您要保持此查询不变，则可以在 postal_code (star,end) 上添加索引以避免完整的表扫描。

Answer 5

我认为您可以像下面这样重构您的查询，

SELECT DISTINCT s.city, pc1.start, pc1.end FROM 
(SELECT pc.start and pc.end from postal_codes pc where pc.user_id = "username") as pc1,    Suspect s
WHERE s.postalcode BETWEEN pc1.start, pc1.end ORDER BY pc1.start

由于左连接和您的之间条件，您的查询没有选取 s 表上的索引。表中拥有索引并不一定意味着它将在所有查询中使用。

Answer 6

尝试强制索引。