Storage Engines

From Hashmysql
Jump to: navigation, search

What is a "storage engine", and how do I decide which one to use?

A storage engine, also known as a "table type", is the engine which actually handles the data for your table(s). This is an alien concept for people coming from another RDBMS, and it is in fact a pretty blatant violation of one of Codd's rules for relational databases, but in MySQL you are able to choose which engine to use on a table by table basis, so that you are not paying the cost of transactional overhead if you do not need it, or so that you can have row-level locking available when you need it, etcetera.

  1. MyISAM
    • This was the default storage engine until MySQL 5.1. It uses table-level locking (so is not always a good choice if you have a large number of concurrent reads and writes on the same table); it does not support foreign keys or transactions. It is an extremely fast engine aside from cases involving very large BLOB/TEXT columns or concurrent reads/writes, but is somewhat fragile if the server shuts down suddenly. Most of the mysql database tables use MyISAM. Tables are stored in a per-database directory in .MYD files, and their indexes in corresponding .MYI files.
  2. InnoDB
    • This is the default storage engine since MySQL 5.5. It supports row-level locking (making it ideal for high concurrency of reads/writes), foreign keys and transactions, along with robust crash-recovery. Also performs better with large BLOB/TEXT columns. Uses a clustered index against the primary key (controlling physical order of data on disk). Until MySQL 5.5, all InnoDB tables were stored in a single system tablespace by default (typically a file called ibdata1) in the data directory. The innodb_file_per_table setting allows you to override that behaviour. Since MySQL 5.6, innodb_file_per_table is on by default.
  3. NDB
    • This is the storage engine for MySQL Cluster.
    • These tables are stored in RAM (and thus the data will not survive a restart/reboot).
  5. BDB
    • Berkeley DB.