MySQL · 特性分析 · MySQL 5.7新特性系列一

本文涉及的产品
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS SQL Server,基础系列 2核4GB
简介: 1. 背景 MySQL 5.7在2015-10-21发布了GA版本,即5.7.9,目前小版本已经到了5.7.12。5.7新增了许多新的feature和优化,接下来一个系列,我们就一起来尝尝鲜。首先这次主要是预览feature的变化以及兼容性问题。后面的系列,会针对重要的feature展开来学习。

1. 背景

MySQL 5.7在2015-10-21发布了GA版本,即5.7.9,目前小版本已经到了5.7.12。5.7新增了许多新的feature和优化,接下来一个系列,我们就一起来尝尝鲜。首先这次主要是预览feature的变化以及兼容性问题。后面的系列,会针对重要的feature展开来学习。

2 安全相关的特性

2.1 认证插件

mysql.user表中的plugin更改成not null,5.7开始不再支持mysql_old_password的认证插件,推荐全部使用mysql_native_password。从低版本升级到5.7的时候,需要处理两个兼容性问题。

[兼容性]
需要先迁移mysql_old_password的用户,然后进行user表结构的升级:

1. 迁移mysql_old_password用户
MySQL 5.7.2之前的版本,是根据password的hash value来判断使用的认证插件类型,5.7.2以后的版本,plugin字段为not null,就直接根据plugin来判断了。新的密码从password字段中,保存到新的字段authentication_string中,password字段废弃处理。

如果user是隐式的mysql_native_password。直接使用sql进行变更:

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE plugin = '' AND (Password = '' OR LENGTH(Password) = 41);
FLUSH PRIVILEGES;

如果user是隐式的或者显示的mysql_old_password, 首先通过以下sql进行查询:

SELECT User, Host, Password FROM mysql.user WHERE (plugin = '' AND LENGTH(Password) = 16) OR plugin = 'mysql_old_password';

如果存在记录,就表示还有使用mysql_old_password的user,使用以下sql进行用户的迁移:

ALTER USER 'user1'@'localhost' IDENTIFIED WITH mysql_native_password BY 'DBA-chosen-password';

2. user表结构升级
通过mysql_upgrade直接进行升级,步骤如下[5.6->5.7]:

  1. stop MySQL 5.6实例
  2. 替换5.7的mysqld二进制版本
  3. 使用5.7启动实例
  4. run mysql_upgrade升级系统表
  5. 重启MySQL 5.7实例

2.2 密码过期

用户可以通过 ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;这样的语句来使用户的密码过期。
并新增加 default_password_lifetime来表示用户密码自动过期时间,从5.7.10开始,其默认值从0变更到了360,也就是默认一年过期。
可以通过以下两种方法禁止过期:

1. SET GLOBAL default_password_lifetime = 0;
2. ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;

[兼容性]
只需要通过mysql_upgrade升级mysql.user系统表就可以使用密码过期新功能。

2.3 账号锁定

用户可以通过以下语法进行账号锁定,阻止这个用户进行登录:

ALTER USER 'jeffrey'@'localhost' ACCOUNT LOCK;
ALTER USER 'jeffrey'@'localhost' ACCOUNT UNLOCK;

[兼容性]
只需要通过mysql_upgrade升级mysql.user系统表就可以使用密码过期新功能。

2.4 SSL连接

如果mysqld编译使用的openssl,在启动的时候,默认创建SSL, RSA certificate 和 key 文件。
但不管是openssl还是yassl,如果没有设置ssl相关的参数,mysqld都会在data directory里查找ssl认证文件,来尽量打开ssl特性。

[兼容性]
不存在兼容性的问题

2.5 安装数据库

5.7开始建议用户使用 mysqld --initialize来初始化数据库,放弃之前的mysql_install_db的方式,新的方式只创建了一个root@localhost的用户,随机密码保存在~/.mysql_secret文件中,并且账号是expired,第一次使用必须reset password,并且不再创建test db。

[兼容性]
不存在兼容性的问题

3 sql mode变更

5.7 sql_mode的默认值变更为:

mode_no_engine_substitution |
      mode_only_full_group_by |
      mode_strict_trans_tables |
      mode_no_zero_in_date |
      mode_no_zero_date |
      mode_error_for_division_by_zero |
      mode_no_auto_create_user

而在5.7之前,sql_mode的默认值都只有mode_no_engine_substitution。
所以在5.7默认的情况下,比如grant不存在的用户的时候,会报一下错误:

ERROR 1133 (42000): Can't find any matching row in the user table

必须先使用create user,然后再使用grant user。

[兼容性]
默认sql mode发生变更会导致sql的行为不一致。

4. online alter table

支持online rename index操作, in_place并且不需要table copy。
[兼容性]
不存在兼容性的问题

5. InnoDB增强

5.1 varchar长度变更支持inplace

变更varchar 类型字段的长度支持inplace方法,但有一个限制,即用于表示varchar字段长度的字节数不能发生变化,也就是支持比如varchar的长度在255以下变更或者255以上的范围进行变更,因为从小于255变更到大于255,其size的字节需要从1个增加到2个。

注意:减少varchar的长度,仍然需要table copy。

5.2 优化InnoDB临时表

因为InnoDB临时表的数据不再不受redo保护,而redo只保护临时表的元数据,所以大幅提升了临时表的性能。
并且InnoDB临时表的元数据保存在一个新的系统表中即innodb_temp_table_info,
临时表将建立一个统一的表空间,我们称之为临时表空间,其目录地址可以通过参数innodb_temp_data_file_path来设置。系统在启动的时候,都会新建这个表空间,重启会删除重建。

例如:

mysql> show global variables like '%temp_data_file_path%';
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+

并且5.7存储引擎默认都变更成InnoDB了:

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 |
+----------------------------------+--------+

注意: 在开启gtid的情况下,非auto commit或者显示begin的context下,create 或者drop 临时表,仍然和5.6一样:

ERROR 1787 (HY000): Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context.

另外, insert into t select * from t也会遇到错误,不能在一个sql语句中reference两次临时表。

备注: 因为InnoDB临时表进行了比较大的变动,我们会专门进行一次详细的介绍。

5.3 InnoDB原生支持DATA_GEOMETRY类型

并且支持在spatial data types上建立index,加速查询。

5.4 buffer pool dump

buffer pool dump和load支持一个新的参数innodb_buffer_pool_dump_pct,即dump的比例,并且使用innodb_io_capacity 来控制load过程中的IO吞吐量。

5.5 多线程flush dirty

从5.7.4开始,innodb_page_cleaners参数可以设置,支持多线程flush dirty page,加快脏块的刷新。

5.6 NVM file system

MySQL 一直使用double write buffer来解决一个page写入的partial write问题,但在linux系统上的Fusion-io Non-Volatile Memory (NVM) file system支持原子的写入。
这样就可以省略掉double write buffer的使用, 5.7.4以后,如果Fusion-io devices支持atomic write,那么MySQL自动把dirty block直接写入到数据文件了。这样减少了一次内存copy和IO操作。

5.7 InnoDB分区表

MySQL 5.7之前的版本,InnoDB并不支持分区表,分区表的支持是在ha_partition引擎上支持的,从5.7开始,InnoDB支持原生的分区表,并且可以使用传输表空间。

[兼容性]
mysql_upgrade会扫描ha_partition引擎支持的InnoDB表,并升级成InnoDB分区表,5.7.9之后,可以通过命令ALTER TABLE … UPGRADE PARTITIONING.进行升级。如果之前的版本大量使用了分区表,要注意使用mysql_upgrade会消耗非常长的时间来升级分区表。

5.8 动态调整buffer pool size

MySQL 5.7.5之后,可以online动态调整buffer pool size,通过设置动态的参数innodb_buffer_pool_size来调整,并且根据Innodb_buffer_pool_resize_status状态来查看resize的进度,因为resize的过程是以chunk为大小,把pages从一个内存区域copy到另一片内存的。

5.9 加快recovery

MySQL 5.7.5之前,在recovery的过程中,需要扫描所有的ibd文件,获取元信息, 5.7.5之后,新加了一种redo log类型,即MLOG_FILE_NAME, 记录从上一次checkpoint以来,发生过变更的文件,这样在recovery的过程中,只需要打开这些文件就可以了。
[兼容性]
因为增加了新的log record type,需要安全的关闭5.7之前的实例,清理掉redo。

5.10 表空间管理

支持创建表空间,例如

CREATE TABLESPACE `tablespace_name`
ADD DATAFILE 'file_name.ibd'
[FILE_BLOCK_SIZE = n]

并可以在创建表的时候,指定属于哪个表空间,

[兼容性]
因为可以任意指定空间目录,要注意升级过程中,不要漏掉目录。

5.11 InnoDB Tablespace Encryption

支持InnoDB数据文件加密,其依赖keyring plugin来进行秘钥的管理,后面我们单独来介绍InnoDB加密的方法,并且RDS也实现了一种InnoDB数据文件透明加密方法,并通过KMS系统来管理秘钥。例如:

create table t(id int) encryption='y';

未完待续

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
6月前
|
存储 JSON 关系型数据库
《MySQL 简易速速上手小册》第9章:高级 MySQL 特性和技巧(2024 最新版)
《MySQL 简易速速上手小册》第9章:高级 MySQL 特性和技巧(2024 最新版)
74 1
|
6月前
|
SQL 关系型数据库 MySQL
MySQL8.0索引新特性
MySQL8.0索引新特性
51 0
|
2月前
|
JSON 关系型数据库 MySQL
MySQL 8.0 新特性
MySQL 8.0 新特性
124 10
MySQL 8.0 新特性
|
2月前
|
存储 Oracle 关系型数据库
Oracle和MySQL有哪些区别?从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
从基本特性、技术选型、字段类型、事务提交方式、SQL语句、分页方法等方面对比Oracle和MySQL的区别。
329 18
Oracle和MySQL有哪些区别?从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
|
23天前
|
SQL 安全 关系型数据库
MySQL8.2有哪些新特性?
【10月更文挑战第3天】MySQL8.2有哪些新特性?
29 2
|
3月前
|
算法 关系型数据库 MySQL
一天五道Java面试题----第七天(mysql索引结构,各自的优劣--------->事务的基本特性和隔离级别)
这篇文章是关于MySQL的面试题总结,包括索引结构的优劣、索引设计原则、MySQL锁的类型、执行计划的解读以及事务的基本特性和隔离级别。
|
3月前
|
SQL 算法 关系型数据库
(二十)MySQL特性篇:2022年的我们,必须要懂的那些数据库新技术!
 MySQL数据库从1995年诞生至今,已经过去了二十多个年头了,到2022.04.26日为止,MySQL8.0.29正式发行了GA版本,在此之前版本也发生了多次迭代,发行了大大小小N多个版本,其中每个版本中都有各自的新特性,所有版本的特性加起来,用一本书的篇幅也无法完全阐述清楚,因此本章主要会挑重点特性来讲,具体各版本的特性可参考MySQL官网的开发手册。
130 1
|
5月前
|
关系型数据库 MySQL 测试技术
深入探索MySQL 8:隐藏索引与降序索引的新特性
深入探索MySQL 8:隐藏索引与降序索引的新特性
|
5月前
|
关系型数据库 MySQL 数据库
MySQL 8.0 新特性之不可见主键
【6月更文挑战第9天】MySQL 8.0 引入了不可见主键特性,提供更灵活的数据库管理方式。不可见主键能减少业务逻辑干扰,提高数据安全性和隐私,同时在某些场景下更适用。示例展示了如何创建和使用不可见主键,但需要注意它可能带来的理解和调试难题。此特性增加了设计和管理数据库的选项,适用于对数据隐私有高要求的场景。随着技术发展,不断学习和探索新特性将提升数据库性能和功能。
75 9
|
5月前
|
SQL JSON 关系型数据库
MySQL 8.0新特性?
【6月更文挑战第14天】MySQL 8.0新特性?
47 1

相关产品

  • 云数据库 RDS MySQL 版