我有一个包含多个连接的查询。仅在本例中,查询中的所有值都是相同的(key = name, value = italy)。
这个查询包含26个连接,对于一个大约10000条记录的数据库,它运行大约需要12秒。
但是当我查询多达20个连接时,查询的速度会大大加快,通常不到1秒,甚至更快。
当检查这个查询的EXPLAIN时,我可以看到当我达到一个特定的连接数时,数据库停止使用我的索引,而使用临时表。
我知道MySQL 61的连接限制,但是找不到任何关于这个的信息。
这是我的查询,在EXPLAIN输出下面。
SELECT DISTINCT a.id
FROM nodes a
JOIN relationships c0
ON a.id IS NOT NULL
AND c0._start = a.id
AND c0._noderevision = a.revision
AND c0._type = 'author.address.state'
JOIN properties d0
ON d0._nodeid = c0._end
AND d0._noderevision = c0._noderevision
AND d0._key = 'name'
AND d0._value = 'italy'
JOIN relationships c1
ON a.id IS NOT NULL
AND c1._start = a.id
AND c1._noderevision = a.revision
AND c1._type = 'author.address.state'
JOIN properties d1
ON d1._nodeid = c1._end
AND d1._noderevision = c1._noderevision
AND d1._key = 'name'
AND d1._value = 'italy'
JOIN relationships c2
ON a.id IS NOT NULL
AND c2._start = a.id
AND c2._noderevision = a.revision
AND c2._type = 'author.address.state'
JOIN properties d2
ON d2._nodeid = c2._end
AND d2._noderevision = c2._noderevision
AND d2._key = 'name'
AND d2._value = 'italy'
JOIN relationships c3
ON a.id IS NOT NULL
AND c3._start = a.id
AND c3._noderevision = a.revision
AND c3._type = 'author.address.state'
JOIN properties d3
ON d3._nodeid = c3._end
AND d3._noderevision = c3._noderevision
AND d3._key = 'name'
AND d3._value = 'italy'
JOIN relationships c4
ON a.id IS NOT NULL
AND c4._start = a.id
AND c4._noderevision = a.revision
AND c4._type = 'author.address.state'
JOIN properties d4
ON d4._nodeid = c4._end
AND d4._noderevision = c4._noderevision
AND d4._key = 'name'
AND d4._value = 'italy'
JOIN relationships c5
ON a.id IS NOT NULL
AND c5._start = a.id
AND c5._noderevision = a.revision
AND c5._type = 'author.address.state'
JOIN properties d5
ON d5._nodeid = c5._end
AND d5._noderevision = c5._noderevision
AND d5._key = 'name'
AND d5._value = 'italy'
JOIN relationships c6
ON a.id IS NOT NULL
AND c6._start = a.id
AND c6._noderevision = a.revision
AND c6._type = 'author.address.state'
JOIN properties d6
ON d6._nodeid = c6._end
AND d6._noderevision = c6._noderevision
AND d6._key = 'name'
AND d6._value = 'italy'
JOIN relationships c7
ON a.id IS NOT NULL
AND c7._start = a.id
AND c7._noderevision = a.revision
AND c7._type = 'author.address.state'
JOIN properties d7
ON d7._nodeid = c7._end
AND d7._noderevision = c7._noderevision
AND d7._key = 'name'
AND d7._value = 'italy'
JOIN relationships c8
ON a.id IS NOT NULL
AND c8._start = a.id
AND c8._noderevision = a.revision
AND c8._type = 'author.address.state'
JOIN properties d8
ON d8._nodeid = c8._end
AND d8._noderevision = c8._noderevision
AND d8._key = 'name'
AND d8._value = 'italy'
JOIN relationships c9
ON a.id IS NOT NULL
AND c9._start = a.id
AND c9._noderevision = a.revision
AND c9._type = 'author.address.state'
JOIN properties d9
ON d9._nodeid = c9._end
AND d9._noderevision = c9._noderevision
AND d9._key = 'name'
AND d9._value = 'italy'
JOIN relationships c10
ON a.id IS NOT NULL
AND c10._start = a.id
AND c10._noderevision = a.revision
AND c10._type = 'author.address.state'
JOIN properties d10
ON d10._nodeid = c10._end
AND d10._noderevision = c10._noderevision
AND d10._key = 'name'
AND d10._value = 'italy'
JOIN relationships c11
ON a.id IS NOT NULL
AND c11._start = a.id
AND c11._noderevision = a.revision
AND c11._type = 'author.address.state'
JOIN properties d11
ON d11._nodeid = c11._end
AND d11._noderevision = c11._noderevision
AND d11._key = 'name'
AND d11._value = 'italy'
JOIN relationships c12
ON a.id IS NOT NULL
AND c12._start = a.id
AND c12._noderevision = a.revision
AND c12._type = 'author.address.state'
JOIN properties d12
ON d12._nodeid = c12._end
AND d12._noderevision = c12._noderevision
AND d12._key = 'name'
AND d12._value = 'italy'
和EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a index id_UNIQUE,revision revision 138 NULL 214 "Using where; Using index; Using temporary"
1 SIMPLE c0 ref fk1_idx,fk3_idx,index1 index1 142 graph.a.revision,graph.a.id 1 "Using where; Distinct"
1 SIMPLE c1 ref fk1_idx,fk3_idx,index1 index1 142 graph.a.revision,graph.a.id 1 "Using where; Distinct"
1 SIMPLE c2 ref fk1_idx,fk3_idx,index1 index1 142 graph.a.revision,graph.a.id 1 "Using where; Distinct"
1 SIMPLE c3 ref fk1_idx,fk3_idx,index1 index1 142 graph.a.revision,graph.a.id 1 "Using where; Distinct"
1 SIMPLE c4 ref fk1_idx,fk3_idx,index1 index1 142 graph.a.revision,graph.a.id 1 "Using where; Distinct"
1 SIMPLE c5 ref fk1_idx,fk3_idx,index1 index1 142 graph.a.revision,graph.a.id 1 "Using where; Distinct"
1 SIMPLE c6 ref fk1_idx,fk3_idx,index1 index1 142 graph.a.revision,graph.a.id 1 "Using where; Distinct"
1 SIMPLE c7 ref fk1_idx,fk3_idx,index1 index1 142 graph.a.revision,graph.a.id 1 "Using where; Distinct"
1 SIMPLE c8 ref fk1_idx,fk3_idx,index1 index1 142 graph.a.revision,graph.a.id 1 "Using where; Distinct"
1 SIMPLE c9 ref fk1_idx,fk3_idx,index1 index1 142 graph.a.revision,graph.a.id 1 "Using where; Distinct"
1 SIMPLE d0 ref fk2_idx,index1 index1 240 graph.c0._end,graph.a.revision,const 1 "Using index condition; Using where; Distinct"
1 SIMPLE d1 ref fk2_idx,index1 index1 240 graph.c1._end,graph.a.revision,const 1 "Using index condition; Using where; Distinct"
1 SIMPLE d2 ref fk2_idx,index1 index1 240 graph.c2._end,graph.a.revision,const 1 "Using index condition; Using where; Distinct"
1 SIMPLE d3 ref fk2_idx,index1 index1 240 graph.c3._end,graph.a.revision,const 1 "Using index condition; Using where; Distinct"
1 SIMPLE d4 ref fk2_idx,index1 index1 240 graph.c4._end,graph.a.revision,const 1 "Using index condition; Using where; Distinct"
1 SIMPLE d5 ref fk2_idx,index1 index1 240 graph.c5._end,graph.a.revision,const 1 "Using index condition; Using where; Distinct"
1 SIMPLE d6 ref fk2_idx,index1 index1 240 graph.c6._end,graph.a.revision,const 1 "Using index condition; Using where; Distinct"
1 SIMPLE d7 ref fk2_idx,index1 index1 240 graph.c7._end,graph.a.revision,const 1 "Using index condition; Using where; Distinct"
1 SIMPLE d8 ref fk2_idx,index1 index1 240 graph.c8._end,graph.a.revision,const 1 "Using index condition; Using where; Distinct"
1 SIMPLE d9 ref fk2_idx,index1 index1 240 graph.c9._end,graph.a.revision,const 1 "Using index condition; Using where; Distinct"
我怀疑发生的事情是你正在连接数据,导致它拉回0条记录,因此为什么它突然快了很多。
我要做的就是一次慢慢地添加一个连接,当它拉回0个数据时,然后用另一个连接切换连接,你知道的工作,如果返回0行,那么你知道它的连接是搞砸了!