首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >MySQL:存储过程慢

问MySQL:存储过程慢
EN

Stack Overflow用户
提问于 2016-05-05 07:18:55
回答 2查看 2.3K关注 0票数 1

我有一个数据库表,在这里我想获取指定父级的所有子级(n级)。为此,我使用了工作正常的从这里回答。

这是代码:

代码语言:javascript
复制
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的新手,所以我不知道如何优化这个存储过程。谢谢你的帮助

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2016-05-05 16:04:07

Update:我可能忘记了最有可能的优化:我想您不会为临时表使用内存。对于mysql >= 5.6,您可以在您的配置中设置default_tmp_storage_engine=MEMORY (当您回答下一个堆栈溢出问题时,这会产生使您忘记它的副作用),或者您可以在查询中使用engine = memory,如果您有更早的版本,或者不能或不想更改配置。我更新了查询以使用内存。如果这确实是您的问题,并且您正在使用hdd作为临时表,那么您可能已经对原来的代码和添加的engine=memory感到满意了,因为它将产生最大的效果。

创建和删除临时表是昂贵的操作。第一个优化是创建它们一次,然后删除内容,如下所示:

代码语言:javascript
复制
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来删除一个临时表:

代码语言:javascript
复制
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中管理分层数据。阅读和理解起来有点困难,但嵌套集的操作要快得多(它们正是针对数据库中的问题而设置的)。您可以在同一表中同时使用这两个结构(如果您可能需要它们在另一个地方,所以这并不是反对它的理由),您只需在更改数据时保持它们的最新。而且,嗯,先读很多书。但这是值得的。

票数 4
EN

Stack Overflow用户

发布于 2016-05-05 16:06:21

(注: Solarflare建议我以错误的方式进行这种递归,在我的编辑结束时看到了来自父母的孩子。)

这是我能想出的最快的办法,假设每个孩子只有一个父母,我希望这条线是正确的。注意,我将表名更改为agentsZ,因为开头有一个drop命令,如果没有Z.运行,它将删除原始表,原因是这样,如果您将表名更改为“”,并且数据列名被替换为所需列的实际名称(星号无法工作),则存储过程将耗尽方框。

原始代码:

代码语言:javascript
复制
# 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);

经过测试的代码:

代码语言:javascript
复制
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)

稍微不那么优雅,但这里的另一个功能是:

代码语言:javascript
复制
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);

下面是执行时输出的副本:

代码语言:javascript
复制
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)

致以敬意,

詹姆斯

票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/37044738

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档