Storage Engines

MySQL supports multiple storage engines, with InnoDB and MyISAM being the most commonly used. InnoDB is the default engine as of MySQL 5.5 due to its support for transactions, foreign keys, and crash recovery.

InnoDB vs MyISAM

InnoDB provides ACID-compliant trensactions, row-level locking, and referential integrity via foreign keys. MyISAM uses table-level locking, lacks transaction support, but offers faster read operations and full-text search capabilities (prior to MySQL 5.6).

Column Type Selection in Schema Design

Choose data types that closely match the expected data to minimize storage and improve performance. For example, use TINYINT instead of INT for small numeric ranges, and prefer fixed-length types like CHAR over VARCHAR when length is consistent.

Maximum Data Capacity of VARCHAR(M)

The VARCHAR(M) type can store up to M characters, where M ≤ 65,535. However, the actual limit depends on the row size (max ~65KB), character set (e.g., UTF8MB4 uses up to 4 bytes per character), and other columns in the table.

ACID Properties of Transactions

• Atomicity: All operation in a transaction succeed or none do.
• Consistency: Transactions bring the database from one valid state to another.
• Isolation: Concurrent transactions do not interfere.
• Durability: Committed changes persist even after system failure.

Concurrency Issues in Transactions

• Dirty Read: Reading uncommitted data from another transaction.
• Non-Repeatable Read: Same query returns different results within a transaction.
• Phantom Read: New rows appear in repeated range queries.

Indexing in MySQL

Indexes speed up data retrieval but slow down writes. Common types include B+ tree (default for InnoDB), hash (used internally), and full-text indexes. Proper indexing requires balancing selectivity and maintenance overhead.

Three-Star Index Design

A high-quality index satisfies:

1. All WHERE clause columns are in the index.
2. Columns are ordered to avoid sorting (ORDER BY).
3. The index covers all selected columns (covering index), avoiding table lookups.

Row Capacity in an InnoDB B+ Tree

Assuming a page size of 16KB and average row size of 1KB, a leaf node holds ~16 rows. With internal nodes storing ~1000 pointers (due to key + pointer size), a 3-level B+ tree can hold approximately 16 × 1000 × 1000 = 16 million rows.

Improving INSERT Performance

Bulk inserts benefit from increasing the bulk_insert_buffer_size, which caches index updates during large inserts into non-empty MyISAM tables (less relevant for InnoDB). Check and adjust the buffer:

SHOW VARIABLES LIKE 'bulk_insert_buffer_size';
SET SESSION bulk_insert_buffer_size = 125829120; -- 120 MB

Note: This setting primarily affects MyISAM; InnoDB relies more on innodb_buffer_pool_size and innodb_log_file_size.

Lock Types

• Global Lock: Locks the entire database (e.g., FLUSH TABLES WITH READ LOCK).
• Shared Lock (S): Allows concurrent reads but blocks writes.
• Exclusive Lock (X): Blocks both reads and writes.

Deadlock Handling

Deadlocks occur when two or more transactions mutually block each other. InnoDB automatically detects and rolls back one transaction. The timeout for lock waits is controlled by:

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

Default is 50 seconds. Reduce this value in high-concurrency environments to fail fast.

Read-Write Splitting

Replication Topologies

Common setups include:

• Single master → single slave
• Single master → multiple slaves
• Dual master (active-active)
• Cascading replication

Replication Mechanism

MySQL replication is asynchronous by default (semi-synchronous available from 5.7). Slaves pull binary logs (binlog) from the master, relay them through an I/O thread, and apply changes via SQL threads.

Sharding Strategies

Table Sharding

Distribute data across tables using a sharding key (e.g., user ID):

-- Example: 100 tables
SELECT * FROM order_85 WHERE user_id = 101;

In MyBatis:

<select id="fetchOrder" resultType="Order">
  SELECT * FROM order_${shardIndex}
  WHERE user_id = #{userId}
</select>

Database and Table Sharding

For 10 databases, each with 100 tables:

shard_key = 200885
total_shards = 10 * 100  # 1000
intermediate = shard_key % total_shards  # 885
db_index = intermediate // 100  # 8
table_index = intermediate % 100  # 85

Scalability Solutions for Large Datasets

• Caching: Use Redis with randomized TTLs, cache null results, employ Bloom filters, and implement rate limiting.
• Static Content Delivery: Serve HTML, JS, CSS, and images via CDN or NGINX.
• Database Tuning: Optimize schema, write efficient queries, add proper indexes, and consider partitioning.
• Hot/Cold Data Separation: Move inactive data out of primary storage.
• Batch Processing: Combine multiple reads; defer frequent writes to background jobs.
• Read-Write Splitting: Offload reads to replicas.
• Alternative Data Stores: Use MongoDB for unstructured data or Elasticsearch for complex searches.
• Service Decomposition: Separate application and data services; adopt microservices.

Sharding vs Partitioning vs Splitting

• Sharding (Horizontal Scaling): Distributes data across independent database instances (physical separation).
• Table Splitting: Breaks one logical table into multiple physical tables (same DB or different DBs).
• Partitioning: Divides a single table into segments (partitions) managed as one logical unit (e.g., by range, hash, or list).
• Database Splitting: Splits a monolithic database into multiple smaller databases based on business domains or load.