MySQL Storage Engine

MySQL supports multiple storage engines, each with its own features and use cases. The most commonly used storage engines are InnoDB and MyISAM.

  • InnoDB: This is the default storage engine in MySQL. It supports transactions, foreign keys, and row-level locking, making it suitable for high-concurrency applications. InnoDB is designed for reliability and performance, and it is the recommended storage engine for most applications.
  • MyISAM: This storage engine is older and does not support transactions or foreign keys. It uses table-level locking, which can lead to performance issues in high-concurrency environments. MyISAM is suitable for read-heavy applications where transactions are not required.
  • Memory: This storage engine stores all data in memory, providing very fast access. However, it is not suitable for large datasets or applications that require data persistence, as all data will be lost if the server is restarted.
  • CSV: This storage engine allows you to store data in CSV files. It is useful for data exchange and import/export operations but is not suitable for high-performance applications.
  • Archive: This storage engine is designed for storing large amounts of data that are rarely accessed. It uses a compressed format to save disk space and is optimized for insert operations, but it does not support indexes or transactions.
Storage Engine Transactions Foreign Keys Locking Mechanism Use Cases
InnoDB Yes Yes Row-level locking High-concurrency applications, data integrity
MyISAM No No Table-level locking Read-heavy applications, simple data storage
Memory No No Table-level locking Temporary data, fast access
CSV No No Table-level locking Data exchange, import/export
Archive No No None Large datasets, infrequent access

When choosing a storage engine for your MySQL database, consider the specific requirements of your application, such as the need for transactions, data integrity, concurrency, and performance. In most cases, InnoDB is the recommended choice due to its robust features and reliability. However, for certain use cases, such as read-heavy applications or temporary data storage, other storage engines may be more suitable.

When creating a table in MySQL, you can specify the storage engine using the ENGINE clause. For example:

1
2
3
4
CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(255)
) ENGINE=InnoDB;

This will create a table using the InnoDB storage engine. You can also change the default storage engine for the entire MySQL server by modifying the my.cnf configuration file:
1
2
[mysqld]
default-storage-engine=InnoDB

In summary, choosing the right storage engine is crucial for optimizing the performance and reliability of your MySQL database. Consider the specific requirements of your application, such as transaction support, concurrency, and data persistence, when selecting a storage engine.

InnoDB Storage Engine

InnoDB is the default storage engine in MySQL and is designed for high performance and reliability. It supports transactions, foreign keys, and row-level locking, making it suitable for applications that require data integrity and concurrent access. InnoDB uses a clustered index to store data, which allows for efficient retrieval of rows based on the primary key. InnoDB also supports MVCC (Multi-Version Concurrency Control), which allows multiple transactions to access the same data simultaneously without locking, improving performance in multi-user environments. Additionally, InnoDB provides crash recovery capabilities, ensuring that data is not lost in the event of a server failure. Overall, InnoDB is the recommended storage engine for most applications due to its robust features and performance benefits.

Row-level locking in InnoDB allows multiple transactions to access different rows of the same table simultaneously without blocking each other, which can significantly improve performance in high-concurrency environments. In contrast, MyISAM uses table-level locking, which can lead to performance bottlenecks when multiple transactions are trying to access the same table.

1
2
3
4
5
6
-- Example of row-level locking in InnoDB
START TRANSACTION;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE; -- Locks only the row with account_id = 1
-- Other transactions can still access different rows in the accounts table without being blocked
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; -- Locks only the row with account_id = 1
COMMIT;

In this example, only the row with account_id = 1 is locked during the transaction, allowing other transactions to access different rows in the accounts table without being blocked. This is one of the key advantages of using InnoDB over MyISAM for applications that require high concurrency and data integrity.

Locking Mechanisms

InnoDB uses various locking mechanisms to manage concurrent access to data:

  • Row Locks: InnoDB uses row-level locking to allow multiple transactions to access different rows of the same table simultaneously. This helps to improve performance in high-concurrency environments.
  • Gap Locks: InnoDB uses gap locks to prevent other transactions from inserting new rows into a gap between existing rows. This is used to prevent phantom reads and ensure data consistency.
  • Next-Key Locks: InnoDB uses next-key locks to lock both the index record and the gap before it. This is used to prevent other transactions from inserting new rows into the gap or modifying the existing record, ensuring data consistency during transactions.

InnoDB Locking Mechanisms

1
2
3
4
5
6
- row lock = transaction + for update
  - Index is needed for row lock, otherwise it will be a table lock
- gap lock = transaction + for update + range condition
  - Index is needed for gap lock, otherwise it will be a table lock
- next-key lock = transaction + for update + range condition
  - Index is needed for next-key lock, otherwise it will be a table lock

1
2
3
4
5
6
SELECT ...
FROM ...
WHERE ...
FOR UPDATE | FOR SHARE
    [OF table_name]
    [NOWAIT | SKIP LOCKED];

In this syntax, FOR UPDATE is used to acquire exclusive locks on the selected rows, while FOR SHARE is used to acquire shared locks. The OF table_name clause specifies the table for which the locks should be acquired, and the NOWAIT and SKIP LOCKED options control how the transaction behaves when it encounters locked rows.

  • NOWAIT: If the transaction encounters locked rows, it will return an error immediately instead of waiting for the locks to be released.
  • SKIP LOCKED: If the transaction encounters locked rows, it will skip them and continue processing the remaining rows. This can be useful for avoiding deadlocks and improving performance in high-concurrency environments.
1
2
3
4
5
-- Example of using NOWAIT and SKIP LOCKED
START TRANSACTION;
SELECT * FROM accounts WHERE balance > 1000 FOR UPDATE NOWAIT; -- This will return an error if any rows are locked
SELECT * FROM accounts WHERE balance > 1000 FOR UPDATE SKIP LOCKED; -- This will skip any locked rows and return the remaining rows
COMMIT;

MVCC (Multi-Version Concurrency Control)

MVCC is a concurrency control method used by the InnoDB storage engine to provide high concurrency and maintain data consistency. It allows multiple transactions to access the same data simultaneously without locking, by creating multiple versions of the data. Each transaction sees a consistent snapshot of the database at the time it started, which helps to prevent conflicts and improve performance in multi-user environments.

MVCC in InnoDB

Merchanism of MVCC

  1. When a transaction starts, it is assigned a unique transaction ID (XID) and a consistent snapshot of the database is created for that transaction. This snapshot includes the state of all data at the time the transaction started.
  2. When a transaction modifies data, InnoDB creates a new version of the data and assigns it a new XID. The old version of the data is retained until all transactions that started before the modification have completed.
  3. When a transaction reads data, it checks the XIDs of the data versions to determine which version of the data to read. It will read the version of the data that was committed before the transaction started, ensuring that it sees a consistent snapshot of the database.
  4. When a transaction commits, the new versions of the data are made visible to other transactions, and the old versions of the data are marked for deletion. The old versions will be removed once all transactions that started before the modification have completed.
1
2
3
4
5
-- Example of MVCC in InnoDB
START TRANSACTION; -- Transaction A starts and gets a consistent snapshot of the database
SELECT * FROM accounts WHERE account_id = 1; -- Transaction A reads the data for account_id = 1 and sees the committed version of the data at the time it started
UPDATE accounts SET balance = 200 WHERE account_id = 1; -- Transaction A modifies the data for account_id = 1 and creates a new version of the data with a new XID
COMMIT; -- Transaction A commits, making the new version of the data visible to other transactions
1
2
3
4
5
6
7
data
┌───────────────┐
│ account_id = 1│
│ balance = 200 │
│ trx_id = 100  │
│ roll_ptr ---> │────→ undo log record for the previous version of the data
└───────────────┘