的MySQL查询时间太长

问题描述：

CREATE TABLE temp (
    i1 DECIMAL(10, 5), 
    i2 DECIMAL(10, 5), 
    i3 DECIMAL(10, 5), 
    i4 DECIMAL(10, 5), 
    i5 DECIMAL(10, 5), 
    i6 DECIMAL(10, 5), 
    i7 DECIMAL(10, 5), 
    i8 DECIMAL(10, 5), 
    i9 DECIMAL(10, 5), 
    o1 DECIMAL(10, 5), 
    o2 DECIMAL(10, 5), 
    o3 DECIMAL(10, 5), 
    o4 DECIMAL(10, 5), 
    o5 DECIMAL(10, 5), 
    o6 DECIMAL(10, 5), 
    o7 DECIMAL(10, 5), 
    o8 DECIMAL(10, 5), 
    o9 DECIMAL(10, 5) 
); 
CREATE INDEX input_data_index 
ON temp (i1, i2, i3, i4, i5, i6, i7, i8, i9); 
CREATE INDEX test_index 
ON temp (o1);

我试图通过使用此查询该表来搜索：

SELECT * FROM temp t 
INNER JOIN temp x 
ON t.i1 = x.i1 
AND t.i2 = x.i2 
AND t.i3 = x.i3 
AND t.i4 = x.i4 
AND t.i5 = x.i5 
AND t.i6 = x.i6 
AND t.i7 = x.i7 
AND t.i8 = x.i8 
AND t.i9 = x.i9 
WHERE t.o1 != x.o1;

表包含180362行。如果我拿出where子句，只需要0.157秒的时间运行，然而使用where子句需要很长时间才能运行（超过300秒），此时我只是取消它。

为什么在添加where子句时需要很长时间才能运行？你对我如何加快速度有什么建议吗？

编辑：

当我运行使用原始索引我有我得到的解释声明：img

当我使用@Simulant的一个建议运行EXPLAIN语句（添加01到索引）我得到：img2

但这个查询仍然需要很长时间才能执行。

它可能会在连接上单独运行每行的where查询，即它正在执行数千个查询。 – MadHatter

这种问题是令人震惊的糟糕设计的症状 – Strawberry

尝试删除'o1'上的索引。这可能会让优化器感到困惑。 –

答

如果你想O1，其中I值相同的值，你可能会考虑：

select i1, i2, i3, i4, i5, i6, i7, i8, i9, 
     group_concat(o1) 
from temp 
group by i1, i2, i3, i4, i5, i6, i7, i8, i9;

这是不完全一样的结果集，但它可能会满足您的需求。

为什么会以匿名方式降级？ –

谢谢，但没有它不会产生所需的结果。另外我不知道谁投了你的帖子，大声笑 – User9813

如果你把鼠标悬停在向下的箭头上，它可能会提供一个线索。 – Strawberry

答

列i1, i2, i3, i4, i5, i6, i7, i8, i9被索引在同一个索引中，因此索引完全利用了没有where的查询。您的列o1已在另一个索引中编入索引。但是查询只能在一个表中使用一个索引。你应该做的是将查询所需的所有列添加到同一个索引。

CREATE INDEX input_data_index 
ON temp (i1, i2, i3, i4, i5, i6, i7, i8, i9, o1);

使用explain语句可以帮助您找出索引如何减少扫描的行。

根据这条推理，把'WHERE'改成'HAVING'会有所帮助。 –

答

有

INDEX((i1, i2, i3, i4, i5, i6, i7, i8, i9, o1)

而WHERE子句更改为

WHERE t.o1 > x.o1;

这将减少在输出的行数的一半，并可能改变执行查询的方式。

的MySQL查询时间太长

相关推荐