N+1 Query Rails

I. Giới thiệu Phần lớn các application dùng Rails đều có dữ liệu được phân bổ qua các model và các mối quan hệ giữa chúng và sử dụng các ORM để truy vấn. Các ORM sẽ phát hiện và tìm kiếm trong database để lấy ra các mối quan hệ được khai báo trong model. Nhưng nếu không để ý thì chúng ta sẽ mắc 1 số vấn đề lớn đó là hiệu năng của rails application. Một trong những vấn đề đó chính là quá nhiều truy vấn (N+1 queries).

• Ta có mỗi quan hệ
• Khi sử dụng rails console để lấy tất cả các restaurants đã order của supplier có id là 80 => ORM đã sử dụng rất nhiều câu query để lấy ra được restaurants
• Để giải quyết vấn đề này rails đã đưa ra 1 số giải pháp để xử lý việc trên như Preload, Eagerload, Includes và Joins

1. Preload

• preload sử dụng bằng cách tải dữ liệu kết hợp trong một truy vấn riêng biệt.

[4] pry(main)> supplier = Supplier.preload(:restaurants).find(80)
  Supplier Load (0.7ms)  SELECT  `suppliers`.* FROM `suppliers` WHERE `suppliers`.`deleted_at` IS NULL AND `suppliers`.`id` = 80 LIMIT 1
  SupplierRestaurantMapping Load (0.7ms)  SELECT `supplier_restaurant_mappings`.* FROM `supplier_restaurant_mappings` WHERE `supplier_restaurant_mappings`.`deleted_at` IS NULL AND `supplier_restaurant_mappings`.`supplier_id` = 80
  Restaurant Load (0.9ms)  SELECT `restaurants`.* FROM `restaurants` WHERE `restaurants`.`deleted_at` IS NULL AND `restaurants`.`id` IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 
  15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 36, 37, 38, 39, 40, 41, 42, 
  43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 
  70, 71, 72, 73, 74, 75, 76, 81, 82, 84, 85, 102, 105, 108, 119, 181, 185, 186, 143, 220, 226)

=> Thay vì sử dụng rất nhiều câu query để lấy ra các restaurants, preload chỉ sử dụng 1 câu query để lấy ra tất cả các restaurants

• Preload luôn luôn tạo ra 2 câu query để lấy được dữ liệu ra và nó không thể sử dụng với các điều kiện từa table khác

[16] pry(main)> Supplier.preload(:orders).where(orders: {restaurant_id = 10}).first
  Supplier Load (0.7ms)  SELECT  `suppliers`.* FROM `suppliers` WHERE `suppliers`.`deleted_at` IS NULL AND (`orders`.`restaurant_id` = 10) ORDER BY `suppliers`.`id` ASC LIMIT 1
  ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'orders.restaurant_id' in 'where clause': SELECT  `suppliers`.* FROM `suppliers` WHERE `suppliers`.`deleted_at` IS NULL AND (`orders`.`restaurant_id` = 10) ORDER BY `suppliers`.`id` ASC LIMIT 1

• Với cùng table thì ta có thể lấy ra bình thường

[20] pry(main)> Supplier.preload(:orders).where("`suppliers`.`id` = 10").first
  Supplier Load (0.5ms)  SELECT  `suppliers`.* FROM `suppliers` WHERE `suppliers`.`deleted_at` IS NULL AND (`suppliers`.`id` = 10) ORDER BY `suppliers`.`id` ASC LIMIT 1
  Order Load (1.8ms)  SELECT `orders`.* FROM `orders` WHERE `orders`.`deleted_at` IS NULL AND `orders`.`supplier_id` = 10

1. Includes

• Includes bao gồm cả tải dữ liệu giống như preload
• Tuy nhiên nó thông minh hơn preload. Ở trên chúng ta thấy rằng preload không thành công đối với truy vấn Supplier.preload(:orders).where(orders: {restaurant_id = 10}) . Hãy cùng thử với includes.

[1] pry(main)> Supplier.includes(:orders).where(orders: {restaurant_id: 10})
 SQL (1.5ms)  SELECT `suppliers`.`id` AS t0_r0, `suppliers`.`name` AS t0_r1, `suppliers`.`tel` AS 
 t0_r2, `suppliers`.`address` AS t0_r3, `suppliers`.`charge_name` AS t0_r4, 
 `suppliers`.`order_way_id` AS t0_r5, `suppliers`.`memo` AS t0_r6, `suppliers`.`deleted_at` AS 
 t0_r7, `suppliers`.`created_at` AS t0_r8, `suppliers`.`updated_at` AS t0_r9, 
 `suppliers`.`office_name` AS t0_r10, `suppliers`.`fax` AS t0_r11, `suppliers`.`mail_address` AS 
 t0_r12, `suppliers`.`supplier_code` AS t0_r13, `suppliers`.`open_time` AS t0_r14, 
 `suppliers`.`end_time` AS t0_r15, `suppliers`.`password_digest` AS t0_r16, 
 `suppliers`.`password_reset_send_at` AS t0_r17, `suppliers`.`password_reset_token` AS t0_r18, 
 `suppliers`.`image_file` AS t0_r19, `suppliers`.`appeal_point` AS t0_r20, `suppliers`.`catch_copy` 
 AS t0_r21, `suppliers`.`charge_image_file` AS t0_r22, `suppliers`.`name_kana` AS t0_r23, 
 `suppliers`.`address_2` AS t0_r24, `suppliers`.`delivery_price` AS t0_r25, `suppliers`.`is_delivery` 
 AS t0_r26, `suppliers`.`state` AS t0_r27, `suppliers`.`city` AS t0_r28, `suppliers`.`zipcode1` AS 
 t0_r29, `suppliers`.`zipcode2` AS t0_r30, `suppliers`.`is_publish` AS t0_r31, `suppliers`.`rates` AS 
 t0_r32, `suppliers`.`first_login` AS t0_r33, `suppliers`.`category_id` AS t0_r34, 
 `suppliers`.`admin_memo` AS t0_r35, `suppliers`.`delivery_memo` AS t0_r36, 
 `suppliers`.`lock_version` AS t0_r37, `suppliers`.`is_demo` AS t0_r38, `suppliers`.`confirm_token`
 AS t0_r39, `suppliers`.`confirm_token_send_at` AS t0_r40, `suppliers`.`confirmed_at` AS t0_r41, 
 `suppliers`.`invoice_expires_at` AS t0_r42, `suppliers`.`aggregate_at` AS t0_r43, 
 `suppliers`.`invoice_target_at` AS t0_r44, `suppliers`.`invoice_expires_month` AS t0_r45, 
 `orders`.`id` AS t1_r0, `orders`.`user_id` AS t1_r1, `orders`.`deliver_way_id` AS t1_r2, 
 `orders`.`price` AS t1_r3, `orders`.`order_status` AS t1_r4, `orders`.`final_at` AS t1_r5, 
 `orders`.`created_at` AS t1_r6, `orders`.`updated_at` AS t1_r7, `orders`.`order_way_id` AS t1_r8, 
 `orders`.`supplier_id` AS t1_r9, `orders`.`staff_id` AS t1_r10, `orders`.`shop_id` AS t1_r11, 
 `orders`.`order_code` AS t1_r12, `orders`.`tax_price` AS t1_r13, `orders`.`postage` AS t1_r14, 
 `orders`.`deleted_at` AS t1_r15, `orders`.`commission` AS t1_r16, 
 `orders`.`is_check_commission` AS t1_r17, `orders`.`rates` AS t1_r18, `orders`.`type` AS t1_r19, 
 `orders`.`auth_token` AS t1_r20, `orders`.`auth_token_at` AS t1_r21, `orders`.`invoice_id` AS 
 t1_r22, `orders`.`is_draft` AS t1_r23, `orders`.`is_public` AS t1_r24, `orders`.`restaurant_id` AS 
 t1_r25, `orders`.`memo` AS t1_r26, `orders`.`shipment_address_id` AS t1_r27, 
 `orders`.`is_supplier_create` AS t1_r28, `orders`.`received_at` AS t1_r29, `orders`.`is_tax` AS 
 t1_r30, `orders`.`postage_tax_price` AS t1_r31, `orders`.`is_restaurant_created` AS t1_r32, 
 `orders`.`pre_supplier_id` AS t1_r33 FROM `suppliers` LEFT OUTER JOIN `orders` ON 
 `orders`.`supplier_id` = `suppliers`.`id` AND `orders`.`deleted_at` IS NULL WHERE
 `suppliers`.`deleted_at` IS NULL AND `orders`.`restaurant_id` = 10

=> Như bạn thấy includes chuyển mạch từ sử dụng hai truy vấn riêng biệt để tạo ra một LEFT OUTER JOIN duy nhất để lấy dữ liệu. Và nó cũng áp dụng điều kiện includes.

• Vì vậy, includes thay đổi từ hai truy vấn sang một truy vấn trong một số trường hợp. Theo mặc định cho một trường hợp đơn giản nó sẽ sử dụng hai truy vấn. Giả sử rằng vì một lý do nào đó bạn muốn buộc một trường hợp cần sử dụng một truy vấn thay vì hai. Sử dụng references để làm được điều đó.

[9] pry(main)> Supplier.includes(:orders)
 Supplier Load (0.7ms)  SELECT `suppliers`.* FROM `suppliers` WHERE `suppliers`.`deleted_at` IS NULL
 Order Load (3.0ms)  SELECT `orders`.* FROM `orders` WHERE `orders`.`deleted_at` IS NULL AND 
 `orders`.`supplier_id` IN (10, 11, 12, 13, 14, 15, 16, 18, 19, 20, 22, 23, 24, 26, 27, 28, 29, 30, 31, 32,
 34, 35, 36, 37, 38, 39, 40, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61,
 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 
 100, 101, 102, 103, 104)

=>

[10] pry(main)> Supplier.includes(:orders).references(:orders)
 SQL (90.7ms)  SELECT `suppliers`.`id` AS t0_r0, `suppliers`.`name` AS t0_r1, `suppliers`.`tel` AS t0_r2, `suppliers`.`address` AS t0_r3, `suppliers`.`charge_name` AS t0_r4, `suppliers`.`order_way_id` AS t0_r5, `suppliers`.`memo` AS t0_r6, `suppliers`.`deleted_at` AS t0_r7, `suppliers`.`created_at` AS t0_r8, `suppliers`.`updated_at` AS t0_r9, `suppliers`.`office_name` AS 
 t0_r10, `suppliers`.`fax` AS t0_r11, `suppliers`.`mail_address` AS t0_r12, `suppliers`.`supplier_code` AS t0_r13, `suppliers`.`open_time` AS t0_r14, `suppliers`.`end_time` AS t0_r15, `suppliers`.`password_digest` AS t0_r16, `suppliers`.`password_reset_send_at` AS t0_r17, `suppliers`.`password_reset_token` AS t0_r18, `suppliers`.`image_file` AS t0_r19, `suppliers`.`appeal_point` AS t0_r20, `suppliers`.`catch_copy` AS t0_r21, `suppliers`.`charge_image_file` AS t0_r22, `suppliers`.`name_kana` AS t0_r23, 
 `suppliers`.`address_2` AS t0_r24, `suppliers`.`delivery_price` AS t0_r25, `suppliers`.`is_delivery` AS t0_r26, `suppliers`.`state` AS t0_r27, `suppliers`.`city` AS t0_r28, `suppliers`.`zipcode1` AS t0_r29, `suppliers`.`zipcode2` AS t0_r30, `suppliers`.`is_publish` AS t0_r31, `suppliers`.`rates` AS t0_r32, `suppliers`.`first_login` AS t0_r33, `suppliers`.`category_id` AS t0_r34, `suppliers`.`admin_memo` AS t0_r35, `suppliers`.`delivery_memo` AS t0_r36, 
 `suppliers`.`lock_version` AS t0_r37, `suppliers`.`is_demo` AS t0_r38, `suppliers`.`confirm_token` AS t0_r39, `suppliers`.`confirm_token_send_at` AS t0_r40, `suppliers`.`confirmed_at` AS t0_r41, `suppliers`.`invoice_expires_at` AS t0_r42, `suppliers`.`aggregate_at` AS t0_r43, `suppliers`.`invoice_target_at` AS t0_r44, `suppliers`.`invoice_expires_month` AS t0_r45, 
 `orders`.`id` AS t1_r0, `orders`.`user_id` AS t1_r1, `orders`.`deliver_way_id` AS t1_r2, `orders`.`price` AS t1_r3, `orders`.`order_status` AS t1_r4, `orders`.`final_at` AS t1_r5, `orders`.`created_at` AS t1_r6, `orders`.`updated_at` AS t1_r7, `orders`.`order_way_id` AS t1_r8, `orders`.`supplier_id` AS t1_r9, `orders`.`staff_id` AS t1_r10, `orders`.`shop_id` AS t1_r11, `orders`.`order_code` AS t1_r12, `orders`.`tax_price` AS t1_r13, `orders`.`postage` AS t1_r14, `orders`.`deleted_at` AS t1_r15, `orders`.`commission` AS t1_r16, `orders`.`is_check_commission` 
 AS t1_r17, `orders`.`rates` AS t1_r18, `orders`.`type` AS t1_r19, `orders`.`auth_token` AS t1_r20, `orders`.`auth_token_at` AS t1_r21, `orders`.`invoice_id` AS t1_r22, `orders`.`is_draft` AS t1_r23, `orders`.`is_public` AS t1_r24, `orders`.`restaurant_id` AS t1_r25, `orders`.`memo` AS t1_r26, `orders`.`shipment_address_id` AS t1_r27, `orders`.`is_supplier_create` AS t1_r28, `orders`.`received_at` AS t1_r29, `orders`.`is_tax` AS t1_r30, `orders`.`postage_tax_price` AS t1_r31, `orders`.`is_restaurant_created` AS t1_r32, `orders`.`pre_supplier_id` AS t1_r33 FROM 
 `suppliers` LEFT OUTER JOIN `orders` ON `orders`.`supplier_id` = `suppliers`.`id` AND `orders`