mysql - MySQL-性能问题可能是由动态SQL引起的吗？

This question already has answers here:

Can MySql nested SP be a bottleneck?

                                    （2个答案）


                                5年前关闭。


我们有这个MySQL SP，它使用动态SQL。
似乎在负载下表现不佳。

由于使用动态SQL，此SP可能在负载下变慢。
动态SQL会导致MySql中的性能问题吗（例如，因为它没有被引擎缓存）？

请注意，此SP是从其他几个SP调用的。它使用临时表将结果传递给父SP。

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

最佳答案

是。存储过程的一个优点是可以对其进行解析，可以对查询计划进行缓存等。

动态SQL（或临时查询）没有这种优势。

也就是说，您的性能瓶颈不太可能来自动态SQL，更可能是由于缺少索引，插入/删除过多等引起的。

关于mysql - MySQL-性能问题可能是由动态SQL引起的吗？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19807451/

动态sql

mysql - MySQL-性能问题可能是由动态SQL引起的吗？