如何从我的Ruby on Rails应用程序中解决此方法中的高响应时间

我的 Rails API 中有这种方法，它不断向我发送高响应时间警报。我已经尝试根据我目前的知识尽可能多地优化它，但它显然仍然没有完成这项工作。

有关如何优化这些查询的任何帮助将不胜感激：

这是我获取标记并将其发送到我的 API 的方法

首先我获取地址

  longitude = params[:longitude]
  latitude = params[:latitude]
  @addresses = Address.joins('INNER JOIN users ON users.id = addresses.addressable_id')
                      .joins('INNER JOIN items ON items.user_id = users.id')
                      .where('items.name IS NOT NULL').where("items.name <> ''")
                      .where('items.visibility = TRUE')
                      .where('items.photo IS NOT NULL').where("items.photo <> ''")
                      .where('addresses.latitude IS NOT NULL AND addresses.addressable_type = ? ', "User")
                      .near([latitude, longitude], (params[:distance].to_i + 1000))

其次，我使用这些地址将 JSON 对象渲染回我的 API

我有一个检查项方法

def checkitem(item)
  begin
    requests = Request.where('item_id = ? AND created_at < ? AND created_at > ?', item.id, (DateTime.now - 1.day), (DateTime.now - 6.months)).pluck(:status)
    if (requests.exists? && requests.count > 2)
      if requests.count('pending') >= 3 && (item.user.current_sign_in_at.present? && item.user.current_sign_in_at < (DateTime.now - 2.weeks))
        false
      else
        true
      end
    elsif (requests == [] || requests.count <= 2)
      true
    elsif (item.user.current_sign_in_at.present? && item.user.current_sign_in_at > (DateTime.now - 2.weeks)) || item.user.created_at > (DateTime.now - 2.weeks)
      true
    else
      false
    end
  rescue
    true
  end
end

然后我渲染我的 JSON

@places = Address.where(addressable_type: 'Item').where.not(type_add: nil).near([latitude, longitude], 10)
render json: {markers: @addresses.uniq.map { |address|
                      [{
                        name: address.user.items.first.name,
                        photo: { uri: address.user.items.first.photo.url },
                        id: Item.where(user_id: address.addressable_id).first.id,
                        latitude: address.latitude,
                        longitude: address.longitude,
                        breed: address.user.items.first.breed.id,
                        innactive: checkitem(address.user.items.first) ? false : true,
                        power: (address.user.items.first.requests.count >= 2 && address.user.items.first.requests.last(3).map(&:status).count('pending') < 1) ? true : false,
                      }]
                }.reject { |e| e.nil? }.flatten.first(100)
              }
end

@address.解释

    => EXPLAIN for: SELECT addresses.*, 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((45.501689 - addresses.latitude) * PI() / 180 / 2), 2) + COS(45.501689 * PI() / 180) * COS(addresses.latitude * PI() / 180) * POWER(SIN((-73.567256 - addresses.longitude) * PI() / 180 / 2), 2))) AS distance, MOD(CAST((ATAN2( ((addresses.longitude - -73.567256) / 57.2957795), ((addresses.latitude - 45.501689) / 57.2957795)) * 57.2957795) + 360 AS decimal), 360) AS bearing FROM "addresses" INNER JOIN users ON users.id = addresses.addressable_id INNER JOIN items ON items.user_id = users.id WHERE (items.name IS NOT NULL) AND (items.name <> '') AND (items.visibility = TRUE) AND (items.photo IS NOT NULL) AND (items.photo <> '') AND (addresses.latitude IS NOT NULL AND addresses.addressable_type = 'User' ) AND (addresses.latitude BETWEEN 31.028510688915205 AND 59.97486731108479 AND addresses.longitude BETWEEN -94.21702228070411 AND -52.91748971929589 AND (3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((45.501689 - addresses.latitude) * PI() / 180 / 2), 2) + COS(45.501689 * PI() / 180) * COS(addresses.latitude * PI() / 180) * POWER(SIN((-73.567256 - addresses.longitude) * PI() / 180 / 2), 2)))) BETWEEN 0.0 AND 1000) ORDER BY distance ASC
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=224.28..224.28 rows=1 width=138)
   Sort Key: (('7917.511728464'::double precision * asin(sqrt((power(sin((((('45.501689'::double precision - addresses.latitude) * '3.14159265358979'::double precision) / '180'::double precision) / '2'::double precision)), '2'::double precision) + (('0.70088823836273'::double precision * cos(((addresses.latitude * '3.14159265358979'::double precision) / '180'::double precision))) * power(sin((((('-73.567256'::double precision - addresses.longitude) * '3.14159265358979'::double precision) / '180'::double precision) / '2'::double precision)), '2'::double precision)))))))
   ->  Nested Loop  (cost=0.11..224.28 rows=1 width=138)
         ->  Nested Loop  (cost=0.06..207.10 rows=39 width=8)
               ->  Seq Scan on items  (cost=0.00..126.62 rows=39 width=4)
                     Filter: ((name IS NOT NULL) AND visibility AND (photo IS NOT NULL) AND ((name)::text <> ''::text) AND ((photo)::text <> ''::text))
               ->  Index Only Scan using users_pkey on users  (cost=0.06..2.06 rows=1 width=4)
                     Index Cond: (id = items.user_id)
         ->  Index Scan using index_addresses_on_addressable_type_and_addressable_id on addresses  (cost=0.06..0.44 rows=1 width=98)
               Index Cond: (((addressable_type)::text = 'User'::text) AND (addressable_id = users.id))
               Filter: ((latitude IS NOT NULL) AND (latitude >= '31.0285106889152'::double precision) AND (latitude <= '59.9748673110848'::double precision) AND (longitude >= '-94.2170222807041'::double precision) AND (longitude <= '-52.9174897192959'::double precision) AND (('7917.511728464'::double precision * asin(sqrt((power(sin((((('45.501689'::double precision - latitude) * '3.14159265358979'::double precision) / '180'::double precision) / '2'::double precision)), '2'::double precision) + (('0.70088823836273'::double precision * cos(((latitude * '3.14159265358979'::double precision) / '180'::double precision))) * power(sin((((('-73.567256'::double precision - longitude) * '3.14159265358979'::double precision) / '180'::double precision) / '2'::double precision)), '2'::double precision)))))) >= '0'::double precision) AND (('7917.511728464'::double precision * asin(sqrt((power(sin((((('45.501689'::double precision - latitude) * '3.14159265358979'::double precision) / '180'::double precision) / '2'::double precision)), '2'::double precision) + (('0.70088823836273'::double precision * cos(((latitude * '3.14159265358979'::double precision) / '180'::double precision))) * power(sin((((('-73.567256'::double precision - longitude) * '3.14159265358979'::double precision) / '180'::double precision) / '2'::double precision)), '2'::double precision)))))) <= '1000'::double precision))
(11 rows)

你的问题并不那么简单，我的答案是建立在我看到的假设和代码之上的。我相信，通过您的反馈和合作，我们将:)

我认为第一个主要问题是您有单独的查询来requests每个item_id表，这绝对是一个瓶颈。

步骤1：您可以按如下方式改进获取地址代码：

@addresses = Address.joins("INNER JOIN users ON users.id = addresses.addressable_id AND addresses.addressable_type = 'User' INNER JOIN items ON items.user_id = users.id")
                    .where.not({ 
                                 items: {
                                   name: [nil, ''],
                                   photo: [nil, ''],
                                   visibility: false
                                 },
                                 addresses: { latitude: nil }
                               })
                    .near([latitude, longitude], (params[:distance].to_i + 1000))
                    .select('addresses.*, items.id AS item_id')

步骤2：删除@places =查询。至少我没有看到你使用它的任何地方

步骤3：防止(N + 1(查询，包括：

@requests = Request.where(item_id: @addresses.map(&:item_id).uniq).where('created_at < ? AND created_at > ?', (DateTime.now - 1.day), (DateTime.now - 6.months)).to_a
render json: {markers: @addresses.uniq.map { |address|
                  [{
                    name: address.user.items.first.name,
                    photo: { uri: address.user.items.first.photo.url },
                    id: Item.where(user_id: address.addressable_id).first.id,
                    latitude: address.latitude,
                    longitude: address.longitude,
                    breed: address.user.items.first.breed.id,
                    innactive: checkitem(@address.user.items.first, @requests) ? false : true,
                    power: (address.user.items.first.requests.count >= 2 && address.user.items.first.requests.last(3).map(&:status).count('pending') < 1) ? true : false,
                  }]
            }.reject { |e| e.nil? }.flatten.first(100)
          }
end

步骤4：从checkitem中删除查询：

def checkitem(item, requests)
  begin
    statuses = requests.select { |r| r.item_id = item.id }.map(&:status)
    if (requests.exists? && requests.count > 2)
      if requests.count('pending') >= 3 && (item.user.current_sign_in_at.present? && item.user.current_sign_in_at < (DateTime.now - 2.weeks))
        false
      else
        true
      end
    elsif (requests == [] || requests.count <= 2)
      true
    elsif (item.user.current_sign_in_at.present? && item.user.current_sign_in_at > (DateTime.now - 2.weeks)) || item.user.created_at > (DateTime.now - 2.weeks)
      true
    else
      false
    end
  rescue
    true
  end
end

这段代码仍然很臭，但让我们把它作为第一步，走得更远。对于其他更改，我将需要更多的代码片段/等，但我真的认为这应该消除主要瓶颈。

我对火车轨道上的水晶一无所知，但是如果您的问题是由SQL查询引起的，这些查询需要很长时间才能发布输出。你可以试试这些。

您加入带有地址的Users_Table，然后您获取items_Table并将其加入上一个操作。

在过滤这些之前;

items_name 不应为空
items_name不应该是">
items_photo不应该为空
items_photo不应该是">
项目可见性 = 真
addresses.latitude 不应该为 NULL
还有一些我假设不容易避免的事情。

我不确定您的设计，但可以避免上述一些情况。我会做什么创建一个视图。用户之间的静态条件(如 NOT NULL(已经被过滤，不需要每次都执行。

名为 (Showable_Items( 的视图，它都是项目，但

items_name 不应为空
items_name不应该是">
items_photo不应该为空
items_photo不应该是">
items.可见性应为 TRUE

名为 (Addressable_Addresses( 的视图，它们都是地址，但

addresses.latitude 不应该为 NULL

并将这两个视图与即用型内容连接起来。

也试试; 您最关键的过滤器参数是什么。坐标比较可能会过滤表格的 99.9%。所以这张表也应该分开。再次查看 .ALL_ADDRESSES_VIEW 但是哪个纬度在 10 到 15 之间等等。任何对您的设计有意义的东西。

相关内容

最新更新

热门标签：