远程MySQL DB雄辩地花了太长时间 [英] Eloquent taking too long with remote MySQL DB

问题描述

我正在开发一个Web(Laravel)和iOS应用程序，它们都使用我也在与Laravel一起开发的REST API中使用.从一个AWS RDS实例托管的MySQL数据库查询API.

I'm developing a web (Laravel) and iOS app that both consumes from a REST API that I'm also developing with Laravel. The API queries from a MySQL Database hosted at an AWS RDS instance.

当我同时在本地主机上设置了API和应用程序，并且将API配置为连接我的本地数据库(与应用程序和API相同的机器)时，它按预期工作正常，但是问题是当我设置API时从AWS RDS数据库查询，而不是从本地查询. 用口才做的简单查询，例如

When I set up both, the API and the app on localhost, and the API configured to connect my local Database (same machine as the apps and API) it works ok as expected, but the problem is when I setup the API to query from the AWS RDS Database instead of locally. Simple queries made with eloquent, like

Product::where('brand_id', '=', $id)
               ->get()

大约需要10到15分钟才能完成，但是如果我使用查询生成器进行相同的查询，例如；

takes like 10 - 15 mins to complete, but if I do the same query by using the query builder, for expample;

DB::select('select * from products where brand_id = ?', [$id]);

工作正常.

直接在MySQL上执行查询或在Laravel Tinker上运行Product :: all()可以与本地API/远程数据库设置一起使用.

Executing the query directly on MySQL or running Product::all() at the Laravel Tinker works as intended with local API / Remote DB setup.

这是我执行SHOW CREATE TABLE products

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `SKU` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `price` double(8,2) NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_details` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `material_and_care` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `material` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `care` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `colors` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `made_in` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `size_chart` text COLLATE utf8mb4_unicode_ci,
  `size_chart_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `brand_id` int(10) unsigned NOT NULL,
  `category_id` int(10) unsigned NOT NULL,
  `published` tinyint(1) NOT NULL DEFAULT '0',
  `featured` tinyint(1) NOT NULL DEFAULT '0',
  `promo_title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `promo_caption` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `shipping_height` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `shipping_width` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `shipping_length` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `shipping_weight` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_vertical` tinyint(1) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `products_category_id_foreign` (`category_id`),
  KEY `products_brand_id_foreign` (`brand_id`),
  CONSTRAINT `products_brand_id_foreign` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `products_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `subcategories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=501 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

我的产品型号:

class Product extends Model
{
    use SoftDeletes, CascadeSoftDeletes;

    protected $cascadeDeletes = [
        'items',
        'options',
        'images',
        'sizes'
    ];

    protected $fillable = [
        'sku',
        'name',
        'price',
        'description',
        'product_details',
        'material_and_care',
        'material',
        'color_1',
        'color_2',
        'color_3',
        'care',
        'made_in',
        'size_chart',
        'size_chart_url',
        'brand_id',
        'category_id',
        'published',
        'featured',
        'promo_title',
        'promo_caption',
        'shipping_weight',
        'shipping_height',
        'shipping_width',
        'shipping_length',
        'is_vertical'
    ];

    protected $appends = [
        'interests',
        'brand',
        'options',
        'sizes',
        'items',
        'images',
        'comment_count',
        'discount',
        'suits_me_count'
    ];

    protected $hidden = [
        'created_at',
        'deleted_at',
        'updated_at',
        'subcategory'
    ];

    protected static function boot()
    {
        parent::boot();
        static::addGlobalScope(new PublishedProductScope);
    }

    public function getDiscountAttribute() {
        return $this->discount()->first();
    }

    public function getSuitsMeCountAttribute() {
        return $this->suitsmes()->count();
    }

    public function getCommentCountAttribute() {
        return $this->comments()->count();
    }


    public function getImagesAttribute(){
        return $this->images()->pluck("url");
    }

    public function getInterestsAttribute() {
        return $this->interests()->get();
    }

    public function getBrandAttribute(){
        return $this->brand()->first();
    }

    public function getOptionsAttribute(){
        return $this->options()->get();
    }

    public function getSizesAttribute(){
        return $this->sizes()->get();
    }

    public function getItemsAttribute(){
        return $this->items()->get();
    }

    public function interests() {
        return $this->belongsToMany('App\Interest', 'product_interest');
    }

    public function brand() {
        return $this->belongsTo('App\Brand');
    }

    public function options() {
        return $this->hasMany('App\ProductOption');
    }

    public function sizes() {
        return $this->hasMany('App\ProductSize');
    }

    public function items() {
        return $this->hasMany('App\ProductItem');
    }

    public function images() {
        return $this->hasMany('App\ProductImage');
    }

    public function comments() {
        return $this->hasMany('App\ProductComment');
    }

    public function suitsmes() {
        return $this->belongsToMany('App\User', 'wishlist', 'product_id', 'user_id');
    }

    public function discount(){
        return $this->hasOne('App\Discount');
    }

    public function category() {
        return $this->belongsTo('App\Subcategory', 'category_id');
    }

}

我的品牌模型:

class Brand extends Model
{


 protected $fillable = [
        'first_name',
        'last_name',
        'email',
        'phone_number',
        'birth_date',
        'ssn',
        'street_address',
        'street_address_2',
        'city_address',
        'state_address',
        'postal_code_address',
        'legal_name',
        'dba',
        'tax_id',
        'street_business_address',
        'street_business_address_2',
        'city_business_address',
        'state_business_address',
        'postal_code_business_address',
        'destination_fund',
        'email_business',
        'phone_number_business',
        'account_number_fund',
        'routing_number_fund'
    ];

    protected $hidden = [
        'created_at',
        'deleted_at',
        'updated_at'
    ];

    protected $appends = [
        'images'
    ];

    public function getImagesAttribute()
    {
        return $this->images()->get();
    }

    public function getBillboardPicturesAttribute() {
        $pictures = [$this->billboard, $this->billboard2, $this->billboard3, $this->billboard4];
        return $pictures;
    }    

    public function users()
    {
        return $this->belongsToMany('App\User', 'user_brand_role', 'brand_id', 'user_id');
    }

    public function getInterestsAttribute() {
        return $this->interests()->pluck('interest_id');
    }

    public function interests() {
        return $this->belongsToMany('App\Interest', 'brand_interest', 'brand_id', 'interest_id');
    }

    public function products() {
        return $this->hasMany('App\Product');
    }

    public function images() {
        return $this->hasMany('App\BrandImage');
    }

    public function categories() {
        return $this->hasMany('App\Category');
    }

    public function getCatalogAttribute() {
        return $this->categories()->orderBy('name', 'asc')->get();
    }


}

推荐答案

问题是您的Product模型中的这段代码:

The issue is this piece of code in your Product model:

protected $appends = [
    'interests',
    'brand',
    'options',
    'sizes',
    'items',
    'images',
    'comment_count',
    'discount',
    'suits_me_count'
];

这些属性是关系，因此它们导致多个 每次Eloquent读/返回一行时都会加入.

These properties are relationships, and so they result in multiple joins every time Eloquent reads/returns a row.

此查询:DB::select('select * from products where brand_id = ?', [$id]);不导致任何联接，并且是来自单个表的选择查询.这就是为什么在比较结果时会发现查询时间有显着差异的原因.

This query: DB::select('select * from products where brand_id = ?', [$id]); doesn't result in any joins and is a select query from a single table. That's why when you compare results, you find a dramatic difference in query time.

有2种可能的解决方案:

There are 2 possible solutions:

1.删除附件

删除附件，每当需要关系时，使用with函数

Remove the appends and whenever you need a relationship use the with function

2.隐藏附加属性

您可以将此代码添加到您的Product模型中:

You could add this code to your Product Model:

protected $hidden = [
    'created_at',
    'deleted_at',
    'updated_at',
    'interests',
    'brand',
    'options',
    'sizes',
    'items',
    'images',
    'comment_count',
    'discount',
    'suits_me_count'
];

当您需要属性时，只需使用$product->makeVisible('property_name').否则，默认情况下，将不会加载或查询这些属性

When you need a property just use $product->makeVisible('property_name'). Otherwise, by default, these properties won't be loaded or queried

这篇关于远程MySQL DB雄辩地花了太长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！