MySql嵌套SP可以成为瓶颈吗? [英] Can MySql nested SP be a bottleneck?

问题描述

我们有这个MySQL SP，它称为嵌套SP.似乎在负载下表现不佳.

We have this MySQL SP, which calls a nested SP. It seems it does NOT perform well under load.

此SP是否有可能在加载时变慢，因为它调用了嵌套SP并使用临时表将数据传递给主SP?

It is possible that this SP becomes slow under load because it calls a nested SP and uses temporary tables to pass the data to main SP?

DELIMITER $$

drop procedure if exists `GeoAreaFlattened_Select`;

create procedure `GeoAreaFlattened_Select`(
    _areas MEDIUMTEXT,
    _comparisonGroup varchar(21844),
    _parentArea varchar(21844),
    _areaType varchar(21844)
)
begin

drop temporary table if exists areas;

-- areas
call CreateAreas(_areas, _comparisonGroup, _parentArea, _areaType);

SELECT
    areas.ID,
    areas.Code,
    areas.Name,
    areas.LevelId,
    GeoAreaLevel.Name AS AreaTypeLabel,
    GeoAreaLevel.Identifier AS AreaTypeIdentifier
FROM
    areas
INNER JOIN
    GeoAreaLevel
ON
    areas.levelid = GeoAreaLevel.id
ORDER BY areas.name ASC;

drop temporary table areas;

end

嵌套的SP:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
drop procedure if exists `CreateAreas`;

DELIMITER $$

CREATE PROCEDURE `CreateAreas`(
    _areas varchar(21844),
    _comparisonGroup varchar(21844),
    _parentArea varchar(21844),
    _areaType varchar(21844)
)
BEGIN

    -- create temporary table "areas"
    -- fill with area ids

    create temporary table areas (
        id int not null,
        code varchar(30),
        name varchar(100),
        shortName varchar(100),
        levelid int not null,
        sortOrder int not null,
        key (id)
    );

    -- assumes that only one of the 3 options is valid, areas, comparison group, bounded comparison group

    if (_areas is not null) then

        set @sql = concat('insert into areas (id, code, name, shortName, levelid, sortOrder) select id, Code, Name, ShortName, LevelID, 0 from GeoArea where Code in (''', replace(_areas, ',', ''','''), ''')');
        prepare stmt from @sql;
        execute stmt;
        deallocate prepare stmt;

    elseif (_comparisonGroup is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        insert into areas (id, code, name, shortName, levelid, sortOrder)
        select GeoAreaID, GeoArea.Code, GeoArea.Name, GeoArea.ShortName, GeoArea.LevelID, SortOrder
        from ComparisonGroupGeoAreaLink
        INNER JOIN
        GeoArea
        ON GeoArea.ID = GeoAreaID
        where ComparisonGroupID = (select id from ComparisonGroup where Identifier = _comparisonGroup)
        and IsMember = 1;

    elseif (_parentArea is not null and _areaType is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        insert into areas (id, code, name, shortName, levelid, sortOrder)
    select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
        from (select id from GeoArea where Code = _parentArea) as t
        INNER JOIN
        GeoAreaLinkCache c
        ON
        c.ParentAreaID = t.id
        inner join GeoArea a
        on c.ChildAreaID = a.ID
        INNER JOIN
        (select id from GeoAreaLevel where Identifier = _areaType) as l
        ON
        a.LevelID = l.id;        

    elseif (_areaType is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        set @sql = concat('insert into areas (id, code, name, shortName, levelid, sortOrder)
        select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
        from 
        (select id from GeoAreaLevel where Identifier in (''', replace(_areaType, ',', ''','''), ''')) l
        INNER JOIN
        GeoArea a
        ON
        a.LevelID = l.id');
        prepare stmt from @sql;
        execute stmt;
        deallocate prepare stmt;


    end if;                 

END

推荐答案

似乎嵌套的SP不是主要瓶颈.

It seems nested SPs was NOT the main bottleneck.

我已经将临时表更改为MEMORY引擎，它确实起到了作用，并且使出众.在

I've changed temp tables to MEMORY engine and it really did the trick and made AMAZING difference. Solution was suggested in

https://dba .stackexchange.com/questions/52825/can-mysql-nested-sp-be-a-bottleneck/52863?noredirect = 1#52863

create temporary table areas (
    id int not null,
    code varchar(30),
    name varchar(100),
    shortName varchar(100),
    levelid int not null,
    sortOrder int not null,
    key (id)
) ENGINE=MEMORY;

这篇关于MySql嵌套SP可以成为瓶颈吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！