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

This article introduces the knowledge of "what are the advantages and disadvantages of MySQL storage engine". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Today, we mainly share the common storage engines: MyISAM, InnoDB, MERGE, MEMORY (HEAP), BDB (BerkeleyDB), etc., as well as the most commonly used MyISAM and InnoDB engines, which are compared in detail at the end of the article.

Introduction of common storage engines in MySQL

1.InnoDB engine (MySQL5.5 will be used by default later)

The default storage engine in MySQL 5.5 and later has the following advantages:

Good disaster recovery

Support transaction

Use row-level locks

Foreign key association is supported

Hot backup is supported

For tables in the InnoDB engine, the data is physically organized in the form of Cluster Table, the primary key index and data are together, and the data is physically distributed in the order of the primary key.

Buffer management is implemented, which can buffer not only the index but also the data, and the hash index is automatically created to speed up the data acquisition.

Hot backup is supported

2.MyISAM engine

The characteristics are as follows:

Transactions are not supported

Use table-level locks with poor concurrency

After the mainframe downtime, the MyISAM table is easy to be damaged and the disaster recovery is not good.

It can cooperate with lock to realize replication, backup and migration under operating system.

Only the index is cached, and the data cache is implemented using the operating system buffer. May cause too many system calls and are inefficient

Compact storage of data, resulting in smaller indexes and faster full table scan performance

3.MEMORY storage engine

Memory tables are provided, and transactions and foreign keys are not supported. Significantly improve the speed of accessing data, which can be used to cache frequently accessed and reconfigurable data, calculation results, statistics, and intermediate results.

The disadvantages are as follows:

Using table-level locks, although memory access is fast, table-level locks will become a bottleneck if you read and write frequently.

Only fixed-size rows are supported. Fields of type Varchar will be stored as fixed-length Char types, wasting space

TEXT and BLOB fields are not supported. When some queries need to use temporary tables (also using the MEMORY storage engine), if there are TEXT and BLOB fields in the table, they will be converted to disk-based MyISAM tables, which will seriously degrade performance.

Due to the high cost of memory resources, it is generally not recommended to set a large memory table. If the memory table is full, you can clear the data or adjust the memory table parameters to avoid errors.

Data will be lost after the server is restarted, so care should be taken during replication maintenance.

How to choose MySQL storage engine MyISAM and InnoDB

1. The general differences between the two storage engines are as follows:

1) it is very important that InnoDB supports transactions, but MyISAM does not. Transactions are an advanced way of processing, such as in some column additions and deletions, as long as any error can be rolled back, but MyISAM is not.

2) MyISAM is suitable for query and insert-based applications, and InnoDB is suitable for frequent modifications and applications involving high security.

3) InnoDB supports foreign keys, but MyISAM does not

4) since MySQL5.5.5, InnoDB is the default engine

5) InnoDB does not support indexes of type FULLTEXT

6) when the number of rows of a table is not saved in InnoDB, such as select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, but MyISAM can simply read out the number of saved rows. Note that MyISAM also needs to scan the entire table when the count (*) statement contains where conditions.

7) for self-growing fields, the InnoDB must contain an index with only that field, but a federated index can be established with other fields in the MyISAM table.

8) when emptying the entire table, InnoDB is deleted row by row, which is very slow. MyISAM rebuilds the table.

9) InnoDB supports row locking (or locking the entire table in some cases, such as update table set axiom 1 where user like'% lee%'

Some people say that MYISAM can only be used for small applications, but this is just a prejudice.

If the amount of data is large, this needs to be solved by upgrading the architecture, such as sub-table and sub-database, read-write separation, rather than relying solely on the storage engine.

Now generally choose InnoDB, mainly MyISAM full table lock, read-write serial problems, parallel efficiency lock table, low efficiency, MyISAM for read-write-intensive applications generally will not choose.

This is the end of the content of "what are the pros and cons of the MySQL storage engine". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.