I was making a deep dig into mariadb and recently went through innodb storage engine and found quite interesting features about the aforementioned storage engine.
InnoDB is a storage engine used in MySQL and MariaDB relational database management systems. It is the default storage engine in MySQL and a popular choice in MariaDB due to its support for transactional processing, high concurrency, and scalability. In this article, we will explore the features and advantages of InnoDB, its architecture, and how it differs from other storage engines in MariaDB.
Architecture of Innodb
InnoDB uses a Multi-Versioning Concurrency Control (MVCC) system to handle concurrency. This allows multiple transactions to read and write to the same data simultaneously without blocking each other. Each transaction sees a snapshot of the database at the start of the transaction, which allows for consistent reads. InnoDB also uses row-level locking, which means that only the rows being modified by a transaction are locked, rather than locking the entire table. This allows for better concurrency than table-level locking used by some other storage engines.
InnoDB stores data in a tablespace, which is a collection of files that can be shared across multiple tables. Each table in InnoDB is stored as a separate file in the tablespace, which allows for better performance and more efficient use of disk space. InnoDB also supports multiple tablespaces, which can be useful for partitioning data across different physical storage devices.
InnoDB uses a clustered index to store data, which means that the primary key is stored with the data in the same B-tree index. This allows for faster queries when using the primary key to search for data. InnoDB also supports secondary indexes, which are stored separately from the data in their own B-tree index.
InnoDB also uses a buffer pool to cache frequently-accessed data in memory. This helps to reduce disk I/O and improve performance. InnoDB also supports adaptive hash indexes, which can be used for frequently accessed small tables.
Transactions and Concurrency Control in Innodb
InnoDB is a transactional storage engine, which means that it supports the ACID properties of database transactions. Transactions are a sequence of operations that must be completed as a single unit of work. If any operation fails, the entire transaction is rolled back to its original state.
InnoDB uses the MVCC system to handle concurrency. This means that multiple transactions can read and write to the same data simultaneously without blocking each other. Each transaction sees a snapshot of the database at the start of the transaction, which allows for consistent reads.
Locking is used in InnoDB to ensure that transactions do not interfere with each other. InnoDB uses row-level locking, which means that only the rows being modified by a transaction are locked. This allows for better concurrency than table-level locking used by some other storage engines.
InnoDB also supports deadlock detection and automatic rollback to prevent transactions from getting stuck in a deadlock situation.
Further details about the innodb storage engine is provided in the second part of the article named ( Innodb Storage Engine (part 2) )
Following is the link to part 2
Innodb Storage Engine (part 2)
Top comments (0)