Why MySQL Uses B+ Trees for Indexes and How to Design Effective Indexes

MySQL Index Data Structure: Why B+ Tree?

MySQL uses B+ trees for indexes because they keep the tree height low, reduce disk I/O, and store all key values in the leaf nodes, enabling fast range queries.

What Is the Left‑most Prefix Principle?

When a composite index is built, MySQL can only use the index starting from its leftmost column.

Example: a composite index on (major, class) stores values in a B+ tree sorted first by major then by class.

How to Design Indexes?

Use covering indexes to avoid table look‑ups.

Create unique indexes for columns that have unique business meaning.

Limit joins to three tables; ensure join columns are indexed and have identical data types.

Specify index length for VARCHAR columns based on selectivity (e.g., 20‑character prefix often gives >90% selectivity).

Avoid left‑most or full wildcard searches on indexed columns.

Target at least range type in EXPLAIN; ref is better, const best.

Place the most selective column on the left side of a composite index; for mixed equality and range conditions, put the equality column first.

Prevent implicit type conversion that can invalidate indexes.

MyBatis Cache Basics

First‑level cache lives in a SqlSession and is cleared on commit.

Second‑level cache is mapper‑level and disabled by default.

MySQL Master‑Slave Replication

Master writes changes to the binary log.

Slave I/O thread fetches the binary log and writes it to a relay log.

Slave SQL thread reads the relay log and applies the changes to the slave database.

Sharding: When and How

Split tables when rows exceed 5 million or size exceeds 2 GB. Strategies include vertical vs. horizontal splitting and various sharding middleware such as Sharding‑Sphere, TDDL, and Mycat.

Common sharding keys: modulo, hash, range, or fixed‑digit extraction; also separate large or rarely used columns into dedicated tables.

Querying by Phone Number When Data Is Sharded by userId

Create a separate phone‑to‑userId lookup table, retrieve the userId first, then query the appropriate shard using that userId.