{"id":12305,"date":"2023-11-12T03:14:33","date_gmt":"2023-11-12T10:14:33","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=12305"},"modified":"2024-01-08T17:37:22","modified_gmt":"2024-01-09T00:37:22","slug":"mysql-innodb-storage-engine","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-innodb-storage-engine\/","title":{"rendered":"MySQL InnoDB Storage Engine"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you will learn about the MySQL InnoDB storage engine, its features, and benefits.<\/p>\n\n\n\n

Introduction to MySQL InnoDB storage engine<\/h2>\n\n\n\n

InnoDB is a general-purpose and default storage engine<\/a> in MySQL that balances reliability and performance.<\/p>\n\n\n\n

When you create a table using the CREATE TABLE<\/a> statement without the ENGINE<\/code> clause, MySQL creates a table with the storage engine InnoDB unless you have a different default storage engine configuration:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> sample_table(\n   ... \n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you want to create an InnoDB table explicitly, you can set the ENGINE<\/code> clause to INNODB<\/code> as follows:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> sample_table(\n   ... \n) ENGINE<\/span>=INNODB<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Please note that MySQL has used InnoDB as the storage engine since version 5.5. Before that, it used MyISAM as the default storage engine.<\/p>\n\n\n\n
To convert a table from one storage engine to InnoDB, you use the ALTER TABLE<\/code> statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> sample_table ENGINE<\/span> = InnoDB<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The key features of MySQL InnoDB storage engine<\/h2>\n\n\n\n
Here are some key features of InnoDB storage engines.<\/p>\n\n\n\n
2) Row-Level locking<\/h3>\n\n\n\n
InnoDB uses row-level locking that allows multiple transactions to modify different rows within the same table simultaneously without blocking each other.<\/p>\n\n\n\n
The row-level locking enhances the concurrency and performance of the database.<\/p>\n\n\n\n
3) Foreign key support<\/h3>\n\n\n\n
InnoDB supports foreign key<\/a>s that allow you to create relationships between tables and enforce referential integrity in the database.<\/p>\n\n\n\n
The foreign key support is crucial for maintaining data consistency.<\/p>\n\n\n\n
4) Automatic crash recovery<\/h3>\n\n\n\n
InnoDB storage engine provides an automatic crash recovery mechanism that helps the database recover from system crashes or unexpected shutdowns. <\/p>\n\n\n\n
This feature ensures the database is in a consistent state without the need for manual intervention.<\/p>\n\n\n\n
5) MVCC (Multi-Version Concurrency Control)<\/h3>\n\n\n\n
The MVCC feature manages concurrent access to data so that readers don’t block writers and vice versa. Due to the MVCC feature, InnoDB tables allow for high levels of concurrency.<\/p>\n\n\n\n
6) Full-Text Search<\/h3>\n\n\n\n
InnoDB fully supports full-text search<\/a> that allows you to perform complex text-based searches on data stored in the tables.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • InnoDB storage engine balances high reliability and performance.<\/li>\n\n\n\n
  • InnoDB is the default storage engine of MySQL 5.5 or later.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
1) Transaction support<\/h3>\n\n\n\n
InnoDB fully supports transactions<\/a> that allow you to group multiple SQL statements into a single transaction. It allows you to commit or roll back a transaction as a unit, ensuring data consistency and reliability.<\/p>\n\n\n\n