MySQL comes with a variety of storage engines. Each and every storage engine has some particular behavior and traits. It is very much important to know about these engines in order to create a well designed database. MySQL stores each database(also called as schema) as a subdirectory of its data directory in the underlying file system. Whenever a table is created, MySQL creates a file named as the name of the table with .frm extension and stores the table definition in it. It should be noted that the case sensitivity depends on the platform as MySQL stores the database name and table definition in the file system of the hosting operating system. On UNIX like systems, database and table names are case-sensitive while on Windows, they are case insensitive. Table's data and indexes are stored differently by each storage engine, however, table definition is handled by the server itself.

Lets look into the various storage engines MySQL offers :

1. The InnoDB Engine : InnoDB is the default and the most important and broadly useful transactional storage engine for MySQL. The main feature of InnoDB is its performance and its ability of automatic crash recovery. It is primarily designed to process many short-lived transactions that usually complete rather than being rolled back.
   InnoDB stores its data in tablespace which is a series of one or more data files. Each table data and indexes can be stored in a separate file. InnoDB implements al four SQL standard isolation levels and uses MVCC to achieve higher accuracy. It defaults to the REPEATABLE READ isolation level, and it has a next-key locking strategy that obviates phantom reads in this isolation level: rather than locking only the rows youve physically contacted in a query, InnoDB locks gaps in the index structure as well, obviating phantoms from being inserted.
   InnoDB tables are built on a clustered index. InnoDBs index structures are very different from those of most other MySQL storage engines. As a result, it provides very fast primary key lookups. However, secondary indexes (indexes that arent the primary key) contain the primary key columns, so if your primary key is large, other indexes will also be large. InnoDB format of storage is platform neutral. InnoDB has a variety of internal optimizations. These include predictive read-ahead for prefetching data from disk, an adaptive hash index that automatically builds hash indexes in memory for very expeditious lookups, and an insert buffer to speed inserts.
   
2. The MyISAM Engine : MyISAM is the default storage engine in version 5.1 and older. It provides a large set of features like full-text indexing, compression and spatial(GIS) functions. However, it doesn't support transactions and it not crash safe.
   Whenever a table is created, MyISAM created two files, a data file with an extension of .MYD and an index file with .MYI extension. MyISAM tables can have either dynamic or static rows, however, which format to use is decided by MySQL based on table definition. The number of rows a MyISAM table can hold is limited primarily by the available disk space on your database server and the largest file your operating system will let you create.
   MyISAM provides numerous features. Some of them are :
   
   • Locking and concurrency : MyISAM locks entire tables, not rows. Readers obtain shared (read) locks on all tables they need to read. Writers obtain exclusive (write) locks. However, you can insert new rows into the table while select queries are running against it (concurrent inserts).
   • Repair : MySQL supports manual and automatic checking and repairing of MyISAM tables, but dont confuse this with transactions or crash recovery. After repairing a table, youll likely find that some data is simply gone. Repairing is slow, too. A table can be checked for errors by CHECK TABLE mytable command and can be repaired by REPAIR TABLE mytable command. myisamchk command-line tool can also be used to check and repair tables when the server is offline.
   • Index features : You can create indexes on the first 500 characters of BLOB and TEXT columns in MyISAM tables. MyISAM supports full-text indexes, which index individual words for complex search operations.
   • Delayed key writes : MyISAM tables marked with the DELAY_KEY_WRITE create option dont write changed index data to disk at the end of a query. Instead, MyISAM buffers the changes in the in-memory key buffer. It flushes index blocks to disk when it prunes the buffer or closes the table. This can boost performance, but after a server or system crash, the indexes will definitely be corrupted and will need repair. Delayed key writes can be configured globally, as well as for individual tables.
     MyISAM tables can be compressed by using myisampack utility. The compressed tables can not be modified while in the compressed state. They offer faster performance, because their smaller size requires fewer disk seeks to find records. Compressed MyISAM tables can have indexes, but theyre read-only. The overhead of decompressing the data to read it is insignificant for most applications on modern hardware, where the real gain is in reducing disk I/O. The rows are com- pressed individually, so MySQL doesnt need to unpack an entire table (or even a page) just to fetch a single row.

3. The Archive engine : The Archive engine fortifies only INSERT and SELECT queries, and it does not fortify indexes until MySQL 5.1. It causes much less disk I/O than MyISAM, because it buffers data writes and compresses each row with zlib as its indited. Also, each SELECT query requires a full table scan.
   Archive fortifies row-level locking and a special buffer system for high-concurrency inserts. It gives consistent reads by ceasing a SELECT after it has retrieved the number of rows that subsisted in the table when the query commenced. It withal makes bulk inserts invisible until theyre consummate. These features emulate some aspects of transactional and MVCC behaviors, but Archive is not a transactional storage engine. It is simply a storage engine thats optimized for high-speed inserting and compressed storage.
4. The Blackhole engine : The Blackhole engine has no storage mechanism at all. It discards every INSERT instead of storing it. However, the server writes queries against Blackhole tables to its logs, so they can be replicated or simply kept in the log. That makes the Blackhole engine popular for fancy replication setups and audit logging.
5. The CSV engine : The CSV engine can treat comma-separated values (CSV) files as tables, but it does not support indexes on them. This engine lets you copy files into and out of the database while the server is running. If you export a CSV file from a spreadsheet and save it in the MySQL servers data directory, the server can read it immediately. Similarly, if you write data to a CSV table, an external program can read it right away. CSV tables are thus useful as a data interchange format.
6. The Federated engine :This storage engine is sort of a proxy to other servers. It opens a client connection to another server and executes queries against a table there, retrieving and sending rows as needed. It was originally marketed as a competitor to features supported in many enterprise-grade proprietary database servers, such as Microsoft SQL Server and Oracle, but that was always a stretch, to say the least. Although it seemed to enable a lot of flexibility and neat tricks, it has proven to be a source of many problems and is disabled by default.
7. The Memory engine : Memory tables (formerly called HEAP tables) are useful when you need fast access to data that either never changes or doesnt need to persist after a restart. Memory tables can be up to an order of magnitude faster than MyISAM tables. All of their data is stored in memory, so queries dont have to wait for disk I/O. The table structure of a Memory table persists across a server restart, but no data survives.
   Memory tables support HASH indexes, which are very fast for lookup queries. Although Memory tables are very fast, they often dont work well as a general-purpose replacement for disk-based tables. They use table-level locking, which gives low write concurrency. They do not support TEXT or BLOB column types, and they support only fixed-size rows, so they really store VARCHARs as CHARs, which can waste memory. MySQL uses the Memory engine internally while processing queries that require a temporary table to hold intermediate results. If the intermediate result becomes too large for a Memory table, or has TEXT or BLOB columns, MySQL will convert it to a MyISAM table on disk.

Refrences

High Performance MySQL, 3rd Edition