Posts tagged ‘新特性’

5.7新特性

本文仅翻译了5.7.4 新增和修改的属性,有兴趣的同学可以深入里面感兴趣的东西细看。

增强特性
  1. 安全:
    • 不支持老的password算法
    • 支持密码过期
    • 新装的机器只有root@localhost,匿名账户和test schema都不默认安装了。root密码第一次登录需要修改密码。
  2. sql mode默认值修改: NO_ENGINE_SUBSTITUTION to NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES.

  3. alter table 的rename index从copy table变成inplace

  4. Innodb增强:

    • varchar()字段变长部分支持inplace的online ddl。length bytes 变化和varchar()长度缩短都必须使用copy的方式来做ddl。length bytes:0-255 长度是1个字节;256和256以上长度是2字节。length bytes不变的话,支持inplace修改。length bytes变化的话,必须用copy算法
      ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);
      ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change
      column type INPLACE. Try ALGORITHM=COPY.
    • CREATE TABLE, DROP TABLE, TRUNCATE TABLE, and ALTER TABLE 的DDL 临时表性能增强。啥都没说
    • innodb的临时表元信息不再存在innodb的系统表空间,而是存储再INNODB_TEMP_TABLE_INFO。从这个表可以查到当前活跃的临时表,不管是用户的还是系统帮忙创建的。
    • innodb支持mysql的空间数据。之前用BLOB现在用DATA_GEOMETRY支持。
    • 对非压缩的Innodb临时表有了专门的独立表空间。这个表空间默认在系统启动时自动在DATADIR上创建起来。另外,可以通过innodb_temp_data_file_path来指定文件路径。
    • innodchecksum增强。
    • innodb buffer pool dump和load增强了。通过innodb_buffer_pool_dump_pct参数控制buffer pool里面最近使用的page中有多少百分比读出并dump去。当InnoDB后台任务在做IO时,InnoDB通过 innodb_io_capacity来限制加载的速度。
    • InnoDB增加了对全文索引插件的支持。
    • Innodb对buffer pool instance提供多页清理线程。innodb_page_cleaners可以指定线程数。跟purge不同:
      purge: A type of garbage collection performed by a separate thread, running on a periodic schedule. The purge includes these actions: removing obsolete values from indexes; physically removing rows that were marked for deletion by previous DELETE statements. 清理index里面的过时数据,对delete删除的数据进行物理清除。cleaner是做脏页刷新的。
    • 支持InnoDB的普通和分区表 online ddl(ALGORITHM=INPLACE)OPTIMIZE TABLE ALTER TABLE … FORCE ALTER TABLE … ENGINE=INNODB (when run on an InnoDB table)。这样的话,online ddl能够缩减吃哦年间时间,允许并并发DML,用户的停机时间就减少了。
    • Fusion-io Non-Volatile Memory (NVM)文件系统支持 atomic write ,这样innodb doublewrite buffer比较多余了,对于把ibdata表空间文件存放在支持原子写的Fusion io上的时候,Innodb的doublewrite buffer会自动禁用掉
    • innodb 支持对分区表和单个的innodb表分区 Transportable Tablespace特性。这样,分区表备份,分区表在MySQL各个instance中迁移就方便了很多。
    • innodb_buffer_pool_size 可以在线修改,不用重启服务器了。通过 innodb_buffer_pool_chunk_size 来控制内存中的多少个页挪动。通过Innodb_buffer_pool_resize_status 监控buffer pool迁移状态。
      Active transactions, and operations performed through InnoDB APIs, should be completed before resizing the buffer pool. When initiating a resizing operation, the operation does not start until all active transactions are completed. Once the resizing operation is in progress, new transactions and operations that require access to the buffer pool must wait until the resizing operation finishes. The exception to this rule is that concurrent access to the buffer pool is permitted when defragmenting the buffer pool and withdrawing pages during an operation to decrease buffer pool size. A drawback of allowing concurrent buffer pool access while withdrawing pages is that there could be a temporary shortage of available pages during this phase of the resizing operation.
      活动事务/通过Innodb api进行的动作需要在buffer pool变更大小前做完。也就是说,resize buffer pool会一直等到active transaction完成之后才开始。这个动作开始后,新的事务和操作只要需要用到buffer pool都必须等到resizing完成。例外:当进行buffer pool碎片整理和释放page以便减少buffer pool时,可以访问buffer pool。允许在释放page时同时访问buffer pool的一个缺点是在这个resizing操作时有一个临时缺页的问题。
  5. 增强了MySQL存储过程。MySQL现在有了stacked diagnostics areas。对于执行过的SQL可以获得它返回的各种信息:
    mysql> DROP TABLE test.no_such_table;
    ERROR 1051 (42S02): Unknown table ‘test.no_such_table’
    mysql> GET DIAGNOSTICS CONDITION 1
    -> @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
    mysql> SELECT @p1, @p2;
    +——-+————————————+
    | @p1 | @p2 |
    +——-+————————————+
    | 42S02 | Unknown table ‘test.no_such_table’ |
    +——-+————————————+

  6. explain可以对正在运行的连接显示执行情况。如果你需要了解那些运行时间很长的会话SQL执行为什么这么慢,就可以用这种方法。

  7. trigger。之前在同一个表上的(insert,update,delete)的(before,after)只能加一个trigger。现在可以加多个了。

  8. mysql 客户端支持–syslog,这样mysql客户端提交的语句就会被记录到系统日志中。可以用来做审核和安全审计。

  9. Test suite. 现在使用innodb作为默认存储引擎

  10. mysql client中ctrl+c会中断真正执行的SQL,或者退出MySQL。但是新版本中只会中断SQL或者中断输入,不会退出MySQL了。

  11. mysqlbinlog现在也支持 –rewrite-db 在输出的时候把数据库名修改掉。mysql自身的复制是本来就支持这个特性的。现在给mysqlbinlog工具也增加了这个特性。

  12. handler 在5.7中支持分区表了。handler提供对表存储引擎的直接访问。比如HANDLER … OPEN 可以打开一张表,HANDLER tbl_name READ index_name =可以直接从存储引擎读一行数据。好处在于比select块,没有Server层的解析,快速。缺点在于没有数据快照,读到的数据可能是刚刚被其他session修改过的。

  13. 分区表 现在支持Index condition pushdown。

  14. alter table exchange artition现在可以忽略一行一行的验证数据。(without validation)

  15. 给slave发送binlog信息的master dump thread减少了锁竞争,增强了吞吐量。之前,master dump thread只要读一个event就要给binlog加一个锁,5.7.2之后,它只在读上次成功写入event的位置的时候加锁。这样多个线程同时读binlog文件也快了,就算binlog 文件正被写入,也没有问题。

  16. 扩展了GB18030字符集

  17. change master to部分情况下可以不用stop slave了。

    • SQL thread停掉时,change master可以修改relay_log_file,relay_log_pos,master_delay.
    • io thread 停掉时,你可以修改除relay_log_file,relay_log_pos,master_delay.以外的所有参数值。
    • io,SQL thread都停掉了,你可以修改master_auto_position=1
去掉的特性
  • insert delay 不支持了
  • show engine innodb mutex去掉了
  • innodb tablespace monitor/innodb table monitor也去掉了
  • innodb_monitor、innodb_lock_monitor去掉了.修改为两个系统变量
  • innodb_status_output、innnodb_status_output_locks.
    innodb_user_sys_malloc、innodb_additional_mem_pool_size去掉了。

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/