MySQL database storage engine (1)

The storage engine for MySQL refers to the type of table. The storage engine of the database determines how the table is stored on the computer.

First we can look at the storage engine supported by MySQL:

mysql> show engines;+------------+---------+----------------------------------------------------- -----------+--------------+------+------------+| engine     | support  | comment                                                           | transactions | xa   | savepoints |+--------- ---+---------+----------------------------------------------------------------+--------------+------+---------- --+| innodb     | yes     | supports  transactions, row-level locking, and foreign keys     |  YES          | YES  | YES         | |  MRG_MYISAM | YES     | Collection of identical  myisam tables                           | NO            | NO   | NO          | |  blackhole  | yes     | /dev/null storage engine   (Anything you write to it disappears)  | NO            | NO   | NO          | |  csv        | yes     | csv storage engine                                                | no           | no   |  no         | |  memory     | yes     | hash based,  stored in memory, useful for temporary tables       | NO           | NO    | no         | |  FEDERATED  | NO      | Federated MySQL  Storage engine                                  |  null         | null | null        | |  ARCHIVE    | YES     | Archive storage  engine                                           | NO            | no   | no         | |  MyISAM     | DEFAULT | Default engine as of  mysql 3.23 with great performance         | no            | NO   | NO          |+------------+---------+------------------------------------------- ---------------------+--------------+------+------------+8 rows in set  (0.00 sec) mysql> show engines\g+------------+---------+---------------------------------------------------- ------------+--------------+------+------------+| engine     | support  | Comment                                                            | transactions | xa   | savepoints |+------------+---------+--------- -------------------------------------------------------+--------------+------+------------+| innodb      | YES     | Supports transactions,  row-level locking, and foreign keys     | yes           | YES  | YES         | |  MRG_MYISAM | YES     | Collection of identical  myisam tables                           | NO            | NO   | NO          | |  blackhole  | yes     | /dev/null storage engine   (Anything you write to it disappears)  | NO            | NO   | NO          | |  csv        | yes     | csv  storage engine                                               | NO            | NO   | NO          | |  memory     | yes     | hash based, stored in memory, useful for temporary  tables      | no            | no   | no         | |  FEDERATED  | NO      | Federated MySQL  storage engine                                  |  NULL         | NULL | NULL        | |  ARCHIVE    | YES     | Archive storage  engine                                           | no           |  no   | no         | |  MyISAM     | DEFAULT | Default engine as of  mysql 3.23 with great performance         |  no           | no   | no          |+------------+---------+------------------------------- ---------------------------------+--------------+------+------------+8 rows in set  (0.00  SEC)

The

Show engines statement can end with a ";" sign, or you can use "\g" or "\g". The "\g" effect makes the results look more beautiful. As follows:

mysql> show engines\g*************************** 1. row ************************** *      engine: innodb     support: yes      Comment: Supports transactions, row-level locking, and  foreign keystransactions: yes          xa:  yes  savepoints: yes*************************** 2. row ************************* **      engine: mrg_myisam     support: yes      Comment: Collection of identical MyISAM  tablestransactions: no          xa: no   savepoints: no*************************** 3. row ***************************       engine:&nBsp Blackhole     support: yes     comment: /dev/null  storage engine  (anything you write to it disappears) transactions:  no          xa: no  savepoints: no  4. row ***************************       engine: csv     support: yes     comment:  CSV storage engineTransactions: NO           xa: no  savepoints: no*************************** 5. row ************       engine: memory     support:  YES     Comment: Hash based, stored in memory,  Useful for temporary tablestransactions: no          xa:  no  savepoints: no*************************** 6. row ***************************       Engine: FEDERATED     Support: NO      comment: federated mysql storage enginetransactions: null           xa: null  savepoints: null****  7. row ***************************       Engine: archive     support: yes     comment:  Archive storage engineTransactions: NO           xa: no  savepoints: no*************************** 8. row ******       engine: myisam     support: default      Comment: Default engine as of MySQL 3.23 with  Great performancetransactions: no          xa:  NO  Savepoints: NO8 rows in set  (0.00 sec) mysql>

In the query results, the engine parameter refers to the name of the storage engine, support indicates whether MySQL supports the class engine, comment is a comment on the engine, transaction indicates whether the transaction is supported, and XA indicates whether the distributed transaction has an XA specification, The SavePoint parameter indicates whether the save point is supported so that the transaction is rolled back to the savepoint.

You can also use the following statement to view the supported engines for MySQL:

mysql> show variables like  ' have% '; +-------------------------+----------+|  variable_name           | value     |+-------------------------+----------+| have_community_features | yes       | |  have_compress           | YES       | |  have_crypt              | yes       | |  have_csv                 | yes      | |  have_dynamic_loading    | yes      | |  have_geometry           | YES       | |  have_innodb             | yes       | |  have_ndbcluster         | NO        | |  have_openssl            | disabled  | |  have_partitioning       | YES       | |  have_query_cache        | YES       | |  have_rtree_keys         | YES       | |  have_ssl                 | disabled | |  have_symlink            | YES       |+-------------------------+----------+14 rows in set  (0.00 sec) Mysql>

The first column represents the engine name, and the second column, value, indicates the support situation for MySQL. Yes for support, no for unsupported, disabled for support but not yet open.

To view the current MySQL default engine:

Mysql> Show variables like ' storage_engine '; +----------------+--------+| variable_name | Value |+----------------+--------+| Storage_engine | MyISAM |+----------------+--------+1 row in Set (0.00 sec) mysql> Show variables like ' Storage_engine ' \g************** 1. Row ***************************variable_name:storage_engine value:myisam1 row in Set (0.00 sec) mysql>

When you create a table, the storage engine for the table is the default storage engine if you do not specify a storage engine for the table.

This article is from "Custom" blog, declined reprint!

MySQL database storage engine (1)