What is InnoDB storage engine

The InnoDB storage engine provides secure transaction capability. A transaction is a logical groping of statements that is handled by the database server as a single unit.  Either all the statements execute successfully to completion or all modification made by the statements are discarded if an error occurs. 
Transaction system often are described as ACID complient :

ATOMIC - All the statements execute successfully or are canceled as a unit.

CONSISTENT - A database that is in a consistent state when a transaction begins is left in a consistent state by the transaction.

ISOLATED - One transaction does not affected to another transaction.

DURABLE - All the changes made by a transaction that completes successfully are stored properly in the database. Changes are not lost.

Features of InnoDB storage engine:
  1. Provides Full transaction capability with full ACID (Atomic, Consistency, Isolated, and Durabe) compliance.
  2. It has row level locking. By supporting row level locking, you can add data to an InnoDB table without the engine locking the table with each insert and this speeds up both the recovery and storage of information in the database.
  3. The key to the InnoDB system is a database, caching and indexing structure where both indexes and data are cached in memory as well as being stored on disk This enables very fast recovery, and works even on very large data sets.
  4. InnoDB supports foreign key constraints
  5. InnoDB supports automatic crash recovery.
  6. InnoDB supports table compression (read/write)
  7. InnoDB supports spatial data types (no spatial indexes)
  8. Innodb support non-locking ANALYZE TABLE and is only required when the server has been running for a long time since it dives into the index statistics and gets the index information when the table opens.
  9. Innodb does not have separate index files so they do not have to be opened.
  10. Innodb builds its indexes one row at a time in primary key order (after an ALTER), which means index trees aren't built in optimal order and are fragmented.There is currently no way to defragment InnoDB indexes, as 
  11. InnoDB can't build indexes by sorting in MySQL 5.0. Even dropping and recreating InnoDB indexes may result in fragmented indexes, depending on the data.
  12. A table can contain a maximum of 1000 columns.
  13. The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. (1024 bytes for non-64-bit builds before MySQL 5.0.17, and for all builds before 5.0.15.)
  14. The default database page size in InnoDB is 16KB. By recompiling the code, you can set it to values ranging from 8KB to 64KB. You must update the values of UNIV_PAGE_SIZE and UNIV_PAGE_SIZE_SHIFT in the univ.i source file.
  15. InnoDB tables do not support FULLTEXT indexes.
If you like this don't forget to leave a comment below.

Happy Coding:)

Post a Comment

Previous Post Next Post