MySQL 8 Storage Engines

In the previous chapter, we learned about setting up a new system, data dictionary, and system database. Detailed information was provided on caching techniques, globalization, different types of components, and plugin configuration, along with several types of log files which are very important for administration.

This chapter gives detailed information on MySQL 8 storage engines. It explains the InnoDB storage engine and its features in detail and also provides a practical guideline on custom storage engine creation and how to make it pluggable so that it can be installed in MySQL 8. The topics that we will be covering in this chapter are as follows:

• Overview of storage engines
• Several types of storage engines
• The InnoDB storage engine
• Creating a custom storage engine

Overview of storage engines

Storage engines are MySQL components for handling the SQL operations used in different types of tables. MySQL storage engines are designed to manage different types of tasks in different types of environments. It is very important to know and choose which storage engine is best suited for the system or application requirements. In following sections, we will get to know in detail about the types of storage engines, the default storage engine, and the creation of custom storage engines.

Let us go through and see why the storage engine is a very important component in databases, including MySQL 8. Storage engines work with database engines to perform various types of tasks in different environments. They execute create, read, update, and delete operations in the form of statements on data from the database. It looks quite simple when you provide the ENGINE...

Several types of storage engines

In this section, we will take a closer look at the widely used storage engines that are supported by MySQL 8. But before checking on them, let us see how the storage engine architecture has made it pluggable and provided flexibility to enable using multiple storage engines in the same schema or server.

The following is the list of storage engines supported in MySQL 8:

• InnoDB: The default storage engine for MySQL 8. It is an ACID compliant (transaction-safe) storage engine that has commit, roll back, and crash-recovery for protecting the user data and referential-integrity constraints to maintain data integrity, and much more.
• MyISAM: The storage engine with tables having a small footprint. It has table-level locking and so is mostly used in read-only or read-mostly data workloads, such as in data warehousing and web configurations.
• Memory: The...

The InnoDB storage engine

InnoDB is the most general-purpose storage engine and is the default engine in MySQL 8, providing high reliability and high performance .

The following are the key advantages offered by the InnoDB storage engine:

• Its DML operations follows the ACID model and transactions have commit, rollback, and crash-recovery features to protect user data
• Oracle-style gives consistent reads and row level locking increases the performance of multi-user concurrency
• Each InnoDB table has a primary key index, known as the clustered index, that arranges data on the disk in order to optimize queries based on primary key and minimizes I/O during primary key lookups
• By supporting foreign keys, inserts, deletes, and updates are checked, ensuring consistency across different tables in order to maintain data integrity

The following are the key benefits of using InnoDB tables...

Creating a custom storage engine

MySQL AB introduced pluggable storage engine architecture in MySQL 5.1 and all later versions, including MySQL 8, have taken advantage of the flexible storage engine architecture.

The storage engine pluggable architecture provides the capability to create and add new storage engines without recompiling the server, adding directly to a running MySQL server. The architecture makes it very easy to develop and deploy new storage engines to MySQL 8.

When developing new storage engine, it is required to take care of all the components that work for and with storage engines. These include installation handlers, operations on table such as creating, opening, and closing, DML, indexing, and so on.

In this section, we will cover how you can start developing a new storage engine on a high-level basis with reference to the MySQL documentation provided in the...

Summary

By now, you have learned the different database engines available in MySQL 8 and we learned why we should care about storage engines and available storage engine options in MySQL 8. We covered in detail theInnoDB storage engine and related important features already provided within the InnoDB storage engine. Now you are practically able to create a custom storage engine as per the system requirement and make it pluggable in MySQL 8. An important aspect was to choose a suitable storage engine for your system, which is covered detail.

In the next chapter, you will learn about how indexing works in MySQL 8, the new features introduced related to indexing, the different types of indexing, and how to use indexing on your tables. Along with that, a comparison will also be provided along with in-depth knowledge of various ways of index implementation.

...