Practical utilities
The article explains why a single MySQL server eventually hits read, write, or availability limits, outlines a step‑by‑step evolution—from SQL tuning and caching to read‑write splitting, high‑availability setups, and finally vertical or horizontal sharding—while warning against premature distribution.
This article walks through every stage a MySQL statement undergoes—from client connection, authentication, and (now‑removed) query cache, through parsing, optimization, execution, and storage‑engine access—highlighting the roles of InnoDB vs MyISAM, logging mechanisms, and best‑practice table design tips.
This article explains what MySQL's binary log (binlog) is, how it differs from the redo log, the three binlog formats, the full execution flow of an UPDATE statement, why MySQL uses a two‑phase commit, and how crash recovery ensures data consistency across primary and replica servers.
The article explains how to make MySQL do less work by reducing scanned rows, avoiding back‑and‑forth lookups with covering indexes, and minimizing sorting or temporary tables, while detailing common index pitfalls, deep pagination tricks, COUNT usage, ORDER BY/filesort handling, JOIN strategies, and step‑by‑step methods for finding and fixing slow SQL statements.
This article walks through MySQL InnoDB's five‑level storage hierarchy—record, page, extent, segment, tablespace—explaining page internals, record chaining, page directories, headers, trailers, extent and segment management, and the system tablespace and data dictionary across MySQL 5.7 and 8.0.
This article explains why MySQL needs locks beyond MVCC snapshot reads, categorizes lock granularity, mode and design, details global, table and row‑level locks—including intention, gap, next‑key, and implicit locks—then compares pessimistic and optimistic locking, shows how to choose the right lock, and covers deadlock causes, detection, and best‑practice avoidance.
The article explains why InnoDB must cache disk pages in a Buffer Pool, details the pool's internal structure, eviction policies, dirty‑page flushing mechanisms, multi‑instance and chunk sizing for large workloads, and how to monitor its health with SHOW ENGINE INNODB STATUS.
This article walks through MySQL's EXPLAIN output column by column, explaining id, table, select_type, type rankings, possible_keys, key, key_len, ref, rows, filtered, and Extra, while demonstrating each concept with concrete SQL examples, JSON‑formatted plans, and SHOW WARNINGS insights.
The article explains how InnoDB implements indexes as B+ trees, detailing their evolution from simple page directories, the differences between clustered and secondary indexes, the costs of indexing, and the specific query patterns and scenarios where such indexes are effective.
