Muutke küpsiste eelistusi

MySQL 8 Query Performance Tuning: A Systematic Method for Improving Execution Speeds 1st ed. [Pehme köide]

4.25/5 (7 hinnangut Goodreads-ist)
  • Formaat: Paperback / softback, 965 pages, kõrgus x laius: 254x178 mm, kaal: 1871 g, 135 Illustrations, black and white; XXXVII, 965 p. 135 illus., 1 Paperback / softback
  • Ilmumisaeg: 17-Mar-2020
  • Kirjastus: APress
  • ISBN-10: 1484255836
  • ISBN-13: 9781484255834
Teised raamatud teemal:
  • Pehme köide
  • Hind: 53,33 €*
  • * hind on lõplik, st. muud allahindlused enam ei rakendu
  • Tavahind: 62,74 €
  • Säästad 15%
  • Raamatu kohalejõudmiseks kirjastusest kulub orienteeruvalt 2-4 nädalat
  • Kogus:
  • Lisa ostukorvi
  • Tasuta tarne
  • Tellimisaeg 2-4 nädalat
  • Lisa soovinimekirja
MySQL 8 Query Performance Tuning: A Systematic Method for Improving Execution Speeds 1st ed.Suurem pilt
  • Formaat: Paperback / softback, 965 pages, kõrgus x laius: 254x178 mm, kaal: 1871 g, 135 Illustrations, black and white; XXXVII, 965 p. 135 illus., 1 Paperback / softback
  • Ilmumisaeg: 17-Mar-2020
  • Kirjastus: APress
  • ISBN-10: 1484255836
  • ISBN-13: 9781484255834
Teised raamatud teemal:
Püsilink: https://www.kriso.ee/db/9781484255834.html
Intermediate-Advanced user level

Identify, analyze, and improve poorly performing queries that damage user experience and lead to lost revenue for your business. This book will help you make query tuning an integral part of your daily routine through a multi-step process that includes monitoring of execution times, identifying candidate queries for optimization, analyzing their current performance, and improving them to deliver results faster and with less overhead. Author Jesper Krogh systematically discusses each of these steps along with the data sources and the tools used to perform them. 

MySQL 8 Query Performance Tuning aims to help you improve query performance using a wide range of strategies. You will know how to analyze queries using both the traditional EXPLAIN command as well as the new EXPLAIN ANALYZE tool. You also will see how to use the Visual Explain feature to provide a visually-oriented view of an execution plan. Coverage of indexes includes indexing strategies and index statistics, and you will learn how histograms can be used to provide input on skewed data distributions that the optimizer can use to improve query performance. You will learn about locks, and how to investigate locking issues. And you will come away with an understanding of how the MySQL optimizer works, including the new hash join algorithm, and how to change the optimizer’s behavior when needed to deliver faster execution times. You will gain the tools and skills needed to delight application users and to squeeze the most value from corporate computing resources. 


What You Will Learn
  • Monitor query performance to identify poor performers
  • Choose queries to optimize that will provide the greatest gain
  • Analyze queries using tools such as EXPLAIN ANALYZE and Visual Explain
  • Improve slow queries through a wide range of strategies
  • Properly deploy indexes and histograms to aid in creating fast execution plans
  • Understand and analyze locks to resolve contention and increase throughput

Who This Book Is For

Database administrators and SQL developers who are familiar with MySQL and need to participate in query tuning. While some experience with MySQL is required, no prior knowledge of query performance tuning is needed.

About the Author xxiii
About the Technical Reviewer xxv
Acknowledgments xxvii
Introduction xxix
Part I Getting Started
1(2)
Chapter 1 MySQL Performance Tuning
3(6)
Consider the Whole Stack
3(2)
Monitoring
5(1)
The Lifecycle of a Query
6(2)
Summary
8(1)
Chapter 2 Query Tuning Methodology
9(10)
Overview
9(2)
Verify the Problem
11(1)
Determine the Cause
11(1)
Determine the Solution
12(1)
Implement the Solution
13(2)
Work Proactively
15(1)
Summary
16(3)
Chapter 3 Benchmarking with Sysbench
19(36)
Best Practices
19(3)
Standard TPC Benchmarks
22(1)
Common Benchmarks Tools
23(1)
Sysbench Installation
24(8)
Executing Benchmarks
32(8)
Creating Custom Benchmarks
40(13)
Overview of the Custom Script
41(2)
Defining the Options
43(2)
The run Command
45(3)
The prepare Command
48(2)
The cleanup Command
50(1)
Registering Commands
51(2)
Summary
53(2)
Chapter 4 Test Data
55(20)
Downloading the Example Databases
55(1)
The world Database
56(2)
Schema
56(1)
Installation
57(1)
The world_x Database
58(2)
Schema
58(1)
Installation
59(1)
The sakila Database
60(7)
Schema
60(6)
Installation
66(1)
The employees Database
67(5)
Schema
67(4)
Installation
71(1)
Other Databases
72(1)
Summary
73(2)
Part II Sources of Information
75(2)
Chapter 5 The Performance Schema
77(24)
Terminology
77(1)
Threads
78(5)
Instruments
83(2)
Consumers
85(2)
Events
87(7)
Event Types
88(1)
Event Scopes
89(1)
Event Nesting
90(2)
Event Properties
92(2)
Actors and Objects
94(1)
Digests
94(2)
Table Types
96(1)
Dynamic Configuration
97(2)
Summary
99(2)
Chapter 6 The sys Schema
101(10)
Sys Schema Configuration
101(4)
Formatting Functions
105(2)
The Views
107(2)
Helper Functions and Procedures
109(1)
Summary
110(1)
Chapter 7 The Information Schema
111(22)
What Is the Information Schema?
111(1)
Privileges
112(1)
Views
113(15)
System Information
113(2)
Schema Information
115(6)
Performance Information
121(5)
Privilege Information
126(2)
Caching of Index Statistics Data
128(3)
Summary
131(2)
Chapter 8 SHOW Statements
133(20)
Relationship to the Information Schema
133(2)
Relationship to the Performance Schema
135(2)
Engine Status
137(2)
Replication and Binary Logs
139(7)
Listing Binary Logs
139(1)
Viewing Log Events
140(5)
Show Connected Replicas
145(1)
Miscellaneous Statements
146(4)
Summary
150(3)
Chapter 9 The Slow Query Log
153(12)
Configuration
154(4)
Log Events
158(2)
Aggregation
160(4)
Summary
164(1)
Part III Tools
165(108)
Chapter 10 MySQL Enterprise Monitor
167(32)
Overview
167(3)
Installation
170(12)
Download
170(5)
Installation Process
175(7)
Starting and Stopping the Service Manager
182(4)
Microsoft Windows
183(1)
Linux
184(2)
Adding MySQL Instances
186(3)
The Graphical User Interface
189(8)
General Navigation
189(2)
Advisors
191(2)
Timeseries Graphs
193(2)
The Query Analyzer
195(2)
Summary
197(2)
Chapter 11 MySQL Workbench
199(28)
Installation
199(17)
Microsoft Windows
200(7)
Enterprise Linux 7
207(5)
Debian and Ubuntu
212(4)
Creating Connections
216(2)
Using MySQL Workbench
218(5)
Overview
218(2)
Configuration
220(1)
Safe Settings
221(1)
Reformatting Queries
222(1)
EER Diagrams
223(3)
Summary
226(1)
Chapter 12 MySQL Shell
227(46)
Overview
227(9)
Installing MySQL Shell
228(1)
Invoking MySQL Shell
228(1)
Creating Connections
229(3)
Language Modes
232(1)
Built-in Help
233(2)
Built-in Global Objects
235(1)
The Prompt
236(9)
Built-in Prompts
236(3)
Custom Prompt Definition
239(3)
Powerline and Awesome Fonts
242(3)
Using External Modules
245(3)
Reporting Infrastructure
248(13)
Report Information and Help
249(1)
Executing Reports
250(2)
Adding Your Own Reports
252(9)
Plugins
261(9)
Summary
270(3)
Part IV Schema Considerations and the Query Optimizer
273(266)
Chapter 13 Data Types
275(22)
Why Data Types?
275(5)
Data Validation
276(2)
Documentation
278(1)
Optimized Storage
278(1)
Performance
279(1)
Correct Sorting
280(1)
MySQL Data Types
280(13)
Numeric Data Types
281(2)
Temporal Data Types
283(1)
String and Binary Data Types
284(3)
JSON Data Type
287(2)
Spatial Data Types
289(1)
Hybrid Data Types
290(3)
Performance
293(1)
Which Data Type Should You Choose?
293(3)
Summary
296(1)
Chapter 14 Indexes
297(50)
What Is an Index?
297(1)
Index Concepts
298(5)
Key Versus Index
299(1)
Unique Index
299(1)
Primary Key
300(1)
Secondary Indexes
301(1)
Clustered Index
301(1)
Covering Index
302(1)
Index Limitations
303(1)
SQL Syntax
304(5)
Creating Tables with Indexes
305(1)
Adding Indexes
306(1)
Removing Indexes
307(2)
What Are the Drawbacks of Indexes?
309(2)
Storage
309(1)
Updating the Index
310(1)
The Optimizer
310(1)
Index Types
311(17)
B-Tree Indexes
312(3)
Full Text Indexes
315(1)
Spatial Indexes (R-Tree)
316(2)
Multi-valued Indexes
318(5)
Hash Indexes
323(5)
Index Features
328(7)
Functional Indexes
328(1)
Prefix Indexes
329(2)
Invisible Indexes
331(1)
Descending Indexes
332(1)
Partitioning and Indexes
332(3)
Auto-generated Indexes
335(1)
InnoDB and Indexes
335(3)
The Clustered Index
336(1)
Secondary Indexes
336(1)
Recommendations
337(1)
Optimal Use Cases
337(1)
Index Strategies
338(6)
When Should You Add or Remove Indexes?
338(2)
Choice of the Primary Key
340(1)
Adding Secondary Indexes
341(1)
Multicolumn Index
342(1)
Covering Indexes
343(1)
Summary
344(3)
Chapter 15 Index Statistics
347(38)
What Are Index Statistics?
347(1)
InnoDB and Index Statistics
348(5)
How Statistics Are Collected
348(1)
Sample Pages
349(2)
Transaction Isolation Level
351(1)
Configuring Statistics Type
352(1)
Persistent Index Statistics
353(9)
Configuration
353(3)
Index Statistics Tables
356(6)
Transient Index Statistics
362(1)
Monitoring
363(14)
Information Schema STATISTICS View
363(3)
The SHOW INDEX Statement
366(4)
The Information Schema INNODBJABLESTATS View
370(2)
The Information Schema TABLES View and SHOW TABLE STATUS
372(5)
Updating the Statistics
377(7)
Automatic Updates
377(1)
The ANALYZE TABLE Statement
378(2)
The mysqlcheck Program
380(4)
Summary
384(1)
Chapter 16 Histograms
385(32)
What Are Histograms?
385(1)
When Should You Add Histograms?
386(3)
Histogram Internals
389(7)
Buckets
389(1)
Cumulative Frequencies
390(4)
Histogram Types
394(2)
Adding and Maintaining Histograms
396(5)
Create and Update Histograms
397(2)
Sampling
399(1)
Dropping a Histogram
400(1)
Inspecting Histogram Data
401(2)
Histogram Reporting Examples
403(8)
List All Histograms
403(1)
List All Information for a Single Histogram
404(2)
List Bucket Information for a Singleton Histogram
406(3)
List Bucket Information for an Equi-height Histogram
409(2)
Query Example
411(4)
Summary
415(2)
Chapter 17 The Query Optimizer
417(70)
Transformations
418(1)
Cost-Based Optimization
419(7)
The Basics: Single Table SELECT
419(2)
Table Join Order
421(1)
Default Filtering Effects
422(1)
The Query Cost
423(3)
Join Algorithms
426(17)
Nested Loop
426(6)
Block Nested Loop
432(4)
Hash Join
436(7)
Join Optimizations
443(19)
Index Merge
443(9)
Multi-Range Read (MRR)
452(2)
Batched Key Access (BKA)
454(2)
Other Optimizations
456(6)
Configuring the Optimizer
462(14)
Engine Costs
462(2)
Server Costs
464(2)
Optimizer Switches
466(3)
Optimizer Hints
469(5)
Index Hints
474(1)
Configuration Options
475(1)
Resource Groups
476(8)
Retrieving Information About Resource Groups
477(1)
Managing Resource Groups
478(3)
Assigning Resource Groups
481(2)
Performance Considerations
483(1)
Summary
484(3)
Chapter 18 Locking Theory and Monitoring
487(52)
Why Are Locks Needed?
488(1)
Lock Access Levels
488(1)
Lock Granularity
489(25)
User-Level Locks
489(3)
Flush Locks
492(2)
Metadata Locks
494(4)
Explicit Table Locks
498(1)
Implicit Table Locks
499(3)
Record Locks
502(3)
Gap Locks, Next-Key Locks, and Predicate Locks
505(2)
Insert Intention Locks
507(3)
Auto-increment Locks
510(1)
Backup Locks
511(2)
Log Locks
513(1)
Failure to Obtain Locks
514(7)
Metadata and Backup Lock Wait Timeouts
515(1)
InnoDB Lock Wait Timeouts
516(1)
Deadlocks
517(4)
Reduce Locking Issues
521(7)
Transaction Size and Age
521(1)
Indexes
522(2)
Record Access Order
524(1)
Transaction Isolation Levels
524(4)
Preemptive Locking
528(1)
Monitoring Locks
528(9)
The Performance Schema
528(2)
The sys Schema
530(1)
Status Counters and InnoDB Metrics
531(1)
InnoDB Lock Monitor and Deadlock Logging
532(5)
Summary
537(2)
Part V Query Analysis
539(220)
Chapter 19 Finding Candidate Queries for Optimization
541(60)
The Performance Schema
542(25)
The Statement Event Tables
542(9)
Prepared Statements Summary
551(4)
Table I/O Summaries
555(6)
File I/O
561(4)
The Error Summary Tables
565(2)
The sys Schema
567(16)
Statement Views
568(3)
Table I/O Views
571(2)
File I/O Views
573(3)
Statement Performance Analyzer
576(7)
MySQL Workbench
583(4)
Performance Reports
584(2)
Client Connections Report
586(1)
MySQL Enterprise Monitor
587(10)
The Query Analyzer
587(5)
Timeseries Graphs
592(2)
Ad Hoc Reports
594(3)
The Slow Query Log
597(2)
Summary
599(2)
Chapter 20 Analyzing Queries
601(82)
EXPLAIN Usage
601(4)
Usage for Explicit Queries
602(1)
EXPLAIN ANALYZE
603(1)
Usage for Connections
604(1)
EXPLAIN Formats
605(20)
Traditional Format
607(3)
JSON Format
610(4)
Tree Format
614(5)
Visual Explain
619(6)
EXPLAIN Output
625(18)
EXPLAIN Fields
626(4)
Select Types
630(2)
Access Types
632(7)
Extra Information
639(4)
EXPLAIN Examples
643(11)
Single Table, Table Scan
643(1)
Single Table, Index Access
644(2)
Two Tables and a Covering Index
646(2)
Multicolumn Index
648(2)
Two Tables with Subquery and Sorting
650(4)
Optimizer Trace
654(4)
Performance Schema Events Analysis
658(22)
Examining a Stored Procedure
659(7)
Analyzing Stage Events
666(3)
Analysis with the sys.ps_trace_thread() Procedure
669(5)
Analysis with the ps_trace_statement_digest() Procedure
674(6)
Summary
680(3)
Chapter 21 Transactions
683(30)
Impact of Transactions
683(4)
Locks
684(1)
Undo Logs
685(2)
Innodb_Trx
687(6)
InnoDB Monitor
693(2)
INNODBJVIETRICS and sys.metrics
695(4)
Performance Schema Transactions
699(12)
Transaction Events and Their Statements
699(10)
Transaction Summary Tables
709(2)
Summary
711(2)
Chapter 22 Diagnosing Lock Contention
713(46)
Flush Locks
714(9)
The Symptoms
714(1)
The Cause
715(1)
The Setup
716(1)
The Investigation
716(5)
The Solution
721(1)
The Prevention
722(1)
Metadata and Schema Locks
723(13)
The Symptoms
723(1)
The Cause
724(1)
The Setup
724(1)
The Investigation
725(10)
The Solution
735(1)
The Prevention
735(1)
Record-Level Locks
736(8)
The Symptoms
736(4)
The Cause
740(1)
The Setup
740(1)
The Investigation
741(2)
The Solution
743(1)
The Prevention
744(1)
Deadlocks
744(14)
The Symptoms
745(1)
The Cause
745(1)
The Setup
746(1)
The Investigation
747(10)
The Solution
757(1)
The Prevention
757(1)
Summary
758(1)
Part VI Improving the Queries
759(188)
Chapter 23 Configuration
761(32)
Best Practices
761(5)
InnoDB Overview
766(2)
The InnoDB Buffer Pool
768(11)
The Buffer Pool Size
770(3)
Buffer Pool Instances
773(1)
Dumping the Buffer Pool
773(1)
The Old Blocks Sublist
774(3)
Flushing Pages
777(2)
The Redo Log
779(6)
Log Buffer
780(1)
Log Files
781(4)
Parallel Query Execution
785(1)
Query Buffers
786(1)
Internal Temporary Tables
787(4)
Summary
791(2)
Chapter 24 Change the Query Plan
793(60)
Test Data
793(1)
Symptoms of Excessive Full Table Scans
794(2)
Wrong Query
796(3)
No Index Used
799(14)
Not a Left Prefix of Index
800(4)
Data Types Not Matching
804(5)
Functional Dependencies
809(4)
Improving the Index Use
813(15)
Add a Covering Index
813(2)
Wrong Index
815(11)
Rewriting Complex Index Conditions
826(2)
Rewriting Complex Queries
828(13)
Common Table Expressions
829(6)
Window Functions
835(3)
Rewrite Subquery As Join
838(1)
Splitting a Query Into Parts
839(2)
Queue System: SKIP LOCKED
841(3)
Many OR or IN Conditions
844(7)
Summary
851(2)
Chapter 25 DDL and Bulk Data Load
853(36)
Schema Changes
853(4)
Algorithm
854(2)
Other Considerations
856(1)
Dropping or Truncating Tables
857(1)
General Data Load Considerations
857(15)
Insert in Primary Key Order
872(6)
Auto-increment Primary Key
872(2)
Inserting Existing Data
874(2)
UUID Primary Keys
876(2)
InnoDB Buffer Pool and Secondary Indexes
878(2)
Configuration
880(3)
Transactions and Load Method
883(1)
MySQL Shell Parallel Load Data
883(4)
Summary
887(2)
Chapter 26 Replication
889(28)
Replication Overview
890(2)
Monitoring
892(11)
Connection Tables
894(3)
Applier Tables
897(4)
Log Status
901(1)
Group Replication Tables
902(1)
The Connection
903(4)
Replication Events
903(1)
The Network
904(1)
Maintaining Source Info
905(1)
Writing the Relay Log
906(1)
The Applier
907(6)
Parallel Applier
907(3)
Primary Keys
910(1)
Relaxing Data Safety
911(1)
Replication Filters
912(1)
Offloading Work to a Replica
913(2)
Read Scale-Out
914(1)
Separation of Tasks
914(1)
Summary
915(2)
Chapter 27 Caching
917(30)
Caching Is Everywhere
917(2)
Caching Inside MySQL
919(5)
Cache Tables
919(3)
Histogram Statistics
922(2)
Memcached
924(10)
Tandalone Memcached
925(4)
MySQL InnoDB Memcached Plugin
929(5)
ProxySQL
934(10)
Caching Tips
944(1)
Summary
944(3)
Index 947
Jesper Wisborg Krogh has worked with MySQL databases since 2006 both as an SQL developer, a database administrator, and for more than eight years as part of the Oracle MySQL Support team. He has spoken at MySQL Connect and Oracle OpenWorld on several occasions, and addition to his books, he regularly blogs on MySQL topics and has authored around 800 documents in the Oracle Knowledge Base. He has contributed to the sys schema and four Oracle Certified Professional (OCP) exams for MySQL 5.6 to 8.0.





He earned a PhD in computational chemistry before changing to work with MySQL and other software development in 2006. Jesper lives in Sydney, Australia, and enjoys spending time outdoors walking, traveling, and reading. His areas of expertise include MySQL Cluster, MySQL Enterprise Backup, performance tuning, and the Performance and sys schemas.