Dịch vụ doanh nghiệp
Khóa học
PostgreSQL vs MySQL, chọn ông nào? Đây là câu anh em tra Google nhiều nhất trong mảng cơ sở dữ liệu. Phần lớn câu trả lời ngoài kia là bảng liệt kê tính năng chép từ tài liệu, và bảng đó không giúp anh em quyết được gì. Bài này đi theo đúng cách tôi lập luận trong video: chỉ có hai câu hỏi thật sự quyết định, cả hai đều nằm ở kiến trúc bên trong.
Video gốc 32 phút, tôi vẽ tay toàn bộ cơ chế trên bảng. Bài viết giữ nguyên mạch đó, bổ sung phần đối chiếu tài liệu gốc và mục "Giới hạn thật".
Nhìn vào đúng hai thứ. Thứ nhất, khi người dùng đồng thời tăng cao, rất nhiều giao dịch cùng chọc vào một bảng, thì ông nào chịu tải tốt hơn. Thứ hai, khi hệ thống chạy báo cáo nặng, câu lệnh SQL dài hàng trang giấy, join tùm lum, thì ông nào ra kết quả nhanh hơn.
Dữ liệu ít, câu lệnh đơn giản thì không chấp, chọn ông nào cũng được. Còn hai câu hỏi trên thì gốc rễ nằm ở hai chỗ khác hẳn nhau trong kiến trúc:
| Câu hỏi | Gốc rễ nằm ở đâu | Ai thắng |
|---|---|---|
| Nhiều người ghi cùng lúc, ông nào chịu được? | Cơ chế MVCC, tức cách lưu nhiều phiên bản của một dòng dữ liệu | MySQL |
| Câu lệnh phân tích phức tạp, ông nào nhanh hơn? | Bộ tối ưu, tức cách dựng chiến lược thực thi | PostgreSQL |
Hai dòng đó là toàn bộ bài viết này. Phần còn lại là tôi chứng minh vì sao.
MVCC là cơ chế cho phép nhiều phiên cùng đọc ghi một dòng dữ liệu mà không chặn nhau, bằng cách giữ đồng thời nhiều phiên bản của dòng đó. Đây là thứ quyết định hệ thống sống hay chết khi lượng truy cập đồng thời tăng lên.
Anh em hình dung cho dễ. Trong database có ông Huy, lương 1000 đô. Phiên thứ nhất gõ update đổi lương lên 1500 nhưng chưa commit. Cùng lúc đó phiên thứ hai gõ select, và nó phải nhìn thấy 1000. Còn phiên thứ nhất, chính nó vừa gõ update xong, phải nhìn thấy 1500.
Cùng một dòng, cùng một thời điểm, ông này ra 1500, ông kia ra 1000. Làm sao được như thế? Đó chính là việc của MVCC.
Tài liệu PostgreSQL gọi cái lợi lớn nhất của mô hình này là: đọc không bao giờ chặn ghi, ghi không bao giờ chặn đọc. Tài liệu MySQL nói y hệt về InnoDB.
Đến đây hai ông giống nhau. Nhưng cách làm ra kết quả đó thì khác nhau hoàn toàn. Vì khác nhau nên hiệu năng cũng khác nhau luôn.
PostgreSQL không sửa đè lên dòng cũ. Nó thêm hẳn một dòng mới vào một vị trí vật lý khác, rồi trỏ ngược về dòng cũ.
Tôi lấy đúng cái bảng nhân viên trong video. Ba bản ghi: ông Huy lương 1000 ở Hà Nội, ông An lương 50 ở Nam Định, ông Bình lương 800 ở Hà Nam. Cột ID là primary key. Điều anh em ít để ý là PostgreSQL còn tự gắn thêm cột ctid, mô tả vị trí vật lý của dòng trên đĩa. Tôi gọi ba vị trí đó là ô A, ô B, ô C.
CTID bên trái chính là vị trí vật lý. Góc phải: index cột name trỏ thẳng vào ô A, ô B, ô C. Nhớ kỹ chỗ này.Thực tế làm dự án thì bảng nào cũng đẻ thêm index phụ. Giả sử có index trên cột name và trên cột salary. Index name lưu An, Bình, Huy theo chiều tăng dần, mỗi tên trỏ tới ô A, B, C tương ứng. Index salary lưu 50, 800, 1000, cũng trỏ tới ô vật lý.
Bây giờ update lương ông Huy lên 1500. PostgreSQL làm gì? Nó không sửa ô A. Nó ghi một dòng hoàn toàn mới vào ô D, đủ cả ID là 1, tên Huy, lương 1500, địa chỉ Hà Nội. Rồi nó tạo một con trỏ để biết dòng ở ô D vốn là bản thay đổi của dòng ở ô A. Có con trỏ đó thì lúc rollback mới quay về được.
Vì trong PostgreSQL, mọi index đều trỏ thẳng tới vị trí vật lý của dòng. Dòng chuyển sang ô mới thì mọi index đều phải trỏ lại.
Đây là chỗ nhọ. Anh em nhìn kỹ nhá:
Chúng ta chỉ cập nhật mỗi cột salary, cột name không đụng tới một chữ. Vậy mà index của cột name vẫn bị cập nhật theo, chỉ vì giá trị Huy giờ phải trỏ vào một ô khác.
Đội kỹ thuật của Uber gọi hiện tượng này là write amplification, tức khuếch đại ghi. Trong bài viết giải thích vì sao họ chuyển từ Postgres sang MySQL, họ đếm ra một lần sửa năm sinh của một bản ghi kéo theo ít nhất bốn lần ghi vật lý. Và họ nói thẳng: bảng nào có một tá index thì một lần sửa cột chỉ nằm trong một index sẽ phải lan ra cả mười hai index. Đúng bài toán tôi vẽ trên bảng, chỉ khác là ở quy mô Uber.
Vacuum dọn ngay trên chính cái bảng đang phục vụ người dùng. Đó là khác biệt lớn nhất so với MySQL.
Những dòng cũ không ai còn cần thì phải có ai đó dọn đi. PostgreSQL giao việc đó cho tiến trình vacuum, chạy tự động và định kỳ. Tài liệu PostgreSQL ghi rõ hai điều đáng nhớ. Một là vacuum tạo ra lượng I/O đáng kể, làm các phiên đang chạy chậm đi. Hai là nó thường không trả dung lượng về cho hệ điều hành, chỉ đánh dấu chỗ trống để dùng lại.
Nghĩa là trước khi vacuum kịp chạy, hệ thống phải chứa nhiều hơn thực tế nó có. Bảng chỉ 5.000 bản ghi thật, nhưng update liên tục thì nó có thể phình lên 10.000 hay 15.000. Index cũng phình theo. Tôi có phân tích riêng cơ chế này trong bài Vacuum trong PostgreSQL ảnh hưởng tới hiệu năng thế nào.
MySQL sửa đè trực tiếp lên dòng cũ, còn giá trị cũ thì nó vứt sang một vùng riêng gọi là undo tablespace. Cùng cái bảng đó, cùng câu lệnh đổi 1000 thành 1500, nhưng tư duy của InnoDB khác hẳn: không nhét mọi phiên bản vào chung một bảng.
Ai gõ rollback thì nó lấy dữ liệu từ vùng undo ra khôi phục. Ai đang trong phiên update thì thấy 1500. Ai ở ngoài thì nó dẫn sang vùng undo, thấy 1000. Cùng một bài toán, giải bằng cách chia góc nhìn chứ không chất đống vào một chỗ.
Tài liệu MySQL mô tả đúng cơ chế này: mỗi dòng có thêm trường DB_ROLL_PTR trỏ vào bản ghi undo, và bản ghi undo thường nhỏ hơn chính dòng dữ liệu đã sửa.
Vì secondary index của InnoDB không trỏ vào vị trí vật lý. Nó trỏ vào giá trị primary key.
Đây là câu quan trọng nhất của cả bài. Index name của MySQL lưu An, Bình, Huy, nhưng ba mục đó không trỏ vào ô A, ô B, ô C. Chúng trỏ vào ID 2, ID 3, ID 1. Mà ID thì không đổi khi ta sửa lương. Nên index name không phải sờ vào, primary key cũng không. Cập nhật cột salary thì đúng một mình index salary phải đổi. Hết.
Tài liệu MySQL về clustered và secondary index viết đúng như vậy: mỗi bản ghi trong secondary index chứa các cột primary key của dòng, và InnoDB dùng giá trị đó để tìm dòng trong clustered index.
Cái giá phải trả là tra cứu qua secondary index phải đi qua hai cây index thay vì một. Đổi lại, ghi nhẹ hơn hẳn.
| Thứ | PostgreSQL | MySQL với InnoDB |
|---|---|---|
| Update một dòng | Ghi hẳn dòng mới ở vị trí vật lý khác | Sửa đè tại chỗ |
| Bản cũ nằm đâu | Nằm chung trong chính bảng dữ liệu | Nằm ở undo tablespace, tách riêng |
| Index trỏ vào gì | Vị trí vật lý của dòng, tức ctid | Giá trị primary key |
| Sửa một cột có index | Mọi index đều phải thêm mục mới | Chỉ index của cột đó phải đổi |
| Ai dọn rác | Tiến trình vacuum | Tiến trình purge |
| Dọn ở đâu | Ngay trên bảng đang phục vụ | Trên vùng undo, tách khỏi bảng chính |
| Ảnh hưởng lúc dọn | Có, vì chung chỗ với bảng chính | Nhẹ hơn, vì khác phân vùng |
Chốt lại. Hệ thống nào ghi rất nhiều, bảng lại đeo nhiều secondary index, thì PostgreSQL sẽ chậm hơn MySQL. Không phải vì ông nào dở hơn ông nào. Là vì kiến trúc hai ông vốn khác nhau từ gốc.
Sáu bước. Và chỉ có bước bốn với bước năm là ăn hiệu năng thật sự. Database nào cũng cùng một mạch này:
Hai bước đầu thì database nào cũng như nhau. Bước ba liên quan tới bộ nhớ đệm chiến lược, tôi có chia sẻ riêng trong bài Tối ưu SQL: cùng một câu lệnh nhưng lúc nhanh, lúc chậm.
Bước bốn và bước năm mới là nơi hai ông tách nhau ra. Anh em hình dung đi từ điểm A tới điểm B có n con đường. Có đường vòng, có đường tắc, có đường phí cao. Việc của bộ tối ưu là đánh giá hết chỗ đó rồi chọn con đường rẻ nhất. Đúng như tài liệu PostgreSQL viết: bộ tối ưu duyệt các phương án và chọn phương án được ước lượng là chạy nhanh nhất.
Đây cũng là lý do đôi khi anh em chỉ nâng phiên bản database thôi, chưa hề đổi loại, mà hiệu năng đã khác hẳn. Vì cái được nâng chính là bộ tối ưu này. Nâng MySQL từ 5.7 lên 8.0 là thấy ngay, tôi có phân tích riêng trong bài Hiệu năng MySQL 8: nâng cấp lớn trong Query Optimizer.
Vì bộ tối ưu của PostgreSQL dùng nhiều yếu tố đầu vào hơn để tính cost, và nó dám viết lại câu lệnh của anh em.
Nó biến subquery thành join khi thấy làm vậy rẻ hơn.
Giả sử anh em có câu subquery trên bảng customers với bảng orders. Logic viết ra là chạy câu bên trong trước, xong mới ra ngoài. MySQL làm rất đơn phương, đúng y như anh em viết. PostgreSQL thì không. Nó tự viết lại thành hash join, dù câu lệnh chẳng có chữ join nào.
Hash Join. Câu lệnh tôi gõ là subquery, không có chữ join nào. Bộ tối ưu tự dựng ra nó.Trong demo của tôi, cùng một câu lệnh, cùng cấu trúc bảng, cùng dữ liệu, chạy trên hai database:
| Database | Cách nó chọn | Actual time của node trên cùng |
|---|---|---|
| PostgreSQL | Tự chuyển thành hash join | 0,039 tới 0,041 ms |
| MySQL | Chạy tuần tự đúng thứ tự câu lệnh | 0,355 tới 0,37 ms |
Anh em đừng quan trọng hóa con số này. Dữ liệu demo rất nhỏ, một câu lệnh, một máy, không phải benchmark. Điều đáng nhớ là cơ chế: PostgreSQL dám chuyển câu lệnh sang kiểu join khác. Câu lệnh càng phức tạp thì bước chuyển đổi này càng cho hiệu quả rõ rệt.
Muốn tự nhìn thấy chuyện này trên hệ thống của mình thì dùng explain và explain analyze. Tôi có chia sẻ cách đọc trong bài Tối ưu MySQL với Explain và Explain Analyze.
PostgreSQL hỗ trợ đủ ba kiểu join từ lâu, còn MySQL mới có thêm hash join gần đây.
Tài liệu PostgreSQL liệt kê ba chiến lược join: nested loop join, merge join, hash join. Bộ tối ưu cân cả ba rồi chọn cái rẻ nhất. Phía MySQL, các phiên bản cũ chủ yếu chỉ chạy nested loop. Tài liệu MySQL ghi rõ mốc: từ 8.0.18 mới bắt đầu dùng hash join, tới 8.0.20 thì bỏ hẳn block nested loop.
Về index cũng vậy. PostgreSQL có B-tree, Hash, GiST, SP-GiST, GIN, BRIN. MySQL thì cú pháp CREATE INDEX chỉ cho USING BTREE hoặc USING HASH, kèm hai loại riêng là FULLTEXT và SPATIAL. Nhiều lựa chọn hơn nghĩa là bộ tối ưu có nhiều con đường hơn để cân. Anh em nào muốn hiểu vì sao có index rồi mà câu lệnh vẫn chậm thì đọc thêm bài Vì sao có Index mà câu SQL vẫn chậm và bài Tầm quan trọng của thứ tự các cột trong Index PostgreSQL.
| Hoàn cảnh hệ thống của anh em | Nên nghiêng về |
|---|---|
| Ghi nhiều, update liên tục, bảng đeo nhiều secondary index | MySQL |
| Người dùng đồng thời cực lớn, chủ yếu giao dịch ngắn | MySQL |
| Báo cáo nặng, câu lệnh nhiều join, nhiều subquery | PostgreSQL |
| Cần nhiều kiểu index đặc thù, tìm kiếm không gian | PostgreSQL |
| Câu lệnh đơn giản, dữ liệu nhỏ | Ông nào cũng được |
Chép bộ câu lệnh này về chạy luôn, nó trả lời đúng câu hỏi hệ thống của mình đang rơi vào bên nào.
-- [1] PostgreSQL: xem chiến lược thực thi thật của một câu lệnh
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;
-- [2] PostgreSQL: bao nhiêu phần trăm update phải sờ vào index
-- n_tup_hot_upd cang gan n_tup_upd thi cang do
SELECT relname, n_tup_upd, n_tup_hot_upd,
round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd,0), 1) AS ty_le_hot
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT 10;
-- [3] PostgreSQL: bang nao dang phinh vi ban cu chua duoc don
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
-- [4] PostgreSQL: dem so index dang deo tren mot bang
SELECT tablename, indexname FROM pg_indexes WHERE tablename = 'ten_bang';
-- [5] MySQL 8: xem chien luoc thuc thi that
EXPLAIN ANALYZE SELECT ... ;
-- [6] MySQL: undo co bi ton lai khong, so cang lon cang do
SELECT NAME, COUNT
FROM information_schema.INNODB_METRICS
WHERE NAME = 'trx_rseg_history_len';
-- [7] MySQL: dem so index dang deo tren mot bang
SELECT INDEX_NAME, COLUMN_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'ten_schema' AND TABLE_NAME = 'ten_bang';Chạy câu số 2 trước. Tỷ lệ HOT thấp mà số index lại cao, đó chính là hoàn cảnh PostgreSQL trả giá đắt nhất cho mỗi lần ghi.
Phần này quan trọng ngang cả bài. Đọc kỹ trước khi mang kết luận đi quyết định.
Video quay tháng 06/2024, hai ông đã đi tiếp. Câu "MySQL chỉ hỗ trợ nested loop" đúng với các phiên bản cũ. Từ 8.0.18 MySQL đã có hash join, từ 8.0.20 bỏ hẳn block nested loop. Chạy MySQL 8 bản mới thì khoảng cách ở câu lệnh phân tích hẹp lại nhiều.
PostgreSQL có một cửa thoát tên là HOT mà tôi chưa nói trong video. Tài liệu PostgreSQL về heap-only tuples ghi rõ: nếu câu update không đụng cột nào đang được index, và trang chứa dòng cũ còn đủ chỗ, thì PostgreSQL không cần thêm mục index nào. Nhưng để ý điều kiện đó. Trong ví dụ của tôi cột salary có index, nên HOT không cứu được.
Bài của Uber dựa trên PostgreSQL 9.2, viết năm 2016. Chính họ ghi chú điều đó. Đừng áp nguyên kết luận của họ vào một cụm PostgreSQL bản mới.
Hai con số 0,041 và 0,37 là demo, không phải benchmark. Đơn vị là mili giây, đọc thẳng từ màn hình, và là actual time của node trên cùng chứ không phải tổng thời gian chạy. Riêng PostgreSQL còn in Execution Time: 0.073 ms ở dòng dưới. Dữ liệu nhỏ, một lần chạy. Nó minh họa cơ chế chứ không đo năng lực hai hệ thống.
Toàn bộ phần MySQL trong bài giả định engine là InnoDB. Dùng engine khác thì mọi thứ về MVCC ở trên không còn đúng.
Hiệu năng chỉ là một chân trong ba chân. Đội quen ông nào, hệ sinh thái công cụ ra sao, chi phí vận hành thế nào, cả ba đều nặng ngang hiệu năng khi quyết định thật. Tôi có chia sẻ thêm trong bài Tối ưu SQL nên bắt đầu từ đâu.
Cứ làm đi. Sai lại sửa.
Không. PostgreSQL chậm hơn ở đúng một hoàn cảnh: ghi nhiều trên bảng đeo nhiều secondary index. Ở câu lệnh phân tích phức tạp thì ngược lại, nhờ bộ tối ưu tốt hơn. Hỏi ông nào nhanh hơn mà không nói hoàn cảnh thì không có câu trả lời.
Vì tải của họ nặng về ghi và bảng có rất nhiều index. Mỗi lần sửa một cột, PostgreSQL phải cập nhật toàn bộ index vì index trỏ vào vị trí vật lý của dòng. Đây là bài toán riêng của họ, không phải kết luận chung.
Khác ở chỗ dọn. Vacuum dọn ngay trên bảng đang phục vụ người dùng, nên nó ảnh hưởng tới các phiên đang làm việc với bảng đó. Purge của MySQL dọn trên vùng undo, tách khỏi bảng chính, nên nhẹ hơn.
Có khả năng, lý do là bộ tối ưu được nâng chứ không phải máy khỏe lên. MySQL 8 có hash join từ 8.0.18 và cách tính cost cũng khác. Đừng coi là phép màu, vẫn phải đo lại từng câu lệnh sau khi nâng.
Chữa được, nhưng chỉ khi câu update không đụng cột nào đang được index và trang còn đủ chỗ. Hệ thống hay update đúng cột có index thì HOT không giúp được. Kiểm bằng cột n_tup_hot_upd trong pg_stat_user_tables.
Ông nào cũng được. Câu lệnh đơn giản, dữ liệu nhỏ thì hai ông cho kết quả gần như nhau. Lúc đó chọn theo thứ đội quen tay, theo hệ sinh thái công cụ và chi phí vận hành.
Chuyện chọn PostgreSQL hay MySQL không nằm ở bảng so sánh tính năng. Nó nằm ở hai chỗ trong kiến trúc: cách hai ông làm MVCC, và cách bộ tối ưu dựng chiến lược thực thi.
Ghi nhiều, nhiều index, người dùng đồng thời lớn thì nghiêng về MySQL. Báo cáo nặng, câu lệnh phức tạp thì nghiêng về PostgreSQL. Hiểu cái gốc rồi thì lần sau gặp một loại database mới, anh em cũng tự đánh giá được, không phải đi hỏi ai.
Anh em muốn đi sâu hơn vào tối ưu cơ sở dữ liệu và làm qua nhiều bài toán thật thì tham gia chương trình của tôi. Tùy anh em.