MySQL Storage engine Introduction 1

1.1 Use of the storage engine

• Each table in the database is processed by the storage engine specified (when the table was created).
• The engines available to the server depend on the following factors:
  • Version of MySQL
  • How the server is configured at development time
  • Startup options
• To understand which storage engines are available in the current server, use the show engines statement:

Mysql> SHOW Engines\g

• When you create a table, you can use the engine option to explicitly specify the storage engine for the CREATE TABLE statement.

CREATE TABLE T (i INT) ENGINE = MyISAM;

• If the storage engine is not explicitly specified when the table is created, the table uses the current default storage engine
• The default storage engine can be specified using the Default-storage-engine option in the My.ini configuration file.
• The storage engine for an existing table can be changed using the ALTER TABLE statement:

ALTER TABLE t ENGINE = INNODB;

• To determine the storage engine used by a table, you can use the show CREATE table or Show table status statement:

mysql> SHOW CREATE TABLE emp\g

Mysql> SHOW TABLE STATUS like ' emp ' \g

1.2 Common storage Engine 1.2.1 MyISAM storage engine

• The MyISAM storage engine is the most commonly used engine for MySQL.
• The tables it manages have the following characteristics:

– Use three files to represent each table:

• Format file-definition of the storage table structure (MYTABLE.FRM)
• Data file-stores the contents of the table row (mytable. MYD)
• Index file-stores the index on the table (mytable. MYI)

– Flexible auto_increment field handling

– can be converted to compressed, read-only tables to save space

1.2.2 InnoDB Storage Engine

• The InnoDB storage engine is the default engine for MySQL.
• The tables it manages have the following key characteristics:

– Each InnoDB table is represented in the database directory as a. frm format file

The –innodb table space is used to store the contents of the table

– Provides a set of log files used to record transactional activity

– Support for transactions with commit (commit), savepoint, and rollback (rollback)

– Provides full acid compatibility

– Provides automatic recovery after a MySQL server crash

– Multi-version (MVCC) and row-level locking

– Support for the integrity of foreign keys and references, including cascading deletions and updates

1.2.3 Memory Storage Engine

• Using the memory storage engine's table, whose data is stored in RAM and the length of the row is fixed, these two features make the memory storage engine very fast.
• The tables managed by the memory storage engine have the following characteristics:

– Within the database directory, each table is represented as a file in the. frm format.

– Table data and indexes are stored in memory.

– Table-level locking mechanism.

– cannot contain text or BLOB fields.

• The memory storage engine was formerly known as the Heap engine.
• When you create a table, you should choose the appropriate storage engine based on the scenario of the table.
• The MyISAM table is best suited for mixed operations where a large amount of data is read while a small amount of data is updated.

1.3 Choosing the right storage engine

Another scenario for MyISAM tables is to use a compressed read-only table.

• If your query contains more data update operations, you should use InnoDB. Its row-level locking mechanism and multi-version support provide a good concurrency mechanism for data read and update hybrid operations.
• Memory storage engines can be used to store data that is not permanently required, or data that can be regenerated from disk-based tables.

MySQL Storage engine Introduction 1