如何优化这个MySQL查询的速度（非常慢）？

Question

我有一张2GB的MySQL表格，其中包含500k行数据。在系统没有负载的情况下，我运行了以下查询语句。

select * from mytable 
where name in ('n1', 'n2', 'n3', 'n4', ... bunch more... ) 
order by salary

执行这个操作需要进行文件排序，需要50到70秒的时间才能完成。

如果删除按薪水排序并在应用程序中进行排序，则总运行时间（包括排序）可缩短至约25-30秒。但这仍然太长了。

您有任何想法如何加快这个过程吗？

谢谢。

- CharlesS

---

请参见：https://dev59.com/xHM_5IYBdhLWcg3w2W9g - hobodave

Answer 1

将名字列表放入临时表中，然后在这两个表上执行内连接。这种方法比为每一行组合整个列表要快得多。以下是伪代码：

将名字列表放入临时表中，然后在这两个表上执行内连接。这种方法比为每一行组合整个列表要快得多。以下是伪代码：

create temporary table names
    (name varchar(255));

insert into names values ('n1'),('n2'),...,('nn');

select
    a.*
from
    mytable a
    inner join names b on
        a.name = b.name

另外还要注意，name 应该有一个索引。这样可以让事情变得更快。感谢 Thomas 做出了这个备注。

- Eric

---

确保原始表格在名称上有索引，以最大程度地利用连接。 - Thomas Jones-Low

谢谢，这确实快了一些；对于相同的查询大约需要25秒，但25秒仍然不算是真正的改进... - CharlesS

有或没有order by？有order by时，查询速度快50％，没有它只会稍微快一点。您需要在两个表上都建立一个仅包含名称的索引。然后运行查询的EXPLAIN命令以查看其执行情况。 - Thomas Jones-Low

你能提供一些证明“IN”查询不是一个好选择的参考资料吗？在我使用Mysql（5.0+）的经验中，如果你使用join，equals（一个name ='xxx'）或者“In”，它们的性能非常相似。我认为最重要的点是正确的索引和服务器配置。 - Leonel Martins

Answer 2

一些想法：

• 你是否需要选择*，你能否只选择一个子集？
• 如果你可以只选择一个子集，你可以添加一个覆盖索引，它已经按薪水排序了
• 如果所有东西都有相同的模式，你可以使用LIKE('n%')

- Sam Saffron

Answer 3

尝试使用子查询选择所需的行，然后对该子查询的结果进行排序。请参见此问题。

而且你在 mytable 中的 name 上有索引吗？

- Bond - James Bond

Answer 4

根据数据分布和WHERE子句匹配的行数，您可能需要尝试在(salary, name)或甚至(name, salary)上创建索引，尽管后者对于这种类型的查询可能不是非常有用。

您还可以增加sort_buffer_size设置。分别测试每个设置，并比较EXPLAIN的输出。

- Josh Davis

Answer 5

create index xyz on mytable(name(6));

"

“IN”查询几乎总是效率低下的，因为它们在概念上被处理成这样：

"

select * from mytable where name = n1  
or name = n2
or name = n3
...

我上面给出的索引可能意味着查询优化器通过索引而不是表扫描访问行。

- tpdi

---

你能提供一些证明“IN”查询几乎总是低效的参考资料吗？根据我在Mysql（5.0+）的经验，无论你使用连接还是等于（一个名字 = 'xxx'），它们的性能都是相同的。我认为最重要的因素是正确的索引和服务器配置。 - Leonel Martins