Hibernate连接postgresql 数据库慢 PgDatabaseMetaData 问题的解决

问题描述

2024-07-30 18:26:43.949 INFO 4525 --- [ main] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.MySQL5InnoDBDialect
在撰写本公司项目的时候，博主发现了一个问题。此项目的数据库选用的是基于 PostgreSQL ，其中包含几千张数据库表。然而，在使用 Hibernate 启动并连接 PostgreSQL 时，速度极其缓慢，竟然需要约半小时之久。于是便一步步追踪代码，最终跟踪到 org/hibernate/engine/jdbc/spi/TypeInfo.class:48：

进入PgDatabaseMetaData发现

 /*
 * Copyright (c) 2004, PostgreSQL Global Development Group
 * See the LICENSE file in the project root for more information.
 */
 
package org.postgresql.jdbc;
 
import org.postgresql.core.BaseStatement;
import org.postgresql.core.Field;
import org.postgresql.core.Oid;
import org.postgresql.core.ServerVersion;
import org.postgresql.util.GT;
import org.postgresql.util.JdbcBlackHole;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
 
import java.sql.Array;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.RowIdLifetime;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;
 
public class PgDatabaseMetaData implements DatabaseMetaData {
 
  public PgDatabaseMetaData(PgConnection conn) {
    this.connection = conn;
  }
 
  private static final String keywords = "abort,acl,add,aggregate,append,archive,"
      + "arch_store,backward,binary,boolean,change,cluster,"
      + "copy,database,delimiter,delimiters,do,extend,"
      + "explain,forward,heavy,index,inherits,isnull,"
      + "light,listen,load,merge,nothing,notify,"
      + "notnull,oids,purge,rename,replace,retrieve,"
      + "returns,rule,recipe,setof,stdin,stdout,store,"
      + "vacuum,verbose,version";
 
  protected final PgConnection connection; // The connection association
 
  private int NAMEDATALEN = 0; // length for name datatype
  private int INDEX_MAX_KEYS = 0; // maximum number of keys in an index.
 
  public ResultSet getTypeInfo() throws SQLException {
 
    Field f[] = new Field[18];
    List<byte[][]> v = new ArrayList<byte[][]>(); // The new ResultSet tuple stuff
 
    f[0] = new Field("TYPE_NAME", Oid.VARCHAR);
    f[1] = new Field("DATA_TYPE", Oid.INT2);
    f[2] = new Field("PRECISION", Oid.INT4);
    f[3] = new Field("LITERAL_PREFIX", Oid.VARCHAR);
    f[4] = new Field("LITERAL_SUFFIX", Oid.VARCHAR);
    f[5] = new Field("CREATE_PARAMS", Oid.VARCHAR);
    f[6] = new Field("NULLABLE", Oid.INT2);
    f[7] = new Field("CASE_SENSITIVE", Oid.BOOL);
    f[8] = new Field("SEARCHABLE", Oid.INT2);
    f[9] = new Field("UNSIGNED_ATTRIBUTE", Oid.BOOL);
    f[10] = new Field("FIXED_PREC_SCALE", Oid.BOOL);
    f[11] = new Field("AUTO_INCREMENT", Oid.BOOL);
    f[12] = new Field("LOCAL_TYPE_NAME", Oid.VARCHAR);
    f[13] = new Field("MINIMUM_SCALE", Oid.INT2);
    f[14] = new Field("MAXIMUM_SCALE", Oid.INT2);
    f[15] = new Field("SQL_DATA_TYPE", Oid.INT4);
    f[16] = new Field("SQL_DATETIME_SUB", Oid.INT4);
    f[17] = new Field("NUM_PREC_RADIX", Oid.INT4);
 
    String sql;
    sql = "SELECT t.typname,t.oid FROM pg_catalog.pg_type t"
          + " JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid) "
          + " WHERE n.nspname  != 'pg_toast'";
 
    Statement stmt = connection.createStatement();
    ResultSet rs = stmt.executeQuery(sql);
 
    byte bZero[] = connection.encodeString("0");
    byte b10[] = connection.encodeString("10");
    byte bf[] = connection.encodeString("f");
    byte bt[] = connection.encodeString("t");
    byte bliteral[] = connection.encodeString("'");
    byte bNullable[] =
        connection.encodeString(Integer.toString(java.sql.DatabaseMetaData.typeNullable));
    byte bSearchable[] =
        connection.encodeString(Integer.toString(java.sql.DatabaseMetaData.typeSearchable));
 
    while (rs.next()) {
      byte[][] tuple = new byte[18][];
      String typname = rs.getString(1);
      int typeOid = (int) rs.getLong(2);
 
      tuple[0] = connection.encodeString(typname);
      int sqlType = connection.getTypeInfo().getSQLType(typname);
      //主要是上面的哪句话 每次都去查询
      tuple[1] =
          connection.encodeString(Integer.toString(sqlType));
      tuple[2] = connection
          .encodeString(Integer.toString(connection.getTypeInfo().getMaximumPrecision(typeOid)));
 
  
      if (connection.getTypeInfo().requiresQuotingSqlType(sqlType)) {
        tuple[3] = bliteral;
        tuple[4] = bliteral;
      }
 
      tuple[6] = bNullable; // all types can be null
      tuple[7] = connection.getTypeInfo().isCaseSensitive(typeOid) ? bt : bf;
      tuple[8] = bSearchable; // any thing can be used in the WHERE clause
      tuple[9] = connection.getTypeInfo().isSigned(typeOid) ? bf : bt;
      tuple[10] = bf; // false for now - must handle money
      tuple[11] = bf; // false - it isn't autoincrement
      tuple[13] = bZero; // min scale is zero
      // only numeric can supports a scale.
      tuple[14] = (typeOid == Oid.NUMERIC) ? connection.encodeString("1000") : bZero;
 
      // 12 - LOCAL_TYPE_NAME is null
      // 15 & 16 are unused so we return null
      tuple[17] = b10; // everything is base 10
      v.add(tuple);
 
      // add pseudo-type serial, bigserial
      if (typname.equals("int4")) {
        byte[][] tuple1 = tuple.clone();
 
        tuple1[0] = connection.encodeString("serial");
        tuple1[11] = bt;
        v.add(tuple1);
      } else if (typname.equals("int8")) {
        byte[][] tuple1 = tuple.clone();
 
        tuple1[0] = connection.encodeString("bigserial");
        tuple1[11] = bt;
        v.add(tuple1);
      }
 
    }
    rs.close();
    stmt.close();
 
    return ((BaseStatement) createMetaDataStatement()).createDriverResultSet(f, v);
  }
}

他初始化的时候，执行了这么一条 sql 语句：sql = “SELECT t.typname,t.oid FROM pg_catalog.pg_type t” + " JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid) " + " WHERE n.nspname != ‘pg_toast’ " 。
这个 sql 语句是为了把所有的类型和数据库表都查出来，然后放到缓存里面，目的是提高查询性能。可我的数据库超大，查出来差不多有 5k 条数据。看起来好像也不算多，但是注意看代码，这里面循环了这 5k 条数据，而且在循环里还又做了一次查询。接着追踪，我们找到了 getSQLType() 方法所在的类，它里面还在调用SQL执行

/*
 * Copyright (c) 2005, PostgreSQL Global Development Group
 * See the LICENSE file in the project root for more information.
 */
 
package org.postgresql.jdbc;
 
import org.postgresql.core.BaseConnection;
import org.postgresql.core.BaseStatement;
import org.postgresql.core.Oid;
import org.postgresql.core.QueryExecutor;
import org.postgresql.core.ServerVersion;
import org.postgresql.core.TypeInfo;
import org.postgresql.util.GT;
import org.postgresql.util.PGobject;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
 
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
 
public class TypeInfoCache implements TypeInfo {
 
  // pgname (String) -> java.sql.Types (Integer)
  private Map<String, Integer> _pgNameToSQLType;
 
  // pgname (String) -> java class name (String)
  // ie "text" -> "java.lang.String"
  private Map<String, String> _pgNameToJavaClass;
 
  // oid (Integer) -> pgname (String)
  private Map<Integer, String> _oidToPgName;
  // pgname (String) -> oid (Integer)
  private Map<String, Integer> _pgNameToOid;
 
  // pgname (String) -> extension pgobject (Class)
  private Map<String, Class<? extends PGobject>> _pgNameToPgObject;
 
  // type array oid -> base type's oid
  private Map<Integer, Integer> _pgArrayToPgType;
 
  // array type oid -> base type array element delimiter
  private Map<Integer, Character> _arrayOidToDelimiter;
 
  private BaseConnection _conn;
  private final int _unknownLength;
  private PreparedStatement _getOidStatementSimple;
  private PreparedStatement _getOidStatementComplexNonArray;
  private PreparedStatement _getOidStatementComplexArray;
  private PreparedStatement _getNameStatement;
  private PreparedStatement _getArrayElementOidStatement;
  private PreparedStatement _getArrayDelimiterStatement;
  private PreparedStatement _getTypeInfoStatement;
 
  // basic pg types info:
  // 0 - type name
  // 1 - type oid
  // 2 - sql type
  // 3 - java class
  // 4 - array type oid
  private static final Object types[][] = {
      {"int2", Oid.INT2, Types.SMALLINT, "java.lang.Integer", Oid.INT2_ARRAY},
      {"int4", Oid.INT4, Types.INTEGER, "java.lang.Integer", Oid.INT4_ARRAY},
      {"oid", Oid.OID, Types.BIGINT, "java.lang.Long", Oid.OID_ARRAY},
      {"int8", Oid.INT8, Types.BIGINT, "java.lang.Long", Oid.INT8_ARRAY},
      {"money", Oid.MONEY, Types.DOUBLE, "java.lang.Double", Oid.MONEY_ARRAY},
      {"numeric", Oid.NUMERIC, Types.NUMERIC, "java.math.BigDecimal", Oid.NUMERIC_ARRAY},
      {"float4", Oid.FLOAT4, Types.REAL, "java.lang.Float", Oid.FLOAT4_ARRAY},
      {"float8", Oid.FLOAT8, Types.DOUBLE, "java.lang.Double", Oid.FLOAT8_ARRAY},
      {"char", Oid.CHAR, Types.CHAR, "java.lang.String", Oid.CHAR_ARRAY},
      {"bpchar", Oid.BPCHAR, Types.CHAR, "java.lang.String", Oid.BPCHAR_ARRAY},
      {"varchar", Oid.VARCHAR, Types.VARCHAR, "java.lang.String", Oid.VARCHAR_ARRAY},
      {"text", Oid.TEXT, Types.VARCHAR, "java.lang.String", Oid.TEXT_ARRAY},
      {"name", Oid.NAME, Types.VARCHAR, "java.lang.String", Oid.NAME_ARRAY},
      {"bytea", Oid.BYTEA, Types.BINARY, "[B", Oid.BYTEA_ARRAY},
      {"bool", Oid.BOOL, Types.BIT, "java.lang.Boolean", Oid.BOOL_ARRAY},
      {"bit", Oid.BIT, Types.BIT, "java.lang.Boolean", Oid.BIT_ARRAY},
      {"date", Oid.DATE, Types.DATE, "java.sql.Date", Oid.DATE_ARRAY},
      {"time", Oid.TIME, Types.TIME, "java.sql.Time", Oid.TIME_ARRAY},
      {"timetz", Oid.TIMETZ, Types.TIME, "java.sql.Time", Oid.TIMETZ_ARRAY},
      {"timestamp", Oid.TIMESTAMP, Types.TIMESTAMP, "java.sql.Timestamp", Oid.TIMESTAMP_ARRAY},
      {"timestamptz", Oid.TIMESTAMPTZ, Types.TIMESTAMP, "java.sql.Timestamp",
          Oid.TIMESTAMPTZ_ARRAY},
      //JCP! if mvn.project.property.postgresql.jdbc.spec >= "JDBC4.2"
//JCP>       {"refcursor", Oid.REF_CURSOR, Types.REF_CURSOR, "java.sql.ResultSet", Oid.REF_CURSOR_ARRAY},
      //JCP! endif
      {"json", Oid.JSON, Types.OTHER, "org.postgresql.util.PGobject", Oid.JSON_ARRAY},
      {"point", Oid.POINT, Types.OTHER, "org.postgresql.geometric.PGpoint", Oid.POINT_ARRAY}
  };
 
  /**
   * PG maps several alias to real type names. When we do queries against pg_catalog, we must use
   * the real type, not an alias, so use this mapping.
   */
  private final static HashMap<String, String> typeAliases;
 
  static {
    typeAliases = new HashMap<String, String>();
    typeAliases.put("smallint", "int2");
    typeAliases.put("integer", "int4");
    typeAliases.put("int", "int4");
    typeAliases.put("bigint", "int8");
    typeAliases.put("float", "float8");
    typeAliases.put("boolean", "bool");
    typeAliases.put("decimal", "numeric");
  }
 
  public TypeInfoCache(BaseConnection conn, int unknownLength) {
    _conn = conn;
    _unknownLength = unknownLength;
    _oidToPgName = new HashMap<Integer, String>();
    _pgNameToOid = new HashMap<String, Integer>();
    _pgNameToJavaClass = new HashMap<String, String>();
    _pgNameToPgObject = new HashMap<String, Class<? extends PGobject>>();
    _pgArrayToPgType = new HashMap<Integer, Integer>();
    _arrayOidToDelimiter = new HashMap<Integer, Character>();
 
    // needs to be synchronized because the iterator is returned
    // from getPGTypeNamesWithSQLTypes()
    _pgNameToSQLType = Collections.synchronizedMap(new HashMap<String, Integer>());
 
    for (Object[] type : types) {
      String pgTypeName = (String) type[0];
      Integer oid = (Integer) type[1];
      Integer sqlType = (Integer) type[2];
      String javaClass = (String) type[3];
      Integer arrayOid = (Integer) type[4];
 
      addCoreType(pgTypeName, oid, sqlType, javaClass, arrayOid);
    }
 
    _pgNameToJavaClass.put("hstore", Map.class.getName());
  }
 
  public synchronized int getSQLType(String pgTypeName) throws SQLException {
    if (pgTypeName.endsWith("[]")) {
      return Types.ARRAY;
    }
    Integer i = _pgNameToSQLType.get(pgTypeName);
    if (i != null) {
      return i;
    }
 
    if (_getTypeInfoStatement == null) {
      // There's no great way of telling what's an array type.
      // People can name their own types starting with _.
      // Other types use typelem that aren't actually arrays, like box.
      //
      String sql;
      // in case of multiple records (in different schemas) choose the one from the current
      // schema,
      // otherwise take the last version of a type that is at least more deterministic then before
      // (keeping old behaviour of finding types, that should not be found without correct search
      // path)
      sql = "SELECT typinput='array_in'::regproc, typtype "
            + "  FROM pg_catalog.pg_type "
            + "  LEFT "
            + "  JOIN (select ns.oid as nspoid, ns.nspname, r.r "
            + "          from pg_namespace as ns "
            // -- go with older way of unnesting array to be compatible with 8.0
            + "          join ( select s.r, (current_schemas(false))[s.r] as nspname "
            + "                   from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r "
            + "         using ( nspname ) "
            + "       ) as sp "
            + "    ON sp.nspoid = typnamespace "
            + " WHERE typname = ? "
            + " ORDER BY sp.r, pg_type.oid DESC LIMIT 1;";
 
      _getTypeInfoStatement = _conn.prepareStatement(sql);
    }
 
    _getTypeInfoStatement.setString(1, pgTypeName);
 
    // Go through BaseStatement to avoid transaction start.
    if (!((BaseStatement) _getTypeInfoStatement)
        .executeWithFlags(QueryExecutor.QUERY_SUPPRESS_BEGIN)) {
      throw new PSQLException(GT.tr("No results were returned by the query."), PSQLState.NO_DATA);
    }
 
    ResultSet rs = _getTypeInfoStatement.getResultSet();
 
    Integer type = null;
    if (rs.next()) {
      boolean isArray = rs.getBoolean(1);
      String typtype = rs.getString(2);
      if (isArray) {
        type = Types.ARRAY;
      } else if ("c".equals(typtype)) {
        type = Types.STRUCT;
      } else if ("d".equals(typtype)) {
        type = Types.DISTINCT;
      } else if ("e".equals(typtype)) {
        type = Types.VARCHAR;
      }
    }
 
    if (type == null) {
      type = Types.OTHER;
    }
    rs.close();
 
    _pgNameToSQLType.put(pgTypeName, type);
    return type;
  }
}

首先去官网看 github pgjdbc 看有没有相关的问题
然后试着升级org.postgresql 原版本42.2.5 升级到42.2.20 问题解决 回到问题的方法去看一下是否解决

  public synchronized int getSQLType(String pgTypeName) throws SQLException {
        if (pgTypeName.endsWith("[]")) {
            return 2003;
        } else {
            Integer i = (Integer)this.pgNameToSQLType.get(pgTypeName);
            if (i != null) {
                return i;
            } else {
                LOGGER.log(Level.FINEST, "querying SQL typecode for pg type '{0}'", pgTypeName);
                PreparedStatement getTypeInfoStatement = this.prepareGetTypeInfoStatement();
                getTypeInfoStatement.setString(1, pgTypeName);
                if (!((BaseStatement)getTypeInfoStatement).executeWithFlags(16)) {
                    throw new PSQLException(GT.tr("No results were returned by the query.", new Object[0]), PSQLState.NO_DATA);
                } else {
                    ResultSet rs = (ResultSet)Nullness.castNonNull(getTypeInfoStatement.getResultSet());
                    int type = 1111;
                    if (rs.next()) {
                        type = this.getSQLTypeFromQueryResult(rs);
                    }

                    rs.close();
                    this.pgNameToSQLType.put(pgTypeName, type);
                    return type;
                }
            }
        }
    }

发现没有啦 SQL执行 所以 新版本升级后 启动变得非常快