- vừa được xem lúc

N + 1 query - Tính năng hay là bug

0 0 38

Người đăng: Bamboo

Theo Viblo Asia

1. N + 1 query là gì ?

Câu chuyện xảy ra khi chúng ta, những coder viết code chưa khéo, sinh ra nhiều truy vấn vào cơ sở dữ liệu làm giảm performance của hệ thống. Khi đồng nghiệp đọc code thường thì sẽ bình luận ngay: fix N + 1

Nếu để trót lọt, sau một thời gian vận hành mà chương trình chậm, điều tra ra nguyên nhân rồi lại câu nói kinh điển:

✌️ Đứa nào code ra cái đống shit này đây ✌️

2. Ví dụ về N + 1

Mình viết ví dụ tựa như mã giả thui nhé, không đặt nặng vấn đề cú pháp:

Giả sử ta có một cơ sở dữ liệu, trong đó table post có khóa ngoại user_id, nói theo kiểu mã giả là một post thuộc về một user

Thực hiện truy vấn vào cơ sở dữ liệu và lấy tất cả User kèm theo các Post của User đó:

User.all.each do |user| user.posts
end

Các câu lệnh SQL sinh ra như sau

 User Load (0.2ms) SELECT "users".* FROM "users" Post Load (0.1ms) SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ? [["user_id", 1]] Post Load (0.1ms) SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ? [["user_id", 2]] Post Load (0.1ms) SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ? [["user_id", 3]]

Lấy máy tính CASIO FX-500 MS ra để đếm thì thấy mình cần dùng 4 câu truy vấn:

  • Một truy vấn để lấy ra tất cả users => đây chính là 1 trong "N+1"
  • Ba truy vấn để lấy ra các post tương ứng với ba user trong cơ sở dữ liệu => đây chính là N trong "N+1"

Đối với những hệ thống có số lượng bản ghi lớn (cỡ như phải trả về 1000 user thì chúng ta phải thực hiện 1001 truy vấn) hoặc có database với độ trễ cao (thời gian thực thi truy vấn cao) thì ắt hẳn sẽ làm giảm performance của hệ thống.

Vậy làm sao để có thể lấy ra dữ liệu tương đương như vậy nhưng với số lượng truy vấn hơn ?

2. Cách khắc phục

2.1 Sử dụng select in ()

Tối ưu câu lệnh SQL ngay và luôn.

User Load (0.2ms) SELECT "users".* FROM "users"
Post Load (0.4ms) SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (1, 2, 3)

Hehe, nhìn có vẻ đơn giản nhỉ 😄

Chúng ta cần 2 truy vấn:

  • Truy vấn đầu tiên để load toàn bộ user
  • Truy vấn thứ 2 để load các post tương ứng với những users đó.

Vậy là okie dồi nà 😄

2.2 Sử dụng joins

Luyên thuyên về joins một lát để hiểu tại sao dùng joins lại giảm được số query.

2.2.1 Joins là gì ?

  • Khái niệm joins bảng trong SQL hoặc là hiểu thấu tận tâm can, hoặc là có biết nhưng chưa hiểu lắm, hoặc nói toẹt luôn là không hiểu vì trông nó loằng ngoằng vãi cả chưởng, chưa kể lại còn chia ra mấy loại.

  • Mệnh đề joins trong SQL được sử dụng để kết hợp các bản ghi từ hai hoặc nhiều bảng trong một cơ sở dữ liệu bằng cách sử dụng các giá trị chung của mỗi bảng.

Ta có một ví dụ như này:

  • Bảng User:
id name address email
1 Hoa Vinh Ha Noi hoavinh@gmaill.com
2 Mão Mèo Ha Noi maomeokta@gmail.com
  • Bảng Post:
id user_id title content
1 1 Cô gái M52 Nhà em có bán rượu không
2 2 Kém Duyên Mà sao anh say vì em mất rồi

Trong ví dụ trên bạn có thể dùng joins để tạo ra bảng mới chứa tên, email của user và những tên bài đăng mà user đó tạo ra.

SELECT User.name, User.email, Post.title FROM User INNER JOIN Post ON User.id = Post.user_id

Và kết quả là:

name email titlle
Hoa Vinh hoavinh@gmaill.com Cô gái M52
Mão Mèo maomeokta@gmail.com Kém Duyên

Như vậy, joins sẽ tem tém hai bảng lại rồi truy vấn trên kết quả đó nên joins chỉ tốn một query duy nhất đã cho ra kết quả.

2.2.2 Các loại joins

Khi sử dụng joins thì chắc chắn bạn đang dùng một trong các loại sau nên ta sẽ tìm hiểu luôn:

  • Inner join: Trả về tất cả bản ghi được so khớp đúng.

    VD: Chỉ liệt kê những sinh viên nào có điểm toán lớn hơn 9

  • Left (outer) join: Trả về tất cả bản ghi từ bảng bên trái và các giá trị được so khớp từ bảng bên phải hoặc NULL khi không có so khớp nào.

    VD: Liệt kê tất cả sinh viên, nếu sinh viên nào có điểm toán lớn hơn 9 điểm thì in ra.

  • Right (outer) join: Trả về tất cả bản ghi từ bảng bên phải và các giá trị được so khớp từ bảng bên trái hoặc NULL khi không có so khớp nào.

    VD: Liệt kê tất cả điểm môn toán, nếu điểm toán lớn hơn 9 điểm thì in tên sinh viên ra.

  • Full (outer) join: Trả về tất cả bản ghi từ hai bảng.

    VD: Liệt kê tất cả sinh viên kèm theo điểm thi môn toán.

2.2 Lựa chọn cái nào

  • Hãy so sánh truy vấn do joinsselect in() tạo ra, bạn sẽ nhận thấy ngay select in() đơn giản, dễ hiểu và ngắn gọn hơn joins rất nhiều.

  • Trong trường hợp hai bảng cần joins có số lượng record lớn thì select in() sẽ vượt trội hơn hẳn.

  • Thế tại sao còn sinh ra joins trong khi select in() đã ngon, bổ, rẻ.

Vậy thì hãy xem 3 ví dụ sau:

  • Không có điều kiện where
# Mọi thứ bình yên vô sự
2.3.1 :092 > User.all.includes(:posts) User Load (0.2ms) SELECT "users".* FROM "users" Post Load (0.4ms) SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (1, 2, 3)
  • Có thêm điều kiện where trong bảng bên trái (Tức là bảng User)
# Mọi thứ vẫn bình yên vô sự
2.3.1 :097 > User.all.includes(:posts).where("users.name = ?" , "Gate") User Load (0.5ms) SELECT "users".* FROM "users" WHERE (users.name = 'Gate') Post Load (0.4ms) SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (2, 5, 8)
  • Có thêm điều kiện where trong bảng bên phải (Tức là bảng Post)
# Lỗi rồi, ahihi
2.3.1 :099 > User.all.includes(:posts).where("posts.title = ?" , "Hoa") User Load (0.6ms) SELECT "users".* FROM "users" WHERE (posts.title = 'Hoa')
ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: posts.title: SELECT "users".* FROM "users" WHERE (posts.title = 'Hoa')

Lý do lỗi ở đây là gì ? Tại sao where với bảng bên trái thì không gặp lỗi mà bảng bên phải lại gặp lỗi no such column

-> Bởi vì select in() sẽ không load trước được dữ liệu từ bảng post, nên nó không hiểu trường title là gì.

-> Còn trường name nằm ngay trong table user rồi nên không gặp lỗi.

  • Khi where với bảng bên phải thì đây là đất để joins dụng võ rồi, vì hai bảng được kết hợp với nhau nên luôn có đầy đủ thông tin các trường của mỗi bảng.
# Query succeeded rồi, ahihi.
User.all.includes(:posts).references(:posts).where("posts.title = ?" , "Hoa") SQL (0.6ms) SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "users"."created_at" AS t0_r2, "users"."updated_at" AS t0_r3, "users"."post_id" AS t0_r4, "posts"."id" AS t1_r0, "posts"."title" AS t1_r1, "posts"."created_at" AS t1_r2, "posts"."updated_at" AS t1_r3, "posts"."user_id" AS t1_r4 FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id" WHERE (posts.title = 'Hoa')
  • Các bạn đừng quá quan tâm includesreferences là gì, cứ hiểu mình dùng nó để ép kiểu truy vấn thành select in() hoặc joins là được. Phần sau mình sẽ giải thích kĩ hơn.

Vậy trong trường hợp có điều kiện where ở bảng bên phải thì phải sài joins nhé, còn ngược lại thì cứ sài select in() cho ngon, bổ, rẻ.

3. N +1 trong Rails

  • Sử dụng cách nào để khử N + 1 query thì mỗi framework sẽ có cách khái quát và trình bày riêng, nhưng bản chất thì vẫn là xoay quanh sử dụng select in() hay joins.

  • Đã là framework thì đâu có chuyện gõ truy vấn như select from ..., trừ những truy vấn phức tạp quá mới cần tạo select ... thuần rồi exec command.

  • Phần này, ta sẽ tìm hiểu giới thiệu với Rails framework, trong Rails có 3 cách khử N + 1 như sau:

preload:

# Auto sài select in()
2.3.1 :110 > User.preload(:posts) User Load (0.3ms) SELECT "users".* FROM "users" Post Load (1.0ms) SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9)

Eagerload

# Auto sài joins
2.3.1 :111 > User.eager_load(:posts) SQL (0.7ms) SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "users"."created_at" AS t0_r2, "users"."updated_at" AS t0_r3, "users"."post_id" AS t0_r4, "posts"."id" AS t1_r0, "posts"."title" AS t1_r1, "posts"."created_at" AS t1_r2, "posts"."updated_at" AS t1_r3, "posts"."user_id" AS t1_r4 FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id" => #<ActiveRecord::Relation [#<User id: 1, name: "David", created_at: "2018-04-24 11:42:42", updated_at: "2018-04-24 11:42:42", post_id: 1>, #<User id: 2, name: "Gate", created_at: "2018-04-24 11:42:42", updated_at: "2018-04-24 11:42:42", post_id: 2>, #<User id: 3, name: "Jack", created_at: "2018-04-24 11:42:42", updated_at: "2018-04-24 11:42:42", post_id: 2>, #<User id: 4, name: "David", created_at: "2018-04-24 12:44:37", updated_at: "2018-04-24 12:44:37", post_id: 1>, #<User id: 5, name: "Gate", created_at: "2018-04-24 12:44:37", updated_at: "2018-04-24 12:44:37", post_id: 2>, #<User id: 6, name: "Jack", created_at: "2018-04-24 12:44:37", updated_at: "2018-04-24 12:44:37", post_id: 2>, #<User id: 7, name: "David", created_at: "2018-04-24 12:44:39", updated_at: "2018-04-24 12:44:39", post_id: 1>, #<User id: 8, name: "Gate", created_at: "2018-04-24 12:44:39", updated_at: "2018-04-24 12:44:39", post_id: 2>, #<User id: 9, name: "Jack", created_at: "2018-04-24 12:44:39", updated_at: "2018-04-24 12:44:39", post_id: 2>]> 

Inludes

# Thanh niên này khôn biết # Mặc định thì thanh niên này dùng select in()
2.3.1 :112 > User.includes(:posts) User Load (0.3ms) SELECT "users".* FROM "users" Post Load (0.4ms) SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9) # Nhưng khi cần thiết thì thêm references vào để chuyển sang joins, ghê chưa v: 2.3.1 :113 > User.includes(:posts).references(:posts) SQL (0.7ms) SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "users"."created_at" AS t0_r2, "users"."updated_at" AS t0_r3, "users"."post_id" AS t0_r4, "posts"."id" AS t1_r0, "posts"."title" AS t1_r1, "posts"."created_at" AS t1_r2, "posts"."updated_at" AS t1_r3, "posts"."user_id" AS t1_r4 FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id" => #<ActiveRecord::Relation [#<User id: 1, name: "David", created_at: "2018-04-24 11:42:42", updated_at: "2018-04-24 11:42:42", post_id: 1>, #<User id: 2, name: "Gate", created_at: "2018-04-24 11:42:42", updated_at: "2018-04-24 11:42:42", post_id: 2>, #<User id: 3, name: "Jack", created_at: "2018-04-24 11:42:42", updated_at: "2018-04-24 11:42:42", post_id: 2>, #<User id: 4, name: "David", created_at: "2018-04-24 12:44:37", updated_at: "2018-04-24 12:44:37", post_id: 1>, #<User id: 5, name: "Gate", created_at: "2018-04-24 12:44:37", updated_at: "2018-04-24 12:44:37", post_id: 2>, #<User id: 6, name: "Jack", created_at: "2018-04-24 12:44:37", updated_at: "2018-04-24 12:44:37", post_id: 2>, #<User id: 7, name: "David", created_at: "2018-04-24 12:44:39", updated_at: "2018-04-24 12:44:39", post_id: 1>, #<User id: 8, name: "Gate", created_at: "2018-04-24 12:44:39", updated_at: "2018-04-24 12:44:39", post_id: 2>, #<User id: 9, name: "Jack", created_at: "2018-04-24 12:44:39", updated_at: "2018-04-24 12:44:39", post_id: 2>]>
  • Đó, vậy là tùy từng trường hợp cụ thể, ta sẽ dùng một trong ba cách trên.

  • Thế trong trường hợp có 3 bảng thì sao, cú pháp nó thế nào ?

2.3.1 :024 > User.includes(posts: :comments).references(:posts).where("comments.content = ?", "A") SQL (0.8ms) SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "users"."created_at" AS t0_r2, "users"."updated_at" AS t0_r3, "users"."post_id" AS t0_r4, "posts"."id" AS t1_r0, "posts"."title" AS t1_r1, "posts"."created_at" AS t1_r2, "posts"."updated_at" AS t1_r3, "posts"."user_id" AS t1_r4, "posts"."comment_id" AS t1_r5, "comments"."id" AS t2_r0, "comments"."content" AS t2_r1, "comments"."created_at" AS t2_r2, "comments"."updated_at" AS t2_r3, "comments"."post_id" AS t2_r4 FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id" WHERE (comments.content = 'A')

Cú pháp cũ hơn:

2.3.1 :026 > User.includes(:posts => [:comments]).references(:posts).where("comments.content = ?", "A")

4. Tính năng hay bug

  • Từ đầu tới giờ ta tìm hiểu thì có vẻ như N + 1 là điều không tốt cho performance của chương trình.

Nhưng chưa hẳn, hãy xem xét ví dụ sau:

  • Bạn truy cập vào trang chủ của website bán hàng, có tính năng phân trang nên chỉ load ra 20 sản phẩm đầu tiên (có kèm theo nơi sản xuất ở Hà Nội)

  • Dễ thấy rằng ở đây dễ xuất hiện N + 1 query:

    1 truy vấn đầu tiên để lấy ra 20 sản phẩm từ bảng Product.

    20 truy vấn tiếp theo lấy ra nơi sản xuất của 20 sản phẩm đó tư bảng Company.

    Có thêm điều kiện where("company.product.id = ?", "Hà Nội") ở bảng bên phải

    Vậy chúng ta cần 20 + 1 = 21 truy vấn và chúng ta sử dụng joins để khử N + 1.

  • Trong trường hợp đối với một cơ sở dữ liệu khủng, với hàng triệu record cho mỗi bảng thì việc joins hai bảng khủng như vậy sẽ tiêu tốn tài nguyên hơn 21 câu truy vấn ngắn rất nhiều lần.

  • Bản chất của joins là sẽ duyệt qua từng bản ghi của bảng thứ nhất, đối chiếu với từng bản ghi của bảng thứ 2, nếu điều kiện được thỏa mãn thì kết hợp thành một hàng, duyệt 2 vòng lặp, tiêu tốn nhiều thời gian và tài nguyên.

Hoặc một trường hợp đơn giản hơn, khi sử dụng html cache thì sao ?

Như vậy, không phải lúc nào gặp N + 1 cũng sẽ khử, nó còn phụ thuộc vào từng trường hợp cụ thể, khử được theo select in() thì thì khử nó không thương tiếc, còn phải khử theo joins thì nên chú ý tới độ lớn của bảng.


✌️ Happy coding ✌️

✌️ Thank for your attention ✌️


Nguồn:

  1. https://viblo.asia
  2. https://www.w3schools.com
  3. http://vietjack.com/index.jsp

Bình luận

Bài viết tương tự

- vừa được xem lúc

Giới thiệu Typescript - Sự khác nhau giữa Typescript và Javascript

Typescript là gì. TypeScript là một ngôn ngữ giúp cung cấp quy mô lớn hơn so với JavaScript.

0 0 525

- vừa được xem lúc

Cài đặt WSL / WSL2 trên Windows 10 để code như trên Ubuntu

Sau vài ba năm mình chuyển qua code trên Ubuntu thì thật không thể phủ nhận rằng mình đã yêu em nó. Cá nhân mình sử dụng Ubuntu để code web thì thật là tuyệt vời.

0 0 396

- vừa được xem lúc

Đặt tên commit message sao cho "tình nghĩa anh em chắc chắn bền lâu"????

. Lời mở đầu. .

1 1 737

- vừa được xem lúc

Tìm hiểu về Resource Controller trong Laravel

Giới thiệu. Trong laravel, việc sử dụng các route post, get, group để gọi đến 1 action của Controller đã là quá quen đối với các bạn sử dụng framework này.

0 0 358

- vừa được xem lúc

Phân quyền đơn giản với package Laravel permission

Như các bạn đã biết, phân quyền trong một ứng dụng là một phần không thể thiếu trong việc phát triển phần mềm, dù đó là ứng dụng web hay là mobile. Vậy nên, hôm nay mình sẽ giới thiệu một package có thể giúp các bạn phân quyền nhanh và đơn giản trong một website được viết bằng PHP với framework là L

0 0 449

- vừa được xem lúc

Bạn đã biết các tips này khi làm việc với chuỗi trong JavaScript chưa ?

Hi xin chào các bạn, tiếp tục chuỗi chủ đề về cái thằng JavaScript này, hôm nay mình sẽ giới thiệu cho các bạn một số thủ thuật hay ho khi làm việc với chuỗi trong JavaScript có thể bạn đã hoặc chưa từng dùng. Cụ thể như nào thì hãy cùng mình tìm hiểu trong bài viết này nhé (go).

0 0 433