Core features in MySQL

Let's look back and quickly glance through some of the core features in MySQL. We will be discussing various features throughout the book in detail as we progress.

Structured database

Structured databases are traditional databases that have been used by many enterprises for more than 40 years. However, in the modern world, data volume is becoming bigger and bigger and a common need has taken its place--data analytics. Analytics is becoming difficult with structured databases as the volume and velocity of digital data grow faster by the day; we need to find a way to achieve such needs in an effective and efficient way. The most common database that is used as a structured database in the open source world is MySQL.

Many organizations use a structured database to store their data in an organized way with the formatted repository. Basically, data in a structured database has a fixed field, a predefined data length, and defines what kind of data is to be stored such as numbers, dates, time, addresses, currencies, and so on. In short, the structure is already defined before data gets inserted, which gives a clearer idea of what data can reside there. The key advantage of using a structured database is that data being easily stored, queried, and analyzed.

An unstructured database is the opposite of this; it has no identifiable internal structure. It can have a massive unorganized agglomerate or various objects. Mainly, the source of structured data is machine-generated, which means information is generated from the machine and without human intervention, whereas unstructured data is human-generated data. Organizations use structured databases for data such as ATM transactions, airline reservations, inventory systems, and so on. In the same way, some organizations use unstructured data such as emails, multimedia content, word processing documents, web pages, business documents, and so on.

Database storage engines and types

Let's now look at an overview of different MySQL storage engines. This is an important section that gives a brief of different database storage engines; we will be discussing this in detail in Chapter 6, MySQL 8 Storage Engines. MySQL stores data in the database as a subdirectory. In each database, data is stored as tables. When you create a table, MySQL stores the table definition in .frm with the same name as the table name. You can use the SHOW TABLE STATUS command to show information about your table:

mysql> SHOW TABLE STATUS LIKE 'admin_user' \G;
*************************** 1. row ***************************
 Name: admin_user
 Engine: InnoDB
 Version: 10
 Row_format: Dynamic
 Rows: 2
 Avg_row_length: 8192
 Data_length: 16384
 Max_data_length: 0
 Index_length: 16384
 Data_free: 0
 Auto_increment: 3
 Create_time: 2017-06-19 14:46:49
 Update_time: 2017-06-19 15:15:08
 Check_time: NULL
 Collation: utf8_general_ci
 Checksum: NULL
 Create_options:
 Comment: Admin User Table
1 row in set (0.00 sec)

This command shows that this is an InnoDB table with the column name Engine. There is additional information that you can refer to for other purposes such as the number of rows, index length, and so on.

The storage engine is the way to handle SQL operations for different table types. Each storage engine has its own advantages and disadvantages. It is important to understand each storage engine's features and choose the most appropriate one for your tables to maximize the performance of the database. InnoDB is the default storage engine when we create a new table in MySQL 8.

The MySQL server uses a plug-and-play storage engine architecture. You can load the required storage engine and unload unnecessary storage engines from the MySQL server with the help of the SHOW ENGINES command as follows:

mysql> SHOW ENGINES \G;
*************************** 1. row ***************************
 Engine: InnoDB
 Support: YES
 Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
 XA: YES
 Savepoints: YES
*************************** 2. row ***************************
 Engine: MRG_MYISAM
 Support: YES
 Comment: Collection of identical MyISAM tables
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 3. row ***************************
 Engine: MEMORY
 Support: YES
 Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 4. row ***************************
 Engine: BLACKHOLE
 Support: YES
 Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 5. row ***************************
 Engine: MyISAM
 Support: DEFAULT
 Comment: MyISAM storage engine
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 6. row ***************************
 Engine: CSV
 Support: YES
 Comment: CSV storage engine
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 7. row ***************************
 Engine: ARCHIVE
 Support: YES
 Comment: Archive storage engine
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 8. row ***************************
 Engine: PERFORMANCE_SCHEMA
 Support: YES
 Comment: Performance Schema
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 9. row ***************************
 Engine: FEDERATED
 Support: NO
 Comment: Federated MySQL storage engine
Transactions: NULL
 XA: NULL
 Savepoints: NULL
9 rows in set (0.00 sec)

Overview of InnoDB

InnoDB is the default storage engine broadly used out of all other available storage engines. It was released with MySQL 5.1 as a plugin in 2008. MySQL 5.5 and later has InnoDB as a default storage engine. It has been taken over by Oracle Corporation in October 2005, from the Innobase Oy, which is a Finland-based company.

InnoDB tables support ACID-compliant commits, rollback, and crash recovery capabilities to protect user data. It also supports row-level locking, which helps with better concurrency and performance. It stores data in clustered indexes to reduce I/O operations for all SQL select queries based on the primary key. It also supports FOREIGN KEY constraints that allow better data integrity for the database. The maximum size of an InnoDB table can scale up to 64 TB, which should be good enough to serve many real-world use cases.

Overview of MyISAM

MyISAM was the default storage engine for MySQL prior to 5.5 1. MyISAM storage engine tables do not support ACID-compliant as opposed to InnoDB. MyISAM tables support table-level locking only, so MyISAM tables are not transaction-safe; however, they are optimized for compression and speed. It is generally used when you need to have primarily read operations with minimal transaction data. The maximum size of a MyISAM table can grow up to 256 TB, which helps in use cases such as data analytics. MyISAM supports full-text indexing, which can help in complex search operations. Using full-text indexes, we can index data stored in BLOB and TEXT data types.

Overview of memory

A memory storage engine is generally known as a heap storage engine. It is used to access data extremely quickly. This storage engine stores data in the RAM so it wouldn't need I/O operation. As it stores data in the RAM, all data is lost upon server restart. This table is basically used for temporary tables or the lookup table. This engine supports table-level locking, which limits high write concurrency.

Important notes about memory tables are as follows:

• Because memory table stores data in the RAM, which has a very limited storage capacity; if you try to write too much data into the memory table, it will start swapping data into the disk and then you lose the benefits of the memory storage engine
• These tables don't support TEXT and BLOB data types, and it is not even required as it has limited storage capacity
• This storage engine can be used to cache the results; lookup tables, for example, or postal codes and the names of states
• Memory tables support B-tree indexes and Hash indexes

Overview of archive

This storage engine is used to store large amounts of historical data without any indexes. Archive tables do not have any storage limitations. The archive storage engine is optimized for high insert operations and also supports row-level locking. These tables store data in a compressed and small format. The archive engine does not support DELETE or UPDATE operations; it only allows INSERT, REPLACE, and SELECT operations.

Overview of BLACKHOLE as a storage engine

This storage engine accepts data but does not store it. It discards data after every INSERT instead of storing it.

Now, what is the use of this storage engine; why would anybody use it? Why would we run an INSERT query that doesn't insert anything into the table?

This engine is useful for replication with large number of servers. A BLACKHOLE storage engine acts as a filter server between the master and slave server, which do not store any data, but only apply replicate-do-* and replicate-ignore-* rules and write a binlogs. These binlogs are used to perform replication in slave servers. We will discuss this in detail in Chapter 8, Replication in MySQL 8.

Overview of CSV

The comma separated values (CSV) engine stores data in the .csv file type using the comma-separated values format. This engine extracts data from the database and copies it to .csv out of the database. If you create a CSV file from the spreadsheet and copy it into the MYSQL data folder server, it can read the data using the select query. Similarly, if you write data in the table, an external program can read it from the CSV file. This storage engine is used for the exchange of data between software or applications. A CSV table does not support indexing and partitioning. All columns in the CSV storage engine need to be defined with the NOT NULL attribute to avoid errors during table creation.

Overview of merge

This storage engine is also known as an MRG_MyISAM storage engine. This storage engine merges a MyISAM table and creates it to be referred to a single view. For a merge table, all columns are listed in the same order. These tables are good for data warehousing environments.

The table is used to manage log-related tables, generally. You can create different months of logs in separate MyISAM tables and merge these tables using the merge storage engine.

MyISAM tables have storage limit for the operating system, but a collection of MyISAM (merge) tables do not have storage limits. So using a merge table would allow you to split data into multiple MyISAM tables, which can help in overcoming storage limits.

Merge tables do not support partitioning. Also, you cannot partition a merge table or any of a merge table's underlying MyISAM tables in a different partition.

Overview of federated

This storage engine allows you to create a single database on a multiple physical server. It opens a client connection to another server and executes queries against a table there, retrieving and sending rows as needed. It was originally marketed as a competitive feature that supported many enterprise-grade proprietary database servers, such as Microsoft SQL Server and Oracle, but that was always a stretch, to say the least. Although it seemed to enable a lot of flexibility and neat tricks, it has proven to be a source of many problems and is disabled by default. This storage engine is disabled by default in MySQL; to enable it, you need to start the MySQL server binary using the federated option.

Overview of the NDB cluster

NDB cluster (also known as NDB) is an in-memory storage engine offering high availability and data persistence features.

The NDB cluster storage engine can be configured with a range of failover and load balancing options, but it is easiest to start with the storage engine at the cluster level. NDB cluster uses the NDB storage engine that contains a complete set of data, which is dependent only on other datasets available within the cluster.

The cluster portion of the NDB cluster is configured independently of the MySQL servers. In an NDB cluster, each part of the cluster is considered to be a node.

Each storage engine has its own advantage and usability, as follows:

• Search Engine: NDBCluster
• Transactions data: InnoDB
• Session data: MyISAM or NDBCluster
• Localized calculations: Memory
• Dictionary: MyISAM

The following diagram will help you understand which store engine you need to use for your requirement:

Now you have a better idea about various storage engines along with different use cases, which will help you to make a decision based on your needs.

It's time to move on to our next topic where we will look at delightful new features available in MySQL 8.