MySQL engine (reprint)

The MySQL database engine depends on how MySQL was compiled when it was installed. To add a new engine, you must recompile MySQL. By default, MySQL supports three engines: ISAM, MyISAM, and heap. Two other types of InnoDB and Berkley (BDB) are also often available. If you are skilled, you can also use the mysql++ API to make an engine yourself. Several database engines are described below :

    ISAM : ISAM is a well-defined and time-tested method of data table management that, at design time, takes into account that the number of times the database is queried is much larger than the number of updates. Therefore, isam reads quickly and does not consume a large amount of memory and storage resources . isam is that it does not support transactional processing, nor can it be fault-tolerant : If your hard drive crashes, the data file cannot be recovered. If you are using ISAM in mission-critical applications, you must always back up all of your real-time data, and with its replication features, MySQL can support such a backup application.

    MyISAM : MyISAM is the ISAM extended format for MySQL and the default database engine. of the updated mechanism. MyISAM also has some useful extensions, such as the Myisamchk tool for repairing database files and the Myisampack tool for recovering wasted space. myisam emphasizes the fast read operation , This may be the main reason why MySQL is so favored by Web development: The bulk of your data operations in Web development are read operations. So, . MYISAM format is the inability to recover data after a table corruption 。

    HEAP : HEAP allows temporary tables that reside only in memory. Residing in memory makes the heap faster than ISAM and MyISAM, but the data it manages is unstable, and if it is not saved before shutdown, all data is lost . The heap does not waste a lot of space when data rows are deleted. The heap table is useful when you need to select and manipulate data using a select expression. after the table is exhausted.

    InnoDB : The InnoDB database engine is a direct product of the technology that makes MySQL flexible, and this technology is the mysql++ API. . As mentioned earlier, if your design requires accesses than either or both of these features, you will be forced to use one of the latter two engines.  

MySQL is the official explanation for InnoDB: InnoDB provides MySQL with a transaction-safe (acid-compatible) storage engine with Commit, rollback, and crash resiliency. InnoDB locks the row level and also provides an Oracle-style, non-locking read in the SELECT statement, which adds multiuser deployment and performance. There is no need to widen the lock in the InnoDB because a row-level lock in InnoDB is suitable for very small spaces. InnoDB also supports foreign key coercion. In SQL queries, you are free to mix tables of the InnoDB type with other MySQL table types, even in the same query.

InnoDB is designed for maximum performance when dealing with huge amounts of data , and its CPU efficiency can be unmatched by any other disk-based relational database engine.

The InnoDB storage Engine is fully integrated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory . InnoDB stores its table and index in a table space, a tablespace can contain several files (or raw disk partitions). This is different from the MyISAM table, such as in the MyISAM table where each table is in a separate file. The InnoDB table can be any size, even if the file size is limited to 2GB on the operating system.

The InnoDB is included in the MySQL binary distribution by default . Windows Essentials Installer makes InnoDB a default table for MySQL on Windows.

InnoDB is used to generate large database sites that require high performance . The famous Internet news site slashdot.org runs on InnoDB. Mytrix, Inc. stores more than 1TB of data on InnoDB, and there are other sites on the InnoDB that handle an average of 800 insertions/updates per second

    Generally, myisam fit : (1) do a lot of count calculation; (2) infrequently inserted, very frequent queries, (3) no transactions. innodb for : (1) Reliability requirements are higher, or require transactions, (2) Table updates and queries are fairly frequent, and the chances of table locking are larger.

In General,MySQL will provide a variety of storage engines by default, which can be viewed through the following:

(1) See what your MySQL has now provided for storage engine: Mysql> show engines;

(2) Look at your MySQL current default storage engine: Mysql> show variables like '%storage_engine% ';

(3) you want to see what engine the table uses (in the result of the parameter engine is the storage engine that the table is currently using): Mysql> show create table table name;

all performance tests are tested on computers with: Micrisoft window XP SP2, Intel (r) pentinum (r) M processor 1.6oGHz 1G memory.

test Method: Submit 10 consecutive query, table records total: 380,000, time unit s

Engine Type MyISAM InnoDB performance difference

        count                       0.0008357             3.0163                 3609

        Query primary Key                     0.005708              0.1574                27.57

        querying non-primary keys                   24.01                    80.37                 3.348

Update primary key 0.008124 0.8183 100.7

Update non-primary key 0.004141 0.02625 6.338

Insert 0.004188 0.3694 88.21

(1) After the index, for the MyISAM query can be accelerated: 4 206.09733 times times, the InnoDB query 510.72921 times times faster, while the MyISAM update slowed down to the original 1/2,innodb update speed slowed to the original 1/30. to see if the situation determines whether to index, such as the log table without querying, do not do any index .

(2) If your data volume is millions, and there is no transaction processing, then using MyISAM is the best choice for performance.

(3) The size of the InnoDB table is more large, with MyISAM can save a lot of hard disk space.

in this 38w table we tested, the table takes up space as follows:
Engine type MyISAM InnoDB
Data 53,924 kb 58,976 KB
Index 13,640 KB 21,072 KB
total space occupied 67,564 kb 80,048 KB

Another 176W record table, where the table occupies space, is as follows:

Engine type MyIsam Innordb
Data 56,166 kb 90,736 KB
Index 67,103 KB 88,848 KB
total space occupied 123,269 KB 179,584 KB


Reprint: http://www.cnblogs.com/sopc-mc/archive/2011/11/01/2232212.html

MySQL engine (reprint)