10万级 多表 关联查询 优化问题

leondryu 发布于 2018/05/17 11:09
阅读 2K+
收藏 12

感谢各位前辈的回复。

这是对原SQL explain的结果,另外我把原问题中的表名改了,以适合这个图。

我想要查询的数据,主要集中在最上层的dp表,和最下层的dcb表,中间几个表没太多有用的数据,但是需要中间表做关联。

关系是这样的dp-->dpd-->dtsi-->dtsd-->dtc-->dcb。

-------------------这是Explain的结果-------------------------------

-------------------以下是原问题-----------------

我在业务中遇到一个历史遗留慢sql,想要优化一下。具体的sql这样的

select dp的四五个字段,dcb的很多个字段,

from dp

join dpd on dp.tdh = dpd.tdh

join dtsi ON dtsi.plan_id = dpd.id

join dtsd ON dtsd.pid = dtsi.id

join dtc.p_id = dtsd.id

join dcb.p_id = dtc.id

where dcb.sometimefield >= '2018-04-01' AND dcb.sometimefield <= '今天' AND (dp.somename = 'aaa' or dp.somecode = '某个code')

说明:1、TDH有索引。

2、每一个表id都是主键唯一。

3、dcb.sometimefield这个字段是一个datetime类型。有索引,但是感觉没太大作用。

4、dp表数据8k,dpd表数据8万,dtsi、dtsd、dtc都是10万,最后一个表dcb数据60万。

5、dp.somename,dp.somecode也有索引,也感觉没有太大作用。

现在的问题是这个查询要花费时间18秒左右。这个时间太长了,我想把时间缩短一半,或者更快,但是尝试了几次没什么作用。

第一种,纯粹的优化SQL,尽量不改SQL语句逻辑。

    1、将原来的数据表分区,使得每个区的数据都不超过10万?

实际上我没有将表分区,只是尝试把dcb表删除了50万数据做测试,发现似乎并没有提升多少速度。删数据后,第一次查询,速度大概稍微快一点点(没太大变化),然后重复查询,sql完全不改任何条件,在反复查询以后,耗时能减少到2~3秒但我觉得这显然不是因为优化的原因,而是因为反复查询了同一个sql,操作系统对数据访问做了“缓存”,可能是硬盘的缓存,因为我用show status like '%Qcache%'发现Qcache_hit一直都是0,使用show variables like '%cache%'查看query_cache_type是OFF。虽然反复查询同一条sql的时间降低了很多,但问题是,第一次查询的时间并没太大的变化

    如果不删除dcb表50万数据,那么不论多少次查询同一个sql(不改变条件),反复查询,速度最低降低到15秒,再降不下去了。

第二种,改变SQL的语句逻辑,重写SQL。

    1、单独执行select * from a where (dp.somename = 'aaa' or dp.somecode = '某个code'),耗时0.15秒,查询结果不到200条。

            单独执行select * from dcb where dcb的时间条件,耗时3.6秒,查询结果8万条。

请教一下数据库大神,这样的情形该怎么优化呢?请帮忙指点一下,感谢!

 

以下是问题补充:

@leondryu:我昨天发现在试验的时候,问了一下前同事,他说sql在服务器上(阿里云)运行速度并不是特别慢,我试了一下果然如此。在服务器上,首次运行,只需要4~5秒,但是我把同样的数据库,导入到我本地之后,同样的sql,需要18秒。我看了一下服务器没有开启缓存(配置的优化,我只知道缓存优化其他不知道)。怎么会相差这么多? 突然想到,我本地的MySQL是放在普通硬盘上的,服务器的MySQL莫非是在固态硬盘上? (2018/05/18 11:00)
@leondryu:然后我把数据库copy到一台mysql安装在ssd的电脑上,测试同一个SQL,首次执行,果然只需要4~5秒。 (2018/05/18 11:13)
@leondryu:从表结构上看,dp连接dpd,用的是双方的tdh字段,所以第一个连接是一个n:n。 从dpd开始一直到最终的dcb,关系都是1:n。 dpd-->dtsi(1:n) dtsi-->dtsd(1:n) dtsd-->dtc(1:n) dtc-->dcb(1:n)。 而且第一个表dp,如果加上条件的话,其实数据很少,就不到200条。 (2018/05/22 15:55)
加载中
0
乌龟壳
乌龟壳

查询优化是要根据数据的实际情况来做的。

而且为了深入数据库执行细节,必须先去了解执行计划相关的知识才能精确地调优而不是把SQL的写法换来换去去蒙。

建议你把执行计划发出来,这是最基本的,再谈优化。

乌龟壳
乌龟壳
回复 @leondryu : explain的结果
leondryu
leondryu
谢谢,请问你说的执行计划是指什么呢?是我想要查询的目的或结果吗?我想要的是a表的部分字段和f表的几乎全部字段。因为主要信息就在这两个表中。a->b->c...->f,这个表关联是一层层的关联,他们之间没有其他关联方式了。
0
Shazi199
Shazi199

发一个执行计划看看先

0
魔力猫
魔力猫

先发执行计划。我需要确认每个外键是否都有索引?A表如果只有200多条,那么作为驱动表是没问题的,但是你最后的F表sometimefield ,如果返回值太多,肯定不会走索引的。这种情况下,F表做时间分区是一个可以考虑的可能性,但是这要在分析完成执行计划之后。

最后,你删除是测试不了速度变化的。因为删除不代表数据真的没了,只是这部分被标记为不访问而已。表水线还是一样。除非你进行表压缩和行迁移来让数据结构紧凑。

leondryu
leondryu
dcb.gross_time是一个datetime字段。缩短时间范围到一定程度,explain单独这条SQL的type是range。速度能快一些。但这个时间范围是客户提的要求,不能缩短。现在就是不知道有没有什么办法能让datetime字段的查询更快。
leondryu
leondryu
dcb这个表确实比较大,而且问题是,这个表的时间范围条件是这样的,where dcb.gross_time >= '上个月一天' AND dcb.gross_time <= '今天'。时间范围是一个月。单独拿出这一个查询,耗时就有3秒多,explain这条语句显示是全表扫描。gross_time这个字段是一个datetime,我在上面建立索引了,但是在一个月时间范围的条件下(),索引没有用到。
leondryu
leondryu
谢谢你的回答。我不太明白执行计划是什么,是指explain的结果吗?
0
独孤晓林

执行条件查询做成临时表,在去join过滤数据。它需要join的数据会小很多

0
光脚满地跑
光脚满地跑

尝试将SQL逻辑拆分到业务上层呢?

比如在业务层先查询A表结果集,然后将结果和关联条件传递给B,然后将结果和关联条件传递给C……

中心思想就是先查询出较少的数据集,然后再从中进行进一步过滤。

最终的时间综合略大于各个单表查询的总和,应该也不慢。

楼上所说的执行计划,就是在你的sql语句之前写explain,将结果贴上来用于分析

比如:explain select * from table1

leondryu
leondryu
好的
0
呓语的风
呓语的风

最起码得用between and吧

0
乌龟壳
乌龟壳

给一下这个的结果

explain select * from dcb where dcb.sometimefield >= '2018-04-01' AND dcb.sometimefield <= '今天'

魔力猫
魔力猫
我估计也是这里太大了。如果太大系统自动会全表。
0
leondryu
leondryu

原问题好像不能编辑了。

@乌龟壳

这是select * from dcb where dcb.sometimefield >= '2018-04-01' AND dcb.sometimefield <= '今天'的结果

0
Q润客工作室
Q润客工作室

尽量不要去连接那么多张表,需要外键的话,可以适当冗余到一两张表这样再去连接查询

返回顶部
顶部