不带where子句的查询-性能调优- mysql

Question

我有这样的疑问

执行需要40分钟。我不能添加任何where子句，因为我需要所有的数据

表的配置如下所示，下面是解释计划的快照。

我知道这是一个不合理的查询，因为它访问每个数据。但是，有什么办法我可以改善时间。

select 
    pl.CompanyId,
    pl.FarmerCropId,
    ai.ActivityId,
    ai.ActivityName,
    ai.ActivityPlanId,
    ai.ActivityPlanName,
    ai.ActivityScheduleId,
    ai.DAS,
    (case when fca.CompletionDate = '1900-01-01' then null else fca.CompletionDate end)CompletionDate,
    fca.IsClosed,
    fca.LastModifiedDate,
    ai.AttributeDataTypeId,
    ai.AttributeId,
    ai.AttributeName,
    fcaa.FarmerCropActivityAttributeId,
    fcaa.Reading,
    ai.DataTypeId,
    ai.DataTypeName,
    ai.SequenaceNo,
    fca.isactive,
    pl.partitionflag,
    fca.ExpectedStartDate,
    fca.ExpectedClosureDate,
    ai.ActivityMasterIsActive,
    ai.ActivityPlanIsActive,
    ai.ActivityScheduleIsActive,
    ai.AttributeDataTypeIsActive,
    ai.AttributeMasterIsActive,
    case when isclosed = '1' then 'Closed On Time' 
                when isclosed = '2' then 'In Progress' 
                when isclosed = '3' then 'Pending' 
                when isclosed ='5' then 'Late Closed'
                when isclosed ='4' then 'Not Started'  
                end as ClosureStatus  ,
                fca.FarmerCropActivityID
from
activityinformation ai
join FarmerCropActivity fca 
    on ai.activityscheduleid = fca.activityscheduleid 
    and fca.isactive = 1
left join FarmerCropActivityAttributes fcaa 
    on fcaa.farmercropactivityid = fca.farmercropactivityid
    and fcaa.AttributeDataTypeID = ai.AttributeDataTypeID
join plot pl on
pl.farmercropid = fca.farmercropid 
where pl.partitionflag = 1;

activityinformation

1.行总数- 137653

2.指数-

idx_activityinformation_combinedids(ActivityMasterIsActive, ActivityPlanIsActive , ActivityScheduleIsActive,AttributeDataTypeIsActive
 ,AttributeMasterIsActive)

3.栏-

source
            ActivityId
            ActivityName
            ActivityPlanId
            ActivityPlanName
            CropTypeId
            ActivityScheduleId
            ActivityintervalDays
            AttributeDataTypeId
            SequenaceNo
            AttributeId
            AttributeName
            DAS
            DataTypeID
            DataTypeName
            talendate
            companyid
            ActivityMasterIsActive
            ActivityPlanIsActive
            ActivityScheduleIsActive
            AttributeDataTypeIsActive
            AttributeMasterIsActive
            Min
            Max

farmercropactivity

1.行总数- 2721502

索引-

idx_fca_activityscheduleid(activityscheduleid), 

idx_fca_activityid(ActivityID),

idx_fca_farmercropid(FarmerCropId),

idx_fca_composite(ActivityScheduleID , IsActive , ActivityID , FarmerCropId , FarmerCropActivityID),

idx_fca_composite1(ActivityScheduleID , IsActive , FarmerCropId , FarmerCropActivityID , ActivityID , CompletionDate , IsClosed ,
 LastModifiedDate , ExpectedStartDate , ExpectedClosureDate)

3.栏-

FarmerCropActivityID
            FarmerCropId
            ActivityScheduleID
            CompletionDate
            ExpectedStartDate
            ExpectedClosureDate
            Suggestions
            Comments
            IsClosed
            IsActive
            LastModifiedDate
            LastModifiedBy
            ActivityID
            ActivityReading
            IsMandatory
            ActivityCategoryID
            ClientId

farmercropactivityattributes

1.行总数- 4993953

1. 索引- idx_fcaa_farmercropactivityid(FarmerCropActivityID)，idx_fcaa_AttributeDataTypeID(AttributeDataTypeID)
2. 栏- FarmerCropActivityAttributeID FarmerCropActivityID AttributeDataTypeID读ClientId

图

1.行总数--其中分区标志=1- 328838

2.指数-

idx_composite2(companyid , partitionflag , FarmerCropId)

3.栏-

 source
            companyid
            Company
            FarmerId
            FarmerCode
            FarmerName
            LandId
            PlotName
            Latitude
            Longitude
            FarmerCropId
            Village
            DistrictId
            CropId
            crop
            VarietyId
            Variety
            SowingDate
            SowingWeek
            SowingWeekRange
            SowingMonth
            SowingMonthName
            SowingYear
            DeclaredArea
            AuditedArea
            SowingArea
            IsAudited
            IsGeoTagged
            ExpectedHarvestPerAcre
            FirstExpectedHarvestDate
            FirstActualHarvestDate
            HarvestArrivalStatus
            PlannedQuantity
            ExpectedQuantity
            CurrentQuantity
            HarvestQuantity
            RegistrationDate
            RegistrationWeek
            RegistrationWeekRange
            RegistrationMonth
            RegistrationMonthName
            RegistrationYear
            ActivityPlanId
            ActivityPlanName
            HarvestPlanId
            HarvestPlanName
            LastModifiedDate
            Address
            Territory
            Taluka
            ReestimateDate
            NewHarvestDate
            NewHarvestQuantity
            ReestimateReasonCode
            ReestimateReason
            Comments
            FarmerCropHarvestreestimateId
            IsReestimated
            ReestimateDaysShift
            CurrentQuantityWithoutReestimate
            HarvestUnitId
            harvestunit
            ExtendedHarvestUnitId
            ExtendedHarvestUnit
            ConversionFactor
            MobileNumber
            Coordinates
            PreferredSKUTypeId
            PreferredSKUName
            Capacity
            Soiltypeid
            soildesc
            Irrigationtypeid
            irrigationtypedesc
            lastmodifiedon
            partitionflag
            areaunitid
            areaunitname
            archivedate
            talendate
            geoid
            idtoroot
            nametoroot
            RevisedFirstExpectedHarvestDate
            HarvestStatusId
            HarvestStatus

​

​

谢谢拉蒂

Answer 1

需要：

fcaa: INDEX(FarmerCropActivityID, AttributeDataTypeID, Reading)

(Reading必须是最后一个；其他两个的排序是可选的。)这比你拥有的两个单列索引要好，而且它是“覆盖”的。

plot: INDEX(partitionflag , FarmerCropId, companyid)

companyid必须是最后一个，而不是第一个。再一次，这比你所拥有的要好，而且它是“掩护”。添加此索引。并删除现有的查询--但前提是其他查询不需要它。