如何提高具有依赖子查询的MySQL查询的性能？

I am working with a table in MySQL that defines a tree hierarchy using the "adjacency list" method, which should be fine for my purposes.

我正在使用MySQL中的一个表,该表使用“邻接列表”方法定义树层次结构,这对于我的目的应该没问题。

I need to compute the maximum of some value over all children using the (fast) query

我需要使用(快速)查询计算所有孩子的某个值的最大值

SELECT MAX(N.someValue) AS rate
FROM `nodes` N
WHERE N.parent = <some node id>;

Sometimes I am not as lucky and have to work with descendants of the children (its defined, and always references some leaf node in the branch of that tree).

有时候我不那么幸运,不得不与孩子的后代一起工作(它的定义,并且总是引用那棵树的分支中的一些叶子节点)。

SELECT MAX(N.someValue) AS rate
FROM `nodes` N
WHERE N.id IN (SELECT N2.descendant FROM `nodes` N2 WHERE N2.parent = <some node id>);

This second query is quite slow. The number of children for a given parent is quite low, rarely more than 10, never more than 20. It does not appear to be a correlated subquery by my eye, though EXPLAIN says the subquery is dependent. I am testing in MySQL 5.1. nodes.id is the primary key and there is a BTREE index on nodes.parent. Is there a way I can improve the speed of this query?

第二个查询非常慢。给定父母的子女数量非常少,很少超过10,从不超过20.虽然EXPLAIN说子查询是依赖的,但我的眼睛似乎并不是相关的子查询。我在MySQL 5.1中测试。 nodes.id是主键,在nodes.parent上有一个BTREE索引。有没有办法可以提高这个查询的速度?

1 个解决方案

#1

I don't see anything that specifically explains why this query is slow, but I do see that it could be restructured using a JOIN instead of a subquery. Something like this (I've reversed the table aliases because that's how my brain works):

我没有看到任何具体解释为什么这个查询很慢的东西,但我确实看到它可以使用JOIN而不是子查询进行重组。像这样的东西(我已经颠倒了表别名,因为这是我大脑的工作方式):

SELECT MAX(n2.someValue) AS `rate`
FROM `nodes` n1
JOIN `nodes` n2 ON n1.descendant = n2.id
WHERE n1.parent = '<some static id>'

I don't know why your example query was slow, but this structure shouldn't pose performance issues as long as the proper fields are indexed and of course depending on how many rows are being fetched.

我不知道为什么你的示例查询很慢,但只要正确的字段被索引,这个结构不应该造成性能问题,当然这取决于提取的行数。