java - 查询性能差，选择少量字段比选择许多字段慢 10 倍

Question

我的团队在使用 SQL Server 数据库时不断遇到性能问题。首先，该应用程序是用 Java 编写的并使用 Hibernate。我们有一些数据存储在数据库中，我们可以轻松顺利地检索这些数据，以防我们想要选择完整的对象(例如:给定表中的所有或大部分字段)。

这很好用，但是当我们执行较小的查询时，它会慢 10 倍。最明显的部分可能是我们只检索一小部分字段(在本例中:3)并使用其中带有 like 的 WHERE 子句(检索 84 个字段时这似乎不是问题，但当检索 3 个字段)。

Hibernate 完成简单查询大约需要 7 秒，而大型查询大约需要 3.4 秒。在 DbVisualizer 中运行纯 SQL 时，复杂查询需要 0.02 秒的查询时间和 0.7 秒的传输数据时间。在 DbVisualizer 中运行的简单查询耗时高达 0.2 秒(慢 10 倍)，然后传输仅用了大约 0.36 秒(因此，传输的数据更少)。有人可能会争辩说这仍然比大查询更快，但它似乎对 Hibernate 性能有某种影响。

我开玩笑说“只需添加额外的字段，直到该死的东西运行得很快!”，但这并不能解决问题，因为要选择的字段和 where 子句都将是用户配置。

84 字段查询如下所示:

select
          locateable0_.Un_ID as Un1_37_,
          locateable0_.active as active37_,
          locateable0_.code as code37_,
          locateable0_.name as name37_,
          locateable0_.ClientID as ClientID37_,
          locateable0_.equipmentGroup as equipmen7_37_,
          locateable0_.SupplierContractID as Supplier8_37_,
          locateable0_.orderingCode as ordering9_37_,
          locateable0_.expirationDate as expirat10_37_,
          locateable0_.purchaseDate as purchas11_37_,
          locateable0_.warrantyExpirationAlert as warrant12_37_,
          locateable0_.price as price37_,
          locateable0_.priceUnit as priceUnit37_,
          locateable0_.VatID as VatID37_,
          locateable0_.WEAlertJobID as WEAlert16_37_,
          locateable0_.barcode as barcode37_,
          locateable0_.erpCode as erpCode37_,
          locateable0_.description as descrip19_37_,
          locateable0_.innerWorksheet as innerWo20_37_,
          locateable0_.outerWorksheet as outerWo21_37_,
          locateable0_.ContractorCompanyID as Contrac22_37_,
          locateable0_.ContractorPersonID as Contrac23_37_,
          locateable0_.ManufacturerCompanyID as Manufac24_37_,
          locateable0_.ManufacturerPersonID as Manufac25_37_,
          locateable0_.ServicerCompanyID as Service26_37_,
          locateable0_.ServicerPersonID as Service27_37_,
          locateable0_.BudgetID as BudgetID37_,
          locateable0_.costObjectId as costObj29_37_,
          locateable0_.costKindId as costKindId37_,
          locateable0_.TemplateId as TemplateId37_,
          locateable0_.openingDate as opening32_37_,
          locateable0_.mainClassId as mainCla33_37_,
          locateable0_.pictureId as pictureId37_,
          locateable0_.productType as product35_37_,
          locateable0_.propertiesId as propert36_37_,
          locateable0_.ParentID as ParentID37_,
          locateable0_.accessDomainId as accessD38_37_,
          locateable0_.orderStateType as orderSt39_37_,
          locateable0_.orderEventId as orderEv40_37_,
          locateable0_.locationId as locationId37_,
          locateable0_.siteId as siteId37_,
          locateable0_.buildingId as buildingId37_,
          locateable0_.storeyId as storeyId37_,
          locateable0_.roomId as roomId37_,
          locateable0_.cadObjectId as cadObje46_37_,
          locateable0_.geoLattitude as geoLatt47_37_,
          locateable0_.geoLongitude as geoLong48_37_,
          locateable0_.ratingId as ratingId37_,
          locateable0_.grossArea as grossArea37_,
          locateable0_.grossVolume as grossVo51_37_,
          locateable0_1_.floor as floor99_,
          locateable0_2_.calculatedArea as calculat2_100_,
          locateable0_2_.nominalArea as nominalA3_100_,
          locateable0_2_.categoryId as categoryId100_,
          locateable0_2_.wingId as wingId100_,
          locateable0_2_.areaUnitId as areaUnitId100_,
          locateable0_2_.cleaningArea as cleaning7_100_,
          locateable0_2_.rentableArea as rentable8_100_,
          locateable0_2_.windowSurface as windowSu9_100_,
          locateable0_2_.bottomSurface as bottomS10_100_,
          locateable0_2_.topSurface as topSurface100_,
          locateable0_2_.wallSurface as wallSur12_100_,
          locateable0_2_.floorType as floorType100_,
          locateable0_3_.areaSize as areaSize101_,
          locateable0_3_.areaType as areaType101_,
          locateable0_3_.flooring as flooring101_,
          locateable0_4_.workplaceNumber as workplac2_102_,
          locateable0_4_.workplaceType as workplac3_102_,
          locateable0_4_.usingCompanyId as usingCom4_102_,
          locateable0_5_.quantity as quantity190_,
          locateable0_5_.quantityUnitId as quantity3_190_,
          locateable0_6_.inventoryNumber as inventor2_202_,
          locateable0_7_.area as area233_,
          locateable0_7_.meterValue as meterValue233_,
          locateable0_7_.calibrationFactor as calibrat4_233_,
          locateable0_7_.areaDomainId as areaDoma5_233_,
          locateable0_7_.degreeDayId as degreeDa6_233_,
          locateable0_7_.virtualType as virtualT7_233_,
          locateable0_7_.differenceFactor as differen8_233_,
          locateable0_7_.differenceMasterId as differen9_233_,
          locateable0_7_.virtual as virtual233_,
          locateable0_7_.startDate as startDate233_,
          locateable0_7_.scheduleId as scheduleId233_,
          locateable0_.discriminator as discrimi2_37_
      from
          MNT_Equipments locateable0_
      left outer join
          Storey locateable0_1_
              on locateable0_.Un_ID=locateable0_1_.id
      left outer join
          Room locateable0_2_
              on locateable0_.Un_ID=locateable0_2_.id
      left outer join
          RoomArea locateable0_3_
              on locateable0_.Un_ID=locateable0_3_.id
      left outer join
          Workplace locateable0_4_
              on locateable0_.Un_ID=locateable0_4_.id
      left outer join
          INV_Product locateable0_5_
              on locateable0_.Un_ID=locateable0_5_.id
      left outer join
          MNT_FacilityProduct locateable0_6_
              on locateable0_.Un_ID=locateable0_6_.id
      left outer join
          EN_ResourceMeter locateable0_7_
              on locateable0_.Un_ID=locateable0_7_.id
      left outer join
          DynamicProperties dynamicpro1_
              on locateable0_.propertiesId=dynamicpro1_.id
      left outer join
          KEYWORDSEARCH tags2_
              on dynamicpro1_.id=tags2_.dynamicPropertiesId
      left outer join
          MNT_Clients client3_
              on locateable0_.ClientID=client3_.Un_ID
      where
          (
              locateable0_.accessDomainId is null
              or locateable0_.accessDomainId in       (
                  select
                      uad.domainId
                  from
                      PERM_UserAccessDomain uad
                  join
                      Users u
                          on uad.userId = u.Un_ID
                  where
                      u.UserName = 'wsc'
                      and uad.functionId = 2000
              )
          )
          and locateable0_.discriminator in (
              '17000', '6010', '6020', '6030', '6035', '6040', '6060', '6070', '6080', '18000', '5010', '6000', '6100', '5000', '9000', '14000', '19000'
          )
          and (
              client3_.Un_ID in (
           1012, 1016, 1013, 1014
              )
          )
          and (
              locateable0_.productType not in  (
   18000, 19000
              )
          )
          and locateable0_.active=1
          and locateable0_.productType<>6080
          and (
              upper(tags2_.keyword) like 'B%'
          )

缓慢的 3 字段查询如下所示:

select
       locateable0_.Un_ID as col_0_0_,
       locateable0_.code as col_1_0_,
       locateable0_.name as col_2_0_
   from
       MNT_Equipments locateable0_
   left outer join
       DynamicProperties dynamicpro1_
           on locateable0_.propertiesId=dynamicpro1_.id
   left outer join
       KEYWORDSEARCH tags2_
           on dynamicpro1_.id=tags2_.dynamicPropertiesId
   left outer join
       MNT_Clients client3_
           on locateable0_.ClientID=client3_.Un_ID
   where
       (
           locateable0_.accessDomainId is null
           or locateable0_.accessDomainId in       (
               select
                   uad.domainId
               from
                   PERM_UserAccessDomain uad
               join
                   Users u
                       on uad.userId = u.Un_ID
               where
                   u.UserName = 'wsc'
                   and uad.functionId = 2000
           )
       )
       and locateable0_.discriminator in (
           '17000', '6010', '6020', '6030', '6035', '6040', '6060', '6070', '6080', '18000', '5010', '6000', '6100', '5000', '9000', '14000', '19000'
       )
       and (
           client3_.Un_ID in (
   1012, 1016, 1013, 1014
           )
       )
       and (
           locateable0_.productType not in  (
   18000, 19000
           )
       )
       and locateable0_.active=1
       and (
           upper(tags2_.keyword) like 'B%'
       )

Microsoft SQL Server Management Studio 建议为我们请求的字段添加一个索引，但这样做根本没有帮助。特别是要记住要查询的字段将是用户可配置的，并且 hibernate 将在查询和获取所花费的时间之上增加巨大的好处。

那么，我们可以做些什么来将性能提高到可接受的水平？

Answer 1

我会注意到您的 where 子句有所不同，第一个是 this:

and locateable0_.productType<>6080

第二个就不见了。

您的数据库中是否可能有更多关于此产品类型的记录？即您的第二个查询返回的行数是否比第一个多？