时间戳

首页 > 解决方案 > 带有 in 子句的 H2 慢查询不使用索引

java - 带有 in 子句的 H2 慢查询不使用索引

问题描述

我在内存配置中嵌入了数据库：

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;LOCK_TIMEOUT=4000

我有以下查询用休眠条件api构造了什么，当我执行它时它会永远执行，并且它会锁定所有我无法读写的h2操作。

最大的表是 priceline，有 18 000 条记录。似乎出于某种原因，where子句中使用的字段不使用我的索引（我对所有用于查询的字段都有非唯一索引，ID 有唯一索引）

计划 

SELECT
   * 
FROM PUBLIC.EVENTS EVENT0_
    /* PUBLIC.EVENT_SPORTID_INDX: SPORTID IN(227, 239, 240) */
    /* WHERE EVENT0_.SPORTID IN(227, 239, 240)
    */
    /* scanCount: 137 */
LEFT OUTER JOIN PUBLIC.MARKETS MARKETS1_
    /* PUBLIC.FK7ODW956QJK1DITHJORMVBIV3Q_INDEX_5: EVENTID = EVENT0_.ID */
    ON EVENT0_.ID = MARKETS1_.EVENTID
    /* scanCount: 3230 */
LEFT OUTER JOIN PUBLIC.OUTCOMES OUTCOMES2_
    /* PUBLIC.FK2UJCQIHU8G4B8BQY4JEEY8Y8V_INDEX_3: MARKETID = MARKETS1_.ID */
    ON MARKETS1_.ID = OUTCOMES2_.MARKETID
    /* scanCount: 20599 */
LEFT OUTER JOIN PUBLIC.PRICELINES PRICELINES3_
    /* PUBLIC.FK6PK92YV9ASVTBBIF95R5UWU9N_INDEX_C: OUTCOMEID = OUTCOMES2_.ID */
    ON OUTCOMES2_.ID = PRICELINES3_.OUTCOMEID
    /* scanCount: 36140 */
LEFT OUTER JOIN PUBLIC.OUTCOME_DESCRIPTIONS OUTCOMEDES4_
    /* PUBLIC.FK9AMHE7T653TOKDBK97HK31D4Q_INDEX_7: OUTCOMEID = OUTCOMES2_.ID */
    ON OUTCOMES2_.ID = OUTCOMEDES4_.OUTCOMEID
    /* scanCount: 260890 */
LEFT OUTER JOIN PUBLIC.MARKET_DESCRIPTIONS MARKETDESC5_
    /* PUBLIC.FK5X545B89XD3COBCN7EFM04RJC_INDEX_A: MARKETID = MARKETS1_.ID */
    ON MARKETS1_.ID = MARKETDESC5_.MARKETID
    /* scanCount: 3391570 */
LEFT OUTER JOIN PUBLIC.MARKETFILTER_MAPPING FILTERS6_
    /* PUBLIC.PRIMARY_KEY_E: EVENTID = EVENT0_.ID */
    ON EVENT0_.ID = FILTERS6_.EVENTID
    /* scanCount: 26881816 */
LEFT OUTER JOIN PUBLIC.MARKET_FILTERS MARKETFILT7_
    /* PUBLIC.PRIMARY_KEY_9: ID = FILTERS6_.MARKETFILTERID */
    ON FILTERS6_.MARKETFILTERID = MARKETFILT7_.ID
    /* scanCount: 47472776 */
LEFT OUTER JOIN PUBLIC.EVENT_DESCRIPTIONS EVENTDESCR8_
    /* PUBLIC.FKOR6YEH06F2B0D389L2SYV1LX8_INDEX_F: EVENTID = EVENT0_.ID */
    ON EVENT0_.ID = EVENTDESCR8_.EVENTID
    /* scanCount: 332363850 */
WHERE (OUTCOMEDES4_.LOCALE = 'en-GB')
    AND ((MARKETDESC5_.LOCALE = 'en-GB')
    AND ((EVENTDESCR8_.LOCALE = 'en-GB')
    AND ((MARKETFILT7_.PERIODID IN(209, 113, 100))
    AND ((MARKETS1_.PERIODID IN(209, 113, 100))
    AND ((MARKETFILT7_.MARKETTYPEID IN(3059, 68, 1))
    AND ((MARKETS1_.TYPEID IN(3059, 68, 1))
    AND ((EVENT0_.SPORTID IN(227, 239, 240))
    AND (PRICELINES3_.PRICELINEID = 2))))))))
ORDER BY 10 DESC
(1 row, 152204 ms)

标签： javasqlhibernateh2

解决方案

推荐阅读