📚 Tổng quan
Hôm nay chúng ta đã khám phá sâu về cách các tệp cơ sở dữ liệu được tổ chức trên ổ đĩa và các chiến lược tối ưu hiệu suất thông qua quản lý lưu trữ. Từ việc hiểu cách MySQL đọc dữ liệu cho đến việc thiết kế kiến trúc lưu trữ phân tầng.
📋 Mục lục
1. 🗂️ Tổ chức Files Database trên Ổ đĩa
Cấu trúc tệp MySQL điển hình
📁 /var/lib/mysql/ (Linux) hoặc C:\ProgramData\MySQL\ (Windows)
├── 📁 mysql/ ← System database
├── 📁 performance_schema/ ← Performance monitoring
├── 📁 hanzi/ ← User database
│ ├── admins.ibd (112 KB) ← Table files
│ ├── category.ibd (144 KB) │ ├── e_questions.ibd (21 MB) ← Hot table
│ ├── e_answers.ibd (10 MB) ← Hot table
│ └── db.opt ← Database config
├── 📄 ib_logfile0 ← Transaction log #1
├── 📄 ib_logfile1 ← Transaction log #2 ├── 📄 ibdata1 ← System tablespace
└── 📄 binlog.000001 ← Binary logs
Lưu trữ theo Trang
📄 Mỗi tệp .ibd được chia thành các trang (16KB mỗi trang): Trang 0: Tiêu đề tệp + Metadata
Trang 1: Gốc Index chính
Trang 2: Gốc các Index phụ Trang 3-N: Trang dữ liệu
Điểm quan trọng
- Mỗi bảng = 1 tệp .ibd
- Mỗi tệp chia thành các trang 16KB
- MySQL đọc theo đơn vị trang, không phải từng bản ghi
2. ⚡ Thực thi Truy vấn: Từ SQL đến Lưu trữ Vật lý
Quy trình thực thi truy vấn
SELECT * FROM user WHERE id = 100;
Bước 1: 🧠 Phân tích Truy vấn (Bộ nhớ)
✅ Phân tích cú pháp SQL
✅ Kiểm tra sự tồn tại của bảng/cột ✅ Chọn kế hoạch thực thi
Bước 2: 💿→🧠 Tải Trang từ Ổ đĩa
MySQL KHÔNG đọc trực tiếp từ ổ đĩa!
1. Tải trang từ ổ đĩa → Vùng đệm (RAM)
2. Xử lý dữ liệu trong bộ nhớ
3. Trả về kết quả
Bước 3: 🧠 Xử lý Dữ liệu (Bộ nhớ)
Các thao tác CPU trong RAM:
- Phân tích nội dung trang
- Lọc bản ghi theo điều kiện WHERE
- Trích xuất các cột cần thiết
Thời gian Hiệu suất
💿 I/O ổ đĩa: ~10ms (tải trang)
🧠 Xử lý RAM: ~0.001ms (xử lý dữ liệu) = I/O ổ đĩa chậm hơn 10,000 lần!
3. 🔍 Tìm kiếm qua Index so với Quét toàn bộ Bảng
Quét toàn bộ Bảng (Không có Index)
SELECT * FROM user WHERE id = 100; -- Không có index trên 'id'
Quy trình thực thi:
🐌 Quy trình tuần tự:
1. Tải trang 0 → Quét bản ghi → Không tìm thấy
2. Tải trang 1 → Quét bản ghi → Không tìm thấy
3. Tải trang 2 → Quét bản ghi → Không tìm thấy
...
50. Tải trang 49 → Tìm thấy bản ghi id=100! Kết quả: 50 thao tác I/O ổ đĩa = 500ms
Tìm kiếm qua Index (Có Index)
SELECT * FROM user WHERE id = 100; -- Có PRIMARY KEY index
Duyệt cây B+:
⚡ Quy trình logarit:
1. Tải gốc Index → Tìm con trỏ đến Cấp 1
2. Tải Index Cấp 1 → Tìm con trỏ đến Lá 3. Tải Lá Index → Tìm thấy: Trang 22, Vị trí 75
4. Tải Trang dữ liệu 22 → Trích xuất bản ghi tại Vị trí 75 Kết quả: 4 thao tác I/O ổ đĩa = 40ms
= Nhanh hơn 12.5 lần!
Cấu trúc Index Entry
📊 Mục trong nút Lá:
┌─────────────────────────────────────────────────────┐
│ Giá trị Khóa │ Định vị Hàng (ID Trang + Vị trí + Độ dài) │
├─────────────┼─────────────────────────────────────────┤
│ id = 100 │ Trang: 22, Vị trí: 75, Độ dài: 128 │
└─────────────────────────────────────────────────────┘ = Tọa độ GPS của bản ghi!
4. 💥 Nghẽn cổ chai I/O và Chiến lược Đa ổ đĩa
Vấn đề: Nghẽn cổ chai Một ổ đĩa
❌ Tất cả files trên 1 ổ C:\:
Yêu cầu 1: SELECT từ bảng A → Xếp hàng
Yêu cầu 2: INSERT vào bảng B → Chờ... Yêu cầu 3: UPDATE bảng C → Chờ...
Yêu cầu 4: DELETE từ bảng D → Chờ... = Xử lý tuần tự thay vì song song!
Giải pháp: Kiến trúc Đa ổ đĩa
✅ Phân tán I/O:
SSD 1: Bảng dữ liệu nóng
SSD 2: Nhật ký giao dịch
HDD: Dữ liệu lạnh + sao lưu = Các thao tác I/O song song!
Tác động Hiệu suất
📊 Trước (Một ổ đĩa):
4 truy vấn đồng thời: 200ms tổng cộng (tuần tự) 📊 Sau (Đa ổ đĩa): 4 truy vấn đồng thời: 50ms tổng cộng (song song) = Cải thiện hiệu suất 4 lần!
5. 📁 Tách Thư mục so với Tách Ổ đĩa Vật lý
❌ Thư mục trên cùng 1 ổ đĩa: KHÔNG hiệu quả
C:\ (Ổ đĩa Vật lý giống nhau)
├── C:\MySQL\Data\ ← Thư mục 1
├── C:\MySQL\Logs\ ← Thư mục 2
├── C:\MySQL\Indexes\ ← Thư mục 3 = VẪN LÀ 1 đầu đọc = VẪN I/O tuần tự!
✅ Tách Ổ đĩa Vật lý: Hiệu quả cao
D:\ (Ổ đĩa Vật lý 1): Tệp dữ liệu
E:\ (Ổ đĩa Vật lý 2): Nhật ký giao dịch F:\ (Ổ đĩa Vật lý 3): Lưu trữ = Xử lý song song thật sự!
Quy tắc Quan trọng
Chỉ có tách biệt vật lý mới mang lại lợi ích hiệu suất!
6. 🔗 Mối quan hệ Bảng và Chiến lược Lưu trữ
❌ Sai lầm phổ biến: Tách các bảng liên quan
-- Ý TƯỜNG XẤU: orders trên ổ đĩa A, order_details trên ổ đĩa B
SELECT o.*, od.* FROM orders o JOIN order_details od ON o.id = od.order_id; Vấn đề:
1. Đọc orders từ ổ đĩa A (10ms)
2. Đọc order_details từ ổ đĩa B (10ms) ← Chờ! 3. JOIN trong bộ nhớ
Tổng: 20ms + thời gian JOIN
✅ Cách tiếp cận đúng: Các bảng liên quan cùng lưu trữ
-- TỐT: Cả orders và order_details trên cùng SSD nhanh
Thực thi truy vấn giống nhau:
1. Đọc orders (10ms)
2. Đọc order_details (đã cache!) (0ms)
3. JOIN trong bộ nhớ Tổng: 10ms + thời gian JOIN = Nhanh hơn 2 lần + hiệu quả vùng đệm tốt hơn!
Thực hành Tốt nhất
Nhóm theo mối quan hệ, tách theo mẫu truy cập!
7. 📊 Tách riêng Index: Khi nào có ý nghĩa
❌ Ứng dụng OLTP: KHÔNG nên tách
-- Truy vấn điển hình của hệ thống học trực tuyến
SELECT * FROM e_questions WHERE exam_id = 123; Mẫu Truy cập:
1. Tìm kiếm Index → Tìm vị trí 2. Lấy dữ liệu → Lấy toàn bộ bản ghi = Cả index VÀ dữ liệu đều cần → Để cùng nhau!
✅ Phân tích/OLAP: CÓ THỂ tách
-- Truy vấn kho dữ liệu
SELECT category_id, COUNT(*), AVG(price)
FROM products WHERE created_date >= '2024-01-01'
GROUP BY category_id; Mẫu Truy cập:
1. Quét index nặng (SSD)
2. Truy cập trang dữ liệu tối thiểu (HDD OK)
3. Tổng hợp trong bộ nhớ = Việc tách index có ý nghĩa!
Ma trận Quyết định
📊 Khi nào tách indexes:
✅ Cơ sở dữ liệu rất lớn (100GB+)
✅ Tải làm việc phân tích (quét nhiều, trả về ít)
✅ Nhiều thao tác đọc với lọc phức tạp ❌ Ứng dụng OLTP ❌ Cơ sở dữ liệu nhỏ-vừa
❌ Thường xuyên truy cập toàn bộ bản ghi
8. 📂 Phân vùng và Phân phối Files
Phân vùng = Các tệp riêng biệt
CREATE TABLE orders ( order_id INT, order_date DATE, total_amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) ( PARTITION p2021 VALUES LESS THAN (2022), PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2024 VALUES LESS THAN (2025), PARTITION p2025 VALUES LESS THAN (2026)
);
Các tệp Vật lý
📁 Thư mục Database:
├── orders#P#p2021.ibd ← Dữ liệu 2021
├── orders#P#p2022.ibd ← Dữ liệu 2022
├── orders#P#p2024.ibd ← Dữ liệu 2024 └── orders#P#p2025.ibd ← Dữ liệu 2025
Lợi ích
1. Hiệu suất Truy vấn (Cắt bỏ Phân vùng)
SELECT * FROM orders WHERE order_date >= '2024-01-01'; ❌ Không phân vùng: Quét TẤT CẢ dữ liệu 4 năm
✅ Có phân vùng: Quét CHỈ p2024 + p2025 = Truy vấn nhanh hơn 2 lần!
2. Thao tác Bảo trì
-- Xóa dữ liệu cũ ngay lập tức
ALTER TABLE orders DROP PARTITION p2021; -- Trong một giây! -- so với DELETE truyền thống
DELETE FROM orders WHERE YEAR(order_date) = 2021; -- Hàng giờ!
3. Phân phối Lưu trữ
-- Dữ liệu hiện tại → SSD nhanh
ALTER TABLE orders REORGANIZE PARTITION p2025 TABLESPACE fast_ssd; -- Dữ liệu lưu trữ → HDD rẻ
ALTER TABLE orders REORGANIZE PARTITION p2021 TABLESPACE archive_hdd;
9. 🔥❄️ Chiến lược Lưu trữ Phân tầng
Phân loại Dữ liệu
🔥 Dữ liệu NÓNG (Truy cập thường xuyên):
- Dữ liệu kỳ học hiện tại (e_questions, e_answers) - Phiên người dùng, xác thực
- Giao dịch đang hoạt động 🌤️ Dữ liệu ẤM (Truy cập vừa phải):
- Dữ liệu kỳ học gần đây
- Hồ sơ người dùng - Dữ liệu cấu hình ❄️ Dữ liệu LẠNH (Truy cập hiếm):
- Dữ liệu lịch sử (>1 năm)
- Nhật ký kiểm toán
- Tệp sao lưu
Sơ đồ Lưu trữ
📊 Kiến trúc Phân tầng:
Tầng 1 - NVMe SSD (D:\): Dữ liệu nóng
├── Chi phí: $100/TB
├── Tốc độ: 3,500 MB/s
└── Sử dụng: Các thao tác thời gian thực Tầng 2 - SATA SSD (E:\): Dữ liệu ấm ├── Chi phí: $60/TB
├── Tốc độ: 500 MB/s
└── Sử dụng: Báo cáo, thao tác quản trị Tầng 3 - HDD (F:\): Dữ liệu lạnh
├── Chi phí: $20/TB ├── Tốc độ: 150 MB/s
└── Sử dụng: Lưu trữ, tuân thủ
Cân bằng Chi phí-Hiệu suất
💰 Phân tích Chi phí Lưu trữ:
Toàn SSD: 100% hiệu suất, 100% chi phí
Phân tầng: 95% hiệu suất, 30% chi phí = Hiệu quả chi phí tốt hơn 3.3 lần!
Quản lý Chu kỳ sống Tự động
-- Tự động di chuyển dữ liệu theo tuổi
CREATE EVENT move_old_data
ON SCHEDULE EVERY 1 MONTH DO
BEGIN -- Di chuyển phân vùng cũ sang lưu trữ ALTER TABLE e_questions REORGANIZE PARTITION p2022 TABLESPACE archive_hdd; -- Thêm phân vùng mới cho tương lai ALTER TABLE e_questions ADD PARTITION ( PARTITION p2026 VALUES LESS THAN (2027) TABLESPACE hot_ssd );
END;
10. 🎯 Hướng dẫn Triển khai
Giai đoạn Đánh giá
1. Phân tích Sử dụng Hiện tại
-- Kích thước bảng và mẫu truy cập
SELECT table_name, ROUND(data_length/1024/1024, 2) AS data_mb, table_rows, update_time
FROM information_schema.tables WHERE table_schema = 'hanzi'
ORDER BY data_length DESC; -- Phân tích tần suất truy vấn
SELECT digest_text, count_star, avg_timer_wait/1000000000 AS avg_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY count_star DESC;
2. Giám sát I/O
# Windows
Task Manager → Performance → Disks # Linux
iostat -x 1
iotop -u mysql
Chiến lược Triển khai
Giai đoạn 1: Thành công Nhanh
-- Xác định và di chuyển các bảng nóng nhất
CREATE TABLESPACE hot_tier ADD DATAFILE 'D:\MySQL\Hot\hot.ibd';
ALTER TABLE e_questions TABLESPACE hot_tier;
ALTER TABLE e_answers TABLESPACE hot_tier;
Giai đoạn 2: Kiến trúc Toàn diện
-- Tạo các tablespace phân tầng
CREATE TABLESPACE hot_tier ADD DATAFILE 'D:\MySQL\Hot\hot.ibd';
CREATE TABLESPACE warm_tier ADD DATAFILE 'E:\MySQL\Warm\warm.ibd';
CREATE TABLESPACE cold_tier ADD DATAFILE 'F:\MySQL\Cold\cold.ibd'; -- Phân phối bảng theo mẫu truy cập
ALTER TABLE e_questions TABLESPACE hot_tier; -- Tần suất cao
ALTER TABLE e_exams TABLESPACE warm_tier; -- Tần suất vừa
ALTER TABLE audit_logs TABLESPACE cold_tier; -- Tần suất thấp
Phase 3: Partitioning + Tiering
-- Partition large tables với tiered storage
CREATE TABLE e_questions_new ( question_id INT, exam_id INT, created_date DATE
) PARTITION BY RANGE (YEAR(created_date)) ( PARTITION p2024 VALUES LESS THAN (2025) TABLESPACE hot_tier, PARTITION p2025 VALUES LESS THAN (2026) TABLESPACE hot_tier, PARTITION p2022 VALUES LESS THAN (2023) TABLESPACE cold_tier, PARTITION p2021 VALUES LESS THAN (2022) TABLESPACE cold_tier
);
Monitoring và Optimization
Performance Metrics
-- Buffer pool efficiency
SHOW ENGINE INNODB STATUS\G -- I/O statistics per table
SELECT object_name, count_read, count_write, sum_timer_wait/1000000000 as total_time_sec
FROM performance_schema.table_io_waits_summary_by_table WHERE object_schema = 'hanzi'
ORDER BY sum_timer_wait DESC;
Continuous Optimization
📊 Weekly Review Tasks:
1. Check query performance trends
2. Monitor storage usage per tier 3. Identify access pattern changes
4. Adjust tier assignments accordingly
5. Plan partition maintenance
🎉 Điểm Quan trọng
Nguyên tắc Cốt lõi
- 🧠 Bộ nhớ Trước tiên: RAM là lưu trữ nhanh nhất, tối ưu sử dụng vùng đệm
- 📍 Vị trí Quan trọng: Dữ liệu liên quan nên ở gần nhau về mặt vật lý
- 🔥❄️ Phân tầng theo Mức độ Nóng: Dữ liệu nóng → lưu trữ nhanh, dữ liệu lạnh → lưu trữ rẻ
- ⚡ I/O Song song: Nhiều ổ đĩa vật lý cho phép thao tác đồng thời
- 📂 Sức mạnh Phân vùng: Tách tệp cho phép quản lý linh hoạt
Quy tắc Hiệu suất
- Index + Dữ liệu Cùng nhau: Cho các tải làm việc OLTP
- Bảng Liên quan Cùng nhau: Các bảng JOIN trên cùng lưu trữ nhanh
- Phân vùng theo Chu kỳ sống: Dữ liệu hiện tại nóng, dữ liệu cũ lạnh
- Giám sát và Điều chỉnh: Mẫu truy cập thay đổi theo thời gian
Cost Optimization
- Right Data, Right Storage: 95% performance với 30% cost
- Automated Lifecycle: Move data as it ages
- Compression for Archives: Save 50-70% space on cold storage
💡 Nhận định Cuối cùng
Hiệu suất cơ sở dữ liệu là nghệ thuật của việc giữ đúng dữ liệu ở đúng nơi vào đúng thời điểm.
Từ việc hiểu cách MySQL đọc từng trang, đến thiết kế kiến trúc lưu trữ phân tầng - mọi quyết định đều ảnh hưởng đến cả hiệu suất và chi phí.
Nhớ rằng:
- ✅ Tách biệt vật lý → Cải thiện hiệu suất
- ✅ Tách biệt logic → Lợi ích quản lý
- ✅ Phân tầng theo chu kỳ sống → Tối ưu chi phí
- ✅ Giám sát và điều chỉnh → Hiệu suất bền vững