mysql - 使用 128GB RAM 进行查询

Question

我一直在努力处理一个大型查询，但我还没有成功运行过一次。每次我运行它时，它都会意外地显示“Killed”。最终在查看之后，我看到内存使用量在大约 20-30 分钟的过程中从 10GB 上升到 128GB，然后进程被终止并且内存恢复正常。

EXPLAIN SELECT 结果 can be seen here .

查询本身可以在这里看到:

SELECT
Import_AcesApplication_New.sku,
Import_AcesApplication_New.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,
SpringType.SpringTypeName,
SteeringType.SteeringTypeName,
SteeringSystem.SteeringSystemName,
TransmissionType.TransmissionTypeName,
TransmissionNumSpeeds.TransmissionNumSpeeds,
TransmissionMfrCode.TransmissionMfrCode,
TransElecControlled.ElecControlled,
TransmissionMfr.MfrName,
WheelBase.WheelBase,
WheelBase.WheelBaseMetric,
VehicleType.VehicleTypeName,
VehicleTypeGroup.VehicleTypeGroupName
FROM
Import_AcesApplication_New
STRAIGHT_JOIN BaseVehicle ON Import_AcesApplication_New.base_vehicle_id=BaseVehicle.BaseVehicleID
STRAIGHT_JOIN Parts ON Import_AcesApplication_New.part_type_id=Parts.PartTerminologyID
STRAIGHT_JOIN Make ON BaseVehicle.MakeID=Make.MakeID
STRAIGHT_JOIN Model ON BaseVehicle.ModelID=Model.ModelID
STRAIGHT_JOIN Vehicle ON Import_AcesApplication_New.base_vehicle_id=Vehicle.BaseVehicleID
STRAIGHT_JOIN Submodel ON Vehicle.SubmodelID=Submodel.SubmodelID
STRAIGHT_JOIN VehicleToBedConfig ON Vehicle.VehicleID=VehicleToBedConfig.VehicleID
STRAIGHT_JOIN VehicleToBodyStyleConfig ON Vehicle.VehicleID=VehicleToBodyStyleConfig.VehicleID
STRAIGHT_JOIN VehicleToBrakeConfig ON Vehicle.VehicleID=VehicleToBrakeConfig.VehicleID
STRAIGHT_JOIN VehicleToDriveType ON Vehicle.VehicleID=VehicleToDriveType.VehicleID
STRAIGHT_JOIN VehicleToEngineConfig ON Vehicle.VehicleID=VehicleToEngineConfig.VehicleID
STRAIGHT_JOIN VehicleToSpringTypeConfig ON Vehicle.VehicleID=VehicleToSpringTypeConfig.VehicleID
STRAIGHT_JOIN VehicleToSteeringConfig ON Vehicle.VehicleID=VehicleToSteeringConfig.VehicleID
STRAIGHT_JOIN VehicleToTransmission ON Vehicle.VehicleID=VehicleToTransmission.VehicleID
STRAIGHT_JOIN VehicleToWheelbase ON Vehicle.VehicleID=VehicleToWheelbase.VehicleID
STRAIGHT_JOIN EngineConfig ON VehicleToEngineConfig.EngineConfigID=EngineConfig.EngineConfigID
STRAIGHT_JOIN EngineBase ON EngineConfig.EngineBaseID=EngineBase.EngineBaseID
STRAIGHT_JOIN Positions ON Import_AcesApplication_New.position_id=Positions.PositionID
STRAIGHT_JOIN BedConfig ON VehicleToBedConfig.BedConfigID=BedConfig.BedConfigID
STRAIGHT_JOIN BedLength ON BedConfig.BedLengthID=BedLength.BedLengthID
STRAIGHT_JOIN BedType ON BedConfig.BedTypeID=BedType.BedTypeID
STRAIGHT_JOIN BodyStyleConfig ON VehicleToBodyStyleConfig.BodyStyleConfigID=BodyStyleConfig.BodyStyleConfigID
STRAIGHT_JOIN BodyNumDoors ON BodyStyleConfig.BodyNumDoorsID=BodyNumDoors.BodyNumDoorsID
STRAIGHT_JOIN BodyType ON BodyStyleConfig.BodyTypeID=BodyType.BodyTypeID
STRAIGHT_JOIN BrakeConfig ON VehicleToBrakeConfig.BrakeConfigID=BrakeConfig.BrakeConfigID
STRAIGHT_JOIN BrakeType FrontBrakeType ON BrakeConfig.FrontBrakeTypeID=FrontBrakeType.BrakeTypeID
STRAIGHT_JOIN BrakeType RearBrakeType ON BrakeConfig.RearBrakeTypeID=RearBrakeType.BrakeTypeID
STRAIGHT_JOIN BrakeSystem ON BrakeConfig.BrakeSystemID=BrakeSystem.BrakeSystemID
STRAIGHT_JOIN BrakeABS ON BrakeConfig.BrakeABSID=BrakeABS.BrakeABSID
STRAIGHT_JOIN DriveType ON VehicleToDriveType.DriveTypeID=DriveType.DriveTypeID
STRAIGHT_JOIN EngineDesignation ON EngineConfig.EngineDesignationID=EngineDesignation.EngineDesignationID
STRAIGHT_JOIN EngineVIN ON EngineConfig.EngineVINID=EngineVIN.EngineVINID
STRAIGHT_JOIN Valves ON EngineConfig.ValvesID=Valves.Valvesid
STRAIGHT_JOIN FuelDeliveryConfig ON EngineConfig.FuelDeliveryConfigID=FuelDeliveryConfig.FuelDeliveryConfigID
STRAIGHT_JOIN FuelDeliveryType ON FuelDeliveryConfig.FuelDeliveryTypeID=FuelDeliveryType.FuelDeliveryTypeID
STRAIGHT_JOIN FuelDeliverySubType ON FuelDeliveryConfig.FuelDeliverySubTypeID=FuelDeliverySubType.FuelDeliverySubTypeID
STRAIGHT_JOIN FuelSystemControlType ON FuelDeliveryConfig.FuelSystemControlTypeID=FuelSystemControlType.FuelSystemControlTypeID
STRAIGHT_JOIN FuelSystemDesign ON FuelDeliveryConfig.FuelSystemDesignID=FuelSystemDesign.FuelSystemDesignID
STRAIGHT_JOIN Aspiration ON EngineConfig.AspirationID=Aspiration.AspirationID
STRAIGHT_JOIN CylinderHeadType ON EngineConfig.CylinderHeadTypeID=CylinderHeadType.CylinderHeadTypeID
STRAIGHT_JOIN FuelType ON EngineConfig.FuelTypeID=FuelType.FuelTypeID
STRAIGHT_JOIN IgnitionSystemType ON EngineConfig.IgnitionSystemTypeID=IgnitionSystemType.IgnitionSystemTypeID
STRAIGHT_JOIN Mfr EngineMfr ON EngineConfig.EngineMfrID=EngineMfr.MfrID
STRAIGHT_JOIN EngineVersion ON EngineConfig.EngineVersionID=EngineVersion.EngineVersionID
STRAIGHT_JOIN PowerOutput ON EngineConfig.PowerOutputId=PowerOutput.PowerOutputId
STRAIGHT_JOIN SpringType ON VehicleToSpringTypeConfig.SpringTypeConfigID=SpringType.SpringTypeID
STRAIGHT_JOIN SteeringConfig ON VehicleToSteeringConfig.SteeringConfigID=SteeringConfig.SteeringConfigID
STRAIGHT_JOIN SteeringType ON SteeringConfig.SteeringConfigID=SteeringType.SteeringTypeID
STRAIGHT_JOIN SteeringSystem ON SteeringConfig.SteeringSystemID=SteeringSystem.SteeringSystemID
STRAIGHT_JOIN Transmission ON VehicleToTransmission.TransmissionID=Transmission.TransmissionID
STRAIGHT_JOIN TransmissionBase ON Transmission.TransmissionBaseID=TransmissionBase.TransmissionBaseID
STRAIGHT_JOIN TransmissionType ON TransmissionBase.TransmissionTypeID=TransmissionType.TransmissionTypeID
STRAIGHT_JOIN TransmissionNumSpeeds ON TransmissionBase.TransmissionNumSpeedsID=TransmissionNumSpeeds.TransmissionNumSpeedsID
STRAIGHT_JOIN TransmissionControlType ON TransmissionBase.TransmissionControlTypeID=TransmissionControlType.TransmissionControlTypeID
STRAIGHT_JOIN TransmissionMfrCode ON Transmission.TransmissionMfrCodeID=TransmissionMfrCode.TransmissionMfrCodeID
STRAIGHT_JOIN ElecControlled TransElecControlled ON Transmission.TransmissionElecControlledID=TransElecControlled.ElecControlledID
STRAIGHT_JOIN Mfr TransmissionMfr ON Transmission.TransmissionMfrID=TransmissionMfr.MfrID
STRAIGHT_JOIN WheelBase ON VehicleToWheelbase.WheelbaseID=WheelBase.WheelBaseID
STRAIGHT_JOIN VehicleType ON Model.VehicleTypeID=VehicleType.VehicleTypeID
STRAIGHT_JOIN VehicleTypeGroup ON VehicleType.VehicleTypeGroupId=VehicleTypeGroup.VehicleTypeGroupId

我能做些什么来优化查询以使其完成(而不会消耗超过 100GB 的数据)？

谢谢!

Answer 1

我会考虑使用表格来保存您需要的结果并通过多个步骤填充。

例如，使用简化的导入表以及品牌和型号的查找表:

create table imp( sku int, notes varchar(20), make_id int, model_id int ) ;
create table make ( id int, make_name varchar(20) ) ;
create table model ( id int, model_name varchar(20) ) ;

为我们的结果创建一个表:

create table res( sku int, 
                  notes varchar(20), 
                  make varchar(20), 
                  model varchar(20) 
                );

插入一些示例数据:

insert into imp values
( 1, 'blue ford focus', 2, 3 ), 
( 2,'green audi a4', 1, 2 ), 
( 3,'black audi a3', 1, 1 );

insert into make values( 1, 'audi'), (2,'ford');

insert into model values( 1, 'a3'), (2,'a4'), (3,'focus');

现在获取我们感兴趣的所有导入数据

insert into res ( sku, notes ) select sku,notes from imp; 

修复问题

update res 
join imp on res.sku = imp.sku
join make on imp.make_id = make.id
set res.make = make.make_name ;

和模型

update res 
join imp on res.sku = imp.sku
join model on imp.model_id = model.id
set res.model = model.model_name;

现在得到我们想要的结果...

select * from res;

http://sqlfiddle.com/#!2/36fdc2/1

使用这样的技术，您可以将查询分解为多个查询，每个查询都会运行。

您可能希望为结果表(上例中的 sku)中的任何相关字段添加索引