一、表结构DDL
root@dbmonitor-qb-01 10:59: [trswcmtest]> show create table WCMMetaTableWebSite\G*************************** 1. row ***************************Table: WCMMetaTableWebSiteCreate Table: CREATE TABLE `wcmmetatablewebsite` (`WCMMetaTableWebSiteID` int(11) NOT NULL DEFAULT '0',`MetaDataId` int(11) DEFAULT NULL,`ChannelId` int(11) DEFAULT NULL,`CrUser` varchar(50) DEFAULT NULL,`CrTime` timestamp NULL DEFAULT NULL,`FromType` int(11) DEFAULT NULL,`fromid` varchar(50) DEFAULT NULL,`ShareDocId` int(11) DEFAULT NULL,`Title` varchar(750) DEFAULT NULL,`OriginalTitle` varchar(750) DEFAULT NULL,`OriginalAddress` varchar(750) DEFAULT NULL,`Channel` varchar(750) DEFAULT NULL,`HomeTitle` varchar(750) DEFAULT NULL,`Author` varchar(75) DEFAULT NULL,`MoveTitle` varchar(750) DEFAULT NULL,`Keywords` varchar(750) DEFAULT NULL,`Abstract` varchar(1125) DEFAULT NULL,`ReadingPics` varchar(750) DEFAULT NULL,`AudioVideo` varchar(750) DEFAULT NULL,`IssueTime` timestamp NULL DEFAULT NULL,`CommentSettings` int(11) DEFAULT NULL,`FlowVersionTime` timestamp NULL DEFAULT NULL,`OriginMetaDataId` int(11) DEFAULT NULL,`DocType` int(11) DEFAULT NULL,`METALOGO` varchar(300) DEFAULT NULL,`METALOGOURL` varchar(4000) DEFAULT NULL,`Content` longtext,`AdditionalTextTop` longtext,`AdditionalTextBottom` longtext,`AdditionalTextLeft` longtext,`AdditionalTextRight` longtext,`Hits` int(11) DEFAULT NULL,`AttributionLabel` varchar(750) DEFAULT NULL,`OutLinePics` varchar(750) DEFAULT NULL,`Editor` varchar(750) DEFAULT NULL,`EditorTrueName` varchar(750) DEFAULT NULL,`TitleColor` varchar(300) DEFAULT NULL,`DocSource` int(11) DEFAULT NULL,`SrcMetaDataId` int(11) DEFAULT NULL,`DocWordsCount` int(11) DEFAULT NULL,`attachpic` int(11) DEFAULT NULL,`attachvideo` int(11) DEFAULT NULL,`attachaudio` int(11) DEFAULT NULL,`FgdError` int(2) DEFAULT NULL,`Remarks` varchar(750) DEFAULT NULL,`CrDept` varchar(1500) DEFAULT NULL,`HTMLContent` longtext,`srcUrl` varchar(300) DEFAULT NULL,`UpdateCount` int(11) DEFAULT NULL,`original` int(11) DEFAULT '0',`IsPushHome` int(11) DEFAULT '0',`DocSourceName` varchar(300) DEFAULT NULL,`TimingPublishTime` timestamp NULL DEFAULT NULL,`LeadTitle` varchar(750) DEFAULT NULL,`SubTitle` varchar(750) DEFAULT NULL,`specialFile` varchar(750) DEFAULT NULL,`specialCatalog` varchar(300) DEFAULT NULL,`specialContent` longtext,`mlfrootid` int(11) DEFAULT '0',`ISCOMMENTED` int(2) DEFAULT NULL,`customtime` timestamp NULL DEFAULT NULL,`AudioVideoUrl` varchar(600) DEFAULT NULL,`isContainSensitiveWords` int(11) DEFAULT NULL,`ORIGINALTYPE` int(11) DEFAULT NULL,`ORIGINALCHILDTYPE` int(11) DEFAULT NULL,`IsSecondReview` int(11) DEFAULT NULL,PRIMARY KEY (`WCMMetaTableWebSiteID`),UNIQUE KEY `IX_WebSite_282457558` (`MetaDataId`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.01 sec)root@dbmonitor-qb-01 09:45: [trswcmtest]> show create table WCMChnlDoc\G*************************** 1. row ***************************Table: WCMChnlDocCreate Table: CREATE TABLE `wcmchnldoc` (`CHNLID` int(11) NOT NULL,`DOCID` int(11) NOT NULL,`DOCORDER` int(11) NOT NULL DEFAULT '0',`DOCSTATUS` int(11) NOT NULL DEFAULT '0',`CRUSER` varchar(100) NOT NULL DEFAULT 'admin',`CRTIME` datetime DEFAULT NULL,`DOCPUBTIME` datetime DEFAULT NULL,`DOCPUBURL` varchar(300) DEFAULT NULL,`RECID` int(11) NOT NULL,`DOCORDERPRI` int(11) NOT NULL DEFAULT '0',`INVALIDTIME` datetime DEFAULT NULL,`OPERUSER` varchar(50) DEFAULT NULL,`OPERTIME` datetime DEFAULT NULL,`MODAL` int(11) DEFAULT '1',`DOCRELTIME` datetime DEFAULT NULL,`DOCCHANNEL` int(11) DEFAULT NULL,`DOCFLAG` int(11) DEFAULT NULL,`DOCKIND` int(11) DEFAULT '0',`SITEID` int(11) NOT NULL DEFAULT '0',`SRCSITEID` int(11) NOT NULL DEFAULT '0',`DOCFIRSTPUBTIME` datetime DEFAULT NULL,`NODEID` int(11) DEFAULT '0',`CRDEPT` varchar(200) DEFAULT NULL,`DOCOUTUPID` int(11) DEFAULT '0',`DOCFORM` int(11) DEFAULT '0',`DOCLEVEL` int(11) DEFAULT NULL,`attachpic` smallint(6) DEFAULT NULL,`POSCHNLID` int(11) DEFAULT '0',`ISPUSHTOPCHNL` int(2) DEFAULT '0',`HIDDEN` int(2) DEFAULT '0',`DOCTYPE` int(2) DEFAULT '0',`ISTIMINGPUBLISH` int(2) DEFAULT '0',`GDORDER` int(2) DEFAULT NULL,`setTopInfo` varchar(100) DEFAULT NULL,`OriginDocId` int(9) DEFAULT NULL,`ATTACHVIDEO` int(2) DEFAULT '0',`ATTACHAUDIO` int(2) DEFAULT '0',`SrcMetaDataId` int(11) DEFAULT NULL,`pubUser` varchar(50) DEFAULT NULL,`mrsFlag` int(2) DEFAULT NULL,`timingPubUser` varchar(50) DEFAULT NULL,`isTransmit` int(2) DEFAULT NULL,`TIMINGPUBUSERDEPT` varchar(200) DEFAULT NULL,`PUBUSERDEPT` varchar(200) DEFAULT NULL,`DocOldStatus` int(11) DEFAULT '0',`isZhengShen` int(2) DEFAULT '0',`ClientExamine` int(2) DEFAULT '0',`srcChannelId` int(11) DEFAULT NULL,PRIMARY KEY (`RECID`),KEY `IX_WCMCHNLDOC_CHNL_DOC` (`CHNLID`,`DOCID`) USING BTREE,KEY `IX_WCMCHNLDOC_CHNL_ORDER` (`CHNLID`,`DOCORDER`) USING BTREE,KEY `IX_WCMCHNLDOC_DOCID_MODAL` (`DOCID`,`MODAL`) USING BTREE,KEY `idx_DocId_SrcMetaDataId` (`DOCID`,`SrcMetaDataId`),KEY `idx_chnlid_docstatus_dockind_docpubtime` (`CHNLID`,`DOCSTATUS`,`DOCKIND`,`DOCPUBTIME`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
二、优化前SQL
root@dbmonitor-qb-01 10:57: [trswcmtest]> desc select WCMMetaTableWebSite.MetaDataId from WCMMetaTableWebSite where exists(select WCMChnlDoc.DocId from WCMChnlDoc where WCMChnlDoc.ChnlId>=1 and WCMChnlDoc.Modal>=1 and WCMChnlDoc.DocId=WCMMetaTableWebSite.MetaDataId and WCMChnlDoc.docKind =2 and WCMChnlDoc.DOCSTATUS in(10) and WCMChnlDoc.ChnlId=11224) order by issuetime desc;+----+--------------------+---------------------+------------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+---------+-------------------------------------------------+-------+----------+-----------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------------+---------------------+------------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+---------+-------------------------------------------------+-------+----------+-----------------------------+| 1 | PRIMARY | WCMMetaTableWebSite | NULL | ALL | NULL | NULL | NULL | NULL | 44976 | 100.00 | Using where; Using filesort || 2 | DEPENDENT SUBQUERY | WCMChnlDoc | NULL | ref | IX_WCMCHNLDOC_CHNL,IX_WCMCHNLDOC_CHNL_DOC,IX_WCMCHNLDOC_CHNL_ORDER,IX_WCMCHNLDOC_DK_ST_CI,IX_WCMCHNLDOC_DOCKIND,IX_WCMCHNLDOC_DS_CHNL,IX_WCMCHNLDOC_DOCID_MODAL,IX_WCMChnlDoc_OperTimeChnlStatus,IX_WCMChnlDoc_CrUserStatus,IX_WCMChnlDoc_SiteIdStatus,IX_WCMChnlDoc_PubTimeChnlStatus,IX_WCMChnlDoc_CrUserSiteStatus,IX_WCMChnlDoc_FullSite | IX_WCMCHNLDOC_CHNL_DOC | 8 | const,trswcmtest.WCMMetaTableWebSite.MetaDataId | 1 | 5.00 | Using where |+----+--------------------+---------------------+------------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+---------+-------------------------------------------------+-------+----------+-----------------------------+2 rows in set, 2 warnings (0.00 sec)root@dbmonitor-qb-01 10:58: [trswcmtest]> show warnings;+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1276 | Field or reference 'trswcmtest.WCMMetaTableWebSite.MetaDataId' of SELECT #2 was resolved in SELECT #1 || Note | 1003 | /* select#1 */ select `trswcmtest`.`wcmmetatablewebsite`.`MetaDataId` AS `MetaDataId` from `trswcmtest`.`wcmmetatablewebsite` where exists(/* select#2 */ select `trswcmtest`.`wcmchnldoc`.`DOCID` from `trswcmtest`.`wcmchnldoc` where ((`trswcmtest`.`wcmchnldoc`.`CHNLID` = 11224) and (`trswcmtest`.`wcmchnldoc`.`DOCSTATUS` = 10) and (`trswcmtest`.`wcmchnldoc`.`DOCKIND` = 2) and (`trswcmtest`.`wcmchnldoc`.`MODAL` >= 1) and (`trswcmtest`.`wcmchnldoc`.`DOCID` = `trswcmtest`.`wcmmetatablewebsite`.`MetaDataId`))) order by `trswcmtest`.`wcmmetatablewebsite`.`IssueTime` desc |+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)
从执行计划看，驱动表选择的是wcmmetatablewebsite，该表数据量为63093，而被驱动表为wcmchnldoc，加上谓词条件的结果集要远小于结果集wcmmetatablewebsite，并且可以看到表wcmmetatablewebsite为全表扫描，效率非常低，这一点其实从后面的profile的结果也可以看出来
三、改写后SQL
root@dbmonitor-qb-01 10:58: [trswcmtest]> desc SELECT w.MetaDataId FROM WCMMetaTableWebSite w INNER JOIN WCMChnlDoc d ON w.MetaDataId=DocId WHERE d.Modal>=1 AND d.docKind =2 AND d.DOCSTATUS IN(10) AND d.ChnlId=11224 ORDER BY issuetime DESC;+----+-------------+-------+------------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+---------+--------------------+------+----------+----------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+---------+--------------------+------+----------+----------------------------------------------+| 1 | SIMPLE | d | NULL | ref | IX_WCMCHNLDOC_CHNL,IX_WCMCHNLDOC_CHNL_DOC,IX_WCMCHNLDOC_CHNL_ORDER,IX_WCMCHNLDOC_DK_ST_CI,IX_WCMCHNLDOC_DOCKIND,IX_WCMCHNLDOC_DS_CHNL,IX_WCMCHNLDOC_DOCID_MODAL,IX_WCMChnlDoc_OperTimeChnlStatus,IX_WCMChnlDoc_CrUserStatus,IX_WCMChnlDoc_SiteIdStatus,IX_WCMChnlDoc_PubTimeChnlStatus,IX_WCMChnlDoc_CrUserSiteStatus,IX_WCMChnlDoc_FullSite | IX_WCMCHNLDOC_DK_ST_CI | 13 | const,const,const | 3 | 33.33 | Using where; Using temporary; Using filesort || 1 | SIMPLE | w | NULL | ref | IX_WebSite_282457558 | IX_WebSite_282457558 | 5 | trswcmtest.d.DOCID | 1 | 100.00 | NULL |+----+-------------+-------+------------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+---------+--------------------+------+----------+----------------------------------------------+2 rows in set, 1 warning (0.00 sec)root@dbmonitor-qb-01 10:58: [trswcmtest]> show warnings;+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1003 | /* select#1 */ select `trswcmtest`.`w`.`MetaDataId` AS `MetaDataId` from `trswcmtest`.`wcmmetatablewebsite` `w` join `trswcmtest`.`wcmchnldoc` `d` where ((`trswcmtest`.`w`.`MetaDataId` = `trswcmtest`.`d`.`DOCID`) and (`trswcmtest`.`d`.`CHNLID` = 11224) and (`trswcmtest`.`d`.`DOCSTATUS` = 10) and (`trswcmtest`.`d`.`DOCKIND` = 2) and (`trswcmtest`.`d`.`MODAL` >= 1)) order by `trswcmtest`.`w`.`IssueTime` desc |+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
改写后的执行计划看到驱动表由wcmmetatablewebsite变为了wcmchnldoc，并且都用到了索引，小结果集驱动大结果集
四、profile结果
root@dbmonitor-qb-01 11:00: [trswcmtest]> show profiles;+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Query_ID | Duration | Query |+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| 1 | 1.29911175 | select WCMMetaTableWebSite.MetaDataId from WCMMetaTableWebSite where exists(select WCMChnlDoc.DocId from WCMChnlDoc where WCMChnlDoc.ChnlId>=1 and WCMChnlDoc.Modal>=1 and WCMChnlDoc.DocId=WCMMetaTableWebSite.MetaDataId and WCMChnlDoc.docKind =2 and WCMChnlDoc.DOCSTATUS in(10) and WCMChnlDoc.ChnlId= || 2 | 0.00011800 | show profilings || 3 | 0.00022575 | set profiling=0l || 4 | 1.34196650 | select WCMMetaTableWebSite.MetaDataId from WCMMetaTableWebSite where exists(select WCMChnlDoc.DocId from WCMChnlDoc where WCMChnlDoc.ChnlId>=1 and WCMChnlDoc.Modal>=1 and WCMChnlDoc.DocId=WCMMetaTableWebSite.MetaDataId and WCMChnlDoc.docKind =2 and WCMChnlDoc.DOCSTATUS in(10) and WCMChnlDoc.ChnlId= |+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+4 rows in set, 1 warning (0.00 sec)root@dbmonitor-qb-01 11:01: [trswcmtest]> show profile for query 4;+--------------------+----------+| Status | Duration |+--------------------+----------+| executing | 0.000007 || Sending data | 0.000018 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000008 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000021 || Sending data | 0.000019 || executing | 0.000007 || Sending data | 0.000040 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000010 || executing | 0.000007 || Sending data | 0.000012 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000016 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000017 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000019 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000011 || executing | 0.000007 || Sending data | 0.000029 || executing | 0.000007 || Sending data | 0.000012 || executing | 0.000007 || Sending data | 0.000013 || executing | 0.000007 || Sending data | 0.000035 || end | 0.000009 || query end | 0.000014 || closing tables | 0.000015 || freeing items | 0.000027 || logging slow query | 0.000060 || cleaning up | 0.000020 |+--------------------+----------+100 rows in set, 1 warning (0.00 sec)root@dbmonitor-qb-01 11:01: [trswcmtest]> SELECT w.MetaDataId FROM WCMMetaTableWebSite w LEFT JOIN WCMChnlDoc d ON w.MetaDataId=DocId WHERE d.Modal>=1 AND d.docKind =2 AND d.DOCSTATUS IN(10) AND d.ChnlId=11224 ORDER BY issuetime DESC;+------------+| MetaDataId |+------------+| 736271 || 736261 || 735298 |+------------+3 rows in set (0.00 sec)root@dbmonitor-qb-01 11:01: [trswcmtest]> show profiles;+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Query_ID | Duration | Query |+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| 1 | 1.29911175 | select WCMMetaTableWebSite.MetaDataId from WCMMetaTableWebSite where exists(select WCMChnlDoc.DocId from WCMChnlDoc where WCMChnlDoc.ChnlId>=1 and WCMChnlDoc.Modal>=1 and WCMChnlDoc.DocId=WCMMetaTableWebSite.MetaDataId and WCMChnlDoc.docKind =2 and WCMChnlDoc.DOCSTATUS in(10) and WCMChnlDoc.ChnlId= || 2 | 0.00011800 | show profilings || 3 | 0.00022575 | set profiling=0l || 4 | 1.34196650 | select WCMMetaTableWebSite.MetaDataId from WCMMetaTableWebSite where exists(select WCMChnlDoc.DocId from WCMChnlDoc where WCMChnlDoc.ChnlId>=1 and WCMChnlDoc.Modal>=1 and WCMChnlDoc.DocId=WCMMetaTableWebSite.MetaDataId and WCMChnlDoc.docKind =2 and WCMChnlDoc.DOCSTATUS in(10) and WCMChnlDoc.ChnlId= || 5 | 0.00132675 | SELECT w.MetaDataId FROM WCMMetaTableWebSite w LEFT JOIN WCMChnlDoc d ON w.MetaDataId=DocId WHERE d.Modal>=1 AND d.docKind =2 AND d.DOCSTATUS IN(10) AND d.ChnlId=11224 ORDER BY issuetime DESC |+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+5 rows in set, 1 warning (0.00 sec)root@dbmonitor-qb-01 11:01: [trswcmtest]> show profile for query 5;+----------------------+----------+| Status | Duration |+----------------------+----------+| starting | 0.000119 || checking permissions | 0.000020 || checking permissions | 0.000020 || Opening tables | 0.000036 || init | 0.000055 || System lock | 0.000027 || optimizing | 0.000049 || statistics | 0.000518 || preparing | 0.000039 || Creating tmp table | 0.000042 || Sorting result | 0.000021 || executing | 0.000018 || Sending data | 0.000131 || Creating sort index | 0.000045 || end | 0.000019 || query end | 0.000025 || removing tmp table | 0.000023 || query end | 0.000018 || closing tables | 0.000024 || freeing items | 0.000036 || cleaning up | 0.000044 |+----------------------+----------+21 rows in set, 1 warning (0.00 sec)
小小总结一下：
使用MySQL的过程中，尽量用关联代替子查询提高效率，尤其是更新或者删除数据时少用子查询包括exsits，一定要用关联来代替
若有收获，就点个赞吧
0 人点赞
