高性能MySQL（第4版） ( etc.) (Z-Library)

# High Performance MySQL (4th Edition) — Reading Guide ## 【One-Line Pitch】 The definitive practical handbook for running MySQL at scale — covering everything from storage engine internals and indexing strategy to hardware tuning, schema migrations, and SRE-style monitoring — essential reading for DBAs, backend engineers, and architects who operate MySQL in production. ## 【Book Arc】 - **Opening (~0%–11%)**: Foundations of MySQL architecture — locking mechanisms, transaction semantics, InnoDB as the default engine, and the shift toward defining service-level objectives (SLI/SLO/SLA) as the starting point for performance work. - **Early (~11%–28%)**: Monitoring and hardware — using Performance Schema for query and memory inspection, then moving to operating system and hardware optimization including RAID levels, network configuration, and OS-level diagnostics with vmstat/iostat. - **Middle (~28%–44%)**: InnoDB deep dive and schema management — buffer pool tuning, purge/undo log management, advanced InnoDB settings, data type selection (CHAR vs. VARCHAR, BIT, SET), and the critical practice of running online schema changes in production. - **Middle (~44%–61%)**: Indexing strategy — index types, prefix indexes and selectivity, the importance of column order in composite indexes, and why ORM users still must understand indexes. - **Late (~61%–end)**: Query optimization — understanding the full query lifecycle, reading execution plans, avoiding unnecessary scans, optimizing joins, and the eternal trade-off between "fast, accurate, and simple." ## 【Key Takeaways】 - **Locking is a balance between overhead and safety** (Early): MySQL offers multiple storage engines with different lock granularities; InnoDB's row-level locking with MVCC is the default choice for good reason, but lock management itself consumes resources that must be accounted for in high-concurrency designs. - **SLOs come before performance tuning** (Early): Define SLIs (what to measure), SLOs (acceptable ranges), and SLAs before optimizing anything — this turns performance work from a technical exercise into a business-aligned practice and guides prioritization across teams. - **Performance Schema is your window into server internals** (Early): Enable specific instruments to inspect SQL statements, memory usage, and locks; the `threads` table is the key to correlating processlist IDs with internal THREAD_IDs when diagnosing blocking issues. - **Hardware and OS choices set your performance ceiling** (Early): RAID 0 is never acceptable for production (failure probability exceeds single disks), RAID 5 becomes viable with SSDs, and simple network issues like 1% packet loss or slow DNS resolution can devastate MySQL performance — enable `skip_name_resolve` on production servers. - **InnoDB tuning requires understanding its internals** (Middle): The buffer pool, purge threads, and I/O capacity settings (`innodb_io_capacity`, `innodb_buffer_pool_instances`) directly impact scalability; a growing undo log with a long history list signals purge lag that must be addressed before it fills the disk. - **Schema changes in production need online tools** (Middle): Even with MySQL 8.0's expanded INPLACE/INSTANT DDL support, large tables may still require external tools like `pt-online-schema-change` or `gh-ost` for throttled, non-blocking migrations — and CI integration should enforce schema-change discipline. - **Index selectivity drives query performance** (Middle): Prefix indexes save space but reduce selectivity; measure cardinality to find the right prefix length, and remember that composite index column order matters because MySQL only uses the leftmost prefix effectively. - **Query optimization starts with understanding the lifecycle** (Late): Most slow queries involve unnecessary operations, repeated work, or overly slow steps; use `SHOW FULL PROCESSLIST` states to identify bottlenecks, and remember that `COUNT()` queries are inherently hard to optimize — you must choose between fast, accurate, and simple. ## 【Reading Tips】 - **Skim the opening chapters on locking and transactions** if you already know ACID basics — the key insight is that InnoDB is the default for good reason, and MVCC with gap locks prevents phantom reads at REPEATABLE READ. - **Deep-read the Performance Schema chapter** (Early) — it's dense but pays off immediately; focus on the `events_statements_history` tables and the `threads` table, which are the most practically useful for daily diagnosis. - **The hardware chapter (Early) is skimmable for cloud users** but don't skip the network section — DNS resolution and port range issues are common even in managed environments. - **Pay special attention to the InnoDB tuning section (Middle)** — the discussion of purge lag and `innodb_max_purge_lag` is rare practical wisdom you won't find in official docs; the schema-change tooling comparison is also worth careful reading. - **The indexing and query optimization chapters (Middle–Late) deserve multiple passes** — the examples build on each other, and the discussion of why ORMs can't save you from understanding indexes is essential for application developers. ## 【Coverage Limits】 This guide synthesizes the first ~61% of the book (through query optimization fundamentals). The excerpts do not cover the later chapters on replication, backup/recovery, disaster recovery, or regulatory compliance — these are referenced but not detailed in the available material. ##