Mysql Data Engine Introduction and selection method _mysql

introduction of Data Engine

InMySQLIn 5.1, MySQL AB introduces a new plug-in storage engine architecture that allows the storage engine to be loaded into the new MySQL server being shipped.

Use the MySQL plug-in storage engine architecture to allowDatabaseProfessionals choose specialized storage engines for specific application requirements and do not need to manage any particular application coding requirements at all. With the MySQL server architecture, application programmers and DBAs can no longer consider all the underlying implementation details, given the consistent and simple application models and APIs at the storage level. So, although different storage engines have different capabilities, applications are separated from them.

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

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

· The memory storage engine provides an in-memory table. The merge storage engine allows the collection to be processed by the same MyISAM table as a separate table. Like MyISAM, memory and the merge storage engine handle non-transaction tables, both of which are included by default in MySQL.

Note:The memory storage engine is officially determined as the heap engine.

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

· The example storage engine is a "stub" engine, and it does nothing. You can use this engine to create a table, but no data is stored in or retrieved from it. The purpose of this engine is to serve an example in the MySQL source code, which demonstrates how to start writing a new storage engine. Again, its main interest is for developers.

· NDB cluster is a storage engine that is used by MySQL cluster to implement tables that are split onto multiple computers. It is available in the Mysql-max 5.1 binary distribution. This storage engine is currently supported only by Linux, Solaris, and Mac OS X. In the future MySQL distribution, we want to add other platforms to support this engine, including Windows.

· The archive storage engine is used to easily overwrite large amounts of stored data without indexing.

· The CSV storage engine stores data in a comma-delimited format in textfileIn

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

· The federated storage Engine puts the dataRemoteDatabase. In MySQL 5.1, it works only with MySQL, using the MySQL C Client API. In a future distribution, we would like to have it connect to another data source using a different drive or client connection method.

The plug-in storage engine architecture provides a set of standard management and support services that are common to all basic storage engines. The storage engine itself is the component of the database server that is responsible for the actual operation of the basic data maintained at the physical server level.

This is an efficient modular architecture, it provides great convenience and benefits for those who want to focus on specific application requirements, such as data warehousing, transactional, high-availability scenarios, and leveraging a set of interfaces and services that are independent of any storage engine.

Application programmers and DBAs process MySQL databases through the connector APIs and service tiers located above the storage engine. If the application changes need to change the underlying storage engine, or need to add 1 or more additional storage engines to support new requirements, no large coding or process changes are required to achieve such requirements. The MySQL server architecture provides consistent and Easy-to-use APIs that apply to a variety of storage engines, which isolate applications from the underlying complexity of the storage engine.
In the following figure, tographicMethod describes the MySQL plug-in storage engine architecture:



second, choose storage Engine

The various storage engines provided with MySQL are designed with different usage considerations in mind. For more efficient use of plug-in storage architectures, it is best to understand the advantages and disadvantages of the various storage engines.

The following table outlines the storage engines that are provided with MySQL:





The following storage engines are most commonly used:

· MyISAM: The default MySQL plug-in storage engine, which is one of the most commonly used storage engines in the Web, data warehousing, and other application environments. Note that by changing the Storage_engineConfigurationVariable that makes it easy to change the default storage engine for the MySQL server.

· InnoDB: For transactional applications, with many features, including acid transaction support.

· BDB: An alternative to the INNODB transaction engine that supports commit, rollback, and other transactional features.

· Memory: Keep all your data in RAM and provide extremely fast access in environments where you need to quickly find references and other similar data.

· Merge: Allows a MySQL DBA or developer to logically combine a series of equivalent MyISAM tables and reference them as 1 objects. It is ideal for VLDB environments such as data warehousing.

· Archive: Provides a perfect solution for storing and retrieving a large number of rarely-referenced histories, archives, or security audit information.

· Federated: The ability to link multiple separate MySQL servers and create a logical database from multiple physical servers. Ideal for distributed environments or data mart environments.

· Cluster/ndb:mysql's clustered database engine, especially for highPerformanceFind the requested application, which also requires the highest normal working hours and availability.

· Other: The other storage engines include CSV (referencing a comma-separated file used as a database table), blackhole (used to temporarily prohibit application input to the database), and the example engine, which can help you create a custom plug-in storage engine quickly.

Keep in mind that you don't have to use the same storage engine for the entire server or scenario, and you can use a different storage engine for each table in the scenario, which is important.

Third, assign the storage engine to the table

You can specify the storage engine when you create a new table, or specify the storage engine 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 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 now the preferred term.

If you omit the engine or type option, the default storage engine is used. The general default is MyISAM, but you can use--default-storage-engine or--default-table-type server startup options to change it, or by setting storage_engine or Table_ Type system variable to change.
To change the storage engine for an existing table, you can 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 that has not been compiled into MySQL, or try to use a storage engine that is compiled into MySQL but not activated, MySQL creates a MyISAM-type table instead. This behavior is convenient when you copy tables between MySQL servers that support different storage engines. (for example, in a replication build, your primary server may support the transaction storage engine for increased security, but use a non-transactional storage engine for faster speeds from the server.) )

Automatically substituting MyISAM tables when unavailable types are specified, which can cause confusion for new users of MySQL. Whenever a table is automatically changed, a warning is generated.

MySQL always creates a. frm file to keep the table and column definitions. The index and data of a table may be stored in one or more files, depending on the type of table. The server creates a. frm file on top of 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 savepoint.

· NDB: Supports transactions through MVCC, allowing commit and rollback.

· BDB: Supports transactions, allowing commit and rollback.

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


· More secure. You can retrieve data even if MySQL crashes or encounters hardware problems, either automatically recover, or restore from backup plus transaction log.

· You can combine many statements and accept them all at once with a commit statement (if the autocommit is banned).

· You can perform rollback to ignore your changes (if autocommit is banned).

· If the update fails, all of your changes are turned back. (with a non-transactional security table, all changes that occur are permanent).

· The transaction security storage engine can provide better deployment to those tables that are currently being read with many updates.

The non-transactional security table itself has several advantages, because there is no transaction expense and all the benefits can occur:


· Faster

· Requires less disk space

· Less memory is required to perform updates


You can combine transaction security and non-transactional security tables in the same statement to get the best of both. However, in autocommit transactions, the transform to the non-transactional security table is still submitted immediately and will not be rolled back.


Although MySQL supports several transaction security storage engines, for the best results, you should not mix different table types in one transaction. If you have mixed table types that can cause problems,

v. Insert search engine

Before you can use the storage engine, you must use the Install plugin statement to mount the storage Engine plugin (plug-in) to MySQL. For example, to load the example engine, you should first load the Ha_example.so module:
INSTALL pluginha_examplesoname ' ha_example.so '; The
file. So must be located in the MySQL server library directory (typically Installdir/lib).

Six, unplugging the storage engine

to unplug the storage engine, use the UNINSTALL plugin statement:
UNINSTALL Pluginha_example;
If you unplug a storage engine that is being used by an existing table, the tables become inaccessible. Before you unplug the storage engine, make sure that no tables use the storage engine.

To install the plug-in storage engine, the plugin file must be located in the appropriate MySQL library directory, and the user issuing the Install plugin statement must have super permissions.

 

When you create a TABLE, you can specify the storage engine to use by using the engine keyword, and if omitted, use the system default storage engine:

create TABLE t (I INT) ENGINE = MYISAM;


to view the types of storage engines that are supported in the system:

mysql> show Engines
The standard installer only provides support for some of the engines, and if you need to use a different storage engine, you need to recompile with the source code and various parameters. Where default indicates that the system defaults to the storage engine, which can be changed by modifying configuration parameters:

default-storage-engine=myisam

View specific information for a storage engine

mysql> show engine InnoDB status\g;