Introduction and selection of MySql Data Engine

1. Introduction to data engines

InMySQL5.1, MySQL AB introduced a new plug-in storage engine architecture, allowing the storage engine to be loaded into the new MySQL server being migrated.

Use the MySQL plug-in storage engine architecture to allowDatabaseProfessionals select a dedicated storage engine for specific application requirements, and do not need to manage any special application coding requirements. Using the MySQL server architecture, because consistent and simple application models and APIs are provided at the storage level, application programmers and DBAs can no longer consider all the underlying implementation details. Therefore, although different storage engines have different capabilities, applications are separated from each other.

MySQL supports several storage engines as processors for different table types. The MySQL storage engine includes the engine for processing transaction security tables and the engine for processing non-transaction security tables:

· MyISAM manages non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is supported in all MySQL configurations. It is the default storage engine, unless you configure MySQL to use another engine by default.

· The MEMORY storage engine provides "in-MEMORY" tables. The MERGE storage engine allows a set to process the same MyISAM table as a separate table. Like MyISAM, MEMORY and MERGE storage engines process non-transaction tables. Both engines are included in MySQL by default.

Note:The MEMORY storage engine is officially identified as the HEAP engine.

· InnoDB and BDB storage engines provide transaction security tables. BDB is included in the MySQL-Max binary distribution version released for the operating system that supports it. InnoDB is also included in all MySQL 5.1 binary distributions by default. You can configure MySQL to allow or disable any engine as you like.

· The EXAMPLE storage engine is a "stub" engine, which does not do anything. You can use this engine to create tables, but no data is stored or retrieved from them. The purpose of this engine is to provide a service. In the MySQL source code example, it demonstrates how to start writing a new storage engine. Similarly, it is mainly interested in developers.

· NDB Cluster is a storage engine used by MySQL Cluster to split tables into multiple computers. It is provided in MySQL-Max 5.1 binary distribution. Currently, this storage engine is only supported by Linux, Solaris, and Mac OS X. In the future MySQL distribution version, we want to add support for this engine from other platforms, including Windows.

· The ARCHIVE storage engine is used to cover a large amount of data stored without indexing.

· The CSV storage engine stores data in text in a comma-separated formatFile.

· The BLACKHOLE storage engine accepts but does not store data, and retrieval always returns an empty set.

· FEDERATED storage engine stores dataRemoteDatabase. In MySQL 5.1, it only works with MySQL and uses the MySQL C Client API. In future distributions, we want to connect it to another data source using another drive or client connection method.

The plug-in storage engine architecture provides a collection of Standard Management and support services, which are common to all basic storage engines. The storage engine itself is a component of the database server and is responsible for actual operations on the basic data maintained on the physical server layer.

This is an efficient modular architecture that provides great convenience and benefits for those who want to focus on specific application needs, these special application requirements include data warehousing, transaction processing, and high availability. They can also use a group of interfaces and services independent of any storage engine.

Application programmers and DBAs process MySQL databases through the connector API and service layer located on the storage engine. If the application needs to change the underlying storage engine, or you need to add one or more additional storage engines to support new requirements, this type of requirement can be achieved without large coding or process changes. The MySQL server architecture provides consistent and easy-to-use APIs, which are applicable to multiple storage engines. In this way, the architecture isolates applications from the underlying complexity of the storage engine.
InGraphicsThe following describes the architecture of the MySQL plug-in storage engine:

2. Select storage engine

Different storage engines provided with MySQL are designed with different usage considerations. To use the plug-in storage architecture more effectively, it is best to understand the advantages and disadvantages of various storage engines.

The following table describes the storage engine provided with MySQL:

The following storage engines are the most commonly used:

· MyISAM: the default MySQL plug-in storage engine, which is one of the most commonly used storage engines in Web, data warehousing, and other application environments. Note: by changing the STORAGE_ENGINEConfigurationVariable to easily change the default storage engine of the MySQL server.

· InnoDB: used for transaction processing applications. It has many features, including ACID transaction support.

· BDB: it can replace InnoDB's transaction engine and support COMMIT, ROLLBACK, and other transaction features.

· Memory: stores all data in RAM and provides extremely fast access in environments where reference and other similar data need to be quickly searched.

· Merge: Allows MySQL DBAs or developers to logically combine a series of equivalent MyISAM tables and reference them as one object. It is suitable for VLDB environments such as data warehousing.

· Archive: provides a perfect solution for storing and retrieving a large number of rarely referenced historical, archived, or security audit information.

· Federated: connects multiple separated MySQL servers and creates a logical database from multiple physical servers. It is very suitable for distributed environments or data mart environments.

· Cluster/NDB: MySQL Cluster-based database engine, especially suitable for highPerformanceFind the desired application, which also requires the highest normal working time and availability.

· Other: Other storage engines include CSV (referencing files separated by commas as database tables), Blackhole (used to temporarily prohibit database application input ), and the Example engine (which can help you quickly create a custom plug-in storage engine ).

Remember that you do not have to use the same storage engine for the entire server or solution. You can use different storage engines for each table in the solution. This is very important.

3. Specify the storage engine to the table

You can specify the storage engine when creating a new TABLE or by using the alter table statement.

To specify the storage ENGINE when creating a table, you can use the ENGINE parameter:
Create table engineTest (
Id INT
) ENGINE = MyISAM;

You can also use the TYPE option to go To the create table statement to tell MySQL what TYPE of TABLE you want to CREATE.

Create table engineTest (
Id INT
) TYPE = MyISAM;

Although TYPE is still supported in MySQL 5.1, ENGINE is the preferred term.

If you omit the ENGINE or TYPE option, the default storage ENGINE is used. The default value is MyISAM, but you can use the -- default-storage-engine or -- default-table-type server startup option to change it, you can also change it by setting the storage_engine or table_type system variable.
To change the storage engine of an existing TABLE, use the alter table statement:
ALTER TABLEengineTestENGINE = ARCHIVE;
Alter table t ENGINE = MYISAM;
Alter table t TYPE = BDB;

If you try to use a storage engine not compiled into MySQL, or use a storage engine compiled into MySQL but not activated, MySQL will create a MyISAM table instead. When you copy tables between MySQL servers that support different storage engines, the above behavior is very convenient. (For example, in a replication establishment, your master server may support the transaction storage engine for increased security, but the slave server only uses the non-transaction storage engine for faster speed .)

When the unavailable type is specified, the MyISAM table is automatically used for replacement, which will cause confusion for new MySQL users. A warning is generated whenever a table is automatically changed.

MySQL always creates a. frm file to maintain the definition of tables and columns. Table indexes and data may be stored in one or more files, depending on the table type. The server creates a. frm File above the storage engine level. A separate storage engine creates any additional files that need to be used to manage tables.

A database can contain different types of tables.

Iv. storage engines and transactions

The following storage engines support transactions:
· InnoDB: supports transactions through MVCC, allowing COMMIT, ROLLBACK, and storage points.

· NDB: supports transactions through MVCC and allows COMMIT and ROLLBACK.

· BDB: supports transactions and supports COMMIT and ROLLBACK.

The transaction security table (TST) has the following advantages over the non-Transaction Security table (NTST:

· More secure. Even if MySQL crashes or encounters hardware problems, you can recover data automatically or from the backup plus transaction log.

· You can merge multiple statements and use the COMMIT statement to accept all of them at the same time (if autocommit is disabled ).

· You can execute ROLLBACK to ignore your changes (if autocommit is disabled ).

· If the update fails, all your changes will change back to the original one. (For non-transactional security tables, all changes are permanent ).

· The transaction security storage engine can provide better deployment for tables with many updates currently read.

Non-transaction security tables have several advantages, because there is no transaction expense, all advantages can appear:

· Faster

· Requires less disk space

· Requires less memory to execute updates

You can merge the Transaction Security and non-transaction security tables in the same statement to obtain the best of both. Even so, in a transaction that is disabled by autocommit, it is still committed immediately to a non-transactional Security table and will not be rolled back.

Although MySQL supports several transaction security storage engines, you should not mix different table types in one transaction to obtain the best results. If the hybrid table type is incorrect,

5. Insert a search engine

Before using the storage engine, you must use the INSTALL plug-in statement to load the storage engine plug-in to mysql. For example, to load the example engine, you must first load the ha_example.so module:
INSTALL PLUGINha_exampleSONAME 'Ha _ example. so ';
File. so must be located in the MySQL server library directory (typically installdir/lib ).

6. Pull out the storage engine

To pull out the storage engine, use the uninstall plugin statement:
UNINSTALL PLUGINha_example;
If you pull out the storage engine that is being used by existing tables, these tables become inaccessible. Before Pulling out the storage engine, make sure that no tables use the storage engine.

To INSTALL the plug-in storage engine, the plug-in file must be in the appropriate MySQL library directory, and the user who issues the INSTALL plug-in statement must have the SUPER permission.

When creating a table, you can use the engine keyword to specify the storage engine to use. If it is omitted, use the default storage engine:

Create table t (I INT) ENGINE = MYISAM;

View the supported storage engine types in the system:

Mysql> show engines;
The standard installer only supports some engines. To use other storage engines, you must use the source code and different parameters to re-compile. Here, DEFAULT indicates the DEFAULT storage engine of the system. You can modify the configuration parameters:

Default-storage-engine = MyISAM

View the details of a storage engine

Mysql> show engine InnoDB status \ G;