mysql5.7 提高写效率_MySQL 5.7 优化SQL提升100倍执行效率的深度思考

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 ;