使用索引优化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 秒。当我使用 explain 运行此 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

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

希望这可以帮助您。

- Joseph

---

嗨，Joseph，你能编辑一下问题并列出每个表的原始索引吗，包括每个表中哪些列以及顺序是什么？ - TetonSig

另外，请问这个查询应该返回什么？在我看来，对于给定的用户，您想要显示与任何涉嫌邮政编码相关联的城市，前提是该编码落在分配给该用户的范围内。您还想知道城市属于哪个范围。我之所以问这个问题，是因为了解您想要什么，将让我们知道如何构建查询的选项。 - TetonSig

嗨，我已经编辑了帖子，希望现在有更多有用的信息。 - Joseph

Answer 1

每当使用左连接时，第一个表的所有记录都会被选中，而不是基于索引的选择。我建议使用内连接。类似下面的查询：
INNER JOIN table2 ON table1.column = table2.column

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

- pseudocode

---

内连接通常比左连接慢，并且子查询中的SELECT不使用索引。Joseph，你试过吗？ - Vadim Samokhin

谢谢，它给了我一个错误：操作数应该只包含1列。可能是在between部分吗？我不确定如何重写这个。 - Joseph

Answer 2

它只使用了一个索引，并且没有用于连接涉及的字段。尝试为开始和结束字段创建索引，或者使用>=和<=代替BETWEEN。

- golimar

---

嗨，开始和结束字段都被索引了（抱歉我没有提到），我已经尝试了<= >=，但结果相同。 - Joseph

Answer 3

不是100%确定，但this可能与此相关：

有时候MySQL即使有索引可用也不会使用。其中一种情况是优化器估计使用索引需要访问表中非常大比例的行数。（在这种情况下，全表扫描可能更快，因为它需要较少的查找。）然而，如果这样的查询使用LIMIT仅检索部分行，则MySQL仍然使用索引，因为它可以更快地找到要返回的少量行。

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

- Amadan

---

嗨，我尝试使用非常小的值（如10）添加LIMIT，但没有任何效果 :/ - Joseph

Answer 4

我必须说，你的表命名规则让我有点困惑。我本来期望“嫌疑人”表应该有一个user_id而不是postal_code，但你一定有你的理由。如果你打算保留这个查询，你可以在postal_code（起始，结束）上添加索引以避免完全扫描整个表。

- Assaf Karmon

---

嗨，postal_codes表包含3个字段：user_id、start和end，因此它基本上保留了指定user_id的各种邮政编码范围的信息。 - Joseph

你好，我已经有一个包含user_id、start和end的主键。问题是MySQL只能使用索引的前缀。由于user id不在where子句中，所以你的索引没有被使用。尝试创建一个以(start,end,...)开头的索引。 - Assaf Karmon

嗨，我已经添加了索引，但结果仍然相同。 - Joseph

Answer 5

尝试使用FORCE INDEX。

- Vadim Samokhin

---

嗨，感谢回复。我已经在 suspects 表上强制索引（postalcode 字段），但没有效果，我尝试在 postal_codes 表的 start 和 end 上也强制索引，但查询时间增加了 5 倍。 - Joseph

Answer 6

我认为您可以按以下方式重新构建查询：

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 表上的索引，这是因为你使用了左连接和 between 条件。在表中建立索引并不意味着它会被所有查询使用。

- codemaster

---

谢谢，我使用了您的查询，但是花费的时间相同 :/ SELECT DISTINCT s.city, pc1.start, pc1.end FROM (SELECT pc.start, pc.end FROM postal_codes pc WHERE pc.user_id = "username") as pc1, suspects s WHERE s.postalcode BETWEEN pc1.start AND pc1.end ORDER BY pc1.start - Joseph

Joseph- 评估查询的时间也取决于表中数据的分布。 - codemaster

我的意思是说，您的表中存储的数据也会影响查询。例如，在某些情况下，在起始值和结束值之间可能有更多的邮政编码，而在其他情况下可能有较少的值。虽然如果您在可疑表中有 340,000 条条目，0.5 看起来似乎并不算太差。 - codemaster

哦，我现在明白了。是的，我已经尝试在可能结果的+-相同数量上进行测试。是的，这对于一个用户来说不是一个坏结果，但我要用它来显示大约25个用户的结果 :/ - Joseph