MySQL 8 Query Performance Tuning

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