Mysql database storage engine

The storage engine refers to the table type. The storage engine of the database determines how tables are stored on the computer. The concept of a storage engine is a feature of MySQl and an plug-in storage engine. This determines M.

The storage engine refers to the table type. The storage engine of the database determines how tables are stored on the computer. The concept of a storage engine is a feature of MySQl and an plug-in storage engine. This determines M.

Brief Introduction

The storage engine refers to the table type. The storage engine of the database determines how tables are stored on the computer. The concept of a storage engine is a feature of MySQl and an plug-in storage engine. This determines that tables in the MySQl database can be stored in different storage methods. You can select different storage methods and whether to perform Transaction Processing Based on your requirements.

Query method and content Parsing

Use the show engines statement to view the storage engine types supported by the MySQL database. The query method is as follows:

Show engines;

The show engunes statement can end with ";" or "\ g" or "\ G. "\ G" and ";" play the same role, "\ G" can make the results display more beautiful.

Mysql> show engines \ G ***************************** 1. row ************************* Engine: MRG_MYISAMSupport: YESComment: Collection of identical MyISAM tablesTransactions: NOXA: NO Savepoints: NO **************************** 2. row ************************* Engine: InnoDBSupport: DEFAULTComment: Supports transactions, row-level locking, and foreign keysTransactions: YESXA: YES Savepoints: YES **************************** 3. row ************************* Engine: MyISAMSupport: YESComment: MyISAM storage engineTransactions: NOXA: NO Savepoints: NO ############### skipped ###################** * ************************* 8. row ************************* Engine: MEMORYSupport: YESComment: Hash based, stored in memory, useful for temporary tablesTransactions: NOXA: NO Savepoints: NO8 rows in set (0.11 sec)

Resolution: In the query results, the Engine parameter indicates the name of the storage Engine; The Support parameter indicates whether MySQL supports this type of Engine; YES indicates Support; and the Comment parameter indicates Comment on this Engine; the Transactions parameter indicates whether transaction processing is supported, and YES indicates YES. The XA parameter indicates whether a Distributed Transaction Processing XA specification is supported, and YES indicates YES. The Savepoints parameter indicates whether a storage point is supported, so that the transaction can be rolled back to the storage point. YES indicates YES.

The query results show that MySQL supports the following engine parameters: MyISAM, MEMORY, InnoDB, ARCHIVE, and MRG_MYISAM. InnoDB is the default storage engine. You can use the statement to query the default storage engine. The Code is as follows:

Show variables like 'Storage _ engine ';

The result of code execution is as follows:

Mysql> show variables like 'Storage _ engine '; + ---------------- + -------- + | Variable_name | Value | + ------------------ + -------- + | storage_engine | InnoDB | + ---------------- + -------- + 1 row in set (0.10 sec)

Resolution: The default storage engine is InnoDB. If you want to modify the default storage engine, you can modify it in the configuration file my. ini. Change "default-storage-engine = InnoDB" to "default-storage-engine = MyISAM ". Then restart the service and the modification takes effect.

You can use show tablestatus to view the storage engine types supported by all tables in a database as follows:

Mysql> USE hellodbDatabase changedmysql> show table status \ G ***************************** 7. row ************************* Name: tocEngine: MyISAMVersion: 10Row_format: FixedRows: 0 Avg_row_length: 0Data_length: Capacity: 2533274790395903 Index_length: Capacity: 0 Auto_increment: 1Create_time: 2013-08-12 16: 17: 23Update_time: 2013-08-12 16: 17: 23Check_time: Duration: NULL Create_options: Comment:

Personal suggestion:

Store logs or time-based data: MyISAM and ARCHIVE

Forum application: InnoDB

E-commerce order: InnoDB

Large data volumes: Infobright, NoSQL, and sphloud

In my personal summary, I hope it will be useful to bloggers !!

This article is from the "starting point dream" blog. Please keep this source