Advanced MySQL 8电子书

Title Page

Copyright and Credits

Advanced MySQL 8

About Packt

Why subscribe?

Packt.com

Contributors

About the authors

About the reviewer

Packt is searching for authors like you

Preface

Who this book is for

What this book covers

To get the most out of this book

Download the color images

Conventions used

Get in touch

Reviews

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

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

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

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

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

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

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

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

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

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

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

Leave a review - let other readers know what you think