MySQL 引擎 概述

MySQL 引擎 概述

分类： MySQL 2011-12-03 00:58 962人阅读 评论(0) 举报

 

一.引擎说明

MySQL supportsseveral storage engines that act as handlers for different table types. MySQLstorage engines include both those that handle transaction-safe tables and thosethat handle nontransaction-safe tables.

--MySQL 支持不同的的类型的storage engine 来处理不同类型的表，包括事务表和非事务表。

 

MySQL Serveruses a pluggable storage engine architecture that enables storage engines to beloaded into and unloaded from a running MySQL server.

       --MySQL 使用可插入式的存储引擎架构来load 和unload 不同的引擎。

 

To determinewhich storage engines your server supports by using the SHOW ENGINES statement.The value in the Support column indicates whether an engine can be used. Avalue of YES, NO, or DEFAULT indicates that an engine is available, notavailable, or available and currently set as the default storage engine.

       --可以通过show engines 命令来查看server 支持的不同的engines。 Support列的信息包括YES,NO 和 DEFAULT。

 

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| Engine             | Support | Comment                                                       | Transactions | XA   | Savepoints|

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| InnoDB             | DEFAULT | Supports transactions,row-level locking, and foreign keys     |YES          | YES  | YES       |

| CSV                | YES     | CSV storage engine                                             |NO           | NO   | NO        |

| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO        |

| BLACKHOLE          | YES     | /dev/null storage engine (anything youwrite to it disappears) | NO           |NO   | NO         |

| MRG_MYISAM         | YES     | Collection of identical MyISAMtables                          | NO           | NO   | NO        |

| MEMORY             | YES     | Hash based, stored in memory, useful fortemporary tables      | NO           | NO   | NO        |

| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO        |

| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             |NO           | NO   | NO        |

| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

9 rows in set (0.00 sec)

 

mysql> show engines \G

--这里的\G 是垂直显示结果，可以用mysql> help 查看。

--ego      (\G) Send command to mysql server,display result vertically.

 

*************************** 1. row***************************

      Engine: InnoDB

     Support: DEFAULT

    Comment: Supports transactions, row-level locking, and foreign keys

Transactions: YES

         XA: YES

 Savepoints: YES

*************************** 2. row***************************

     Engine: CSV

    Support: YES

    Comment: CSV storage engine

Transactions: NO

         XA: NO

  Savepoints:NO

*************************** 3. row***************************

     Engine: MyISAM

    Support: YES

    Comment: MyISAM storage engine

Transactions: NO

         XA: NO

 Savepoints: NO

*************************** 4. row ***************************

     Engine: BLACKHOLE

    Support: YES

    Comment: /dev/null storage engine (anything you write to it disappears)

Transactions: NO

         XA: NO

 Savepoints: NO

*************************** 5. row***************************

     Engine: MRG_MYISAM

    Support: YES

    Comment: Collection of identical MyISAM tables

Transactions: NO

         XA: NO

 Savepoints: NO

….

 

二.MySQL5.5 supported storage engines

1. InnoDB: A transaction-safe (ACIDcompliant) storage engine for MySQL that has commit, rollback, andcrash-recovery capabilities to protect user data. InnoDB row-level locking(without escalation to coarser granularity locks) and Oracle-style consistentnonlocking reads increase multi-user concurrency and performance. InnoDB storesuser data in clustered indexes to reduce I/O for common queries based onprimary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrityconstraints. InnoDB is the default storage engine as of MySQL 5.5.5.

--InnoDB 是transaction-safe的引擎，它支持事务操作，支持行级锁。InnoDB 在clusteredindex里存储数据，即InnoDB的表就是索引。这样在查询时可以直接使用索引，可以减少IO。InnoDB 支持外键，是MySQL5.5.5 中的默认引擎。

 

2. MyISAM: The MySQL storage engine that isused the most in Web, data warehousing, and other application environments. MyISAMis supported in all MySQL configurations, and is the default storage engineprior to MySQL 5.5.5.

 

3. Memory: Stores all data in RAM forextremely fast access in environments that require quick lookups of referenceand other like data. This engine was formerly known as the HEAP engine.

       --将数据库直接存放在内存里，这样可以提供极快的访问速度。

 

4. Merge: Enables a MySQL DBA or developerto logically group a series of identical MyISAM tables and reference them asone object. Good for VLDB environments such as data warehousing.

 

5. Archive: Provides the perfect solutionfor storing and retrieving large amounts of seldom-referenced historical, archived,or security audit information.

 

6. Federated: Offers the ability to linkseparate MySQL servers to create one logical database from many physicalservers. Very good for distributed or data mart environments.

       --提供一个连接，类似与Oracle中的dblink.

 

7. CSV: The CSV storage engine stores data intext files using comma-separated values format. You can use the CSV engine toeasily exchange data between other software and applications that can importand export in CSV format.

 

8. Blackhole: The Blackhole storage engineaccepts but does not store data and retrievals always return an empty set. Thefunction-ality can be used in distributed database design where data isautomatically replicated, but not stored locally.

 

9. Example: The Example storage engine is“stub” engine that does nothing. You can create tables with this engine, but nodata can be stored in them or retrieved from them. The purpose of this engineis to serve as an example in the MySQL source code that illustrates how tobegin writing new storage engines. As such, it is primarily of interest todevelopers.

 

It is importantto remember that you are not restricted to using the same storage engine for anentire server or schema: you can use a different storage engine for each tablein your schema.

--可以对每个表使用不同的引擎。

 

三. Choosing a Storage Engine

The various storageengines provided with MySQL are designed with different use cases in mind. Touse the pluggable storage architecture effectively, it is good to have an ideaof the advantages and disadvantages of the various storage engines. Thefollowing table provides an overview of some storage engines provided withMySQL:

 

 

四. Setting the Storage Engine

statement:

CREATE TABLE t(i INT) ENGINE = INNODB;

 

If you omit the ENGINEoption, the default storage engine is used. The default engine is InnoDB as ofMySQL 5.5.5 (MyISAM before 5.5.5). You can specify the default engine by usingthe --default-storage-engine server startup option, or by setting the default-storage-engineoption in the my.cnf configuration file.

--在创建表时可以指定表的引擎，如果没有指定，使用默认的引擎，在MySQL 5.5.5 之前，默认引擎是MyISAM，5.5.5 之后是InnoDB。 在server 启动时，可以通过--default-storage-engine参数来指定默认的存储引擎。

 

You can set thedefault storage engine to be used during the current session by setting the default_storage_enginevariable:

SETdefault_storage_engine=MYISAM;

       --也可以在当前的session中使用set 命令来设置默认的引擎。

 

To convert atable from one storage engine to another, use an ALTER TABLE statement thatindicates the new engine:

ALTER TABLE tENGINE = MYISAM;

       --如果我们需要更换表的引擎，可以使用alter 命令来进行修改。

 

If you try touse a storage engine that is not compiled in or that is compiled in butdeactivated, MySQL instead creates a table using the default storage engine.This behavior is convenient when you want to copy tables between MySQL serversthat support different storage engines. (For example, in a replication setup,perhaps your master server supports transactional storage engines for increasedsafety, but the slave servers use only nontransactional storage engines forgreater speed.)

--如果我们使用一个没有编译或者无效的引擎，那么MySQL 会自动使用默认引擎来代替这个无效的引擎。 这个行为可以方便我们在不同的server 之间copy 表。

 

This automaticsubstitution of the default storage engine for unavailable engines can beconfusing for new MySQL users. A warning is generated whenever a storage engineis automatically changed. To prevent this from happening if the desired engineis unavailable, enable the NO_ENGINE_SUBSTITUTION SQL mode. In this case, anerror occurs instead of a warning and the table is not created or altered ifthe desired engine is unavailable.

       --当使用的引擎无效时，如果使用默认引擎自动代替无效的引擎，那么server会生成一个警告。如果要阻止这种自动转换，可以使用 NO_ENGINE_SUBSTITUTION 参数。设置之后，当表之间的引擎不一致时，不会自动转换，而是直接报错，同时对应的表也不能创建。

 

For new tables,MySQL always creates an .frm file to hold the table and column definitions. Thetable's index and data may be stored in one or more other files, depending onthe storage engine. The server creates the .frm file above the storage enginelevel. Individual storage engines create any additional files required for thetables that they manage. If a table name contains special characters, the namesfor the table files contain encoded versions of those characters as describedin Section 8.2.3, “Mapping of Identifiers to File Names”.

--当创建一个新表时，MySQL会创建一个.frm 文件来保存表和列的定义。 至于index 和data 存放在几个文件由存储引擎决定。

 

A database maycontain tables of different types. That is, tables need not all be created withthe same storage engine.

数据库可以包含使用不同引擎的表，表不需要使用相同的存储引擎。

 

设置表的存储引擎示例：

mysql> alter table anqing engine=MyISAM;

Query OK, 1 row affected (0.03 sec)

Records: 1 Duplicates: 0  Warnings: 0

 

mysql> show table status like 'anqing'\G

*************************** 1. row***************************

          Name: anqing

        Engine: MyISAM

       Version: 10

    Row_format: Fixed

          Rows: 1

 Avg_row_length: 55

   Data_length: 55

Max_data_length: 15481123719086079

  Index_length: 1024

     Data_free: 0

 Auto_increment: NULL

   Create_time: 2011-11-23 14:16:42

   Update_time: 2011-11-23 14:16:42

    Check_time: NULL

     Collation: latin1_swedish_ci

      Checksum: NULL

 Create_options:

       Comment:

1 row in set (0.01 sec)

 

mysql>

 

 

五. Overview of MySQL Storage EngineArchitecture

The MySQLpluggable storage engine architecture enables a database professional to selecta specialized storage engine for a particular application need while beingcompletely shielded from the need to manage any specific application codingrequirements. The MySQL server architecture isolates the application programmerand DBA from all of the low-level implementation details at the storage level, providinga consistent and easy application model and API. Thus, although there aredifferent capabilities across different storage engines, the application isshielded from these differences.

 

The MySQL pluggable storage enginearchitecture is shown in Figure 13.1, “MySQL Architecture with PluggableStorage Engines”.

 

The pluggablestorage engine architecture provides a standard set of management and supportservices that are common among all underlying storage engines. The storageengines themselves are the components of the database server that actuallyperform actions on the underlying data that is maintained at the physicalserver level.

 

This efficientand modular architecture provides huge benefits for those wishing tospecifically target a particular application need—such as data warehousing,transaction processing, or high availability situations—while enjoying theadvantage of utilizing a set of interfaces and services that are independent ofany one storage engine.

 

The applicationprogrammer and DBA interact with the MySQL database through Connector APIs andservice layers that are above the storage engines. If application changes bringabout requirements that demand the underlying storage engine change, or thatone or more storage engines be added to support new needs, no significantcoding or process changes are required to make things work. The MySQL serverarchitecture shields the application from the underlying complexity of the storageengine by presenting a consistent and easyto-use API that applies acrossstorage engines.

 

5.1 Pluggable Storage Engine Architecture

MySQL Serveruses a pluggable storage engine architecture that enables storage engines to beloaded into and unloaded from a running MySQL server.

 

5.1.1 Plugging in a Storage Engine

Before a storageengine can be used, the storage engine plugin shared library must be loadedinto MySQL using the INSTALL PLUGIN statement. For example, if the EXAMPLE engineplugin is named example and the shared library is named ha_example.so, you loadit with the following statement:

--在engine 使用之前，需要先将存储引擎加载到sharedlibrary里。 使用install plugin 命令执行加载操作。 如：

mysql> INSTALL PLUGIN example SONAME'ha_example.so';

      

To install apluggable storage engine, the plugin file must be located in the MySQL plugindirectory, and the user issuing the INSTALL PLUGIN statement must have INSERT privilegefor the mysql.plugin table.

--为了能成功install 引擎，引擎的文件必须先存在plugindirectory里，并且指定语句的用户还必须有对mysql.plugin 表的insert 权限。

 

The sharedlibrary must be located in the MySQL server plugin directory, the location ofwhich is given by the plugin_dir system variable.

--Shared library 必须是在server 的plugnidirectory，这个目录由plugin_dir 参数指定。

 

 

5.1.2 Unplugging a Storage Engine

To unplug a storage engine, use the UNINSTALLPLUGIN statement:

mysql> UNINSTALL PLUGIN example;

--卸载引擎使用uninstall命令

 

If you unplug astorage engine that is needed by existing tables, those tables becomeinaccessible, but will still be present on disk (where applicable). Ensure thatthere are no tables using a storage engine before you unplug the storageengine.

       --如果unplug的引擎正在被表使用，那么在unplug 后表将不可访问。 所以在执行unplug 之前必须确保没有表使用该引擎。

 

5.2  The CommonDatabase Server Layer

A MySQL pluggablestorage engine is the component in the MySQL database server that isresponsible for performing the actual data I/O operations for a database aswell as enabling and enforcing certain feature sets that target a specificapplication need.

--storage engine 是server 的一个组件，主要用来负责data I/O 操作，并且也启用一些特性的支持。

 

A major benefit ofusing specific storage engines is that you are only delivered the featuresneeded for a particular application, and therefore you have less systemoverhead in the database, with the end result being more efficient and higherdatabase performance. This is one of the reasons that MySQL has always beenknown to have such high performance, matching or beating proprietary monolithicdatabases in industry standard benchmarks.

--使用指定的storage engines 主要的好处就是根据不同的应用来使用不同的特性，从而减少数据库的压力，提高数据库的性能。

 

From a technicalperspective, what are some of the unique supporting infrastructure componentsthat are in a storage engine? Some of the key feature differentiations include:

--从技术的角度看，storage engine 独特的架构组件有如下：

 

1.     Concurrency: Some applicationshave more granular lock requirements (such as row-level locks) than others.Choosing the right locking strategy can reduce overhead and therefore improveoverall performance. This area also includes support for capabilities such asmulti-version concurrency control or “snapshot” read.

 

2. Transaction Support: Not everyapplication needs transactions, but for those that do, there are very welldefined requirements such as ACID compliance and more.

 

3. Referential Integrity: The need to havethe server enforce relational database referential integrity through DDLdefined foreign keys.

 

4. Physical Storage: This involves everythingfrom the overall page size for tables and indexes as well as the format usedfor storing data to physical disk.

 

5. Index Support: Different applicationscenarios tend to benefit from different index strategies. Each storage enginegenerally has its own indexing methods, although some (such as B-tree indexes)are common to nearly all engines.

 

6. Memory Caches: Different applicationsrespond better to some memory caching strategies than others, so although somememory caches are common to all storage engines (such as those used for userconnections or MySQL's high-speed Query Cache), others are uniquely definedonly when a particular storage engine is put in play.

 

7. Performance Aids: This includes multipleI/O threads for parallel operations, thread concurrency, databasecheckpointing, bulk insert handling, and more.

 

8. Miscellaneous Target Features: This mayinclude support for geospatial operations, security restrictions for certaindata manipulation operations, and other similar features.

 

Each set of thepluggable storage engine infrastructure components are designed to offer aselective set of benefits for a particular application .

--每一种storage engine 架构组件都是根据特定的场合来设计的。

Conversely,avoiding a set of component features helps reduce unnecessary overhead. Itstands to reason that understanding a particular application's set ofrequirements and selecting the proper MySQL storage engine can have a dramaticimpact on overall system efficiency and performance.

 

这里仅仅是个概述，关于各个引擎的详细说明，参考官方文档。