c# - 使用 MySql、EF 和 CF 的慢速查询

Question

我有一个查询，大约需要 2 分钟。您能给我一些关于如何改进它的建议吗？正如您在 Ads 类中看到的数据库设计不太好...是否可以在不更改列类型的情况下优化查询？

我可以轻松地将 AdStatus 列更改为整数...但要更改 OperationType 和 AdType 则需要进行大量迁移。顺便说一句，我尝试仅使用纬度和经度值进行查询，但速度也很慢。

Ads 表有 230 万个项目，Pictureads 表有 1100 万个项目

我正在使用亚马逊 RDS t2.micro

此外，如果您发现模型中需要更改某些内容，请告诉我

var queryResults = (from d in ApplicationContext.Ads.AsNoTracking()
    where (d.Latitude >= latitude - radio)
    && (d.AdStatus != "Pending")
    && (d.Latitude <= latitude + radio)
    && (d.Longitude >= longitude - radio)
    && (d.Longitude <= longitude + radio)
    && (String.IsNullOrEmpty(operationType) || d.OperationType == operationType)
    && (!(priceMax > priceMin && (priceMin > 0 || priceMax > 0)) || (d.USDPrice >= priceMin && d.USDPrice <= priceMax))
    && (String.IsNullOrEmpty(adType) || d.AdType == adType)
    orderby d.USDPrice ascending
    select new
    {
        d.AdsID,
        d.Username,
        d.administrative_area_level_1,
        d.administrative_area_level_2,
        d.administrative_area_level_3,
        d.neighborhood,
        d.Address,
        d.PictureUrl,
        d.Latitude,
        d.Longitude,
        d.ExpirationDate,
        d.FeaturedAd,
        d.PremiumAd,
        d.Views,
        d.Code,
        d.OperationType,
        d.Price,
        d.USDPrice,
        d.PriceCurrency,
        d.AdType,
        d.Rooms,
        d.Restrooms,
        d.Description,
        d.AdStatus
    });

当调用 queryResults.Count() 或 queryResults.ToArray() 时，大约需要 2 分钟。

型号:

public class Ads
{
    [Key]
    [Index]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int AdsID { get; set; }
    [Index]
    [MaxLength(128)]
    public string Username { get; set; }
    [Index]
    [MaxLength(64)]
    public string country { get; set; }
    [Index]
    [MaxLength(64)]
    public string administrative_area_level_1 { get; set; }
    [Index]
    [MaxLength(64)]
    public string administrative_area_level_2 { get; set; }
    [MaxLength(64)]
    public string administrative_area_level_3 { get; set; }
    [MaxLength(64)]
    public string neighborhood { get; set; }
    [MaxLength(64)]
    public string Address { get; set; }
    // Navigation property
    public virtual ICollection<PictureAds> Pictures { get; set; }
    [MaxLength(256)]
    public string PictureUrl { get; set; }
    [MaxLength(256)]
    public string UserPicUrl { get; set; }
    [Index]
    [DisplayFormat(DataFormatString = "{0:n15}", ApplyFormatInEditMode = true)]
    public decimal Latitude { get; set; }
    [Index]
    [DisplayFormat(DataFormatString = "{0:n15}", ApplyFormatInEditMode = true)]
    public decimal Longitude { get; set; }
    [DataType(DataType.Date)]
    public DateTime CreatedDate { get; set; }
    [DataType(DataType.Date)]
    public DateTime ExpirationDate { get; set; }
    public int MonthsAlive { get; set; }
    public decimal PriceSurfaceRatio { get; set; }
    public bool FeaturedAd { get; set; }
    public bool PremiumAd { get; set; }
    public int Views { get; set; }
    [MaxLength(256)]
    public string Code { get; set; }
    [MaxLength(256)]
    public string Title { get; set; }
    [MaxLength(32)]
    public string SunOrientation { get; set; }
    public bool IsFurnished { get; set; }
    [Required]
    [MaxLength(64)]
    public string Name { get; set; }
    [Required]
    [MaxLength(64)]
    public string Email { get; set; }
    [Required]
    [MaxLength(32)]
    public string Phone { get; set; }
    [Required]
    [MaxLength(32)]
    public string UserType { get; set; }
    [Required]
    [Index]
    [MaxLength(32)]
    public string OperationType { get; set; }
    [Required]
    public int Price { get; set; }
    [Index]
    public int? USDPrice { get; set; }
    [Required]
    [MaxLength(16)]
    public string PriceCurrency { get; set; }
    [Required]
    [Index]
    [MaxLength(32)]
    public string AdType { get; set; }
    [Required]
    public int SizeTotal { get; set; }
    public int SizeIndoor { get; set; }
    public int SizeOutdoor { get; set; }
    public int Expenses { get; set; }
    [Index]
    public int Rooms { get; set; }
    [Index]
    public int Restrooms { get; set; }
    [MaxLength(16)]
    public string Age { get; set; }
    public int Garage { get; set; }
    public string Description { get; set; }
    [Index]
    [MaxLength(32)]
    public string AdStatus { get; set; } //Pending/Approved/Denied/OnHold
}

public class PictureAds
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int PictureAdID { get; set; }
    public string url { get; set; }

    // Foreign key
    public int AdsID { get; set; }

    // Navigation properties
    public virtual Ads AdsModels { get; set; }
}

================================================== =============

这是使用 Clay Ver Valen 建议更新的代码:

var queryResults = (from d in ApplicationContext.Ads.AsNoTracking()
                                    where (d.Latitude >= latitude - radio)
                                    && (d.Latitude <= latitude + radio)
                                    && (d.Longitude >= longitude - radio)
                                    && (d.Longitude <= longitude + radio)
                                    && (String.IsNullOrEmpty(operationType) || d.OperationType == operationType)
                                    && (!(priceMax > priceMin && (priceMin > 0 || priceMax > 0)) || (d.USDPrice >= priceMin && d.USDPrice <= priceMax))
                                    && (String.IsNullOrEmpty(adType) || d.AdType == adType)
                                    orderby d.USDPrice ascending
                                    select new
                                    {
                                        d.AdsID,
                                        d.Username,
                                        d.administrative_area_level_1,
                                        d.administrative_area_level_2,
                                        d.administrative_area_level_3,
                                        d.neighborhood,
                                        d.Address,
                                        d.PictureUrl,
                                        d.Latitude,
                                        d.Longitude,
                                        d.ExpirationDate,
                                        d.FeaturedAd,
                                        d.PremiumAd,
                                        d.Views,
                                        d.Code,
                                        d.OperationType,
                                        d.Price,
                                        d.USDPrice,
                                        d.PriceCurrency,
                                        d.AdType,
                                        d.Rooms,
                                        d.Restrooms,
                                        d.Description,
                                        d.AdStatus
                                    });

多列索引:

public class Ads
{
    [Key]
    [Index]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int AdsID { get; set; }
    [Index]
    [MaxLength(128)]
    public string Username { get; set; }
    [Index]
    [MaxLength(64)]
    public string country { get; set; }
    [Index]
    [MaxLength(64)]
    public string administrative_area_level_1 { get; set; }
    [Index]
    [MaxLength(64)]
    public string administrative_area_level_2 { get; set; }
    [MaxLength(64)]
    public string administrative_area_level_3 { get; set; }
    [MaxLength(64)]
    public string neighborhood { get; set; }
    [MaxLength(64)]
    public string Address { get; set; }
    // Navigation property
    public virtual ICollection<PictureAds> Pictures { get; set; }
    [MaxLength(256)]
    public string PictureUrl { get; set; }
    [MaxLength(256)]
    public string UserPicUrl { get; set; }
    [Index("IX_FilterAds", 1)] 
    [DisplayFormat(DataFormatString = "{0:n15}", ApplyFormatInEditMode = true)]
    public decimal Latitude { get; set; }
    [Index("IX_FilterAds", 2)] 
    [DisplayFormat(DataFormatString = "{0:n15}", ApplyFormatInEditMode = true)]
    public decimal Longitude { get; set; }
    [DataType(DataType.Date)]
    public DateTime CreatedDate { get; set; }
    [DataType(DataType.Date)]
    public DateTime ExpirationDate { get; set; }
    public int MonthsAlive { get; set; }
    public decimal PriceSurfaceRatio { get; set; }
    public bool FeaturedAd { get; set; }
    public bool PremiumAd { get; set; }
    public int Views { get; set; }
    [MaxLength(256)]
    public string Code { get; set; }
    [MaxLength(256)]
    public string Title { get; set; }
    [MaxLength(32)]
    public string SunOrientation { get; set; }
    public bool IsFurnished { get; set; }


    [Required]
    [MaxLength(64)]
    public string Name { get; set; }
    [Required]
    [MaxLength(64)]
    public string Email { get; set; }
    [Required]
    [MaxLength(32)]
    public string Phone { get; set; }
    [Required]
    [MaxLength(32)]
    public string UserType { get; set; }
    [Required]
    [Index("IX_FilterAds", 3)] 
    [MaxLength(32)]
    public string OperationType { get; set; }
    [Required]
    public int Price { get; set; }
    [Index]
    [Index("IX_FilterAds", 4)] 
    public int? USDPrice { get; set; }
    [Required]
    [MaxLength(16)]
    public string PriceCurrency { get; set; }
    [Required]
    [Index("IX_FilterAds", 5)] 
    [MaxLength(32)]
    public string AdType { get; set; }
    [Required]
    public int SizeTotal { get; set; }
    public int SizeIndoor { get; set; }
    public int SizeOutdoor { get; set; }
    public int Expenses { get; set; }
    [Index]
    public int Rooms { get; set; }
    [Index]
    public int Restrooms { get; set; }
    [MaxLength(16)]
    public string Age { get; set; }
    public int Garage { get; set; }
    public string Description { get; set; }
    [Index]
    [MaxLength(32)]
    public string AdStatus { get; set; } //Pending/Approved/Denied/OnHold
}

Answer 1

您已经使用默认选项创建了一堆单列索引，您需要的是一个针对您的查询进行调整的覆盖索引。

查看 Multiple-Column Indexes有关创建多列索引的详细信息，请参阅 MSDN 部分，并且在创建多列索引时要特别注意列顺序，以便它与您的查询相匹配。您还可以考虑将 SQL 的纬度部分分组在一起。