Dịch vụ doanh nghiệp
Khóa học
Nguyên lý 3+2 tối ưu database gồm ba nguyên lý page, cache, cost và hai cơ chế read, write. Nắm được năm thứ này, anh em học Oracle, PostgreSQL, SQL Server hay MySQL đều thấy chúng chỉ là năm miếng ghép đó xếp khác nhau. Đây là thứ tôi mất nhiều năm mới ngộ ra, sau khi đọc miên man đủ loại tài liệu mà vào dự án vẫn không biết bắt đầu từ đâu.
Video gốc dài 1 tiếng 30 phút. Bài này dựng lại nguyên lý 3+2 thành khung áp được ngay, kèm ảnh cắt từ chính các demo trong video và dẫn nguồn tài liệu chính chủ của bốn hãng để anh em tự kiểm chứng.
Nguyên lý 3+2 gồm ba nguyên lý và hai cơ chế. Ba nguyên lý là page (đơn vị làm việc nhỏ nhất), cache (page được giữ trên bộ nhớ), cost (chi phí mà database ước lượng cho từng cách lấy dữ liệu). Hai cơ chế là read và write, tức hai đường duy nhất mà mọi câu lệnh phải đi qua.
Đây là bảng anh em nên chụp lại mang về.
| Thành phần | Tên | Câu hỏi phải trả lời | Chỗ đọc ra số thật |
|---|---|---|---|
| Nguyên lý 1 | Page hoặc Block | Câu lệnh này chạm vào bao nhiêu page? | Số block đọc trong execution plan, dung lượng bảng |
| Nguyên lý 2 | Cache | Page cần dùng đã nằm trên bộ nhớ chưa? | Tỉ lệ logical read so với physical read |
| Nguyên lý 3 | Cost | Database chọn đường nào, và vì sao chọn đường đó? | Cột cost trong execution plan |
| Cơ chế 1 | Read | Tìm page, đưa page lên bộ nhớ, trả kết quả | Bước access trong plan |
| Cơ chế 2 | Write | Đưa page lên bộ nhớ, sửa trên bộ nhớ, ghi xuống đĩa | Redo, WAL, log ghi |
Thứ tự áp dụng luôn là 1 rồi 2 rồi 3. Đo số page trước. Xem page có nằm trên bộ nhớ không. Rồi mới hỏi vì sao optimizer chọn đường đó. Làm ngược thứ tự là ngồi đoán.
Cả bài này chỉ là năm ô trong tấm hình trên. Anh em nhìn tam giác đó là nhớ được cả khung.
Vì lối cũ dạy anh em nghĩ theo bản ghi, còn database làm việc theo page. Hai đơn vị đo khác nhau thì mọi kết luận rút ra đều lệch.
Trường đại học dạy tôi rằng thêm điều kiện WHERE là lọc bớt dữ liệu, lọc bớt thì nhanh lên. Tôi tin điều đó nhiều năm. Sai. Xóa bản ghi cho bảng nhẹ bớt cũng sai luôn. Cả hai cách đó đều không đụng gì tới số page mà database phải đọc.
Năm 2013 tôi vào một dự án core banking, đúng dịp chốt sổ cuối năm. Tiến trình tắc ở một bảng chỉ vài nghìn bản ghi. Cả mấy phòng ban loay hoay không ra. Cuối cùng họ tạo lại đúng bảng đó, tiến trình chạy ngon ngay. Lúc ấy công lực tôi chưa tới, tôi không giải thích nổi vì sao.
Vì database đọc ghi xuống đĩa theo cả page, không bao giờ theo từng dòng. Anh em hình dung dữ liệu của mình giống một quyển sổ toàn tờ A4. Mỗi bản ghi là một dòng chữ trên tờ A4. Muốn lấy một dòng, anh em không xé được đúng dòng đó ra, anh em phải cầm nguyên tờ A4 lên.
Mặc định một tờ A4 đó là 8 KB. Con số này không phải tôi bịa, nó nằm trong tài liệu của cả bốn hãng.
| Database | Kích thước page mặc định | Đổi được không |
|---|---|---|
| Oracle | 8192 byte (DB_BLOCK_SIZE) | Đổi được, dải 2048 tới 32768, đặt lúc tạo database và không sửa lại |
| SQL Server | 8 KiB | Không, hãng ghi cứng |
| PostgreSQL | 8 kB | Đổi được nhưng phải biên dịch lại server |
| MySQL InnoDB | 16 KB (innodb_page_size) | Đổi được, đặt lúc khởi tạo instance |
Hệ quả của nguyên lý này thay đổi hẳn cách anh em nhìn một bảng. Bảng ít bản ghi không có nghĩa là bảng nhanh. Cái quyết định là bảng đó nằm trên bao nhiêu tờ A4.
Vì bảng bị phân mảnh. Anh em ghi dữ liệu đầy vào 300 tờ A4, rồi chạy DELETE xóa gần hết. Số bản ghi tụt xuống còn vài nghìn, nhưng 300 tờ A4 kia vẫn còn nguyên đó, chỉ là bên trong rỗng. Database vẫn phải quét đủ 300 tờ.
Đây chính là lý do tạo lại bảng thì tiến trình chạy ngon. Tạo lại bảng là dồn dữ liệu về đúng số tờ giấy cần thiết.
Phép kiểm rất nhanh: bảng ít bản ghi mà chiếm 10 GB thì biết ngay là phân mảnh. Chuyện này ở PostgreSQL có tên riêng và có cơ chế dọn riêng, tôi có phân tích kỹ trong bài Vacuum trong PostgreSQL ảnh hưởng tới hiệu năng thế nào. Trường hợp bảng 0 bản ghi mà câu lệnh vẫn chậm thì cùng một gốc, xem thêm Vì sao có Index mà câu SQL vẫn chậm.
Một tờ A4 chứa lẫn bản ghi của nhiều bảng khác nhau, chứ không phải hết bảng này mới sang bảng kia. Anh em cứ hình dung nó hổ lốn như thế cho đúng thực tế.
Cache lưu page, không lưu bản ghi, cũng không lưu kết quả tính toán. Đây là chỗ tôi từng hiểu sai hoàn toàn.
Trước đây tôi tưởng tìm user có ID bằng 100 thì hệ thống cache đúng bản ghi ID 100 lên bộ nhớ. Không phải. Đơn vị nhỏ nhất là page, nên muốn cache thì nó phải cache cả tờ A4 chứa bản ghi đó. Cache thừa ra rất nhiều. Với hệ thống nhỏ chẳng thấy gì, với hệ thống lớn thì lượng cache thừa đó ăn hết bộ nhớ.
Từ đó suy ra một hệ quả rất thực tế: bản ghi nào hay đi cùng nhau thì nên nằm cùng một page. Nằm rời rạc ở n chỗ là phải kéo n tờ A4 lên bộ nhớ cho một câu lệnh.
Vì kết quả sắp xếp không phải là page nên database không cache nó được. Lần một chạy select * from users order by downvotes, hệ thống cache được các block của bảng users. Nhưng bước sắp xếp phải làm lại từ đầu cho mọi phiên chạy sau. Không mất I/O nữa, nhưng vẫn mất CPU.
Oracle là ngoại lệ. Nó có server result cache, cache thẳng kết quả câu lệnh chứ không dừng ở mức block. Bật bằng hint.
select /*+ RESULT_CACHE */ * from users
order by downvotesTrong video tôi chạy thử: lần đầu mất khoảng 2 giây, chạy lại lần hai thì ra kết quả gần như tức thì.
Nhưng đây là bí kíp có điều kiện, và tôi phải nói rõ điều kiện. Oracle ghi trong tài liệu: kết quả đã cache bị vô hiệu ngay khi bất kỳ đối tượng dùng để dựng ra nó bị thay đổi. Nghĩa là gì? Nghĩa là chỉ cần một câu UPDATE chạm vào bảng, toàn bộ kết quả cache đó tính lại từ đầu. Áp cái này vào hệ thống OLTP dữ liệu nhảy liên tục thì tỉ lệ toạch rất cao. Tôi đã đi ứng cứu vài nơi áp bí kíp mà không đọc kỹ hướng dẫn sử dụng. Chỉ dùng cho hệ thống dữ liệu tĩnh.
SQL Server không có cơ chế tương đương. Anh em muốn dùng thì đọc tài liệu của đúng hãng mình đang chạy, đừng suy từ hãng này sang hãng kia.
Không. Bộ nhớ hữu hạn, giữ mãi thì chết. Mọi database đều có giải thuật đẩy page lâu không dùng ra khỏi bộ nhớ và ưu tiên giữ page hay dùng. MySQL InnoDB ghi rõ trong tài liệu là buffer pool quản theo một biến thể của LRU, chia thành hai danh sách con, page mới nạp vào giữa danh sách chứ không vào thẳng đầu.
Khi dữ liệu vượt quá bộ nhớ, hệ thống buộc phải đọc thẳng từ đĩa. Đó là physical read, và đó là lúc hiệu năng tụt hẳn. Nên khi vào một dự án, việc tôi làm sớm nhất là thống kê tỉ lệ giữa logical read và physical read của database đó.
Cost là con số ước lượng chi phí mà database gán cho từng cách lấy dữ liệu, rồi nó chọn cách có cost nhỏ nhất. Không có luật nào kiểu cứ index là ngon hơn quét bảng. Chỉ có cost.
Oracle ghi thẳng trong SQL Tuning Guide: optimizer gán cost cho từng phương án, cost đo bằng đơn vị công gồm I/O đĩa, CPU và bộ nhớ, rồi chọn phương án cost thấp nhất. Chỗ này đúng với mọi database dùng cost-based optimizer, khác nhau chỉ là công thức.
Và cost tính chủ yếu dựa vào hai nguyên lý trên: số page phải quét, và dữ liệu có sẵn trên bộ nhớ chưa. Ba cạnh của tam giác dính vào nhau là ở đây.
Không, nếu chiến lược thực thi không đổi. Đây là demo thẳng thắn nhất trong video.
Tôi chạy hai câu trên cùng một bảng users có 2.465.713 bản ghi. Câu một lấy tất cả. Câu hai thêm điều kiện where downvotes=0, số bản ghi trả về ít hơn hẳn. Chiến lược thực thi của hai câu giống hệt nhau, cùng là TABLE ACCESS FULL. Về bản chất chúng chạy chẳng khác gì nhau.
Tôi dựng lại đúng bảng đó trên SQL Server để anh em khỏi nghĩ là lỗi riêng của Oracle. Kết quả ra Clustered Index Scan trên PK_Users_Id. Nhìn không thấy chữ table nào, nhưng ý nghĩa y hệt.
Vì sao lại thế? Vì bảng users có primary key trên cột ID, nên SQL Server dựng cột đó thành clustered index. Microsoft ghi rất rõ trong tài liệu thiết kế index: ở clustered index, các nút lá chính là data page của bảng. Nghĩa là clustered index đó chính là cái bảng. Quét nó tức là quét toàn bộ bảng.
Muốn nhanh thì phải đổi được chiến lược thực thi sang một chiến lược quét ít block hơn. Đó mới là chỗ index, partition, composite index sinh ra để giải. Câu chuyện này tôi có phân tích riêng ở bài Tối ưu SQL nên bắt đầu từ đâu.
Khi số page phải quét đằng nào cũng gần hết bảng. Lúc đó nhảy qua nhảy lại theo index còn đắt hơn quét thẳng.
Tôi hay ví thế này. Quét full bảng giống mang một chiếc ô tô tải đi chở đồ, một chuyến chở hết. Quét theo index giống chở bằng xe máy, mỗi chuyến được một người. Có 32 người mà đi xe máy thì phải chạy 32 lượt. Ô tô tải một lượt là xong.
Trường hợp cụ thể: bảng nhỏ nằm gọn trong một page. Quét một tờ A4 là xong, cần gì index. Hoặc bảng có quá nhiều bản ghi thỏa mãn điều kiện, đến mức các bản ghi đó rải trên 90% số block. Lúc này cost của đường index lớn hơn hẳn cost của full scan, hệ thống tự chọn full scan.
Nên trong mọi trường hợp hãy xem cost thực thi, đừng lúc nào cũng index. Cứ index vô tội vạ là teo. Chuyện cùng một câu lệnh mà lúc nhanh lúc chậm cũng nằm ở đây, tôi có chia sẻ trong bài Tối ưu SQL: cùng một câu lệnh nhưng lúc nhanh, lúc chậm.
Không. Đây là hiểu lầm kinh điển, và tôi demo thẳng trong video.
Câu where downvotes > 0 quét full, cost ra 12.793. Câu hai thêm fetch first 10 row only, plan vẫn ghi full table scan y như câu trên, nhưng cost chỉ còn 2. Lệch nhau hơn sáu nghìn lần.
Bản chất của full table scan là hệ thống nhảy vào block đầu tiên của bảng rồi quét tiếp. Nó quét cho tới khi đủ dữ liệu người dùng yêu cầu thì dừng. Khoảng 90% trường hợp là quét hết, nhưng đó là vì câu lệnh không chặn gì cả, chứ không phải vì giải thuật bắt buộc phải quét hết.
Rất quan trọng, còn thứ tự cột trong mệnh đề WHERE thì không.
Viết WHERE a=1 AND b=2 AND c=3 hay đảo thành WHERE c=3 AND b=2 AND a=1 cho ra cùng một chiến lược thực thi, không khác gì nhau. Nhưng index trên (a, b, c) khác hoàn toàn index trên (a, c, b), và khác hoàn toàn index trên (b, c, a).
Lý do nằm ở nguyên lý 1. Composite index là một bảng con được sắp xếp theo đúng thứ tự cột anh em khai. Đổi thứ tự cột là đổi cách sắp xếp, đổi cách sắp xếp là đổi số block phải đọc. Anh em cứ hình dung nó thành bảng chữ rồi sẽ thấy ngay. Chi tiết chuyện này tôi có phân tích riêng ở bài Tầm quan trọng của thứ tự các cột trong Index PostgreSQL.
Còn kiểu đánh index mỗi cột một cái, tôi gọi là trường phái mổ cò. Tôi không khoái mổ cò lắm. Nó thường không đem lại hiệu quả, tôi không nói là không bao giờ, nhưng thường là không.
Hai cơ chế này chính là chỗ ba nguyên lý ghép lại thành một hình. Read có hai bước, write có ba bước, và cả hai đều bắt buộc đi qua bộ nhớ.
NGUYEN LY 3 + 2
[1] Page/Block : don vi lam viec nho nhat, mac dinh 8KB
[2] Cache : giu page tren bo nho, khong giu ket qua
[3] Cost : uoc luong chi phi, chon duong re nhat
READ
buoc 1 xac dinh page nao chua du lieu can tim
buoc 2 dua page do len bo nho
buoc 3 tra ket qua ra tu bo nho
WRITE
buoc 1 dua page chua ban ghi can sua len bo nho
buoc 2 sua du lieu ngay tren bo nho
buoc 3 ghi tu bo nho xuong dia
THU TU CHAN DOAN
1. cau lenh nay cham bao nhieu page
2. page do da nam tren bo nho chua
3. optimizer dang chon duong nao, cost bao nhieu
4. muon nhanh thi phai giam so page o buoc 1Nhìn vào ba bước của write là hiểu ngay vì sao các mô hình triển khai lại đẻ ra như hiện nay. Mô hình đơn giản nhất là một máy chủ, read và write dùng chung một vùng bộ nhớ. Hệ thống lớn lên thì đọc và ghi tranh nhau bộ nhớ, nên tách ra làm hai máy: máy primary lo ghi, máy standby đồng bộ về và lo đọc.
Còn tại sao ngân hàng và chứng khoán hay chọn Oracle RAC? Vì cả read lẫn write đều phải đi qua bước đưa page lên bộ nhớ, nên ai dùng được bộ nhớ hiệu quả nhất thì ăn. RAC cho hai máy chủ cùng nạp một block lên bộ nhớ và cùng sửa mà dữ liệu không hỏng. Oracle gọi cơ chế này là Cache Fusion, tài liệu mô tả nó là cơ chế đồng bộ cache không qua đĩa, chuyển bản sao block thẳng từ bộ nhớ máy này sang bộ nhớ máy kia, ghép các vùng SGA của cả cụm lại thành một khối logic. Chết một node thì hệ thống chính vẫn sống.
Anh em thấy không? Chỉ từ ba nguyên lý và hai cơ chế mà giải thích được luôn cả chuyện kiến trúc hạ tầng. Đó là lý do tôi gọi nó là khung, không gọi là mẹo.
Đây là phần tôi bắt buộc phải nói, vì khung nào cũng có chỗ nó không với tới.
Nó không chữa được thiết kế dữ liệu sai. Bảng dựng sai chuẩn hóa, kiểu dữ liệu chọn ẩu, khóa đặt nhầm chỗ thì đo bao nhiêu page cũng không cứu nổi. Khung này giúp anh em thấy bệnh, không giúp anh em thiết kế lại từ đầu.
Nó không chữa được vấn đề tranh chấp. Lock, deadlock, hot block, transaction giữ quá lâu là họ hàng khác. Cùng một câu lệnh, cùng một số page, mà lúc chạy 1 giây lúc chạy 40 giây thì phần lớn là tranh chấp chứ không phải cost.
Nó không thay được số liệu thật của hệ thống anh em. Mọi con số trong bài này là số trên môi trường demo của tôi. Bảng của anh em có bao nhiêu page, tỉ lệ physical read bao nhiêu, cost bao nhiêu, anh em phải tự đo trên chính hệ thống mình. Đừng tin tôi, hãy tự kiểm chứng.
Ba nguyên lý không cân nhau ở mọi loại database. Với database phi quan hệ, cách chia đơn vị lưu trữ và cách optimizer ra quyết định khác hẳn. Khung vẫn dùng được để hỏi đúng câu hỏi, nhưng câu trả lời khác, tôi có chia sẻ trong bài MongoDB lúc nhanh lúc chậm.
Và nó không thay được việc đọc tài liệu của đúng hãng anh em đang chạy. Result cache có ở Oracle, không có ở SQL Server. Page mặc định 8 KB ở ba hãng, 16 KB ở MySQL InnoDB. Khung chỉ ra chỗ cần tra, tài liệu hãng mới cho con số.
Không cần khóa học nào. Lấy đúng một câu lệnh đang chậm trong hệ thống của mình rồi đi hết bốn bước sau.
Ngày 1 và 2, đo nguyên lý 1. Lấy dung lượng thật của bảng và index liên quan, đổi ra số page. Bảng ít bản ghi mà dung lượng lớn thì ghi lại, đó là phân mảnh.
Ngày 3, đo nguyên lý 2. Lấy tỉ lệ logical read so với physical read. Trên Oracle thì xem báo cáo AWR, các hãng khác đều có báo cáo hiệu năng tương đương.
Ngày 4 và 5, đo nguyên lý 3. Lấy execution plan, ghi lại cost. Rồi thêm một điều kiện lọc bất kỳ và lấy plan lần nữa. Plan không đổi thì anh em vừa tự chứng minh được rằng thêm WHERE không phải cách tối ưu.
Ngày 6 và 7, thử đúng một thay đổi nhắm vào việc giảm số page. Tạo lại bảng nếu nó phân mảnh, hoặc dựng composite index đúng thứ tự cột. Đo lại cost, ghi con số trước và sau.
Làm đủ bốn bước này thì anh em đã đi trước rất nhiều người có 5 năm kinh nghiệm. Cứ làm đi, sai lại sửa.
Nguyên lý 3+2 áp cho database nào? Áp cho mọi database quan hệ dùng cost-based optimizer, gồm Oracle, SQL Server, PostgreSQL, MySQL, MariaDB. Tên gọi khác nhau, Oracle gọi block, SQL Server gọi page, nhưng bản chất là một.
Một page mặc định bao nhiêu KB? Oracle, SQL Server và PostgreSQL mặc định 8 KB. MySQL InnoDB mặc định 16 KB. Oracle đổi được trong dải 2 KB tới 32 KB nhưng phải đặt lúc tạo database, SQL Server thì không đổi được.
Có nên đổi page size lên 16 KB hay 32 KB không? Chỉ khi anh em có lý do cụ thể và đo được, ví dụ biết chắc dữ liệu nằm liên tục cạnh nhau nên page to giúp giảm số lần nạp. Không có lý do rõ ràng thì để mặc định. Đây là tham số đặt một lần rồi khó quay lại.
Cache lưu bản ghi hay lưu page? Lưu page. Muốn cache một bản ghi thì hệ thống phải kéo nguyên page chứa nó lên bộ nhớ, kèm cả những bản ghi khác nằm cùng page đó. Riêng Oracle có thêm result cache lưu được kết quả câu lệnh.
Index có luôn nhanh hơn quét full bảng không? Không. Bảng nhỏ nằm trong một page, hoặc điều kiện lọc thỏa mãn quá nhiều bản ghi rải khắp các block, thì full scan rẻ hơn. Database tự so cost rồi chọn, anh em chỉ cần đọc plan để biết nó chọn gì.
Đổi thứ tự cột trong WHERE có đổi hiệu năng không? Không. Thứ tự trong WHERE không ảnh hưởng chiến lược thực thi. Thứ tự cột trong index thì ảnh hưởng rất lớn, vì nó đổi cách sắp xếp của bảng con index và đổi số block phải đọc.
Ba nguyên lý và hai cơ chế. Page, cache, cost, cùng read và write. Đo theo đúng thứ tự đó thì mọi bài toán tối ưu đều hiện ra chỗ cần chạm.
Cái làm câu lệnh nhanh không phải điều kiện WHERE, không phải xóa bớt bản ghi. Là số page database phải đọc. Giảm được số đó thì nhanh, không giảm được thì mọi thứ khác là vô nghĩa.
Đừng tin tôi, hãy tự kiểm chứng trên hệ thống của anh em. Lấy một câu lệnh, mở execution plan ra, đọc cost. Tự tay làm một lần là nhớ cả đời. Cứ làm đi, có chết đâu.
Toàn bộ là tài liệu chính chủ của hãng, không dẫn bài tóm tắt trung gian.
Oracle, DB_BLOCK_SIZE, mặc định 8192 byte, dải 2048 tới 32768, đặt lúc tạo database.
Oracle, Query Optimizer Concepts, cost đo bằng I/O đĩa, CPU và bộ nhớ, optimizer chọn plan có cost thấp nhất.
Oracle, Tuning the Result Cache, hint RESULT_CACHE và luật vô hiệu kết quả khi đối tượng phụ thuộc bị thay đổi.
Oracle, Introduction to Oracle RAC, Cache Fusion đồng bộ buffer cache giữa các instance.
Microsoft, Page and extent architecture guide, page cố định 8 KiB, I/O thực hiện ở mức page.
Microsoft, Index architecture and design guide, nút lá của clustered index chính là data page của bảng.
PostgreSQL, Database Page Layout, page cố định thường 8 kB, đổi được khi biên dịch server.
MySQL, InnoDB Buffer Pool, buffer pool quản page theo biến thể của giải thuật LRU.