mysql 状态统计查询,mysql – SQL查询滞留在统计状态

我今天一直在排查这个复杂的查询,认为这是一个性能问题.看来,查询正在陷入“统计”状态.目前处于1300秒的状态.

我检查了所涉及的表的索引 – 我优化了表 – 可能导致此挂起？

SELECT

Import_Values.id,

Import_Values.part_id,

Import_Values.qty,

Import_Values.note,

Parts.partterminologyname,

GROUP_CONCAT(BaseVehicle.YearID, ' ', Make.MakeName, ' ', Model.modelname, ' ', SubModel.SubModelName SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(EngineDesignation.EngineDesignationName) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(EngineVIN.EngineVINName) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(EngineBase.Liter) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(EngineBase.CC) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(EngineBase.CID) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(EngineBase.Cylinders) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(EngineBase.BlockType) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(EngineBase.EngBoreIn) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(EngineBase.EngBoreMetric) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(EngineBase.EngStrokeIn) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(EngineBase.EngStrokeMetric) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(FuelDeliveryType.FuelDeliveryTypeName) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(FuelDeliverySubType.FuelDeliverySubTypeName) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(FuelSystemControlType.FuelSystemControlTypeName) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(FuelSystemDesign.FuelSystemDesignName) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(Aspiration.AspirationName) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(CylinderHeadType.CylinderHeadTypeName) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(FuelType.FuelTypeName) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(IgnitionSystemType.IgnitionSystemTypeName) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(Mfr.MfrName) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(EngineVersion.EngineVersion) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(Valves.ValvesPerEngine) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(BedLength.BedLength) SEPARATOR ', '),

GROUP_CONCAT(DISTINCT(BedLength.BedLengthMetric) SEPARATOR ', ')

FROM

Import_Values

INNER JOIN BaseVehicle

ON Import_Values.base_vehicle_id=BaseVehicle.BaseVehicleID

INNER JOIN Parts

ON Import_Values.part_type_id=Parts.PartTerminologyID

INNER JOIN Make

ON BaseVehicle.MakeID=Make.MakeID

INNER JOIN Model

ON BaseVehicle.ModelID=Model.ModelID

INNER JOIN Vehicle

ON Import_Values.base_vehicle_id=Vehicle.BaseVehicleID

INNER JOIN SubModel

ON Vehicle.SubModelID=SubModel.SubModelID

INNER JOIN VehicleToEngineConfig

ON Vehicle.VehicleID=VehicleToEngineConfig.VehicleID

INNER JOIN EngineConfig

ON VehicleToEngineConfig.EngineConfigID=EngineConfig.EngineConfigID

INNER JOIN EngineDesignation

ON EngineConfig.EngineDesignationID=EngineDesignation.EngineDesignationID

INNER JOIN EngineVIN

ON EngineConfig.EngineVINID=EngineVIN.EngineVINID

INNER JOIN EngineBase

ON EngineConfig.EngineBaseID=EngineBase.EngineBaseID

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

ON EngineConfig.EngineMfrID=Mfr.MfrID

INNER JOIN EngineVersion

ON EngineConfig.EngineVersionID=EngineVersion.EngineVersionID

INNER JOIN Valves

ON EngineConfig.ValvesID=Valves.Valvesid

INNER JOIN VehicleToBedConfig

ON Vehicle.VehicleID=VehicleToBedConfig.VehicleID

INNER JOIN BedConfig

ON VehicleToBedConfig.BedConfigID=BedConfig.BedConfigID

INNER JOIN BedLength

ON BedConfig.BedLengthID=BedLength.BedLengthID

GROUP BY part_id