MySQL – InnoDB vs MyISAM

文章目錄

• Storage Engine
• MyISAM
• InnoDB
• Comparison
• Decision Matrix

2008.06.14

 

Most people who use MySQL know that MyISAM and InnoDB are
the two most-common database engines available with the popular
open-source database provider. I would bet that most of those people
don't even take the time to select a storage-engine and just accept the
database default. Those of you who are left probably heard from a friend
who saw something online that said one of the two is better than the
other. Those of you who are left will still probably learn a thing or
two here

Storage Engine

The storage-engine is what will store, handle, and retrieve
information for a particular table. As hinted by the opening of this
article, there is no be-all end-all solution to every or even most
cases. Each has very specific pros and cons that by design can not be
all-inclusive of each other.

This is a screenshot of some of the available storage engines given to you by phpMyAdmin when you create a new table.

 

MyISAM

Let's start with MyISAM since it is the default engine with MySQL.
MyISAM is based on the older but proven ISAM code but has been extended
to be fully-featured while retaining the reliability. Data in MyISAM
tables is split between three different files on the disk. One for the
table format, another for the data, and lastly a third for the indexes.

The maximum number of rows supported amounts to somewhere around
~4.295E+09 and can have up to 64 indexed fields per table. Both of these
limits can be greatly increased by compiling a special version of
MySQL.

Text/Blob fields are able to be fully-indexed which is of great importance to search functions.

Much more technical information can be found on MySQL's MyISAM Manual Page

.

InnoDB

InnoDB is relatively newer so the scene than MyISAM is so people are
still weary about its use in environments than run fine under MyISAM.
InnoDB is transaction-safe meaning data-integrity is maintained
throughout the entire query process. InnoDB also provides row-locking,
as opposed to table-locking, meaning while one query is busy updating or
inserting a row, another query can update a different row at the same
time. These features increase
multi-user concurrency and performance.

Another great feature InnoDB boasts is the ability to use foreign-key
constraints. FK constraints allows developers to ensure that inserted
data referencing another table remains valid. For example, if you had an
authors table and a books table and you wanted to insert a new book
while referencing the author. The author would have to exist in the
authors table before inserting them in the books table because a foreign
key was specified in the books table. At the same time you would not be
able to delete an author from the authors table if they had any entries
associated with them in the books table. More on this in a later
article...

Because of its row-locking feature InnoDB is said to thrive in high load environments. Its CPU efficiency
is probably not matched by any other disk-based relational
database engine.

 

Comparison

MyISAM in most cases will be faster than InnoDB for run of the mill
sort of work. Selecting, updating and inserting are all very speedy
under normal circumstances. It is the default engine chosen by the MySQL
development team which speaks to its integrity, reliability, and
performance.

InnoDB, or the OSX of the database-engine world, has emerged with
some nifty features and created a niche for itself very quickly.
Boasting features like row-level locking, transaction-safe queries, and
relational table design are all very temping. The first two features
really shine in a table that is constantly getting hammered like a logs,
or search engine-type table. Since queries happen in the blink of an
eye (faster actually) table-level locking(MyISAM) is sufficient in most
other normal cases.

InnoDB recovers from a crash or other unexpected shutdown by replaying
its logs. MyISAM must fully scan and repair or rebuild any indexes or
possibly tables which had been updated but not fully flushed to disk.

Decision Matrix

Is your table is going to be inserted, deleted, and updated much much more than it is going to be selected?	InnoDB
If you need full-text search	MyISAM
If you prefer/require relational database design	InnoDB
Is disk-space or ram an issue?	MyISAM
In Doubt?	MyISAM

There is no winner

.

REMEMBER! It's OK to mix table types in the same database! In fact
it's recommended and frequently required. However, it is important to
note that if you are having performance issues when joining the two
types, try converting one to the other and see if that fixes it. This
issue does not happen often but it has been reported.

http://www.mikebernat.com/blog/MySQL_-_InnoDB_vs_MyISAM