文档章节

MySQL的一次优化记录 (IN子查询和索引优化)

o
 osc_wws45aot
发布于 2019/08/20 00:58
字数 1058
阅读 9
收藏 0

精选30+云产品,助力企业轻松上云!>>>

这两天实习项目遇到一个网页加载巨慢的问题(10多秒),然后定位到是一个MySQL查询特别慢的语句引起的:

SELECT *
FROM (
	SELECT DISTINCT t.vc_date, t.c_bankno, t.vc_bankacco, t.vc_moneytype, t.en_totalbala
		, t.en_usablebala, t1.vc_nameinbank, date_format(t.D_IMPORTTIME, '%Y-%m-%d %H:%i:%S') AS D_IMPORTTIME
		, t.vc_fundcode, t.c_datamode, t.vc_taskid, t.id, t.vc_projectname
		, t.vc_projectcode, t1.c_accotype
		, (
			SELECT IF(vc_occurtime IS NULL, DATE_FORMAT(vc_occurdate, '%Y-%m-%d'), DATE_FORMAT(CONCAT(vc_occurdate, vc_occurtime), '%Y-%m-%d %H:%i:%S')) AS tradeTime
			FROM tbanktradedetail_view
			WHERE vc_bankacco = t.vc_bankacco
		) AS d_tradetime, t3.vc_entry_caption AS C_ACCOTYPE_STR, t5.vc_entry_caption AS VC_BANKNAME, t4.vc_entry_caption AS VC_MONEYTYPE_STR
	FROM tbankaccobala t
		INNER JOIN tbankaccoinfo t1 ON t.vc_bankacco = t1.vc_bankacco
		INNER JOIN (
			SELECT vc_entry_value, vc_entry_caption
			FROM ot_dic_tdictionaryentry
			WHERE vc_entry_no = '5087'
		) t3
		ON t1.c_accotype = t3.vc_entry_value
		INNER JOIN (
			SELECT vc_entry_value, vc_entry_caption
			FROM ot_dic_tdictionaryentry
			WHERE vc_entry_no = '1004'
		) t4
		ON t1.VC_MONEYTYPE = t4.vc_entry_value
		INNER JOIN (
			SELECT vc_entry_value, vc_entry_caption
			FROM ot_dic_tdictionaryentry
			WHERE vc_entry_no = '1014'
		) t5
		ON t.c_bankno = t5.vc_entry_value
	WHERE 1 = 1
		AND t.id IN (
            --   this query will take 4.6s:
			-- SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY d_importtime DESC), ',', 1)
			-- FROM tbankaccobala
			-- GROUP BY vc_bankacco
            -- but the following query only takes 1.1s:
            SELECT hhhh from(
                SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY d_importtime DESC), ',', 1) as hhhh
                FROM tbankaccobala
                GROUP BY vc_bankacco
            ) as sbstr
            -- 对IN的子查询做二次查询
		)
) t
WHERE 1 = 1
ORDER BY t.D_IMPORTTIME DESC

抽出查询慢关键部分:

SELECT *
FROM (
	SELECT DISTINCT t.vc_date, t.c_bankno, t.vc_bankacco, t.vc_moneytype, t.en_totalbala
        -- 此处省略选择多个列语句
	FROM tbankaccobala t
        -- 此处省略多张表连表查询语句
	WHERE 1 = 1
		AND t.id IN (
            --   这个查询需要3s:
			SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY d_importtime DESC), ',', 1)
			FROM tbankaccobala
			GROUP BY vc_bankacco
		)
) t

这个语句导致前端页面10多秒才有响应(但MySQL执行显示要4.6秒,phpMyAdmin也是10秒左右响应,为何?)

IN子查询语句优化

把IN语句里面的内容改成下面这样,只在外层再加一个select,就把3s的查询缩短为0.006s:

            SELECT hhhh from(
                SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY d_importtime DESC), ',', 1) as hhhh
                FROM tbankaccobala
                GROUP BY vc_bankacco
            ) as sbstr
            -- 对IN的子查询做二次select,或者把IN改为JOIN都可以解决速度奇慢的问题

原语句空行处省略了一系列的其他表和 INNER JOIN 语句。一开始怀疑是多表的JOIN操作导致速度变慢,但删去JOIN变成上面这段注释掉的语句之后,速度依然非常慢,显示要3s,于是猜测 IN 才是导致速度变慢的主要因素,改后只要0.006s,啧…

EXPLAIN 未优化的语句: (相关子查询是使用外部查询中的值的子查询)

EXPLAIN 优化的语句:

我的理解:优化前,子查询是相关子查询,对于外部产生的每个值,都要执行一次子查询;优化后,子查询不再是相关子查询,只需要执行一次子查询并缓存中间结果,外部查到的每个值去缓存的中间结果里比对一下就行了。

(有人说是能不能用索引的原因——这么说应该是不对的)

完整查询的后端响应速度对比: 前:

后:

索引优化

对于这么小的数据规模,时间还是太长了… 看前面explain执行计划的截图,嗯,没有索引… 给t1的vc_bankacco加上索引之后 解释执行计划: 查询和网页响应用时大幅缩短:

再看sql里还有三个join: 用的都是ot_dic_tdictionaryentry这张表的t4.vc_entry_value字段,那么试着给这个字段也加上索引吧,然后用时如下: 是的,时间反而变长了

explain执行计划:

所以变慢原因是:

没加vc_entry_value的索引时,会先用vc_entry_no选出一个数量很小的表,再和t1做join,

而加了vc_entry_value的索引之后,MySQL就把这个索引用了起来,join语句被优化为先FirstMatch(ot_dic_tdictionaryentry),这产生了一个1713*1713=2934369行的中间结果(笛卡尔乘积),然后才使用vc_entry_no进行where过滤。

所以索引不能乱加啊,加错了反而会导致性能下降!这个示例里的查询要加索引只能在vc_entry_no上索引,而不能在vc_entry_value上!

<br> 这个示例中主要提升是IN子查询语句的优化。在使用索引的情况下,对IN子查询做优化前后的查询时间分别是3.1s和0.16s

深入理解MySql子查询IN的执行和优化 多个单列索引和联合索引的区别详解

o
粉丝 0
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。
MySQL性能优化速记

MySQL性能优化速记http://www.bieryun.com/3064.html 总结自《MySQL 5.7从入门到精通(视频教学版)》刘增杰编著。 优化简介 MySQL数据库优化是多方面的,原则是减少系统的瓶颈,减少资源的占...

优惠码发放
2018/07/02
0
0
MySQL性能优化速记

MySQL性能优化速记 总结自《MySQL 5.7从入门到精通(视频教学版)》刘增杰编著。 优化简介 MySQL数据库优化是多方面的,原则是减少系统的瓶颈,减少资源的占用,增加系统的反应速度。 在MyS...

推荐码发放
2018/04/14
0
0
MySQL查询性能优化

本文为《高性能MySQL》读书笔记 慢查询基础:优化数据访问 查询性能低下最基本的原因是访问的数据太多。对于低效的查询,我们发现通过下面两个步骤来分析总是很有效的: 确认应用程序是否在检...

osc_h3robkrt
2018/02/27
2
0
查询性能优化

为什么查询速度会变慢 真正重要的是响应时间,如果把查询看作是一个任务,那么它由一系列子任务组成,每个子任务都会消耗一定的时间。如果要优化查询,实际上要优化其子任务,要么减少子任务...

KafkaPlus
2019/07/12
1
0
MySQL查询性能优化(转)

MySQL查询性能优化 1.为什么查询速度为变慢   在尝试编写快速的查询之前,需要清除一点,真正重要是响应时间。如果把查询看作是一个任务,那么他由一系列子任务组成,每个子任务都会消耗一定...

osc_r1i0f044
2019/10/09
1
0

没有更多内容

加载失败,请刷新页面

加载更多

百度技术沙龙第67期 百度开源专场

本文作者:HelloDeveloper 具体的产品案例,分享百度开源技术最新实践经验。目前这些项目都已经在 github/baidu 上开源。 什么是 PaddlePaddle 深度学习平台? 首先做个简单的介绍,PaddleP...

百度开发者中心
2019/07/23
0
0
Hacker News 简讯 2020-07-10

更新时间: 2020-07-10 01:15 US Supreme Court deems half of Oklahoma a Native American Reservation - (reuters.com) 美国最高法院认为俄克拉荷马州的一半是印第安人保留地 得分:131 | 评...

FalconChen
今天
26
0
OSChina 周五乱弹 —— 求求你吃了我吧,不要再玩弄食物的感情了

Osc乱弹歌单(2020)请戳(这里) 【今日歌曲】 @巴拉迪维 :张喆的单曲《陷阱 》 这首歌已经在网易找不到原唱了,不知道被哪家买了版权。#今日歌曲推荐# 《陷阱 》- 张喆 手机党少年们想听歌...

小小编辑
今天
26
1
清华陈文光教授:AI 超算基准测试的最新探索和实践。

道翰天琼认知智能平台为您揭秘新一代人工智能。 无规矩不成方圆。放在超级计算机的研发领域,没有一个大家普遍接受的算力评测指标,便难以推动超算迅猛发展。 而现在伴随着人工智能的发展,大...

jackli2020
今天
7
0
@RequestMapping, consumes 提交简单有意思的测试

getParm @GetMapping("getParm")public Result getParm(String id){ System.out.println(); return ResultFactory.success(id);} 等同于 == bodyParm @PostMapping("bodyParm......

莫库什勒
今天
25
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部