网易MySQL数据库工程师微专业学习笔记（十四）

一、mysql5.7新增的功能

mysql5.7相对于mysql5.5和5.6来说还是添加了很多实用的新功能的，下面简单列举几个实用的新功能。

1. 添加了json字段

为了应对非结构化数据库的冲击mysql从5.7版本开始支持json类型的字段了。不仅仅是能够存储json类型的数据，mysql也提供了一系列的json处理函数。因为是在mysql曾实现的对json的支持因此所有的存储引擎都一般是支持json的，如innodb和myisam。另外因为json是表中的字段类型，因此可以设计使表中的部分字段是普通字段，部分为json字段，这就意味着可以进行结构化数据和非结构化数据的混合存储。最后虽然json实在mysql层实现的与存储引起无关，但是并不影响存储引擎原有的事务功能，即如果在innodb存储引擎中json字段的加入并不会影响原来的事务功能。

举例来说，首先通过如下命令创建一张表。

create table tj10 (a JSON, b INT);

然后在向tj10表中插入一些数据，命令如下。

insert into tj10 values ("[3,10,5,17,44]",33);
insert into tj10 values ("[3,10,5,17,[22,44,66]]",0);
insert into tj10 values ("[5,10,5,17,44]",22);

JSON结构数据列最大的特点就是可以对这列的数据进行键值对查询或者索引位置查询，如下面的两个命令。

select a->"$[4]" from tj10;
select * from tj10 where a->"$[0]"=3;

这两个命令的功能分别是查询json类型的a列的第5列的数据内容（因为是从0开始计数的）和第1列值为3的数据。具体结果如下图所示。

2. 安全性的功能改进

mysql5.7相对于之前版本的mysql在安全性上做了不少的改进。首先mysql再初始化数据库时不再是给root账号设置空密码了，而是设置一个随机密码（应该只有在二进制文件安装时才是这样的），并且msql5.7后初始化数据库时不会再安装test数据库了。此外再5.7版本中还提供了设置密码的过期策略和锁定、解锁用户的功能。

设置密码的过期策略的命令如下。

alter user gz@'localhost' password expire interval 30 day;

锁定和解锁用户的命令如下。

alter user gz@'localhost' account lock;
alter user gz@'localhost' account unlock;

3. 虚拟列

虚拟列就是创建一个在数据存储时不依赖于用户输入，而是根据其他列的数据计算出来的列。例如有一个订单表，里面有两个字段，一个是单价price，另一个是购买数量count，这样就可以创建一个总价的虚拟列total，它的值不依赖于用户输入，而是通过price*count来算出来的。表的创建命令如下。

create table order_lines(
    orderid bigint,
    userid bigint,
    price decimal(10,2),
    count int,
    total decimal(10,2) generated always as (price*count) stored);

在虚拟列声明的最后有一个关键字stored，这个关键字表示这个虚拟列的数据会被实际存储在磁盘上。如果不想让虚拟列的数据存到磁盘上可以将关键字改为virtual。stored和virtual的区别除了数据是否会实际存在磁盘上外还在于stored的虚拟列可以在列上建立主键而 virtual不行，stored的虚拟列可以建立b-tree索引、全文索引和r-tree索引，而virtual只能建立b-tree索引。
二、mysql5.7的性能优化

1. 只读事务优化

只读事务就是只在事务中没有进行数据增删改操作的事务。一般mysql中的只读事务有通过start transaction read only打开的事务，以及在autocommit模式下没有加锁的select操作都是只读事务。在mysql5.7中所有的事务只要没有在事务内修改数据则mysql都认为是只读事务，并且mysql不会为只读事务分配事务id和回滚段，以及降低内存开销、减少锁竞争等多种方式来优化了只读事务的开销。

2. 临时表优化

临时表就是只在当前这个连接中可见的表，因此这种表对于一致性没有什么要求。如果断开连接或者重启mysql服务则临时表就消失了。针对于这些特性mysql5.7对于临时表进行了以下几点优化：第一，对于临时表中的元数据不再进行持久化，因为这些数据只是对于这个连接可见，因此没有必要持久化；第二，对于临时表的DML操作mysql不会存储redo log并且会关闭change buffer。通过上述两点的改进就可以省略掉一些不必要的IO，从而提升mysql临时表的性能。

3. 并行复杂性能优化

mysql从5.6开始就支持主从复制的并行复制了。但是5.6中的并行复制只能将不同数据库中的操作在从库上进行并行负责。如果只对一个数据库进行操作，那么主从间的复制实际上还是单线程的复制。而mysql5.7参考MariaDB的并行复制进行了修改，5.7中将主库上并行提交的事务打包成一个组，那么在主库上并行提交的一个组内的事务那么在从库上当然也是可以并行的执行回放的。这样就实现了主从数据库间的并行复制。mysql中可以通过修改全局变量slave_parallel_type来设置并行复制的方式。这个参数默认值是database，就是还是和5.6一样是基于不同的数据库来进行并行的主从复制的，如果需要改为基于事务组来并行复制的，可以将参数的值改为LOGICAL_CLOCK。

4. 读写性能的提升

对于数据库来说最主要的性能提升还是数据读写的性能的提升，下图就是mysql官方进行的性能测试的结果图。

图中左面表示的是随着连接数的增加mysql的读写性能的对比，可以看出mysql5.7相对于5.6和5.5在多个连接的情况下读写性能也没有急剧的下降。而右边图中2表示的是在只读情况下数据读取性能与cpu内核数的关系，可以看出只有mysql5.7随着cpu内核的增加读性能始终有着较大的提升。

三、mysql5.7运维中的改进
1. innodb buffer pool的动态修改
再mysql5.7中innodb buffer pool的大小可以动态设置了，可以通过set global variables innodb_buffer_pool_size这个命令来设置。但是要注意innodb_buffer_pool_size的大小并不是任意的，innodb_buffer_pool_size有个一计算公示如下图所示。

公示中innodb_buffer_pool_chunk_size是innodb buffer pool的chunk的大小，innodb buffer pool中最小就是按照chunk来使用的。而innodb_buffer_pool_instances指的是innodb buffer pool的实例个数，mysql5.7中innodb buffer pool是可以有多个实例的。也就是说innodb_buffer_pool_size的大小必须是innodb_buffer_pool_instances乘上innodb_buffer_pool_chunk_size大小的N倍。当然如果设置的时候没有设置为两者乘积的N倍mysql会自动调整设置的大小，使其符合公式要求。此外还要主要虽然innodb_buffer_pool_size可以动态设置了，但是在修改innodb_buffer_pool_size大小的过程中可能会导致用户的新请求都被阻塞。如果要查看innodb_buffer_pool_size的设置是否完成可以通过以下命令或者error log来查看。

show status where variable_name='InnoDB_buffer_pool_resize_status';

2. sys schema

sys库是mysql5.7新引入的一个数据库，里面有一系列的视图、存储过程和函数，可以帮助DBA更加方便的查看到当前数据库的运行状态，包括系统运行状态、用户会话信息与资源占用、数据对象信息、sql执行情况、索引覆盖、锁信息等。下面列举几个常用的查询。

查看进行了全表扫描的sql语句

select * from statements_with_full_table_scans;

查看用户会话信息与资源占用

select * from host_summary;

查看Innodb中锁等待情况

select * from innodb_lock_waits;

3. 在线修改表结构

一个数据库对在线修改表结构操作的支持的好坏主要看三个方面：第一，修改是否需要拷贝原来表中的数据；第二，修改的过程中是否运行对表在执行DML操作了；第三，修改的过程中是否运行进行select操作。mysql5.7中对于修改索引名称、添加索引、删除索引、重命名字段的操作都已经做了比较好的在线修改的支持。但是对于添加字段、删除字段、修改字段类型还没有很好的支持，虽然官方上说这几个操作是支持的，但是实际还是会要拷贝表中数据，并且如update和delete这样的DML操作在修改完成前效率会大大降低。