Methods for enabling the InnoDB data engine in MySQL

1, what is the storage engine?

Data in MySQL is stored in files (or memory) in a variety of different technologies. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately provides a wide range of different capabilities and capabilities. By selecting different technologies, you can gain additional speed or functionality to improve the overall functionality of your application. These different technologies and associated functions are called storage engines (also known as table types) in MySQL. MySQL defaults to a number of different storage engines, which can be set up in advance or enabled in the MySQL server.

2, MySQL-supported data engine

MyISAM: The default MySQL plug-in storage engine, which is one of the most commonly used storage engines in the Web, data warehousing, and other application environments.

InnoDB: For transactional applications, with many features, including acid transaction support.

BDB: An alternative to the INNODB transaction engine that supports commit, rollback, and other transactional features.

Memory: Keep all your data in RAM and provide extremely fast access in environments where you need to quickly find references and other similar data.

Merge: Allows a MySQL DBA or developer to logically combine a series of equivalent MyISAM tables and reference them as 1 objects. It is ideal for VLDB environments such as data warehousing.

Archive: Provides a perfect solution for storing and retrieving a large number of rarely-referenced histories, archives, or security audit information.

Federated: The ability to link multiple separate MySQL servers and create a logical database from multiple physical servers. Ideal for distributed environments or data mart environments.

Cluster/ndb:mysql's clustered database engine, especially for applications with high performance lookup requirements, requires the highest uptime and availability.

Other: The other storage engines include CSV (referencing a comma-separated file used as a database table), blackhole (used to temporarily prohibit application input to the database), and the example engine, which can help you create a custom plug-in storage engine quickly.

3, start the InnoDB engine method

MySQL defaults to the MyISAM data engine, unfortunately this engine does not support transaction processing, we need to change the default data engine to InnoDB. where InnoDB and BerkeleyDB support transaction processing, only by default are disable. Of all the engines, the InnoDB performance is the most powerful, commercial grade. The way to start the InnoDB engine is as follows:

1. Turn off MySQL service

2) Modify My.ini

Remove the comment (#) before the Default-storage-engine=innodb

Will skip-innodb this line of comments (plus #)

3 Restart MySQL service after saving

4, the basic difference between the MyISAM engine and the InnoDB engine

The MyISAM type does not support advanced processing, such as transaction processing, but InnoDB type support.

Tables of the MyISAM type emphasize performance, which executes more than INNODB types faster, but does not provide transactional support, while InnoDB provides transactional support for advanced database features such as foreign keys.

Myiasm is a new version of the Iasm table, with the following extensions: binary-level portability, NULL column indexes, fewer fragments for variable-length rows than ISAM tables, support for large files, better index compression, etc.

InnoDB does not support fulltext types of indexes

The exact number of rows in the InnoDB without saving the table

For fields of type auto_increment, InnoDB must contain an index with only that field, but in MyISAM tables, you can establish a federated index with other fields

When you delete the From table, InnoDB does not re-establish the table, but a row of deletes

............

These differences can affect the performance and functionality of your application, so you have to choose the right engine for your business type to maximize MySQL's performance advantage.