Sql nâng cao cho Rails part 1

Giới thiệu

Như các bạn đã biết khi làm việc với rails chúng ra có 1 hỗ trợ tuyệt vời để kết nối với dữ liệu mysql thông qua Active record. Công cụ này giúp chúng ta, các coder có thể xử lý dữ liệu một cách nhanh chóng với những câu lệnh đơn giản và ngắn gọn. Nhờ vậy ta có thể tập trung vào việc xử lý logic và các thứ khác của hệ thống mà ko cần phải viết những câu sql dài ngoằng phức tạp. Điều này với tôi thật tuyệt vời bởi lẽ với đa phần công việc truy vấn tới dữ liệu Active record đã hoàn thành rất tốt rồi. Tuy nhiên sau một thời gian làm việc chắc chắc các bạn sẽ nhận thấy một vấn đề đó là tốc độ xử lý dữ liệu phức tạp ỏ trong 1 hệ thống lớn thì Active record thực sự là không tốt và nó khiến cho hiệu năng trang web bị giảm đáng kể. Và giải pháp duy nhất đó là chúng ta cần tiếp cận "gần" hơn đến tầng dữ liệu để cải thiện hiệu năng.

Do đó tôi đã quyết định viết bài này để giúp bạn có thêm các cách xử lý dữ liệu phức tạp với sql. Và ở phần 1 của bài viết này chúng ta sẽ đi tìm hiểu về 2 tính năng khá mạng và hay được áp dụng gần đây đó là windown function và view.

Window function

Trước hết hãy bắt đầu bằng 1 ví dụ, giả sử bạn muốn xem được tổng lượng tiền của mình còn lại trong ngân hàng thông qua các lần giao dịch. Thật đơn giản với hàm sum

SELECT sum(amount) FROM transactions WHERE account = 'debit';

  sum
--------
 387.04

Vậy là ta đã có 1 kết quả duy nhất la tổng lượng tiền hiện tại. Tuy nhiên giờ bạn lại muốn theo dõi số tiền bạn có ứng với mỗi lần giao dịch, như mô hình ở kết quả sau.

   date    | amount | balance
------------+--------+---------
 2016-07-01 |  50.25 |   50.25
 2016-07-01 |  17.35 |   67.60
 2016-07-01 |  21.56 |   89.16
 2016-07-02 |  14.01 |  103.17
 2016-07-02 |  79.23 |  182.40
 2016-07-02 | -15.00 |  167.40
 2016-07-02 |  46.23 |  213.63
 2016-07-03 | 100.74 |  314.37
 2016-07-03 |  72.67 |  387.04

Ok cũng không khó, với Rails cơ bản thì ta cần 2 bước để có được bảng dữ này, trước hết ta cần lấy lịch sử giao dịch, sau đó chạy 1 vòng each để tìm ra số dư ứng với mỗi lần giao dịch đó. Tuy nhiên ở đây SQL có hỗ trợ chúng ta 1 thứ gọi là window function để xử lý vấn đề này. Window function cho phép chúng ta kết hợp việc ính toán ứng với từng row bằng cách sử dụng 1 cái gọi là “windown” trong câu lệnh query để giúp ta có thể chia dữ liệu rồi xử lý chúng. Mình nói có vẻ hơi phức tạp nên ta có thể tìm hiểu qua ví dụ sau ứng với trường hợp in ra bảng ở trên. SELECT date, amount, sum(amount) OVER(ORDER BY date, id) AS balance FROM transactions WHERE account = 'debit'; Trong ví dụ này ứng với mỗi row ta đã yêu cầu tính toán thêm 1 trường balance bằng cách tính tổng “sum” của trường amount thông qua 1 “window” là tập con của bảng dữ liệu thông qua lệnh OVER ở trên. Có thể tóm gọn câu lện trên là tạo ra cột balance là tổng của các row amount nhưng tính theo lượng row từ đầu đến row hiện tại với thứ tự sắp xếp theo date và id tính từ. Mô hình tính toán của câu lệnh trên có thể được mô tả ở hình sau

Ngoài ra Window function có thể lọc dữ liệu theo nhiều cách và từ đó tá có thể ứng dụng tính toán với từng phần dữ liệu được lọc ra đó. Ví dụ ta có thể nhóm các bản ghi vào nhóm khác nhau trước khi thưc hiện việc tính toán

Ví dụ:

SELECT date, amount, account,
sum(amount) OVER(PARTITION BY account ORDER BY date, id) AS balance
FROM transactions;

    date    | amount | account | balance
------------+--------+---------+---------
 2016-07-01 |  50.25 | debit   |   50.25
 2016-07-01 |  17.35 | debit   |   67.60
 2016-07-01 |  21.56 | debit   |   89.16
 2016-07-02 |  14.01 | debit   |  103.17
 2016-07-02 |  79.23 | debit   |  182.40
 2016-07-02 | -15.00 | debit   |  167.40
 2016-07-02 |  46.23 | debit   |  213.63
 2016-07-03 | 100.74 | debit   |  314.37
 2016-07-03 |  72.67 | debit   |  387.04
 2016-06-15 |     25 | savings |      25
 2016-06-22 |     25 | savings |      50
 2016-07-01 |     25 | savings |      75
 2016-07-08 |     25 | savings |     100
 2016-07-16 |     25 | savings |     125
(14 rows)

Ở đây gần giống với câu lệnh sql trước đó, tuy nhiên ta có bổ sung thêm 1 phần đó là PARTITION BY account để giúp ta chia nhóm dữ liệu trước khi xử lý tính toán. Mô hình xử lý của câu lệnh này có thể được mô hình theo ảnh dưới đây

Đầu tiên câu lệnh sẽ lấy dữ liệu và chia làm 2 phần theo dạng accout là debit và saving, trong mỗi phần đó các bản ghi vẫn được sắp xếp theo date và id bản ghi. Sau đó việc tính tổng balance mới được thự hiện.

Đọc đến đây chắc nhiều bạn nghĩ rằng việc gì phải làm phức tạp như vậy, cứ lấy dữ liệu ra rồi làm hàm tính toán để in ra kết quả là được rồi mà. Cách làm đó không sai tuy nhiên như mục đích ban đầu tôi đã đề ra là việc xử lý dữ liệu lớn (như file log) cho hàng triệu bản ghi và nhằm tối ưu hóa hệ thống. Và ví dụ này khá dễ hiểu, ở thực tế có thể bạn sẽ phải xử lý trường hợp phức tạp hơn nên đây có thể là 1 gợi ý không tồi cho bài toán dạng này chút nào.

VIEW

Ở 2 ví dụ ta đã thực hiện ở trên có thể thấy rằng cột balance là cột dữ liệu ảo được tính toán ra từ cột khác và không có trong cơ sở dữ liệu. Và chúng ta thực hiện lại việc tính toán này mỗi khi câu query được gọi đến. Đồng thời ta cũng không thể thay đổi cấu trúc dữ liệu để thêm cột mới vì chi phí cao với số lg bản ghi lớn tuy nhiên ta lại cần đảm bảo rằng mọi tính toàn dữ liệu đều phải chính xác và đồng nhất mỗi khi có dữ liệu mới được thêm vào. Và chúng ta cũng biết là trong thời điểm này câu query này được gọi lại nhiều lần để sử dụng đưa ra kết qua trong view. Do đó ta cần giải pháp cho bài toán này và View tỏ ra là 1 đáp án không tồi.

Dữ liệu View cho phép bạn lưu lại query và có thể truy xuất lại nó như một bảng ghi vậy. Nhờ đó bạn có thể lấy dưc liệu một cách thông qua câu lệnh đơn giản. Và đồng thời việc tạo View cũng rất dễ dàng

Ví dụ:

CREATE VIEW debit_account_activity AS (
  SELECT date, amount,
  sum(amount) OVER(ORDER BY date, id) AS balance
  FROM transactions WHERE account = 'debit'
);

Ở đây bạn đã tạo ra 1 view tên là debit_account_activity chứa câu query của ví dụ đầu tiên ta làm.

Và ở các lần sau khi ta muốn lấy lại dữ liệu ra thì ta không cần phải viết lại cả câu query dài kia nữa mà thay vào đó ta chỉ cần gọi từ bảng View mà ta vừa tạo thôi

SELECT * FROM debit_account_activity;

    date    | amount | balance
------------+--------+---------
 2016-07-01 |  50.25 |   50.25
 2016-07-01 |  17.35 |   67.60
 2016-07-01 |  21.56 |   89.16
 2016-07-02 |  14.01 |  103.17
 2016-07-02 |  79.23 |  182.40
 2016-07-02 | -15.00 |  167.40
 2016-07-02 |  46.23 |  213.63
 2016-07-03 | 100.74 |  314.37
 2016-07-03 |  72.67 |  387.04
(9 rows)

Lưu ý ở đây ta dùng view tương tự như ta gọi hàm vậy, chỉ đơn giản là ta có thể viết gọn hơn nhưng hệ thống sẽ gọi hàm mà đã được ta khai báo từ view

Ngoài ra ở một số hệ thống còn hỗ trợ 1 thứ gọi là “materialized view” đó là view không lưu câu lệnh thông thường mà nó lưu lại cả dữ liệu giống như là 1 bảng ghi vậy. Do đó ta cần phải làm mới lại dữ liệu bảng này mỗi khi cần cập nhật dữ liệu mới. Phương pháp này phù hợp cho cho việc dữ liệu real time không cần thiết và cần lấy dữ liệu nhiều lần mà không tăng size cho hệ thống dữ liệu

Tổng kết

Trên đây là 2 phương pháp cũng không quá phức tạp và ở 1 số hoàn cảnh nào đó trong dự án bạn chạy có lẽ dùng đến và tôi tin rằng nó sẽ là 1 giải pháp không tồi chút nào đâu