转发自:http://www.kavoir.com/2009/09/mysql-engines-innodb-vs-myisam-a-comparison-of-pros-and-cons.html
The 2 major types of table storage engines for MySQL databases are
InnoDB and MyISAM. To summarize the differences of features and
performance,
InnoDB is newer while MyISAM is older.
InnoDB is more complex while MyISAM is simpler.
InnoDB is more strict in data integrity while MyISAM is loose.
InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock.
InnoDB has transactions while MyISAM does not.
InnoDB has foreign keys and relationship contraints while MyISAM does not.
InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.
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
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.
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
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.
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.
No full-text indexing.
Advantages of MyISAM
Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
Full-text indexing.
Especially good for read-intensive (select) tables.
Disadvantages of MyISAM
No data integrity (e.g. relationship constraints)
check, which then comes a responsibility and overhead of the database
administrators and application developers.
Doesn’t support transactions which is essential in critical data applications such as that of banking.
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.转自:http://linuxers.org/howto/myisam-or-innodb-mysql-engine
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.
InnoDBMRG_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.
转自:http://www.ovaistariq.net/460/why-do-i-recommend-switching-over-from-myisam-to-innodb/
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.