Advanced MySQL 8: Discover the Full Potential of MySQL and Ensure High Performance of Your Database

Cover
Title Page
Copyright and Credits
About Packt
Contributors
Table of Contents
Preface
Chapter 1: Introduction
	Why MySQL 8? 
	Why is MySQL 8.0 the next generation?
	Why it is so important to have a good MySQL architecture design
	Summary
Chapter 2: MySQL 8\'s New Features
	Global data dictionary
	MySQL 8\'s support roles and history
	MySQL 8 supports the creation and management of resource groups and permissions
	InnoDB enhancements
	JSON enhancements functionalities
	Invisible indexes from MySQL optimizer
	Features deprecated in MySQL 8.0
	A quick look at the features removed from MySQL 8.0
	Summary
Chapter 3: Indexing Your Data for High Performance
	How does MySQL optimizer think in MySQL 8.0?
	What kind of data type should I consider indexing first and why?
	Why should I have a good index strategy?
	What impact does an index have on MySQL performance?
	How to display and analyze a table structure
	How to efficiently read MySQL query execution plans
		How to effectively read the EXPLAIN results
			id (JSON name: select_id)
			select_type (JSON name: none)
			table (JSON name: table_name)
			partitions (JSON name: partitions)
			type (JSON name: access_type)
		EXPLAIN extra information
	How to know when to create an index
	Multiple column index versus multiple indexes
	How to organize your columns in an index for good performance
		Case study 1 – how to use the EXPLAIN plan tool in MySQL 8.0
			EXPLAIN options
		Case study 2 – how to display and analyze a table structure versus the EXPLAIN plan tool
		Case study 3 – how to organize your columns in an index efficiently
			Creating a missing index
	Tips and techniques
		The five general rules for your indexes
		Tip 2 – the five general rules to optimize your queries
		Tip 3 – understand your material resources
		Tip 4 – the configuration is not the only thing to take into consideration
		Tip 5 – recommendations on the performance of the MySQL architecture
		A technical case study
	Summary
Chapter 4: Advanced Data Techniques for Large Queries
	The most important variables are full-scan indicators
	Partitioning a table
		An overview of partitioning in MySQL 8.0
		Available partitioning type
		Horizontally partitioning your data
	Managing partitions
		RANGE partitioning
		LIST partitioning
		HASH partitioning
		KEY partitioning
	Using partitions
		Partition pruning
	Getting rid of unused and duplicate indexes
		Unused indexes
		Duplicate indexes
		Bonus – potentially missing indexes
	The most important query optimizations
		Optimizing a query with the WHERE clause
		Optimizing a query with a GROUP BY clause
		Optimizing a query with the ORDER BY clause
	Temporary tables
	Case study 1 – an example of how to optimize a complex query
	Case study 2 – how to optimize sort indexes
	Tips and techniques
		Partitions
		Optimization
		Techniques
		A typical use case: time series data
		Example of a mass DELETE
	Summary
Chapter 5: MySQL Data Dictionary in MySQL 8.0
	MySQL data dictionary structure in MySQL 8.0
	Dictionary object cache
	Transactional storage of the data dictionary
	Applications of the data dictionary
	Removal of file-based storage metadata
	Serialized Dictionary Information (SDI)
	Limitations of the data dictionary
	Tips and techniques
	Summary
Chapter 6: MySQL Server Settings
	Getting started with the most significant variables
	MySQL server optimization
		Control the types of data change operations
		Enabling the adaptive hash indexing function
		Set a limit on the number of concurrent threads
		Controlling the amount of InnoDB preloading
		Increasing the number of background threads
		Controlling InnoDB input/output performance in the background
		Taking advantage of multicore processors
		Preventing punctual operations
		Configuring the number and size of instances
		The InnoDB buffer pool
	The thread cache
		Case study 1 – when MySQL uses more than 100% of a CPU
			How to detect high usage of the MySQL processor
			Correcting the use of the MySQL CPU
			How to prevent MySQL from using high CPUs
		Case study 2 – when MySQL swaps on disk
	Tips and techniques
	Summary
Chapter 7: Group Replication in MySQL 8.0
	High availability and requirements
		Scaling
	Replication
	Group replication
		Use cases for group replication
			Elastic replication
			Highly available shards
			Alternative to master – slave replication
			Autonomic systems
	An overview of MySQL\'s database replication
		Asynchronous replication
		Semi-synchronous replication
		Delayed replication
		Global transaction identifier-based replication
		Multi-source replication
	MySQL\'s group replication architecture
		Group
		Writeset
		How group communication works
		Certification process
			Total order delivery  
			Detecting failure
				Network partitioning
			Traditional locking versus optimistic locking
				Distributed first commit wins rule 
				Drawbacks of optimistic locking 
		Modes of group replication
			Single primary mode
			Multi-primary
		Group replication requirements
		Configuring the server
		Configuring group replication
		Monitoring group replication
			Replication_group_members
				replication_group_member_stats
			Replication_connection_status
			Replication_applier_status
				Server state
	Limitations of group replication
	Group replication security
		IP address whitelist
		SSL
		VPN
	Operations on an online group
		Changing the group mode
		Tuning recovery
		Combining group replication versions
		Performance tuning
		Message compression
		Flow control
	Summary
Chapter 8: InnoDB Cluster in MySQL 8.0
	What is InnoDB cluster?
		InnoDB cluster requirements
			Installing MySQL Shell
				How to use MySQL Shell
		Installing an InnoDB cluster
			MySQL InnoDB cluster for a sandbox environment
			InnoDB cluster in a production environment
			Configuring the router configuration
		Managing clusters
			Getting details of a cluster
			Removing instances from a cluster
			Adding instances to a cluster
			Restoring a cluster after quorum loss
			Rebooting a cluster after a major outage
			Rescanning a cluster
			Checking instance states
			Dissolving an InnoDB cluster
		InnoDB cluster limitations
	Storage engines
		Setting a storage engine
			MyISAM storage engine
			The MEMORY storage engine
			The CSV storage engine
			The ARCHIVE storage engine
			The BLACKHOLE storage engine
			The MERGE storage engine
			The FEDERATED storage engine
			InnoDB engine
	Migrating from master-slave replication to MySQL InnoDB cluster
	Summary
Chapter 9: Monitoring Your Large Distributed Databases
	MONyog
		Pros
		Cons
		Conclusion
	Datadog
		Getting started
		Pros
		Cons
		Conclusion
	Navicat
		Pros
		Cons
		Conclusion
	Comparison between monitoring tools
		The price
		Pros
		Cons
		Top clients
	Tips and techniques
	Summary
Chapter 10: Authentication and Security Management with MySQL 8.0
	MySQL 8.0 security features
	Privileges provided by MySQL 8.0
	Where are privileges stored in MySQL 8.0?
	The differences between dynamic and static privileges
	Creating roles and users in MySQL 8.0
	Displaying assigned roles using SHOW GRANTS
	Troubleshooting connection problems
	Tips and techniques
		Restricting or disabling remote access to the server
		Disabling LOCAL INFILE usage
		Changing the username and password for root
	Summary
Chapter 11: Advanced MySQL Performance Tips and Techniques
	Tips/best practices
		Optimizing your queries for the query cache
		EXPLAIN your SELECT queries
		LIMIT 1 when getting a unique row
		Indexing for search fields
		Indexing strategy for Joins
		Avoiding SELECT * and COUNT *
		Almost always have an ID field
		Using ENUM over VARCHAR
		Using prepared statements if and when possible
		Splitting the big DELETE or INSERT queries
		Avoiding the delete trigger
	Techniques
		Can MySQL perform queries on billions of rows?
		Is InnoDB the right choice for multi-billion rows?
		How big can a MySQL database get before the performance starts to degrade?
		Why MySQL could be slow with large tables
		Is MySQL the best solution for handling blobs?
	Summary
Other Books You May Enjoy
Index