Optimizing MySQL 8

In the previous chapter, we learned about security, which is an important aspect of any production-grade application. The chapter started with an introduction to security and identifying common security issues. Moving on, the chapter covered access control mechanisms, account management, and encryption in MySQL 8. We learned various MySQL 8 security plugins in the later part of the chapter. Security is an important benchmark for every production-grade application. That's why the previous chapter is an important one.

Moving along a similar line, with the objective of developing highly optimized databases, this chapter focuses on optimization methods. It starts with an overview of what optimization means in MySQL 8. It takes the reader through MySQL 8 server and client optimization, optimizing database structure, and optimizing common queries and database...

Overview of MySQL 8 optimization

Let's start with understanding MySQL 8 optimization. Optimization is the process of identifying performance bottlenecks and implementing optimized solutions to overcome these issues. Optimization in MySQL 8 involves performance measurement, configuration, and tuning at several different levels. It is an important task for an administrator to optimize the performance at different levels, like individual SQL queries, entire database applications, database servers, or distributed database servers. Performance optimization at the CPU and memory levels improves scalability. It also allows the database to handle more complex queries without slowing down the database server.

The performance of a database depends on multiple factors. At the database level, these factors can be tables, queries, and configurations. Database server startups and database...

Optimizing MySQL 8 servers and clients

This section focuses on optimization for MySQL 8 database servers and clients, starting with optimizing the server and followed by optimizing MySQL 8 client-side entities. This section is more relevant to database administrators, to ensure performance and scalability across multiple servers. It would also help developers preparing scripts (which includes setting up the database) and users running MySQL for development and testing to maximize the productivity.

Optimizing disk I/O

In this section, we will learn ways to configure storage devices to devote more and faster storage hardware to the database server. A major performance bottleneck is disk seeking (finding the correct place on...

Optimizing database structure

As a database administrator, we must look for efficient ways to organize table schema, tables, and columns. We minimize I/O, plan ahead, and keep related items together to tune the application code in order to keep performance high with an increase in data volume. It usually starts with efficient database design, which makes it easier for team members to write high-performance application code. It also makes the database likely to sustain itself as applications evolve or are rewritten.

Optimizing data size

In order to minimize the space on the disk, we should start designing the database tables. This results in huge performance improvements, as it reduces the amount of data to be written to and...

Optimizing queries

Similar to tables, database queries are the most crucial element of any database. Applications interact with the databases using queries. Queries are also called executable SQL statements. This section focuses on techniques to improve the performance of query execution.

Optimizing SQL statements

SQL statements are used to perform the core logic of any database application. It does not matter whether the statements are issued directly through an interpreter or submitted behind the scenes by an API. This section outlines guidelines to improve the performance of SQL operations that read and write data in the database.

SELECT statements perform all of the lookup operations in the database. Considering the frequency...

Optimizing tables

Database tables are the most basic building blocks for any database. In this section of the chapter, we will focus on optimizing tables. The section provides detailed guidelines for improving performance through table optimization techniques.

Optimization for InnoDB tables

The InnoDB storage engine is preferred in production environments in situations where reliability and concurrency are important. It is the default storage engine for MySQL tables. This section focuses on optimizing database operations for InnoDB tables.

The following are the guidelines to optimize InnoDB tables:

• Use of the OPTIMIZE TABLE statement should be considered to reorganize the table and compact the wasted space once the data reaches...

Leveraging buffering and caching

This section focuses on using buffering and caching techniques to increase the performance.

InnoDB buffer pool optimization

The InnoDB storage engine maintains a storage area known as the buffer pool. It is used for caching data and indexes in the memory. It is important to know how the InnoDB buffer pool works, so as to take advantage of it to keep frequently accessed data in memory. It is an important aspect of MySQL tuning.

The following are the general guidelines for improving performance with the InnoDB buffer pool:

• In an ideal case, the size of the buffer pool should be set large enough, while leaving enough memory for other processes on the server to run without excessive paging. With...

Summary

In this chapter, we learned, in detail, the techniques to optimize MySQL 8 components. The chapter started with the basics of optimization, including hardware and software optimization guidelines. We also discussed optimization guidelines for the MySQL 8 server and client, database structure, queries, and tables. We also covered optimization for tables belonging to different storage engines, such as MyISAM, InnoDB, and MEMORY. We learned the tools, such as EXPLAIN and EXPLAIN ANALYZE, needed to understand the query execution plan. In the later part of the chapter, we learned buffering and caching techniques to improve performance.

It's time to move on to the next chapter now. The next chapter focuses on techniques to extend MySQL 8. The chapter will cover in-depth details of MySQL 8 plugins, which help to extend the default MySQL 8 features. It will also explain the...