MySQL storage engine

MySQL storage engine

MySQL introduction:

It is a relational database management system developed by MySQL AB in Sweden and currently belongs to Oracle.

MySQL is an associated database management system that stores data in different tables rather than in a large warehouse. This increases the speed and flexibility.

Because of its small size, fast speed, and low total cost of ownership, especially the open source code, many small and medium websites have chosen MySQL as their website database to reduce their total cost of ownership.

Compared with other large databases such as Oracle, DB2, and SQL Server, MySQL has its own shortcomings, such as small scale and limited functions (MySQL Cluster has relatively poor functions and efficiency) and so on, but this does not reduce its popularity.

Storage engine:

MyISAM

The default database engine before Mysql5.5 is the most commonly used. High insertion and query speed, but not transactions

InnoDB

The preferred engine for transactional databases. It supports ACID transactions and row-level locking. MySQL 5.5 is the default database engine.

BDB

Originated from Berkeley DB, another choice for transactional databases, supporting other transaction features such as COMMIT and ROLLBACK

Memory

All data is stored in the memory storage engine, with extremely high insertion, update, and query efficiency. However, memory space is directly proportional to the data volume. And the content will be lost when Mysql is restarted.

Merge

Combining a certain number of MyISAM tables into a whole is useful in ultra-large-scale data storage.

Archive

It is ideal for storing a large number of independent data records as historical data. Because they are not frequently read. Archive has an efficient insert speed, but its query support is relatively poor.

Federated combines different Mysql servers to form a complete database logically. Suitable for distributed applications

Cluster/NDB

The high-redundancy storage engine uses multiple data machines to provide services to improve overall performance and security. Suitable for applications with large data volumes, high security and performance requirements

CSV

A storage engine that logically separates data by commas. It creates a. CSV file for each data table in the database subdirectory. This is a common text file. Each Data row occupies one text row. The CSV storage engine does not support indexing.

BlackHole

The black hole engine will disappear any data written. It is generally used to record binlog for replication relay.

EXAMPLE

The storage engine is a stub engine that does nothing. It is used as an example of MySQL source code to demonstrate how to write a new storage engine. Similarly, it is mainly interested in developers. The EXAMPLE storage engine does not support indexing.

In addition, the storage engine interface of Mysql is well defined. Interested developers can write their own storage engines by reading the documentation.

Theoretically, you can create an index for each field in the data table, but MySQL limits the total number of indexes in the same data table to 16.

 

How to selectMysqlStorage engine:

1. myisam storage: if the transaction requirements are not high and the query and addition are the main factors, we will consider this engine. (For example, bbs posting and reply tables)

2. innodb Storage: this engine is recommended for storing duplicate data because of high transaction requirements. (For example, order table and account table)

3. memory storage: for example, if our data changes frequently, we do not need to store the data in the database, and frequently query and modify the data, we should consider using this engine.

 

Optimize tableRole:

After a large amount of data is deleted from your database, you may find that the size of the data file is not reduced. This is because fragments are left in the data file after the delete operation.

The data table optimization function can remove the data file fragments left after the delete operation, reduce the file size, and accelerate future read/write operations. You only need to delete data tables in batches or optimize data tables on a regular basis (such as every one or two months.

Optimize table only applies to MyISAM, BDB, and InnoDB tables.