Storage engine is a (pluggable) module in MySQL Server which primarily handles the storage layout, reading and writing of the data associated with database tables. MySQL supports different storage engines for different tables.
Why this abstraction useful, if at all? For one thing, it allows familiar SQL interface irrespective of the storage mechanism. Imagine if you could access the contents of a CSV (comma separated values) file using SQL interface? The MySQL CSV Storage engine does exactly that. Storing in CSV is not the most efficient way to organize data for querying purpose, but you can clearly see the benefit of having a storage engine to leverage SQL interface capabilities. MySQL also enables connectivity from almost any popular programming language or platforms -- like C, PHP, Java/JDBC, Perl, etc.
MyISAM and Memory storage engines (introduced in version 3.23.0) were two of the initial popular storage engines which does not support transactional tables. InnoDB storage engine which supports transaction and row level locking was introduced in later versions of MySQL 3.23. InnoDB is the default engine as of MySQL version 5.5.5.
There is also a separate storage engine for cluster which is called NDB. MySQL cluster supports highly available, automatic-failover supported installations mainly used in domains such as telecom.
Note that the query optimizer is largely outside the scope of storage engine as per the existing design. There is lot of scope for improvement in this area where storage engine can help better with query optimizations, but that requires extensions in APIs between core MySQL server and storage engines.
For some gory details of storage engine internals, you can look here:
http://forge.mysql.com/wiki/MySQL_Internals_Custom_Engine
See Also:
- http://dev.mysql.com/doc/refman/5.5/en/storage-engines.html
- http://dev.mysql.com/doc/refman/5.1/en/storage-engines-other.html
