热门标签 | HotTags
当前位置:  开发笔记 > 数据库 > 正文

PostgreSQL的缩小数据集优化过程一例-mysql教程

小黄同学拿过来一句SQL问我,看看能不能优化,看了下,数据量不是很大,查询还不算很复杂。以下是记录优化过程:DB:PostgreSQ

小黄同学拿过来一句SQL问我,看看能不能优化,看了下,数据量不是很大,查询还不算很复杂。以下是记录优化过程: DB:PostgreSQ

小黄同学拿过来一句SQL问我,看看能不能优化,看了下,数据量不是很大,查询还不算很复杂。以下是记录优化过程:

DB:PostgreSQL 9.1
OS:CentOS 6
count(d_personal_report_view) ~ 9K条,涉及金额数量的字段类型为numeric(9,2)类型

原始SQL:

select distinct c.doctor_name,c.department_name,c.hospital_id,c.hospital_name,c.part_hospital_name,t.* from d_personal_report_view c,( select dc.part_hospital_id,dc.department_id,dc.doctor_id, coalesce(sum(coalesce(dc.surgery_amount, '0.0')), '0.0') as surgery_amount,sum(coalesce(dc.surgery_quantity, 0)) as surgery_quantity, coalesce(sum(coalesce(dc.repair_amount, '0.0')), '0.0') as repair_amount,sum(coalesce(dc.repair_quantity, 0)) as repair_quantity, coalesce(sum(coalesce(dc.orthod_amount, '0.0')), '0.0') as orthod_amount,sum(coalesce(dc.orthod_quantity, 0)) as orthod_quantity, coalesce(sum(coalesce(dc.radiation_amount, '0.0')), '0.0') as radiation_amount,sum(coalesce(dc.radiation_quantity, 0)) as radiation_quantity, coalesce(sum(coalesce(dc.teethcln_amount, '0.0')), '0.0') as teethcln_amount,sum(coalesce(dc.teethcln_quantity, 0)) as teethcln_quantity, coalesce(sum(coalesce(dc.crop_amount, '0.0')), '0.0') as crop_amount,sum(coalesce(dc.crop_quantity, 0)) as crop_quantity, coalesce(sum(coalesce(dc.assay_amount, '0.0')), '0.0') as assay_amount,sum(coalesce(dc.assay_quantity, 0)) as assay_quantity, coalesce(sum(coalesce(dc.drugs_amount, '0.0')), '0.0') as drugs_amount,sum(coalesce(dc.drugs_quantity, 0)) as drugs_quantity, coalesce(sum(coalesce(dc.regist_amount, '0.0')), '0.0') as regist_amount,sum(coalesce(dc.regist_quantity, 0)) as regist_quantity, coalesce(sum(coalesce(dc.others_amount, '0.0')), '0.0') as others_amount,sum(coalesce(dc.others_quantity, 0)) as others_quantity, coalesce(sum(surgery_amount + repair_amount + orthod_amount + radiation_amount + teethcln_amount + crop_amount + assay_amount + drugs_amount + regist_amount + others_amount), '0.0') as totalRowAmount, coalesce(sum(surgery_quantity + repair_quantity + orthod_quantity + radiation_quantity + teethcln_quantity + crop_quantity + assay_quantity + drugs_quantity + regist_quantity + others_quantity), 0) as totalRowQuantity from d_personal_report_view dc where 1=1 group by dc.part_hospital_id,dc.department_id,dc.doctor_id order by dc.part_hospital_id,dc.doctor_id limit 10 offset 0) as t where c.part_hospital_id=t.part_hospital_id and c.department_id=t.department_id and c.doctor_id=t.doctor_id order by t.part_hospital_id,t.department_id,t.doctor_id;

一.分析
看其结果是要取10条不同的数据做报表展示,用了同一张表做了两次关联,没有涉及查询条件,索引效果不大。分析这个SQL好不好,先看一下执行计划



二.优化过程
1.取消两张表的关联,只取一次查询 优化后的SQL:

select dc.doctor_name,dc.department_name,dc.hospital_name,dc.part_hospital_name,dc.part_hospital_id,dc.department_id,dc.doctor_id, coalesce(sum(coalesce(dc.surgery_amount, '0.0')), '0.0') as surgery_amount,sum(coalesce(dc.surgery_quantity, 0)) as surgery_quantity, coalesce(sum(coalesce(dc.repair_amount, '0.0')), '0.0') as repair_amount,sum(coalesce(dc.repair_quantity, 0)) as repair_quantity, coalesce(sum(coalesce(dc.orthod_amount, '0.0')), '0.0') as orthod_amount,sum(coalesce(dc.orthod_quantity, 0)) as orthod_quantity, coalesce(sum(coalesce(dc.radiation_amount, '0.0')), '0.0') as radiation_amount,sum(coalesce(dc.radiation_quantity, 0)) as radiation_quantity, coalesce(sum(coalesce(dc.teethcln_amount, '0.0')), '0.0') as teethcln_amount,sum(coalesce(dc.teethcln_quantity, 0)) as teethcln_quantity, coalesce(sum(coalesce(dc.crop_amount, '0.0')), '0.0') as crop_amount,sum(coalesce(dc.crop_quantity, 0)) as crop_quantity, coalesce(sum(coalesce(dc.assay_amount, '0.0')), '0.0') as assay_amount,sum(coalesce(dc.assay_quantity, 0)) as assay_quantity, coalesce(sum(coalesce(dc.drugs_amount, '0.0')), '0.0') as drugs_amount,sum(coalesce(dc.drugs_quantity, 0)) as drugs_quantity, coalesce(sum(coalesce(dc.regist_amount, '0.0')), '0.0') as regist_amount,sum(coalesce(dc.regist_quantity, 0)) as regist_quantity, coalesce(sum(coalesce(dc.others_amount, '0.0')), '0.0') as others_amount,sum(coalesce(dc.others_quantity, 0)) as others_quantity, coalesce(sum(surgery_amount + repair_amount + orthod_amount + radiation_amount + teethcln_amount + crop_amount + assay_amount + drugs_amount + regist_amount + others_amount), '0.0') as totalRowAmount, coalesce(sum(surgery_quantity + repair_quantity + orthod_quantity + radiation_quantity + teethcln_quantity + crop_quantity + assay_quantity + drugs_quantity + regist_quantity + others_quantity), 0) as totalRowQuantity from d_personal_report_view dc where 1=1 group by dc.doctor_name,dc.department_name,dc.hospital_name,dc.part_hospital_name,dc.part_hospital_id,dc.department_id,dc.doctor_id order by dc.part_hospital_id,dc.doctor_id limit 10 offset 0

查看其执行计划:



2.其执行计划中的cost还是有点高,再次优化 postgresql中的sum会自动把null值替换成0,并考虑到不会存入''或者' '等空值,故可以去掉里面的coalesce转换函数。
优化后的SQL:

select dc.doctor_name,dc.department_name,dc.hospital_name,dc.part_hospital_name,dc.part_hospital_id,dc.department_id,dc.doctor_id, sum(dc.surgery_amount) as surgery_amount,sum(dc.surgery_quantity) as surgery_quantity, sum(dc.repair_amount) as repair_amount,sum(dc.repair_quantity) as repair_quantity, sum(dc.orthod_amount) as orthod_amount,sum(dc.orthod_quantity) as orthod_quantity, sum(dc.radiation_amount) as radiation_amount,sum(dc.radiation_quantity) as radiation_quantity, sum(dc.teethcln_amount) as teethcln_amount,sum(dc.teethcln_quantity) as teethcln_quantity, sum(dc.crop_amount) as crop_amount,sum(dc.crop_quantity) as crop_quantity, sum(dc.assay_amount) as assay_amount,sum(dc.assay_quantity) as assay_quantity, sum(dc.drugs_amount) as drugs_amount,sum(dc.drugs_quantity) as drugs_quantity, sum(dc.regist_amount) as regist_amount,sum(dc.regist_quantity) as regist_quantity, sum(dc.others_amount) as others_amount,sum(dc.others_quantity) as others_quantity, sum(surgery_amount + repair_amount + orthod_amount + radiation_amount + teethcln_amount + crop_amount + assay_amount + drugs_amount + regist_amount + others_amount) as totalRowAmount, sum(surgery_quantity + repair_quantity + orthod_quantity + radiation_quantity + teethcln_quantity + crop_quantity + assay_quantity + drugs_quantity + regist_quantity + others_quantity) as totalRowQuantity from d_personal_report_view dc where 1=1 group by dc.doctor_name,dc.department_name,dc.hospital_name,dc.part_hospital_name,dc.part_hospital_id,dc.department_id,dc.doctor_id order by dc.part_hospital_id,dc.doctor_id limit 10 offset 0
推荐阅读
  • 本文介绍了一款用于自动化部署 Linux 服务的 Bash 脚本。该脚本不仅涵盖了基本的文件复制和目录创建,还处理了系统服务的配置和启动,确保在多种 Linux 发行版上都能顺利运行。 ... [详细]
  • 在Ubuntu 16.04 LTS上配置Qt Creator开发环境
    本文详细介绍了如何在Ubuntu 16.04 LTS系统中安装和配置Qt Creator,涵盖了从下载到安装的全过程,并提供了常见问题的解决方案。 ... [详细]
  • 使用Vultr云服务器和Namesilo域名搭建个人网站
    本文详细介绍了如何通过Vultr云服务器和Namesilo域名搭建一个功能齐全的个人网站,包括购买、配置服务器以及绑定域名的具体步骤。文章还提供了详细的命令行操作指南,帮助读者顺利完成建站过程。 ... [详细]
  • 本文详细介绍了VMware的多种认证选项,帮助你根据职业需求和个人技能选择最合适的认证路径,涵盖从基础到高级的不同层次认证。 ... [详细]
  • 本文介绍如何通过更改软件源来提前体验Ubuntu 8.10,包括详细的配置步骤和相关注意事项。 ... [详细]
  • 本文详细介绍了如何在CentOS 7操作系统上安装和配置Grafana,包括必要的依赖项安装、插件管理以及服务启动等步骤。 ... [详细]
  • 基于KVM的SRIOV直通配置及性能测试
    SRIOV介绍、VF直通配置,以及包转发率性能测试小慢哥的原创文章,欢迎转载目录?1.SRIOV介绍?2.环境说明?3.开启SRIOV?4.生成VF?5.VF ... [详细]
  • 本文详细介绍了Linux系统中init进程的作用及其启动过程,解释了运行级别的概念,并提供了调整服务启动顺序的具体步骤和实例。通过了解这些内容,用户可以更好地管理系统的启动流程和服务配置。 ... [详细]
  • 解决网站乱码问题的综合指南
    本文总结了导致网站乱码的常见原因,并提供了详细的解决方案,包括文件编码、HTML元标签设置、服务器响应头配置、数据库字符集调整以及PHP与MySQL交互时的编码处理。 ... [详细]
  • 本教程将详细指导您如何通过光盘引导安装 CentOS 7.0,包括选择语言、系统分区和配置网络等关键步骤。整个过程大约需要8分钟,具体时间视硬件配置而定。 ... [详细]
  • 本文介绍如何在现有网络中部署基于Linux系统的透明防火墙(网桥模式),以实现灵活的时间段控制、流量限制等功能。通过详细的步骤和配置说明,确保内部网络的安全性和稳定性。 ... [详细]
  • 在Ubuntu 8.04中安装美观的Cairo-Dock桌面增强工具
    本文介绍如何在Ubuntu 8.04系统中安装和配置Cairo-Dock,这款桌面增强工具以其精美的界面和高效的功能备受用户青睐。文章将指导您通过简单的步骤完成安装,并提供一些实用的配置建议。 ... [详细]
  • 解决U盘安装系统后无法重启的问题
    本文详细探讨了运维新手常遇到的U盘安装系统后无法正常重启的问题,提供了从问题分析到具体解决方案的完整步骤。通过理解Boot Loader的工作原理和正确配置启动项,帮助用户顺利解决问题。 ... [详细]
  • 选择适合生产环境的Docker存储驱动
    本文旨在探讨如何在生产环境中选择合适的Docker存储驱动,并详细介绍不同Linux发行版下的配置方法。通过参考官方文档和兼容性矩阵,提供实用的操作指南。 ... [详细]
  • CentOS系统安装与配置常见问题及解决方案
    本文详细介绍了在CentOS系统安装过程中遇到的常见问题及其解决方案,包括Vi编辑器的操作、图形界面的安装、网络连接故障排除等。通过本文,读者可以更好地理解和解决这些常见问题。 ... [详细]
author-avatar
前年老妖
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有