热门标签 | 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
推荐阅读
  • 作为一名Java Web开发新手,我在尝试将项目部署至Tomcat服务器并连接MySQL数据库时遇到了驱动加载失败的问题。经过一番排查和努力,最终找到了解决方案。 ... [详细]
  • 在DELL Inspiron 14R上部署CentOS X64 6.4的详细步骤
    本文详细记录了在DELL Inspiron 14R笔记本电脑上安装CentOS X64 6.4操作系统的过程,包括遇到的问题及解决方法。 ... [详细]
  • 本文介绍了iftop的下载地址、基本参数配置方法及其在不同Linux发行版中的安装问题解决方案。iftop是一款强大的实时网络流量监控工具,适用于需要精确监控网络带宽使用情况的场景。 ... [详细]
  • 本文详细介绍了如何在CentOS 7操作系统上搭建GitLab服务器的过程,包括环境准备、软件安装及基本配置等关键步骤。 ... [详细]
  • 通常,ping命令用于快速检测与远程服务器之间的网络延迟,以评估连接质量。然而,在某些情况下,即使服务器可以正常访问,ping请求也可能无法成功。本文将介绍在CentOS 7系统上禁用ping命令的方法,包括临时和永久性设置。 ... [详细]
  • Docker入门与实践指南
    本文介绍了Docker的基础知识,包括其作为开源应用容器引擎的特点,以及如何利用Docker将应用程序及其依赖项打包成轻量级的容器镜像。同时,还详细讲解了Docker的核心概念、安装过程及基本命令操作。 ... [详细]
  • GNU GRUB(简称GRUB)是一个来自GNU项目的支持多启动的引导加载程序。它允许用户在同一台计算机上安装多个操作系统,并在启动时选择希望启动的系统。 ... [详细]
  • 本文详细介绍了如何在Python环境中配置与Oracle数据库的连接,包括必要的软件安装和环境变量设置等步骤,旨在为初学者提供清晰的操作指南。 ... [详细]
  • 解决腾讯社区开放平台.NET SDK在Linux下的兼容性问题
    本文探讨了腾讯社区开放平台.NET SDK在Linux系统(如CentOS)中遇到的QzoneException异常,并提供了通过安装根证书等方法解决该问题的具体步骤。 ... [详细]
  • Docker基础指南与核心命令解析
    本文全面介绍了Docker的基本概念、安装方法、核心命令及其用法,并深入探讨了Docker容器的数据卷管理及应用部署策略,适合初学者快速掌握Docker技术。 ... [详细]
  • 在CentOS 7上轻松安装Elasticsearch的JDBC插件
    本文介绍如何在CentOS 7操作系统中安装Elasticsearch (简称ES) 的JDBC插件,以实现与MySQL数据库的有效连接,特别强调了该方法对于增量数据同步的支持。 ... [详细]
  • 本文档详细介绍了在 CentOS Linux 7.9 系统环境下,如何从源代码编译安装 libwebsockets 库及其示例程序,并提供了编译过程中可能遇到的问题及解决方案。 ... [详细]
  • magent是一款开源的Memcached代理服务器软件,其项目网址为:  http:code.google.compmemagent  一、安装步骤& ... [详细]
  • 本文详细介绍了在Ubuntu 7.10操作系统上安装多种常用软件的方法,包括RAR压缩工具、即时通讯软件Pidgin、办公软件永中Office 2007试用版、多线程下载软件MultiGet及d4x、FTP客户端gFTP与FireFTP插件,以及P2P下载工具aMule。每部分都提供了具体的安装步骤和配置方法。 ... [详细]
  • 本文详细介绍了如何在CentOS系统中安装和配置Varnish缓存服务器,包括安装过程、服务启动与配置文件编辑等关键步骤。 ... [详细]
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社区 版权所有