Tôi nhớ lần đầu tiên tôi nhìn thấy ActiveRecord của rails. Đó là một sự mặc khải. Điều này đã trở lại vào năm 2005 và tôi đang viết tay các truy vấn SQL cho một ứng dụng PHP. Đột nhiên, việc sử dụng cơ sở dữ liệu trở thành một công việc tẻ nhạt, trở nên dễ dàng và - tôi dám nói - thú vị.
... sau đó tôi bắt đầu nhận thấy các vấn đề về hiệu suất.
Bản thân ActiveRecord không chậm. Tôi chỉ ngừng chú ý đến các truy vấn thực sự đang được chạy. Và hóa ra, một số truy vấn cơ sở dữ liệu thành ngữ nhất được sử dụng trong các ứng dụng Rails CRUD theo mặc định là khá kém trong việc mở rộng thành các tập dữ liệu lớn hơn.
Trong bài viết này, chúng ta sẽ thảo luận về ba trong số những thủ phạm lớn nhất. Nhưng trước tiên, hãy nói về cách bạn có thể biết liệu các truy vấn DB của bạn có mở rộng quy mô tốt hay không.
Đo lường Hiệu suất
Mọi truy vấn DB đều hiệu quả nếu bạn có một tập dữ liệu đủ nhỏ. Vì vậy, để thực sự cảm nhận được hiệu suất, chúng ta cần phải so sánh với cơ sở dữ liệu có kích thước sản xuất. Trong các ví dụ của chúng tôi, chúng tôi sẽ sử dụng một bảng có tên là faults với khoảng 22.000 bản ghi.
Chúng tôi đang sử dụng postgres. Trong postgres, cách bạn đo lường hiệu suất là sử dụng explain . Ví dụ:
# explain (analyze) select * from faults where id = 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Index Scan using faults_pkey on faults (cost=0.29..8.30 rows=1 width=1855) (actual time=0.556..0.556 rows=0 loops=1)
Index Cond: (id = 1)
Total runtime: 0.626 ms
Điều này cho thấy cả chi phí ước tính để thực hiện truy vấn (cost=0.29..8.30 rows=1 width=1855) và thời gian thực tế để thực hiện nó (actual time=0.556..0.556 rows=0 loops=1)
Nếu bạn thích định dạng dễ đọc hơn, bạn có thể yêu cầu người đăng bài in kết quả trong YAML.
# explain (analyze, format yaml) select * from faults where id = 1;
QUERY PLAN
--------------------------------------
- Plan: +
Node Type: "Index Scan" +
Scan Direction: "Forward" +
Index Name: "faults_pkey" +
Relation Name: "faults" +
Alias: "faults" +
Startup Cost: 0.29 +
Total Cost: 8.30 +
Plan Rows: 1 +
Plan Width: 1855 +
Actual Startup Time: 0.008 +
Actual Total Time: 0.008 +
Actual Rows: 0 +
Actual Loops: 1 +
Index Cond: "(id = 1)" +
Rows Removed by Index Recheck: 0+
Triggers: +
Total Runtime: 0.036
(1 row)
Hiện tại, chúng tôi sẽ chỉ tập trung vào "Hàng kế hoạch" và "Hàng thực tế".
- Hàng trong kế hoạch Trong trường hợp xấu nhất, DB sẽ phải lặp lại bao nhiêu hàng để trả lời truy vấn của bạn
- Hàng Thực tế Khi nó thực thi truy vấn, DB thực sự đã lặp qua bao nhiêu hàng?
Nếu "Hàng kế hoạch" là 1, giống như ở trên, thì truy vấn có thể sẽ mở rộng quy mô tốt. Nếu "Hàng kế hoạch" bằng số hàng trong cơ sở dữ liệu, điều đó có nghĩa là truy vấn sẽ thực hiện "quét toàn bộ bảng" và sẽ không mở rộng quy mô tốt.
Bây giờ bạn đã biết cách đo lường hiệu suất truy vấn, chúng ta hãy xem xét một số thành ngữ đường ray phổ biến và xem chúng xếp chồng lên nhau như thế nào.
Đếm
Thực sự thường thấy mã như thế này trong các chế độ xem Rails:
Total Faults <%= Fault.count %>
Điều đó dẫn đến SQL trông giống như sau:
select count(*) from faults;
Hãy tham gia vào explain và hãy xem chuyện gì xảy ra.
# explain (analyze, format yaml) select count(*) from faults;
QUERY PLAN
--------------------------------------
- Plan: +
Node Type: "Aggregate" +
Strategy: "Plain" +
Startup Cost: 1840.31 +
Total Cost: 1840.32 +
Plan Rows: 1 +
Plan Width: 0 +
Actual Startup Time: 24.477 +
Actual Total Time: 24.477 +
Actual Rows: 1 +
Actual Loops: 1 +
Plans: +
- Node Type: "Seq Scan" +
Parent Relationship: "Outer"+
Relation Name: "faults" +
Alias: "faults" +
Startup Cost: 0.00 +
Total Cost: 1784.65 +
Plan Rows: 22265 +
Plan Width: 0 +
Actual Startup Time: 0.311 +
Actual Total Time: 22.839 +
Actual Rows: 22265 +
Actual Loops: 1 +
Triggers: +
Total Runtime: 24.555
(1 row)
Chà! Truy vấn đếm đơn giản của chúng tôi đang lặp lại hơn 22.265 hàng - toàn bộ bảng! Trong postgres, số lượng luôn lặp lại trên toàn bộ tập hợp bản ghi.
Bạn có thể giảm kích thước của bộ ghi bằng cách thêm where điều kiện cho truy vấn. Tùy thuộc vào yêu cầu của bạn, bạn có thể nhận được kích thước đủ thấp để hiệu suất có thể chấp nhận được.
Cách duy nhất khác để giải quyết vấn đề này là lưu các giá trị đếm của bạn vào bộ nhớ cache. Rails có thể làm điều này cho bạn nếu bạn thiết lập nó:
belongs_to :project, :counter_cache => true
Một giải pháp thay thế khác có sẵn khi kiểm tra xem truy vấn có trả về bất kỳ bản ghi nào hay không. Thay vì Users.count > 0 , hãy thử Users.exists? . Truy vấn kết quả hoạt động hiệu quả hơn nhiều. (Cảm ơn độc giả Gerry Shaw đã chỉ ra điều này cho tôi.)
Sắp xếp
Trang chỉ mục. Hầu hết mọi ứng dụng đều có ít nhất một. Bạn kéo 20 bản ghi mới nhất từ cơ sở dữ liệu và hiển thị chúng. Điều gì có thể đơn giản hơn?
Mã để tải các bản ghi có thể trông giống như sau:
@faults = Fault.order(created_at: :desc)
Sql cho điều đó trông giống như sau:
select * from faults order by created_at desc;
Vì vậy, hãy phân tích nó:
# explain (analyze, format yaml) select * from faults order by created_at desc;
QUERY PLAN
--------------------------------------
- Plan: +
Node Type: "Sort" +
Startup Cost: 39162.46 +
Total Cost: 39218.12 +
Plan Rows: 22265 +
Plan Width: 1855 +
Actual Startup Time: 75.928 +
Actual Total Time: 86.460 +
Actual Rows: 22265 +
Actual Loops: 1 +
Sort Key: +
- "created_at" +
Sort Method: "external merge" +
Sort Space Used: 10752 +
Sort Space Type: "Disk" +
Plans: +
- Node Type: "Seq Scan" +
Parent Relationship: "Outer"+
Relation Name: "faults" +
Alias: "faults" +
Startup Cost: 0.00 +
Total Cost: 1784.65 +
Plan Rows: 22265 +
Plan Width: 1855 +
Actual Startup Time: 0.004 +
Actual Total Time: 4.653 +
Actual Rows: 22265 +
Actual Loops: 1 +
Triggers: +
Total Runtime: 102.288
(1 row)
Ở đây chúng ta thấy rằng DB đang sắp xếp tất cả 22.265 hàng mỗi khi bạn thực hiện truy vấn này. Không bueno!
Theo mặc định, mọi mệnh đề "order by" trong SQL của bạn khiến tập hợp bản ghi được sắp xếp ngay sau đó, trong thời gian thực. Không có bộ nhớ đệm. Không có phép thuật nào để cứu bạn.
Giải pháp là sử dụng các chỉ mục. Đối với những trường hợp đơn giản như trường hợp này, việc thêm một chỉ mục được sắp xếp vào cột create_at sẽ tăng tốc truy vấn lên một chút.
Trong quá trình di chuyển Rails, bạn có thể đặt:
class AddIndexToFaultCreatedAt < ActiveRecord::Migration
def change
add_index(:faults, :created_at)
end
end
Cái nào chạy SQL sau:
CREATE INDEX index_faults_on_created_at ON faults USING btree (created_at);
Cuối cùng, (created_at) chỉ định một thứ tự sắp xếp. Theo mặc định, nó tăng dần.
Bây giờ, nếu chúng tôi chạy lại truy vấn sắp xếp của mình, chúng tôi thấy rằng nó không còn bao gồm bước sắp xếp nữa. Nó chỉ đơn giản là đọc dữ liệu được sắp xếp trước từ chỉ mục.
# explain (analyze, format yaml) select * from faults order by created_at desc;
QUERY PLAN
----------------------------------------------
- Plan: +
Node Type: "Index Scan" +
Scan Direction: "Backward" +
Index Name: "index_faults_on_created_at"+
Relation Name: "faults" +
Alias: "faults" +
Startup Cost: 0.29 +
Total Cost: 5288.04 +
Plan Rows: 22265 +
Plan Width: 1855 +
Actual Startup Time: 0.023 +
Actual Total Time: 8.778 +
Actual Rows: 22265 +
Actual Loops: 1 +
Triggers: +
Total Runtime: 10.080
(1 row)
Nếu bạn đang sắp xếp theo nhiều cột, bạn cũng cần tạo một chỉ mục được sắp xếp theo nhiều cột. Đây là những gì trông giống như trong một quá trình di chuyển Rails:
add_index(:faults, [:priority, :created_at], order: {priority: :asc, created_at: :desc)
Khi bạn bắt đầu thực hiện các truy vấn phức tạp hơn, bạn nên chạy chúng thông qua explain . Làm điều đó sớm và thường xuyên. Bạn có thể thấy rằng một số thay đổi đơn giản đối với truy vấn đã khiến các postgres không thể sử dụng chỉ mục để sắp xếp.
Giới hạn và Mức chênh lệch
Trong các trang chỉ mục của chúng tôi, chúng tôi hầu như không bao giờ đưa mọi mục vào cơ sở dữ liệu. Thay vào đó, chúng tôi phân trang, chỉ hiển thị 10 hoặc 30 hoặc 50 mục cùng một lúc. Cách phổ biến nhất để thực hiện việc này là sử dụng limit và offset cùng với nhau. Trong Rails, nó trông như thế này:
Fault.limit(10).offset(100)
Điều đó tạo ra SQL trông giống như sau:
select * from faults limit 10 offset 100;
Bây giờ nếu chúng tôi giải thích, chúng tôi thấy một cái gì đó kỳ lạ. Số hàng được quét là 110, bằng giới hạn cộng với phần bù.
# explain (analyze, format yaml) select * from faults limit 10 offset 100;
QUERY PLAN
--------------------------------------
- Plan: +
Node Type: "Limit" +
...
Plans: +
- Node Type: "Seq Scan" +
Actual Rows: 110 +
...
Nếu bạn thay đổi độ lệch thành 10.000, bạn sẽ thấy rằng số hàng được quét sẽ tăng lên 10010 và truy vấn chậm hơn 64 lần.
# explain (analyze, format yaml) select * from faults limit 10 offset 10000;
QUERY PLAN
--------------------------------------
- Plan: +
Node Type: "Limit" +
...
Plans: +
- Node Type: "Seq Scan" +
Actual Rows: 10010 +
...
Điều này dẫn đến một kết luận đáng lo ngại:khi phân trang, các trang sau tải chậm hơn các trang trước đó. Nếu chúng tôi giả định 100 mục mỗi trang trong ví dụ trên, trang 100 chậm hơn 13 lần so với trang 1.
Vậy bạn làm gì?
Thành thật mà nói, tôi đã không thể tìm thấy một giải pháp hoàn hảo. Tôi sẽ bắt đầu bằng cách xem liệu tôi có thể giảm kích thước của tập dữ liệu để tôi không cần phải có 100 hoặc 1000 trang để bắt đầu hay không.
Nếu bạn không thể giảm thiết lập kỷ lục của mình, cách tốt nhất của bạn có thể là thay thế bù đắp / giới hạn bằng mệnh đề where.
# You could use a date range
Fault.where("created_at > ? and created_at < ?", 100.days.ago, 101.days.ago)
# ...or even an id range
Fault.where("id > ? and id < ?", 100, 200)
Kết luận
Tôi hy vọng bài viết này đã thuyết phục bạn rằng bạn thực sự nên tận dụng chức năng giải thích của postgres để tìm các vấn đề về hiệu suất có thể xảy ra với các truy vấn db của bạn. Ngay cả những truy vấn đơn giản nhất cũng có thể gây ra các vấn đề lớn về hiệu suất, vì vậy bạn phải trả tiền để kiểm tra. :)