MySQL 5.7 新特性之三

简介

本系列文章基于5.7.20 版本讲述MySQL的新特性,从安装,文件结构,SQL,优化,复制等几个方面展开介绍5.7 的新特性和功能,同时也建议大家跟踪官方blog和文档 ,以尽快知悉其新的变化。

本文着重介绍5.7版本的innodb 相关的新特性。看了文档,只能使用目不暇接来形容5.7带来的新变化,废话不多说,进入正题--Innodb功能增强

3.1 动态修改varchar 长度大小。

在5.7版本中可以通过ALTER TABLE语句以in place方式修改varchar的大小且无需table-copy。但存在限制:表示varchar长度的字节数不能变化(如果变更前使用1个字节表示长度,变更后也必须使用1个字节表示),即只支持0~255内的或者255以上的范围变更(增大),如果字段的长度从254增到256时就不能使用in-place算法,必须使用copy算法也即会阻塞DML,否侧报错,需要注意的是减小varchar(N)长度的大小必须使用copy类型,如:

mysql> alter table yy change column name name varchar(256) ,algorithm=inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
mysql> alter table yy change column name name varchar(255) ,algorithm=inplace;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table yy change column name name varchar(256) ,algorithm=copy;
Query OK, 1 row affected (0.10 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> alter table yy change column name name varchar(100) ,algorithm=inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

3.2 临时表性能优化

MySQL 5.7对临时表做了极大的改动以便提升性能。通过优化 CREATE TABLE, DROP TABLE, TRUNCATE TABLE,和ALTER TABLE 语句的执行逻辑,提升临时表的性能。(这个是从官网翻译的,还没找到除了alter之外的其他资料说明详细的优化过程) InnoDB临时表元数据不再存储于InnoDB系统表而是存储在INNODB_TEMP_TABLE_INFO,包含所有用户和系统创建的临时表信息。该表在第一次在其上运行select时被创建。

mysql> desc information_schema.INNODB_TEMP_TABLE_INFO;
+----------------------+---------------------+------+-----+---------+-------+
| Field                | Type                | Null | Key | Default | Extra |
+----------------------+---------------------+------+-----+---------+-------+
| TABLE_ID             | bigint(21) unsigned | NO   |     | 0       |       |
| NAME                 | varchar(202)        | YES  |     | NULL    |       |
| N_COLS               | int(11) unsigned    | NO   |     | 0       |       |
| SPACE                | int(11) unsigned    | NO   |     | 0       |       |
| PER_TABLE_TABLESPACE | varchar(64)         | YES  |     | NULL    |       |
| IS_COMPRESSED        | varchar(64)         | YES  |     | NULL    |       |
+----------------------+---------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

3.3 新增临时表空间

为所有非压缩的innodb临时表提供一个独立的表空间,我们可通过innodb_temp_data_file_path参数指定临时表空间的路径和大小。语法为:

file_name:file_size[:autoextend[:max:max_file_size]]

例子:

innodb_temp_data_file_path=ibtmp1:50M;ibtmp2:12M:autoextend:max:500MB

如果不指定该参数,系统会在data_dir指定的目录创建一个名为ibtmp1大于12M的文件,而且是自适应增长的。

mysql> show global variables like 'innodb_temp_data_file_path';
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.00 sec)

MySQL每次重新启动时,会重新创建临时表空间。如果启动是不能创建临时空间则MySQL启动失败。

注意: 从5.7.5开始,新增一个系统选项 internal_tmp_disk_storage_engine 可定义磁盘临时表的引擎类型为 InnoDB,而在<= 5.6版本只能使用 MyISAM。而在5.6.3以后新增的参数default_tmp_storage_engine是控制create temporary table创建的临时表的存储引擎,在以前默认是MEMORY,不要把这二者混淆了。

mysql> show global variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+

3.4 引入新的"non-redo" undo log

从MySQL 5.7.2开始 针对临时表及相关对象引入新的"non-redo" undo log,存放于临时表空间。该类型的undo log非 redolog, 因为临时表在数据库崩溃后不需要恢复,也就无需redo logs,避免了写relog相关的io,从而提高了性能。必须指出操作临时表需要 undo log用于MySQL运行时的回滚、MVCC等。详见《innodb-temporary-table-undo-logs》

3.5 支持新的DATA_GEOMETRY空间类型的数据

InnoDB现在支持MySQL-supported空间数据类型。也即,之前的空间数据是以binary BLOB数据存储的,现在空间数据类型被映射到了一个InnoDB内部数据类型DATA_GEOMETRY.

3.6 升级innochecksum

innochecksum--离线的InnoDB文件校验工具,新增新的选择项或扩展的功能,如可指定特定的校验算法、可以只重写校验值而不进行验证、可指定允许的校验和不匹配量、显示各类页的个数、导出页类型信息、输出至日志、从标准输入读取数据等。从 5.7.2 起可支持校验超过2G的文件。

innochecksum --page-type-summary ../data/test/tab1.ibd

File::../data/test/tab1.ibd
================PAGE TYPE SUMMARY==============
#PAGE_COUNT PAGE_TYPE
===============================================
       2        Index page
       0        Undo log page
       1        Inode page
       0        Insert buffer free list page
       2        Freshly allocated page
       1        Insert buffer bitmap
       0        System page
       0        Transaction system page
       1        File Space Header
       0        Extent descriptor page
       0        BLOB page
       0        Compressed BLOB page
       0        Other type of page
===============================================
Additional information:
Undo page type: 0 insert, 0 update, 0 other
Undo page state: 0 active, 0 cached, 0 to_free, 0 to_purge, 0 prepared, 0 other

详细的用法参考innochecksum 官方文档

3.7 online DDL语句重建普通表和分区表

OPTIMIZE TABLE、ALTER TABLE … FORCE、ALTER TABLE … ENGINE=INNODB等操作支持支持使用inplace算法。减少了重建时间和对应用的影响。

3.8 针对Fusion-io NVM 文件系统的优化

Linux系统中Fusion-io Non-Volatile Memory (NVM)文件系统提供了原子写能力,使InnoDB的doublewrite变得冗余。因此,MySQL5.7.4以后,如果Fusion-io设备支持原子写, MySQL系统会自动关闭doublewrite,减少IO,提升性能。

3.9 支持多个线程刷dirty pages

InnoDB 支持通过参数innodb_page_cleaners配置多个线程从 buffer pool instances 刷新脏页。该参数默认值为1,不过5.7.8 之后,innodb_purge_threads 和 innodb_page_cleaners的默认值从1修改为4,而且如果innodb_page_cleaners的个数超过innodb_buffer_pool_instances的大小会被自动设置为buffer pool instances 大小一致。

小结

其实关于innodb的新特性和新功能一篇远远不够,下一篇继续介绍关于 5.7 innodb相关的知识。

原文发表时间:2018-03-24

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏吴伟祥

百万级数据库优化方案 转

1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

1032
来自专栏唐郑望的专栏

Django数据处理的一些实践

提到 Django 肯定避不开 MVC 模式,即模型(Model)-视图(View)-控制器(Controller),通过将业务逻辑、数据、界面显示分离的方法组...

2961
来自专栏维C果糖

史上最简单的 MySQL 教程(二十三)「数据的高级操作 之 查询(上)」

字段别名,即当数据进行查询的时候,有时候字段的名字并不一定满足需求(特别地,在多表查询的时候,很可能会有同名字段),这时就需要对字段进行重命名、取别名。

3606
来自专栏杨建荣的学习笔记

关于date格式的两个案例(r4笔记第96天)

在工作中总是会碰到各种和date相关的问题,一般这种问题都是让人很纠结的。 比如前几天一个朋友和我分享了他关于时间问题的两个案例。 第一个是他在做impdp导入...

3134
来自专栏社区的朋友们

MySQL 入门常用命令大全(上)

作为一个 MySQL 的初学者,在短短的几个月中接触了一下,记录了一下工作中用到的 SQL 语句以及未来可能会用到的 MySQL 知识点,作为日后的参考手册。因...

9221
来自专栏Hongten

SQL SERVER事务处理

事务三种运行模式: 自动提交事务 每条单独的语句都是一个事务。 显式事务 每个事务均以 BEGIN TRANSACTION 语句显式开始, 以 COMMIT 或...

2472
来自专栏数据存储

十分钟包会MySQL插件开发

请看下面的MySQL UDF插件模版,通过C执行shell语句。所以只要替换shell语句的位置,保存并编译。duang的一下,你的MySQL插件就成功出炉。

3648
来自专栏乐沙弥的世界

MySQL 状态变量(Server Status Variables)

    MySQL状态变量是当前服务器自启动后累计的一些系统状态信息,主要用于评估当前系统资源的使用情况以进一步分析系统性能而做出相应的调整决策。这些状态变量我...

1031
来自专栏芋道源码1024

数据库中间件 MyCAT源码分析:【单库单表】插入

本文主要基于 MyCAT 1.6.5 正式版 1. 概述 2. 接收请求,解析 SQL 3. 获得路由结果 4. 获得 MySQL 连接,执行 SQL 5. 响...

51712
来自专栏H2Cloud

游戏服务器ID生成器组件

游戏服务器程序中,经常需要生成全局的唯一ID号,这个功能很常用,本文将介绍一种通用ID生成组件。游戏服务器程序中使用此组件的场景有:  创建角色时,为其分配唯一...

5198

扫码关注云+社区

领取腾讯云代金券