MySQL 5.7 优化SQL提升100倍执行效率的深度思考
来源:互联网 发布:淘宝客如何推广才挣钱 编辑:程序博客网 时间:2024/05/29 10:04
系统环境:微软云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
- MySQL 5.7 优化SQL提升100倍执行效率的深度思考
- 优化sql语句,提升程序执行效率
- SQL优化语句提升执行效率
- [SQL]提升SQL执行效率诀窍2-优化SQL语句
- 缩小包围圈,mysql效率提升千万倍
- MySql批量插入优化Sql执行效率
- 优化临时表使用,SQL语句性能提升100倍
- 优化临时表使用,SQL语句性能提升100倍
- 优化临时表使用 SQL语句性能提升100倍
- 优化临时表使用,SQL语句性能提升100倍
- 优化临时表使用,SQL语句性能提升100倍
- 优化临时表使用,SQL语句性能提升100倍
- 优化临时表使用,SQL语句性能提升100倍
- 优化临时表使用,SQL语句性能提升100倍
- 优化临时表使用,SQL语句性能提升100倍
- “整数类.到文本()”执行效率提升至原来的2.5倍以上
- 关于提升效率的思考
- 关于SQL执行效率的优化
- iptables: unrecognized service 腾讯 云服务器
- 关于毕设,读paper《XXX》的一些小笔记
- 数据结构——不用栈、不用递归中序遍历树
- AndroidStudio新建布局文件,R文件不能生成起控件id的问题
- 条件编译
- MySQL 5.7 优化SQL提升100倍执行效率的深度思考
- 救济金发送
- direct x基础 二
- js-保留两位小数,取后缀名
- 玲珑学院 1058 - Coco
- 小程序的影响到底有多大?
- 小工具-BBQScreen
- 选择排序
- 无序列表排列导航-横排排列-颜色变化