mysql - 使用许多连接优化 SQL 查询

Question

我正在尝试对我们需要为内部目的运行的查询进行故障排除。此查询不是应用程序的一部分，因此它不必那么高效，但我希望它尽可能快，以便我们可以获得前进所需的数据集。

我遇到的第一个问题是查询卡在统计状态。通过将以下内容添加到我们的 mySQL 配置中来解决这个问题......

optimizer_search_depth = 0

现在查询直接进入“将数据复制到 tmp 表”，到目前为止已经发生了 5 多分钟。我们的服务器非常强大，我们有 128GB 的​​ RAM，所以我认为这根本不是资源问题。我确实相信查询本身可能需要优化一点，如果可能的话，这就是我想要的指导。

“EXPLAIN SELECT”的结果can be seen here .在运行 EXPLAIN SELECT 之前，我对该数据库中的所有表(为一次性查询而创建)执行了“ANALYZE”。

查询本身在这里:

SELECT DISTINCT
      IAN.sku,
      IAN.notes,
      Parts.partterminologyname,
      BaseVehicle.YearID,
      Make.MakeName,
      Model.modelname,
      SubModel.SubModelName,
      CONCAT(EngineBase.Cylinders, ' Cyl ', EngineBase.Liter, 'L') as engine,
      Positions.position,
      BedLength.BedLength,
      BedLength.BedLengthMetric,
      BedType.BedTypeName,
      BodyNumDoors.BodyNumDoors,
      BodyType.BodyTypeName,
      FrontBrakeType.BrakeTypeName,
      RearBrakeType.BrakeTypeName,
      BrakeSystem.BrakeSystemName,
      BrakeABS.BrakeABSName,
      DriveType.DriveTypeName,
      EngineDesignation.EngineDesignationName,
      EngineVIN.EngineVINName,
      Valves.ValvesPerEngine,
      EngineBase.Liter,
      EngineBase.CC,
      EngineBase.CID,
      EngineBase.Cylinders,
      EngineBase.BlockType,
      EngineBase.EngBoreIn,
      EngineBase.EngBoreMetric,
      EngineBase.EngStrokeIn,
      EngineBase.EngStrokeMetric,
      FuelDeliveryType.FuelDeliveryTypeName,
      FuelDeliverySubType.FuelDeliverySubTypeName,
      FuelSystemControlType.FuelSystemControlTypeName,
      FuelSystemDesign.FuelSystemDesignName,
      Aspiration.AspirationName,
      CylinderHeadType.CylinderHeadTypeName,
      FuelType.FuelTypeName,
      IgnitionSystemType.IgnitionSystemTypeName,
      EngineMfr.MfrName,
      EngineVersion.EngineVersion,
      PowerOutput.HorsePower,
      PowerOutput.KilowattPower,
      MfrBodyCode.MfrBodyCodeName,
      SpringType.SpringTypeName,
      SteeringType.SteeringTypeName,
      SteeringSystem.SteeringSystemName,
      TransmissionType.TransmissionTypeName,
      TransmissionNumSpeeds.TransmissionNumSpeeds,
      TransmissionMfrCode.TransmissionMfrCode,
      TransElecControlled.ElecControlled,
      TransmissionMfr.MfrName,
      WheelBase.WheelBase,
      WheelBase.WheelBaseMetric
   FROM
      Import_AcesApplication_New IAN
         INNER JOIN BaseVehicle 
            ON IAN.base_vehicle_id = BaseVehicle.BaseVehicleID
            INNER JOIN Make 
               ON BaseVehicle.MakeID = Make.MakeID
            INNER JOIN Model 
               ON BaseVehicle.ModelID = Model.ModelID
         INNER JOIN Positions 
            ON IAN.position_id = Positions.PositionID
         INNER JOIN Parts 
            ON IAN.part_type_id = Parts.PartTerminologyID
         INNER JOIN Vehicle 
            ON IAN.base_vehicle_id = Vehicle.BaseVehicleID
            INNER JOIN SubModel 
               ON Vehicle.SubModelID = SubModel.SubModelID
            INNER JOIN VehicleConfig 
               ON Vehicle.VehicleID = VehicleConfig.VehicleID
               INNER JOIN EngineConfig 
                  ON VehicleConfig.EngineConfigID = EngineConfig.EngineConfigID
                  INNER JOIN EngineBase 
                     ON EngineConfig.EngineBaseID = EngineBase.EngineBaseID
                  INNER JOIN EngineDesignation 
                     ON EngineConfig.EngineDesignationID = EngineDesignation.EngineDesignationID
                  INNER JOIN EngineVIN 
                     ON EngineConfig.EngineVINID = EngineVIN.EngineVINID
                  INNER JOIN Valves 
                     ON EngineConfig.ValvesID = Valves.Valvesid
                  INNER JOIN FuelDeliveryConfig 
                     ON EngineConfig.FuelDeliveryConfigID = FuelDeliveryConfig.FuelDeliveryConfigID
                     INNER JOIN FuelDeliveryType 
                        ON FuelDeliveryConfig.FuelDeliveryTypeID = FuelDeliveryType.FuelDeliveryTypeID
                     INNER JOIN FuelDeliverySubType 
                        ON FuelDeliveryConfig.FuelDeliverySubTypeID = FuelDeliverySubType.FuelDeliverySubTypeID
                     INNER JOIN FuelSystemControlType 
                        ON FuelDeliveryConfig.FuelSystemControlTypeID = FuelSystemControlType.FuelSystemControlTypeID
                     INNER JOIN FuelSystemDesign 
                        ON FuelDeliveryConfig.FuelSystemDesignID = FuelSystemDesign.FuelSystemDesignID
                  INNER JOIN Aspiration 
                     ON EngineConfig.AspirationID = Aspiration.AspirationID
                  INNER JOIN CylinderHeadType 
                     ON EngineConfig.CylinderHeadTypeID = CylinderHeadType.CylinderHeadTypeID
                  INNER JOIN FuelType 
                     ON EngineConfig.FuelTypeID = FuelType.FuelTypeID
                  INNER JOIN IgnitionSystemType 
                     ON  EngineConfig.IgnitionSystemTypeID = IgnitionSystemType.IgnitionSystemTypeID
                  INNER JOIN Mfr EngineMfr 
                     ON EngineConfig.EngineMfrID = EngineMfr.MfrID
                  INNER JOIN EngineVersion 
                     ON EngineConfig.EngineVersionID = EngineVersion.EngineVersionID
                  INNER JOIN PowerOutput 
                     ON EngineConfig.PowerOutputId = PowerOutput.PowerOutputId
               INNER JOIN BedConfig 
                  ON VehicleConfig.BedConfigID = BedConfig.BedConfigID
                  INNER JOIN BedLength 
                     ON BedConfig.BedLengthID = BedLength.BedLengthID
                  INNER JOIN BedType 
                     ON BedConfig.BedTypeID = BedType.BedTypeID
               INNER JOIN BodyStyleConfig 
                  ON VehicleConfig.BodyStyleConfigID = BodyStyleConfig.BodyStyleConfigID
                  INNER JOIN BodyNumDoors 
                     ON BodyStyleConfig.BodyNumDoorsID = BodyNumDoors.BodyNumDoorsID
                  INNER JOIN BodyType 
                     ON BodyStyleConfig.BodyTypeID = BodyType.BodyTypeID
               INNER JOIN BrakeConfig 
                  ON VehicleConfig.BrakeConfigID = BrakeConfig.BrakeConfigID
                  INNER JOIN BrakeType FrontBrakeType 
                     ON BrakeConfig.FrontBrakeTypeID = FrontBrakeType.BrakeTypeID
                  INNER JOIN BrakeType RearBrakeType 
                     ON BrakeConfig.RearBrakeTypeID = RearBrakeType.BrakeTypeID
                  INNER JOIN BrakeSystem 
                     ON BrakeConfig.BrakeSystemID = BrakeSystem.BrakeSystemID
                  INNER JOIN BrakeABS 
                     ON BrakeConfig.BrakeABSID = BrakeABS.BrakeABSID
               INNER JOIN DriveType 
                  ON VehicleConfig.DriveTypeID = DriveType.DriveTypeID
               INNER JOIN MfrBodyCode 
                  ON VehicleConfig.MfrBodyCodeID = MfrBodyCode.MfrBodyCodeID
               INNER JOIN SpringType 
                  ON VehicleConfig.SpringTypeConfigID = SpringType.SpringTypeID
               INNER JOIN SteeringConfig 
                  ON VehicleConfig.SteeringConfigID = SteeringConfig.SteeringConfigID
                  INNER JOIN SteeringType 
                     ON SteeringConfig.SteeringConfigID = SteeringType.SteeringTypeID
                  INNER JOIN SteeringSystem 
                     ON SteeringConfig.SteeringSystemID = SteeringSystem.SteeringSystemID
               INNER JOIN Transmission 
                  ON VehicleConfig.TransmissionID = Transmission.TransmissionID
                  INNER JOIN TransmissionBase 
                     ON Transmission.TransmissionBaseID = TransmissionBase.TransmissionBaseID
                     INNER JOIN TransmissionType 
                        ON TransmissionBase.TransmissionTypeID = TransmissionType.TransmissionTypeID
                     INNER JOIN TransmissionNumSpeeds 
                        ON TransmissionBase.TransmissionNumSpeedsID = TransmissionNumSpeeds.TransmissionNumSpeedsID
                     INNER JOIN TransmissionControlType 
                        ON TransmissionBase.TransmissionControlTypeID = TransmissionControlType.TransmissionControlTypeID
                  INNER JOIN TransmissionMfrCode 
                     ON Transmission.TransmissionMfrCodeID = TransmissionMfrCode.TransmissionMfrCodeID
                  INNER JOIN ElecControlled TransElecControlled 
                     ON Transmission.TransmissionElecControlledID = TransElecControlled.ElecControlledID
                  INNER JOIN Mfr TransmissionMfr 
                     ON Transmission.TransmissionMfrID = TransmissionMfr.MfrID
               INNER JOIN WheelBase 
                  ON VehicleConfig.WheelbaseID = WheelBase.WheelBaseID
   LIMIT 
      0,10

由于数据库的设置方式，查询中有许多主要的 JOINS。数据库是我们行业的标准，我不想改变它。应该注意的是，这个查询可能只会每隔几个月运行一次，因此虽然优化它很重要，但它不一定非常高效。对最终结果的唯一期望是我们不会日复一日地等待数据准备就绪。

我很感激任何关于这方面的指导，如果这有点需要征求意见，我深表歉意。

Answer 1

您的查询看起来布局完美。您的主表是“Import_AcesApplication_New”表，其余所有表都是从主表派生的查找表。我有一个类似的问题，政府拨款/契约(Contract)查询查找 20 多个表并且查询被阻塞，这是针对 15+ 百万条记录

只需添加关键字

“STRAIGHT_JOIN”

选择 STRAIGHT_JOIN [其余查询]

它强制引擎“按照您提供的顺序”运行查询。它可能会尝试过度分析自己，了解哪个表具有最少的记录、最佳索引等，并通过一些奇怪的表选择向后工作。

此外，编辑原始查询以更好地通过层次结构显示关联链接以提高可读性

我希望您所有的查找表都在其主键上有索引，但您也可以通过覆盖索引受益，这样每次查找的原始数据页都不是必需的。

Table                       Index suggestion
Import_AcesApplication_New  ( sku, notes, base_vehicle_id, position_id, part_type_id )
BaseVehicle                 ( BaseVehicleID, MakeID, ModelID, YearID )
Make                        ( MakeID, MakeName )
Model                       ( ModelID, modelname )
Positions                   ( PositionID, Position )
Parts                       ( PartTerminologyID, partterminologyname )
Vehicle                     ( BaseVehicleID, SubModelID, VehicleID )
SubModel                    ( SubModelID, SubModelName )
VehicleConfig               ( VehicleID ) -- too many other fields to be practical
EngineConfig                ( EngineConfigID ) -- too many to be practical
EngineBase                  ( EngineBaseID ) -- too many to be practical
EngineDesignation           ( EngineDesignationID, EngineDesignationName )
EngineVIN                   ( EngineVINID, EngineVINName )
Valves                      ( Valvesid, ValvesPerEngine )
FuelDeliveryConfig          ( FuelDeliveryConfigID, FuelDeliveryTypeID, FuelDeliverySubTypeID, FuelSystemControlTypeID, FuelSystemDesignID )
FuelDeliveryType            ( FuelDeliveryTypeID, FuelDeliveryTypeName )
FuelDeliverySubType         ( FuelDeliverySubTypeID, FuelDeliverySubTypeName )
FuelSystemControlType       ( FuelSystemControlTypeID, FuelSystemControlTypeName )
FuelSystemDesign            ( FuelSystemDesignID, FuelSystemDesignName )
Aspiration                  ( AspirationID, AspirationName )
CylinderHeadType            ( CylinderHeadTypeID, CylinderHeadTypeName )
FuelType                    ( FuelTypeID, FuelTypeName )
IgnitionSystemType          ( IgnitionSystemTypeID, IgnitionSystemTypeName )
EngineMfr                   ( MfrID, MfrName )
EngineVersion               ( EngineVersionID, EngineVersion )
PowerOutput                 ( PowerOutputId, HorsePower, KilowattPower )
BedConfig                   ( BedConfigID, BedLengthID, BedTypeID )
BedLength                   ( BedLengthID, BedLength, BedLengthMetric 
BedType                     ( BedTypeID, BedTypeName )
BodyStyleConfig             ( BodyStyleConfigID, BodyNumDoorsID, BodyTypeID )
BodyNumDoors                ( BodyNumDoorsID, BodyNumDoors )
BodyType                    ( BodyTypeID, BodyTypeName )
BrakeConfig                 ( BrakeConfigID, FrontBrakeTypeID, RearBrakeTypeID, BrakeSystemID, BrakeABSID )
BrakeType                   ( BrakeTypeID, BrakeTypeName )
BrakeSystem                 ( BrakeSystemID, BrakeSystemName )
BrakeABS                    ( BrakeABSID, BrakeABSName )
DriveType                   ( DriveTypeID, DriveTypeName )
MfrBodyCode                 ( MfrBodyCodeID, MfrBodyCodeName )
SpringType                  ( SpringTypeID, SpringTypeName )
SteeringConfig              ( SteeringConfigID, SteeringTypeID, SteeringSystemID)  -- see note below indexes via JOIN
SteeringType                ( SteeringTypeID, SteeringTypeName )
SteeringSystem              ( SteeringSystemID, SteeringSystemName )
Transmission                ( TransmissionID, TransmissionBaseID )
TransmissionBase            ( TransmissionBaseID, TransmissionTypeID, TransmissionNumSpeedsID, TransmissionControlTypeID )
TransmissionType            ( TransmissionTypeID, TransmissionTypeName )
TransmissionNumSpeeds       ( TransmissionNumSpeedsID, TransmissionNumSpeeds ) 
TransmissionControlType     ( TransmissionControlTypeID,  ??? )  -- possible description column per note below
TransmissionMfrCode         ( TransmissionMfrCodeID, TransmissionMfrCode )
ElecControlled              ( ElecControlledID, ElecControlled )
Mfr                         ( MfrID, MfrName )
WheelBase                   ( WheelBaseID, WheelBase, WheelBaseMetric )

此外，在查看 JOIN 以获得覆盖索引时，我注意到转向配置与转向类型的连接是基于

ON SteeringConfig.SteeringConfigID = SteeringType.SteeringTypeID

这应该是……

ON SteeringConfig.SteeringTypeID = SteeringType.SteeringTypeID

在另一个项目上... TransmissionControlType 已连接到，但您没有从输出查询中提取任何列...可能是对这么多表/连接的轻微疏忽。

这些应该对查询中的覆盖索引有很大帮助