我有一个数据库表,在这里我想获取指定父级的所有子级(n级)。为此,我使用了工作正常的从这里回答。
这是代码:
DELIMITER $$
CREATE PROCEDURE getParents(in_id INT)
BEGIN
DROP TEMPORARY TABLE IF EXISTS results;
DROP TEMPORARY TABLE IF EXISTS temp2;
DROP TEMPORARY TABLE IF EXISTS temp1;
CREATE TEMPORARY TABLE temp1 AS
SELECT DISTINCT * FROM agents WHERE upline_id = in_id;
CREATE TEMPORARY TABLE results AS
SELECT * FROM temp1;
WHILE (SELECT count(*) FROM temp1) DO
CREATE TEMPORARY TABLE temp2 AS
SELECT DISTINCT *
FROM agents
WHERE upline_id IN (SELECT id FROM temp1);
INSERT INTO results SELECT * FROM temp2;
DROP TEMPORARY TABLE IF EXISTS temp1;
CREATE TEMPORARY TABLE temp1 AS
SELECT * FROM temp2;
DROP TEMPORARY TABLE IF EXISTS temp2;
END WHILE;
SELECT * FROM results;
END $$
DELIMITER ;以下是我如何使用它:
呼叫getParents(2060);
一切都很好,但查询运行缓慢。表中还不包含一万多条记录。
是否有方法优化上述存储过程,使其运行得更快一些?
我是MySQL的新手,所以我不知道如何优化这个存储过程。谢谢你的帮助
发布于 2016-05-05 16:04:07
Update:我可能忘记了最有可能的优化:我想您不会为临时表使用内存。对于mysql >= 5.6,您可以在您的配置中设置default_tmp_storage_engine=MEMORY (当您回答下一个堆栈溢出问题时,这会产生使您忘记它的副作用),或者您可以在查询中使用engine = memory,如果您有更早的版本,或者不能或不想更改配置。我更新了查询以使用内存。如果这确实是您的问题,并且您正在使用hdd作为临时表,那么您可能已经对原来的代码和添加的engine=memory感到满意了,因为它将产生最大的效果。
创建和删除临时表是昂贵的操作。第一个优化是创建它们一次,然后删除内容,如下所示:
DELIMITER $$
CREATE PROCEDURE getParents(in_id INT)
BEGIN
drop table if exists temp1;
drop table if exists temp2;
drop table if exists results;
create temporary table temp2 engine=memory as (select id, upline_id from agents where upline_id = in_id);
create temporary table results engine=memory as (select id, upline_id from temp2);
create temporary table temp1 (id int, upline_id int) engine=memory;
while (select count(*) from temp2) do
insert into temp1 (id, upline_id)
select a.id, upline_id
from agents a
where a.upline_id in (select id from temp2) ;
insert into results (id, upline_id)
select distinct id, upline_id
from temp1;
delete from temp2;
insert into temp2 (id, upline_id)
select distinct id, upline_id
from temp1;
delete from temp1;
end while;
select a.*
from results r
join agents a
on a.id = r.id;
drop table if exists temp1;
drop table if exists temp2;
drop table if exists results;
End $$
DELIMITER ; 下一步优化可以是通过加入前面的代码(使代码看起来更复杂,但应该更快)来减少重复次数,从而一次执行几个级别,并通过保存子级n来删除一个临时表:
DELIMITER $$
CREATE PROCEDURE getParents(in_id INT)
BEGIN
set @n = 1;
drop table if exists temp1;
drop table if exists results;
create temporary table results (id int, upline_id int, n int) engine = memory;
insert into results (id, upline_id, n)
select id, upline_id, @n from agents where upline_id = in_id;
create temporary table temp1 (id0 int, upline_id0 int, id1 int, upline_id1 int,
id2 int, upline_id2 int, id3 int, upline_id3 int) engine = memory;
while (select count(*) from results where n = @n) do
insert into temp1
select a0.id as id0, a0.upline_id as upline_id0,
a1.id as id1, a1.upline_id as upline_id1,
a2.id as id2, a2.upline_id as upline_id2,
a3.id as id3, a3.upline_id as upline_id3
from agents a0
left outer join agents a1
on a1.upline_id = a0.id
left outer join agents a2
on a2.upline_id = a1.id
left outer join agents a3
on a3.upline_id = a2.id
where a0.upline_id in (select id from results where n = @n) ;
insert into results (id, upline_id, n)
select distinct id0, upline_id0, @n + 1
from temp1
where not id0 is null;
insert into results (id, upline_id, n)
select distinct id1, upline_id1, @n + 2
from temp1
where not id1 is null;
insert into results (id, upline_id, n)
select distinct id2, upline_id2, @n + 3
from temp1
where not id2 is null;
insert into results (id, upline_id, n)
select distinct id3, upline_id3, @n + 4
from temp1
where not id3 is null;
set @n = @n + 4;
delete from temp1;
end while;
select a.*
from results r
join agents a
on a.id = r.id;
drop table if exists temp1;
drop table if exists results;
End $$
DELIMITER ; 您可以增加或减少联接数。更多的联接并不一定会更快,因为如果数据稀疏,您可能会执行未使用的联接,因此您可能需要进行一些测试。(这将取决于您的数据、每个家长的子女数以及您最常查询的深度,但3-4可能是一个很好的起点。你不应该把它弄得太高,应该给有很多孩子/孙子的父母测试。)
但是获得结果的最快方法是查看嵌套集,在MySQL中管理分层数据。阅读和理解起来有点困难,但嵌套集的操作要快得多(它们正是针对数据库中的问题而设置的)。您可以在同一表中同时使用这两个结构(如果您可能需要它们在另一个地方,所以这并不是反对它的理由),您只需在更改数据时保持它们的最新。而且,嗯,先读很多书。但这是值得的。
发布于 2016-05-05 16:06:21
(注: Solarflare建议我以错误的方式进行这种递归,在我的编辑结束时看到了来自父母的孩子。)
这是我能想出的最快的办法,假设每个孩子只有一个父母,我希望这条线是正确的。注意,我将表名更改为agentsZ,因为开头有一个drop命令,如果没有Z.运行,它将删除原始表,原因是这样,如果您将表名更改为“”,并且数据列名被替换为所需列的实际名称(星号无法工作),则存储过程将耗尽方框。
原始代码:
# DROP TABLE IF EXISTS agentsZ;
CREATE TABLE agentsZ (id TINYINT UNSIGNED PRIMARY KEY, upline_id TINYINT UNSIGNED, `data` CHAR(8));
INSERT INTO agentsZ
VALUES (1, 4, 'A'),
(2, 3, 'B'),
(5, 8, 'C'),
(6, 7, 'D'),
(4, 9, 'E'),
(3, 9, 'F'),
(9, 12, 'G'),
(8, 11, 'H'),
(7, 10, 'I'),
(12, 13, 'J'),
(11, 14, 'K'),
(10, 14, 'L');
DELIMITER $
DROP PROCEDURE IF EXISTS getParents$
CREATE PROCEDURE getParents(in_id INT)
BEGIN
SET @VUplineID := in_id;
SELECT id, @VUplineID := upline_id upline_id, `data` FROM agentsZ WHERE id = @VUplineID;
END$
DELIMITER ;
CALL getParents(1);经过测试的代码:
mysql> DROP TABLE IF EXISTS agentsZ;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>
mysql> CREATE TABLE agentsZ (id TINYINT UNSIGNED PRIMARY KEY, upline_id TINYINT UNSIGNED, `data` CHAR(8));
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> INSERT INTO agentsZ
-> VALUES (1, 4, 'A'),
-> (2, 3, 'B'),
-> (5, 8, 'C'),
-> (6, 7, 'D'),
-> (4, 9, 'E'),
-> (3, 9, 'F'),
-> (9, 12, 'G'),
-> (8, 11, 'H'),
-> (7, 10, 'I'),
-> (12, 13, 'J'),
-> (11, 14, 'K'),
-> (10, 14, 'L');
Query OK, 12 rows affected (0.02 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql>
mysql> DELIMITER $
mysql>
mysql> DROP PROCEDURE IF EXISTS getParents$
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> CREATE PROCEDURE getParents(in_id INT)
-> BEGIN
->
-> SET @VUplineID := in_id;
-> SELECT id, @VUplineID := upline_id upline_id, `data` FROM agentsZ WHERE id = @VUplineID;
->
-> END$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql> CALL getParents(1);
+----+-----------+------+
| id | upline_id | data |
+----+-----------+------+
| 1 | 4 | A |
| 4 | 9 | E |
| 9 | 12 | G |
| 12 | 13 | J |
+----+-----------+------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)稍微不那么优雅,但这里的另一个功能是:
DELIMITER $
DROP PROCEDURE IF EXISTS getChildren$
CREATE PROCEDURE getChildren(in_id INT)
BEGIN
SET @VBeforeRows := -1;
SET @VAfterRows := 0;
SET @VDownLineIDRegex := CONCAT('^', in_id, '$');
WHILE @VAfterRows != @VBeforeRows DO
SET @VBeforeRows := @VAfterRows;
DROP TEMPORARY TABLE IF EXISTS ZResults;
CREATE TEMPORARY TABLE ZResults
SELECT id, upline_id, IF(@VDownLineIDRegex REGEXP CONCAT('\|\^', id, '\$'), @VLoop := FALSE, @VDownLineIDRegex := CONCAT(@VDownLineIDRegex, '|^', id, '$')) idRegex, `data` FROM agentsZ WHERE upline_id REGEXP @VDownLineIDRegex;
SELECT COUNT(*) INTO @VAfterRows FROM ZResults;
END WHILE;
SELECT id, upline_id, `data` FROM ZResults;
END$
DELIMITER ;
CALL getChildren(14);下面是执行时输出的副本:
mysql> DROP TABLE IF EXISTS agentsZ;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> CREATE TABLE agentsZ (id TINYINT UNSIGNED PRIMARY KEY, upline_id TINYINT UNSIGNED, `data` CHAR(8));
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> INSERT INTO agentsZ
-> VALUES (1, 4, 'A'),
-> (2, 3, 'B'),
-> (5, 8, 'C'),
-> (6, 7, 'D'),
-> (4, 9, 'E'),
-> (3, 9, 'F'),
-> (9, 12, 'G'),
-> (8, 11, 'H'),
-> (7, 10, 'I'),
-> (12, 13, 'J'),
-> (11, 14, 'K'),
-> (10, 14, 'L');
Query OK, 12 rows affected (0.02 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql>
mysql> DELIMITER $
mysql>
mysql> DROP PROCEDURE IF EXISTS getChildren$
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> CREATE PROCEDURE getChildren(in_id INT)
-> BEGIN
->
-> SET @VBeforeRows := -1;
-> SET @VAfterRows := 0;
-> SET @VDownLineIDRegex := CONCAT('^', in_id, '$');
->
-> WHILE @VAfterRows != @VBeforeRows DO
->
-> SET @VBeforeRows := @VAfterRows;
->
-> DROP TEMPORARY TABLE IF EXISTS ZResults;
->
-> CREATE TEMPORARY TABLE ZResults
-> SELECT id, upline_id, IF(@VDownLineIDRegex REGEXP CONCAT('\|\^', id, '\$'), @VLoop := FALSE, @VDownLineIDRegex := CONCAT(@VDownLineIDRegex, '|^', id, '$')) idRegex, `data` FROM agentsZ WHERE upline_id REGEXP @VDownLineIDRegex;
->
-> SELECT COUNT(*) INTO @VAfterRows FROM ZResults;
->
-> END WHILE;
->
-> SELECT id, upline_id, `data` FROM ZResults;
->
-> END$
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql> CALL getChildren(14);
+----+-----------+------+
| id | upline_id | data |
+----+-----------+------+
| 5 | 8 | C |
| 6 | 7 | D |
| 7 | 10 | I |
| 8 | 11 | H |
| 10 | 14 | L |
| 11 | 14 | K |
+----+-----------+------+
6 rows in set (0.13 sec)
Query OK, 0 rows affected (0.13 sec)致以敬意,
詹姆斯
https://stackoverflow.com/questions/37044738
复制相似问题