如何优化MySQL5.5存储过程

Question

您好，我们使用的是下面的SP，它占用了超过100%的CPU利用率，执行该SP需要很长时间，如何改进此存储过程

此过程具有多个连接和内部连接

CREATE DEFINER=`root`@`%` PROCEDURE `get_tp`(
IN loggedInUser varchar(100),IN mName varchar(100),
IN meName varchar(200), IN deviceType varchar(50),
IN deviceName varchar(50),IN pS varchar(100),
IN lp varchar(100), IN segtype varchar(100),
IN PT varchar(100), IN CID varchar(50),
IN FID varchar(50), IN clliCodeIn varchar(500),
IN startIndex INT, IN endIndex INT,
IN columnindex INT, IN sortorder INT,
OUT rowcount INT)
BEGIN
DECLARE ccount INT;
DECLARE ecount INT;
DECLARE mcount1 INT;
DECLARE mcount2 INT;
DECLARE MTID INT;
IF startIndex IS NULL THEN
  SET startIndex = 0;
END IF;
IF endIndex IS NULL THEN
  SET endIndex  = 10;
END IF;
IF meName IS NULL THEN
  set meName = '';
END IF;
IF pS IS NULL THEN
  set pS = '%';
END IF;
IF PT IS NULL THEN
  set PT = '';
END IF;
IF deviceName IS NULL THEN
  set deviceName = '%';
END IF;
IF lp IS NULL THEN
  set lp = '%';
END IF;
IF deviceType IS NULL THEN
  set deviceType = '%';
END IF;
IF FID IS NULL || FID = '' THEN
    set FID = '%';
ELSE
  set FID = CONCAT('%',FID,'%');
END IF;
IF clliCodeIn IS NULL OR '' THEN
  SET clliCodeIn = '%';
END IF;
SELECT id INTO MTID from m_desc where m_name =mName;
SET segtype = '%';
  SELECT distinct me.elementName, tp.elementName, dtp.device_type, dtp.device_name,
         tpt.maxBw,IF(STRCMP(tpt.CKT_used,'null'),tpt.CKT_used,'0'),
         IF(STRCMP(tpt.CKT_res,'null'),tpt.CKT_res,'0'),
         IF(STRCMP(tpt.PKT_use,'null'),tpt.PKT_use,'0'),
         IF(STRCMP(tpt.packet_reservedbw,'null'),tpt.PKT_res,'0'),tp.type,
         G_M_E_T(me.elementName) as segmentType,             tpt.niType,dtp.id,get_derived_managed_element_type(me.elementName),mtc.cllicode
  FROM metro_detail md
  INNER JOIN me1 me ON md.node_clli = me.elementName
  INNER JOIN t_p1 tp ON tp.id_M_Ele = me.id_M_Ele
  INNER JOIN esystem ON esystem.idesystem = me.idesystem and esystem.type LIKE CONCAT(segtype)
  LEFT JOIN t_p_a tpt ON tp.elementName = tpt.tpName AND me.elementName = tpt.neName
  LEFT OUTER JOIN d_t_m dtp ON dtp.me1 = me.elementName AND dtp.ter_pt = tp.elementName
  LEFT JOIN m_e_tp_cl mtc ON (mtc.idChassis = tp.chassisId AND mtc.idm_e_tp_cl = tp.id_M_Ele)
  WHERE tpt.maxBW > 0 AND
        tpt.provisionableState = 1 AND
        CONCAT(me.elementName,tp.elementName) NOT IN (SELECT CONCAT(exit_node,exit_node_tp)
                                                          FROM m_tp_i
                                                          WHERE met_id = MTID) AND
        CONCAT(me.elementName,tp.elementName) NOT IN (SELECT CONCAT(entry_node,entry_node_tp)
                                                          FROM m_tp_i
                                                          WHERE met_id = MTID) AND
        CONCAT(me.elementName,tp.elementName) NOT IN (SELECT CONCAT(a_ne_m1,a_end_m1)
                                                          FROM e_s
                                                          WHERE product = 'PROD_H') AND
        CONCAT(tp.elementName,me.elementName) NOT IN (SELECT CONCAT(ev.a_end, ev.a_ne)
                                                          FROM e_seg ev
                                                          INNER JOIN e_s e ON e.CirID=ev.CirID
                                                          WHERE tpt.niType = "TP_U" AND e.cust_id!=CID) AND
        CONCAT(tp.elementName,me.elementName) NOT IN (SELECT CONCAT(ev.z_end, ev.z_ne)
                                                          FROM e_seg ev
                                                          INNER JOIN e_s e ON e.CirID=ev.CirID
                                                          WHERE tpt.niType = "TP_U" and e.cust_id!=CID) AND
        md.met_id = MTID AND
        tp.type != 'TPT_CTP' AND
        me.elementName COLLATE latin1_general_cs LIKE CONCAT('%',meName,'%') AND
        tpt.maxBw COLLATE latin1_general_cs LIKE CONCAT(pS) AND
        tpt.niType COLLATE latin1_general_cs LIKE CONCAT ('%',PT,'%') AND
        tp.type COLLATE latin1_general_cs LIKE CONCAT(lp) AND
        IF(STRCMP(deviceName,'%'),dtp.device_name COLLATE latin1_general_cs
              LIKE CONCAT(deviceName),dtp.device_name IS NULL OR dtp.device_name IS NOT NULL) AND
        IF(STRCMP(deviceType,'%'),dtp.device_type COLLATE latin1_general_cs
              LIKE CONCAT(deviceType),dtp.device_type IS NULL OR dtp.device_type IS NOT NULL) AND
        IF(STRCMP(FID,'%'),dtp.id COLLATE latin1_general_cs
              LIKE CONCAT(FID),dtp.id IS NULL OR dtp.id IS NOT NULL) AND
        IF(STRCMP(clliCodeIn,'%'),mtc.cllicode COLLATE latin1_general_cs
              LIKE CONCAT('%',clliCodeIn,'%'),mtc.cllicode IS NULL OR mtc.cllicode IS NOT NULL)
   ORDER BY tpt.niType DESC, me.elementName 
   LIMIT startIndex, endIndex;


   SELECT count(*) INTO rowcount
   FROM metro_detail md
   INNER JOIN me1 me ON md.node_clli = me.elementName
   INNER JOIN t_p1 tp ON tp.id_M_Ele = me.id_M_Ele
   INNER JOIN esystem ON esystem.idesystem = me.idesystem AND esystem.type LIKE CONCAT(segtype)
   LEFT JOIN t_p_a tpt ON tp.elementName = tpt.tpName AND me.elementName = tpt.neName
   LEFT OUTER JOIN d_t_m dtp ON dtp.me1 = me.elementName AND dtp.ter_pt = tp.elementName
   LEFT JOIN m_e_tp_cl mtc ON (mtc.idChassis = tp.chassisId AND mtc.idm_e_tp_cl = tp.id_M_Ele)
   WHERE tpt.maxBW > 0 AND
         tpt.provisionableState = 1 AND
         CONCAT(me.elementName,tp.elementName) NOT IN (SELECT CONCAT(exit_node,exit_node_tp)
                                                           FROM m_tp_i
                                                           WHERE met_id = MTID) AND
         CONCAT(me.elementName,tp.elementName) NOT IN (SELECT CONCAT(entry_node,entry_node_tp)
                                                           FROM m_tp_i
                                                           WHERE met_id = MTID) AND
         CONCAT(me.elementName,tp.elementName) NOT IN (SELECT CONCAT(a_ne_m1,a_end_m1)
                                                           FROM e_s
                                                           WHERE product = 'PROD_H') AND
         CONCAT(tp.elementName,me.elementName) NOT IN (SELECT CONCAT(ev.a_end, ev.a_ne)
                                                           FROM e_seg ev
                                                           INNER JOIN e_s e ON e.CirID=ev.CirID
                                                           WHERE tpt.niType = "TP_U" AND e.cust_id!=CID) AND
         CONCAT(tp.elementName,me.elementName) NOT IN (SELECT CONCAT(ev.z_end, ev.z_ne)
                                                           FROM e_seg ev
                                                           INNER JOIN e_s e ON e.CirID=ev.CirID
                                                           WHERE tpt.niType = "TP_U" AND e.cust_id!=CID) AND
         md.met_id = MTID AND
         tp.type != 'TPT_CTP' AND
         me.elementName COLLATE latin1_general_cs LIKE CONCAT('%',meName,'%') AND
         tpt.maxBw COLLATE latin1_general_cs LIKE CONCAT(pS) AND
         tpt.niType COLLATE latin1_general_cs LIKE CONCAT ('%',PT,'%') AND
         tp.type COLLATE latin1_general_cs LIKE CONCAT(lp) AND
         IF(STRCMP(deviceName,'%'),dtp.device_name COLLATE latin1_general_cs
             LIKE CONCAT(deviceName),dtp.device_name IS NULL OR dtp.device_name IS NOT NULL) AND
         IF(STRCMP(deviceType,'%'),dtp.device_type COLLATE latin1_general_cs
             LIKE CONCAT(deviceType),dtp.device_type IS NULL OR dtp.device_type IS NOT NULL) AND
         IF(STRCMP(FID,'%'),dtp.id COLLATE latin1_general_cs
             LIKE CONCAT(FID),dtp.id IS NULL OR dtp.id IS NOT NULL) AND
         IF(STRCMP(clliCodeIn,'%'),mtc.cllicode COLLATE latin1_general_cs
              LIKE CONCAT('%',clliCodeIn,'%'),mtc.cllicode IS NULL OR mtc.cllicode IS NOT NULL) ;

结束

表定义

CREATE TABLE `me1` (
  `id_M_Ele` int(11) NOT NULL AUTO_INCREMENT,
  'idesystem' int(11) DEFAULT NULL,
  `elementName` varchar(200) DEFAULT NULL,
  `productName` varchar(200) DEFAULT NULL, 
  PRIMARY KEY (`id_M_Ele`),
  UNIQUE KEY `idesystem_elementName_UNIQUE` (`idesystem`,`elementName`),
  KEY `fk_me_ems` (`idesystem`),
  KEY `index_ME` (`idesystem`,`id_M_Ele`,`elementName`,`productName`),
  CONSTRAINT `fk_me_ems` FOREIGN KEY (`idesystem`)
       REFERENCES `esystem` (`idesystem`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=45706 DEFAULT CHARSET=latin1


CREATE TABLE `t_p1` (
  `idtp` int(11) NOT NULL AUTO_INCREMENT,
  `id_M_Ele` int(11) DEFAULT NULL,
  `chassisId` int(11) NOT NULL,  
  `elementName` varchar(200) DEFAULT NULL,
  `ctpName` varchar(255) DEFAULT NULL,
  `type` enum(''TPT_PTP'',''TPT_CTP'',''TPT_FTP''), 
  `provisionableState` tinyint(1) DEFAULT NULL,
  `niType` enum(''TPT_UNI'',''TPT_NNI'') DEFAULT NULL, 
  PRIMARY KEY (`idtp`),
  UNIQUE KEY `id_M_Ele_elementName_ctpValue_UNIQUE` (`id_M_Ele`,`elementName`,`ctpName`),
  KEY `fk_tp_me_tp` (`id_M_Ele`),
  KEY `index_TP` (`elementName`,`chassisId`,`id_M_Ele`),
  CONSTRAINT `fk_tp_me_tp` FOREIGN KEY (`id_M_Ele`)
       REFERENCES `me1` (`id_M_Ele`) ON DELETE CASCADE ON UPDATE NO ACTION
) AUTO_INCREMENT=8690591 DEFAULT CHARSET=latin1


'CREATE TABLE `t_p_a` (
  `idesystem` int(11) NOT NULL,
  `neName` varchar(200) NOT NULL,
  `tpName` varchar(200) NOT NULL,
  `provisionableState` tinyint(1) DEFAULT NULL,
  `niType` enum(''TP_U'',''TP_N'') DEFAULT NULL,
  `maxBW` bigint(20) DEFAULT NULL,
  `CKT_use` bigint(20) DEFAULT NULL,
  `CKT_res` bigint(20) DEFAULT NULL,
  `PKT_use` bigint(20) DEFAULT NULL,
  `PKT_res` bigint(20) DEFAULT NULL,    
  PRIMARY KEY (`idesystem`,`neName`,`tpName`),
  KEY `index_TPA` (`tpName`,`neName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

'CREATE TABLE `d_t_m` (
  `id` varchar(255) NOT NULL,
  `me1` varchar(200) NOT NULL,
  `ter_pt` varchar(200) NOT NULL,
  `device_type` varchar(25) NOT NULL,
  `device_name` varchar(50) NOT NULL,
  `device_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_d_t_m_1` (`device_id`,`device_type`),
  KEY `index_DTP` (`me1`,`ter_pt`),
  CONSTRAINT `FK_d_t_m_1` FOREIGN KEY (`device_id`, `device_type`)
       REFERENCES `device_type` (`id`, `device_type`)
       ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC'


CREATE TABLE `m_e_tp_cl` (
  `idm_e_tp_cl` int(11) NOT NULL,
  `idChassis` int(11) NOT NULL,
  `cllicode` varchar(255) NOT NULL,
  PRIMARY KEY (`idm_e_tp_cl`,`idChassis`),
  KEY `fk_id_managed_element` (`idm_e_tp_cl`),
  CONSTRAINT `fk_id_managed_element` FOREIGN KEY (`idm_e_tp_cl`)
       REFERENCES `me1` (`id_M_Ele`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1


CREATE TABLE `m_tp_i` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `met_id` int(11) DEFAULT NULL, 
  `exit_node` varchar(200) DEFAULT NULL,
  `entry_node` varchar(200) DEFAULT NULL,
  `exit_node_tp` varchar(200) DEFAULT NULL,
  `entry_node_tp` varchar(200) DEFAULT NULL,
  `link_name` varchar(45) NOT NULL, 
  PRIMARY KEY (`id`,`link_name`),
  KEY `FK_metro_terminationpoint_info_1` (`met_id`),
  CONSTRAINT `FK_metro_terminationpoint_info_1` FOREIGN KEY (`met_id`)
       REFERENCES `metro_description` (`id`)
       ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC



CREATE TABLE `e_s` (
  `CirID` varchar(255) NOT NULL,
  `bkp_copy` tinyint(4) NOT NULL DEFAULT ''0'',
  `last_order_number` varchar(35) NOT NULL,
  `product` varchar(35) NOT NULL,
  `cust_id` varchar(35) DEFAULT NULL, 
  `a_end_m1` varchar(200) DEFAULT NULL,
  `z_end_m1` varchar(200) DEFAULT NULL,
  `a_ne_m1` varchar(200) DEFAULT NULL,
  `z_ne_m1` varchar(200) DEFAULT NULL,
  `a_end_m2` varchar(200) DEFAULT NULL,
  `z_end_m2` varchar(200) DEFAULT NULL,
  `a_ne_m2` varchar(200) DEFAULT NULL,
  `z_ne_m2` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`CirID`,`bkp_copy`),
  UNIQUE KEY `CirID_index1` (`CirID`,`bkp_copy`),
  KEY `fk_e_s_order` (`last_order_number`),
  KEY `index_en` (`CirID`,`cust_id`),
  CONSTRAINT `fk_e_s_order` FOREIGN KEY (`last_order_number`)
       REFERENCES `order` (`order_number`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=1


CREATE TABLE `e_seg` (
  `CirID` varchar(255) NOT NULL,
  `bkp_copy` tinyint(4) NOT NULL DEFAULT ''0'',
  `segmentid` tinyint(4) NOT NULL, 
  `a_end` varchar(200) NOT NULL,
  `z_end` varchar(200) NOT NULL,
  `a_ne` varchar(200) DEFAULT NULL,
  `z_ne` varchar(200) DEFAULT NULL, 
  PRIMARY KEY (`CirID`,`bkp_copy`,`segmentid`),
  KEY `fk_e_seg_e_s` (`CirID`,`bkp_copy`),
  KEY `index_ev` (`CirID`),
  CONSTRAINT `fk_e_seg_e_s` FOREIGN KEY (`CirID`, `bkp_copy`)
       REFERENCES `e_s` (`CirID`, `bkp_copy`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

此sp返回要在java UI中列出的计数和值。

Answer 1

如何优化存储过程？从测试这些片段开始，看看哪些片段是最慢的。

在这种情况下，很容易看出：NOT IN ( SELECT ... )的效率非常低，至少在旧版本的MySQL中是如此(包括5.5；5.6有一些改进)。将它们转换为LEFT JOIN ... WHERE ... IS NULL。

而且不要比较CONCATs，这完全消除了使用任何索引的更改。取而代之的是使用类似于

me.elementName = exit_mode AND tp.elementName = exit_node_tp

然后有一个综合指数：

INDEX(exit_node, exit_node_tp)

注意不要把COUNT(*)和JOIN混在一起。JOIN可能会夸大中间表中的行数，然后COUNT会获取夸大的计数。

dtp.device_name COLLATE latin1_general_cs还消除了索引的使用。解决方案是否使用排序规则声明dtp.device_name (如果可能)？

可能还有几件事要提建议。根据上面的建议工作，然后再回来寻求更多的建议/滥用。包括用于大型SELECT SHOW CREATE TABLE和EXPLAIN SELECT ...。