MySQL engine introduction ISAM, MyISAM, HEAP, InnoDB

MySQL engine introduction ISAM, MyISAM, HEAP, InnoDB

The MySQL Database Engine depends on how MySQL is compiled during installation. To add a new engine, you must recompile MySQL.

By default, MySQL supports three engines: ISAM, MYISAM, and HEAP. The other two types of INNODB and BERKLEY (BDB) are also frequently used.

If the technology is superb, you can use MySQL ++ API to build an engine.

The following describes several database engines:

ISAM:

ISAM is a well-defined and time-tested data table management method. It is designed to take into account that the number of database queries is much larger than the number of updates. Therefore, ISAM performs read operations quickly without occupying a large amount of memory and storage resources. The two major disadvantages of ISAM are that it does not support transaction processing or fault tolerance: If your hard disk crashes, data files cannot be recovered. If you are using ISAM in a key task application, you must always back up all your real-time data. With its copy feature, MYSQL can support such backup applications.

MyISAM:

MyISAM is the MySQL ISAM extension format and default database engine. In addition to providing a large number of functions for indexing and field management not available in ISAM, MyISAM also uses a table lock mechanism to optimize multiple concurrent read/write operations, the cost is that you need to run the optimize table command frequently to restore the space wasted by the update mechanism. MyISAM also has some useful extensions, such as the MyISAMCHK tool used to fix database files and the MyISAMPACK tool used to restore wasted space. MYISAM emphasizes fast read operations, which may be the main reason why MySQL is so favored by WEB development: In WEB development, a large number of data operations you perform are read operations. Therefore, most VM providers and INTERNET platform providers only allow MYISAM format. An important defect in MyISAM format is that data cannot be restored after the table is damaged.

HEAP:

HEAP allows only temporary tables in memory. HEAP is faster than ISAM and MYISAM in the memory, but the data it manages is unstable. If it is not saved before shutdown, all the data will be lost. When a row is deleted, HEAP does not waste much space. HEAP tables are useful when you need to use SELECT expressions to SELECT and manipulate data. Remember to delete the table after the table is used up.

InnoDB:

The InnoDB database engine is a direct product that creates MySQL flexibility. This technology is MYSQL ++ API. When using MYSQL, almost every challenge you face comes from the fact that the ISAM and MyISAM database engines do not support transaction processing or foreign keys. Although it is much slower than ISAM and MyISAM engines, InnoDB includes support for transaction processing and Foreign keys, both of which are not available in the first two engines. As mentioned above, if your design requires one or both of these features, you will be forced to use one of the two engines.

MySQL officially explains InnoDB as follows: InnoDB provides MySQL with a transaction security (ACID compatible) storage engine with the capabilities of commit, rollback, and crash recovery.

InnoDB locks row-level and also provides an Oracle-style non-locked read in SELECT statements. These features increase multi-user deployment and performance.

There is no need to expand locking in InnoDB, because row-level locking in InnoDB is suitable for very small space. InnoDB also supports foreign key forcing.

In SQL queries, You can freely mix InnoDB tables with other MySQL tables, or even in the same query.

InnoDB is designed for the maximum performance when processing a large amount of data. Its CPU efficiency may be unmatched by any other disk-based relational database engine.

The InnoDB Storage engine is fully integrated with the MySQL server. the InnoDB Storage engine maintains its own buffer pool to cache data and indexes in the main memory.

InnoDB stores its tables and indexes in a tablespace. The tablespace can contain several files (or original disk partitions ). This is different from the MyISAM table. For example, in the MyISAM table, each table is in a separate file.

InnoDB tables can be of any size, even on an operating system with a file size limited to 2 GB.

InnoDB is included in MySQL binary distribution by default. Windows Essentials installer makes InnoDB the default MySQL table on Windows.

InnoDB is used to generate large databases that require high performance. The famous Internet news site Slashdot.org runs on InnoDB.

Mytrix, Inc. Stores more than 1 TB of data on InnoDB, and some other sites process an average of 800 inserts/updates per second on InnoDB.

In general, MyISAM is suitable for: (1) doing a lot of count calculations; (2) Inserting is not frequent, queries are very frequent; (3) no transactions. InnoDB is suitable for: (1) high reliability requirements or transactions; (2) Frequent table updates and queries, and a large chance of table locking.

In general, MySQL provides multiple storage engines by default. You can view the following information:

(1) Check what storage engine your MySQL currently provides: mysql> show engines;

(2) Check your current default MySQL storage engine: mysql> show variables like '% storage_engine % ';

(3) What engine is used for a table (the storage engine used for the table is displayed after the parameter engine in the displayed result): mysql> show create table name;

All performance tests are performed on: Micrisoft window xp sp2, Intel (R) Pentinum (R) M processor 1.6 oGHz 1 GB memory computer.

Test method: 10 queries are submitted consecutively. Total table records: 0.38 million, in seconds.

Engine type MyISAM InnoDB performance difference

Count 0.0008357 3.0163 3609

Query primary keys 0.005708 0.1574 27.57

Query non-primary key 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 is added, MyISAM queries can be accelerated: 4 to 206.09733 times, InnoDB queries can be accelerated by 510.72921 times, And MyISAM updates can be slowed down to the original 1/2, the update speed of InnoDB slowed down to 1/30. It depends on the situation to determine whether to add indexes, such as non-query log tables. do not perform any indexes.

(2) If your data volume is millions and you do not have any transaction processing, MyISAM is the best choice for performance.

(3) InnoDB tables are larger in size. Using MyISAM can save a lot of hard disk space.

In the table we tested, the table occupies the following space:

Engine type MyISAM InnoDB

Data 53,924 KB 58,976 KB

Index 13,640 KB 21,072 KB

Occupied Space: 67,564 KB 80,048 KB

In another million recorded table, the table occupies the following space:

Engine type MyIsam InnorDB

Data 56,166 KB 90,736 KB

Index 67,103 KB 88,848 KB

Occupied Space: 123,269 KB 179,584 KB

-------------------------------------- Split line --------------------------------------

MySQL InnoDB Storage engine lock mechanism Experiment

Startup, shutdown, and restoration of the InnoDB Storage Engine

MySQL InnoDB independent tablespace Configuration

Architecture of MySQL Server layer and InnoDB Engine Layer

InnoDB deadlock Case Analysis

MySQL Innodb independent tablespace Configuration

This article permanently updates the link address: