深入MySQL实战 (it-ebooks)（Z-Library）

# 深入MySQL实战 — Reading Guide ## 【One-Line Pitch】 A practical, battle-tested handbook from Alibaba Cloud engineers covering MySQL high availability, high-concurrency tuning, Java development practices, and query optimization — ideal for backend developers, DBAs, and architects preparing for extreme traffic scenarios like Double 11. --- ## 【Book Arc】 - **Opening (~0%–10%)**: Introduces MySQL Group Replication (MGR) 8.0 — architecture, single-primary vs. multi-primary modes, conflict detection via Paxos-based atomic broadcast, and flow control mechanisms. Establishes the foundation for high-availability cluster design. - **Early (~10%–25%)**: Dives into high-concurrency scenarios — capacity assessment, benchmark testing, full-link stress testing, instance parameter tuning, and AliSQL kernel optimizations (thread pools, statement queuing, inventory annotations) that deliver dramatic throughput gains. - **Early (~25%–35%)**: Shifts to Java development with RDS MySQL — MyBatis framework architecture, project scaffolding from scratch, and connection pool best practices (Druid/HikariCP) including configuration parameters and leak diagnosis. - **Middle (~35%–48%)**: Covers Java application performance diagnostics — memory leak detection (JMX, jmap, MAT), CPU profiling (Async-profiler, flame graphs), and network troubleshooting with TCP state machine fundamentals. - **Late (~48%–82%)**: Presents MySQL query optimization methodology — resource bottleneck analysis, monitoring metrics (CPU, IOPS, QPS/TPS), and a structured optimization workflow from SQL/index tuning to schema and hardware-level improvements. - **Ending (~82%–100%)**: Concludes with MySQL development conventions and RDS table/index optimization practices, plus a developer's perspective on AliSQL kernel 2020 features. --- ## 【Key Takeaways】 - **MGR uses Paxos-based atomic broadcast for distributed consensus** (Opening): Transactions commit only after majority agreement across cluster nodes, with conflict certification ensuring data consistency — critical for understanding MySQL high-availability trade-offs. - **Single-primary vs. multi-primary MGR modes serve different needs** (Opening): Single-primary (default) provides simple failover with one writable node; multi-primary allows writes on all nodes but requires application-level client failover handling via middleware like MySQL Router. - **Full-link stress testing is the "nuclear weapon" for capacity preparation** (Early): Alibaba runs 4000+ tests annually, discovering 400–700 issues per year before Double 11 — combining experience-based estimation, unit testing, and scenario-based full-chain validation. - **AliSQL kernel patches can boost hot-row update throughput 40x** (Early): Statement queuing, inventory annotations, and RETURNING-style results reduce lock contention and network round-trips for scenarios like flash-sale inventory deduction. - **Connection pools are non-negotiable for production Java apps** (Early): Without pooling, every query pays two-handshake costs (TCP + MySQL protocol), connection counts become uncontrolled, and TIME_WAIT accumulation can exhaust ports — Druid's leak detection parameters help diagnose unreturned connections. - **Memory leak diagnosis follows a systematic pattern** (Middle): Use JMX for heap monitoring, jmap for heap dumps, MAT for leak analysis, then correlate with source code — strong references in static variables are a common culprit that GC cannot reclaim. - **CPU profiling with flame graphs pinpoints hot code** (Middle): Async-profiler generates visual flame graphs where the widest top frames reveal the methods consuming most CPU — far more efficient than manually analyzing large jstack outputs. - **SQL and index tuning offers the best cost-to-effect ratio** (Late): The optimization pyramid shows SQL/index work costs least but delivers most; hardware upgrades cost most but deliver least — always start from the bottom of the pyramid. --- ## 【Reading Tips】 - **Skim the MGR architecture diagrams** (Opening): The visual flow of transaction certification and commit across nodes conveys more than the textual explanation — focus on understanding the conflict detection mechanism rather than memorizing component names. - **Deep-read the AliSQL kernel section** (Early): The four patches (thread pool, statement queue, statement return, inventory annotation) are the most actionable content for high-concurrency scenarios — study the performance comparison chart showing 40x improvement. - **Skip the MyBatis project scaffolding if you're not a Java developer** (Early): The connection pool best practices section is more universally valuable — especially the Druid parameter recommendations (test-on-borrow: false, test-while-idle: true). - **Use the Java diagnostics section as a reference manual** (Middle): The exact commands (jmap, jstack, Async-profiler) and tool workflows are worth bookmarking for real incidents rather than reading linearly. - **Pay special attention to the query optimization workflow** (Late): The four-step process (monitoring → diagnosis → business logic analysis → SQL optimization) is a reusable methodology applicable beyond MySQL. --- ## 【Coverage Limits】 Excerpts do not cover the RDS MySQL table/index optimization chapters (~82% onward) or the AliSQL kernel 2020 features in detail — these sections appear in the table of contents but lack sufficient source material for synthesis here. --- ##