MySQL - Lý Thuyết Cơ Bản

📚 Giới Thiệu

MySQL là hệ quản trị cơ sở dữ liệu quan hệ (RDBMS) mã nguồn mở phổ biến nhất thế giới, được phát triển bởi Oracle Corporation.

🏗️ Kiến Trúc MySQL

1. Connection Layer

Client Applications
       ↓
Connection/Thread Handling
       ↓
Authentication & Security

2. SQL Layer

SQL Interface
       ↓
Parser & Optimizer
       ↓
Query Cache
       ↓
Query Execution Engine

3. Storage Engine Layer

InnoDB | MyISAM | Memory | Archive
       ↓
File System
       ↓
Hardware (Disk, Memory, CPU)

🔧 Storage Engines

InnoDB (Default)

  • Đặc điểm: ACID compliant, hỗ trợ transactions
  • Ưu điểm:
  • Row-level locking
  • Foreign key constraints
  • Crash recovery
  • Multi-version concurrency control (MVCC)
  • Sử dụng: OLTP applications, high concurrency

MyISAM

  • Đặc điểm: Table-level locking, không hỗ trợ transactions
  • Ưu điểm:
  • Tốc độ read nhanh
  • Compression tốt
  • Full-text indexing
  • Sử dụng: Read-heavy applications, data warehousing

Memory (HEAP)

  • Đặc điểm: Lưu trữ trong RAM
  • Ưu điểm: Truy cập cực nhanh
  • Nhược điểm: Mất dữ liệu khi restart
  • Sử dụng: Temporary tables, cache

📊 Data Types

Numeric Types

-- Integer types
TINYINT     -- 1 byte (-128 to 127)
SMALLINT    -- 2 bytes (-32,768 to 32,767)
MEDIUMINT   -- 3 bytes (-8,388,608 to 8,388,607)
INT         -- 4 bytes (-2,147,483,648 to 2,147,483,647)
BIGINT      -- 8 bytes

-- Decimal types
DECIMAL(M,D)  -- Exact decimal
FLOAT(M,D)    -- Single precision
DOUBLE(M,D)   -- Double precision

String Types

-- Fixed length
CHAR(M)       -- 0-255 characters

-- Variable length  
VARCHAR(M)    -- 0-65,535 characters
TEXT          -- 0-65,535 characters
MEDIUMTEXT    -- 0-16,777,215 characters
LONGTEXT      -- 0-4,294,967,295 characters

-- Binary
BINARY(M)     -- Fixed binary string
VARBINARY(M)  -- Variable binary string
BLOB          -- Binary large object

Date/Time Types

DATE          -- YYYY-MM-DD
TIME          -- HH:MM:SS
DATETIME      -- YYYY-MM-DD HH:MM:SS
TIMESTAMP     -- UTC timestamp
YEAR          -- YYYY

🔐 Indexes

Index Types

  1. Primary Index: Clustered index trên primary key
  2. Secondary Index: Non-clustered index
  3. Unique Index: Đảm bảo tính duy nhất
  4. Composite Index: Index trên nhiều columns
  5. Partial Index: Index với WHERE condition
  6. Full-text Index: Cho text search

Index Algorithms

-- B-Tree Index (Default)
CREATE INDEX idx_name ON table_name (column_name);

-- Hash Index (Memory engine)
CREATE INDEX idx_name ON table_name (column_name) USING HASH;

-- Full-text Index
CREATE FULLTEXT INDEX idx_name ON table_name (column_name);

🔄 Transactions & ACID

ACID Properties

  1. Atomicity: All or nothing
  2. Consistency: Valid state transitions
  3. Isolation: Concurrent transaction independence
  4. Durability: Committed changes persist

Transaction Control

-- Start transaction
START TRANSACTION;
BEGIN;

-- Commit changes
COMMIT;

-- Rollback changes
ROLLBACK;

-- Savepoints
SAVEPOINT sp1;
ROLLBACK TO sp1;

Isolation Levels

-- Read Uncommitted (Dirty reads possible)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Read Committed (No dirty reads)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Repeatable Read (Default trong MySQL)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Serializable (Strictest)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

🔒 Locking Mechanisms

Lock Types

  1. Shared Lock (S): Read lock
  2. Exclusive Lock (X): Write lock
  3. Intention Locks: IS, IX locks
  4. Gap Locks: Lock gaps between records
  5. Next-key Locks: Record + gap locks

Lock Granularity

  • Row-level: InnoDB default
  • Table-level: MyISAM default
  • Page-level: Ít sử dụng

🚀 Performance Features

Query Cache

-- Enable query cache
SET GLOBAL query_cache_type = ON;
SET GLOBAL query_cache_size = 1048576; -- 1MB

Partitioning

-- Range partitioning
CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023)
);

Replication

  • Master-Slave: Một master, nhiều slaves
  • Master-Master: Nhiều masters
  • Group Replication: Multi-master với consensus

📈 Monitoring & Optimization

Performance Schema

-- Enable performance schema
SHOW VARIABLES LIKE 'performance_schema';

-- Monitor slow queries
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

Key Metrics

  1. Connections: Active connections
  2. QPS: Queries per second
  3. Slow queries: Queries > long_query_time
  4. Lock contention: Waiting threads
  5. Buffer pool hit ratio: InnoDB cache efficiency

Lý thuyết này cung cấp foundation vững chắc cho việc trả lời các câu hỏi phỏng vấn về MySQL.