MySQL storage engine

Recommended for you: Get network issues from WhatsUp Gold. Not end users.
The 1 data storage engine
1.1.  View the database storage engine type
 1.1.1.  Query with show engines
show engines;
#\G function; the same effect
show engines\g
#The results show that more beautiful
show engines\G 
As shown below.
mysql> show engines \G
*************************** 1. row ***************************
      Engine: ndbcluster
     Support: NO
     Comment: Clustered, fault-tolerant tables
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: 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 engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: YES
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 9. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)

Correlation analysis
Engine storage engine name
Support MYSQL whether to support the engine, YES support,
Comment comments on the engine.
Transactions said whether to support transaction processing, YES support
Whether the XA XA specification of distributed transaction processing, YES support
Does Savepoints support save points, so that the transaction rollback to savepoint, support to YES

1.1.2.   The SHOW statement can also display the storage engine support information
 
SHOW VARIABLES LIKE 'have%';
As shown below.
mysql> mysql> show variables like';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| have_community_features | YES      |
| have_compress           | YES      |
| have_crypt              | YES      |
| have_csv                | YES      |
| have_dynamic_loading    | NO       |
| have_geometry           | YES      |
| have_innodb             | YES      |
| have_ndbcluster         | DISABLED |
| 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.01 sec)

Note: the Variable_name indicates the name of the storage engine, the second column Value MySQL support, YES support, NO says he doesn't support, DISABLED support but not open
Query the default storage engine
show variables like 'storage_engine';
Skill:
The same database can use multiple storage engines, three storage engine: InnoDB,MyISAM,MEMORY,

1.2.  The different characteristics of the 3 storage engine
InnoDB:
Support for transaction processing, support foreign key, while supporting the crash recovery and concurrency control, can realize the transaction commit and rollback (Commit)(Rollback),
Application scenarios: integrity of business is relatively high, required to achieve concurrency control, select the InnoDB storage engine has its great advantage, updated frequently, the delete operation database, also optional InnoDB storage engine
MyISAM:
Insert data fast, space and memory usage is relatively low,
Application: mainly used to insert a new record of the scene and reading records, can realize the processing efficiency, integrity application, concurrency requirements is very low, can also choose the MYISAM storage engine
MEMORY:
All data in memory, speed of processing data quickly,
Applications: to quickly read and write speed, lower the security of the data, you can select the MEMORY storage engine, the table size is required, not build too large surface, use only in relatively small database tables

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by August at December 11, 2013 - 1:45 PM