高性能MySQL：第二版精华

需积分: 0 78 浏览量更新于2024-07-29 收藏 5.63MB PDF 举报

"High Performance MySQL 第二版是关于优化和提升MySQL数据库性能的经典著作，由O'Reilly出版社出版。这本书深入探讨了如何管理、使用和优化MySQL，提供了丰富的实践指南和解决方案。" 在《High Performance MySQL》第二版中，读者可以学习到一系列关键知识点： 1. **性能优化基础**：了解如何通过查询优化、索引策略和存储引擎选择来提升MySQL的运行速度。这包括理解执行计划、避免全表扫描以及正确使用JOIN操作。 2. **存储引擎**：深入探讨InnoDB和MyISAM等主要存储引擎的工作原理和优缺点，以及如何根据业务需求选择合适的引擎。 3. **查询优化**：学习编写高效的SQL查询，包括使用EXPLAIN分析查询执行过程，避免子查询和过度复杂的查询结构。 4. **索引策略**：理解如何设计和使用索引来提高查询速度，包括多列索引、覆盖索引和唯一性索引的重要性。 5. **复制与高可用性**：讨论MySQL的主从复制机制，以及如何设置和维护高可用性的数据库集群，确保数据的安全性和服务的连续性。 6. **性能监控与调优**：学习如何监控MySQL的性能指标，如CPU使用率、磁盘I/O和内存使用，以及如何根据这些信息进行系统调优。 7. **备份与恢复**：掌握定期备份的最佳实践，了解不同的备份方法（如增量备份、热备份）以及如何在灾难情况下恢复数据。 8. **安全性**：理解MySQL的安全特性，如用户权限管理、加密连接和防止SQL注入攻击的策略。 9. **扩展性**：讨论MySQL的水平和垂直扩展策略，包括分区、分片和使用读写分离技术处理大数据和高并发场景。 10. **性能工具**：介绍各种用于诊断和优化MySQL性能的工具，如mysqldump、pt-tools和Percona Toolkit。此外，O'Reilly出版社还提供了其他与MySQL相关的书籍，如《Managing and Using MySQL》、《MySQL Cookbook》和《MySQL Pocket Reference》，这些资源可以作为《High Performance MySQL》的补充，帮助读者更全面地理解和掌握MySQL技术。除了书籍，O'Reilly的在线资源中心dotnet.oreilly.com提供了.NET技术和Web服务的样本章节和代码示例，以及ONDotnet.com的独立报道，为.NET程序员提供了最新的技术信息。O'Reilly的会议和Safari Bookshelf在线参考库则提供了更多学习和交流的平台，使得专业人士能够及时获取并掌握行业动态和专业知识。

xiv

Preface

Making Your Application Reliable

The best-designed, most scalable architecture in the world is no good if it can’t sur-

vive power outages, malicious attacks, application bugs or programmer mistakes,

and other disasters.

In Chapter 11, Backup and Recovery, we discuss various backup and recovery strate-

gies for your MySQL databases. These strategies will help minimize your downtime

in the event of inevitable hardware failure and ensure that your data survives such

catastrophes.

Chapter 12, Security, provides you with a firm grasp of some of the security issues

involved in running a MySQL server. More importantly, we offer many suggestions

to allow you to prevent outside parties from harming the servers you’ve spent all this

time trying to configure and optimize. We explain some of the rarely explored areas

of database security, showing both the benefits and performance impacts of various

practices. Usually, in terms of performance, it pays to keep security policies simple.

Miscellaneous Useful Topics

In the last few chapters and the book’s appendixes, we delve into several topics that

either don’t “fit” in any of the earlier chapters or are referenced often enough in mul-

tiple chapters that they deserve a bit of special attention.

Chapter 13, MySQL Server Status shows you how to inspect your MySQL server.

Knowing how to get status information from the server is important; knowing what

that information means is even more important. We cover

SHOW INNODB STATUS in par-

ticular detail, because it provides deep insight into the operations of the InnoDB

transactional storage engine.

Chapter 14, Tools for High Performance covers tools you can use to manage MySQL

more efficiently. These include monitoring and analysis tools, tools that help you

write queries, and so on. This chapter covers the Maatkit tools Baron created, which

can enhance MySQL’s functionality and make your life as a database administrator

easier. It also demonstrates a program called innotop, which Baron wrote as an easy-

to-use interface to what your MySQL server is presently doing. It functions much like

the Unix top command and can be invaluable at all phases of the tuning process to

monitor what’s happening inside MySQL and its storage engines.

Appendix A, Transferring Large Files, shows you how to copy very large files from

place to place efficiently—a must if you are going to manage large volumes of data.

Appendix B, Using EXPLAIN, shows you how to really use and understand the all-

important

EXPLAIN command. Appendix C, Using Sphinx with MySQL, is an intro-

duction to Sphinx, a high-performance full-text indexing system that can comple-

ment MySQL’s own abilities. And finally, Appendix D, Debugging Locks, shows you

Preface

how to decipher what’s going on when queries are requesting locks that interfere

with each other.

Software Versions and Availability

MySQL is a moving target. In the years since Jeremy wrote the outline for the first edi-

tion of this book, numerous releases of MySQL have appeared. MySQL 4.1 and 5.0

were available only as alpha versions when the first edition went to press, but these

versions have now been in production for years, and they are the backbone of many of

today’s large online applications. As we completed this second edition, MySQL 5.1

and 6.0 were the bleeding edge instead. (MySQL 5.1 is a release candidate, and 6.0 is

alpha.)

We didn’t rely on one single version of MySQL for this book. Instead, we drew on

our extensive collective knowledge of MySQL in the real world. The core of the book

is focused on MySQL 5.0, because that’s what we consider the “current” version.

Most of our examples assume you’re running some reasonably mature version of

MySQL 5.0, such as MySQL 5.0.40 or newer. We have made an effort to note fea-

tures or functionalities that may not exist in older releases or that may exist only in

the upcoming 5.1 series. However, the definitive reference for mapping features to

specific versions is the MySQL documentation itself. We expect that you’ll find your-

self visiting the annotated online documentation (http://dev.mysql.com/doc/) from

time to time as you read this book.

Another great aspect of MySQL is that it runs on all of today’s popular platforms:

Mac OS X, Windows, GNU/Linux, Solaris, FreeBSD, you name it! However, we are

biased toward GNU/Linux

and other Unix-like operating systems. Windows users

are likely to encounter some differences. For example, file paths are completely dif-

ferent. We also refer to standard Unix command-line utilities; we assume you know

the corresponding commands in Windows.

†

Perl is the other rough spot when dealing with MySQL on Windows. MySQL comes

with several useful utilities that are written in Perl, and certain chapters in this book

present example Perl scripts that form the basis of more complex tools you’ll build.

Maatkit is also written in Perl. However, Perl isn’t included with Windows. In order

to use these scripts, you’ll need to download a Windows version of Perl from

ActiveState and install the necessary add-on modules (

DBI and DBD::mysql) for

MySQL access.

* To avoid confusion, we refer to Linux when we are writing about the kernel, and GNU/Linux when we are

writing about the whole operating system infrastructure that supports applications.

† You can get Windows-compatible versions of Unix utilities at http://unxutils.sourceforge.net or http://

gnuwin32.sourceforge.net.

xviii

Preface

Acknowledgments for the Second Edition

Sphinx developer Andrew Aksyonoff wrote Appendix C, Using Sphinx with MySQL

We’d like to thank him first for his in-depth discussion.

We have received invaluable help from many people while writing this book. It’s

impossible to list everyone who gave us help—we really owe thanks to the entire

MySQL community and everyone at MySQL AB. However, here’s a list of people

who contributed directly, with apologies if we’ve missed anyone: Tobias Asplund,

Igor Babaev, Pascal Borghino, Roland Bouman, Ronald Bradford, Mark Callaghan,

Jeremy Cole, Britt Crawford and the HiveDB Project, Vasil Dimov, Harrison Fisk,

Florian Haas, Dmitri Joukovski and Zmanda (thanks for the diagram explaining

LVM snapshots), Alan Kasindorf, Sheeri Kritzer Cabral, Marko Makela, Giuseppe

Maxia, Paul McCullagh, B. Keith Murphy, Dhiren Patel, Sergey Petrunia, Alexander

Rubin, Paul Tuckfield, Heikki Tuuri, and Michael “Monty” Widenius.

A special thanks to Andy Oram and Isabel Kunkle, our editor and assistant editor at

O’Reilly, and to Rachel Wheeler, the copyeditor. Thanks also to the rest of the

O’Reilly staff.

From Baron

I would like to thank my wife Lynn Rainville and our dog Carbon. If you’ve written a

book, I’m sure you know how grateful I am to them. I also owe a huge debt of grati-

tude to Alan Rimm-Kaufman and my colleagues at the Rimm-Kaufman Group for

their support and encouragement during this project. Thanks to Peter, Vadim, and

Arjen for giving me the opportunity to make this dream come true. And thanks to

Jeremy and Derek for breaking the trail for us.

From Peter

I’ve been doing MySQL performance and scaling presentations, training, and con-

sulting for years, and I’ve always wanted to reach a wider audience, so I was very

excited when Andy Oram approached me to work on this book. I have not written a

book before, so I wasn’t prepared for how much time and effort it required. We first

started talking about updating the first edition to cover recent versions of MySQL,

but we wanted to add so much material that we ended up rewriting most of the

book.

This book is truly a team effort. Because I was very busy bootstrapping Percona,

Vadim’s and my consulting company, and because English is not my first language,

we all had different roles. I provided the outline and technical content, then I

reviewed the material, revising and extending it as we wrote. When Arjen (the former

head of the MySQL documentation team) joined the project, we began to fill out the

剩余709页未读，继续阅读

hmilygxl

粉丝: 0
资源: 2

高性能MySQL：第二版精华

High Performance MySQL 2nd Edition Jun 2008

ASP.NET Core 2 High Performance(2nd)_Code 源码

SpringBoot 20道面试题和答案（2025最新版）.docx

【电力负荷预测】基于matlab灰狼算法优化长短时记忆网络GWO-LSTM电力负荷预测【含Matlab源码 1518期】.zip

SpringBoot-548大学生社团活动平台.zip

线程111111111111111

基于Matlab/Simscape的动静摩擦切换模型（速度转换法）

03_AUTOSAR_in_Practice_InputOutput.pptx

SLAM-Multi-Robot多机器人SLAM系统设计-优质项目实战.zip

动力电池管理系统仿真BMS Battery Simulink电池平衡控制策略模型

最新资源