MySql 嵌套 SP 會成為瓶頸嗎？

我們有這個 MySQL SP，它呼叫了一個嵌套的 SP。似乎它在負載下表現不佳。

有可能這個 SP 在負載下變慢，因為它呼叫嵌套 SP 並使用臨時表將數據傳遞給主 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

假設您要拆分成片段以形成IN集合的字元串來自安全來源，那麼您所做的事情本質上並沒有錯，但是我會提出以下優化建議：

呼叫單獨的過程沒有明顯的理由，因為這一切都可以在單個過程中完成，並且所有事情都需要一些有限的時間來完成，因此將其編寫為單個過程可以節省一些時間，當您做大量相同的事情時，這會有所幫助……儘管到目前為止，我認為這不是最大的問題。

我懷疑這裡的大殺手是臨時表的重複創建。在我不起眼的雙 1GHz Opteron 測試機器上，我看到臨時表的創建時間截然不同：

Query OK, 0 rows affected (0.24 sec) # ENGINE=InnoDB (default)
Query OK, 0 rows affected (0.02 sec) # ENGINE=MyISAM
Query OK, 0 rows affected (0.00 sec) # ENGINE=MEMORY

由於您沒有使用臨時表直接更新另一個表，因此您應該可以自由使用適合應用程序的最輕量級引擎，即使這意味著您正在混合事務和非事務儲存引擎，因為您不需要基於臨時表的後續查詢才能實際正確複製到下游從站，這裡沒有什麼可以回滾的。您正在創建、插入、選擇、刪除，這不應該對複制產生任何影響（否則，它可能會）。

您還應該評估EXPLAIN嵌套過程中查詢的輸出，以確保它們是最優的。例如，使用派生表SELECT ... FROM (SELECT...)似乎並不是必需的，也可能不是編寫這些查詢的最佳方式。

例如：

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

…這樣寫可能會更好（假設我已經正確解析了它）：

INSERT INTO areas (id, code, name, shortName, levelid, sortOrder)
SELECT a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
 FROM GeoArea t
INNER JOIN GeoAreaLinkCache c ON c.ParentAreaID = t.id
INNER JOIN GeoArea a on c.ChildAreaID = a.ID
INNER JOIN GeoAreaLevel l ON l.id = a.LevelID
WHERE l.Identifier = _areaType
  AND t.Code = _parentArea;

避免派生表可能會在製定查詢計劃時為優化器提供更多選擇，因為派生表通常會限制索引的可用性。它們可能在邏輯上是等價的，但在執行上並不等價。

引用自：https://dba.stackexchange.com/questions/52825