MySQL Engines: InnoDB vs. MyISAM

转发自：

The 2 major types of table storage engines for MySQL databases are InnoDB and MyISAM. To summarize the differences of features and performance,

1. InnoDB is newer while MyISAM is older.
2. InnoDB is more complex while MyISAM is simpler.
3. InnoDB is more strict in data integrity while MyISAM is loose.
4. InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock.
5. InnoDB has transactions while MyISAM does not.
6. InnoDB has foreign keys and relationship contraints while MyISAM does not.
7. InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.
8. MyISAM has full-text search index while InnoDB has not.

In light of these differences, InnoDB and MyISAM have their unique advantages and disadvantages against each other. They each are more suitable in some scenarios than the other.

Advantages of InnoDB

1. InnoDB should be used where data integrity comes a priority because it inherently takes care of them by the help of relationship constraints and transactions.
2. Faster in write-intensive (inserts, updates) tables because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.

Disadvantages of InnoDB

1. Because InnoDB has to take care of the different relationships between tables, database administrator and scheme creators have to take more time in designing the data models which are more complex than those of MyISAM.
2. Consumes more system resources such as RAM. As a matter of fact, it is recommended by many that InnoDB engine be turned off if there’s no substantial need for it after installation of MySQL.
3. No full-text indexing.

Advantages of MyISAM

1. Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
2. Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
3. Full-text indexing.
4. Especially good for read-intensive (select) tables.

Disadvantages of MyISAM

1. No data integrity (e.g. relationship constraints) check, which then comes a responsibility and overhead of the database administrators and application developers.
2. Doesn’t support transactions which is essential in critical data applications such as that of banking.
3. Slower than InnoDB for tables that are frequently being inserted to or updated, because the entire table is locked for any insert or update.

The comparison is pretty straightforward. InnoDB is more suitable for data critical situations that require frequent inserts and updates. MyISAM, on the other hand, performs better with applications that don’t quite depend on the data integrity and mostly just select and display the data.

转自：

Most people don't bother about what MySQL engine they are using. Generally, it doesn't even matter, the default MyISAM does the job really well but sometimes switching to some other engine or at least knowing what others are capable of, could speed up a lot of things for you. Managing transactions for a Financial website was how I came across InnoDB engine at first. In this article, we will see the difference between MyISAM and InnoDB MySQL engines.

So, lets begin by taking a look at all the engines. Type the following command in the mysql prompt.

mysql> SHOW ENGINES\G;

You will see something the following engines, with a whole bunch of other information.

InnoDB   
MRG_MYISAM
BLACKHOLE 
CSV       
MEMORY
FEDERATED
ARCHIVE  
MyISAM    

As you can see they are a lot but we will focus on MyISAM and InnoDB here. Lets start with their limitations:-

MyISAM limitations

• No Foriegn keys and cascading deletes and updates
• No rollback abilities
• No transactional integrity (ACID compliance)
• Row limit of 4,284,867,296 rows
• Maximum of 64 indexes per row

InnoDB Limitations

• No full text indexing
• Cannot be compressed for fast, read-only

MyISAM uses table level locking and this can be a great problem if your database INSERT/DELETE/UPDATE load is very high. If this is the problem then you should try converting it to INNODB. It manages non transactional tables. It has fast storage and retrieval, as well as full text searching capabilities. Its supported by every MySQL and comes as a default engine.

When to use MyISAM?

MyISAM is designed with the idea that your database is queried far more than its updated and as a result it performs very fast read operations. If your read to write(insert|update) ratio is less than 15% its better to use MyISAM.

When to use InnoDB?

InnoDB uses row level locking, has commit, rollback, and crash-recovery capabilities to protect user data. It supports transaction and fault tolerance.

Convert from one type of engine to other.

You can create a table and tell which storage engine to use, if none is specified MyISAM is used

CREATE TABLE table_name (rid INT) ENGINE = INNODB;

CREATE TABLE table_name (rid INT) TYPE = INNODB;

TYPE is older term and is supported for backward compatibility.

You can set the default storage engine to be used during the current session by setting the storage engine variable:

SET storage_engine=INNODB

Converting tables from one storage mechanism to another can be done by using the alter command:

ALTER TABLE table_name ENGINE = INNODB

Ok, it could be difficult if you have many tables to manually run alter query. So here's another way that will work.

DUMP the sql into a file using mysqldump.

Using sed command update to INNODB engine from MYISAM in the sql file.

转自：

MyISAM has been the default storage engine for MySQL for years now. But that is soon going to change. With MySQL 5.5, Innodb is going to be the default storage engine (and I am pretty pleased about that, no more new users complaining about MySQL not being ACID compliant).

But why do I recommend Innodb over MyISAM. Following are the reasons:

I will start of with reasons not so obviously discussed during comparisons of Innodb and MyISAM storage engines. But these reasons are so compelling that I bet you would want to switch over to Innodb after reading the reasons.

Innodb is being constantly improved and worked upon

There hasn’t been much work put forth into improving the MyISAM storage engine, and with Oracle acquiring MySQL and owning Innodb, it has put its weight on the Innodb storage engine. While there hasn’t been much work done on MyISAM side, Innodb has seen improvements and work is being done aggressively. MySQL 5.5 will ship with a new version of Innodb – version 1.1, which has major performance improvements. Even if you are using MySQL 5.1, you can replace the built-in Innodb with the newer Innodb plugin – version 1.0.8, which has introduced major performance improvements.

Innodb supports row-level locking

Innodb not only supports table-level locking, but also supports row-level locking, making it the storage engine of choice for high concurrency applications.

Innodb supports hot backup

With MyISAM, backing up a live database, is pretty much an issue for many DBAs because of the way how MyISAM supports locking. MyISAM only supports table-level locks which pretty much means that the server must go down during the backup, for sometime depending upon the amount of data in the database. With Innodb this is no issue at all, Innodb supports row level locking which allows you to do hot backups.

Innodb is ideal for high-concurrency applications

Yet another big reason for using Innodb is that with Innodb you can achieve high-user-throughput and high-database-concurrency. That is again thanks to the row-level mechanism of Innodb, which makes it a big winner (compared to MyISAM) in cases where you need highly concurrent read-write operations.

Innodb is crash proof

What I essentially mean by being crash proof is the database, recovering back into a consistent state in case of a crash (be it an OS crash or a MySQL server crash). While in the case of MyISAM, a crash would mean that you would have to deal with recovering tables on the crash or partially executed statements, in the case of Innodb there would be essentially no data inconsistency and all the more its more of an automated recovery, where by Innodb would recover itself by replaying its logs.

Innodb supports clustered indexes

Innodb stores rows ordered physically in PRIMARY KEY order. Storing the rows in primary key order corresponds to what is known in MSSQL Server as “Clustered index” and in Oracle as “index organized tables”. This makes primary key fetches extremely efficient.

Innodb implies various internal optimizations that improve the performance further

There are many different optimizations that Innodb performs internally that improve performance drastically. Following are some of the optimizations:

• Adaptive Hash Indexing:
  To quote from the MySQL manual: “If a table fits almost entirely in main memory, the fastest way to perform queries on it is to use hash indexes rather than B-tree lookups. InnoDB monitors searches on each index defined for a table. If it notices that certain index values are being accessed frequently, it automatically builds an in-memory hash table for that index.
• Read Ahead:
  A read-ahead request is an I/O request sent by Innodb to pre-fetch data pages into the innodb buffer pool cache. Innodb sends these read-ahead requests based on the access patterns of the data pages already in the buffer pool cache. This pre-fetching can improve read performance drastically for data that is being fetched linearly.
• Innodb Buffer Pool:
  While MyISAM relies on the OS cache for caching reads and writes, Innodb has its own cache which it manages on its own and which is of course fine-tuned for Innodb operations. And combine this with adaptive hash indexing and read-aheads, and you can imagine the performance gains.
• Innodb change buffering:
  When INSERT, UPDATE, and DELETE operations are performed on a table, Innodb buffers these changes into the Innodb buffer pool instead of flushing them directly to disk instantly. These changes are flushed to the disk only when needed. This avoids expensive I/O operations and thus providing great performance boost for applications that do a lot of inserts/updates/deletes. To quote the MySQL manual: “Because it can result in fewer disk reads and writes, this feature is most valuable for workloads that are I/O-bound, for example applications with a high volume of DML operations such as bulk inserts.”
• Innodb background flushes:
  Innodb performs many of the I/O operations using asynchronous background threads, operations like read-ahead requests, dirty-pages flushing etc. This ensures that these I/O operations do not block user operations on the database.

Now come the reasons that are discussed more often, mentioned in all the comparisons of Innodb and MyISAM. Although you might not need transactions or foreign key constraints in all of your applications, but these do make Innodb a complete winner when compared with MyISAM.

Innodb supports transactions

Innodb supports ACID-compliant transactions, what that means is that you can group SQL statements in a transaction and be sure that the database will be in a consistent state, either you commit the transaction or rollback, or even more so the server crashes in the middle of a SQL statement.

Innodb supports foreign key constraints

Innodb supports foreign keys, so that you can enforce parent-child relationships. Operations such as deleting child rows when parent is deleted, or updating the child rows on updating of parent row are supported and enforced. This ensures that the database takes care of ensuring that the relationships are consistent, without the onus being on the application developer.

Conclusion:

If you are still not convinced about Innodb and have some questions in mind you may discuss them out with me.

What’s coming?

I had written a post about tuning MySQL server settings applicable to the MyISAM storage engine, I will soon be posting about tuning Innodb related MySQL server settings. Besides that, I will soon be deploying MySQL 5.5 on the server hosting this blog and I will be sharing my findings soon. Besides that I plan on posting some benchmarking results for MyISAM vs Innodb.