n + 1 query

1. N + 1 query là gì

Trong rails, ORM mặc định kích hoạt chức năng lazy-loading, điều đó có nghĩa là khi nào cần dữ liệu thì chương trình mới load ra và tạo câu truy vấn. Vì vậy các truy vấn được tìm tới bản ghi "cha", sau đó mới thực hiện từng truy vấn đối với các bản ghi "con“, sẽ xảy ra tình trạng có 1 truy vấn của bản ghi cha và n truy vấn của bản ghi con

=> n+1 query

2. Ví dụ n + 1 query

Ta có bảng users, posts có quan hệ một nhiều như sau

class User < ApplicationRecord
  has_many :posts
end

class Post < ApplicationRecord
  belongs_to :user
end

Tại controllers/posts_controller.rb ta khai báo 1 biến instance như sau

class PostsController < ApplicationController
  def index
    @posts = Post.limit 5
  end
end

Ta cùng xem ví dụ một câu truy vấn đơn giản rất hay dùng:

Post.limit(5).each{|post| post.user.name }

Kết quả đúng như mong đợi, nhưng:

2.3.1 :001 > Post.limit(5).each{|post| post.user.name }
  Post Load (179.0ms)  SELECT  `posts`.* FROM `posts` LIMIT 5
  User Load (0.5ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 11 LIMIT 1
  User Load (0.8ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 11 LIMIT 1
  User Load (0.4ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 11 LIMIT 1
  User Load (0.4ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 11 LIMIT 1
  User Load (0.3ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 12 LIMIT 1

Đã có rất nhiều câu truy vấn giống nhau được thực hiện. Bởi vì mỗi một vòng lặp thì sẽ có 1 câu truy vấn lấy ra user của một post. Đó chính là vấn đề n+1 query

Trong trường hợp có rất nhiều bài post, thì sẽ cực kỳ nguy hiểm, nó làm giảm hiệu năng đi rất rất nhiều.

Vậy giải pháp ở đây là gì?

3. Giải pháp

Ví dụ giải pháp: Ta thực hiện tương tự, nhưng thêm includes(:user) vào. Kết quả:

2.3.1 :002 > Post.limit(5).includes(:user).each{|post| post.user.name }
  Post Load (1940.6ms)  SELECT  `posts`.* FROM `posts` LIMIT 5
  User Load (141.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` IN (11, 12)

Kết quả đã được cải thiện tốt hơn đúng không ạ?

Như vậy là từ 6 câu truy vấn đã giảm xuống còn 2 câu truy vấn, hiệu năng tăng lên rất nhiều.

Bên cạnh includes() còn có preload() và eager_load() cũng hỗ trợ giải quyết vấn đề này. Vậy chúng ta bắt đầu tìm hiểu kĩ hơn về chúng.

Trước hết ta xem chúng hoạt động thế nào đã

#preload
Post.limit(5).preload(:user).each{|post| post.user.name }
  Post Load (9.5ms)  SELECT  `posts`.* FROM `posts` LIMIT 5
  User Load (0.7ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` IN (8, 9, 10, 11, 12)

#includes
Post.limit(5).includes(:user).each{|post| post.user.name }
  Post Load (9.5ms)  SELECT  `posts`.* FROM `posts` LIMIT 5
  User Load (0.7ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` IN (8, 9, 10, 11, 12)

#eager_load
Post.limit(5).eager_load(:user).each{|post| post.user.name }
  SQL (34.0ms)  SELECT  `posts`.`id` AS t0_r0, `posts`.`title` AS t0_r1, `posts`.`description` AS t0_r2, `posts`.`content` AS t0_r3, `posts`.`image` AS t0_r4, `posts`.`audio` AS t0_r5, `posts`.`user_id` AS t0_r6, `posts`.`category_id` AS t0_r7, `posts`.`status` AS t0_r8, `posts`.`post_type` AS t0_r9, `posts`.`accepted_by` AS t0_r10, `posts`.`likes_count` AS t0_r11, `posts`.`created_at` AS t0_r12, `posts`.`updated_at` AS t0_r13, `users`.`id` AS t1_r0, `users`.`name` AS t1_r1, `users`.`user_name` AS t1_r2, `users`.`sex` AS t1_r3, `users`.`birthday` AS t1_r4, `users`.`phone_number` AS t1_r5, `users`.`address` AS t1_r6, `users`.`avatar` AS t1_r7, `users`.`status` AS t1_r8, `users`.`role` AS t1_r9, `users`.`created_at` AS t1_r10, `users`.`updated_at` AS t1_r11, `users`.`email` AS t1_r12, `users`.`encrypted_password` AS t1_r13, `users`.`reset_password_token` AS t1_r14, `users`.`reset_password_sent_at` AS t1_r15, `users`.`remember_created_at` AS t1_r16, `users`.`sign_in_count` AS t1_r17, `users`.`current_sign_in_at` AS t1_r18, `users`.`last_sign_in_at` AS t1_r19, `users`.`current_sign_in_ip` AS t1_r20, `users`.`last_sign_in_ip` AS t1_r21, `users`.`provider` AS t1_r22, `users`.`uid` AS t1_r23 FROM `posts` LEFT OUTER JOIN `users` ON `users`.`id` = `posts`.`user_id` LIMIT 5

Đã thấy dự khác biệt giữa eager_load() so với includes() và preload() ở đây. eagerload() đã gộp tất cả các câu truy vấn thành 1 câu với lệnh JOIN thay vì 2.

Preload

khi ta sử dụng preload() thì sẽ luôn luôn thực hiện 2 câu truy vấn trong 1 quan hệ, ở ví dụ trên kia thì 1 truy vấn lấy ra 5 posts, 1 truy vấn lấy ra 5 user của 5 thằng post đấy. Tương tự có thể là 3,4,.. câu truy vấn khi chúng ta sử dụng nhiều quan hệ. các câu truy vấn ở các bảng khác nhau, không sử dụng join.

Post.limit(5).preload(:user, :category).each{|post| [post.user.name, post.category.name]  }
  Post Load (1197.4ms)  SELECT  `posts`.* FROM `posts` LIMIT 5
  User Load (143.7ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` IN (8, 9, 10, 11, 12)
  Category Load (73.7ms)  SELECT `categories`.* FROM `categories` WHERE `categories`.`id` IN (2, 1, 6, 12)

Có hạn chế là preload() không thực hiện được khi có điều kiện đối với bảng quan hệ với bảng chính.

User.preload(:posts).where(posts: {post_type: :normal})
  User Load (1.9ms)  SELECT `users`.* FROM `users` WHERE `posts`.`post_type` = 0
ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'posts.post_type' in 'where clause': SELECT `users`.* FROM `users` WHERE `posts`.`post_type` = 0

Eager_load

Đối với eager_load() thì có sự khác biệt là nó luôn luôn gồm tất cả các câu truy vấn dù ít hay nhiều lại thành 1 câu truy vấn phức tạp duy nhất và sử dụng join. Ngược lại preload(), eager_load() vẫn chạy tốt khi chúng ta sử dụng điều kiện của bảng quan hệ với bảng chính

User.eager_load(:posts).where(posts: {post_type: :normal})
  SQL (1.5ms)  SELECT `users`.`id` AS t0_r0, `users`.`name` AS t0_r1, `users`.`user_name` AS t0_r2, `users`.`sex` AS t0_r3, `users`.`birthday` AS t0_r4, `users`.`phone_number` AS t0_r5, `users`.`address` AS t0_r6, `users`.`avatar` AS t0_r7, `users`.`status` AS t0_r8, `users`.`role` AS t0_r9, `users`.`created_at` AS t0_r10, `users`.`updated_at` AS t0_r11, `users`.`email` AS t0_r12, `users`.`encrypted_password` AS t0_r13, `users`.`reset_password_token` AS t0_r14, `users`.`reset_password_sent_at` AS t0_r15, `users`.`remember_created_at` AS t0_r16, `users`.`sign_in_count` AS t0_r17, `users`.`current_sign_in_at` AS t0_r18, `users`.`last_sign_in_at` AS t0_r19, `users`.`current_sign_in_ip` AS t0_r20, `users`.`last_sign_in_ip` AS t0_r21, `users`.`provider` AS t0_r22, `users`.`uid` AS t0_r23, `posts`.`id` AS t1_r0, `posts`.`title` AS t1_r1, `posts`.`description` AS t1_r2, `posts`.`content` AS t1_r3, `posts`.`image` AS t1_r4, `posts`.`audio` AS t1_r5, `posts`.`user_id` AS t1_r6, `posts`.`category_id` AS t1_r7, `posts`.`status` AS t1_r8, `posts`.`post_type` AS t1_r9, `posts`.`accepted_by` AS t1_r10, `posts`.`likes_count` AS t1_r11, `posts`.`created_at` AS t1_r12, `posts`.`updated_at` AS t1_r13 FROM `users` LEFT OUTER JOIN `posts` ON `posts`.`user_id` = `users`.`id` WHERE `posts`.`post_type` = 0

Includes

includes có lẽ là thằng 3D nhất ở đây =)) , nó sẽ thực hiện tương tự như preload() khi không có điều kiện, và thực hiện như eager_load() khi có điều kiện.

#khi không có điều kiện
SELECT `users`.* FROM `users`
  Post Load (8.1ms)  SELECT `posts`.* FROM `posts` WHERE `posts`.`user_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13)

#khi có điều kiện
User.includes(:posts).where(posts: {post_type: :normal})
  SQL (1.5ms)  SELECT `users`.`id` AS t0_r0, `users`.`name` AS t0_r1, `users`.`user_name` AS t0_r2, `users`.`sex` AS t0_r3, `users`.`birthday` AS t0_r4, `users`.`phone_number` AS t0_r5, `users`.`address` AS t0_r6, `users`.`avatar` AS t0_r7, `users`.`status` AS t0_r8, `users`.`role` AS t0_r9, `users`.`created_at` AS t0_r10, `users`.`updated_at` AS t0_r11, `users`.`email` AS t0_r12, `users`.`encrypted_password` AS t0_r13, `users`.`reset_password_token` AS t0_r14, `users`.`reset_password_sent_at` AS t0_r15, `users`.`remember_created_at` AS t0_r16, `users`.`sign_in_count` AS t0_r17, `users`.`current_sign_in_at` AS t0_r18, `users`.`last_sign_in_at` AS t0_r19, `users`.`current_sign_in_ip` AS t0_r20, `users`.`last_sign_in_ip` AS t0_r21, `users`.`provider` AS t0_r22, `users`.`uid` AS t0_r23, `posts`.`id` AS t1_r0, `posts`.`title` AS t1_r1, `posts`.`description` AS t1_r2, `posts`.`content` AS t1_r3, `posts`.`image` AS t1_r4, `posts`.`audio` AS t1_r5, `posts`.`user_id` AS t1_r6, `posts`.`category_id` AS t1_r7, `posts`.`status` AS t1_r8, `posts`.`post_type` AS t1_r9, `posts`.`accepted_by` AS t1_r10, `posts`.`likes_count` AS t1_r11, `posts`.`created_at` AS t1_r12, `posts`.`updated_at` AS t1_r13 FROM `users` LEFT OUTER JOIN `posts` ON `posts`.`user_id` = `users`.`id` WHERE `posts`.`post_type` = 0

Rất thông minh đúng không ạ? Bên cạnh đó chúng ta có thể cho nó hoạt động như eager_load() nếu thêm references vào sau:

 User.includes(:posts).references(:posts)
  SQL (69.9ms)  SELECT `users`.`id` AS t0_r0, `users`.`name` AS t0_r1, `users`.`user_name` AS t0_r2, `users`.`sex` AS t0_r3, `users`.`birthday` AS t0_r4, `users`.`phone_number` AS t0_r5, `users`.`address` AS t0_r6, `users`.`avatar` AS t0_r7, `users`.`status` AS t0_r8, `users`.`role` AS t0_r9, `users`.`created_at` AS t0_r10, `users`.`updated_at` AS t0_r11, `users`.`email` AS t0_r12, `users`.`encrypted_password` AS t0_r13, `users`.`reset_password_token` AS t0_r14, `users`.`reset_password_sent_at` AS t0_r15, `users`.`remember_created_at` AS t0_r16, `users`.`sign_in_count` AS t0_r17, `users`.`current_sign_in_at` AS t0_r18, `users`.`last_sign_in_at` AS t0_r19, `users`.`current_sign_in_ip` AS t0_r20, `users`.`last_sign_in_ip` AS t0_r21, `users`.`provider` AS t0_r22, `users`.`uid` AS t0_r23, `posts`.`id` AS t1_r0, `posts`.`title` AS t1_r1, `posts`.`description` AS t1_r2, `posts`.`content` AS t1_r3, `posts`.`image` AS t1_r4, `posts`.`audio` AS t1_r5, `posts`.`user_id` AS t1_r6, `posts`.`category_id` AS t1_r7, `posts`.`status` AS t1_r8, `posts`.`post_type` AS t1_r9, `posts`.`accepted_by` AS t1_r10, `posts`.`likes_count` AS t1_r11, `posts`.`created_at` AS t1_r12, `posts`.`updated_at` AS t1_r13 FROM `users` LEFT OUTER JOIN `posts` ON `posts`.`user_id` = `users`.`id`

Vậy thì chúng ta nên dùng cách nào là tốt nhất?

Cách phù hợp nhất ở đây tùy vào mục đích sử dụng của mỗi người, Còn theo cá nhân mình thì nên sử dụng includes() vì nó linh động, nhìn câu truy vấn đẹp mắt, dễ hiểu hơn cái join kia nhiều