MySQL 8 Query Performance Tuning : A Systematic Method for Improving Execution Speeds

Table of Contents About the Author About the Technical Reviewer Acknowledgments Introduction Part I: Getting Started Chapter 1: MySQL Performance Tuning Consider the Whole Stack Monitoring The Lifecycle of a Query Summary Chapter 2: Query Tuning Methodology Overview Verify the Problem Determine the Cause Determine the Solution Implement the Solution Work Proactively Summary Chapter 3: Benchmarking with Sysbench Best Practices Standard TPC Benchmarks Common Benchmarks Tools Sysbench Installation Executing Benchmarks Creating Custom Benchmarks Overview of the Custom Script Defining the Options The run Command The prepare Command The cleanup Command Registering Commands Summary Chapter 4: Test Data Downloading the Example Databases The world Database Schema Installation The world_x Database Schema Installation The sakila Database Schema Installation The employees Database Schema Installation Other Databases Summary Part II: Sources of Information Chapter 5: The Performance Schema Terminology Threads Instruments Consumers Events Event Types Event Scopes Event Nesting Event Properties Actors and Objects Digests Table Types Dynamic Configuration Summary Chapter 6: The sys Schema sys Schema Configuration Formatting Functions The Views Helper Functions and Procedures Summary Chapter 7: The Information Schema What Is the Information Schema? Privileges Views System Information Schema Information Performance Information Privilege Information Caching of Index Statistics Data Summary Chapter 8: SHOW Statements Relationship to the Information Schema Relationship to the Performance Schema Engine Status Replication and Binary Logs Listing Binary Logs Viewing Log Events Show Connected Replicas Miscellaneous Statements Summary Chapter 9: The Slow Query Log Configuration Log Events Aggregation Summary Part III: Tools Chapter 10: MySQL Enterprise Monitor Overview Installation Download Installation Process Starting and Stopping the Service Manager Microsoft Windows Linux Adding MySQL Instances The Graphical User Interface General Navigation Advisors Timeseries Graphs The Query Analyzer Summary Chapter 11: MySQL Workbench Installation Microsoft Windows Enterprise Linux 7 Debian and Ubuntu Creating Connections Using MySQL Workbench Overview Configuration Safe Settings Reformatting Queries EER Diagrams Summary Chapter 12: MySQL Shell Overview Installing MySQL Shell Invoking MySQL Shell Creating Connections Language Modes Built-in Help Built-in Global Objects The Prompt Built-in Prompts Custom Prompt Definition Powerline and Awesome Fonts Using External Modules Reporting Infrastructure Report Information and Help Executing Reports Adding Your Own Reports Plugins Summary Part IV: Schema Considerations and the Query Optimizer Chapter 13: Data Types Why Data Types? Data Validation Documentation Optimized Storage Performance Correct Sorting MySQL Data Types Numeric Data Types Temporal Data Types String and Binary Data Types JSON Data Type Spatial Data Types Hybrid Data Types Performance Which Data Type Should You Choose? Summary Chapter 14: Indexes What Is an Index? Index Concepts Key Versus Index Unique Index Primary Key Secondary Indexes Clustered Index Covering Index Index Limitations SQL Syntax Creating Tables with Indexes Adding Indexes Removing Indexes What Are the Drawbacks of Indexes? Storage Updating the Index The Optimizer Index Types B-Tree Indexes Full Text Indexes Spatial Indexes (R-Tree) Multi-valued Indexes Hash Indexes Index Features Functional Indexes Prefix Indexes Invisible Indexes Descending Indexes Partitioning and Indexes Auto-generated Indexes InnoDB and Indexes The Clustered Index Secondary Indexes Recommendations Optimal Use Cases Index Strategies When Should You Add or Remove Indexes? Choice of the Primary Key Adding Secondary Indexes Multicolumn Index Covering Indexes Summary Chapter 15: Index Statistics What Are Index Statistics? InnoDB and Index Statistics How Statistics Are Collected Sample Pages Transaction Isolation Level Configuring Statistics Type Persistent Index Statistics Configuration Index Statistics Tables Transient Index Statistics Monitoring Information Schema STATISTICS View The SHOW INDEX Statement The Information Schema INNODB_TABLESTATS View The Information Schema TABLES View and SHOW TABLE STATUS Updating the Statistics Automatic Updates The ANALYZE TABLE Statement The mysqlcheck Program Summary Chapter 16: Histograms What Are Histograms? When Should You Add Histograms? Histogram Internals Buckets Cumulative Frequencies Histogram Types Adding and Maintaining Histograms Create and Update Histograms Sampling Dropping a Histogram Inspecting Histogram Data Histogram Reporting Examples List All Histograms List All Information for a Single Histogram List Bucket Information for a Singleton Histogram List Bucket Information for an Equi-height Histogram Query Example Summary Chapter 17: The Query Optimizer Transformations Cost-Based Optimization The Basics: Single Table SELECT Table Join Order Default Filtering Effects The Query Cost Join Algorithms Nested Loop Block Nested Loop Hash Join Join Optimizations Index Merge Intersection Algorithm Union Algorithm Sort-Union Algorithm Performance Considerations Configuration Multi-Range Read (MRR) Batched Key Access (BKA) Other Optimizations Condition Filtering Derived Merge Engine Condition Pushdown Index Condition Pushdown Index Extensions Index Visibility Loose Index Scan Range Access Method Semijoin Skip Scan Subquery Materialization Configuring the Optimizer Engine Costs Server Costs Optimizer Switches Optimizer Hints Index Hints Configuration Options Resource Groups Retrieving Information About Resource Groups Managing Resource Groups Assigning Resource Groups Performance Considerations Summary Chapter 18: Locking Theory and Monitoring Why Are Locks Needed? Lock Access Levels Lock Granularity User-Level Locks Flush Locks Metadata Locks Explicit Table Locks Implicit Table Locks Record Locks Gap Locks, Next-Key Locks, and Predicate Locks Insert Intention Locks Auto-increment Locks Backup Locks Log Locks Failure to Obtain Locks Metadata and Backup Lock Wait Timeouts InnoDB Lock Wait Timeouts Deadlocks Reduce Locking Issues Transaction Size and Age Indexes Record Access Order Transaction Isolation Levels Preemptive Locking Monitoring Locks The Performance Schema The sys Schema Status Counters and InnoDB Metrics InnoDB Lock Monitor and Deadlock Logging Summary Part V: Query Analysis Chapter 19: Finding Candidate Queries for Optimization The Performance Schema The Statement Event Tables Prepared Statements Summary Table I/O Summaries File I/O The Error Summary Tables The sys Schema Statement Views Table I/O Views File I/O Views Statement Performance Analyzer MySQL Workbench Performance Reports Client Connections Report MySQL Enterprise Monitor The Query Analyzer Timeseries Graphs Ad Hoc Reports The Slow Query Log Summary Chapter 20: Analyzing Queries EXPLAIN Usage Usage for Explicit Queries EXPLAIN ANALYZE Usage for Connections EXPLAIN Formats Traditional Format JSON Format Tree Format Visual Explain EXPLAIN Output EXPLAIN Fields Select Types Access Types system const eq_ref ref ref_or_null index_merge fulltext unique_subquery index_subquery range index ALL Extra Information EXPLAIN Examples Single Table, Table Scan Single Table, Index Access Two Tables and a Covering Index Multicolumn Index Two Tables with Subquery and Sorting Optimizer Trace Performance Schema Events Analysis Examining a Stored Procedure Analyzing Stage Events Analysis with the sys.ps_trace_thread() Procedure Analysis with the ps_trace_statement_digest() Procedure Summary Chapter 21: Transactions Impact of Transactions Locks Undo Logs INNODB_TRX InnoDB Monitor INNODB_METRICS and sys.metrics Performance Schema Transactions Transaction Events and Their Statements Transaction Summary Tables Summary Chapter 22: Diagnosing Lock Contention Flush Locks The Symptoms The Cause The Setup The Investigation The Solution The Prevention Metadata and Schema Locks The Symptoms The Cause The Setup The Investigation The Solution The Prevention Record-Level Locks The Symptoms The Cause The Setup The Investigation The Solution The Prevention Deadlocks The Symptoms The Cause The Setup The Investigation The Solution The Prevention Summary Part VI: Improving the Queries Chapter 23: Configuration Best Practices InnoDB Overview The InnoDB Buffer Pool The Buffer Pool Size Buffer Pool Instances Dumping the Buffer Pool The Old Blocks Sublist Flushing Pages The Redo Log Log Buffer Log Files Parallel Query Execution Query Buffers Internal Temporary Tables Summary Chapter 24: Change the Query Plan Test Data Symptoms of Excessive Full Table Scans Wrong Query No Index Used Not a Left Prefix of Index Data Types Not Matching Functional Dependencies Improving the Index Use Add a Covering Index Wrong Index Rewriting Complex Index Conditions Rewriting Complex Queries Common Table Expressions Window Functions Rewrite Subquery As Join Splitting a Query Into Parts Queue System: SKIP LOCKED Many OR or IN Conditions Summary Chapter 25: DDL and Bulk Data Load Schema Changes Algorithm Other Considerations Dropping or Truncating Tables General Data Load Considerations Insert in Primary Key Order Auto-increment Primary Key Inserting Existing Data UUID Primary Keys InnoDB Buffer Pool and Secondary Indexes Configuration Transactions and Load Method MySQL Shell Parallel Load Data Summary Chapter 26: Replication Replication Overview Monitoring Connection Tables Applier Tables Log Status Group Replication Tables The Connection Replication Events The Network Maintaining Source Info Writing the Relay Log The Applier Parallel Applier Primary Keys Relaxing Data Safety Replication Filters Offloading Work to a Replica Read Scale-Out Separation of Tasks Summary Chapter 27: Caching Caching Is Everywhere Caching Inside MySQL Cache Tables Histogram Statistics Memcached Standalone Memcached MySQL InnoDB Memcached Plugin ProxySQL Caching Tips Summary Index 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.