| 失效链接处理 |
|
MySQL 8 Query Performance Tuning PDF 下载
本站整理下载：
提取码：4ks3
相关截图：
主要内容：
Introduction
MySQL performance tuning is a very large subject that takes years to master. The length
of this book bears testimony to that, even with the scope reduced to focus on queryrelated topics. There are no simple recipes for improving performance, and often a
solution requires understanding the relationship between various parts not only within
MySQL but also for other parts of the stack. If you feel that it is overwhelming to get
started, you are far from the first one, but do not despair as it is with performance tuning
like with other skills that practice makes perfect.
The aim of this book is to help you to get a good start on the journey to become
skilled at improving the performance of the queries executing on your MySQL instances.
As said, there are no simple recipes, so the best way forward is to learn how the various
components involved in performance tuning work. That is what the bulk of this book
tries to do as well as giving examples of what to look for and how to perform common
tasks. On the other hand, the scope has been limited to MySQL itself, so there is very
limited discussion about the operating system, file system, and hardware levels.
MySQL is famous for its support for storage engines. However, this book exclusively
covers the InnoDB storage engine except for the discussion of internal temporary tables.
With respect to MySQL versions, only MySQL 8 is considered. That said, most of the
discussion also applies to older versions of MySQL, and in general it is mentioned when
a feature is new in MySQL 8 or that MySQL 8 has a different behavior compared to older
versions.
Book Audience
The book has been written for developers and database administrators who have
experience working with MySQL and want to expand their knowledge into the realm of
query performance tuning. No prior experience with performance tuning is required.
http://www.rejoiceblog.com/
xxx
Examples and the Book’s GitHub Repository
I have tried to add as many examples and outputs from examples as possible. Some of
the examples are quite short, some are quite long. In either case, I hope you are able to
follow them and reproduce the effect or result demonstrated. At the same time, please
do bear in mind that by nature there is often randomness involved (sometimes even
explicitly as with the index statistics), and the exact outcome of the examples may
depend on how the tables and data have been used prior to the example. In other words,
you may get different results even if you did everything right. This particularly applies to
numbers that relate to index statistics, timings, and the like.
Examples that are long or produce outputs that are either long or wide have been
added to this book’s GitHub repository. This includes some of the figures that may be
hard to read with the image size that the page format allows. The link to the repository
can be found from the book’s homepage at www.apress.com/gp/book/9781484255834.
The GitHub repository will also be the home of the errata for the book once that
is created. I will use the errata not only to communicate errors in the book but also to
provide updates when bug fixes and new features in MySQL 8 cause changes to book
content. If necessary, I will also update the examples in the repository to reflect the
behavior in the newer releases. For these reasons, I recommend that you keep an eye on
the repository.
Book Structure
The book is divided into six parts with a total of 27 chapters. I have attempted to
keep each chapter relatively self-contained with the aim that you can use the book
as a reference book. The drawback of this choice is that there is some duplication of
information from time to time. An example is Chapter 18 which describes the more
theoretical side of locks and how to monitor locks, and Chapter 22 which provides
practical examples of investigating lock contention. Chapter 22 naturally draws on
the information in Chapter 18, so some of the information is repeated. This was a
deliberate choice, and I hope it helps you reduce the amount of page flipping to find the
information you need.
The six parts progressively move you through the topics starting with some basic
background and finishing with more solution-oriented tasks. The first part starts out
discussing the methodology, benchmarks, and test data. The second part focuses on the
Introduction
http://www.rejoiceblog.com/
xxxi
sources of information such as the Performance Schema. The third part covers the tools
such as MySQL Shell used in this book. The fourth part provides the theoretical background
used in the last two parts. The fifth part focuses on analyzing queries, transactions,
and locks. Finally, the sixth part discusses how to improve performance through the
configuration, query optimization, replication, and caching. There are cases where some
content is a little out of place, like all replication information is contained in a single chapter.
Part I: Getting Started
Part I introduces you to the concepts of MySQL query performance tuning. This includes
some high-level considerations, of which some are not unique to MySQL (but are of
course discussed in the context of MySQL). The four chapters are
1. MySQL Performance Tuning – This introductory chapter covers
some high-level concepts of MySQL performance tuning such as
the importance of considering the whole stack and the lifecycle of
a query.
2. Query Tuning Methodology – It is important to work in an
effective way to solve performance problems. This chapter
introduces a methodology to work effectively and emphasizes the
importance of working proactively rather than doing firefighting.
3. Benchmarking with Sysbench – It is often necessary to use
benchmarks to determine the effect of a change. This chapter
introduces benchmarking in general and specifically discusses
the Sysbench tool including how to create your own custom
benchmarks.
4. Test Data – The book mostly uses a few standard test databases
which are introduced in this chapter.
Part II: Sources of Information
MySQL exposes information about the performance through a few sources. The
Performance Schema, the sys schema, the Information Schema, and the SHOW
statement are introduced in each their chapter. There are only relatively few examples
of using these sources in this part; however, these four sources of information are used
Introduction
http://www.rejoiceblog.com/
xxxii
extensively in the remainder of the book. If you are not already familiar with them, you
are strongly encouraged to read this part. Additionally, the slow query log is covered. The
five chapters are
5. The Performance Schema – The main source of performancerelated information in MySQL is – as the name suggests – the
Performance Schema. This chapter introduces the terminology,
the main concepts, the organization, and the configuration.
6. The sys Schema – The sys schema provides reports through
predefined views and utilities in stored functions and programs.
This chapter provides an overview of what features are available.
7. The Information Schema – If you need metadata about the MySQL
and the databases, the Information Schema is the place to look. It
also includes important information for performance tuning such
as information about indexes, index statistics, and histograms.
This chapter provides an overview of the views available in the
Information Schema.
8. SHOW Statements – The SHOW statements are the oldest way to
obtain information ranging from which queries are executing to
schema information. This chapter relates the SHOW statements to
the Information Schema and Performance Schema and covers in
somewhat more detail the SHOW statements without counterparts
in the two schemas.
9. The Slow Query Log – The traditional way to find slow queries
is to log them to the slow query log. This chapter covers how to
configure the slow query log, how to read the log events, and how
to aggregate the events with the mysqldump utility.
Part III: Tools
MySQL provides several tools that are useful when performing the daily work as well as
specialized tasks. This part covers three tools ranging from monitoring to simple query
execution. This book uses Oracle’s dedicated MySQL monitoring solution (requires
commercial subscription but is also available as a trial) as an example of monitoring.
Introduction
http://www.rejoiceblog.com/
xxxiii
Even if you are using other monitoring solutions, you are encouraged to study the
examples as there will be a large overlap. These three tools are also used extensively in
the remainder of the book. The three chapters in this part are
10. MySQL Enterprise Monitor – Monitoring is one of the most
important aspects of maintaining a stable and well-performing
database. This chapter introduces MySQL Enterprise Monitor
(MEM) and shows how you can install the trial and helps you
navigate and use the graphical user interface (GUI).
11. MySQL Workbench – MySQL provides a graphical user interface
through the MySQL Workbench product. This chapter shows
how you can install and use it. In this book, MySQL Workbench is
particularly important for its ability to create diagrams – known as
Visual Explain – representing the query execution plans.
12. MySQL Shell – One of the newest tools around from Oracle for
MySQL is MySQL Shell which is a second-generation commandline client with support for executing code in both SQL, Python,
and JavaScript. This chapter gets you up to speed with MySQL
Shell and teaches you about its support for using external code
modules, its reporting infrastructure, and how to create custom
modules, reports, and plugins.
Part IV: Schema Considerations and the Query Optimizer
In Part IV, there is a change of pace, and the focus moves to the topics more directly
related to performance tuning starting with topics related to the schema, the query
optimizer, and locks. The six chapters are
13. Data Types – In relational databases, each column has a data type.
This data type defines which values can be stored, which rules
apply when comparing two values, how the data is stored, and
more. This chapter covers the data types available in MySQL and
gives guidance on how to decide which data types to use.
Introduction
http://www.rejoiceblog.com/
xxxiv
14. Indexes – An index is used to locate data, and a good indexing
strategy can greatly improve the performance of your queries.
This chapter covers the index concepts, considerations about
indexes, index types, index features, and more. It also includes a
discussion on how InnoDB uses indexes and how to come up with
an indexing strategy.
15. Index Statistics – When the optimizer needs to determine how
useful an index is and how many rows match a condition on an
indexed value, it needs information on the data in the index. This
information is index statistics. This chapter covers how index
statistics work in MySQL, how to configure them, monitoring, and
updating the index statistics.
16. Histograms – If you want the optimizer to know how frequent a
value occurs for a given column, you need to create a histogram.
This is a new feature in MySQL 8, and this chapter covers how
histograms can be used, their internals, and how to query the
histogram metadata and statistics.
17. The Query Optimizer – When you execute a query, it is the
query optimizer that determines how to execute it. This chapter
covers the tasks performed by the optimizer, join algorithms,
join optimizations, configuration of the optimizer, and resource
groups.
18. Locking Theory and Monitoring – One of the problems that can
cause the most frustration is lock contention. The first part of this
chapter explains why locks are needed, lock access levels, and
lock types (granularities). The second part of the chapter goes into
what happens when a lock cannot be obtained, how to reduce
lock contention, and where to find information about locks.
|
| Java1234官方群25： | 
|
| Java1234官方群25： | 838462530 |
苏公网安备 32061202001004号
