存在两个表:
maindata = id, devid, value
(1000 万行)
djournal = id, devid, md_id_begin, md_id_end, state
(10000 行)
我想从中maindata
确定选择devid
除错误行之外的所有内容state
:
SELECT md.*
FROM maindata AS md
LEFT JOIN djournal AS dj
ON md.id BETWEEN dj.md_id_begin AND dj.md_id_end
AND md.devid = dj.devid
WHERE md.devid = 123456789
AND dj.state <> 'idle'
ORDER BY md.id ASC;
给定的查询产生了我想要的结果,但是速度很慢。所有可能的索引都已创建。当然,将state
字段直接存储在maindata
表中很容易,但令人好奇的是,为什么该查询如此缓慢,是否存在任何解决方法?
答案1
您只是遇到了索引问题。
您没有发布数据库结构,但是如果您问这个问题,这是因为您对数据库不太了解(因为每个像样的数据库服务器都可以向您显示查询花费时间的地方)。
您缺失的索引可能位于md_id_begin
以及。只是猜测。如果没有,索引也可能是一个非常好的md_id_end
主意 。state
id
答案2
抱歉打扰了各位,这个问题没有解决方案。这根本不是问题,这是正常的 SQL 引擎行为。我试着解释一下原因。让我们有两个集合:
mysql> select * from Q; mysql> select * from R;
+----+------+ +----+------+
| id | val | | id | val |
+----+------+ +----+------+
| 1 | a | | 1 | a |
| 2 | b | | 2 | b |
| 3 | c | | 3 | c |
| 4 | d | | 4 | d |
| 5 | e | | 5 | e |
+----+------+ +----+------+
让我们做一个没有条件的 JOIN:
mysql> SELECT Q.val AS Qval, R.val AS Rval FROM Q JOIN R;
+------+------+
| Qval | Rval |
+------+------+
| a | a |
| b | a |
| c | a |
| d | a |
| e | a |
| a | b |
| b | b |
| c | b |
| d | b |
| e | b |
| a | c |
| b | c |
| c | c |
| d | c |
| e | c |
| a | d |
| b | d |
| c | d |
| d | d |
| e | d |
| a | e |
| b | e |
| c | e |
| d | e |
| e | e |
+------+------+
25 rows in set (0.00 sec)
让我们通过“=”条件来理顺 JOIN:
mysql> SELECT Q.val AS Qval, R.val AS Rval FROM Q JOIN R ON Q.val = R.val;
+------+------+
| Qval | Rval |
+------+------+
| a | a |
| b | b |
| c | c |
| d | d |
| e | e |
+------+------+
5 rows in set (0.00 sec)
当我们在“>”上加入时,我们得到:
mysql> SELECT Q.val AS Qval, R.val AS Rval FROM Q JOIN R ON Q.val > R.val;
+------+------+
| Qval | Rval |
+------+------+
| b | a |
| c | a |
| d | a |
| e | a |
| c | b |
| d | b |
| e | b |
| d | c |
| e | c |
| e | d |
+------+------+
10 rows in set (0.00 sec)
宽松的条件产生宽松的结果。复杂的条件会减少结果集,但会显著增加计算量。当我们在 BETWEEN 或 < 或 > 上进行 JOIN 时,我们会得到巨大的临时表来存储中间结果 - 没有索引,通过文件排序进行搜索。
因此,用“=”以外的其他东西来连接集合不是一个好主意。