Mysql storage engines

dieutb

·

Follow

4 min read

·

Apr 15, 2021

--

Listen

Share

Mysql Table Engine (Storage Engine)

• There are some table ENGINEs:
  - InnoDB (default, most widely used storage engine)
  - MyISAM
  - Memory
  - CSV
  - Merge (also known as the MRG_MyISAM engine)
  - Archive
  - Federated
  - Blackhole

### Command check supported engines:
mysql> SHOW ENGINES;

• Common create Index command:

InnoDB

• Row locking: lock a row when it’s inserting/updating/deleting. The read-request of a row has to wait until this row’s write-process finish
• The only engine support for foreign_key and transaction
• Index type support: B-Tree, Hash
• read more

MyISAM

• Table locking: lock a whole table if there’s inserting/updating/deleting happen. The read-request has to wait until write-process finish
• Simplest structure and design for the fastest reading (read-only or read-mostly workloads)
• It’s not suitable for big data writing. It could cause crashed Index and Table.
• Store table, data, index in 3 separate places: table_name.FRM, table_name.MYD, table_name.MYI
• Index type support: B-tree (by default), Hash.
• read more

MEMORY

• Stores all data in RAM. So, the fastest way to query DB.
• When Mysql halves or restarts, all the data is lost.
• It has been replaced by InnoDB with its buffer pool memory area and NDBCLUSTER with its fast key-value lookups for huge distributed data sets.
• Index type support: B-tree, Hash (by default).
• read more

CSV

• Store text files with comma-separated values.
• Could import or dump data in CSV format.
• A table is respective with a file CSV that is stored in “storage/csv” directory of a MySQL.
• No index supported.
• Only compiled into the MySQL server.
• read more

Merge

• could consider as a virtual table that maps to the data of many MyISAM tables (underlying tables) with some conditions. Also can be used as a partitioned table, which stores partitions of a single table in separate files and enables some operations to be performed more efficiently.
• “DROP TABLE [merge_table_name]” only drops the merge table, not the underlying tables.
• is a collection of “identical” MyISAM tables
• identical column data types and index information, but can differ from the column name, index name, comments, table options such as AVG_ROW_LENGTH, MAX_ROWS, or PACK_KEYS

• read more

Archive

• It’s used for storing archived data.
• Only support insert, replace and select.
• It’s stored in “storage/archive” directory of Mysql.
• The data uses zlib lossless data compression.
• No index supported.
• read more

Federated

• is used for creating a quick connection to a remote server.

• In case we have many remote tables that need to be connected. We can create a remote server connection to prevent the duplicated connection and the connection can be managed better.

• read more

BlackHole

• No data stored, but could store binary-log (must be turned on)
• The engine is transaction-aware, only write to the binary log when the transaction commits successfully.
• The engine is usually used as the dummy slave that reduces traffic for a master node in the first time filtering binary log. Instead, the master node has to send a huge binary log to slaves, we could set up the dummy slave in the same server with the master node. After a dummy slave is ready with the binary log, it will synchronize with the others.

• Measurement of the overhead from binary logging, by comparing performance using BLACKHOLE with and without binary logging enabled.
• read more