首页 / JAVA / java-通过保留1亿个数据的联接来提高PostgreSQL查询性能

java-通过保留1亿个数据的联接来提高PostgreSQL查询性能

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了java-通过保留1亿个数据的联接来提高PostgreSQL查询性能，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7237字，纯文字阅读大概需要11分钟。

内容图文

我正在使用Postgresql-9.2版本,Windows 7 64位,RAM 6GB.这是一个Java企业项目.

我必须在页面上显示订单相关信息.通过左联接将三个表组合在一起.

表格：

> TV_HD(389772行)
> TV_SNAPSHOT(1564756行)
> TD_MAKKA(419298行)

左联接3个表后,查询给出487252.它也将逐日增加.

表关系：

> TV_HD与TV_SNAPSHOT包含“一对多”关系
> TV_HD与TD_MAKKA包含“一对多”关系

为了更好地理解,我现在使用sql查询提供图片视图

SELECT * FROM tv_hd其中urino = 1630799

SELECT * FROM tv_snapshot其中urino = 1630799

SELECT * FROM td_makka其中urino = 1630799

该查询将在大约90秒内运行.如何改善查询性能？

我也考虑过建立索引.但是据我所知,当我们想从表中获取2％-4％的数据时,实际上就使用了索引.但是就我而言,我需要从这3个表中获取所有数据.

这是查询：

SELECT count(*)
FROM (SELECT HD.URINO
      FROM
        TV_HD HD
        LEFT JOIN TV_SNAPSHOT T ON (HD.URINO = T.URINO AND HD.TCODE = T.TCODE AND T.DELFLG = 0 AND T.SYUBETSU = 1)
        LEFT JOIN TV_SNAPSHOT T_SQ
          ON (HD.URINO = T_SQ.URINO AND HD.SQCODE = T_SQ.TCODE AND T_SQ.DELFLG = 0 AND T_SQ.SYUBETSU = 3)
        LEFT JOIN (SELECT N.URINO
                   FROM
                     TD_MAKKA N
                   WHERE
                     N.UPDATETIME IN (
                       SELECT MIN(NMIN.UPDATETIME)
                       FROM
                         TD_MAKKA NMIN
                       WHERE
                         N.URINO = NMIN.URINO
                         AND
                         NMIN.TORIKESHIFLG <> -1
                     )
                  ) NYUMIN
          ON (HD.URINO = NYUMIN.URINO)
        LEFT JOIN
        (
          SELECT
            NSUM.URINO,
            SUM(COALESCE(NSUM.NYUKIN, 0))                                                             NYUKIN,
            SUM(COALESCE(NSUM.NYUKIN, 0)) + SUM(COALESCE(NSUM.TESU, 0)) + SUM(COALESCE(NSUM.SOTA, 0)) SUMNYUKIN
          FROM
            TD_MAKKA NSUM
          GROUP BY
            URINO
        ) NYUSUM
          ON (HD.URINO = NYUSUM.URINO)
        LEFT JOIN
        (
          SELECT N.URINO
          FROM
            TD_MAKKA N
          WHERE
            UPDATETIME = (
              SELECT MAX(UPDATETIME)
              FROM
                TD_MAKKA NMAX
              WHERE
                N.URINO = NMAX.URINO
                AND
                NMAX.TORIKESHIFLG <> -1
            )
        ) NYUMAX
          ON (HD.URINO = NYUMAX.URINO)
      WHERE ((HD.URIBRUI <> '1') OR (HD.URIBRUI = '1' AND T_SQ.NYUKOBEFLG = '-1'))
      ORDER BY
        HD.URINO DESC
     ) COUNT_

这是EXPLAIN ANALYZE的结果

Aggregate  (cost=7246861.21..7246861.22 rows=1 width=0) (actual time=69549.159..69549.159 rows=1 loops=1)
  ->  Merge Left Join  (cost=7240188.92..7242117.36 rows=379508 width=6) (actual time=68602.689..69510.563 rows=487252 loops=1)
        Merge Cond: (hd.urino = n.urino)
        ->  Sort  (cost=3727299.33..3728248.10 rows=379508 width=6) (actual time=62160.072..62557.132 rows=420036 loops=1)
              Sort Key: hd.urino
              Sort Method: external merge  Disk: 6984kB
              ->  Hash Right Join  (cost=169264.26..3686940.26 rows=379508 width=6) (actual time=54796.930..60172.248 rows=420036 loops=1)
                    Hash Cond: (n.urino = hd.urino)
                    ->  Seq Scan on td_makka n  (cost=0.00..3511201.36 rows=209673 width=6) (actual time=24.326..4640.020 rows=419143 loops=1)
                          Filter: (SubPlan 1)
                          Rows Removed by Filter: 155
                          SubPlan 1
                            ->  Aggregate  (cost=8.33..8.34 rows=1 width=23) (actual time=0.009..0.009 rows=1 loops=419298)
                                  ->  Index Scan using idx_td_makka on td_makka nmin  (cost=0.00..8.33 rows=1 width=23) (actual time=0.006..0.007 rows=1 loops=419298)
                                        Index Cond: (n.urino = urino)
                                        Filter: (torikeshiflg <> (-1)::numeric)
                                        Rows Removed by Filter: 0
                    ->  Hash  (cost=163037.41..163037.41 rows=379508 width=6) (actual time=54771.078..54771.078 rows=386428 loops=1)
                          Buckets: 4096  Batches: 16  Memory Usage: 737kB
                          ->  Hash Right Join  (cost=75799.55..163037.41 rows=379508 width=6) (actual time=51599.167..54605.901 rows=386428 loops=1)
                                Hash Cond: ((t_sq.urino = hd.urino) AND (t_sq.tcode = hd.sqcode))
                                Filter: ((hd.uribrui <> '1'::bpchar) OR ((hd.uribrui = '1'::bpchar) AND (t_sq.nyukobeflg = (-1)::numeric)))
                                Rows Removed by Filter: 3344
                                ->  Seq Scan on tv_snapshot t_sq  (cost=0.00..73705.42 rows=385577 width=15) (actual time=0.053..2002.953 rows=389983 loops=1)
                                      Filter: ((delflg = 0::numeric) AND (syubetsu = 3::numeric))
                                      Rows Removed by Filter: 1174773
                                ->  Hash  (cost=68048.99..68048.99 rows=389771 width=14) (actual time=51596.055..51596.055 rows=389772 loops=1)
                                      Buckets: 4096  Batches: 16  Memory Usage: 960kB
                                      ->  Hash Right Join  (cost=21125.85..68048.99 rows=389771 width=14) (actual time=579.405..51348.270 rows=389772 loops=1)
                                            Hash Cond: (nyusum.urino = hd.urino)
                                            ->  Subquery Scan on nyusum  (cost=0.00..35839.52 rows=365638 width=6) (actual time=17.435..49996.674 rows=385537 loops=1)
                                                  ->  GroupAggregate  (cost=0.00..32183.14 rows=365638 width=34) (actual time=17.430..49871.702 rows=385537 loops=1)
                                                        ->  Index Scan using idx_td_makka on td_makka nsum  (cost=0.00..21456.76 rows=419345 width=34) (actual time=0.017..48357.702 rows=419298 loops=1)
                                            ->  Hash  (cost=13969.71..13969.71 rows=389771 width=20) (actual time=491.549..491.549 rows=389772 loops=1)
                                                  Buckets: 4096  Batches: 32  Memory Usage: 567kB
                                                  ->  Seq Scan on tv_hd hd  (cost=0.00..13969.71 rows=389771 width=20) (actual time=0.052..242.415 rows=389772 loops=1)
        ->  Sort  (cost=3512889.60..3512894.84 rows=2097 width=6) (actual time=6442.600..6541.728 rows=486359 loops=1)
              Sort Key: n.urino
              Sort Method: external sort  Disk: 8600kB
              ->  Seq Scan on td_makka n  (cost=0.00..3512773.90 rows=2097 width=6) (actual time=0.135..4053.116 rows=419143 loops=1)
                    Filter: ((updatetime)::text = (SubPlan 2))
                    Rows Removed by Filter: 155
                    SubPlan 2
                      ->  Aggregate  (cost=8.33..8.34 rows=1 width=23) (actual time=0.008..0.008 rows=1 loops=419298)
                            ->  Index Scan using idx_td_makka on td_makka nmax  (cost=0.00..8.33 rows=1 width=23) (actual time=0.005..0.006 rows=1 loops=419298)
                                  Index Cond: (n.urino = urino)
                                  Filter: (torikeshiflg <> (-1)::numeric)
                                  Rows Removed by Filter: 0
Total runtime: 69575.139 ms

这是解释分析结果的详细信息：

http://explain.depesz.com/s/23Fg

解决方法:

第一步：
您可以删除选择查询中不需要的更多列,因为您只需要计算总行数即可.例如：

select count(*) from ( SELECT
    HD.URINO
FROM
    TV_HD HD
    LEFT JOIN TV_SNAPSHOT T ON (HD.URINO = T.URINO AND HD.TCODE = T.TCODE AND T.DELFLG = 0 AND T.SYUBETSU = 1)
    LEFT JOIN TV_SNAPSHOT T_SQ ON (HD.URINO = T_SQ.URINO AND HD.SQCODE = T_SQ.TCODE AND T_SQ.DELFLG = 0 AND T_SQ.SYUBETSU = 3)
    LEFT JOIN (SELECT
                    N.URINO
            FROM
                TD_MAKKA N
            WHERE
                N.UPDATETIME IN (
                    SELECT
                        MIN (NMIN.UPDATETIME)
                    FROM
                        TD_MAKKA NMIN
                    WHERE
                        N.URINO = NMIN.URINO
                    AND
                        NMIN.TORIKESHIFLG <> -1 
                )
        ) NYUMIN
    ON  (HD.URINO = NYUMIN.URINO) 
            LEFT JOIN
                (
                    SELECT
                        NSUM.URINO
                        ,SUM (COALESCE(NSUM.NYUKIN ,0)) NYUKIN
                        ,SUM (COALESCE(NSUM.NYUKIN ,0)) + SUM (COALESCE(NSUM.TESU ,0)) + SUM (COALESCE(NSUM.SOTA ,0)) SUMNYUKIN
                    FROM
                        TD_MAKKA NSUM
                    GROUP BY
                        URINO
                ) NYUSUM
            ON  (HD.URINO = NYUSUM.URINO)
            LEFT JOIN
                (
                    SELECT
                         N.URINO
                    FROM
                        TD_MAKKA N
                    WHERE
                        UPDATETIME = (
                            SELECT
                                MAX (UPDATETIME)
                            FROM
                                TD_MAKKA NMAX
                            WHERE
                                N.URINO = NMAX.URINO
                            AND
                                NMAX.TORIKESHIFLG <> -1 
                        )
               ) NYUMAX
            ON  (HD.URINO = NYUMAX.URINO)
WHERE ( (HD.URIBRUI <> '1') OR ( HD.URIBRUI = '1' AND T_SQ.NYUKOBEFLG = '-1' ) )
 ORDER BY 
 HD.URINO DESC
  ) COUNT_

第二步：
您可以避免左连接对获取行数没有意义.
例如：

select count(*) from ( SELECT
    HD.URINO
FROM
    TV_HD HD
    LEFT JOIN TV_SNAPSHOT T ON (HD.URINO = T.URINO AND HD.TCODE = T.TCODE AND T.DELFLG = 0 AND T.SYUBETSU = 1)
    LEFT JOIN TV_SNAPSHOT T_SQ ON (HD.URINO = T_SQ.URINO AND HD.SQCODE = T_SQ.TCODE AND T_SQ.DELFLG = 0 AND T_SQ.SYUBETSU = 3)
    LEFT JOIN (SELECT
                    N.URINO
            FROM
                TD_MAKKA N
            WHERE
                N.UPDATETIME IN (
                    SELECT
                        MIN (NMIN.UPDATETIME)
                    FROM
                        TD_MAKKA NMIN
                    WHERE
                        N.URINO = NMIN.URINO
                    AND
                        NMIN.TORIKESHIFLG <> -1 
                )
        ) NYUMIN
    ON  (HD.URINO = NYUMIN.URINO) 
            LEFT JOIN
                (
                    SELECT
                         N.URINO
                    FROM
                        TD_MAKKA N
                    WHERE
                        UPDATETIME = (
                            SELECT
                                MAX (UPDATETIME)
                            FROM
                                TD_MAKKA NMAX
                            WHERE
                                N.URINO = NMAX.URINO
                            AND
                                NMAX.TORIKESHIFLG <> -1 
                        )
               ) NYUMAX
            ON  (HD.URINO = NYUMAX.URINO)
WHERE ( (HD.URIBRUI <> '1') OR ( HD.URIBRUI = '1' AND T_SQ.NYUKOBEFLG = '-1' ) )

  ) COUNT_

第三步：您可以使用PgAdmin图形解释计划来分析查询并避免其他不必要的执行开销.

内容总结

以上是互联网集市为您收集整理的java-通过保留1亿个数据的联接来提高PostgreSQL查询性能全部内容，希望文章能够帮你解决java-通过保留1亿个数据的联接来提高PostgreSQL查询性能所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/884955.html

来源：【匿名】

【上一篇】java-如何在JPA中创建序列而不直接在数据库中创建【下一篇】JAVA/JSP学习系列之五

更多 ►

【java-通过保留1亿个数据的联接来提高PostgreSQL查询性能】教程文章相关的互联网学习教程文章

Java+MyEclipse+Tomcat (三)配置MySQL及查询数据显示在JSP网页中

Java+MyEclipse+Tomcat (三)配置MySQL及查询数据显示在JSP网页中标签：本文系统来源：http://www.cnblogs.com/yyilur/p/4496289.html

Java+MyEclipse+Tomcat (三)配置MySQL及查询数据显示在JSP网页中【代码】【图】

JavaMyEclipseTomcat (一)配置过程及jsp网站开发入门 JavaMyEclipseTomcat (二)配置Servlet及简单实现表单提交代码及MySQL下载地址： http://pan.baidu.com/s/1jGGKJ1k一. 配置MySQL 首先下载mysql-5.0.96-winx64，安装过程如下图所示。 1.安装MySQL 5.0 2.选择手动配置、服务类型、通用多功能型和安装路径 3.设置数据库访问量连接数为15、端口为3306（代码中设置UR...

Spring Jdbc 自定义 ORM——sql查询对应Java数据对象【代码】【图】

实现如下，通过Spring Jdbc，传入sql，查询满足条件的Java数据对象： pojo: public class ZmTest {public ZmTest() {// TODO Auto-generated constructor stub}private Integer id;private String name;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}}ZmTest sql: create table zm_test_sjdbc( i...

Java 获取SQL查询语句结果【代码】

step1:构造连接Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db","root","123"); step2:构造语句String sql = "select username,password from t_user where username=?"; PreparedStatement stmt = con.prepareStatement(sql); stmt.setString(1, "zhangshan"); //给SQL的"?"（问号）传值。step3:执行查询ResultSet rs = stmt.executeQuery(); //rs就是S...

Eclipse中java获得mysql的查询结果集【代码】【图】

1 package com.ningmeng;2 3 import java.sql.*;4 /**5 * 1：获取查询结果集6 * @author biexiansheng7 *8 */9 public class Test03 { 10 11 public static void main(String[] args) { 12 try { 13 Class.forName("com.mysql.jdbc.Driver"); 14 System.out.println("加载数据库驱动成功"); 15 String url="jdbc:mysql://localhost:3306/test";//声明数据库test的url 16 ...

Java连接MySQL数据库实现用户名密码的验证方法 Java语句中sql查询语句'' ""作用【代码】

com.swift;import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List;public class LoginJDBC {public static void main(String[] args) {User userZhangsan=new User("swift","123456");if(login(userZhangsan)) {System.out.println("登陆成功");}else {System.out.println("登陆失...

MySQL 查询语句中自己定义的中文内容在Java Web 中显示为问号【图】

的值为latin1 在mysql的安装目录下找到my.ini文件, 在my.ini文件中找到[client]和[mysqld]字段，在下面均加上default-character-set=utf8，保存并关闭，重启mysql服务。打开再输入：show variables like ‘character_set_%‘; 得到如下结果之后能运行Web程序，性别按照预期显示为男，女。MySQL 查询语句中自己定义的中文内容在Java Web 中显示为问号标签：中文支持 .post logs mysql pos cas rac ase 本文系...

Java-MySQL-查询操作【代码】

public static List<Map<String, Object>> getWX(String cardid) throws SQLException {String sql = "select * from baoxiu where cardid=? order by id desc";PreparedStatement ptmt = conn.prepareStatement(sql);ptmt.setString(1, cardid);ResultSet rs = ptmt.executeQuery();List<Map<String, Object>> list = new ArrayList<Map<String,Object>>() ;while(rs.next()) {HashMap<String, Object> map = new HashMap<String,...

java-从SQL查询检索参数【代码】

我想从SQL查询中获取所有参数.在这种情况下,查询是数组中的纯字符串.我有以下形式的查询对：SELECT * FROM my_table t WHERE (t.attr1 = ? AND t.attr2 = ?) OR t.attr3= ?它的对是(其中包含我要检索的参数)：SELECT * FROM my_table t WHERE (t.attr1 = 3 AND t.attr2 = 'str') OR t.attr3= 4因此输出将是：3, 'str' and 4.如何基于此对检索这些值？我在Java中尝试使用正则表达式,但是会很困难,因为？可以是数字字符串等等.该字符...

java-使用PreparedStatement(JDBC)时如何打印使用的SQL查询

我在Java程序中使用了PreparedStatement.我需要调试SQL查询,因为它无法正常工作. 是否可以打印插入了值的使用的SQL语句例如在PreparedStatement中：从表中选择*,其中a =？比我设定的？我可以打印使用的SQL,例如：从表中选择*,其中a = 1解决方法:不能使用java.sql.PreparedStatement接口本身完成此操作.这取决于您的数据库供应商的实现. 但是你很幸运. MySQL驱动程序允许您使用其toString实现来实现： http://www.avajava.com/...

android-SQLite查询以创建定义的Java对象【代码】

我有这个提示的SQLite表台车：CATEGORY COUNTRY ID NAME EMAIL A GE 1 BMW sample1@salple.it A GE 2 Lamborghini sample2@salple.it B GE 3 BMW sample3@salple.it我想选择所有具有指定名称或指定类别的条目,并将所有参数传递给构造函数中的每一行Vehicle(String category, String country, int id, String name, Strin...

java-如何将MySQL查询结果放入ArrayList【代码】

我有导致多列的SQL查询.我想执行此查询并将结果放入我的ArrayList<>中.而不是ResultSet.我的列定义课是public class Record{private String FileName;private String FileID;private String Loan;private String Page; }查询是：String query = "SELECT FileName, FileID, loanNumnber, PageNumber FROM table"; ResultSet rs = stmt.executeQuery(query);我想要在recordData对象中查询的结果.ArrayList<Record> recordData = new ...

java-StringUtil indexOf()等效的postgreSQL查询【代码】

我需要在postgresql中实现stringUtils类的indexOf()方法. 可以说我有一个表,其中url是列之一. 网址：“ http://paypal-info.com/home.webapps.cgi-bin-limit/webscr.cmd-login-submit” 我的要求是在上述网址中查找“ /”的第三次出现的索引并执行子字符串,并仅在Postgresql查询中使用paypal-info.com主机名任何实现此想法将不胜感激.谢谢解决方法:string functions and operators section of the manual相当于String.indexOf,例如...

java-按日期过滤的Spring JDBC模板SQL查询【代码】

我需要按两个日期过滤查询.当我使用Oracle SQL Developer或直接在Eclipse中查询它时,这是可行的：select * from TABLE where rechnungs_eingang >= '06.08.2012' AND rechnungs_eingang <= '06.08.2015')但是,如果我尝试通过我的Web服务获取数据,而我已使用Spring 4实现了此异常：Caused by: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [select * from TABLE where rechnungs_eingang >= '...