代码之家 › 专栏 › 技术社区 › Jan Nielsen

通过JPQL查询慢速子选择组

jpql spring-data-jpa postgresql spring

Jan Nielsen · 技术社区 · 7 年前

针对第9.6页执行以下JPQL大约需要1.5秒:

@Query(" SELECT t FROM T t WHERE t.id in ("
     + "   SELECT MAX(t.id) FROM T t, C c "
     + "     WHERE t.m           = :m     "
     + "       AND t.c           = c      "
     + "       AND c.createdDate < :date  "
     + "     GROUP BY t.m, t.p            "
     + "   )                              ")
List<T> tByDate(@Param("m") M m, @Param("date") LocalDateTime date);

如何在不改变数据库模型的情况下加快速度?

表T (尺寸45k)

CREATE TABLE public.t 
(
  id bigint NOT NULL DEFAULT nextval('t_id_seq'::regclass),
  c_id bigint NOT NULL,
  m_id bigint NOT NULL,
  p_id bigint NOT NULL,
  CONSTRAINT t_pkey PRIMARY KEY (id),
  CONSTRAINT fkcdo362oanw5jshu29kavksyfy FOREIGN KEY (m_id)
    REFERENCES public.m (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fkjpyqqd0vys4jayau98eij2xv3 FOREIGN KEY (c_id)
    REFERENCES public.c (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fknjjuiq1kn44mu5299dn67t3np FOREIGN KEY (p_id)
    REFERENCES public.p (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
)

表C (尺寸45k)

CREATE TABLE public.c
(
  id bigint NOT NULL DEFAULT nextval('c_id_seq'::regclass),
  created_by character varying(255),
  created_date timestamp without time zone NOT NULL,
  m_id bigint,
  CONSTRAINT c_pkey PRIMARY KEY (id),
  CONSTRAINT fkbv33l9w17owvi5kgctqvaepn0 FOREIGN KEY (m_id)
    REFERENCES public.m (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
)

表M (尺寸200)

CREATE TABLE public.m
(
  id bigint NOT NULL DEFAULT nextval('m_id_seq'::regclass),
  created_by character varying(255),
  created_date timestamp without time zone NOT NULL,
  a_id bigint,
  CONSTRAINT m_pkey PRIMARY KEY (id),
  CONSTRAINT fkikqmae593j3mruwqy84pc56is FOREIGN KEY (a_id)
      REFERENCES public.a (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

表P (尺寸5k)

CREATE TABLE public.p
(
  id bigint NOT NULL,
  created_by character varying(255),
  created_date timestamp without time zone NOT NULL,
  e character varying(255) NOT NULL,
  a_id bigint,
  CONSTRAINT p_pkey PRIMARY KEY (id),
  CONSTRAINT fkehggtafv310ewdtcq772pwl01 FOREIGN KEY (a_id)
      REFERENCES public.a (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

1 回复 | 直到 7 年前

Lukas Eder 7 年前

This is a special case of the common "top N per category" problem ,SQL中有一些解决方案。为什么不直接使用本机SQL?您的查询可以转换为:

SELECT (t).*
FROM (
  SELECT t, ROW_NUMBER() OVER (PARTITION BY t.m_id, t.p_id ORDER BY t.id DESC) rn
  FROM t
  JOIN c ON t.c_id = c.id
  WHERE t.m_id = :m
  AND c.created_date < :date
) t
WHERE t.rn = 1

主要区别在于,您可以避免一次访问 T 桌子

当然,我假设您拥有所有适当的索引,包括所有外键上的索引和 C.CREATED_DATE 柱结果仍然可以映射到实体。

笔记 I'm using PostgreSQL specific syntax to nest records ,以便移除 RN 再次从投影中提取列要容易得多。