從 mysql 伺服器 5.0 遷移到 5.5 時儲存過程的性能下降

我最近測試了我們的數據庫從一台伺服器移動到另一台伺服器。作為此過程的一部分，我將所有數據複製到新數據庫並開始對其進行測試。作為比較，我執行了一個儲存過程，它在舊數據庫上的執行大約需要 2.5 小時，而新數據庫大約需要 8 個小時。現在數據量相同，sp 相同，差異在於：

舊數據庫

mysql version 5.0.45
pc 
OS Name Microsoft Windows XP Professional
Version 5.1.2600 Service Pack 3 Build 2600
Processor   x86 Family 6 Model 15 Stepping 13 GenuineIntel ~2394 Mhz
Total Physical Memory   2,048.00 MB

新數據庫

mysql version 5.5.25a
pc
OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition
Version 5.2.3790 Service Pack 2 Build 3790
System Manufacturer Dell Inc.
System Model    PowerEdge 2900
System Type X86-based PC
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Total Physical Memory   4,090.64 MB

如果有人知道要檢查什麼，我將很高興聽到。

現在需要花費大量時間的 SP 之一是：

BEGIN
DECLARE tempSlot INT(10);
DECLARE tempPort INT(10);
DECLARE tempOnx INT(10);
DECLARE tempLagId INT(10);
DECLARE tempIp TEXT;

DROP TABLE IF EXISTS `temp_ports_table`;
CREATE TEMPORARY TABLE `temp_ports_table` (
 `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `IP` TEXT,
 `LAG` INT(10) UNSIGNED ,
 `SLOT` INT(10) UNSIGNED ,
 `PORT` INT(10) UNSIGNED ,
 `ONX` INT(10) UNSIGNED , 
   PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO temp_ports_table (IP, LAG)
   SELECT DISTINCT IP, LAG_ID
   FROM  n_to_1_table        
   WHERE LAG_ID IS NOT NULL AND LAG_ID > 0;
INSERT INTO temp_ports_table (IP, SLOT, PORT, ONX)
   SELECT DISTINCT IP, SLOT, PORT, ONX
   FROM  n_to_1_table        
   WHERE LAG_ID IS NULL OR LAG_ID <= 0;
SET @numOfPortsInNto1Table:=(
   SELECT count(ID) 
   FROM temp_ports_table accessChassis 
   GROUP BY ID>0);
IF @numOfPortsInNto1Table<=0 THEN
 SET @numOfPortsInNto1Table:=0;
END IF;
INSERT INTO debug_table(DATE, TIME, RESULT) SELECT CURDATE(), CURTIME(), 'N:1 Modifications Stored Procedure : Created Temporary Tables';

SET @tempNto1tableCounter:=1;
WHILE @tempNto1tableCounter<=@numOfPortsInNto1Table DO

   SET @tempFlowInstanceId := 0;

   SELECT tempPortsTable.SLOT, tempPortsTable.PORT, tempPortsTable.ONX, tempPortsTable.LAG, tempPortsTable.IP 
       INTO tempSlot, tempPort, tempOnx, tempLagId, tempIp
           FROM temp_ports_table tempPortsTable 
           WHERE tempPortsTable.ID=@tempNto1tableCounter;

   IF (tempLagId IS NOT NULL AND tempLagId > 0) THEN
       UPDATE n_to_1_table 
           SET INSTANCE_ID=@tempFlowInstanceId:=@tempFlowInstanceId+1 
           WHERE 
               IP=tempIp AND 
               LAG_ID=tempLagId;
   ELSE
       UPDATE n_to_1_table 
           SET INSTANCE_ID=@tempFlowInstanceId:=@tempFlowInstanceId+1 
           WHERE 
               IP=tempIp AND 
               SLOT=tempSlot AND 
               PORT=tempPort AND 
               ((tempOnx IS NULL AND ONX IS NULL) OR ONX=tempOnx) AND 
               (LAG_ID IS NULL OR LAG_ID <= 0);
   END IF;
   SET @tempNto1tableCounter:=@tempNto1tableCounter+1;

END WHILE;
INSERT INTO debug_table(DATE, TIME, RESULT) SELECT CURDATE(), CURTIME(), 'N:1 Modifications Stored Procedure : Modified All N:1 Flow INstance ID According To Interface';
DROP TABLE IF EXISTS `temp_ports_table`;
INSERT INTO debug_table(DATE, TIME, RESULT) SELECT CURDATE(), CURTIME(), 'N:1 Modifications Stored Procedure Finished';
END

如果你所有的數據都是 InnoDB，我給你一個驚喜。

這是一個鮮為人知的事實，但在某些情況下，舊版本的 MySQL/InnoDB 可以超越/超過更新的 MySQL/InnoDB。這怎麼可能？

我之前在 DBA StackExchange 上寫過這個

• 為什麼 mysql 5.5 比 5.1 慢（linux，使用 mysqlslap）（2011 年 11 月 24 日）
• 查詢在一些較新的 MySQL 版本中執行很長時間（2011 年 10 月 5 日）
• 多核和 MySQL 性能（2011 年 9 月 20 日）
• 如何正確執行 MySQL 烘烤？（2011 年 6 月 19 日）

您必須在配置 MySQL 以利用更多執行緒和更多 CPU 時進行一些盡職調查。

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