WSO2 API Manager对Store Rest API的SQL查询变慢，导致UI性能下降

部署如下：

• Mgr节点：默认（Store + Pub），网关管理器。

• 工作者节点：GW-Workers，密钥管理器

我们使用MySQL NDB作为我们的API Manager数据库。在商店用户界面上观察到非常缓慢的API加载时间。使用Chrome Dev-Tools，我看到单击API打开（使用登录的会话）时，它将调用以下API：

http：// {host / port} / store / apis / info？name = {api-name}＆version = {v}＆provider = {provider}

手动调用API（包括cookie）在我们的负载均衡器上超时（504）。在Store Jaggery文件中查找，我发现它似乎调用了该​​API（../repository/deplyement/server/jaggeryapps/store/modules/api/api.jag），但是我无法将其与实际的API Impl相关联。使用常识，我认为需要从数据库中检索API及其相关的元数据。

启用慢查询日志（25s）会产生以下查询，当我尝试打开API时将记录该查询：

SELECT
  ICA.CONSUMER_KEY AS CONSUMER_KEY,
  ICA.CONSUMER_SECRET AS CONSUMER_SECRET,
  IAT.ACCESS_TOKEN AS ACCESS_TOKEN,
  IAT.VALIDITY_PERIOD AS VALIDITY_PERIOD,
  ISAT.TOKEN_SCOPE AS TOKEN_SCOPE,
  AKM.KEY_TYPE AS TOKEN_TYPE,
  AKM.STATE AS STATE
FROM
  AM_APPLICATION_KEY_MAPPING AKM,
  IDN_OAUTH2_ACCESS_TOKEN IAT,
  IDN_OAUTH2_ACCESS_TOKEN_SCOPE ISAT,
  IDN_OAUTH_CONSUMER_APPS ICA
WHERE AKM.APPLICATION_ID = 149
  AND IAT.USER_TYPE = 'APPLICATION'
  AND ICA.CONSUMER_KEY = AKM.CONSUMER_KEY
  AND IAT.CONSUMER_KEY_ID = ICA.ID
  AND IAT.TOKEN_ID = ISAT.TOKEN_ID
  AND AKM.KEY_TYPE = 'PRODUCTION'
  AND (
    IAT.TOKEN_STATE = 'ACTIVE'
    OR IAT.TOKEN_STATE = 'EXPIRED'
    OR IAT.TOKEN_STATE = 'REVOKED'
  )
ORDER BY IAT.TIME_CREATED DESC;

该查询平均需要80秒钟才能执行，返回约33000行。由于没有联接，该查询的编写不佳，并且其以下优化版本在<3s内返回相同的结果集：

SELECT
  `api_am_dev_1`.`ICA`.`CONSUMER_KEY` AS `CONSUMER_KEY`,
  `api_am_dev_1`.`ICA`.`CONSUMER_SECRET` AS `CONSUMER_SECRET`,
  `api_am_dev_1`.`IAT`.`ACCESS_TOKEN` AS `ACCESS_TOKEN`,
  `api_am_dev_1`.`IAT`.`VALIDITY_PERIOD` AS `VALIDITY_PERIOD`,
  `api_am_dev_1`.`ISAT`.`TOKEN_SCOPE` AS `TOKEN_SCOPE`,
  `api_am_dev_1`.`AKM`.`KEY_TYPE` AS `TOKEN_TYPE`,
  `api_am_dev_1`.`AKM`.`STATE` AS `STATE`
FROM
  `api_am_dev_1`.`AM_APPLICATION_KEY_MAPPING` `AKM`
  JOIN `api_am_dev_1`.`IDN_OAUTH2_ACCESS_TOKEN` `IAT`
  JOIN `api_am_dev_1`.`IDN_OAUTH2_ACCESS_TOKEN_SCOPE` `ISAT`
  JOIN `api_am_dev_1`.`IDN_OAUTH_CONSUMER_APPS` `ICA`
WHERE (
    (
      `api_am_dev_1`.`AKM`.`KEY_TYPE` = 'PRODUCTION'
    )
    AND (
      `api_am_dev_1`.`ISAT`.`TOKEN_ID` = `api_am_dev_1`.`IAT`.`TOKEN_ID`
    )
    AND (
      `api_am_dev_1`.`IAT`.`CONSUMER_KEY_ID` = `api_am_dev_1`.`ICA`.`ID`
    )
    AND (
      `api_am_dev_1`.`ICA`.`CONSUMER_KEY` = `api_am_dev_1`.`AKM`.`CONSUMER_KEY`
    )
    AND (
      `api_am_dev_1`.`IAT`.`USER_TYPE` = 'APPLICATION'
    )
    AND (
      `api_am_dev_1`.`AKM`.`APPLICATION_ID` = 149
    )
    AND (
      (
        `api_am_dev_1`.`IAT`.`TOKEN_STATE` = 'ACTIVE'
      )
      OR (
        `api_am_dev_1`.`IAT`.`TOKEN_STATE` = 'EXPIRED'
      )
      OR (
        `api_am_dev_1`.`IAT`.`TOKEN_STATE` = 'REVOKED'
      )
    )
  )
ORDER BY `api_am_dev_1`.`IAT`.`TIME_CREATED` DESC

添加索引似乎无济于事。我们每周进行一次令牌清理，因此数据库中总共只有约20000个令牌〜我们的质量检查非常忙。

问题：查询是由Hibernate生成的，并且是创建补丁的唯一选择吗？

WSO2商店是一个了不起的开发人员门户，但这破坏了许多用户的体验。

编辑：

API Manager版本= 2.1.0