高性能MySQL：第3版

需积分: 0 74 浏览量更新于2024-07-27 收藏 6.33MB PDF 举报

"O'Reilly.High.Performance.MySQL.3rd.Edition.Mar.2012" 《High Performance MySQL》第三版是由Baron Schwartz、Peter Zaitsev和Vadim Tkachenko共同编著的一本关于优化MySQL性能的专业书籍。本书主要针对MySQL数据库的高效运行和管理提供深入的技术指导，适合数据库管理员、开发人员以及对MySQL性能优化感兴趣的读者阅读。在本书中，作者们详细探讨了以下几个关键知识点： 1. **性能调优基础**：介绍如何评估MySQL的性能，包括监控工具的使用、查询分析和性能瓶颈识别。理解这些基础知识是提升数据库效率的第一步。 2. **存储引擎**：MySQL支持多种存储引擎，如InnoDB、MyISAM、Memory等。书中详细阐述了各种引擎的特性，帮助读者根据业务需求选择最适合的引擎。 3. **索引优化**：索引是提升查询速度的关键。书中详细介绍了如何创建和管理索引，包括单列索引、复合索引、覆盖索引等，以及如何避免索引失效的情况。 4. **查询优化**：讲解如何编写高效的SQL查询，包括使用EXPLAIN分析查询计划、避免全表扫描、使用JOIN操作的最佳实践等。 5. **并发和复制**：MySQL的并发控制和复制机制对于高可用性和扩展性至关重要。书中解释了事务处理、锁机制以及主从复制的工作原理，同时提供了优化这些特性的策略。 6. **架构设计**：讨论如何设计数据库架构以适应高并发和大数据量场景，包括分区、分片和读写分离等高级技术。 7. **性能监控和调优工具**：介绍了一系列用于监控MySQL性能的工具，如MySQL Enterprise Monitor、Percona Toolkit、pt-query-digest等，以及如何利用这些工具进行性能分析和问题诊断。 8. **备份与恢复**：详细阐述了MySQL的备份策略，包括热备份、冷备份和增量备份，以及如何在数据丢失或系统故障时快速恢复。 9. **安全性**：讲解了如何确保MySQL的安全性，包括用户权限管理、加密通信、防火墙配置等。 10. **最新版本特性**：由于本书是第三版，因此涵盖了MySQL的最新版本（可能是5.x或更高）的新特性和改进，例如InnoDB的性能增强、新的内存管理机制等。《High Performance MySQL》第三版不仅仅是技术指南，它还包含了许多实用案例和经验分享，可以帮助读者在实际工作中解决各种性能问题，提高数据库系统的整体效率。书中的内容深度适中，既适合初学者学习，也适合经验丰富的DBA参考。

E. Debugging Locks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 735

F. Using Sphinx with MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 745

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 771

xiv | Table of Contents

Foreword

I’ve been a fan of this book for years, and the third edition makes a great book even

better. Not only do world-class experts share that expertise, but they have taken the

time to update and add chapters with high-quality writing. While the book has many

details on getting high performance from MySQL, the focus of the book is on the pro-

cess of improvement rather than facts and trivia. This book will help you figure out

how to make things better, regardless of changes in MySQL’s behavior over time.

The authors are uniquely qualified to write this book, based on their experience, prin-

cipled approach, focus on efficiency, and commitment to improvement. By experi-

ence, I mean that the authors have been working on MySQL performance from the days

when it didn’t scale and had no instrumentation to the current period where things are

much better. By principled approach, I mean that they treat this like a science, first

defining problems to be solved and then using reason and measurement to solve those

problems.

I am most impressed by their focus on efficiency. As consultants, they don’t have the

luxury of time. Clients getting billed by the hour want problems solved quickly. So the

authors have defined processes and built tools to get things done correctly and effi-

ciently. They describe the processes in this book and publish source code for the tools.

Finally, they continue to get better at what they do. This includes a shift in concern

from throughput to response time, a commitment to understanding the performance

of MySQL on new hardware, and a pursuit of new skills like queueing theory that can

be used to understand performance.

I believe this book augurs a bright future for MySQL. As MySQL has evolved to support

demanding workloads, the authors have led a similar effort to improve the under-

standing of MySQL performance within the community. They have also contributed

directly to that improvement via XtraDB and XtraBackup. I continue to learn from them

and hope you take the time to do so as well.

—Mark Callaghan, Software Engineer, Facebook

Preface

We wrote this book to serve the needs of not just the MySQL application developer

but also the MySQL database administrator. We assume that you are already relatively

experienced with MySQL. We also assume some experience with general system ad-

ministration, networking, and Unix-like operating systems.

The second edition of this book presented a lot of information to readers, but no book

can provide complete coverage of a topic. Between the second and third editions, we

took notes on literally thousands of interesting problems we’d solved or seen others

solve. When we started to outline the third edition, it became clear that not only would

full coverage of these topics require three to five thousand pages, but the book still

wouldn’t be complete. After reflecting on this problem, we realized that the second

edition’s emphasis on deep coverage was actually self-limiting, in the sense that it often

didn’t teach readers how to think about MySQL.

As a result, this third edition has a different focus from the second edition. We still

convey a lot of information, and we still emphasize the same goals, such as reliability

and correctness. But we’ve also tried to imbue the book with a deeper purpose: we want

to teach the principles of why MySQL works as it does, not just the facts about how it

works. We’ve included more illustrative stories and case studies, which demonstrate

the principles in action. We build on these to try to answer questions such as “Given

MySQL’s internal architecture and operation, what practical effects arise in real usage?

Why do those effects matter? How do they make MySQL well suited (or not well suited)

for particular needs?”

Ultimately, we hope that your knowledge of MySQL’s internals will help you in situa-

tions beyond the scope of this book. And we hope that your newfound insight will help

you to learn and practice a methodical approach to designing, maintaining, and trou-

bleshooting systems that are built on MySQL.

How This Book Is Organized

We fit a lot of complicated topics into this book. Here, we explain how we put them

together in an order that makes them easier to learn.

xvii

A Broad Overview

Chapter 1, MySQL Architecture and History is dedicated to the basics—things you’ll

need to be familiar with before you dig in deeply. You need to understand how MySQL

is organized before you’ll be able to use it effectively. This chapter explains MySQL’s

architecture and key facts about its storage engines. It helps you get up to speed if you

aren’t familiar with some of the fundamentals of a relational database, including trans-

actions. This chapter will also be useful if this book is your introduction to MySQL but

you’re already familiar with another database, such as Oracle. We also include a bit of

historical context: the changes to MySQL over time, recent ownership changes, and

where we think it’s headed.

Building a Solid Foundation

The early chapters cover material we hope you’ll reference over and over as you use

MySQL.

Chapter 2, Benchmarking MySQL discusses the basics of benchmarking—that is, de-

termining what sort of workload your server can handle, how fast it can perform certain

tasks, and so on. Benchmarking is an essential skill for evaluating how the server be-

haves under load, but it’s also important to know when it’s not useful.

Chapter 3, Profiling Server Performance introduces you to the response time–oriented

approach we take to troubleshooting and diagnosing server performance problems.

This framework has proven essential to solving some of the most puzzling cases we’ve

seen. Although you might choose to modify our approach (we developed it by modi-

fying Cary Millsap’s approach, after all), we hope you’ll avoid the pitfalls of not having

any method at all.

In Chapters 4 through 6, we introduce three topics that together form the foundation

for a good logical and physical database design. In Chapter 4, Optimizing Schema and

Data Types, we cover the various nuances of data types and table design. Chapter 5,

Indexing for High Performance extends the discussion to indexes—that is, physical

database design. A firm understanding of indexes and how to use them well is essential

for using MySQL effectively, so you’ll probably find yourself returning to this chapter

repeatedly. And Chapter 6, Query Performance Optimization wraps the topics together

by explaining how MySQL executes queries and how you can take advantage of its

query optimizer’s strengths. This chapter also presents specific examples of many com-

mon classes of queries, illustrating where MySQL does a good job and how to transform

queries into forms that use its strengths.

Up to this point, we’ve covered the basic topics that apply to any database: tables,

indexes, data, and queries. Chapter 7, Advanced MySQL Features goes beyond the

basics and shows you how MySQL’s advanced features work. We examine topics such

as partitioning, stored procedures, triggers, and character sets. MySQL’s implementa-

tion of these features is different from other databases, and a good understanding of

xviii | Preface

剩余825页未读，继续阅读

ryansyj13

粉丝: 0
资源: 4

高性能MySQL：第3版

Java文件上传：Commons FileUpload与com.oreilly.servlet对比分析

《Linux.O_Reilly.SED和AWK手册》第二版：编辑与文本处理利器

掌握Python编程：O'Reilly Learning Python 3rd Edition精髓

OReilly.Head.First.Servlets.and.JSP.2nd.Edition.Mar.2008.pdf 带书签

OReilly.PHP.Cookbook.3rd.Edition

OReilly.Head.First.iPhone.and.iPad.Development.3rd.Edition.epub

OReilly.Learning.React.2nd.Edition.2020.6

OReilly.CSS.Cookbook.2nd.Edition.Oct.2006.chm

Oreilly.Best.iPhone.Apps.2nd.Edition.Oct.2010.rar

OReilly.JavaScript.The.Definitive.Guide.5th.Edition.chm

最新资源