Posts tagged ‘mariadb’

MariaDB 新特性介绍-动态虚拟列

MariaDB为NoSQL的扩展提供的另外一个特性 就是:动态列。对表的每一行都可以有一个“虚拟列”,该列可以用于保存一个对象的不同属性,每个属性在各行之间可以完全不同,“虚拟列”的个数和内容完全有应用程序来控制和解释。

NoSQL的另外一个好处就是,我存储的东西是一个没有结构的东西,而数据库则要求是一个固定列名,确定长度的一个个列。为了满足这个需求,MariaDB引入了动态列的概念。它允许你在每一行上有一个“虚拟列”。这个虚拟列被作为一个额外的字段存在在blob中,并且有一系列函数用于创建,更新,删除,检查,查询这个列。 空说无凭,举例为证。我们先创建一个表t1,用来存储shirt,phone,computer商品的价格,其中dynstr就是用于做虚拟列的:

并插入一些数据

这里利用了COLUMN_CREATE()函数来创建动态列。插入的四条数据。针对各个不同的商品定义不同的属性。比如1在各个商品中表示的是颜色;3表示的是操作系统;10表示的是大小。每行数据的属性不完全相同,也不要求所有的属性值都需要包含在动态列中。那么,动态列的有哪些属性要怎么查看列:

COLUMN_LIST()可以列出动态列到底有哪些属性,这样的话我们就可以根据属性过滤




上面的第一条SQL查询了颜色为black的所有产品哪些属性,第二条SQL查询了存在颜色属性的所有行。对应的COLUMN_GET() 函数表示获取对应属性的属性值。COLUMN_EXISTS()函数表示动态列中是否存储了该属性值。我们也注意到,现在动态列的所有属性都是用数字来表示的。

前面都是对动态列创建和查询的例子,我们再看看更新的例子。比如我们需要对类型为‘computer’的数据加4G内存: 

COLUMN_ADD()函数会给每个’computer’的行加上新的属性,属性值为‘4G ram’。不过,COLUMN_ADD()并不仅仅是添加,它和MySQL的replace一样,如果发现相同类型的属性,对应的属性值将被覆盖。

动态列现在正在快速发展期,还存在一定的限制。比如:

  • 列必须通过数字来标识;
  • 动态列对象最大只有536870911字节;
  • 创建索引问题;

具体信息参考:http://kb.askmonty.org/en/dynamic-columns/

 

MariaDB 新特性介绍-handlesocket

NoSQL的发展如火如荼,如果MySQL也能提供NoSQL的功能和性能,那你是否介意让MySQL在NoSQL领域来个第二春。接下来我们来看看MariaDB在NoSQL方面的扩展。首先是HandleSocket,它能够绕过SQL解析等步骤支持你直接从InnoDB/XtraDB中获取数据。

现在NoSQL的盛行已经超过了我们的想象,各种NoSQL的产品和软件工具不断涌现,层出不穷。不过,MariaDB也不甘示弱,它引入了HandleSocket作为plugin,使得你可以越过server层,直接操作innoDB或者XtraDB。这样的话,你就可以把MariaDB当成一个NoSQL来使用了。目前支持的客户端包括C++,Perl,PHP,Java,Python,Ruby,JavaScript,Scala,Haskell8种。

从MariaDB5.3.0开始,HandleSocket就随源码和二进制分发包一起发布。但是你需要以root用户通过INSTALL PLUGIN安装这个插件。

安装成功以后,SHOW PROCESSLIST就可以看到HandleSocket的各个工作线程了。对应的客户端应用程序需要连接HandleSocket进行操作的话,需要安装对应的handlesocke client,对应的库参考:http://kb.askmonty.org/en/handlersocket-client-libraries/

Handlesock是作为一个精灵进程跑在mysqld进程中,它能够接受TCP链接,并且执行客户端的请求,但是它并不支持SQL。 由于它不分析SQL,并且会尽量优化进行批量操作,所以它的CPU消耗非常小;另外,协议相对mysql/libmysql会紧凑得多,所以网络上的开销也会小的多。网上有很多对HandleSocket的测试,它比一般的NoSQL产品都会快得多。Handlesocket也存在一些限制:

  • 它对IO Bound类型的应用没有太多性能提升。
  • 如果你需要对表进行操作,由于HandleSocket持有锁,那么对应的HandleSocket的业务访问都必须暂停下来
  • HandleSocket在并发情况下和自增配合有问题,使用HandleSocket的情况下,建议取消自增。

HandleSocket具体信息参考:http://kb.askmonty.org/en/handlersocket/

MariaDB 新特性介绍-进度报告

当你需要对MySQL做一个长时间的操作时,比如alter table,能做的就是等待,再等待,如果MySQL告诉你:它正在做”copy to tmp table”,并且这一步骤已经做完了5.37%,你会不会爱它多一点。没错,这就是MariaDB提供的新特性:进度报告。参考http://t.cn/zlPLwtI

很对DBA相信都做过大表的alter table,我们把命令敲进去以后,MySQL就自顾自的做事情了,如果要一个小时,那么我们看到的MySQL界面展示界面就一直是我们刚刚敲进去命令的样子。到底它需要执行多少时间,现在做什么事情,做这个事情做到了什么程度,我们一无所知。于是,MariaDB为我们引入了进度报告的概念。我们先一睹为快:

如上,我们想要修改my_mail表为maria引擎,执行了alter table的命令。MariaDB告诉我它分两步来做,现在正在做第一步:”copy to tmp table”,而这一步它已经完成了5.37%。是不是很人性化?

在MariaDB,我们可以看到一共有多少步stage,目前完成了哪些步stage,目前在这个stage上完成百分之几。进度一目了然,非常友好。对应的在show processlist命令和INFORMATION_SCHEMA.PROCESSLIST表上都进行了配套的修改。这样的话,用户就可以判断目前的进度和整个操作需要进行多少时间。 目前MariaDB对以下几种命令会进行进度报告:

  • ALTER TABLE
  • ADD INDEX
  • DROP INDEX
  • LOAD DATA INFILE (对应的另外一个命令LOAD DATA LOCAL INFILE由于不知道文件大小,所以无效).

而MariaDB默认存储引擎Aria支持其他的几个命令:

  • CHECK TABLE
  • REPAIR TABLE
  • ANALYZE TABLE
  • OPTIMIZE TABLE

另外,进度报告功能是默认支持的,如果需要取消,需要在客户端指定–disable-progress-reports。 具体信息请参考:http://kb.askmonty.org/en/progress-reporting/

MariaDB 新特性介绍-limit 行检查限制

MySQL的分页语法比oracle简单,直接limit就可以了,但是limit 1000000,10将扫描1000010的记录并丢弃一百万的数据,用户翻页获得下10条记录可能需要等待好几分钟。并且数据库的IO资源在此期间受到很大的冲击。MariaDB提供了一种简单的解决办法,在检查的数据行数大于指定值之后就报错退出,节省数据库IO资源。

我们先看看它的语法:

SELECT FROM WHERE [group_clause] [order_clause] LIMIT [[offset,] row_count] ROWS EXAMINED rows_limit;

limit算是MySQL比oracle语法友好的一个非常方便的功能了。但是,对于分页查询来说,如果你limit的offset非常大,那么你的数据库就悲剧了。比如limit 1000000,10。那么MySQL就需要先把1000010条记录找出来,然后丢弃掉前100000条记录。查询这么多记录数据库是一个很大的负担,并且查出来的100000条记录其实也都不是你想要的数据。这也是很多访问量极大的网站取消了你直接跳到某页功能的原因所在。大部分的DBA对这个一筹莫展,部分DBA会说服产品设计师采用记录上一页最大ID,然后where id>? limit 10的办法解决。 我们曾经遇到过在订单库上,很多limit 500000,10的并发使得数据库响应时间变成原来的10倍的情况。导致了非常严重的故障。

MariaDB提供了一个变通的办法,她让你指定一个检查行数限制。如果超过这个检查行数,那么就不要浪费数据库资源了,直接中断查询退出。当然它并不是对所有的Query都有效,它的限制是:它对Delete和Update无效;如果子查询查询的行操作,而你的限制是执行在父查询上,那么它只对父查询有效;如果是组合查询(Union,子查询等),在多个地方有ROWS EXAMINED检查行限制,那么只有最后一个限制有效。 当然,这个只是一个尽量减少对数据库资源浪费的办法,如果没有必要,还是需要尽量从应用层面说服产品负责人避免这种访问。比如:

这个查询限制了检查行数不能超过一万。而下面这个查询

内部的行数限制被覆盖掉了,就只有外围的11行检查限制生效了。具体信息参考:http://kb.askmonty.org/en/limit-rows-examined/

MariaDB 新特性介绍-授权插件

登录操作系统我们需要输入一下密码,登录MySQL我们又要输入数据库密码,何其繁琐。MariaDB的授权插件允许你登录了操作系统以后以同样的用户免密码直接登录MySQL,避免了多次密码输入和设置各种密码的麻烦。MariaDB另外还提供了“提问”和“三次试错”的搜权插件。

国内买MySQL Enterprise版的可能不多,用到了Enterprise的授权插件的可能就更少了。提供了授权登录的插件,同样MariaDB也提供了这样的插件。 比如,用户serg已经登录了系统,那么该用户不用再次输入用户名密码,就可以直接登录MariaDB:

这个是MariaDB socket_peercred授权插件的功能。另外,MariaDB还提供了“提问”和“三次试错”的搜权插件。前者在获得密码以后要求你再次确认“Are you sure?”,后者允许用户可以尝试三次以输入正确的密码。这三种授权插件都没有默认安装,需要用户手工安装上去。具体信息请参考:http://kb.askmonty.org/en/pluggable-authentication/

MariaDB 新特性介绍-MyISAM Keycache 优化

接下来我们进入MariaDB数据库管理新特性。首先是MyISAM Keycache分段。InnoDB在MySQL 5.5的时候就拆分了Buffer pool的锁,但是使用MyISAM的DBA还是不得不竞争keycache唯一的一个锁。MariaDB进行了分段,每个段一个锁,使得MyISAM的性能提升了250%。

对MyISAM来说,常规的keycache只有一个,对应的mutex也只有一个,大家都必须竞争这一个资源,势必引起资源争用的问题。 MariaDB为MyISAM引入了Segmented MyISAM keycache的概念,它把keycache隔离成一个一个的Segment,每个segment独立一个锁。谁需要keycache的某个页,只需要获得这个页所在的segment的锁,而没有必要去竞争整个keycache的锁。MariaDB引入了这个特性以后,使得MyISAM的性能提升了250%。 如果你使用MyISAM作为默认的存储引擎的话,这个特性对你来说相信有足够的吸引力。

通过设置key_cache_segments,对应的分段才能真正生效。该参数取值为[0-64],超过64会自动截断为64。如果该参数设置为0,MyISAM keycache保持原生的不分段不变,否则分段。其他用于设置keycache的参数对分段keycache依然生效。对应了,在INFORMATION_SCHEMA中也添加了一个KEY_CACHES表,用于获得keycache的统计信息,对应的字段和释义如下:

Column Name Description
KEY_CACHE_NAME The name of the key cache
SEGMENTS total number of segments (set to NULL for regular key caches)
SEGMENT_NUMBER segment number (set to NULL for any regular key caches and for rows containing aggregation statistics for segmented key caches)
FULL_SIZE memory for cache buffers/auxiliary structures
BLOCK_SIZE size of the blocks
USED_BLOCKS number of currently used blocks
UNUSED_BLOCKS number of currently unused blocks
DIRTY_BLOCKS number of currently dirty blocks
READ_REQUESTS number of read requests
READS number of actual reads from files into buffers
WRITE_REQUESTS number of write requests
WRITES number of actual writes from buffers into files

 

具体信息参考:https://kb.askmonty.org/en/segmented-key-cache/

 

MariaDB 新特性介绍-GIS 地理信息系统扩展

有没有考虑过把二维空间的点,线段,多边形存在数据库里面,然后用SQL来查询线段是否有交叉,多边形之间是否交汇,让数据库解决地理信息系统的问题。MySQL本身也能存储和处理GIS数据,MariaDB对此做了扩展。

MariaDB提供了地理信息的创建,存储和分析,对于基本的二维地理信息,都可以支持。而且这些都可以用在几大通用的存储引擎上: MyISAM, InnoDB/XtraDB 和 ARCHIVE。 空间类型包括以下几种:
Geometry Types
POINT
LINESTRING
POLYGON
MULTIPOINT
MULTILINESTRING
MULTIPOLYGON
GEOMETRYCOLLECTION
GEOMETRY
比如需要创建一个仅包含点的表,并存入二维点(10,10),(20,10),(20,20),(10,20),可以采用如下SQL:

简单介绍两个函数:Contains(g1,g2)函数可以用来判断空间类型g1是否完全“包含”g2,Overlaps(g1,g2)函数用来判断g1和g2两个空间类型是否相互“交叉”。具体信息参考:http://kb.askmonty.org/en/gis-functionality

 

MariaDB 新特性介绍-subquery 优化

在MySQL里面做子查询你会发现很多情况下都不能用到索引。MariaDB相对于最新版的MySQL的好处就是:她对子查询做了大量的优化。这样的话,你再也不用把subquery写成Join,然后写一堆注释说明这个SQL到底想干嘛。

众所周知,MySQL里的子查询是不建议使用的。因为MySQL对子查询的执行计划完全不靠谱。一般情况下,我们采用Join的办法把子查询重写。但是这样的话对应SQL的意思就非常晦涩,难以理解。并且,现在很多公司都在进行oracle往MySQL迁移的事情,重写子查询,相信是很多人头痛而不得不做的事情。而MariaDB优化了子查询的执行计划,对大部分子查询都可以在生产环境下使用了。 这里有一张图来表明MariaDB做了哪些子查询优化,以及是在哪些版本完成的。

如图:

  • 没有颜色的部分代表不同种类的子查询。比如:x IN (SELECT …)是一种类型的子查询,FROM (SELECT …)是另一种类型的子查询。
  • 没有颜色的框框大小大体代表着该种类型子查询的重要程度,也就是使用的频度。比如:x IN (SELECT …)就是最重要的子查询。EXISTS (SELECT …)相对没有那么重要。
  • 有颜色的区块对应的展示了对该种子查询做了哪些优化和执行计划的调整。
  • 不同颜色的区块表示,对应的优化是在MariaDB的哪个版本进行的。

子查询优化默认都是开启的,如果需要关系,可以设置对应参数。比如你需要关闭semi-join子查询优化,可以通过SET optimizer_switch=’semijoin=off’完成。

具体某种子查询做了哪些优化,可以参考:http://kb.askmonty.org/en/subquery-optimizations/

 

MariaDB 新特性介绍-sphinxse

Sphinx全文搜索引擎是目前当前市场上最炙手可热的开源搜索引擎,MariaDB利用SphinxSE作为存储引擎,直接访问Sphinx,前端的用户不用熟悉两套API,并且Sphinx返回值可以直接跟MySQL表 join,利用MySQL过滤不需要的行等。

在MariaDB中,SphinxSE被编译成so动态链接文件,这样的话,在MySQL中你就可以通过Install Plugin操作将这个全文搜索引擎插入进来。
      装载操作只需要进行一次。装载成功以后,show engines可以查看到SphinxSE的版本信息等情况。

        虽然ShpinxSE的名字里面包含有“引擎”,但是它本身并不存储数据,它本质上就是一个Sphinx的client。MariaDB通过它向搜索引擎发送查询和获得查询结果。而应用程序透过MariaDB来向Sphinx获得数据:或者将MariaDB中的表和sphinx结果进行Join,或者利用MariaDB进行过滤统计等操作。想要通过SphinxSE来搜索,你需要创建一个 ENGINE=SPHINX的特殊表,然后通过SELECT的WHERE中传入全文检索条件来获取数据。示例如下:
Sphinx的具体操作比较复杂,就不在这里详细说明了。 具体信息参考:http://kb.askmonty.org/en/about-sphinxse/

MariaDB 新特性介绍-microsecond

后面我们关注MariaDB对MySQL功能的增加。首先是微秒支持。在MariaDB中,时间类型的值可以指定精度,最大为6,也就是说可以精确到微秒级别。这样的话,用户在使用高精度的时间值比如:23:59:59.998877就不用单独把微秒存到另外单独的字段了。

MariaDB全面支持微秒。你可以在TIME, DATETIME和TIMESTAMP 后面加上表示精度的数字。例如:

精度的数字可以从0到6,如果不指定,则认为为0,也就是最小到秒级别,以便跟MySQL兼容。对应的,配套的日期时间函数,information_schema,存储过程等都同样进行了修改。比如INFORMATION_SCHEMA.COLUMNS就增加DATETIME_PRECISION字段来表示时间的精度。NOW(), CURTIME(), UTC_TIMESTAMP(), UTC_TIME(), CURRENT_TIME(), CURRENT_TIMESTAMP(), LOCALTIME()和 LOCALTIMESTAMP() 都增加了一个可选参数用于表示精度:

具体信息参考:http://kb.askmonty.org/en/microseconds-in-mariadb/