MySQL database storage Engine--go to

Original address: http://pangge.blog.51cto.com/6013757/1303893

Simple Introduction

The storage engine refers to the type of the table. The storage engine of the database determines how the table is stored on the computer. The concept of the storage engine is a feature of MySQL and a plug-in storage engine concept. This determines that the tables in the MySQL database can be stored using different storage methods. According to their different requirements, users can choose different storage methods, whether transaction processing and so on.

Query method and Content analysis

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

1	SHOW ENGINES；

The SHOW engunes statement can end with ";", or it can end with "\g" or "\g". "\g" and ";" function the same, "\g" can make the results appear more beautiful.

12345678910111213141516171819202122232425262728293031

mysql> SHOW ENGINES\G*************************** 1. row ***************************      Engine: MRG_MYISAM     Support: YES     Comment: Collection of identical MyISAM tablesTransactions: NO          XA: NO  Savepoints: NO*************************** 2. row ***************************      Engine: InnoDB     Support: DEFAULT     Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES          XA: YES  Savepoints: YES*************************** 3. row ***************************      Engine: MyISAM     Support: YES     Comment: MyISAM storage engineTransactions: NO          XA: NO  Savepoints: NO###############中间已省略###################*************************** 8. row ***************************      Engine: MEMORY     Support: YES     Comment: Hash based, stored inmemory, useful fortemporary tablesTransactions: NO          XA: NO  Savepoints: NO8 rows in set(0.11 sec)

Parse: In the query result, engine parameter refers to the name of the storage engine; The support parameter indicates whether MySQL supports the class engine, yes supports it; the comment parameter refers to the comment on the engine; The transactions parameter indicates whether the transaction is supported, yes supports The XA parameter indicates whether the Distributed transaction Processing XA specification, yes is supported, and the savepoints parameter indicates whether the savepoint is supported, so that the transaction is rolled back to the savepoint, yes is supported.

As can be seen from the query results, MySQL supports engine parameters including MyISAM, MEMORY, InnoDB, archive, and Mrg_myisam. Where InnoDB is the default storage engine. Use statements to query the default storage engine this code is as follows:

1	SHOW VARIABLES LIKE ‘storage_engine‘;

The result of the code execution is as follows:

1234567

mysql> SHOW VARIABLES LIKE ‘storage_engine‘;+----------------+--------+| Variable_name  | Value  |+----------------+--------+| storage_engine | InnoDB |+----------------+--------+1 row inset(0.10 sec)

Parse: The result shows that 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 changes will take effect.

Use show Tablestatus to view the storage engine type queries supported by all tables in a library as follows:

12345678910111213141516171819202122

mysql> USE hellodbDatabase changedmysql> SHOW TABLE STATUS\G*************************** 7. row ***************************           Name: toc         Engine: MyISAM        Version: 10     Row_format: Fixed           Rows: 0 Avg_row_length: 0    Data_length: 0Max_data_length: 2533274790395903   Index_length: 1024      Data_free: 0 Auto_increment: 1    Create_time: 2013-08-12 16:17:23    Update_time: 2013-08-12 16:17:23     Check_time: NULL      Collation: utf8_general_ci       Checksum: NULL Create_options:        Comment:

Parsing: The results show that for MySQL all tables default to use the MyISAM storage engine, where Name: Table name, Engine: The storage engine used, version: The edition of the storage engine used, Row_format: Row format, For MyISAM storage engines, there is a common dynamic (variable-length recording), fixed (fixed-length recording), compressed (the rows that are stored after compression), compact (common in InnoDB), and rows: The number of rows in the table (this value is estimated for other storage engines); Avg_row_length: The average number of bytes in a row; Data_length: The amount of data in a table, in bytes; Max_data_length: The maximum capacity of a table (different storage engine capacity caps vary) ; index_length: Index data volume, unit byte; Data_free: Storage space that has been allocated but not stored; Auto_increment: The next autogrow value on the field with the autogrow attribute; Create_time: The time when the table was created ; Update_time: The last time the data was updated; Check_time: The time of the last check table using the check command; myisamchk; Checksum: The checksum of the table; Create_options: Other options specified when creating the table; Comment: For MyISAM tables, the information specified by the Comment table option when the table was created is stored, and for InnoDB tables, Stores the remaining tablespace information for the corresponding table space.

Comparison of various storage engine features:

In practice, choosing a suitable storage engine is a complex issue. Each storage engine has its own advantages and cannot be generalized to say who is better than who. The following is a detailed comparison of the storage engines used in different environments and the characteristics of each storage engine, giving different recommendations for selection.

• InnoDB Storage Engine

InnoDB is a storage engine for MySQL databases. InnoDB to MySQL table provides transaction, rollback, crash repair ability, multi-version concurrency control transaction security, Gap Lock (can effectively prevent the appearance of Phantom Read), supporting secondary index, clustered index, Adaptive hash index, support hot standby, row-level lock. And InnoDB is the only engine on MySQL that provides a foreign key constraint.

In the InnoDB storage engine, the table structure of the tables created is stored separately and stored in the. frm file. Data and indexes are stored together and stored in a table space. But by default, MySQL stores all the InnoDB tables of the database in a table space. In fact, this way of management is very inconvenient and does not support advanced features, so it is recommended that each table be stored as a table space implementation by using the server variable innodb_file_per_table = 1.

The InnoDB storage engine can also be selected if a database that requires frequent updates and deletions is required. Because the storage engine can implement transaction commits and rollback.

• MyISAM Storage Engine

The MyISAM storage engine is a common storage engine in MySQL, and the MyISAM storage engine is developed based on the ISAM storage engine. MyISAM supports full-text indexing, compressed storage, spatial indexes (spatial functions), table-level locks, and deferred update index keys. However, MyISAM does not support transactions, row-level locks, or, more intolerable, a full recovery after a crash (can only be repaired manually).

The table for the MyISAM storage engine is stored as 3 files. The name of the file is the same as the name of the table. The extension contains frm, MYD, MYI. Where frm is an extension of the structure of the File store table, myd for the extension of the file store data, which is the abbreviation of MyData, Myi is the extension of the file storage index, which is the abbreviation of Myindex.

The MyISAM storage engine inserts data quickly, with less space and memory usage. If the table is primarily used to insert new and read records, then choosing the MyISAM storage engine enables efficient processing. The MyISAM storage engine can also be selected if the integrity and concurrency requirements of the application are low.

• ARCHIVE

ARCHIVE, see the meaning of the name can be seen in the archive, so many of the advanced features after the archive no longer support only the insertion (insert) and query (select) of the two functions, The archive storage engine does not yet support indexing (it starts supporting indexes after Mysql5.5), but it has a good compression mechanism. Usually used for warehouse use.

The archive storage engine is suitable for storing log information or other data collection classes implemented by time series in the application scenario.

• Csv

CSV is a file that saves data files in CSV format and can be easily imported into other databases (for example, Excel tables, SQL Server, and so on), so it is occasionally recommended to use this storage engine when sharing data between databases. And it does not support indexing; The individual thinks that it is only applicable to data exchange.

• Blackhome

Blackhome is called a black hole, meaning there is no storage mechanism, and any data is discarded, but binary logs are logged. Often used in MySQL replication (relay server), this in the MySQL replication blog will be detailed, please pay attention.

• Federated

Federated can implement a cross-server collation, simply that it can access the data on the remote server storage engine, so it is no longer locally created data will only automatically establish a connection to other servers on the link, a bit similar to the function of the agent, the default is disabled.

• Memory Storage Engine

The memory storage engine is a special kind of storage engine in MySQL. It uses memory stored in memory to create a table, and all data is stored in memory. Data security is low, but lookups and insertions are fast. If the memory exception affects the integrity of the data, if you restart or shut down, all the data in the table is lost, so the life cycle of the table based on the memory storage engine is very short and is generally one-time. Suitable for some special scenarios like lookup and mapping, caching of periodic aggregated data, and so on.

• Mrg_myisam

The Mrg_myisam storage engine merges MyISAM tables by merging multiple MyISAM into one (which, in the view of the user, is working, is actually multiple underlying physical files running).

• Extend some third-party storage engines

(1), OLTP class:

Xtradb:innodb's improved version

PBXT: Supports replication, foreign key constraints, and enables fast access to data on Solid state storage (SSD drives), which is a good storage engine for transactional support, but this bug is no longer fixed and deprecated.

TOKUDB: library database, in the storage of large amounts of data has the advantage of MySQL, there is also MySQL version, its biggest advantage to support the Fractal Tree index structure, which caused it to be independent of the cache and directly caused by the index in the database file will not affect performance. It is generally only suitable for analytical scenarios where large numbers of data are inserted. (Note: The figure here is not a photograph, but a data structure for a complex data connection.) )

(2), column-type storage engine

Column database: This kind of database is best for storing big data, and it is good for data retrieval, but to some extent it needs inverse relational storage, so it may not meet the concept of our relational database paradigm so it is called NoSQL.

Here are a few of the Columnstore engines (all two versions: Community Edition, Commercial Edition):

Infobright: Suitable for dozens of TB in a large environment, support data compression, by default, MySQL does not support the column storage function needs to be customized. Many users, high reputation.

MonetDB: First, its storage model is to slice the data vertically, and secondly, MONETDB is the first database system to optimize the data query using CPU cache, and MonetDB automatically manages and coordinates the indexing mechanism to optimize query efficiency. There are not many users.

INFINIDB:INFINIDB Community Edition (Community Edition) provides a scalable, analytic database engine that is primarily developed for data warehousing, business intelligence, and applications that are less demanding in real time. Based on MySQL build. Includes support for querying, transaction processing, and large data volume loading. There are not many users.

LUCIDDB: Is the only one by one open source RDBMS focused on data warehousing and business intelligence, which uses a columnstore architecture, supports bitmap indexing, hash join/aggregate, and page-level versioning, and most databases initially focus on transactional capabilities, which are later added to the analysis functionality. In contrast, all components in LUCIDDB are designed to meet flexible requirements, high-performance data integration, and large-scale data queries from the outset, and their architectural design is completely user-friendly and easy to operate. There are not many users.

(3), Community storage Engine (Learn):

The next generation version of Aria:maria.

Groona: The full-text indexing engine can be implemented precisely, which can replace the MyISAM feature on the index.

Qqgraph: Support diagram operation, developed by open query

Sphinx: External servers can build an efficient full-text index on MySQL based on MySQL, through C + + development, MySQL support a community engine called Sphinxse, is to let Sphinx directly support the MySQL interface, Compiled support on MariaDB5.5.32.

Spider: Support for Shards, each independent shard can be independent query.

Vpformysql: Supports vertical partitioning, enabling larger levels of data storage for larger data operations.

Selection criteria:

1. Do I need to support business??

2. Do I need to use hot spare?

3, Crash Recovery: Can you accept the crash??

Personal advice:

Storage log or time-based data: MyISAM, ARCHIVE

Forum Application: InnoDB

E-Commerce order: InnoDB

Large data volumes: Infobright, NoSQL, Sphinx

MySQL database storage Engine--go to