一次mysql的sql优化

先上原sql语句（萌新写的），

四张表，TPS_QMCH 5万左右数据，TPS_WWFCT表 5万，TPS_XMCH20万，ST_LPM50万
执行时间30秒

SELECT
    	M.*
    FROM
    	(
    		SELECT DISTINCT
    			N.ID,
    			N.qxdm,
    			N.ywslid,
    			N.xmdz,
    			N.kfqymc,
    			N.chdwmc,
    			N.rksj,
    			n.xmmc
    		FROM
    			(
    				SELECT
    					qmch.ID,
    					lpm.qxdm,
    					lpm.ZRZZL,
    					qmch.ywslid,
    					qmch.xmdz,
    					qmch.kfqymc,
    					qmch.chdwmc,
    					qmch.rksj,
    					qmch.xmmc
    				FROM
    					TPS_QMCH qmch,
    					ST_LPM lpm
    				WHERE
    					qmch.ID = lpm.chid
    				AND lpm.JCNF = '2016'
    				AND qmch.ID IN (
    					SELECT
    						qmchid
    					FROM
    						TPS_WWFCT
    					WHERE
    						lsbz = 0
    				)
    				AND qmch.ID IN (
    					SELECT
    						lpm.chid
    					FROM
    						ST_LPM lpm
    					WHERE
    						lpm.zrzid IN (
    							SELECT
    								zrzid
    							FROM
    								TPS_XMCH xmch
    							WHERE
    								xmch.bjbz = 1
    							AND xmch.sffb = 1
    						)
    				)
    			) N
    	) M
    WHERE
    	1 = 1
    AND M.QXDM IS NOT NULL
    AND M.xmmc IS NOT NULL
    ORDER BY
    	M.rksj DESC

一. 第一步

第一反应，肯定有关联字段没有加索引
看执行计划

type全是all，看rows基本上是全表扫描，看extra没有用到索引
在ST_LPM表的chid和zrzid上加上索引，执行时间缩短到2秒
再看执行计划

可以看到type出现ref，rows的扫描行数也急剧减少

二. 第二步，

再仔细观察sql，多处用了in关键字，其中还有嵌套，肯定会有性能问题
我们先看下mysql帮我们自动优化后的语句长什么样

  /* select#1 */
    SELECT
    	`m`.`ID` AS `ID`,
    	`m`.`qxdm` AS `qxdm`,
    	`m`.`ywslid` AS `ywslid`,
    	`m`.`xmdz` AS `xmdz`,
    	`m`.`kfqymc` AS `kfqymc`,
    	`m`.`chdwmc` AS `chdwmc`,
    	`m`.`rksj` AS `rksj`,
    	`m`.`xmmc` AS `xmmc`
    FROM
    	(
    		/* select#2 */
    		SELECT DISTINCT
    			`oracle2mysql`.`qmch`.`ID` AS `ID`,
    			`oracle2mysql`.`lpm`.`QXDM` AS `qxdm`,
    			`oracle2mysql`.`qmch`.`YWSLID` AS `ywslid`,
    			`oracle2mysql`.`qmch`.`XMDZ` AS `xmdz`,
    			`oracle2mysql`.`qmch`.`KFQYMC` AS `kfqymc`,
    			`oracle2mysql`.`qmch`.`CHDWMC` AS `chdwmc`,
    			`oracle2mysql`.`qmch`.`RKSJ` AS `rksj`,
    			`oracle2mysql`.`qmch`.`XMMC` AS `xmmc`
    		FROM
    			`oracle2mysql`.`tps_qmch` `qmch` semi
    		JOIN (
    			`oracle2mysql`.`st_lpm` `lpm`
    			JOIN `oracle2mysql`.`tps_xmch` `xmch`
    		) semi
    		JOIN (`oracle2mysql`.`tps_wwfct`)
    		JOIN `oracle2mysql`.`st_lpm` `lpm`
    		WHERE
    			(
    				(
    					`oracle2mysql`.`lpm`.`ZRZID` = `oracle2mysql`.`xmch`.`ZRZID`
    				)
    				AND (
    					`oracle2mysql`.`tps_wwfct`.`LSBZ` = 0
    				)
    				AND (
    					`oracle2mysql`.`xmch`.`SFFB` = 1
    				)
    				AND (
    					`oracle2mysql`.`xmch`.`BJBZ` = 1
    				)
    				AND (
    					`oracle2mysql`.`lpm`.`JCNF` = 2016
    				)
    				AND (
    					`oracle2mysql`.`qmch`.`ID` = `<subquery4>`.`qmchid`
    				)
    				AND (
    					`<subquery5>`.`chid` = `<subquery4>`.`qmchid`
    				)
    				AND (
    					`oracle2mysql`.`lpm`.`CHID` = `<subquery4>`.`qmchid`
    				)
    			)
    	) `m`
    WHERE
    	(
    		(`m`.`qxdm` IS NOT NULL)
    		AND (`m`.`xmmc` IS NOT NULL)
    	)
    ORDER BY
    	`m`.`rksj` DESC |

可以看到mysql已经自动把in转化成join来实现，那么我们可以自己直接用join来替代in，减少mysql的自动优化，来提高效率
下面是用join改写最终的sql

SELECT
    	M.*
    FROM
    	(
    		SELECT DISTINCT
    			N.ID,
    			N.qxdm,
    			N.ywslid,
    			N.xmdz,
    			N.kfqymc,
    			N.chdwmc,
    			N.rksj,
    			n.xmmc
    		FROM
    			(
    				SELECT
    					qmch.ID,
    					lpm.qxdm,
    					lpm.ZRZZL,
    					qmch.ywslid,
    					qmch.xmdz,
    					qmch.kfqymc,
    					qmch.chdwmc,
    					qmch.rksj,
    					qmch.xmmc
    				FROM
    					ST_LPM lpm
    				JOIN TPS_QMCH qmch ON qmch.ID = lpm.chid
    				JOIN TPS_WWFCT WWFCT ON lpm.chid = WWFCT.qmchid
    				JOIN TPS_XMCH xmch ON lpm.zrzid = xmch.zrzid
    				WHERE
    					lpm.JCNF = '2016'
    				AND xmch.bjbz = 1
    				AND xmch.sffb = 1
    			) N
    	) M
    WHERE
    	1 = 1
    AND M.QXDM IS NOT NULL
    AND M.xmmc IS NOT NULL
    ORDER BY
    	M.rksj DESC

执行时间0.7秒，再看执行计划，就清爽了许多，执行阶段也变少了

三.第三步

其实最有效的lpm.JCNF字段上一直没有加索引，是为了之前的优化更加明显，加上索引后
执行时间0.1秒，再看执行计划

可以看到基本上都用的了索引，rows扫描很少，extra里面包含了Using index