Dịch vụ doanh nghiệp
Khóa học
Kiến trúc MySQL chỉ có hai khối: một khối nằm trên bộ nhớ, một khối nằm trên ổ cứng. Anh em nắm được hai khối đó thì mọi chuyện nhanh chậm về sau đều giải thích được, không phải đoán nữa.
Tôi có một video dài 1 giờ 42 phút đi hết từ cài đặt tới sao lưu. Bài này không chép lại video. Bài này lấy đúng cái xương sống mà tôi nhấn trong video, rồi ghép thêm những chỗ anh em lập trình hay hiểu sai khiến hệ thống chậm mà không biết vì sao.
Video gốc: Hiểu toàn bộ MySQL Database trong 1 giờ 42 phút. Bài viết này gom phần kiến trúc, phần index và phần thông số vào một mạch, kèm ảnh chụp thật từ buổi demo.
Bản chất của tối ưu là giảm khối lượng công việc mà database phải làm. Mà muốn biết nó đang làm việc gì, anh em phải biết dữ liệu đang nằm ở đâu và đi đường nào.
Tôi ví dụ thế này. Anh em gõ một câu select. Dữ liệu không bao giờ được đẩy thẳng từ ổ cứng ra cho người dùng. Nó phải được nạp lên bộ nhớ trước, xào nấu ở trên đó, rồi mới trả ra. Một yêu cầu chạy được ngay trên bộ nhớ nhanh hơn hàng nghìn lần so với một yêu cầu phải mò xuống file vật lý.
Hiểu chỗ này rồi thì anh em sẽ thấy mọi kỹ thuật tối ưu sau đó đều xoay quanh đúng một câu hỏi: làm sao để nó bớt phải xuống đĩa.
Với anh em lập trình, chỉ cần nhớ hai phần thôi.
Phần thứ nhất là buffer pool. Nó giữ những dữ liệu hay dùng, cả dữ liệu bảng lẫn dữ liệu index. Lần sau có ông nào cần đúng vùng đó thì nó lấy luôn trên bộ nhớ, không phải xuống đĩa nữa.
Phần thứ hai là vùng chuyên xử lý câu lệnh DML. Update, insert, delete thì ghi vào đây. Trong tài liệu của hãng nó tên là log buffer, và thứ nó sinh ra là redo log.
Đây là chỗ nhầm kinh điển. Hai ông này cùng phục vụ câu lệnh DML, nhưng trả lời hai câu hỏi khác hẳn nhau.
| Redo | Undo | |
|---|---|---|
| Trả lời câu hỏi | Nó đang là 10, làm sao nó thành 11? | Nó đang là 11, giá trị cũ là bao nhiêu? |
| Dùng để | Khôi phục lại thao tác đã làm | Rollback về giá trị trước đó |
| Ghi xuống file | #ib_redo_N | undo_001, undo_002 |
Tôi ví dụ cho dễ hình dung. Một giá trị đang là 10, anh em update nó thành 11. Ông redo ghi lại chuyện "có câu update chạy qua đây". Ông undo giữ lại con số 10. Chẳng may anh em gõ rollback, hệ thống phải hỏi ông undo mới biết đường quay về.
Nhớ được cặp này là anh em đọc tài liệu hãng đỡ loạn hẳn.
Bộ nhớ tắt điện một phát là trắng. Nên bao giờ nó cũng phải định kỳ ghi xuống file. Phần file vật lý đó được quy hoạch bằng một khái niệm tên là table space.
Anh em hình dung table space giống như phòng trong một tòa nhà. Tòa nhà là con database. Người ta chia phòng để những file cùng tính chất nằm chung một chỗ cho dễ quản. Nhưng table space chỉ là khái niệm logic. Nhìn vào hệ điều hành anh em không thấy table space đâu cả, anh em chỉ thấy file.
Cài xong một con MySQL trắng trơn, chưa đổ dữ liệu gì, nó đã tự tạo sẵn mấy phòng bắt buộc.
| Table space | File anh em nhìn thấy | Nó giữ cái gì |
|---|---|---|
| System | ibdata1 | Data dictionary và thông tin hoạt động hệ thống |
| Undo | undo_001, undo_002 | Dữ liệu phục vụ rollback |
| Temporary | ibtmp1, temp_1.ibt | Bảng tạm, tạo lại mỗi lần restart |
Ba ông này xóa là chết. Anh em nghịch ngợm vào thư mục data rồi dọn dẹp cho gọn là teo ngay, không cứu được.
Ngoài ba ông bắt buộc, anh em hoàn toàn tạo được table space riêng. Đây là việc tôi làm thường xuyên khi tối ưu cho hệ thống lớn. Dữ liệu lịch sử giao dịch một chỗ, dữ liệu online một chỗ, quy hoạch rõ ràng từ đầu thì về sau đỡ khổ.
-- Tao table space rieng roi ep bang nam vao do
CREATE TABLESPACE data_tbs1
ADD DATAFILE 'datafile1.ibd';
CREATE TABLE lich_su_giao_dich (
id INT,
ngay DATE
) TABLESPACE data_tbs1;
-- Kiem tra bang dang nam o table space nao
SELECT NAME, SPACE_TYPE
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES;Mặc định innodb_file_per_table bằng ON. Nghĩa là cứ tạo một bảng thì nó đẻ ra một file .ibd riêng cho bảng đó. Tạo bảng test thì trong thư mục database hiện thêm một file, drop bảng đi thì file biến mất theo.
Tiện, nhưng có giá của nó. Hệ thống nhỏ thì để mặc định thoải mái. Hệ thống lớn mà số lượng bảng lên tới hàng nghìn thì riêng phần metadata đã nặng, database phải quản lý một đống file rời rạc. Tới lúc đó tôi khuyến cáo anh em phải tự quy hoạch table space theo chiến lược của mình, đừng để mặc định.
Còn doublewrite buffer thì nó ghi hai lần: ghi tạm vào file .dblwr trước, rồi mới ghi vào file dữ liệu thật. Nghe thì tốn, nhưng dữ liệu được ghi thành một khối lớn liên tục và chỉ gọi fsync() một lần, nên chi phí không hề gấp đôi. Nó tồn tại để lúc server chết giữa chừng còn có bản lành mà khôi phục.
Phần này là phần tôi thấy nhiều anh em vấp nhất. Cả ba lỗi đều không báo lỗi gì cả, câu lệnh vẫn chạy ra kết quả đúng, chỉ có điều nó chậm.
Bắt đầu từ một bảng customers có hơn 112 nghìn bản ghi, chưa có index nào ngoài primary key. Câu lệnh lọc theo hai điều kiện nam_sinh và ngay_giao_dich.
type bằng ALL và rows bằng 112200. Nó phải quét sạch bảng rồi mới lọc ra đúng hai bản ghi.Nhìn thấy type bằng ALL là phải dừng lại xem kỹ. Vài chục bản ghi thì không sao. Hàng trăm nghìn bản ghi mà ALL thì rất tốn.
Đánh index trên riêng cột nam_sinh, số bản ghi phải quét tụt từ 112200 xuống 2147, nhưng cột Extra vẫn ghi Using where, tức là nó vẫn phải lọc tiếp. Đánh index trên cả hai cột thì rows xuống còn 2 và Extra bằng NULL, không phải lọc thêm gì nữa.
Đến đây thì đẹp. Nhưng bây giờ bỏ điều kiện nam_sinh đi, chỉ tìm theo ngay_giao_dich thôi, chuyện gì xảy ra?
idx_namsinh_ngaygd không dùng được nữa, key bằng NULL, quay lại quét 112200 bản ghi. Tạo lại index đảo thứ tự thành idx_ngaygiaodich_namsinh thì nó dùng được ngay, chỉ còn quét 39 bản ghi.Cùng hai cột đó, cùng dữ liệu đó, chỉ đổi thứ tự là một đằng quét 112200 một đằng quét 39. Vì sao?
Bản chất là index nhiều cột được xếp như một mảng đã sắp xếp, ghép giá trị các cột lại theo đúng thứ tự anh em khai báo. Tài liệu MySQL gọi đó là leftmost prefix: có index trên (col1, col2, col3) thì anh em tra được theo (col1), theo (col1, col2), theo cả ba. Nhưng tra riêng (col2) thì không, vì nó không phải tiền tố bên trái.
Cột đầu tiên quyết định gần như toàn bộ chuyện index đó có dùng được hay không. Nên đừng có thấy câu lệnh nào cũng đánh index lên hết mọi cột. Đánh index phải có chiến lược. Đánh index phải có chiến lược.
Chuyện thứ tự cột này không riêng gì MySQL. Tôi cũng có phân tích đúng cơ chế đó trên PostgreSQL trong bài tầm quan trọng của thứ tự các cột trong index. Và nếu anh em đang gặp cảnh có index rồi mà vẫn chậm, bài vì sao có index mà SQL vẫn chậm liệt kê thêm mấy kiểu vấp khác trên Oracle và SQL Server.
Partition là kỹ thuật mà ai làm dữ liệu lớn cũng phải biết. Khuyến cáo của tôi: từ 2 GB trở lên, hoặc từ 10 triệu bản ghi trở lên, là phải cân nhắc partition.
Ý tưởng đơn giản như chia tầng cho tòa nhà. Không partition thì cả tòa nhà trải phẳng ra một mặt sàn. Partition theo năm giao dịch thì ông nào giao dịch năm 2018 ở tầng 18, ông nào năm 2019 ở tầng 19.
year(Ngay_giao_dich). Cột partitions chỉ đúng p_2019, số bản ghi phải quét còn 15797. Chưa đánh index gì cả.Đây là chỗ nhiều anh em ngộ nhận. Partition không phải thần thánh. Vẫn câu lệnh đó, đổi điều kiện sang tìm theo nam_sinh thay vì ngay_giao_dich, thì hệ thống chịu. Nó không biết ông sinh năm 1961 giao dịch vào năm nào, nên nó phải quét qua toàn bộ partition từ 2018 tới 2024, mà partition nào cũng ALL. Lúc này partition không giúp gì, đôi khi còn tệ hơn.
Luật rút ra chỉ một dòng: cột đem đi partition bắt buộc phải xuất hiện trong điều kiện WHERE. Tài liệu MySQL gọi cơ chế cắt bỏ partition thừa là partition pruning, và nó chỉ chạy được khi điều kiện quy được về dạng cột_partition = hằng số hoặc cột_partition IN (...).
Trong dự án thật, tôi thường ghép cả hai. Partition để cắt bớt vùng phải nhìn, index để nhảy thẳng vào bản ghi. Bảng có partition vẫn đánh index bình thường, không xung khắc gì cả.
Nhìn cả hai, nhưng phải hiểu chúng khác nhau. EXPLAIN chỉ ước lượng, nó không hề chạy câu lệnh của anh em. EXPLAIN ANALYZE thì chạy thật và trả về số liệu thật.
cost=13.7, rows=39, thời gian thực tế 0.209. Câu quét cả bảng: cost=11324, rows=112200, thời gian thực tế chạy tới 33.7.Cost là chi phí ước tính của một chiến lược thực thi. Khái niệm này không riêng MySQL, Oracle, SQL Server hay PostgreSQL đều có. Một câu lệnh luôn có nhiều đường để chạy, hệ thống tính chi phí từng đường rồi chọn đường rẻ nhất. Riêng bản 8 thì cái ông chọn đường này được nâng cấp khá nhiều, tôi có phân tích riêng trong bài hiệu năng MySQL 8 và những nâng cấp trong query optimizer.
Con số rows là ước lượng, sai được. Trong ảnh trên, nó đoán 112200 trong khi bảng thật có 112225 bản ghi. Sai lệch đó bình thường, nhưng ở hệ thống mà thống kê cũ mèm thì sai lệch to hơn nhiều, và đó là lúc optimizer chọn nhầm đường. Cách đọc chi tiết hai lệnh này tôi có chia sẻ riêng trong bài tối ưu MySQL với EXPLAIN và EXPLAIN ANALYZE.
Một kỹ thuật nữa từ MySQL 8 mà tôi hay dùng: invisible index. Anh em đang phân vân có nên drop một index nào đó không, thay vì drop luôn thì cho nó invisible trước.
-- Giau index khoi optimizer, index van con nguyen tren he thong
ALTER TABLE customers ALTER INDEX idx_namsinh_ngaygd INVISIBLE;
-- Xem index nao dang bi giau
SHOW INDEX FROM customers;
-- Chay lai cau lenh, xem ke hoach co doi khong
EXPLAIN SELECT * FROM customers
WHERE ngay_giao_dich = '2019-01-29';
-- On thi tra lai
ALTER TABLE customers ALTER INDEX idx_namsinh_ngaygd VISIBLE;Index invisible vẫn nằm nguyên trên hệ thống, insert update vẫn phải cập nhật nó, chỉ có optimizer là không nhìn thấy. Theo tài liệu hãng, bật tắt visible là thao tác tại chỗ và nhanh, còn drop rồi tạo lại index trên bảng lớn thì rất đắt. Theo dõi một thời gian dài thấy hiệu năng không đổi thì hẵng drop.
Nhìn bốn tỉ lệ. Bốn tỉ lệ này đều cùng một logic: tổng số yêu cầu là bao nhiêu, trong đó bao nhiêu phần được phục vụ ngay trên bộ nhớ. Tất cả đều lấy bằng SHOW GLOBAL STATUS.
(85049 - 1147) * 100 / 85049 ra 98 phần trăm.| Tỉ lệ | Công thức | Ngưỡng cần xử | Hướng chữa |
|---|---|---|---|
| Buffer cache hit | (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) * 100 / Innodb_buffer_pool_read_requests | Dưới 90 phần trăm | Tăng innodb_buffer_pool_size |
| Table cache hit | Open_tables / Opened_tables * 100 | Dưới 80 phần trăm | Tăng table_open_cache |
| Table definition cache hit | Open_table_definitions / Opened_table_definitions * 100 | Dưới 80 phần trăm | Tăng table_definition_cache |
| Temporary table trên bộ nhớ | (Created_tmp_tables - Created_tmp_disk_tables) * 100 / Created_tmp_tables | Dưới 80 phần trăm | Tối ưu câu lệnh, không phải tăng tham số |
Ba dòng trên chữa bằng tham số. Riêng dòng cuối thì khác, và đây là chỗ hay bị làm sai. Bảng tạm phải xuống đĩa là do câu lệnh của anh em bắt nó sắp xếp và gom nhóm quá nhiều. Cứ đè ra tăng tham số là chữa vào ngọn. Đánh index cho đúng, partition cho đúng, lượng dữ liệu phải tính toán nhỏ đi thì tỉ lệ này tự lên.
Đây là bộ lệnh anh em chép về chạy luôn.
-- [1] Buffer cache hit
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
-- [2] Table cache hit
SHOW GLOBAL STATUS LIKE 'Open_tables';
SHOW GLOBAL STATUS LIKE 'Opened_tables';
-- [3] Table definition cache hit
SHOW GLOBAL STATUS LIKE 'Open_table_definitions';
SHOW GLOBAL STATUS LIKE 'Opened_table_definitions';
-- [4] Temporary table xuong dia
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
-- Xem tham so dang cau hinh bao nhieu
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'table_open_cache';
SHOW VARIABLES LIKE 'table_definition_cache';
SHOW VARIABLES LIKE 'innodb_file_per_table';Phần này tôi phải nói thẳng, vì mấy công thức trên rất dễ bị dùng sai chỗ.
Bốn công thức đó dành cho hệ thống vừa và nhỏ. Đó là bộ thông số dễ tiếp cận nhất, ốp vào là biết ngay có cần tinh chỉnh hay không. Hệ thống core ngân hàng hay core chứng khoán thì tôi phải nhìn nhiều thứ hơn hẳn, bốn con số này chưa đủ để kết luận.
Ngưỡng không phải hằng số. Tôi để 90 phần trăm cho buffer cache hit vì đó là mức chung. Dự án nào ngặt nghèo thì xuống dưới 95 đã phải để ý rồi. Anh em đừng chép ngưỡng của tôi rồi coi đó là chuẩn cho mọi hệ thống.
Tỉ lệ đẹp không có nghĩa là hệ thống khỏe. Buffer cache hit 99 phần trăm mà một câu lệnh vẫn quét hết bảng trên bộ nhớ thì nó vẫn tốn CPU, vẫn chậm. Tỉ lệ cache chỉ nói chuyện đọc từ bộ nhớ hay từ đĩa, nó không nói chuyện câu lệnh viết hay hay dở.
Số liệu trong bài là số của một con database demo trên máy tôi. Bảng customers hơn 112 nghìn bản ghi, dữ liệu tự sinh. Anh em chạy trên hệ thống thật thì con số sẽ khác, cách đọc mới là thứ mang đi được.
SHOW GLOBAL STATUS là số cộng dồn từ lúc khởi động. Server vừa restart xong thì mẫu quá bé, tỉ lệ nhìn đẹp hay xấu đều không có nghĩa. Muốn có số dùng được thì để nó chạy đủ một chu kỳ tải bình thường đã.
Tăng tham số không miễn phí. innodb_buffer_pool_size tăng lên là ăn RAM của máy. Máy hết RAM thì hệ điều hành swap, mà swap còn chậm hơn cả việc đọc đĩa mà anh em đang tránh.
ibdata1, undo_001 và thư mục redo. Đối chiếu với sơ đồ kiến trúc ở trên.SHOW GLOBAL STATUS ở khối trên, tính ra bốn tỉ lệ, ghi lại làm mốc.EXPLAIN, khoanh hết chỗ nào type bằng ALL.ALL, kiểm tra lại thứ tự cột trong index xem có khớp điều kiện WHERE không.WHERE không.INVISIBLE, theo dõi, đừng drop vội.Kiến trúc MySQL gồm mấy phần? Hai phần. Phần bộ nhớ gồm buffer pool giữ dữ liệu hay dùng và log buffer giữ thay đổi của câu lệnh DML. Phần ổ cứng gồm các table space, doublewrite buffer files và redo log. Nắm hai phần này là đủ để làm việc.
Table space là file hay là khái niệm? Là khái niệm logic. Nhìn vào hệ điều hành anh em chỉ thấy file như ibdata1 hay t1.ibd. Table space là lớp quy hoạch gom những file cùng tính chất lại để dễ quản lý.
Vì sao đánh index rồi mà câu lệnh vẫn quét full bảng? Phần lớn là do thứ tự cột trong index nhiều cột. MySQL chỉ dùng được index theo tiền tố bên trái. Index trên (A, B) thì tra theo A được, tra theo riêng B thì không, nó quay lại quét cả bảng.
Khi nào nên dùng partition? Từ 2 GB dữ liệu hoặc từ 10 triệu bản ghi trở lên thì cân nhắc. Điều kiện bắt buộc là cột đem đi partition phải xuất hiện trong WHERE. Không thì partition vô nghĩa, đôi khi còn chậm hơn.
EXPLAIN và EXPLAIN ANALYZE khác nhau thế nào? EXPLAIN chỉ ước lượng, không chạy câu lệnh. EXPLAIN ANALYZE chạy thật và trả về thời gian thực tế. Muốn giỏi tối ưu thì đọc được cả hai, một cái cho kế hoạch, một cái cho kết quả.
Buffer cache hit bao nhiêu là ổn? Trên 90 phần trăm thì hệ thống vừa và nhỏ coi là ổn. Dưới 90 là phải đánh giá lại. Dự án ngặt nghèo thì dưới 95 đã phải để ý. Hướng chữa đầu tiên là tăng innodb_buffer_pool_size.
Kiến trúc không phải thứ để học thuộc. Nó là cái bản đồ giúp anh em biết mình đang đứng ở đâu khi hệ thống chậm.
Ba thứ đáng nhớ nhất trong bài: dữ liệu luôn phải lên bộ nhớ trước khi ra tới người dùng, cột đầu tiên của index nhiều cột quyết định gần hết, và cột partition phải nằm trong WHERE. Ba thứ đó chữa được phần lớn ca chậm mà tôi gặp.
Anh em cứ mở con database của mình lên, chạy bốn cặp lệnh trong bài, xem bốn tỉ lệ ra bao nhiêu. Sai lại sửa. Có chết đâu mà ngại. Muốn đi sâu hơn về cách đọc kế hoạch thực thi thì xem tiếp bài tối ưu SQL nên bắt đầu từ đâu, còn nếu hệ thống của anh em bị cảnh cùng một câu lệnh lúc nhanh lúc chậm thì bài tối ưu SQL khi câu lệnh lúc nhanh lúc chậm nói đúng chuyện đó.