2020-03-19 10:45:03 来源 : 21CTO
本文探讨使用MySQL/MariaDB的Spider存储引擎通过使用单个表链接到多台服务器的实例。
Spider是MariaDB提供的一个新存储引擎,可以让一个标准配置的MariaDB成为一个分布式数据。
虽然实现起来还是会有点复杂,但技术并不太难。本文就来给各位介绍Spider存储引擎的工作与技术原理,并会提供一些实用案例。
MariaDB存储引擎
存储引擎是用于管理低级别数据访问的代码级别实现,它处理写入和读取数据,行锁定,多版本控制以及事务处理等。
从MySQL版本开始,存储引擎不断发展,由表的基础开始,定义一张表使用指定的存储引擎(ENGINE Table),创建后数据库管理系统开始处理,比如表之间的连接,从一张表中选择数据等。在MySQL和MariaDB中,创建完表后,仍然可以更改表的存储引擎。
高能Spider
Spider,一款名为蜘蛛的存储引擎,它提供的是从一台MariaDB服务器访问另一台MariaDB服务器的方法,保存实际表数据的MariaDB服务器可以没有任何Spider处理代码,一台普通的MySQL/MariaDB服务器即可。
可以在一台MariaDB上配置Spider,通过使用Spider存储引擎访问常规的MySQL API通信协议就可以正常访问另一台MariaDB上的数据。
上图中,我们看到,Spider仅在引用节点上安装激活,目标节点并不需要安装Spider。即创建一个Spider表,这意味着我们定义了一张表,这张表包括目标表中的相同一列或此列的子集以及引用的服务器。
安装Spider存储引擎
Spider已经包含在MariaDB服务器中,并提供了一个安装Spider的脚本,我们使用它来安装。
如果将MariaDB以RPM方式安装,Spider将安装在/usr/share/mysql中,脚本名字是install_spider.sql。我们使用MariaDB的命令行source执行即可:
$mysql-uroot
WelcometotheMariaDBmonitor.Commandsendwith;or\g.
YourMariaDBconnectionidis2835
Serverversion:10.4.6-MariaDB-logMariaDBServer
Copyright(c)2000,2018,Oracle,MariaDBCorporationAbandothers.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
MariaDB>source/usr/share/mysql/install_spider.sql
脚本运行完成,使用SHOW ENGINES命令来查看所有安装的存储引擎:
MariaDB>SHOWENGINES;
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
|Engine|Support|Comment|Transactions|XA|Savepoints|
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
|SPIDER|YES|Spiderstorageengine|YES|YES|NO|
|MRG_MyISAM|YES|CollectionofidenticalMyISAMtables|NO|NO|NO|
|MEMORY|YES|Hashbased,storedinmemory,usefulfortemporarytables|NO|NO|NO|
|Aria|YES|Crash-safetableswithMyISAMheritage.Usedforinternaltemporarytablesandprivilegetables|NO|NO|NO|
|MyISAM|YES|Non-transactionalenginewithgoodperformanceandsmalldatafootprint|NO|NO|NO|
|SEQUENCE|YES|Generatedtablesfilledwithsequentialvalues|YES|NO|YES|
|InnoDB|DEFAULT|Supportstransactions,row-levellocking,foreignkeysandencryptionfortables|YES|YES|YES|
|PERFORMANCE_SCHEMA|YES|PerformanceSchema|NO|NO|NO|
|CSV|YES|StorestablesasCSVfiles|NO|NO|NO|
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
9rowsinset(0.001sec)
可以看到Spider引擎已经就绪,已经可以开始使用。
Spider单表连接
来看一个基础的例子:在目标服务器上创建一张表。再次提示,目标服务器不需要Spider,仅访问远端数据的服务器是必需的。现在我们在目标服务器上创建一张新表,不妨称它为Sever2:
$mysql-uroot-S/tmp/mariadb2.sock-uroot
WelcometotheMariaDBmonitor.Commandsendwith;or\g.
YourMariaDBconnectionidis11
Serverversion:10.4.8-MariaDBMariaDBServer
Copyright(c)2000,2018,Oracle,MariaDBCorporationAbandothers.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
MariaDB[(none)]>CREATEDATABASEspidertest;
QueryOK,1rowaffected(0.001sec)
MariaDB[(none)]>usespidertest;
Databasechanged
MariaDB[spidertest]>CREATETABLEcustomer(
idINTNOTNULLPRIMARYKEYAUTO_INCREMENT,
nameVARCHAR(200)NOTNULL,
addressVARCHAR(255)NOTNULL);
QueryOK,0rowsaffected(0.539sec)
接下来向该表中插入一些测试数据:
MariaDB[spidertest]>INSERTINTOcustomerVALUES(NULL,'JohnDoe','1MainStreet');
QueryOK,1rowaffected(0.309sec)
MariaDB[spidertest]>INSERTINTOcustomerVALUES(NULL,'BobSmith','45ElmStreet');
QueryOK,1rowaffected(0.092sec)
MariaDB[spidertest]>INSERTINTOcustomerVALUES(NULL,'JaneJones',
'18SecondStreet');
QueryOK,1rowaffected(0.094sec)
我承认这个不是玛丽.居里夫人发明镭一样的惊喜实验,但可以看到数据也已经成功插入到刚才的新表中。现在要做的事是从MariaDB服务器的另一个实例来访问该表。当Spider以普通用户身份连接到远程服务器,需要在同一台MariaDB Server上创建一个用户帐户,并为它授予创建数据表的权限。
MariaDB[spidertest]>CREATEUSER'spider'@'192.168.0.11'IDENTIFIEDBY'spider';
QueryOK,0rowsaffected(0.236sec)
MariaDB[spidertest]>GRANTALLONspidertest.*TO'spider'@'192.168.0.11';
QueryOK,0rowsaffected(0.238sec)
MariaDB[spidertest]>GRANTALLONmysql.*TO'spider'@'192.168.0.11';
QueryOK,0rowsaffected(0.238sec)
下一步创建一个Server。如果之前你没有用过Spider,也不会用到这个命令,它是用来连接MariaDB Server实例以及相关参数,该服务器是在MariaDB Server实例上定义的。
下面是访问我们在上面创建的表(称为Server1)
$mysql-uroot-S/tmp/mariadb1.sock-uroot
WelcometotheMariaDBmonitor.Commandsendwith;or\g.
YourMariaDBconnectionidis12
Serverversion:10.4.8-MariaDBMariaDBServer
Copyright(c)2000,2018,Oracle,MariaDBCorporationAbandothers.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
MariaDB[none]>CREATESERVERServer2FOREIGNDATAWRAPPERmysql
OPTIONS(HOST'192.168.0.11',DATABASE'spidertest',PORT10482,
USER'spider',PASSWORD'spider');
QueryOK,0rowsaffected(0.233sec)
好的,下面就可以用Spider创建从Server1到Server2的链接了(我们也不必使用目标表中的所有字段)。
$mysql-uroot-S/tmp/mariadb1.sock-uroot
WelcometotheMariaDBmonitor.Commandsendwith;or\g.
YourMariaDBconnectionidis33
Serverversion:10.4.8-MariaDBMariaDBServer
Copyright(c)2000,2018,Oracle,MariaDBCorporationAbandothers.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
MariaDB[(none)]>DROPDATABASEIFEXISTSspidertest;
QueryOK,0rowsaffected,1warning(0.000sec)
MariaDB[(none)]>CREATEDATABASEspidertest;
QueryOK,1rowaffected(0.001sec)
MariaDB[(none)]>usespidertest;
Databasechanged
MariaDB[spidertest]>CREATETABLEcustomer(
idINTNOTNULLPRIMARYKEYAUTO_INCREMENT,
nameVARCHAR(200)NOTNULL)ENGINE=Spider
COMMENT='wrapper"mysql",srv"Server2"';
QueryOK,0rowsaffected(0.132sec)
OK,没有出现任何错误。接着我们来用SELECT获取数据。
MariaDB[spidertest]>SELECT*FROMcustomer;
+----+------------+
|id|name|
+----+------------+
|1|JohnDoe|
|2|BobSmith|
|3|JaneJones|
+----+------------+
3rowsinset(0.006sec)
我们看到了,命令运行也已经成功。居里夫人鼓励我继续下一步,我们正在一步步接近目标。
用于单表链接
即使是单表链接,也有不少用途,比如上述表的链接。在某些情况下,它只是用来复制单张表,可以用它来替换复制。
比如你有一张表,它在维护着一个数据库实例,比如客户数据信息,你希望从另一个MariaDB Server中的应用程序可以引用该库的CustomerID。
这种配置存在一些缺点。比如,性能是一个大问题,对于使用Spider表的每个语句,都要建立与服务器的连接,本地表与Spider表之间的联接也可能很慢。总体来说,Spider存储引擎性能很出色,本身并不瓶颈,还存在很大的调整空间。
第三个选择是当我们只有一张表时,比如日志表,我并不想与任何其它表混在一起用。那么,Spider表是实现此目标的一种选择。
使用Spider在多台服务器上的状态
比如你运行的MariaDB集群是一个主数据库和多个辅助数据组成,我们可以将此集群的所有服务器视为一个单元。接着派Spider出场,在当前场景下,有两个要监视的服务器,一个叫moe,另一个叫homer,moe是主服务器,我们还希望在此中看到两个服务器的全局状态。
在homer服务器上,我们基于infomation_schema.GLOBAL STATUS创建一个视图,添加一个用来保存服务器名字的字段再保存到数据库中。如下:
CREATEORREPLACEVIEWglobal_status_homer
ASSElECT'homer'host,gs.variable_name,gs.variable_value
FROMinformation_schema.global_statusgs;
我们在moe服务器上做相同的事情:
CREATEORREPLACEVIEWglobal_status_moe
ASSElECT'moe'host,gs.variable_name,gs.variable_value
FROMinformation_schema.global_statusgs;
接下来,创建主服务器moe到另一台服务器homer的链接。在moe上我们创建如下命令:
CREATEORREPLACESERVERhomerFOREIGNDATAWRAPPERmysql
OPTIONS(HOST'192.168.0.11',DATABASE'mysql',PORT10482,USER'spider',
PASSWORD'spider');
如此就在moe上创建了一个新视图,用来查看homer服务器的状态。
CREATEORREPLACETABLEglobal_status_homer(hostvarchar(2048),variable_nameVARCHAR(64),variable_valueVARCHAR(64))ENGINE=SpiderCOMMENT='wrapper"mysql",srv"homer"';
在homer服务器上,现有一张表和一张视图。每台服务器都有一个视图,具有相似的架构,它们反映着整个集群的状态。接下来把两张表结合成一个视图。
CREATEORREPLACEVIEWglobal_status_allAS
SELECThost,variable_name,variable_value
FROMglobal_status_homer
UNION
SELECThost,variable_name,variable_value
FROMglobal_status_moe;
然后,视图会显示整个集群的状态。
CREATEORREPLACEVIEWglobal_status_totalAS
SELECTvariable_name,SUM(variable_value)sum,MAX(variable_value)max,
MIN(variable_value)min
FROMglobal_status_all
GROUPBYvariable_name;
来测试下,它能不能正常工作:
MariaDB[mysql]>SELECT*FROMglobal_status_totalWHEREvariable_nameLIKE'open%';
+--------------------------+------+------+------+
|variable_name|sum|max|min|
+--------------------------+------+------+------+
|OPENED_FILES|629|477|152|
|OPENED_PLUGIN_LIBRARIES|1|1|0|
|OPENED_TABLES|112|75|37|
|OPENED_TABLE_DEFINITIONS|125|95|30|
|OPENED_VIEWS|85|43|42|
|OPEN_FILES|132|76|56|
|OPEN_STREAMS|0|0|0|
|OPEN_TABLES|77|46|31|
|OPEN_TABLE_DEFINITIONS|83|49|34|
+--------------------------+------+------+------+
9rowsinset(0.029sec)
嗯,尽管这是一个简单实例,但它却很有用。如果服务器多于两台时,它会更有价值。
合并多个服务器表
在上面的例子中,我们研究了如何使用Spider表将具有相似内容和相同结构的表合并到一个视图中,这在实际场合中有价值的应用,比如有一个多实例的应用程序,如某个由多个部门使用的ERP应用,如果要从报表服务器做跨部门报告,则可以使用此模型访问该应用程序的全部实例。
Spider的替代解决方案是使用多源复制,但它需要在报表服务器中存储更多冗余的数据,这便是Spider解决方案的优势。
Spider分表
分片(也可称为分表)是Spider最常见的用例了。在前面我将一台服务器的表映射到另外一台服务器的表,也可以将Spider用于服务器上的分区表,每个分区都在单独的服务器上,除此之外在实践上并没有什么区别。
尽管Spider可以在分区用例上做更多有趣的事情,对于这些特定用例Spider还有一些性能增强能力。
来举一个简单的示例,我们来展示使用两个分片来设置分区,为了展示其原理,我们将使用customer表。在此实例中总共有三台服务器,两台“数据服务器”,其中包含两个分片数据和一台服务器,而“Spider”服务器中没有任何正在使用表的实际数据,而指向的是驻留在其它两以服务器上的数据。
从最基础开始,先创建在MariaDB Server2和Server3中使用的表(与Customer表相似,但也不完全相同)。这两台服务器均以root用户身份运行。
CREATEDATABASEIFNOTEXISTSspidertest;
CREATETABLEspidertest.customer(
idINTNOTNULLPRIMARYKEY,
nameVARCHAR(200)NOTNULL,
addressVARCHAR(255)NOTNULL);
现在已经创建两个分表,接着来创建链接,从MariaDB Server1来访问它们,我们在Server1上执行类似于以下SQL语句,里面的端口、主机名以及帐号根据情况可以自己更换。
CREATEORREPLACESERVERServer2FOREIGNDATAWRAPPERmysql
OPTIONS(HOST'192.168.0.11',DATABASE'spidertest',PORT10482,
USER'spider',PASSWORD'spider');
CREATEORREPLACESERVERServer3FOREIGNDATAWRAPPERmysql
OPTIONS(HOST'192.168.0.11',DATABASE'spidertest',PORT10483,
USER'spider',PASSWORD'spider');
接下来,我们将它和一个分区表绑定在一起,注意你可以在此使用任何合理的分区方案,我们只是选择一个简单的方案来说明这一点。
CREATETABLEspidertest.customer(idINTNOTNULLPRIMARYKEY,
nameVARCHAR(200)NOTNULL,
addressVARCHAR(255)NOTNULL)ENGINE=Spider
COMMENT'wrapper"mysql",table"customer"'
PARTITIONBYRANGE(id)(
PARTITIONp0VALUESLESSTHAN(1000)COMMENT='srv"Server2"',
PARTITIONp1VALUESLESSTHAN(2000)COMMENT='srv"Server3
现在,我们在Server1上可以插入一些数据,并在Server2和Server3看到它们的显示。
INSERTINTOcustomerVALUES(1,'Larry','MainStreet1');
INSERTINTOcustomerVALUES(2,'Ed','MainStreet1');
INSERTINTOcustomerVALUES(3,'Bob','MainStreet1');
INSERTINTOcustomerVALUES(1001,'Monty','MainStreet1');
INSERTINTOcustomerVALUES(1002,'David','MainStreet1');
INSERTINTOcustomerVALUES(1003,'Allan','MainStreet1');
根据分区设置,1-3行将进入Server2,最后3行将进入Server3。我们会在本节完成之前提出来,分区分表是常见的MariaDB实景,没有Spider存储引擎,因此可以通过连接到Server2或Server3,逐个分表来访问。
用于Spid本文探讨使用MySQL/MariaDB的Spider存储引擎通过使用单个表链接到多台服务器的实例。
Spider是MariaDB提供的一个新存储引擎,可以让一个标准配置的MariaDB成为一个分布式数据。
虽然实现起来还是会有点复杂,但技术并不太难。本文就来给各位介绍Spider存储引擎的工作与技术原理,并会提供一些实用案例。
MariaDB存储引擎
存储引擎是用于管理低级别数据访问的代码级别实现,它处理写入和读取数据,行锁定,多版本控制以及事务处理等。
从MySQL版本开始,存储引擎不断发展,由表的基础开始,定义一张表使用指定的存储引擎(ENGINE Table),创建后数据库管理系统开始处理,比如表之间的连接,从一张表中选择数据等。在MySQL和MariaDB中,创建完表后,仍然可以更改表的存储引擎。
高能Spider
Spider,一款名为蜘蛛的存储引擎,它提供的是从一台MariaDB服务器访问另一台MariaDB服务器的方法,保存实际表数据的MariaDB服务器可以没有任何Spider处理代码,一台普通的MySQL/MariaDB服务器即可。
可以在一台MariaDB上配置Spider,通过使用Spider存储引擎访问常规的MySQL API通信协议就可以正常访问另一台MariaDB上的数据。
上图中,我们看到,Spider仅在引用节点上安装激活,目标节点并不需要安装Spider。即创建一个Spider表,这意味着我们定义了一张表,这张表包括目标表中的相同一列或此列的子集以及引用的服务器。
安装Spider存储引擎
Spider已经包含在MariaDB服务器中,并提供了一个安装Spider的脚本,我们使用它来安装。
如果将MariaDB以RPM方式安装,Spider将安装在/usr/share/mysql中,脚本名字是install_spider.sql。我们使用MariaDB的命令行source执行即可:
$mysql-uroot
WelcometotheMariaDBmonitor.Commandsendwith;or\g.
YourMariaDBconnectionidis2835
Serverversion:10.4.6-MariaDB-logMariaDBServer
Copyright(c)2000,2018,Oracle,MariaDBCorporationAbandothers.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
MariaDB>source/usr/share/mysql/install_spider.sql
脚本运行完成,使用SHOW ENGINES命令来查看所有安装的存储引擎:
MariaDB>SHOWENGINES;
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
|Engine|Support|Comment|Transactions|XA|Savepoints|
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
|SPIDER|YES|Spiderstorageengine|YES|YES|NO|
|MRG_MyISAM|YES|CollectionofidenticalMyISAMtables|NO|NO|NO|
|MEMORY|YES|Hashbased,storedinmemory,usefulfortemporarytables|NO|NO|NO|
|Aria|YES|Crash-safetableswithMyISAMheritage.Usedforinternaltemporarytablesandprivilegetables|NO|NO|NO|
|MyISAM|YES|Non-transactionalenginewithgoodperformanceandsmalldatafootprint|NO|NO|NO|
|SEQUENCE|YES|Generatedtablesfilledwithsequentialvalues|YES|NO|YES|
|InnoDB|DEFAULT|Supportstransactions,row-levellocking,foreignkeysandencryptionfortables|YES|YES|YES|
|PERFORMANCE_SCHEMA|YES|PerformanceSchema|NO|NO|NO|
|CSV|YES|StorestablesasCSVfiles|NO|NO|NO|
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
9rowsinset(0.001sec)
可以看到Spider引擎已经就绪,已经可以开始使用。
Spider单表连接
来看一个基础的例子:在目标服务器上创建一张表。再次提示,目标服务器不需要Spider,仅访问远端数据的服务器是必需的。现在我们在目标服务器上创建一张新表,不妨称它为Sever2:
$mysql-uroot-S/tmp/mariadb2.sock-uroot
WelcometotheMariaDBmonitor.Commandsendwith;or\g.
YourMariaDBconnectionidis11
Serverversion:10.4.8-MariaDBMariaDBServer
Copyright(c)2000,2018,Oracle,MariaDBCorporationAbandothers.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
MariaDB[(none)]>CREATEDATABASEspidertest;
QueryOK,1rowaffected(0.001sec)
MariaDB[(none)]>usespidertest;
Databasechanged
MariaDB[spidertest]>CREATETABLEcustomer(
idINTNOTNULLPRIMARYKEYAUTO_INCREMENT,
nameVARCHAR(200)NOTNULL,
addressVARCHAR(255)NOTNULL);
QueryOK,0rowsaffected(0.539sec)
接下来向该表中插入一些测试数据:
MariaDB[spidertest]>INSERTINTOcustomerVALUES(NULL,'JohnDoe','1MainStreet');
QueryOK,1rowaffected(0.309sec)
MariaDB[spidertest]>INSERTINTOcustomerVALUES(NULL,'BobSmith','45ElmStreet');
QueryOK,1rowaffected(0.092sec)
MariaDB[spidertest]>INSERTINTOcustomerVALUES(NULL,'JaneJones',
'18SecondStreet');
QueryOK,1rowaffected(0.094sec)
我承认这个不是玛丽.居里夫人发明镭一样的惊喜实验,但可以看到数据也已经成功插入到刚才的新表中。现在要做的事是从MariaDB服务器的另一个实例来访问该表。当Spider以普通用户身份连接到远程服务器,需要在同一台MariaDB Server上创建一个用户帐户,并为它授予创建数据表的权限。
MariaDB[spidertest]>CREATEUSER'spider'@'192.168.0.11'IDENTIFIEDBY'spider';
QueryOK,0rowsaffected(0.236sec)
MariaDB[spidertest]>GRANTALLONspidertest.*TO'spider'@'192.168.0.11';
QueryOK,0rowsaffected(0.238sec)
MariaDB[spidertest]>GRANTALLONmysql.*TO'spider'@'192.168.0.11';
QueryOK,0rowsaffected(0.238sec)
下一步创建一个Server。如果之前你没有用过Spider,也不会用到这个命令,它是用来连接MariaDB Server实例以及相关参数,该服务器是在MariaDB Server实例上定义的。
下面是访问我们在上面创建的表(称为Server1)
$mysql-uroot-S/tmp/mariadb1.sock-uroot
WelcometotheMariaDBmonitor.Commandsendwith;or\g.
YourMariaDBconnectionidis12
Serverversion:10.4.8-MariaDBMariaDBServer
Copyright(c)2000,2018,Oracle,MariaDBCorporationAbandothers.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
MariaDB[none]>CREATESERVERServer2FOREIGNDATAWRAPPERmysql
OPTIONS(HOST'192.168.0.11',DATABASE'spidertest',PORT10482,
USER'spider',PASSWORD'spider');
QueryOK,0rowsaffected(0.233sec)
好的,下面就可以用Spider创建从Server1到Server2的链接了(我们也不必使用目标表中的所有字段)。
$mysql-uroot-S/tmp/mariadb1.sock-uroot
WelcometotheMariaDBmonitor.Commandsendwith;or\g.
YourMariaDBconnectionidis33
Serverversion:10.4.8-MariaDBMariaDBServer
Copyright(c)2000,2018,Oracle,MariaDBCorporationAbandothers.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
MariaDB[(none)]>DROPDATABASEIFEXISTSspidertest;
QueryOK,0rowsaffected,1warning(0.000sec)
MariaDB[(none)]>CREATEDATABASEspidertest;
QueryOK,1rowaffected(0.001sec)
MariaDB[(none)]>usespidertest;
Databasechanged
MariaDB[spidertest]>CREATETABLEcustomer(
idINTNOTNULLPRIMARYKEYAUTO_INCREMENT,
nameVARCHAR(200)NOTNULL)ENGINE=Spider
COMMENT='wrapper"mysql",srv"Server2"';
QueryOK,0rowsaffected(0.132sec)
OK,没有出现任何错误。接着我们来用SELECT获取数据。
MariaDB[spidertest]>SELECT*FROMcustomer;
+----+------------+
|id|name|
+----+------------+
|1|JohnDoe|
|2|BobSmith|
|3|JaneJones|
+----+------------+
3rowsinset(0.006sec)
我们看到了,命令运行也已经成功。居里夫人鼓励我继续下一步,我们正在一步步接近目标。
用于单表链接
即使是单表链接,也有不少用途,比如上述表的链接。在某些情况下,它只是用来复制单张表,可以用它来替换复制。
比如你有一张表,它在维护着一个数据库实例,比如客户数据信息,你希望从另一个MariaDB Server中的应用程序可以引用该库的CustomerID。
这种配置存在一些缺点。比如,性能是一个大问题,对于使用Spider表的每个语句,都要建立与服务器的连接,本地表与Spider表之间的联接也可能很慢。总体来说,Spider存储引擎性能很出色,本身并不瓶颈,还存在很大的调整空间。
第三个选择是当我们只有一张表时,比如日志表,我并不想与任何其它表混在一起用。那么,Spider表是实现此目标的一种选择。
使用Spider在多台服务器上的状态
比如你运行的MariaDB集群是一个主数据库和多个辅助数据组成,我们可以将此集群的所有服务器视为一个单元。接着派Spider出场,在当前场景下,有两个要监视的服务器,一个叫moe,另一个叫homer,moe是主服务器,我们还希望在此中看到两个服务器的全局状态。
在homer服务器上,我们基于infomation_schema.GLOBAL STATUS创建一个视图,添加一个用来保存服务器名字的字段再保存到数据库中。如下:
CREATEORREPLACEVIEWglobal_status_homer
ASSElECT'homer'host,gs.variable_name,gs.variable_value
FROMinformation_schema.global_statusgs;
我们在moe服务器上做相同的事情:
CREATEORREPLACEVIEWglobal_status_moe
ASSElECT'moe'host,gs.variable_name,gs.variable_value
FROMinformation_schema.global_statusgs;
接下来,创建主服务器moe到另一台服务器homer的链接。在moe上我们创建如下命令:
CREATEORREPLACESERVERhomerFOREIGNDATAWRAPPERmysql
OPTIONS(HOST'192.168.0.11',DATABASE'mysql',PORT10482,USER'spider',
PASSWORD'spider');
如此就在moe上创建了一个新视图,用来查看homer服务器的状态。
CREATEORREPLACETABLEglobal_status_homer(hostvarchar(2048),variable_nameVARCHAR(64),variable_valueVARCHAR(64))ENGINE=SpiderCOMMENT='wrapper"mysql",srv"homer"';
在homer服务器上,现有一张表和一张视图。每台服务器都有一个视图,具有相似的架构,它们反映着整个集群的状态。接下来把两张表结合成一个视图。
CREATEORREPLACEVIEWglobal_status_allAS
SELECThost,variable_name,variable_value
FROMglobal_status_homer
UNION
SELECThost,variable_name,variable_value
FROMglobal_status_moe;
然后,视图会显示整个集群的状态。
CREATEORREPLACEVIEWglobal_status_totalAS
SELECTvariable_name,SUM(variable_value)sum,MAX(variable_value)max,
MIN(variable_value)min
FROMglobal_status_all
GROUPBYvariable_name;
来测试下,它能不能正常工作:
MariaDB[mysql]>SELECT*FROMglobal_status_totalWHEREvariable_nameLIKE'open%';
+--------------------------+------+------+------+
|variable_name|sum|max|min|
+--------------------------+------+------+------+
|OPENED_FILES|629|477|152|
|OPENED_PLUGIN_LIBRARIES|1|1|0|
|OPENED_TABLES|112|75|37|
|OPENED_TABLE_DEFINITIONS|125|95|30|
|OPENED_VIEWS|85|43|42|
|OPEN_FILES|132|76|56|
|OPEN_STREAMS|0|0|0|
|OPEN_TABLES|77|46|31|
|OPEN_TABLE_DEFINITIONS|83|49|34|
+--------------------------+------+------+------+
9rowsinset(0.029sec)
嗯,尽管这是一个简单实例,但它却很有用。如果服务器多于两台时,它会更有价值。
合并多个服务器表
在上面的例子中,我们研究了如何使用Spider表将具有相似内容和相同结构的表合并到一个视图中,这在实际场合中有价值的应用,比如有一个多实例的应用程序,如某个由多个部门使用的ERP应用,如果要从报表服务器做跨部门报告,则可以使用此模型访问该应用程序的全部实例。
Spider的替代解决方案是使用多源复制,但它需要在报表服务器中存储更多冗余的数据,这便是Spider解决方案的优势。
Spider分表
分片(也可称为分表)是Spider最常见的用例了。在前面我将一台服务器的表映射到另外一台服务器的表,也可以将Spider用于服务器上的分区表,每个分区都在单独的服务器上,除此之外在实践上并没有什么区别。
尽管Spider可以在分区用例上做更多有趣的事情,对于这些特定用例Spider还有一些性能增强能力。
来举一个简单的示例,我们来展示使用两个分片来设置分区,为了展示其原理,我们将使用customer表。在此实例中总共有三台服务器,两台“数据服务器”,其中包含两个分片数据和一台服务器,而“Spider”服务器中没有任何正在使用表的实际数据,而指向的是驻留在其它两以服务器上的数据。
从最基础开始,先创建在MariaDB Server2和Server3中使用的表(与Customer表相似,但也不完全相同)。这两台服务器均以root用户身份运行。
CREATEDATABASEIFNOTEXISTSspidertest;
CREATETABLEspidertest.customer(
idINTNOTNULLPRIMARYKEY,
nameVARCHAR(200)NOTNULL,
addressVARCHAR(255)NOTNULL);
现在已经创建两个分表,接着来创建链接,从MariaDB Server1来访问它们,我们在Server1上执行类似于以下SQL语句,里面的端口、主机名以及帐号根据情况可以自己更换。
CREATEORREPLACESERVERServer2FOREIGNDATAWRAPPERmysql
OPTIONS(HOST'192.168.0.11',DATABASE'spidertest',PORT10482,
USER'spider',PASSWORD'spider');
CREATEORREPLACESERVERServer3FOREIGNDATAWRAPPERmysql
OPTIONS(HOST'192.168.0.11',DATABASE'spidertest',PORT10483,
USER'spider',PASSWORD'spider');
接下来,我们将它和一个分区表绑定在一起,注意你可以在此使用任何合理的分区方案,我们只是选择一个简单的方案来说明这一点。
CREATETABLEspidertest.customer(idINTNOTNULLPRIMARYKEY,
nameVARCHAR(200)NOTNULL,
addressVARCHAR(255)NOTNULL)ENGINE=Spider
COMMENT'wrapper"mysql",table"customer"'
PARTITIONBYRANGE(id)(
PARTITIONp0VALUESLESSTHAN(1000)COMMENT='srv"Server2"',
PARTITIONp1VALUESLESSTHAN(2000)COMMENT='srv"Server3
现在,我们在Server1上可以插入一些数据,并在Server2和Server3看到它们的显示。
INSERTINTOcustomerVALUES(1,'Larry','MainStreet1');
INSERTINTOcustomerVALUES(2,'Ed','MainStreet1');
INSERTINTOcustomerVALUES(3,'Bob','MainStreet1');
INSERTINTOcustomerVALUES(1001,'Monty','MainStreet1');
INSERTINTOcustomerVALUES(1002,'David','MainStreet1');
INSERTINTOcustomerVALUES(1003,'Allan','MainStreet1');
根据分区设置,1-3行将进入Server2,最后3行将进入Server3。我们会在本节完成之前提出来,分区分表是常见的MariaDB实景,没有Spider存储引擎,因此可以通过连接到Server2或Server3,逐个分表来访问。
用于Spider的分片
分片的明显优势是在处理大型数据集合时可以有效提高性能。除了以上功能外,Spider存储引擎还有其它优点,比如每个分片都可以单独访问普通的MariaDB Server,即可以按分片查看数据集,而不存在任何瓶颈,亦可以将其视为整体,同时使用Spider。er的分片
分片的明显优势是在处理大型数据集合时可以有效提高性能。除了以上功能外,Spider存储引擎还有其它优点,比如每个分片都可以单独访问普通的MariaDB Server,即可以按分片查看数据集,而不存在任何瓶颈,亦可以将其视为整体,同时使用Spider。