高性能MySQL数据库构建指南

需积分: 0 159 浏览量更新于2024-07-26 收藏 3.8MB PDF 举报

"高性能MySQL英文原版" 《高性能MySQL》是一本深入探讨如何构建和优化高性能MySQL数据库系统的专业书籍。本书涵盖了MySQL的多个关键领域，旨在帮助读者理解和掌握提高数据库性能的关键技术和策略。作为一本英文版的技术专著，它适合那些希望深入理解MySQL数据库管理、性能调优以及相关技术的专业人士阅读。书中可能包含以下重要知识点： 1. **MySQL体系结构**：介绍MySQL的基本架构，包括存储引擎（如InnoDB和MyISAM）、连接池、线程管理、查询缓存等，以及它们如何影响数据库性能。 2. **索引优化**：讲解如何创建和使用索引来提高查询速度，包括B-Tree、哈希索引、全文索引等，并讨论不同类型的索引在不同场景下的应用。 3. **查询优化**：分析SQL查询性能瓶颈，提供编写高效查询语句的指导，包括避免全表扫描、使用JOIN的技巧、子查询优化等。 4. **存储引擎优化**：针对InnoDB和MyISAM等存储引擎的特点，讲述如何选择合适的存储引擎以满足特定的性能需求。 5. **内存管理**：探讨MySQL服务器如何管理内存，以及如何调整相关参数以优化内存使用。 6. **并发控制与事务处理**：讲解并发控制机制，如锁定和MVCC（多版本并发控制），以及如何处理事务以确保数据的一致性和完整性。 7. **复制与高可用性**：介绍MySQL的主从复制机制，用于实现数据备份和故障恢复，以及如何设置和管理复制以提高系统可用性。 8. **性能监控与调优工具**：介绍用于监控MySQL性能的工具，如pt-query-digest、mysqldumpslow、Percona Toolkit等，以及如何利用这些工具进行性能分析和调优。 9. **分区与分片**：讨论如何通过分区和分片技术来水平扩展MySQL，以处理大规模数据和高并发场景。 10. **安全性与权限管理**：讲解如何设置用户权限，保护数据库安全，以及使用SSL等技术加密通信。 11. **性能基准测试**：介绍如何设计和执行基准测试，以评估MySQL性能并进行性能对比。此外，书中可能还会提到其他相关资源，如O'Reilly出版社的其他MySQL和PHP相关的书籍，以及.NET相关的技术资源，以供读者进一步学习和探索。这本书不仅适合数据库管理员（DBA）和开发人员，也对系统管理员和项目经理等需要理解和优化MySQL数据库性能的人员极具价值。通过学习本书，读者可以提升自己在数据库设计、管理和优化方面的专业技能，从而构建出更稳定、高效的数据库系统。

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

更多电子书教程下载请登陆http://down.zzbaike.com/ebook

本站提供的电子书教程均为网上搜集，如果该教程涉及或侵害到您的版权请联系我们。

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.

更多电子书教程下载请登陆http://down.zzbaike.com/ebook

本站提供的电子书教程均为网上搜集，如果该教程涉及或侵害到您的版权请联系我们。

xvi

Preface

Conventions Used in This Book

The following typographical conventions are used in this book:

Italic

Used for new terms, URLs, email addresses, usernames, hostnames, filenames,

file extensions, pathnames, directories, and Unix commands and utilities.

Constant width

Indicates elements of code, configuration options, database and table names,

variables and their values, functions, modules, the contents of files, or the out-

put from commands.

Constant width bold

Shows commands or other text that should be typed literally by the user. Also

used for emphasis in command output.

Constant width italic

Shows text that should be replaced with user-supplied values.

This icon signifies a tip, suggestion, or general note.

This icon indicates a warning or caution.

Using Code Examples

This book is here to help you get your job done. In general, you may use the code in

this book in your programs and documentation. You don’t need to contact us for

permission unless you’re reproducing a significant portion of the code. For example,

writing a program that uses several chunks of code from this book doesn’t require

permission. Selling or distributing a CD-ROM of examples from O’Reilly books does

require permission. Answering a question by citing this book and quoting example

code doesn’t require permission. Incorporating a significant amount of example code

from this book into your product’s documentation does require permission.

Examples are maintained on the site http://www.highperfmysql.com and will be

updated there from time to time. We cannot commit, however, to updating and test-

ing the code for every minor release of MySQL.

We appreciate, but don’t require, attribution. An attribution usually includes the

title, author, publisher, and ISBN. For example: “High Performance MySQL: Optimi-

zation, Backups, Replication, and More, Second Edition, by Baron Schwartz et al.

更多电子书教程下载请登陆http://down.zzbaike.com/ebook

本站提供的电子书教程均为网上搜集，如果该教程涉及或侵害到您的版权请联系我们。

Preface

xvii

If you feel your use of code examples falls outside fair use or the permission given

above, feel free to contact us at permissions@oreilly.com.

Safari® Books Online

When you see a Safari® Books Online icon on the cover of your

favorite technology book, that means the book is available online

through the O’Reilly Network Safari Bookshelf.

Safari offers a solution that’s better than e-books. It’s a virtual library that lets you

easily search thousands of top tech books, cut and paste code samples, download

chapters, and find quick answers when you need the most accurate, current informa-

tion. Try it for free at http://safari.oreilly.com.

How to Contact Us

Please address comments and questions concerning this book to the publisher:

O’Reilly Media, Inc.

1005 Gravenstein Highway North

Sebastopol, CA 95472

800-998-9938 (in the United States or Canada)

707-829-0515 (international or local)

707-829-0104 (fax)

We have a web page for this book, where we list errata, examples, and any addi-

tional information. You can access this page at:

http://www.oreilly.com/catalog/9780596101718/

To comment or ask technical questions about this book, send email to:

bookquestions@oreilly.com

For more information about our books, conferences, Resource Centers, and the

O’Reilly Network, see our web site at:

http://www.oreilly.com

You can also get in touch with the authors directly. Baron’s weblog is at http://www.

xaprb.com.

Peter and Vadim maintain two weblogs, the well-established and popular http://www.

mysqlperformanceblog.com and the more recent http://www.webscalingblog.com. You

can find the web site for their company, Percona, at http://www.percona.com.

Arjen’s company, OpenQuery, has a web site at http://openquery.com.au. Arjen also

maintains a weblog at http://arjen-lentz.livejournal.com and a personal site at http://

lentz.com.au.

更多电子书教程下载请登陆http://down.zzbaike.com/ebook

本站提供的电子书教程均为网上搜集，如果该教程涉及或侵害到您的版权请联系我们。

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

更多电子书教程下载请登陆http://down.zzbaike.com/ebook

本站提供的电子书教程均为网上搜集，如果该教程涉及或侵害到您的版权请联系我们。

剩余235页未读，继续阅读

freshcolours

粉丝: 2
资源: 1

高性能MySQL数据库构建指南

高性能MySQL英文原版3

[高性能MySQL(第2版)中文版].施瓦茨.扫描版

mysql_tutorial （mysql简明教程，英文原版的哦）

csdn 高性能mysql

高性能mysql 中文 pdf

高性能mysql电子版

高性能mysql中文

高性能mysql讲的什么

高性能mysql 中文版pdf

高性能mysql第四版 pdf

最新资源