📚 Giới thiệu
Sau khi tìm hiểu về Database Normalization, chúng ta tiếp tục khám phá "bên trong" của database - cách DBMS thực sự lưu trữ, quản lý và xử lý dữ liệu. Đây là kiến thức nền tảng giúp hiểu tại sao các quyết định thiết kế database lại quan trọng đến vậy và làm thế nào để optimize performance từ mức độ cơ bản nhất.
📋 Mục lục
1. 🧠💿 Memory vs Disk: Nền tảng của Storage
Đặc điểm cơ bản
Memory (RAM):
- 🚀 Tốc độ: 1-100 nanoseconds
- ⚡ Tính chất: Volatile (mất khi tắt máy)
- 💰 Chi phí: $3-5 per GB
- 📏 Dung lượng: 8GB-64GB thường gặp
Disk (HDD/SSD):
- 🐌 HDD: 5-10 milliseconds
- ⚡ SSD: 0.1-1 milliseconds
- 💾 Tính chất: Persistent (không mất khi tắt máy)
- 💵 Chi phí: $0.03-0.20 per GB
- 📦 Dung lượng: 500GB-10TB++
So sánh tốc độ thực tế
📊 Access Time Comparison:
RAM: 100 nanoseconds (100ns) SSD: 100,000 ns (0.1ms) ← 1,000x chậm hơn RAM
HDD: 10,000,000 ns (10ms) ← 100,000x chậm hơn RAM!
Ví dụ thực tế: Nếu RAM access = 1 giây, thì:
- SSD access = 17 phút ⏰
- HDD access = 3 giờ 🕐
Tại sao DBMS cần cả hai?
Memory cho:
- 🔥 Hot Data: Dữ liệu được truy cập thường xuyên
- ⚡ Query Processing: Sorting, joining data
- 🗂️ Index Caching: B+ tree nodes
- 🔄 Transaction Buffer: Pending changes
Disk cho:
- 🏛️ Persistent Storage: Lưu trữ lâu dài
- 📚 Complete Dataset: Toàn bộ dữ liệu
- 🔒 ACID Compliance: Durability guarantee
- 💰 Cost Effective: Lưu trữ TB dữ liệu
2. 📄 Page-based Storage: Đơn vị cơ bản của DBMS
Page là gì?
Page = "Trang" - là đơn vị nhỏ nhất mà DBMS sử dụng để đọc/ghi dữ liệu.
Analogy:
📖 Cuốn sách = Database
📄 Trang sách = Page 📝 Câu văn = Record/Row
🔤 Từ = Field/Column
Kích thước Page phổ biến
MySQL InnoDB: 16KB per page
PostgreSQL: 8KB per page SQL Server: 8KB per page
Oracle: 8KB default
Cấu trúc Page
┌─ Page Header (128 bytes) ─────────────────┐
│ Page ID: 42 │
│ Next Page: 43 │ │ Record Count: 400 │
├─ Records Data (~15KB) ───────────────────┤
│ Record 1: [1][John Smith ][20][CS ] │
│ Record 2: [2][Jane Doe ][21][IT ] │
│ Record 3: [3][Bob Johnson ][19][Math] │
│ ... (397 more records) │
├─ Free Space (800 bytes) ─────────────────┤
│ (Available for new records) │
├─ Page Footer (128 bytes) ────────────────┤
│ Checksum, Page Directory │
└──────────────────────────────────────────┘ Records per page = 16KB ÷ Average record size
3. ⚡ Quá trình INSERT: Từ SQL đến Physical Storage
Step-by-Step Execution
INSERT INTO students (name, age, major, gender, department_id) VALUES ('Alice', 20, 'Computer Science', 'F', 1);
Step 1: 🧠 Query Parsing & Validation
SQL Parser checks:
✅ Syntax correct?
✅ Table exists?
✅ Columns exist? ✅ Data types match?
✅ Constraints satisfied?
Step 2: 🔒 Transaction Management
BEGIN TRANSACTION (implicit)
- Lock relevant pages/rows
- Create transaction log entry
- Prepare for rollback if needed
Step 3: 🎯 Find Storage Location
Storage Engine looks for:
1. Current page with free space
2. If no space → allocate new page
3. Update page directory
Step 4: 💾 Physical Write Process
🔄 Write to Memory First (Buffer Pool):
┌─────────────────────────────────┐
│ RAM Buffer Pool │
├─────────────────────────────────┤
│ Page 1: [Old Records + Alice] │ ← Modified
│ Page 2: [Other data] │
│ Page 3: [Index updates] │ ← Updated
└─────────────────────────────────┘ ✍️ Write to Transaction Log (WAL):
[LSN:12345] INSERT students: Alice, 20, CS, F, 1 💿 Eventually Flush to Disk:
- Dirty pages written to disk
- Log files synced
- Commit marked complete
Record Storage Layout
Record for Alice:
┌────┬────────┬────┬─────────────┬────┬──────────────┐
│ ID │ Name │Age │ Major │Gen │ Department_ID│
├────┼────────┼────┼─────────────┼────┼──────────────┤
│ 4 │ Alice │ 20 │Computer Sci.│ F │ 1 │
└────┴────────┴────┴─────────────┴────┴──────────────┘ 4B Variable 1B Variable 1B 4B Physical bytes:
[00 00 00 04][05 41 6C 69 63 65][14][0F 43 6F 6D 70...]
4. 📝 Write-Ahead Logging: Đảm bảo Durability
Vấn đề cần giải quyết
⚠️ Crash Scenario:
1. User: UPDATE products SET price = 999 WHERE id = 123;
2. DBMS: Modify data in RAM buffer pool
3. 💥 MẤT ĐIỆN trước khi ghi xuống disk
4. Result: Dữ liệu update bị mất!
Giải pháp: Write-Ahead Logging (WAL)
Nguyên tắc WAL: "Log the change BEFORE making the change"
Quy trình an toàn:
1. 📝 Write to Transaction Log (DISK): [LSN:12345][UPDATE][products][id=123][price: 899→999] 💾 SYNC TO DISK IMMEDIATELY! (fsync) 2. 🧠 Modify data in RAM Buffer Pool: 💧 Change price to 999 in memory 🏷️ Mark page as dirty 3. ✅ Return SUCCESS to user 💥 Nếu crash: Log có đủ thông tin để recovery!
Cấu trúc Transaction Log Entry
┌─────────────────────────────────────────────┐
│ LSN: 12345 (Log Sequence Number) │
│ Transaction ID: TXN_98765 │ │ Operation: UPDATE │
│ Table: products │
│ Page ID: 42 │
│ Before Image: {id:123, price:899} │ ← Undo info
│ After Image: {id:123, price:999} │ ← Redo info
│ Timestamp: 2024-10-04 14:30:001 │
│ Checksum: ABC123 │
└─────────────────────────────────────────────┘
5. 🔥❄️💧 Hot Pages, Dirty Pages và Buffer Pool
Phân loại Pages theo "nhiệt độ"
🔥 Hot Page: Page được truy cập thường xuyên
🌡️ "Nhiệt độ" của Page:
🔥 Hot Page: 100+ accesses/minute
🌤️ Warm Page: 10-100 accesses/minute ❄️ Cold Page: < 10 accesses/minute
🧊 Frozen Page: Không truy cập trong giờ/ngày
💧 Dirty Page: Page đã được modified trong RAM nhưng chưa ghi xuống disk
Page Lifecycle:
1. 📖 Clean Page (RAM = Disk)
2. ✏️ Modification → Dirty Page (RAM ≠ Disk) 3. 💾 Flush → Clean Page again (RAM = Disk)
Buffer Pool Management
🧠 RAM Buffer Pool Layout:
┌─────────────────────────────────────────┐
│ 🔥 Hot Clean Pages (40%) │
│ 🔥 Hot Dirty Pages (20%) │
│ ❄️ Cold Clean Pages (30%) │ │ 💧 Cold Dirty Pages (10%) │
└─────────────────────────────────────────┘
LRU (Least Recently Used) Algorithm
Khi RAM full và cần load page mới: Eviction Priority (Low to High):
1. ❄️ Cold Clean Page ← Evict first (dễ nhất)
2. ❄️ Cold Dirty Page ← Flush then evict 3. 🔥 Hot Clean Page ← Evict miễn cưỡng
4. 🔥 Hot Dirty Page ← Không bao giờ evict trực tiếp
6. 🔄 Crash Recovery: Khi mọi thứ đi sai
Automatic Crash Recovery Process
-- Khi MySQL khởi động lại sau crash: 1. 🔍 ANALYZE Phase: - Scan transaction logs từ last checkpoint - Identify committed vs uncommitted transactions 2. 🔄 REDO Phase: - Replay all committed transactions - Apply changes to data pages 3. ↩️ UNDO Phase: - Rollback uncommitted transactions - Use "before image" data 4. ✅ READY: Database consistent và sẵn sàng
Recovery Example
Transaction Log entries:
┌──────┬─────────────┬────────┬─────────────────┐
│ LSN │ TXN_ID │ Status │ Operation │
├──────┼─────────────┼────────┼─────────────────┤
│ 1001 │ TXN_98765 │ BEGIN │ Start trans │
│ 1002 │ TXN_98765 │ UPDATE │ price: 899→999 │
│ 1003 │ TXN_98765 │ COMMIT │ Transaction end │ ← ✅ Apply
│ 1004 │ TXN_98766 │ BEGIN │ Start trans │ │ 1005 │ TXN_98766 │ INSERT │ New product │ ← ❌ Rollback
└──────┴─────────────┴────────┴─────────────────┘
7. 💰 Tại sao Log rẻ hơn Direct Write?
Cost Comparison
📊 Performance Analysis: Transaction Log (Sequential I/O):
- Write time: 0.1ms
- Size: ~100-500 bytes per operation - Disk movement: Minimal (append only)
→ Cost: VERY LOW 💚 Direct Data Write (Random I/O):
- Write time: 10-50ms - Size: 16KB full page (even for 1 byte change)
- Disk movement: Random seeks
→ Cost: VERY HIGH 🔴 Speedup: ~100-500x faster!
Tại sao không ghi thẳng vào disk?
1. Random I/O vs Sequential I/O
Log File (Sequential):
Data: [Log1][Log2][Log3][Log4]...
Head: ────────────────────────→ (Straight movement) Data File (Random): Pages: [P1]...[P500]...[P23]...[P1200]...
Head: ↑─────────↑─────↑───────↑ (Expensive seeks!)
2. Write Amplification
User change: 1 byte (age: 21→22) Direct approach:
- Read 16KB page → Modify 1 byte → Write 16KB page
- Write amplification: 32,000x! Log approach:
- Write ~100 byte log entry
- Write amplification: 100x (325x better!)
3. Atomicity Problem
-- Transaction updating 2 tables:
BEGIN;
UPDATE products SET price = 999 WHERE id = 123;
UPDATE inventory SET cost = 800 WHERE product_id = 123; COMMIT; Direct write: Crash giữa 2 updates → Inconsistent!
Log write: Cả 2 changes trong 1 log entry → Atomic!
Architecture Tối ưu
Write Path (Durability):
User → Transaction Log → Return Success ↓ (async) Data Files (Performance optimization) Read Path (Performance): User → Data Files (Indexed) → Return Results
8. 📊 Data Types và Page Efficiency
Tác động của Data Type Selection
Scenario Analysis:
-- ❌ Inefficient design
CREATE TABLE products ( id INT, -- 4 bytes name VARCHAR(500), -- Overkill! ~200 bytes average description TEXT(65535), -- Huge! ~1000 bytes metadata JSON -- Variable, ~300 bytes
);
-- Average record: ~1500 bytes
-- Records per page: 16KB ÷ 1500B ≈ 10 records -- ✅ Efficient design
CREATE TABLE products ( id INT, -- 4 bytes name VARCHAR(100), -- Right-sized, ~50 bytes category_id INT, -- 4 bytes, normalized price DECIMAL(10,2) -- 8 bytes
);
-- Average record: ~70 bytes
-- Records per page: 16KB ÷ 70B ≈ 230 records
Performance Impact Example
Query: Tìm records #500 và #600 ❌ Inefficient (10 records/page):
- Record #500 → Page 50
- Record #600 → Page 60 - Pages needed: 11 pages (50-60)
- Memory: 11 × 16KB = 176KB
- I/O: 11 disk reads ✅ Efficient (230 records/page):
- Record #500 → Page 3
- Record #600 → Page 3
- Pages needed: 1 page only!
- Memory: 1 × 16KB = 16KB - I/O: 1 disk read Performance: 11x fewer I/O operations! 🚀
Buffer Pool Efficiency
🧠 Buffer Pool Impact (1GB RAM): ❌ Large records (10/page):
- Total pages in memory: 65,536
- Total records cached: 655,360
- Cache coverage: 6.5% of 10M records ✅ Compact records (230/page): - Total pages in memory: 65,536
- Total records cached: 15,073,280 - Cache coverage: 150% of 10M records (full dataset!) → 23x more records in same memory! 🎯
Index Impact
Index efficiency với different record densities: ❌ Few records per page:
- More page pointers in index
- Deeper B+ tree structure - More I/O for index traversal ✅ Many records per page:
- Fewer page pointers needed
- Shallower B+ tree structure
- Fewer I/O for index lookups
9. 🎯 Best Practices và Insights
Data Type Guidelines
-- ✅ Right-sizing examples: -- Names and text
name VARCHAR(100) -- Not VARCHAR(255)
email VARCHAR(255) -- RFC compliant max
phone VARCHAR(20) -- International format
description TEXT -- Only when needed -- Numbers
age TINYINT UNSIGNED -- 0-255, not INT
price DECIMAL(10,2) -- Precise, not FLOAT
quantity INT UNSIGNED -- Non-negative -- Enums for limited values
status ENUM('active','inactive','pending') -- Not VARCHAR(50)
gender ENUM('M','F','O') -- Not VARCHAR(10) -- Timestamps
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Performance Optimization Strategy
1. Target Record Density:
Optimal: 100-200 records per page
Formula: Record size ≈ 16KB ÷ target records ≈ 80-160 bytes If record > 160 bytes:
→ Consider normalization → Move large fields to separate tables
→ Use appropriate data types
2. Monitor Key Metrics:
-- Buffer pool efficiency
SHOW ENGINE INNODB STATUS\G Key metrics to watch:
- Buffer pool hit rate: >99% ideal
- Dirty pages ratio: 10-25% normal - Pages read from disk: Lower is better
3. Memory Configuration:
-- MySQL tuning for page efficiency
SET innodb_buffer_pool_size = '70%_of_RAM';
SET innodb_page_size = 16384; -- Default, don't change unless necessary
SET innodb_max_dirty_pages_pct = 75;
Architecture Principles
The Three Pillars:
- 📝 Log for Durability - Fast sequential writes ensure data safety
- 🧠 Memory for Speed - Buffer pool maximizes performance
- 💿 Disk for Scale - Persistent storage handles large datasets
Key Design Rules:
- Right-size data types - Every byte counts in page efficiency
- Normalize appropriately - Balance normalization vs query performance
- Index strategically - More records per page = better index efficiency
- Monitor continuously - Buffer pool metrics reveal optimization opportunities
🎉 Kết luận
Key Takeaways
- 🏗️ DBMS Architecture: Hiểu cách storage engine hoạt động giúp thiết kế database thông minh hơn
- 📄 Page Efficiency: Data type choices ảnh hưởng trực tiếp đến số records per page và performance
- 🔄 WAL Strategy: Transaction logs đảm bảo durability với chi phí thấp hơn direct writes nhiều lần
- 🧠 Memory Management: Buffer pool là chìa khóa performance - maximize cache hit rate
- ⚡ I/O Optimization: Sequential I/O (logs) vs Random I/O (data files) - hiểu để optimize
Practical Impact
Từ kiến thức này, chúng ta hiểu tại sao:
- ✅ Chọn đúng data types quan trọng (page efficiency)
- ✅ Normalization có trade-offs thực tế (memory vs consistency)
- ✅ Database có thể "survive" crashes (WAL mechanism)
- ✅ Scaling database cần hiểu buffer pool management
- ✅ Performance tuning phải dựa trên hiểu biết sâu về storage
Lời khuyên cuối
Database performance là nghệ thuật của việc giữ đúng dữ liệu ở đúng nơi vào đúng thời điểm. Hiểu về internals giúp chúng ta không chỉ viết SQL queries mà còn trở thành database architects - những người thiết kế systems có thể scale và perform tốt trong thực tế.
Remember: Every design decision has consequences. Understanding the internals helps make informed trade-offs! 🚀