Конвертируем MyISAM в InnoDB

Очень старая (2005 год) статья от Петра Зайцева (Percona) о конвертировании  MyISAM таблиц в InnoDB


Are there any performance benefits with Innodb tables when ? Yes there are, even if you forget about support
of transactions, row level locking and consistent reads. Innodb tables are clustered by PRIMARY KEY. This
means a lot of overhead for writes but PRIMARY KEY reads could be twice as fast compared to MyISAM tables for
disk bound loads. To retrieve the row by PRIMARY KEY MyISAM normally needs 2 reads, while Innodb only one.
If you have table small enough to fit in memory there are more benefits — Innodb caches both data and index
in memory, while MyISAM caches only index, using OS cache for caching data, which means Innodb can have much
better performance especially for Random IO (joins), moreover Innodb builds hash indexes in the buffer pool based on
BTREE indexes, which speeds up lookups even further. This all makes Innodb up to 3 times faster for some heavy join
queries, when data is in memory. Even if tables do not fit in memory you get asynchronous read-ahead and asynchronous
dirty buffers flush which is helpful in some cases.


Читайте еще: