If you are unfamiliar with MySQL, or are familiar with other relational database systems, the concept of a storage engine can take some time to understand. In summary, although MySQL communicates and manages data via Structured Query Language (SQL), internally MySQL has different mechanisms to support the storage management and retrieval of the underlying data. The flexibility of MySQL storage engines is both a blessing and a curse. The saying “With great flexibility comes great responsibility” is applicable in this sense. We will not be detailing storage engines in this book, but it is critical that you understand some basic information about storage engine features and capabilities, including the following:
• Transactional and non-transactional
• Persistent and non-persistent
• Table and row level locking
• Different index methods such as B-tree, B+tree, Hash, and R-tree
• Clustered indexes versus non-clustered indexes
• Primary versus secondary indexes
• Data compression
• Full text index capabilities
MySQL supports the capability of pluggable storage engines from other service providers, which includes both open source and commercial offerings. Being an open source product, MySQL offers variants that support additional different storage engines.
There are three primary storage engines that are included by default with MySQL:
• MyISAM A non-transactional storage engine that was the default for all MySQL versions prior to 5.5
• InnoDB The most popular transactional storage engine and the default engine starting with version 5.5
• Memory As the name suggests, a memory based, non-transactional, and non-persistent storage engine
NOTE
Starting with version 5.5, the default storage engine for tables has changed from the MyISAM storage engine to the InnoDB storage engine. This can have a significant effect when you are installing packaged software that relies on the default settings and was originally written for the MyISAM storage engine.
Current versions of MySQL also include the built-in storage engines of ARCHIVE, MERGE, BLACKHOLE, and CSV. Some of the other popular storage engines provided by MySQL or third parties include Federated, ExtraDB, TokuDB, NDB, Maria, InfinDB, Infobright, as well as many more.
TIP
You can use the SHOW CREATE TABLE, SHOW TABLE STATUS, or INFORMATION_SCHEMA.TABLES to determine the storage engine of any given table. Chapter 2 provides detailed examples of these options.
For more information about MySQL storage engines and a more detailed list, visit http://EffectiveMySQL.com/article/storage-engines/.
Leave a Reply