Efficient MySQL Performance (Final Release) (Daniel Nichter)（Z-Library）

# Efficient MySQL Performance — Reading Guide ## 【One-Line Pitch】 A practical, mid-level guide for software engineers who know MySQL basics but need to move beyond them—teaching you how to analyze query performance, design effective indexes, and understand the metrics that actually matter, without diving into DBA-level internals. ## 【Book Arc】 - **Opening (~0%–9%)**: Establishes query response time as the "North Star" of MySQL performance and introduces the core query metrics—query time, lock time, query count, rows examined, and rows sent—along with the event hierarchy (transactions → statements → stages → waits) and the importance of metadata like EXPLAIN plans. - **Early (~16%–28%)**: Explains why indexes are the single most important factor in MySQL performance, using a simple example table to illustrate how InnoDB tables are really indexes, how the leftmost prefix requirement works, and how indexes optimize WHERE, GROUP BY, and ORDER BY clauses. - **Early-to-Middle (~28%–38%)**: Covers joins and index management—how MySQL joins tables in optimal order (not query order), why full joins are the worst thing a query can do, and the dangers of too many, duplicate, or unused indexes, plus tools like pt-duplicate-key-checker and the sys schema to find them. - **Middle (~38%–47%)**: Shifts to indirect query optimization—optimizing around the query rather than the query itself—covering data size, working set size and memory allocation (10% of total data as a starting point), why less data is better, and specific optimizations like ORDER BY…LIMIT. ## 【Key Takeaways】 - **Query response time is the North Star of MySQL performance** (Opening): All other metrics—lock time, rows examined, rows sent—are subordinate to response time. Lock time is included in query time, but beware: it's accurate only in the slow query log, not the Performance Schema. - **Query metrics alone are insufficient; you need metadata** (Opening): A complete query analysis requires at least the EXPLAIN plan and table structures. The "low and slow" combination (few executions, high response time) often signals a manually executed or programmatically generated query worth investigating. - **Indexes provide the greatest performance leverage** (Early): Hardware gives the least leverage, optimizations more, and indexes the most—without them, performance is limited to brute force. The book's example of a team buying faster cloud hardware until costs became stratospheric illustrates this perfectly. - **An InnoDB table is really an index** (Early): Understanding that tables are structured as indexes (primary key + secondary indexes) is fundamental. Scanning a secondary index in order doesn't guarantee sequential reads—primary key lookups are almost certainly random reads. - **The leftmost prefix requirement governs index usage** (Early): Indexes only help when queries use a leftmost prefix of the index columns. Violating this (e.g., WHERE on column b when the index is on a, b) forces MySQL to use temporary tables and lose the optimization. - **Filesort is usually not the root cause of slow queries** (Early): Despite its reputation, sorting rows is very fast. MySQL can optimize ORDER BY using an ordered index in three ways, and EXPLAIN ANALYZE (MySQL 8.0.18+) can measure the real-time penalty. - **Too many indexes hurt write performance** (Middle): Each index must be checked, updated, and potentially reorganized on every write. Use invisible indexes (MySQL 8.0+) to verify an index is truly unused before dropping it. - **Less data is better for everything** (Middle): Data size affects performance, management, and cost. Working set size—not total data size—determines memory needs; when it exceeds available memory, even indexes stop helping and sharding becomes necessary. ## 【Reading Tips】 - **Skim the opening chapter's event hierarchy** (~0%–9%): The transaction → statement → stage → wait hierarchy is useful context, but you don't need to memorize it. Focus instead on understanding what each query metric means and its limitations. - **Deep-read Chapter 2 on indexes** (~16%–38%): This is the heart of the book. Work through the table elem examples carefully—they build on each other. Pay special attention to the leftmost prefix requirement and how EXPLAIN output (rows is an estimate, not actual matches) can mislead. - **Use the join section as a reference** (~34%–38%): The key insight—MySQL joins tables in optimal order, not query order—is worth internalizing. The full join warning is critical: it's the single worst thing a query can do. - **Skim the data storage checklist** (~44%–47%): The "less data is better" principle and the 10% memory allocation guideline are practical heuristics worth remembering, but the storage audit details are less critical for everyday work. - **Take away the tooling** (throughout): pt-duplicate-key-checker, sys.schema_unused_indexes, SHOW WARNINGS, and invisible indexes are immediately actionable. Note these tools as you encounter them. ## 【Coverage Limits】 The excerpts cover roughly the first half of the book (through Chapter 3 on data). Later chapters on replication, transactions, row locking, and cloud performance are mentioned in the blurb but not covered in this guide. ##