SQL查询在统计状态中被阻塞 [英] SQL Query Stuck in Statistics State

问题描述

我一直在对这个复杂的查询进行疑难解答，认为这是一个性能问题。看来，查询正在陷入统计状态。它目前处于该状态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 ）SEPARATOR'，'），
 GROUP_CONCAT（DISTINCT（EngineBase.CC）SEPARATOR'，'），
 GROUP_CONCAT（DISTINCT（EngineBase.CID）SEPARATOR'，'），
 GROUP_CONCAT （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 .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零件
 ON Import_Values.part_type_id = Parts.PartTerminologyID 
 INNER JOIN Make 
 ON BaseVehicle.MakeID = Make。 MakeID 
 INNER JOIN模型
 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 
关于Vehicle.VehicleID = VehicleToEngineConfig.VehicleID 
 INNER JOIN EngineConfig 
关于VehicleToEngineConfig.EngineConfigID = EngineConfig.EngineConfigID 
 INNER JOIN EngineDesignation 
关于EngineConfig.EngineDesignationID = EngineDesignation.EngineDesignationID 
 INNER JOIN EngineVIN 
关于EngineConfig.EngineVINID = EngineVIN.EngineVINID 
 INNER JOIN EngineBase 
关于EngineConfig.EngineBaseID = EngineBase.EngineBaseID 
 INNER JOIN FuelDeliveryConfig 
关于EngineConfig.FuelDeliveryConfigID = FuelDeliveryConfig.FuelDeliveryConfigID 
 INNER JOIN FuelDeliveryType 
关于FuelDeliveryConfig.FuelDeliveryTypeID = FuelDeliveryType.FuelDeliveryTypeID 
 INNER JOIN FuelDeliverySubType 
在FuelDeliveryConfig.FuelDeliverySubTypeID = FuelDeliverySubType.FuelDeliverySubTypeID 
 INNER JOIN FuelSystemControlType 
关于FuelDeliveryConfig.FuelSystemControlTypeID = FuelSystemControlType.FuelSystemControlTypeID 
 INNER JOIN FuelSystemDesign 
关于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 
关于EngineConfig.IgnitionSystemTypeID = IgnitionSystemType.IgnitionSystemTypeID 
 INNER JOIN出厂
关于EngineConfig.EngineMfrID = Mfr.MfrID 
 INNER JOIN ENGINEVERSION 
 ON EngineConfig.EngineVersionID = EngineVersion.EngineVersionID 
 INNER JOIN阀
 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 
  

解决方案最近遇到同样的问题：MySQL开始窒息'statistics'）对大量表连接在一起的查询。我找到了一个很好的博文

基本上至少在MySQL 5.5中，配置参数 optimizer_search_depth 的默认值为62将导致查询优化器随着查询中的表数量增加而指数地增加时间。在某个点之后，它将开始花费几天或更长时间来完成查询。

但是，如果您在 my.cnf 中将 optimizer_search_depth 设置为 0 < ，MySQL自动选择合适的深度，但将其限制为7，这还不太复杂。

显然这个问题已经被固定在MySQL 5.6，但我还没有自己测试它。

I've been troubleshooting this complex query today, thinking it was a performance issue. It appears that the query is getting stuck into the 'Statistics' state. It is currently in that state for 1300 seconds.

I've checked the indexes for the tables involved -- I've optimized the table -- what could be causing this hang?

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

解决方案

I ran into the same problem recently: MySQL started to choke (stuck in state 'statistics') on queries with a lot of tables joined together. I found a good blog post explaining why this happens and how to solve it.

Basically at least in MySQL 5.5, the default value for the configuration parameter optimizer_search_depth is 62 which will cause the query optimizer to take exponentially more time as the number of tables in the query increases. After a certain point it will start to take days or even longer to finish the query.

However, if you set the optimizer_search_depth to 0 in your my.cnf, MySQL automatically chooses suitable depth, but limits it to 7 which is not yet too complex.

Apparently this issue has been fixed in MySQL 5.6 but I haven't tested it myself yet.

这篇关于SQL查询在统计状态中被阻塞的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！