InnoDB Storage Engine

Overview

MariaDB Enterprise Server uses the InnoDB storage engine by default. InnoDB is a general purpose transactional storage engine that is performant, ACID-compliant, and well-suited for most workloads.

Benefits

The InnoDB storage engine:

  • Is available with all versions of MariaDB Enterprise Server and MariaDB Community Server.

  • Is a general purpose storage engine.

  • Is transactional and well-suited for online transactional processing (OLTP) workloads.

  • Is ACID-compliant.

  • Performs well for mixed read-write workloads.

  • Supports online DDL.

Feature Summary

Feature

Detail

Resources

Storage Engine

InnoDB

Availability

All ES and CS versions

Workload Optimization

Transactional

Table Orientation

Row

Default Row Format

Dynamic

ACID-compliant

Yes

XA Transactions

Yes

Primary Keys

Yes

Auto-Increment

Yes

Sequences

Yes

Foreign Keys

Yes

Indexes

Yes

Secondary Indexes

Yes

Unique Indexes

Yes

Full-text Search

Yes

Spatial Indexes

Yes

Compression

Yes

Data-at-Rest Encryption

Yes

High Availability (HA)

Yes

Main Memory Caching

Yes

Transaction Logging

Yes

Garbage Collection

Yes

Online Schema changes

Yes

Non-locking Reads

Yes

Row Locking

Yes

Examples

Creating an InnoDB Table

CREATE DATABASE hq_sales;

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
) ENGINE = InnoDB;

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='hq_sales'
AND TABLE_NAME='invoices';
+--------------+------------+--------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE |
+--------------+------------+--------+
| hq_sales     | invoices   | InnoDB |
+--------------+------------+--------+