mysql插件式_MySQL的插件式引擎架构探析

MySQL的插件式存储引擎架构，将开发人员、DBA与存储底层的实现细节隔离开来，提供了一致简单的应用程序模型和API，使专业人员能够为特定的应用程序选择专门的存储引擎，在灵活高效的同时，还很简单。

这种高效且模块化的体系结构为那些希望专门针对特定应用程序需求(如数据仓库、事务处理或高可用性情况)的用户提供了巨大的好处。

如果应用程序变化要求底层存储引擎更改，或者需要添加一个或多个存储引擎以支持新的需求，则无需进行重大的编码或可使工作正常进行。

MySQL的插件式存储引擎架构，使存储引擎能够加载到正在运行的MySQL服务器中，也可以从中卸载。

插入存储引擎

使用INSTALL plugin语句将存储引擎插件共享库加载到MySQL中。例如，要将EXAMPLE引擎插件命名为example，其共享库为ha_example.so，则使用以下语句加载它：

root@database-one 22:50: [(none)]> show engines;

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

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

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

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

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

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

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

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

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

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

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

| FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |

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

9 rows in set (0.00 sec)

root@database-one 22:50: [(none)]> INSTALL PLUGIN example SONAME 'ha_example.so';

Query OK, 0 rows affected (0.06 sec)

root@database-one 22:51: [(none)]> show engines;

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

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

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

| EXAMPLE | YES | Example storage engine | NO | NO | NO |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

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

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

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

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

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

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

| FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |

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

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

10 rows in set (0.00 sec)

要安装插件式存储引擎，插件文件必须位于MySQL plugin目录中，执行install plugin语句的用户必须具有MySQL.plugin表的INSERT权限。

共享库必须位于MySQL server plugin目录中，其位置由plugin dir系统变量指定。

root@database-one 23:01: [(none)]> show variables like 'plugin_dir';

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

| Variable_name | Value |

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

| plugin_dir | /usr/lib64/mysql/plugin/ |

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

1 row in set (0.00 sec)

root@database-one 23:01: [(none)]> exit

Bye

[root@database-one ~]# cd /usr/lib64/mysql/plugin/

[root@database-one plugin]# ls -l *example*

-rwxr-xr-x. 1 root root 401566 9月 29 2016 ha_example.so

-rwxr-xr-x. 1 root root 41980 9月 29 2016 rewrite_example.so

拔出存储引擎

使用UNINSTALL PLUGIN语句可以拔出存储引擎：

root@database-one 23:04: [(none)]> show engines;

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

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

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

| EXAMPLE | YES | Example storage engine | NO | NO | NO |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

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

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

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

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

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

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

| FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |

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

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

10 rows in set (0.00 sec)

root@database-one 23:05: [(none)]> UNINSTALL PLUGIN example;

Query OK, 0 rows affected (0.00 sec)

root@database-one 23:05: [(none)]> show engines;

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

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

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

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

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

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

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

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

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

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

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

| FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |

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

9 rows in set (0.00 sec)

如果拔下现有表所需的存储引擎，则这些表将无法访问，但仍将存在于磁盘上。一般来说，在拔下存储引擎之前，请确保没有使用其作为引擎的表。

从技术角度来看，存储引擎可以差异巨大，根据具体的业务特征研发最适用的存储引擎。一般来说，存储引擎的关键差异在于：

并发性：一些应用程序比其他应用程序有更细粒度的锁需求(如行级锁)。选择正确的锁定策略可以减少开销，从而提高整体性能。并发性还包括对多版本并发控制或“快照”读取等功能的支持。

事务支持：并非每个应用程序都需要事务，但对于那些需要事务的应用程序，有非常明确的需求，如ACID遵从性等。

引用完整性：需要让服务器通过DDL定义的外键强制引用完整性。

物理存储：这包括从表和索引的总页面大小，以及用于存储数据到物理磁盘的格式等所有内容。

索引支持：不同的应用场景往往受益于不同的索引策略。每个存储引擎通常都有自己的索引方法，尽管几乎所有引擎都有一些索引方法(如B树索引)。

内存缓存：不同的应用程序比其他应用程序对某些内存缓存策略的响应更好，因此尽管某些内存缓存对所有存储引擎都是通用的(例如用于用户连接或MySQL的高速查询缓存)，但其他内存缓存只有在某个特定的存储引擎投入使用时才被唯一定义。

性能帮助：这包括用于并行操作、线程并发、数据库检查点、大容量插入处理等的多个I/O线程。

其他目标功能：这可能包括对地理空间操作的支持、对某些数据操作的安全限制以及其他类似功能。