2. InnoDB and XtraDB
MySQL supports several different types of Table Engines also known as “Table Types”. A database can have its tables being a mix of different table engine types or all of the same type.
Most common by far are MyISAM vs InnoDB
MyISAM is older and less sophisticated of the two. In some ways its actually down right primitive, however, you I still run in to MyISAM tables a lot so it’s good to be familiar with them.
InnoDB improves on MyISAM by offering modern features such as:
1. It is fully transactional
2. Has automated crash recovery using logged transactions which we will discuss later
3. And, most importantly, (at least to me) row level locking. MyISAM is limited to table level locking.
MySQL uses a pluggable storage engine architecture that enables storage engines to be loaded into and unloaded from a running MySQL server.
The default storage engine for MySQL as of 5.5 was InnoDB. It is ACID compliant which basically means that it is transactionally safe. It has configurable rollback as well as crash recovery capability. It functions much better than earlier architectures such as MyISAM in dw and reporting uses due to it’s non-locking reads. It supports foreign key constraints which is of course important (just don’t over-do them because they are also a pain) and uses clustered indexes speed up queries based on primary keys.
Percona XtraDB is an enhanced version of the InnoDB storage engine, designed to better scale on modern hardware, and including a variety of other features useful in high performance environments. It is completely backwards compatible with InnoDB. I have yet to run in to an issue in this regard and would swap engines in production without even running a bunch of tests. It just works. Like I mentioned before, XtraDB is the default in Percona and Maria. However, you can still use InnoDB if you want to in Maria (not sure about Percona)
To skip XtraDB in MariaDB add this to my.cnf
# The following should not be needed if you are using a mariadb package:
MyISAM: The default storage engine prior to MySQL 5.5. If you run in to MyISAM tables, you might want to consider upgrading them. You can do this a table at a time as you can run MyISAM and InnoDB/XtraDB tables in the same database. In some cases, MyISAM actually seems to perform better, however this is not usually the case (table level locking yuck) and furthermore, when things go wrong with MyISAM, they can really go wrong. Minor corruption/failure events which more modern engines handle in stride will send you searching for backups with MyISAM.
Memory Engine: This used to be called the Heap engine. Basically, as you would expect, it stores everything in RAM similar to something you would write yourself using hashmaps in java. This can really really speed things up in certain situations, however, limits the amount of data you can deal with in one table quite a bit.
Merge: Allows grouping of identical MyISAM tables so they can be referenced at the same time. I haven’t really run in to a need for this (some uses in datawarehousing and I guess transactional safety is less of an issue in reporting)
Archive: Designed for large data sets that do not change often.
Federated: Allows for databases spread across many machines to be viewed virtually as one. I’ve never tried it this way.
NDB (NDBCLUSTER): Another engine designed to spread a database across multiple machines.