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，永远遵循小结果集驱动大结果集的原则。