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

本文关键字：查询 CF MySql EF 使用 | 更新日期: 2023-09-27 18:34:15

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

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

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

我正在使用 Amazon 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
}

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

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

有关创建多列索引

的详细信息，请查看 MSDN 上的多列索引部分，并在创建多列索引时特别注意列顺序，使其与查询匹配。您还可以考虑将 SQL 的纬度部分分组在一起。