Database Normalization Principles

Relational database design follows progressive normalization rules to eliminate redundancy and insure data integrity:

First Normal Form (1NF) requires that each column contains atomic, indivisible values. No repeating groups or arrays should exist within a single field.

Second Normal Form (2NF) builds upon 1NF by ensuring every non-key attribute fully depends on the entire primary key, eliminating partial dependencies. This necessitates unique row identification through primary key constraints.

Third Normal Form (3NF) extends 2NF by removing transitive dependencies, where non-key columns must not depend on other non-key columns. This typically involves extracting related attributes into separate tables with foreign key relationships.

Storage Engine Architecture

MySQL supports multiple storage engines accessible via:

SHOW ENGINES;

InnoDB serves as the default transactional engine featuring:

• Row-level locking mechanisms
• ACID compliance with crash recovery capabilities
• Foreign key constraint enforcement
• Clustered index organization where data resides within leaf nodes
• Support for automatic increment columns and MVCC

MyISAM provides:

• Table-level locking with high-speed read operations
• Non-transactional processing ideal for read-heavy workloads
• Full-text indexing capabilities
• Separate data and index file storage (non-clustered architecture)
• Smaller storage footprint compared to InnoDB

Memory engine characteristics:

• Hash-based indexing for rapid exact-match lookups
• Volatile storage requiring data reconstruction after restart
• Suitable for temporary lookup tables and session data

Merge engines combine multiple MyISAM tables into a single logical table.

Transaction Management and Isolation

Atomicity, Consistency, Isolation, and Durability (ACID) properties ensure reliable transaction processing:

Atomicity guarantees that operations within a transaction boundary suceed or fail collectively. Partial completions trigger automatic rollback to maintain database consistency.

Consistency ensures that valid transactions transition the database from one legitimate state to another, preserving defined rules and constraints.

Isolation prevents concurrent transactions from interfering with each other through various locking mechanisms and visibility rules.

Durability commits completed transactions to permanent storage, surviving system failures.

Concurrency Anomalies

Without proper isolation, concurrent transactions produce:

• Dirty Reads: Accessing uncommitted modifications from concurrent transactions
• Lost Updates: Overlapping write operations causing earlier modifications to disappear
• Non-repeatable Reads: Subsequent reads within the same transaction returning different values due to concurrent commits
• Phantom Reads: New rows appearing between queries within the same transaction due to concurrent inserts

Isolation Levels

MySQL InnoDB implements four isolation tiers:

1. READ UNCOMMITTED: Permits reading uncommitted data, risking all concurrency anomalies
2. READ COMMITTED: Prevents dirty reads only, allowing non-repeatable and phantom reads
3. REPEATABLE READ (Default): Utilizes Next-Key Locking to prevent dirty reads and non-repeatable reads while minimizing phantom reads
4. SERIALIZABLE: Complete isolation through sequential execution, preventing all anomalies

Verify current isolation settings:

SELECT @@transaction_isolation;

Indexing Strategies

Indexes accelerate data retrieval through B+Tree structures, storing sorted key values with row locators. Primary storage options include:

Clustered Indexes: Physically order table data (InnoDB uses primary keys). Each table supports only one clustered index.

Non-clustered Indexes: Maintain separate structures containing index keys and row pointers (or primary key references in InnoDB).

Composite Indexes: Multi-column structures following leftmost prefix matching rules.

Covering Indexes: Include all queried columns, eliminating table lookups.

Full-text Indexes: Support natural language searching within text columns.

Performance Considerations

Benefits include reduced I/O through efficient data location and accelerated sorting operations. Costs involve storage overhead for index files and decreased write performance due to index maintenance requirements.

Query Optimization Techniques

Effective SQL tuning strategies:

Replace SELECT * with specific column projections to minimize network transfer and buffer pool usage.

Substitute subqueries with JOIN operations (LEFT JOIN, INNER JOIN) where semantically equivalent.

Prefer EXISTS over IN for subquery evaluation, particularly with NULLable columns.

Replace OR conditions with UNION or UNION ALL when operating on indexed columns, though UNION ALL performs better when duplicate elimination isn't required.

Avoid non-sargable expressions in WHERE clauses:

-- Inefficient (prevents index usage)
WHERE salary * 1.1 > 100000

-- Optimized
WHERE salary > 100000 / 1.1

Handle NULL values through default value assignments rather than IS NULL predicates:

-- Instead of
WHERE phone_number IS NULL

-- Use default values
WHERE phone_number = 'N/A'

Data Manipulation Operations

DROP removes table structures and data entirely (DDL), immediately reclaiming storage without transaction logging capability.

DELETE removes specific rows while preserving structure (DML), supporting rollback and trigger execution, though slower due to individual row logging.

TRUNCATE quickly removes all rows while preserving structure (DDL), operating through table recreation without row-level logging or trigger activation.

Join Operations

Inner Join returns only matched records from both tables.

Left Outer Join returns all records from the left table with matched right-table values, filling NULLs for unmatched rows.

Right Outer Join reverses this logic, preserving all right-table records.

Concurrency Control Mechanisms

MVCC Implementation

Multi-Version Concurrency Control maintains data snapshots through undo logs, allowing non-locking consistent reads. Each transaction sees a point-in-time view of data based on transaction IDs, preventing read-write blocking while avoiding dirty reads.

Locking Granularity

Shared Locks (S-Locks): Enable concurrent read access without modification capabilities.

Exclusive Locks (X-Locks): Prevent other transactions from reading or modifying locked resources during write operations.

Row-Level Locking: InnoDB applies locks to index records, requiring index usage to avoid table-level escalation.

Table-Level Locking: MyISAM employs full-table locks with minimal overhead but reduced concurrency.

Lock Escalation

InnoDB may escalate row locks to table locks when:

• Queries fail to utilize indexes, necessitating full-table scans
• Non-unique indexes contain duplicate values exceeding approximately 50% of total records

Optimistic vs Pessimistic Concurrency

Pessimistic Locking: Acquires locks before data modification using SELECT FOR UPDATE or LOCK IN SHARE MODE, suitable for high-conflict environments but potentially reducing throughput.

Optimistic Locking: Assumes minimal conflict, using version columns or timestamps to detect concurrent modifications during commit:

UPDATE inventory 
SET quantity = quantity - 1, version = version + 1 
WHERE item_id = 101 AND version = 5;

Scalability Architectures

Large Dataset Optimization

Range Partitioning: Implement time-based or category-based filtering to limit scan scopes.

Read/Write Splitting: Configure master servers for mutations with replica servers handling read traffic.

Vertical Partitioning: Separate columns into different tables based on access patterns, reducing I/O for narrow queries but requiring application-level joins.

Horizontal Sharding: Distribute rows across multiple servers using strategies like:

• Hash-based distribution on primary keys
• Range-based alocation
• Directory-based mapping

Distributed ID Generation

Post-sharding identifier strategies include:

UUID: 128-bit unique values unsuitable for primary keys due to size and randomness, though appropriate for file naming.

Segment Allocation: Database sequences with pre-allocated ranges reduce contention.

Snowflake Algorithm: Twitter's timestamp-based distributed ID generation providing ordering guarantees without database coordination.

Leaf: Meituan's solution offering monotonic increment guarantees through database and ZooKeeper coordination.

Query Execution Pipeline

MySQL processes statements through distinct phases:

1. Connection Management: Authentication and session establishment via the connection handler.

2. Parsing: Lexical and syntactic analysis converts SQL into parse trees, detecting syntax errors.

3. Optimization: The cost-based optimizer determines execution plans, selecting indexes and join orders.

4. Execution: The engine invokes storage API methods, applying privilege checks before data retrieval or modification.

5. Result Return: Formatted result sets transmit to the client.

Note: Query caching was removed in MySQL 8.0 due to invalidation overhead and limited hit rates.

Data Type Considerations

CHAR allocates fixed-length storage, padding values with spaces. Suitable for consistently sized data (e.g., country codes, MD5 hashes) offering faster comparison operations.

VARCHAR uses variable-length storage with length prefixes, conserving space for irregular data but incurring slight processing overhead.