mysql - 如何优化mysql 5.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。

并且不要比较CONCAT，这完全消除了使用任何索引的变化。相反，使用类似的东西

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 ...。