How to enable 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 offers 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 has a number of different storage engines configured by default and can be pre-set or enabled in 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 group together a series of equivalent MyISAM tables and reference them as 1 objects. Ideal for VLDB environments such as data warehousing.

Archive: Provides the perfect solution for storing and retrieving large amounts of historically, archived, or security audit information that is rarely referenced.

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

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

Other: The other storage engines include CSV (referencing a comma-delimited file used as a database table), blackhole (for temporary suppression of application input to the database), and the example engine, which can help with the quick creation of a custom plug-in storage engine.

3. How to start the InnoDB engine

MySQL is the default 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 transactional processing, only the default case is disable. In all engines, InnoDB performance is the most powerful, commercial grade. here's how to start the InnoDB engine :

1) Turn off the MySQL service

2) Modify My.ini

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

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

3) Restart MySQL service after saving

4. Basic differences between the MyISAM engine and the InnoDB engine

• MyISAM types do not support advanced processing such as transaction processing, and InnoDB type support.
• The MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support, while InnoDB provides transactional support for advanced database functions such as external keys.
• Myiasm is a new version of the Iasm table, with the following extensions: Portability at the binary level, NULL column indexes, less fragmentation of variable-length rows than ISAM tables, support for large files, better index compression, etc.
• InnoDB does not support indexes of fulltext types
• The exact number of rows in the InnoDB table is not saved
• For fields of type auto_increment, InnoDB must contain only the index of the field, but in the MyISAM table, you can establish a federated index with other fields
• Delete from table, InnoDB does not reestablish the table, but deletes one row at a time
• ............

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 the performance benefits of MySQL.

How to enable the InnoDB data engine in MySQL