MySQL 5.7 优化SQL提升100倍执行效率的深度思考(GO)

系统环境:微软云Linux DS12系列、Centos6.5 、MySQL 5.7.10、生产环境,step1,step2是案例,精彩的剖析部分在step3,step4.

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 ……;

4、深度思考 IN ---- EXISTS

按照前面的小结果集驱动大结果集的规则,来深度解析下in和exists,in是把外表和内表作hash 连接,是以in子查询驱动外面的表集合,而exists 是对外表作loop 循环,每次loop 循环再对内表进行查询,以外表集合驱动exists子查询,这一点上in和exists是相反的。
 
所以一直以来dba会经常讲认为exists 比in 效率高的说法的前提是普通开发人员没有认识这么深刻,为了让开发人员容易理解,才这样粗鲁简单的说,而且一般子查询的结果集都会比外表要大,所以80%的情况下都适用。
 
但是数据库工程师要知道in和exists的根本核心区别,所以说,如果查询的两个表大小相当,那么用in 和exists 差别不大。如果外表集合和子查询集合中,一个较小,一个是较大,则子查询表集合大的用exists,子查询表集合小的用in,永远遵循小结果集驱动大结果集的原则。
上一篇:Python基础------列表,元组的调用方法


下一篇:第214天:Angular 基础概念