热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

一周工作总结--一次SQL优化记录

今天收到一个同事的问题,有一段SQL跑了很久很久,根本没有结果,根据同事的反映,这个SQL一个月比一个月要慢。这是不被允许的事情,我们要做的就是对这个SQL进行一次优化。下面就是这次优化的记

     今天收到一个同事的问题,有一段SQL跑了很久很久,根本没有结果,根据同事的反映,这个SQL一个月比一个月要慢。这是不被允许的事情,我们要做的就是对这个SQL进行一次优化。下面就是这次优化的记录。

     首先说SQL:

select t.month_id,
t1.area_id,
t1.local_id,
count(distinct case
when t.type_id = '02' and t.valid_flag = 1 and
t3.trade_id
= '1008601' then
t.
user_id
else
null
end),
count(distinct case
when t.type_id = '02' and t.valid_flag = 1 and
t3.trade_id
= '1008602' then
t.
user_id
else
null
end)
from product_flag_m t,
... --省略部分都是类似上面的运算,很多,为了节省篇幅都取消了
left join VW_CODE_LOCALNET t1
on t.local_id = t1.root_local_id
LEFT JOIN TRADE_LIST T3
ON T.id2 = T3.id2
AND T3.trade_id IN ('1008601', '1008602')
where t.month_id = '201212'
group by t.month_id, t1.area_id, t1.local_id;

      这段代码隐藏了敏感信息,可能会有一些修改的时候错漏的问题。

      接下来就是比较老的套路了,查看这段SQL的执行计划:

      

      这个时候可以初步判断是因为product_flag_m表太大造成的查询效率低下。既然只需要12月的数据,那么我自然而然的想到了将12月的分区压缩一下,利用压缩表的特点进行查询效率的提高。但是这是张生产表,不能随便操作,于是我就将12月份的type_id='02'的数据单独抽取出来形成一张新的表,当然这张表是压缩过的,而且我抽取的时候只抽取自己需要的字段,这样做的好处是尽量减少数据量,减轻数据库的负担。

     下面就是使用了压缩表之后的执行计划:

     

       可以看到COST是有所降低,但是这个和没有降低没什么区别。还是面临执行不出来的问题。

       这个时候我注意到了ID=2的这一部执行计划。在id=3的hash join right outer之后,不管是COST还是BYTES都是在一个比较正常的水平之内的,那么问题就应该出在TRADE_LIST这个表上。

       这个表是一张编码表,本身并不大,但是注意这里:

       

       上图所示应该就是罪魁了。于是我想到了,既然最后需要过滤一下trade_id,那么为什么不直接就用一张只有trade_id为1008601和1008602的表呢?

       于是我鬼使神差的建立了一张视图,这个视图就是只取了上面说的那么多数据,然后替换掉原来的SQL中的TRADE_LIST,删除了其中的

AND T3.trade_id IN ('1008601', '1008602') 语句,再看执行计划:

       

        这个效果就非常好了。

        我本身很担心这个视图用了以后会影响查询结果集。于是我自己造了一张表做了一个小测试。test3中有object_id为2, 3, 4, 5, 6, 7的记录,编码表中只有id为2, 3, 4, 5, 6的编码记录,SQL如下:

        

select t1.object_id, t2.id, t2.name
from test3 t1
left join test4 t2
on t1.object_id = t2.id
and t2.id in (2, 3);

       这个结果有48行。制造一个视图:

create view test5 as select * from test4 where id in (2, 3)

      然后替换成视图:

      

select t1.object_id, t2.id, t2.name
from test3 t1
left join test5 t2
on t1.object_id = t2.id;

      结果还是48行。也就是说这个方法是可行的。

      这样的话,如果在原来的SQL上加上并行提示,效果会更好。经过我的实际测试,3分钟以内就跑出了所有的结果。

      或许会有人问我,为什么不加上索引?我并不是反对加索引,我不习惯使用索引的习惯是因为我们的现实环境所限,我们的磁盘空间基本上每隔一段时间就会满,所以我没办法随心所欲的添加会占用空间的索引,而是更倾向于使用压缩表,节省表空间。而且,id2字段进行关联的时候有一个隐式类型转换,这个字段起码没有办法加索引。至于其他字段,我没办法实验,如果有机会,可以做个实验试试。

       


推荐阅读
  • Python 实战:异步爬虫(协程技术)与分布式爬虫(多进程应用)深入解析
    本文将深入探讨 Python 异步爬虫和分布式爬虫的技术细节,重点介绍协程技术和多进程应用在爬虫开发中的实际应用。通过对比多进程和协程的工作原理,帮助读者理解两者在性能和资源利用上的差异,从而在实际项目中做出更合适的选择。文章还将结合具体案例,展示如何高效地实现异步和分布式爬虫,以提升数据抓取的效率和稳定性。 ... [详细]
  • 在MFC框架中,存在多个全局函数,用于在不同对象间获取信息或创建新对象。其中,`afxGetApp`函数尤为关键,它能够帮助开发者轻松获取当前应用程序的实例指针。本文将详细解析`afxGetApp`函数的内部机制及其在MFC应用程序中的具体应用场景,探讨其在提升代码可维护性和灵活性方面的优势。此外,还将介绍其他常用全局函数如`AfxWinInit()`和`AfxBeginThread()`的功能和使用方法,为开发者提供全面的参考。 ... [详细]
  • 基址获取与驱动开发:内核中提取ntoskrnl模块的基地址方法解析
    基址获取与驱动开发:内核中提取ntoskrnl模块的基地址方法解析 ... [详细]
  • 在过去,我曾使用过自建MySQL服务器中的MyISAM和InnoDB存储引擎(也曾尝试过Memory引擎)。今年初,我开始转向阿里云的关系型数据库服务,并深入研究了其高效的压缩存储引擎TokuDB。TokuDB在数据压缩和处理大规模数据集方面表现出色,显著提升了存储效率和查询性能。通过实际应用,我发现TokuDB不仅能够有效减少存储成本,还能显著提高数据处理速度,特别适用于高并发和大数据量的场景。 ... [详细]
  • Eclipse JFace Text框架中IDocument接口的getNumberOfLines方法详解与编程实例 ... [详细]
  • 优化升级版数据采集与赋值方法,专为前文内容设计
    在前一篇文章中,方法的局限性主要体现在需要传递参数,并且参数数量受限。当页面布局与所需参数不匹配时,该方法将无法正常工作。为此,我们推出了优化升级版1.1,旨在解决这些问题并提高灵活性和适用性。 ... [详细]
  • 为了在VS2017和Qt环境中高效配置Open Inventor与Coin3D,本文提供了详细的实操指南和验证步骤。通过这些步骤,用户可以顺利导入机械臂模型进行三维仿真。本文详细介绍了Coin3D作为开源软件的优势及其在三维图形渲染中的应用,并提供了具体的配置方法和常见问题的解决方案。 ... [详细]
  • ZTree工具类全面汇总:实现节点的增删改及后台提交功能
    本文全面总结了ZTree工具类的使用方法,详细介绍了如何实现节点的增加、删除、修改以及后台数据提交等功能。通过实例代码和具体操作步骤,帮助开发者高效地掌握ZTree的各类操作,提升开发效率。此外,还提供了常见问题的解决方案,如在SpringBoot集成X-admin2.2时遇到的Layui字体图标显示问题。 ... [详细]
  • 设计实战 | 10个Kotlin项目深度解析:首页模块开发详解
    设计实战 | 10个Kotlin项目深度解析:首页模块开发详解 ... [详细]
  • 面向切面编程(AOP)是Spring框架的两大核心概念之一,另一个核心概念是控制反转(IoC)。AOP通过在应用程序中分离横切关注点,如日志记录、事务管理和安全性,从而提高代码的模块化和可维护性。本文将深入探讨AOP的核心概念和术语,帮助读者更好地理解和应用这一重要技术。 ... [详细]
  • JDK 1.8引入了多项并发新特性,显著提升了编程效率。本文重点探讨了LongAdder和StampedLock的特性和应用场景。此外,还介绍了在多线程环境中发生死锁时,如何通过jps命令进行诊断和排查,提供了详细的步骤和示例。这些改进不仅增强了系统的性能,还简化了开发者的调试工作。 ... [详细]
  • 2021年7月22日上午9点至中午12点,我专注于Java的学习,重点补充了之前在视频中遗漏的多线程知识。首先,我了解了进程的概念,即程序在内存中运行时形成的一个独立执行单元。其次,学习了线程作为进程的组成部分,是进程中可并发执行的最小单位,负责处理具体的任务。此外,我还深入研究了Runnable接口的使用方法及其在多线程编程中的重要作用。 ... [详细]
  • MongoDB高可用架构:深入解析Replica Set机制
    MongoDB的高可用架构主要依赖于其Replica Set机制。Replica Set通过多个mongod节点的协同工作,实现了数据的冗余存储和故障自动切换,确保了系统的高可用性和数据的一致性。本文将深入解析Replica Set的工作原理及其在实际应用中的配置和优化方法,帮助读者更好地理解和实施MongoDB的高可用架构。 ... [详细]
  • C#微信开发入门教程第二篇:新手快速上手指南,含详细视频讲解
    在距离上次课程一个多星期后,我们终于带来了第二讲的内容。虽然原计划是一周一次更新,但由于工作繁忙有所延迟。近期在交流群中发现,一些初学者已经能够熟练调用微信接口,但对微信公众平台的消息接收处理机制还不够了解。因此,本次课程将详细介绍如何高效处理微信公众平台的消息接收,并提供详细的视频讲解,帮助大家快速上手。 ... [详细]
  • 在Python编程中,探讨了并发与并行的概念及其区别。并发指的是系统同时处理多个任务的能力,而并行则指在同一时间点上并行执行多个任务。文章详细解析了阻塞与非阻塞操作、同步与异步编程模型,以及IO多路复用技术的应用。通过模拟socket发送HTTP请求的过程,展示了如何创建连接、发送数据和接收响应,并强调了默认情况下socket的阻塞特性。此外,还介绍了如何利用这些技术优化网络通信性能和提高程序效率。 ... [详细]
author-avatar
CCTV2财经2677
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有