Shulou(Shulou.com)06/01 Report--

This article is to share with you about how common storage engines in MySQL differ from each other. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

The four storage engines commonly used in MySQL are: MyISAM storage engine, innoDB storage engine, MEMORY storage engine and ARCHIVE storage engine. This article will focus on the four storage engines, and finally compare the four storage engines.

Storage engine in MySQL

Storage engine

1. The storage engine is actually an access mechanism for database files, such as how to store data, how to index the stored data and how to update and query data.

2. Data in MySQL is stored in files (or memory) using a variety of different technologies, each of which uses a different storage mechanism, indexing techniques, locking levels, and ultimately provides a wide range of different functions and capabilities. In MySQL, these different technologies and related functions are called storage engines.

2. View engine in MySQL

1. Show engines; / / look at the storage engine supported by mysql and get the default storage engine for mysql.

2. Show variables like'% storage_engine'; / / View mysql's default storage engine

3. Show create table tablename; / / look at the storage engine used by a particular table. The default storage engine has been modified!

4. Show table status from database where name= "tablename" / / the storage engine used to accurately view a table in a database

3. Several storage engines commonly used in MySQL:

MyISAM Storage engine:

Location: if MySQL uses the MyISAM storage engine, the database file types include .frm, .MYD and .MYI. The default location is C:\ Documentsand Settings\ All Users\ Application Data\ MySQL\ MySQL Server 5.1\ data.

Storage method: MyISAM this storage engine does not support transactions, does not support row-level locks, only supports concurrent insert table locks, mainly for high-load select.

Indexing method: MyISAM also uses B+tree indexing but is somewhat different from Innodb in its implementation.

The engine is based on the ISAM database engine. In addition to providing a large number of functions such as index and field management that are not available in ISAM, MyISAM also uses a table locking mechanism to optimize multiple concurrent read and write operations, but it needs to often run OPTIMIZE TABLE commands to recover the space wasted by the update mechanism, otherwise fragments will increase and eventually affect data access performance. MyISAM also has some useful extensions, such as the MyISAMChk tool to repair database files and the MyISAMPack tool to recover wasted space. MyISAM emphasizes fast read operations, mainly for high-load select, which may be the main reason why MySQL is so popular with Web development: a large number of data operations in Web development are read operations, so most web hosting providers and Internet platform providers (Internet Presence Provider,IPP) only allow the use of MyISAM format.

MyISAM-type tables support three different storage structures: static, dynamic, and compressed.

Static: means that the size of the defined table column is fixed (that is, it does not contain: xblob, xtext, varchar and other variable length data types), so that MySQL will automatically use the static MyISAM format. Tables that use static formats have higher performance because of the low overhead required to maintain and access data stored in a predetermined format, but this high performance comes at the expense of space because it is fixed at the time of defination. so no matter how large the value in the column is, it will take up the whole space with the maximum value.

Dynamic: if a column (even if there is only one column) is defined as dynamic (xblob, xtext, varchar, etc.), MyISAM automatically uses dynamic. Although dynamic tables take up less space than static tables, it brings performance degradation, because if the content of a field changes, its position is likely to need to move, which will lead to fragmentation, with the increase of data changes. Fragmentation increases and data access performance decreases.

There are two solutions to the problem of reduced data access due to increased fragmentation:

A. Use static data types whenever possible

B. Often use optimize table table_name statements to defragment tables and recover the loss of space caused by updates and deletions of table data. If the storage engine does not support optimize table table_name, you can dump and reload the data, which can also reduce fragmentation

Compressed: if you create read-only tables in the database throughout the lifecycle, you should use MyISAM's compressed tables to reduce space footprint.

Advantages and disadvantages: the advantage of MyISAM lies in its small footprint and fast processing speed. The disadvantage is that the integrity and concurrency of transactions are not supported.

2.innoDB storage engine

Storage location: if MySQL uses the InnoDB storage engine, the database file types include .frm, ibdata1 and .ibd, and there are two storage locations. The default location for .frm files is C:\ Documents and Settings\ All Users\ ApplicationData\ MySQL\ MySQL Server 5.1\ data,ibdata1, and the default location for .ibd files is the data folder under the MySQL installation directory.

The mysql table of the innodb storage engine provides security for transactions, rollbacks, and system crash repair capabilities and multi-version burst control transactions.

Innodb supports self-growing columns (auto_increment). The value of the self-growing column cannot be empty. If it is empty in use, how can it automatically save the existing value and start to increase in value? if there is, but larger than the current one, then save this value.

The innodb storage engine supports foreign keys (foreign key). The table on which the foreign key is located is called the child table and the table on which it depends is called the parent table.

The most important thing for the innodb storage engine is to support transactions and transaction-related functions.

The innodb storage engine supports row-level locks for mvcc.

The innodb storage engine index uses B+Tree

Pros and cons: the advantage of InnoDB is that it provides good transaction processing, crash repair and concurrency control. The disadvantage is that the reading and writing efficiency is poor, and the data space is relatively large.

3.MEMORY storage engine

The memory storage engine is a little different from some of the previous storage engines in that it uses the data stored in it to create tables, and all the data is stored in memory.

Each table based on the memory storage engine actually corresponds to a disk file with the same file name and table name and a type of .frm. This file only stores the structure of the table, and its data files are stored in memory, which is conducive to the rapid processing of data and improve the processing capacity of the whole table.

The memory storage engine uses HASH indexes by default, which is faster than using BMY + tree, which can be referenced at creation time if the reader wants to use the B-tree.

The memory storage engine file data is stored in memory, and if an exception occurs in the mysqld process, the data will disappear when the machine is restarted or shut down. So tables in the memory storage engine have a short life cycle and are generally used only once.

4.ARCHIVE storage engine

The storage engine is ideal for storing large amounts of independent historical data. Different from InnoDB and MyISAM, ARCHIVE provides compression and efficient insertion speed, but this engine does not support indexes, so query performance is poor.

Four. Comparison of four storage engines

InnoDB: supports transaction processing, foreign keys, crash repair and concurrency control. If you need to have high transaction integrity requirements (such as banks) and concurrency control (such as ticket sales), then choosing InnoDB has a great advantage. If you need to update and delete databases frequently, you can also choose InnoDB because transaction commit (commit) and rollback (rollback) are supported.

MyISAM: fast data insertion, low space and memory usage. If the table is mainly used to insert new records and read records, then selecting MyISAM can achieve high processing efficiency. It can also be used if the application has low requirements for integrity and concurrency. If the data table is mainly used to insert and query records, the MyISAM engine can provide higher processing efficiency.

MEMORY: all the data is in memory, the processing speed of the data is fast, but the security is not high. If you need fast reading and writing speed and low data security requirements, you can choose MEMOEY. It has requirements on the size of the table and cannot create too large a table. Therefore, such databases are only used in relatively small database tables. If you only temporarily store data, the amount of data is small, and you do not need high data security, you can choose to save the data in the in-memory Memory engine, which is used in MySQL as a temporary table to store the intermediate results of the query.

If there are only INSERT and SELECT operations, you can choose Archive,Archive to support highly concurrent insert operations, but it is not transaction-safe itself. Archive is very suitable for storing archive data. For example, you can use Archiv to record log information.

Note that tables from multiple storage engines can also be used in the same database. If a table requires high transaction processing, you can choose InnoDB. In this database, the tables with high query requirements can be selected for MyISAM storage. If the database needs a temporary table for the query, you can choose the MEMORY storage engine.

Thank you for reading! On the common storage engines in MySQL what differences they have to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.