Database Storage Engine

Different storage engines provide different storage mechanisms, indexing techniques, lock levels, and other features that use different storage engines and can also get specific functionality.

Viewing the storage engine

SHOW ENGINES

See which engine the database uses by default

SHOW VARIABLES like ' storage_engine ';

In MySQL, there is no need to use the same storage engine throughout the server, and for specific requirements, you can use a different storage engine for each table. The value of the support column indicates whether an engine can be used: Yes indicates that it can be used, no indicates that it cannot be used, and default indicates that the engine is the current default storage engine. Here's a look at some of the most commonly used engines.

InnoDB Storage Engine (recommended)

InnoDB is the preferred engine for transactional databases, supports transaction security tables (ACID), supports row locking and foreign keys, and sees that InnoDB is the default MySQL engine.

InnoDB Main Features

• Provides a secure (acid-compatible) storage engine for MySQL with commit, rollback, and crash resiliency. InnoDB locks the row-level and also provides a non-locking read similar to Oracle in the SELECT statement. These features increase multi-user deployment and performance. In SQL queries, you are free to mix tables of the InnoDB type with other MySQL table types, even in the same query
• The InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB its tables and indexes in a logical table space, the tablespace can contain several files (or raw disk files). This differs from the MyISAM table, such as in the MyISAM table where each table is stored in a detached file. The InnoDB table can be any size, even if the file size is limited to 2GB on the operating system
• INNODB supports foreign key integrity constraints, when storing data in a table, the storage of each table is stored in the primary key order, and if no primary key is specified when the table definition is displayed, InnoDB generates a 6-byte rowid for each row, which is used as the primary key

Using the InnoDB storage engine MySQL will create a 10MB size automatic extension data file named Ibdata1 in the data directory, and two log files of 5MB size named Ib_logfile0 and Ib_logfile1

MyISAM Storage Engine

The MyISAM is based on the ISAM storage engine and extends it. It is one of the most commonly used storage engines in the Web, data warehousing, and other application environments. MyISAM has high insertion, query speed, but does not support things.

MyISAM Main Features:

• Supported by large file system and operating system
• When the delete and update and insert operations are mixed, the rows of the dynamic dimensions produce less fragmentation. This is done by merging adjacent deleted blocks, and if the next block is deleted, it will be expanded to the next one automatically.
• The maximum number of indexes per MyISAM table is 64, which can be changed by recompiling. The maximum number of columns per index is 16
• The maximum key length is 1000 bytes, which can also be compiled to change, for a key length of more than 250 bytes, a key more than 1024 bytes will be used
• BLOBs and text columns can be indexed
• Null is allowed in the indexed column, which accounts for 0~1 bytes per key
• All numeric key values are stored in high-byte precedence to allow a higher index compression
• Each MyISAM-type table has an internal autoincrement column that is updated when the insert and update operations, and the AutoIncrement column is refreshed. So, the AutoIncrement column update for the MyISAM type table is faster than the InnoDB type AutoIncrement
• Data files and index files can be placed in different directories
• Each character column can have a different character set
• Tables with varchar can be fixed or dynamically recorded in length
• varchar and char columns can be up to 64KB

Creating a database using the MyISAM engine will result in 3 files. The name of the file begins with the name of the table, the file type of the extension: frm file store table definition, data file extension. MYD (MYData), the extension of the index file. MYI (Myindex)

Memory Storage Engine

The memory storage engine stores the data in the table in the RAM, providing quick access without querying and referencing other table data.

Memory Main Features:

• Each table in the memory table can have up to 32 indexes, 16 columns per index, and a maximum key length of 500 bytes
• Memory storage engine performs hash and btree miniatures
• can have non-unique key values in a memory table
• The memory table uses a fixed record length format
• Memory does not support BLOB or text columns
• Memory supports auto_increment columns and indexes on columns that can contain null values
• The memory table is shared between the clients (like any other non-temporary table)
• Memory tables are stored in memory, memory tables and servers are idle during query processing, and internal table shares are created
• When you no longer need the contents of the memory table, to release the memories used by the storage table, you should execute delete from or TRUNCATE table, or delete the entire table (using drop table)

Selection of storage engines

In practical work, choosing a suitable storage engine is a complicated problem. Each storage engine has its own pros and cons, and cannot generally say who is better than who. However, it is recommended to use InnoDB

InnoDB: Supports transactional processing, supports foreign keys, supports crash-repair capabilities, and concurrency control. If you need to have high integrity requirements for transactions (such as a bank) and require concurrency control (such as ticketing), then choosing InnoDB has a big advantage. If you need frequent updates, delete operations of the database, you can also choose InnoDB, because the transaction support commits (commit) and rollback (rollback).

MyISAM: Fast insert data, low space and memory usage. If the table is primarily used to insert new records and read out records, then choosing MyISAM can achieve high efficiency. It can also be used if the integrity and concurrency requirements of the application are low.

Memory: All of the data is in RAM, the data is processed fast, but the security is not high. If you need fast read and write speed, the data security requirements are low, you can choose Memoey. It has requirements for the size of the table and cannot build too large a table. Therefore, this type of database is only used in relatively small database tables.

Note: The same database can also use a variety of storage engine tables. If a table requires relatively high transactions, you can choose InnoDB. This database can select MyISAM storage for tables with higher query requirements. If the database requires a temporary table for querying, you can select the memory storage engine.

Database Storage Engine