左联接查询执行时间过长

Question

我有两个表要连接："product“表，这个表包含257613行，这是一个结构：

id int(11) primary key autoincrement
id_category varchar(100)
name_category varchar(500)
name varchar(1000)
name_translated varchar(1000)
reference varchar(100)
link varchar(1000)
original_price varchar(45)
resell_price varchar(45)
active int(11)
ean varchar(16)
json_detail text
date_add date
date_update date

"stores_product“表，该表包含181142行，结构如下：

id int(11) primary key autoincrement
reference varchar(128)
id_product int(11)
id_image_product int(11)
id_stock_product
id_store int(11)

这是一个被指控为缓慢的查询：

SELECT * FROM product AS p 
LEFT JOIN stores_product AS sp ON p.reference = sp.reference 
WHERE sp.id_store = 3

这个查询没有给我答复，我在35分钟后阻止了执行，没有结果。要处理的行太多了？还是我在查询中出错了？

Answer 1

关于这一点，有几点需要注意：

1. 当联接字段(在您的例子中是sp.id_store = 3)上有一个非空条件时，执行外部连接没有好处。由于外部联接比内部连接花费更大，所以在本例中使用后者：inner join。结果是一样的，但可能更快。
2. 另一方面，如果您希望通过外部联接列出所有产品，那么您的查询是不正确的。然后，您必须将条件从where子句移到on子句中，如下所示： 左加入stores_product作为sp ON p.reference = sp.reference和sp.id_store =3
3. 联接条件看起来不像预期的那样。通常，您会期望sp.id_product = p.id。但在注释中，您可以解释这两个字段是不相关的。这是一种非常混乱的命名方式。您应该考虑存储一个在product表中引用主键的外键。
4. 根据数据的分布方式，您将从以下两个索引中获益--您需要创建这两个索引: stores_product(id_store，reference)或stores_product(reference，id_store)。
5. 显然，产品(Id)应该是一个主键。

创建缺少的索引，使用explain select ...查看执行计划，并查看实际使用的索引。

Answer 2

既然你把257613行和181142行连在一起，那就需要时间了。这个查询很好，恐怕除了升级mysql服务器之外，您不能真正提高性能。即使有那么多数据，35分钟似乎也非常长。

您还可以不添加主键、索引和缓存：

• 优化与指标
• 主键对MySQL性能有多重要？
• MySQL查询缓存

Answer 3

知道索引可能导致表中的行更新或插入问题。我建议使用临时表。到目前为止(据我所知)，它们是在不改变数据库配置中任何内容的情况下降低计时成本的最佳方法。在结束时删除临时表总是一个更好的做法。

所以对于上面的问题。它可以是包含以下内容的存储过程：

select reference, (the columns you need or just *) 
INTO #TempTable
from stores_product sp 
where sp.id_store = 3 

Select * 
from product AS p
left join #TempTable sp ON p.reference = sp.reference 

Drop table #TempTable