So sánh database quan hệ: Oracle, PostgreSQL, SQL Server, MySQL

Mô hình quan hệ cho bạn những gì

Trước khi so sánh sản phẩm, cần rõ cái chung của cả bốn: mô hình quan hệ. Đây là nền tảng toán học dựa trên lý thuyết tập hợp và logic vị từ. Một bảng tương ứng với một quan hệ, mỗi dòng là một phát biểu đúng về dữ liệu.¹

Bốn thứ bạn nhận được khi chọn database quan hệ:

• ACID. Transaction là đơn vị công việc nguyên tử: hoặc mọi thay đổi xảy ra, hoặc không thay đổi nào. Dữ liệu đã commit phải sống sót sau sự cố.² Grokking mô tả ACID là một abstraction đủ mạnh để giấu nhiều bất thường (dirty read, lost update, write skew, phantom read) sau một thao tác abort đơn giản.³
• SQL. Ngôn ngữ khai báo: bạn nói muốn lấy gì, engine tự chọn cách lấy.⁴ Chuẩn ANSI/ISO có từ 1986 nên kỹ năng SQL chuyển giữa các sản phẩm khá dễ, dù mỗi hãng có phần mở rộng riêng.
• Constraint. Khoá chính, unique, khoá ngoại, check, default là một phần định nghĩa bảng. Sách gọi đây là toàn vẹn dữ liệu khai báo (declarative), khác với toàn vẹn bằng code như stored procedure hay trigger.⁵
• Join. Dữ liệu chuẩn hoá thành nhiều bảng, nối lại bằng khoá ngoại khi truy vấn. Grokking gọi đây là cơ chế giảm dư thừa (normalization).⁶⁷

Cái giá là impedance mismatch: bảng phẳng không khớp với object lồng nhau trong code ứng dụng.⁶ Đây là lý do một số bài toán chọn NoSQL thay vì SQL. Với dữ liệu có quan hệ, cần transaction và query linh hoạt, database quan hệ vẫn là lựa chọn mặc định. Xem Mô hình dữ liệu và Chọn database.

Bảng so sánh bốn hệ

Thư viện có sách về SQL Server và PostgreSQL. Oracle và MySQL/MariaDB không có sách, nên các ô của hai cột này chỉ nêu khái niệm đã phổ biến, không có phiên bản, giá hay con số benchmark.⁸⁹

Hai hàng đáng nhớ nhất khi phỏng vấn là giấy phép và ngôn ngữ thủ tục. Giấy phép quyết định tổng chi phí sở hữu. Ngôn ngữ thủ tục quyết định bạn đặt bao nhiêu logic trong database. Stored procedure có lợi: gom logic một chỗ, cấp quyền theo procedure thay vì theo bảng, tái dùng execution plan và giảm round trip mạng.²² Nhưng logic nằm trong database khó scale ngang hơn logic ở tầng ứng dụng (nhận định của khoá học).¹⁷

Ví dụ một procedure T-SQL đơn giản:

CREATE PROCEDURE Sales.GetCustomerOrders
  @custid INT, @fromdate DATE, @todate DATE
AS
BEGIN
  SELECT orderid, orderdate, custid
  FROM Sales.Orders
  WHERE custid = @custid
    AND orderdate >= @fromdate AND orderdate < @todate;
END;

Cú pháp tương tự là CREATE FUNCTION ... LANGUAGE plpgsql ở PostgreSQL, nơi sách minh hoạ bằng một hàm ghi log khi event bị cập nhật.²¹

Isolation và MVCC: khoá hay phiên bản

Mọi database quan hệ phải trả lời cùng một câu hỏi: khi một transaction đang sửa dòng, transaction khác đọc dòng đó thì thấy gì? Có hai họ cơ chế.

• Lock-based (bi quan). Người đọc xin shared lock, người ghi xin exclusive lock. Nếu dữ liệu đang ở trạng thái chưa nhất quán, người đọc phải đợi.²⁴
• Row versioning, hay MVCC (lạc quan). Người đọc không khoá và không đợi. Nếu bản hiện tại không phải bản nó được phép thấy, nó nhận bản cũ đã commit.²⁴

SQL Server hỗ trợ cả hai: bốn mức dựa trên khoá (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) và hai mức dựa trên row versioning (SNAPSHOT, READ COMMITTED SNAPSHOT).¹⁶ Bảng dưới là tóm tắt của sách:

Nguồn của bảng: T-SQL Fundamentals, Table 9-3.²⁵ Điểm hay bị hiểu sai: SNAPSHOT cho kết quả logic gần SERIALIZABLE nhưng không dùng shared lock, còn READ COMMITTED SNAPSHOT gần READ COMMITTED nhưng người đọc không bị chặn.²⁶ Cái giá của row versioning là mọi UPDATE và DELETE phải copy bản cũ sang tempdb, nên ghi chậm hơn một chút còn đọc thường nhanh hơn.²⁶

Về mặc định của từng sản phẩm: SQL Server on-premises mặc định READ COMMITTED dựa trên khoá, còn bản cloud SQL Database mặc định READ COMMITTED SNAPSHOT.¹⁶ PostgreSQL, Oracle và InnoDB của MySQL đều dùng đa phiên bản cho đọc; mức mặc định cụ thể của từng hệ khác nhau và thư viện chưa có sách kiểm chứng.¹⁰⁸⁹

Với bài toán tranh chấp như đặt vé, mức isolation mặc định thường chưa đủ, bạn cần thêm khoá dòng hoặc update có điều kiện. Xem Tranh chấp tài nguyên và phần transaction trong PostgreSQL.

Index: clustered, non-clustered và covering

Phần này dùng thuật ngữ của SQL Server, nhưng khái niệm áp dụng cho cả bốn hệ (cách hiện thực mỗi hệ khác nhau, thư viện chỉ có sách cho SQL Server).⁹

• Heap. Bảng mới tạo là một heap: dòng được thêm theo thứ tự đến, không có thứ tự nào khác.²⁸
• Clustered index. Dữ liệu của bảng được sắp xếp và lưu theo khoá của index. Index này chính là bảng, nên mỗi bảng chỉ có một clustered index. Khoá thường chọn theo đường truy cập hay dùng nhất, thường là khoá chính.²⁹
• Non-clustered index. Là cấu trúc riêng, sắp xếp theo các cột được chọn, mỗi dòng trỏ về vị trí dữ liệu trong clustered index hoặc heap. Một bảng có thể có nhiều non-clustered index.³⁰ Mỗi index thêm vào làm chậm ghi và tốn bộ nhớ, nên đừng tạo vô tội vạ.³⁰
• Lookup. Khi query cần cột không có trong non-clustered index, engine phải theo con trỏ về dòng gốc. Đó là key lookup (với clustered index) hoặc RID lookup (với heap). Mỗi lookup tốn thêm một lần đọc trang dữ liệu.³¹
• Covering index. Nếu mọi cột query cần đều nằm trong index, engine không cần chạm vào trang dữ liệu. Có thể đạt được bằng cách thêm cột vào mệnh đề INCLUDE: các cột này nằm trong index nhưng không được sắp xếp.³²³¹

-- Query hay chạy
SELECT orderdate, custid FROM Sales.Orders WHERE custid = 42;

-- Covering index: custid để tìm, orderdate nằm sẵn trong index
CREATE NONCLUSTERED INDEX ix_orders_cust
  ON Sales.Orders (custid) INCLUDE (orderdate);

Sách khuyên clustered index hợp với tập kết quả lớn vì trang lá của nó chính là trang dữ liệu, không cần lookup; còn kết quả nhỏ thì non-clustered index là lựa chọn tốt.³¹ Cột có selectivity cao (nhiều giá trị khác nhau) mới đáng đánh index.³³ Với PostgreSQL, cùng ý tưởng nhưng khác tên: bảng không có clustered index tự duy trì, và INCLUDE được nhắc ở bài PostgreSQL là tính năng sản phẩm.¹⁰ Chi tiết thiết kế index ở Database index.

Quy trình tune một query chậm

Sách SQL Server Query Performance Tuning trình bày một vòng lặp rõ ràng. Nguyên tắc chung dùng được cho cả bốn hệ, còn công cụ thì khác nhau.²³

1. Tìm query đắt nhất. Ghi các query bằng Extended Events, nhóm theo số logical read. Query đọc nhiều nhất thường là thủ phạm gây áp lực bộ nhớ. DMV sys.dm_exec_query_stats cho thông tin nhanh về query đang chạy.³⁴ Đừng tune theo cảm giác "chậm": phải so với một mốc cụ thể.²³
2. Xem execution plan. Plan mô tả chiến lược xử lý của optimizer. Có plan ước lượng (estimated, không cần chạy query) và plan thực tế (actual, thêm số liệu lúc chạy).³⁵ Optimizer không tìm plan hoàn hảo mà tìm plan "đủ tốt", nên đôi khi plan kém.³⁵
3. Đếm reads. SET STATISTICS IO ON cho biết số lần đọc theo từng bảng, giúp tập trung vào bảng đọc nhiều nhất.³⁶
4. Kiểm tra statistics. Optimizer của SQL Server dựa trên chi phí và dùng statistics về phân bố dữ liệu để ước lượng số dòng và chọn index, kiểu join.³⁷ Statistics cũ dẫn tới plan sai. Hãy chắc rằng cơ chế tự cập nhật statistics đang hoạt động; khi cần, tự quản lý bằng tay.³⁷
5. Sửa rồi đo lại. Thêm hoặc đổi index (ví dụ covering index ở trên), viết lại query, rồi so lại số reads và thời gian.

Plan được cache và dùng lại để khỏi tối ưu lại mỗi lần;³⁸ stored procedure dùng lại plan theo mặc định.²² Công cụ tương đương ở PostgreSQL và MySQL (EXPLAIN, thống kê query chậm) là kiến thức sản phẩm, chưa có sách trong thư viện.¹⁰⁹

Chọn database cho team Việt Nam

Phần này là hướng dẫn của khoá học, dựa trên bối cảnh phổ biến ở thị trường Việt Nam, không phải số liệu từ sách.¹⁷

Hai quy tắc cho dự án mới: chọn thứ team vận hành được, và đừng tự ràng buộc vào tính năng độc quyền khi chưa cần. Nếu logic nghiệp vụ nằm hết trong stored procedure PL/SQL hay T-SQL, việc đổi sản phẩm sau này rất tốn kém.¹⁷ Trong phỏng vấn system design, người ta thường chấp nhận "PostgreSQL hoặc MySQL" miễn là bạn nêu được lý do và con số tải.

Một node chịu được bao nhiêu

Dù chọn hệ nào, câu hỏi sizing là như nhau. Mốc từ sách: Grokking dùng khoảng 1.000 QPS cho một server MySQL, 10.000 QPS cho key-value store.³⁹ Con số của khoá học (ước lượng, cần load test): một node 4 vCPU · 16 GB xử lý được vài nghìn truy vấn đơn giản có index mỗi giây khi dữ liệu nóng nằm trong RAM; query join nặng thì thấp hơn nhiều.⁴⁰

Ví dụ (giả định của khoá học): 500.000 DAU, mỗi người 40 request/ngày, 40% request chạm DB, mỗi request 2 query. Số query mỗi ngày là 500.000 × 40 × 40% × 2 = 16 triệu. Chia cho 86.400 giây ra khoảng 185 QPS trung bình; peak ×5 ≈ 925 QPS. Con số này nằm dưới mốc 1.000 QPS của Grokking, nên một primary cộng một replica đủ dùng.⁴¹ Hệ số peak ×5 là giả định. Xem Con số cần thuộc.

Cần nhắc: các con số trên áp dụng cho cả bốn hệ ở mức bậc độ lớn. Khác biệt thật giữa sản phẩm nằm ở cấu hình, schema, workload, nên phải đo.

Khi một node không còn đủ

Thứ tự xử lý từ rẻ tới đắt, giống với trong bài PostgreSQL:

1. Thêm index đúng, tune query bằng quy trình ở trên.
2. Cache đọc bằng Redis, xem Caching.
3. Thêm read replica. Grokking nêu mô hình primary-secondary: mọi ghi đi qua primary; replication bất đồng bộ có thể làm secondary trả dữ liệu cũ.¹⁸ Xem Scale đọc.
4. Nâng cấu hình máy (scale up). Building Microservices cũng nhận định với giới hạn ghi, máy to hơn thường là cách nhanh nhất.⁴²
5. Partition và tách bảng theo chức năng sang database riêng. Grokking mô tả sharding theo chiều dọc (tách bảng hoặc cột) và theo chiều ngang (tách dòng).⁴³
6. Shard ngang theo khoá như user_id, kèm nhược điểm: query xuyên shard, resharding.⁴³ Xem Sharding và Scale ghi.
7. Chuyển sang loại database khác cho đúng workload: Cassandra, DynamoDB cho ghi rất lớn, Elasticsearch cho tìm kiếm. Xem Chọn NoSQL.

Dấu hiệu cần sang bước 5 và 6: CPU primary cao kéo dài, working set không vừa RAM của máy lớn nhất bạn chấp nhận trả, hoặc backup và restore quá lâu (ngưỡng là giả định của khoá học).⁴⁰ Chi tiết các bước ở Database scale.

Nguồn trích dẫn

1. Microsoft SQL Server 2012 T-SQL Fundamentals (file .zip, tải về giải nén) · ch.1 Background to T-SQL Querying and Programming · mục "Set Theory", "Predicate Logic", "The Relational Model" (tr. 3–5). ↩

2. Microsoft SQL Server 2012 T-SQL Fundamentals (file .zip, tải về giải nén) · ch.9 Transactions and Concurrency · mục "Transactions" (định nghĩa atomicity, consistency, isolation, durability, tr. 298). ↩ ↩²

3. Grokking Modern System Design · ch.9 Databases · mục "Relational databases" (ACID và các bất thường được giấu sau transaction abort). ↩

4. Microsoft SQL Server 2012 T-SQL Fundamentals (file .zip, tải về giải nén) · ch.1 · mục "SQL" (SQL là ngôn ngữ chuẩn ANSI/ISO, khai báo, tr. 3). ↩

5. Microsoft SQL Server 2012 T-SQL Fundamentals (file .zip, tải về giải nén) · ch.1 · mục "Defining Data Integrity" (declarative và procedural data integrity, primary key, unique, foreign key, check, default, tr. 22). ↩

6. Grokking Modern System Design · ch.9 Databases · mục "Why relational databases?" (reduced redundancy, concurrency) và "Drawback: Impedance mismatch". ↩ ↩²

7. Seven Databases in Seven Weeks (file .zip, tải về giải nén) · ch.2 PostgreSQL · Day 1, mục về join giữa các bảng (inner join, outer join; Day 1). ↩

8. Chưa có nguồn trong thư viện: thư viện không có sách về Oracle Database; các ô về Oracle chỉ nêu khái niệm phổ biến, không có phiên bản, giá hay benchmark. ↩ ↩² ↩³ ↩⁴ ↩⁵ ↩⁶ ↩⁷ ↩⁸ ↩⁹

9. Chưa có nguồn trong thư viện: thư viện không có sách về MySQL hoặc MariaDB (ngoài con số 1.000 QPS của Grokking); các ô về MySQL/MariaDB chỉ nêu khái niệm cơ bản, không có phiên bản, giá hay benchmark. ↩ ↩² ↩³ ↩⁴ ↩⁵ ↩⁶ ↩⁷ ↩⁸ ↩⁹ ↩¹⁰ ↩¹¹

10. Chưa có nguồn trong thư viện: thư viện không có sách riêng về nội bộ PostgreSQL (JSONB, MVCC, mức isolation mặc định, INCLUDE, EXPLAIN, vận hành); chỉ nêu khái niệm cơ bản, không có số liệu hay phiên bản. ↩ ↩² ↩³ ↩⁴ ↩⁵ ↩⁶

11. SQL Server 2012 Query Performance Tuning (file .zip, tải về giải nén) · Introduction (một số công cụ chỉ có ở Enterprise Edition, được ghi chú trong sách). ↩

12. Seven Databases in Seven Weeks (file .zip, tải về giải nén) · ch.2 PostgreSQL · Day 1 (gói contrib) và Day 3, mục về fuzzy matching bằng trigram và full-text search. ↩ ↩²

13. SQL Server 2012 Query Performance Tuning (file .zip, tải về giải nén) · ch.3 SQL Query Performance Analysis · mục "Execution Plans" và phần tóm tắt (Extended Events, DMV sys.dm_exec_query_stats, Management Studio). ↩

14. Beginning SQL Server 2012 for Developers (file .zip, tải về giải nén) · ch.1 Overview and Installation · mục chọn thành phần cài đặt (Database Engine Services, Replication, Full-Text Search, Data Quality Services). ↩

15. Seven Databases in Seven Weeks (file .zip, tải về giải nén) · ch.2 PostgreSQL · phần mở đầu (PostgreSQL tuân thủ ANSI SQL cao, ACID, stored procedure, plug-in). ↩

16. Microsoft SQL Server 2012 T-SQL Fundamentals (file .zip, tải về giải nén) · ch.9 Transactions and Concurrency · mục "Isolation Levels" (bốn mức dựa trên khoá, hai mức dựa trên row versioning, mặc định của từng bản, tr. 309–310). ↩ ↩² ↩³

17. Hướng dẫn của khoá học (course guidance), tổng hợp từ bối cảnh thị trường phổ biến. Đây là nhận định, không phải số liệu từ sách; cần đối chiếu với tình hình công ty và nhân sự thực tế. ↩ ↩² ↩³ ↩⁴ ↩⁵ ↩⁶ ↩⁷ ↩⁸ ↩⁹ ↩¹⁰ ↩¹¹

18. Grokking Modern System Design · ch.9 Databases · mục "Replication", "Synchronous versus asynchronous replication", "Single leader or primary-secondary replication". ↩ ↩²

19. Beginning SQL Server 2012 for Developers (file .zip, tải về giải nén) · ch.1 Overview and Installation · mục "SQL Server Replication" trong danh sách thành phần. ↩

20. Chưa có nguồn trong thư viện: các sách SQL Server trong thư viện là bản 2012, không mô tả chi tiết cơ chế HA hiện đại (AlwaysOn, cluster) hay kiểu JSON; phần này chỉ nêu khái niệm. ↩ ↩²

21. Seven Databases in Seven Weeks (file .zip, tải về giải nén) · ch.2 PostgreSQL · Day 2, mục "Stored Procedures" (PL/pgSQL và các ngôn ngữ khác, tr. 28). ↩ ↩²

22. Microsoft SQL Server 2012 T-SQL Fundamentals (file .zip, tải về giải nén) · ch.10 Programmable Objects · mục "Stored Procedures" (encapsulate logic, kiểm soát bảo mật, tái dùng plan, giảm traffic mạng, tr. 364–365). ↩ ↩² ↩³

23. SQL Server 2012 Query Performance Tuning (file .zip, tải về giải nén) · Introduction (phương pháp đo và tune theo hệ thống, so sánh với một mốc cụ thể). ↩ ↩² ↩³

24. Microsoft SQL Server 2012 T-SQL Fundamentals (file .zip, tải về giải nén) · ch.9 · mục "Transactions", đoạn Isolation (mô hình dựa trên khoá và mô hình dựa trên row versioning, tr. 298) và mục "Locks and Blocking". ↩ ↩²

25. Microsoft SQL Server 2012 T-SQL Fundamentals (file .zip, tải về giải nén) · ch.9 · mục "Summary of Isolation Levels", Table 9-3. ↩

26. Microsoft SQL Server 2012 T-SQL Fundamentals (file .zip, tải về giải nén) · ch.9 · mục "Isolation Levels Based on Row Versioning", "The SNAPSHOT Isolation Level" và "The READ COMMITTED SNAPSHOT Isolation Level" (tr. 316–321). ↩ ↩² ↩³

27. Microsoft SQL Server 2012 T-SQL Fundamentals (file .zip, tải về giải nén) · ch.9 · mục "The SNAPSHOT Isolation Level" (phát hiện update conflict, khác với REPEATABLE READ và SERIALIZABLE gây deadlock). ↩

28. Expert Performance Indexing for SQL Server 2012 (file .zip, tải về giải nén) · ch.1 Index Fundamentals · mục "Heap Tables" (tr. 1–2). ↩

29. Expert Performance Indexing for SQL Server 2012 (file .zip, tải về giải nén) · ch.1 Index Fundamentals · mục "Clustered Indexes" (tr. 2). ↩

30. Expert Performance Indexing for SQL Server 2012 (file .zip, tải về giải nén) · ch.1 Index Fundamentals · mục "Nonclustered Indexes" (tr. 3–4; nhiều non-clustered index trên một bảng). ↩ ↩²

31. SQL Server 2012 Query Performance Tuning (file .zip, tải về giải nén) · ch.4 Index Analysis · mục "Defining the Bookmark Lookup" (key lookup, RID lookup, covering index). ↩ ↩² ↩³

32. Expert Performance Indexing for SQL Server 2012 (file .zip, tải về giải nén) · ch.1 Index Fundamentals · mục "Included Columns" (tr. 6). ↩

33. SQL Server 2012 Query Performance Tuning (file .zip, tải về giải nén) · ch.4 Index Analysis · mục về selectivity của cột khi chọn cột để đánh index. ↩

34. SQL Server 2012 Query Performance Tuning (file .zip, tải về giải nén) · ch.2 System Performance Analysis · mục "Optimizing Application Workload" (tr. 27) và ch.3, phần tóm tắt (Extended Events, sys.dm_exec_query_stats). ↩

35. SQL Server 2012 Query Performance Tuning (file .zip, tải về giải nén) · ch.3 SQL Query Performance Analysis · mục "Execution Plans" (estimated plan, actual plan, tr. 79). ↩ ↩²

36. SQL Server 2012 Query Performance Tuning (file .zip, tải về giải nén) · ch.3 SQL Query Performance Analysis · mục "Statistics IO". ↩

37. SQL Server 2012 Query Performance Tuning (file .zip, tải về giải nén) · ch.7 Statistics Analysis · mục "The Role of Statistics in Query Optimization" (tr. 175). ↩ ↩²

38. SQL Server 2012 Query Performance Tuning (file .zip, tải về giải nén) · ch.9 Execution Plan Cache Analysis · mục "Execution Plan Generation" và "Execution Plan Caching" (tr. 241). ↩

39. Grokking Modern System Design · ch.5 Back-of-the-envelope Calculations · mục "Standard numbers to remember" (bảng Important Rates). ↩ ↩² ↩³

40. Quy tắc ước lượng của khoá học · Chọn server và PostgreSQL (bảng sizing; ước lượng, cần load test). ↩ ↩² ↩³ ↩⁴ ↩⁵

41. Grokking Modern System Design · ch.5 Back-of-the-envelope Calculations · mục "Requests estimation" và "Number of servers required". ↩

42. Building Microservices (file .zip, tải về giải nén) · ch.11 Microservices at Scale · mục "Scaling for Writes" (tr. 223). ↩

43. Grokking Modern System Design · ch.9 Databases · mục "Sharding", "Vertical sharding" và "Horizontal sharding", "Advantages" và "Disadvantages". ↩ ↩²