IT博客汇 | [原]MySQL 5.7 优化SQL提升100倍执行效率的深度思考

[原]MySQL 5.7 优化SQL提升100倍执行效率的深度思考

mchdba发表于 2016-11-26 20:46:57

系统环境：微软云Linux DS12系列、Centos6.5 、MySQL 5.7.10、生产环境

1、慢sql语句大概需要13秒

原来的sql语句要13秒，sql如下：

SELECT

(SELECT

COUNT(*)

FROM

TB_BIS_POS_DEVICE t1,

TB_BIS_MERCHANT t2

WHERE t1.`PROJECT_ID` = '1024'

AND t1.MERCHANT_ID = t2.ID

AND t2.SPACE_ID = 'DE907E67FB9B487FA762E6E9B795072A') AS '安装',

(SELECT

COUNT(*)

FROM

TB_BIS_POS_DEVICE t1,

TB_BIS_POS_HEARTBEAT t2,

TB_BIS_MERCHANT t3

WHERE t1.`PROJECT_ID` = '1024'

AND t1.MERCHANT_ID = t3.ID

AND t3.SPACE_ID = 'DE907E67FB9B487FA762E6E9B795072A'

AND t1.`ID` = t2.`DEVICE_ID`

AND t2.ENABLED = 1) AS '在线',

(SELECT

COUNT(DISTINCT (t1.`SN`))

FROM

TB_BIS_POS_DEVICE t1,

TB_BIS_POS_ORDER t2,

TB_BIS_MERCHANT t3

WHERE t1.`PROJECT_ID` = '1024'

AND t1.MERCHANT_ID = t3.ID

AND t3.SPACE_ID = 'DE907E67FB9B487FA762E6E9B795072A'

AND t1.ID = t2.`DEVICE_ID`) AS '连通',

(SELECT

COUNT(*)

FROM

TB_BIS_POS_DEVICE t1,

TB_BIS_MERCHANT t2

WHERE t1.`PROJECT_ID` = '1024'

AND t1.MERCHANT_ID = t2.ID

AND t2.SPACE_ID = 'DE907E67FB9B487FA762E6E9B795072A'

AND exists( select 1 )

AND t1.ID IN

(SELECT

t2.`DEVICE_ID`

FROM

TB_BIS_POS_ORDER t2

WHERE t2.`CREATE_DATE` >= DATE_FORMAT(NOW(), '%Y-%m-%d'))

AND t1.ID NOT IN

(SELECT

t2.`DEVICE_ID`

FROM

TB_BIS_POS_ORDER t2

WHERE t2.`CREATE_DATE` <= DATE_FORMAT(NOW(), '%Y-%m-%d'))

) AS '今日连通',

(SELECT

COUNT(DISTINCT (t1.`SN`))

FROM

TB_BIS_POS_DEVICE t1,

TB_BIS_POS_ORDER t2,

TB_BIS_MERCHANT t3

WHERE t1.`PROJECT_ID` = '1024'

AND t1.MERCHANT_ID = t3.ID

AND t3.SPACE_ID = 'DE907E67FB9B487FA762E6E9B795072A'

AND t1.ID = t2.`DEVICE_ID`

AND UNIX_TIMESTAMP(t2.CREATE_DATE) >= UNIX_TIMESTAMP(NOW()) - 60 * 60 * 2) AS '正常交易',

(SELECT

COUNT(*)

FROM

TB_BIS_POS_DEVICE t1,

TB_BIS_POS_ORDER t2,

TB_BIS_MERCHANT t3

WHERE t1.`PROJECT_ID` = '1024'

AND t1.MERCHANT_ID = t3.ID

AND t3.SPACE_ID = 'DE907E67FB9B487FA762E6E9B795072A'

AND t1.ID = t2.`DEVICE_ID`) AS '交易共计',

(SELECT

COUNT(*)

FROM

TB_BIS_POS_DEVICE t1,

TB_BIS_POS_ORDER t2,

TB_BIS_MERCHANT t3

WHERE t1.`PROJECT_ID` = '1024'

AND t1.MERCHANT_ID = t3.ID

AND t3.SPACE_ID = 'DE907E67FB9B487FA762E6E9B795072A'

AND t1.ID = t2.`DEVICE_ID`

AND t2.`CREATE_DATE` >= DATE_FORMAT(NOW(), '%Y-%m-%d')) AS '今日产生'

FROM

DUAL ;

2、优化后提升100倍，只要0.09秒

和开发人员熟悉了业务之后，优化成如下，从13秒到0.09秒，效率提升了100多倍。

采用如下3种策略提升百倍效率，如下

/*（1）内连接+distinct效率低下，换成exists高效*/

/*（2）IN不走索引，优化成EXISTS如下*/

/*（3）字段不能做函数处理，不然不走索引，优化成如下*/

SELECT sql_no_cache

(

SELECT

COUNT(*)

FROM

TB_BIS_POS_DEVICE t1,

TB_BIS_MERCHANT t2

WHERE t1.`PROJECT_ID` = '1024'

AND t1.MERCHANT_ID = t2.ID

AND t2.SPACE_ID = 'DE907E67FB9B487FA762E6E9B795072A'

) AS '安装',

(

SELECT

COUNT(*)

FROM

TB_BIS_POS_DEVICE t1,

TB_BIS_POS_HEARTBEAT t2,

TB_BIS_MERCHANT t3

WHERE t1.`PROJECT_ID` = '1024'

AND t1.MERCHANT_ID = t3.ID

AND t3.SPACE_ID = 'DE907E67FB9B487FA762E6E9B795072A'

AND t1.`ID` = t2.`DEVICE_ID`

AND t2.ENABLED = 1

) AS '在线',

(

SELECT

COUNT(DISTINCT (t1.`SN`))

FROM

TB_BIS_POS_DEVICE t1,

TB_BIS_POS_ORDER t2,

TB_BIS_MERCHANT t3

WHERE t1.`PROJECT_ID` = '1024'

AND t1.MERCHANT_ID = t3.ID

AND t3.SPACE_ID = 'DE907E67FB9B487FA762E6E9B795072A'

AND t1.ID = t2.`DEVICE_ID`*/

/*（1）内连接+distinct效率低下，换成exists高效*/

SELECT

COUNT(t1.`SN`)

FROM

TB_BIS_POS_DEVICE t1

WHERE t1.`PROJECT_ID` = '1024'

AND EXISTS(SELECT 1 FROM TB_BIS_POS_ORDER t2 WHERE t1.ID = t2.`DEVICE_ID`)

AND EXISTS(SELECT 1 FROM TB_BIS_MERCHANT t3 WHERE t1.MERCHANT_ID = t3.ID AND t3.SPACE_ID = 'DE907E67FB9B487FA762E6E9B795072A')

) AS '连通',

(

SELECT

COUNT(*)

FROM

TB_BIS_POS_DEVICE t1,

TB_BIS_MERCHANT t2

WHERE t1.`PROJECT_ID` = '1024'

AND t1.MERCHANT_ID = t2.ID

AND t2.SPACE_ID = 'DE907E67FB9B487FA762E6E9B795072A'

AND exists( select 1 )

AND t1.ID IN

(SELECT

t2.`DEVICE_ID`

FROM

TB_BIS_POS_ORDER t2

WHERE t2.`CREATE_DATE` >= DATE_FORMAT(NOW(), '%Y-%m-%d'))

AND t1.ID NOT IN

(SELECT

t2.`DEVICE_ID`

FROM

TB_BIS_POS_ORDER t2

WHERE t2.`CREATE_DATE` <= DATE_FORMAT(NOW(), '%Y-%m-%d'))

/*（2）IN不走索引，优化成EXISTS如下*/

SELECT

COUNT(*)

FROM

TB_BIS_POS_DEVICE t1,

TB_BIS_MERCHANT t2

WHERE t1.`PROJECT_ID` = '1024'

AND t1.MERCHANT_ID = t2.ID

AND t2.SPACE_ID = 'DE907E67FB9B487FA762E6E9B795072A'

AND EXISTS( SELECT 1 FROM TB_BIS_POS_ORDER t3 WHERE t3.`CREATE_DATE` >= DATE_FORMAT(NOW(), '%Y-%m-%d') AND t3.`DEVICE_ID`=t1.`ID`)

) AS '今日连通',

(

SELECT

COUNT(DISTINCT (t1.`SN`))

FROM

TB_BIS_POS_DEVICE t1,

TB_BIS_POS_ORDER t2,

TB_BIS_MERCHANT t3

WHERE t1.`PROJECT_ID` = '1024'

AND t1.MERCHANT_ID = t3.ID

AND t3.SPACE_ID = 'DE907E67FB9B487FA762E6E9B795072A'

AND t1.ID = t2.`DEVICE_ID`

/*AND UNIX_TIMESTAMP(t2.CREATE_DATE) >= UNIX_TIMESTAMP(NOW()) - 60 * 60 * 2*/

/*（3）字段不能做函数处理，不然不走索引，优化成如下*/

AND t2.CREATE_DATE >= DATE_ADD(NOW(),INTERVAL 2 HOUR)

) AS '正常交易',

(

SELECT

COUNT(*)

FROM

TB_BIS_POS_DEVICE t1,

TB_BIS_POS_ORDER t2,

TB_BIS_MERCHANT t3

WHERE t1.`PROJECT_ID` = '1024'

AND t1.MERCHANT_ID = t3.ID

AND t3.SPACE_ID = 'DE907E67FB9B487FA762E6E9B795072A'

AND t1.ID = t2.`DEVICE_ID`

) AS '交易共计',

(

SELECT

COUNT(*)

FROM

TB_BIS_POS_DEVICE t1,

TB_BIS_POS_ORDER t2,

TB_BIS_MERCHANT t3

WHERE t1.`PROJECT_ID` = '1024'

AND t1.MERCHANT_ID = t3.ID

AND t3.SPACE_ID = 'DE907E67FB9B487FA762E6E9B795072A'

AND t1.ID = t2.`DEVICE_ID`

AND t2.`CREATE_DATE` >= DATE_FORMAT(NOW(), '%Y-%m-%d')

) AS '今日产生'

FROM

DUAL ;

3、SQL优化准则：小结果集驱动大结果集

大家遇到相似的，可以借鉴下，当然还有其它的情况，也需要注意，接下来说下在机械磁盘的时代浪潮里面，优化必须要遵守的一大准则à用小结果集驱动大结果集

永远用小的结果集驱动大的结果集

很多看过数据库开发指南或者听过某某大师网络课程的开发人缘，喜欢在优化 SQL 的时候使用小表驱动大表，在在很多时候有效，但是并不是100%有效，必须看实际场景，主要是因为大表经过 WHERE 条件过滤之后返回的结果集并不一定就比小表所返回的大，也许更小。在这种情况下如果仍然采用小表驱动大表，就会得到相反的性能效果。

bty：他们说的用小表驱动大表只是为了让开发人员方便记忆方便理解，但是开发人员不能死抱这个不放，需要理解深层次的原因。

因为在MySQL中，只有 Nested Loop 一种 Join 方式，也就是说MySQL的 Join 都是通过嵌套循环来实现的。驱动结果集越大，所需要循环就越多，那么被驱动表的访问次数自然也就越多，而每次访问被驱动表，即使需要的逻辑 IO 很少，循环次数多了，总量也不可能小，而且每次循环都不能避免消耗CPU，所以 CPU 运算量也会跟着增加。如果仅仅以表的大小来作为驱动表的判断依据，假若小表过滤后所剩下的结果集比大表多很多，结果就会在嵌套循环中带来更多的循环次数，这种情况小勇大表驱动小表就是低效率了（因为根据在机械磁盘的时代里面，IO是最大瓶颈，减少IO量就是提升sql效率，增加IO就意味增加cpu消耗，就意味着效率低下），反之，所需要的循环次数就会更少，总体 IO 量和 CPU 运算量也会更少。

而在非 Nested Loop 的 Join 算法中，比如 Oracle 中的 Hash Join，就不是以表大小来决定，而是以结果集来决定，所以以小结果集驱动大的结果集同样是最优的选择。

所以，在优化数据库Join Query 的时候，不管是MySQL还是Oracle等，最基本的原则就是“用小结果集驱动大结果集”，通过这个原则来减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数，如下SQL模板所示：

SELECT t1.c1,t2.c2 FROM 小结果集 AS t1 LEFT JOIN 大结果集 AS t2 ON t1.id=t2.cid WHERE t1.created_time > ‘2016-10-13’ AND t1.is_del=’0’ AND t2.project_id=’XJ160603’ and ……;