MySQL 5.7 学习：功能性能的提升

最新推荐文章于 2024-04-23 09:54:37 发布

l_215851356

最新推荐文章于 2024-04-23 09:54:37 发布

阅读量987

点赞数

分类专栏： mysql 文章标签： mysql

mysql 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

背景：

继上次介绍初识 MySQL 5.6 新功能、参数完之后，刚好MySQL 5.7又GA了，在官方测试里看到，MySQL5.7在功能、性能、可用性、安全和监控上又提升了很高。现在看看和MySQL5.6对比，之前介绍了新增配置参数和安全相关特性。本文来说明MySQL5.7关于功能和性能提升的方面（持续更新）。

1，功能性能上的提升

1.1：复制功能的提升。

①支持并行复制。slave-parallel-type

5.6开始支持基于库（database）的并行复制，对于只有一个库的，效果不好。5.7开始支持基于组提交（LOGICAL_CLOCK）的并行复制，提高复制的可用性。

②支持多源复制，通过channel支持一个从库复制多个主库。

③支持在线修改REPLICATION FILTER：REPLICATE_DO_DB、REPLICATE_IGNORE_DB。通过change replicate filter，需要停止SQL thread，修改完成以后，启动SQL thread。可以参考这篇文章。

由于篇幅的原因，后面会另起一篇文章介绍上面功能的细节。

1.2：mysqlpump，并行版 mysqldump，也是替换原生 mysqldump 和 mydumper 的。--watch-progress 查看dump进度，--compress-ouptut 压缩，也支持 SSL。大致的优势如下：（后面会起一篇文章来说明mysqlpump的使用）

支持基于表的并行导出功能(参数–default-parallelism，默认为2，参数–parallel-schemas，控制并行导出的库)
导出的时候带有进度条（参数–watch-progress，默认开启）
支持直接压缩导出导入（参数–compress-output，支持ZLIB和LZ4）

1.3：online alter table。在初识 MySQL 5.6 新功能、参数里介绍的online ddl的基础上又增加了：

①：在线加主键：当主键列为null字段的时候，5.6建主键需要复制表，5.7可以inplace：

复制代码

5.6：
>create table test(id int);
Query OK, 0 rows affected (0.00 sec)
>insert into test values(1),(2),(3);                                                                                                        
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
>alter table test add primary key(id);                                                                                                        Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0      ###copy

5.7：
>create table test(id int);                                                                                                                 Query OK, 0 rows affected (0.01 sec)
>insert into test values(1),(2),(3); 
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
>alter table test add primary key(id);
Query OK, 0 rows affected (0.03 sec)        ###inplace
Records: 0  Duplicates: 0  Warnings: 0

复制代码

②：varchar长度变更(加大)支持inplace，需要注意的是有一个限制，即用于表示varchar字段长度的字节数不能发生变化，也就是支持比如varchar的长度在255以下变更或者255以上的范围进行变更，因为从小于255变更到大于255，其size的字节需要从1个增加到2个，另一个注意的是不允许inplace字段长度变小：

复制代码

>show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

>alter table test modify name varchar(64);   ##inplace modify
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

>alter table test modify name varchar(128);  ##inplace modify
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

>alter table test modify name varchar(255);  ##inplace modify
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

>alter table test modify name varchar(256);  ##copy modify，超过了255
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

>alter table test modify name varchar(64);   ##copy modify，长度变小
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

复制代码

③：支持online rename index操作：

复制代码

>show create table test\G                                                                                                                   *************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

>alter table test rename index name to idx_name;

复制代码

...

1.4：Undo Log日志的在线回收。innodb undo log占用共享表空间并且无法回收，在5.6的时候可以把undo log分离到独立的表空间，并放到单独的文件目录下，但是其文件大小也不会回收。5.7之后可以在线收缩undo log：需要开启innodb_undo_log_truncate、innodb_undo_tablespaces和innodb_undo_directory参数，当大小超过innodb_max_undo_log_size的大小则会被undo会被标记为可truncate。具体的原理可以看这篇文章。undo log 在整个事务未提交前，undo page是必须强占内存，这会让buffer pool size 收到污染，可以看这个例子说明。

1.5：新增json类型。关于json类型操作的函数参考官方文档和MySQL5.7 JSON类型使用介绍，原理介绍见MySQL5.7的JSON 实现。

MySQL对支持JSON的做法是在server层提供了一堆便于操作JSON的函数，简单地将JSON编码成BLOB，然后交由存储引擎层进行处理。MySQL 5.7的JSON支持与存储引擎没有关系，MyISAM 存储引擎也支持JSON 格式。MySQL对JSON的支持，至少有两点能够完胜MongoDB：可以混合存储结构化数据和非结构化数据，同时拥有关系型数据库和非关系型数据库的优点；能够提供完整的事务支持。

1.6：generate column。一列由其他列计算而得，可生成索引的虚拟化列。

复制代码

mysql> create table t(id int,score int,score_ss int as (score*33));
Query OK, 0 rows affected (0.25 sec)

mysql> select * from t;
Empty set (0.00 sec)

mysql> select * from t;
Empty set (0.00 sec)

mysql> insert into t(id,score) values(1,10);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t;
+------+-------+----------+
| id   | score | score_ss |
+------+-------+----------+
|    1 |    10 |      330 |
+------+-------+----------+

建表标准的语法：
CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `score_ss` int(11) GENERATED ALWAYS AS ((`score` * 33)) VIRTUAL/STORED,
  KEY `idx_score_ss` (`score_ss`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

复制代码

MySQL5.7支持两种generated column，即virtual generated column和stored generated column，前者只将generated column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将generated column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与virtual column相比并没有优势。因此，在不指定generated column的类型时，默认是virtual column，并且为generate column创建索引可以提高性能。

1.7：InnoDB 全文索引的加强：支持中文分词。InnoDB默认的全文索引parser非常合适于Latin，因为Latin是通过空格来分词的。可以看这篇文章的介绍，但对于像中文，日文和韩文来说，没有这样的分隔符。一个词可以由多个字来组成，所以我们需要用不同的方式来处理。在MySQL 5.7.6中我们能使用一个新的全文索引插件来处理它们：n-gram parser。关于n-gram的介绍和使用可以看这篇文章。

在全文索引中，n-gram就是一段文字里面连续的n个字的序列。例如，用n-gram来对”信息系统”来进行分词，得到的结果如下：

N=1 : '信', '息', '系', '统'

N=2 : '信息', '息系', '系统';

N=3 : '信息系', '息系统';

N=4 : '信息系统';

其中N是有参数ngram_token_size控制，即分词的大小，默认是2。这里简单说明一下：

复制代码

mysql> CREATE TABLE t_fulltext(
    ->   `id` int(11) DEFAULT NULL,
    ->   `name` varchar(512) DEFAULT NULL,
    ->   `content` text,
    ->   FULLTEXT KEY idx_name(name),
    ->   FULLTEXT KEY idx_content(content) WITH PARSER ngram
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.07 sec)
或则建好表后添加：
alter table t_fulltext add fulltext idx_content(content) WITH PARSER ngram;

mysql> INSERT INTO t_fulltext (id,name,content) VALUES (1,'信息系统','自然科学');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t_fulltext WHERE MATCH (name) AGAINST ('信息系统');    ###普通的全文检索必须要整个词才能检索到。
+------+--------------+--------------+
| id   | name         | content      |
+------+--------------+--------------+
|    1 | 信息系统     | 自然科学     |
+------+--------------+--------------+
1 row in set (0.01 sec)

mysql> SELECT * FROM t_fulltext WHERE MATCH (name) AGAINST ('信息');       ###拿里面的出来检索不出来
Empty set (0.01 sec)

mysql> SELECT * FROM t_fulltext WHERE MATCH (content) AGAINST ('自然');    ###新的全文检索功能可以检索到任意2个组合
+------+--------------+--------------+
| id   | name         | content      |
+------+--------------+--------------+
|    1 | 信息系统     | 自然科学     |
+------+--------------+--------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t_fulltext WHERE MATCH (content) AGAINST ('然科');    ###新的全文检索功能可以检索到任意2个组合
+------+--------------+--------------+
| id   | name         | content      |
+------+--------------+--------------+
|    1 | 信息系统     | 自然科学     |
+------+--------------+--------------+
1 row in set (0.01 sec)

复制代码

这里可以查看分词的信息：

复制代码

mysql> INSERT INTO articles (title) VALUES ('信息系统');
Query OK, 1 row affected (0.01 sec)
 
mysql> SET GLOBAL innodb_ft_aux_table="test/articles";
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| 信息   |            1 |           1 |         1 |      1 |        0 |
| 息系   |            1 |           1 |         1 |      1 |        3 |
| 系统   |            1 |           1 |         1 |      1 |        6 |
+--------+--------------+-------------+-----------+--------+----------+
3 rows in set (0.00 sec)

复制代码

关于全文检索的进一步测试，包括停止词语会另起一篇文章说明。

1.8：动态修改InnoDB Buffer Pool Size。

从5.7.5版本起MySQL支持在不重启系统的情况下动态调整innodb_buffer_pool_size。resize的过程是以innodb_buffer_pool_chunk_size(每个chunk的大小默认为128M)的为单位迁移pages到新的内存空间，迁移进度可以通过Innodb_buffer_pool_resize_status查看。即当我们在线修改buffer pool的时候，以chunk为单位进行增长或收缩。innodb要求buffer pool size是innodb_buffer_pool_chunk_size* innodb_buffer_pool_instances的倍数，如果不是，将会适当调大innodb_buffer_pool_size，以满足要求。因此，可能会出现buffer pool的实际分配比配置文件中指定的size要大的情况。

在线调整的操作：

复制代码

mysql> show variables like '%innodb_buffer_pool_size%';  #当前BP的大小512M
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 536870912 |
+-------------------------+-----------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_buffer_pool_instances'; #BP实例1个，因为BP小于1G
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1     |
+------------------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_buffer_pool_size=1048576000;      #设置BP的大小为1000M
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;#设置1000M报warnings，原因是它不是innodb_buffer_pool_chunk_size* innodb_buffer_pool_instances的倍数，即128M的倍数。
+---------+------+-----------------------------------------------------------------+
| Level   | Code | Message                                                         |
+---------+------+-----------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect innodb_buffer_pool_size value: '1048576000' |
+---------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show global status like 'Innodb_buffer_pool_resize_status'; #查看进度
+----------------------------------+----------------------------------------------------+
| Variable_name                    | Value                                              |
+----------------------------------+----------------------------------------------------+
| Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 160708 18:20:15. |
+----------------------------------+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> show variables like '%innodb_buffer_pool_size%'; #被设置成了1024M，因为1024是128的整数倍，出现了BP比配置文件里指定的size还大。
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 1073741824 |
+-------------------------+------------+
1 row in set (0.00 sec)

复制代码

在线调整BP的日志：

复制代码

2016-07-08T18:20:15.278753+08:00 3 [Note] InnoDB: Requested to resize buffer pool. (new size: 1073741824 bytes) #调整的BP大小。
2016-07-08T18:20:15.278786+08:00 0 [Note] InnoDB: Resizing buffer pool from 536870912 to 1073741824 (unit=134217728).#从多大到多大，单位多少
2016-07-08T18:20:15.278845+08:00 0 [Note] InnoDB: Disabling adaptive hash index. #禁用AHI，清理所有的索引缓存
2016-07-08T18:20:15.280839+08:00 0 [Note] InnoDB: disabled adaptive hash index.  
2016-07-08T18:20:15.280864+08:00 0 [Note] InnoDB: Withdrawing blocks to be shrunken.#回收空闲的block
2016-07-08T18:20:15.280876+08:00 0 [Note] InnoDB: Latching whole of buffer pool. #锁住整个BP
2016-07-08T18:20:15.280896+08:00 0 [Note] InnoDB: buffer pool 0 : resizing with chunks 4 to 8. #大小从4个chunks变成8个chunks
2016-07-08T18:20:15.295961+08:00 0 [Note] InnoDB: buffer pool 0 : 4 chunks (32764 blocks) were added.
2016-07-08T18:20:15.296015+08:00 0 [Note] InnoDB: Completed to resize buffer pool from 536870912 to 1073741824. #设置新值完成
2016-07-08T18:20:15.296031+08:00 0 [Note] InnoDB: Re-enabled adaptive hash index. #开启AHI
2016-07-08T18:20:15.296048+08:00 0 [Note] InnoDB: Completed resizing buffer pool at 160708 18:20:15. #调整完成

复制代码

1.9：多线程脏页刷写

innodb_page_cleaners，表示刷写BP脏页的线程数，5.6.2开始从master线程中独立出来，5.7.4之后开始支持多线程flush，默认是4。这个值必须小于等于innodb_buffer_pool_instances，提高CPU的利用率。

1.10：终止会话ctrl+c

在linux下，我们经常使用ctrl+c来终止一个命令的运行，在MySQL 5.7 之前，如果用户输入了错误的SQL语句，按下ctrl+c，虽然能够"结束"SQL语句的运行，但是，也会退出当前会话，MySQL 5.7对这一违反直觉的地方进行了改进，不再退出会话。

1.11：临时表性能的优化

MYSQL5.7为了提高临时表相关的性能，对临时表相关的部分进行了大幅修改，包括引入新的临时表空间（ibtmp1）；对于临时表的DDL，不持久化相关表定义；对于临时表的DML，不写redo，关闭change buffer等。

InnoDB临时表元数据不再存储于InnoDB系统表而是存储在INNODB_TEMP_TABLE_INFO，包含所有用户和系统创建的临时表信息。该表在第一次在其上运行select时被创建：

复制代码

mysql> select * from information_schema.innodb_temp_table_info;
Empty set (0.00 sec)

mysql> use dba_test
mysql> create temporary table temp_1(id int,name varchar(100))default charset utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.innodb_temp_table_info;
+----------+--------------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME         | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+--------------+--------+-------+----------------------+---------------+
|       42 | #sql1883_8_0 |      5 |    27 | FALSE                | FALSE         |
+----------+--------------+--------+-------+----------------------+---------------+
1 row in set (0.00 sec)

复制代码

并且MySQL5.7使用了独立的临时表空间来存储临时表数据，但不能是压缩表。临时表空间在实例启动的时候进行创建，shutdown的时候进行删除。即为所有非压缩的innodb临时表提供一个独立的表空间，默认的临时表空间文件为ibtmp1，位于数据目录。我们可通过innodb_temp_data_file_path参数指定临时表空间的路径和大小，默认12M。只有重启实例才能回收临时表空间文件ibtmp1的大小。create temporary table和using temporary table将共用这个临时表空间。

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

物理文件：

复制代码

# ls -lh ibtmp1 
-rw-r----- 1 mysql mysql 12M  7月 12 15:13 ibtmp1

mysql> create temporary table temp_1(id int,name varchar(100))default character set utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into temp_1 select * from ttt;
Query OK, 2752512 rows affected (7.10 sec)
Records: 2752512  Duplicates: 0  Warnings: 0

# ls -lh ibtmp1 
-rw-r----- 1 mysql mysql 204M  7月 12 15:16 ibtmp1   #重启才能回收

复制代码

在MySQL5.7中，临时表在连接断开或者数据库实例关闭的时候，会进行删除，也就无需redo logs，避免了写relog相关的io，从而提高了性能。只有临时表的metadata使用了redo保护，保护元数据的完整性，以便异常启动后进行清理工作。临时表的元数据5.7之后使用了一个独立的表（innodb_temp_table_info）进行保存，这样就不要使用redo保护，元数据也只保存在内存中。但这有一个前提，必须使用共享的临时表空间，如果使用file-per-table，仍然需要持久化元数据，以便异常恢复清理。临时表需要undo log，用于MySQL运行时的回滚、MVCC等。具体的可以看官方文档和临时表优化说明。

注意：从5.7.5开始，新增一个系统选项internal_tmp_disk_storage_engine可定义磁盘临时表的引擎类型，默认为InnoDB，可选MyISAM。而在这以前，只能使用MyISAM。而在5.6.3以后新增的参数default_tmp_storage_engine是控制create temporary table创建的临时表的存储引擎，在以前默认是MEMORY。

复制代码

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

复制代码

1.12：innochecksum离线的InnoDB文件校验工具功能增强，可以查看ibd文件。新增选择项或扩展的功能：

复制代码

1.支持使用指定校验算法；

2.支持只重写校验值而不进行验证；

3.可指定允许的校验和不匹配量；

4.显示各类页的个数、导出页类型信息、输出至日志、从标准输入读取数据等；

5.从5.7.2起可支持校验超过2G的文件；

复制代码

如何使用innochecksum可以看官网和执行innochecksum --help。使用时需要停止mysql服务，或则把ibd文件复制出来，否则会报错：

# innochecksum --page-type-summary ibdata1
Error: Unable to lock file:: ibdata1
fcntl: Resource temporarily unavailable

关闭数据库或则复制出ibd文件执行：

复制代码

# innochecksum --page-type-summary /tmp/ibdata1 

File::/tmp/ibdata1
================PAGE TYPE SUMMARY==============
#PAGE_COUNT    PAGE_TYPE
===============================================
      13    Index page       #索引页
    2820    Undo log page    #undo页
       2    Inode page        
       0    Insert buffer free list page  #插入缓存空闲页
    1928    Freshly allocated page        #可用页
       1    Insert buffer bitmap          #插入缓存位图页
      98    System page                   #系统页
       1    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: 2806 insert, 14 update, 0 other
Undo page state: 0 active, 36 cached, 0 to_free, 0 to_purge, 0 prepared, 2784 other

复制代码

通过 --page-type-summary 参数可以看到表空间里面各个页的情况，关于页的一些细节信息可以参考看MySQL Innodb 存储结构。

通过 --count 参数可以查看表空间的总页数：

# innochecksum --count /tmp/ibdata1 
Number of pages:4864

更多的信息可以查看官方文档和这篇文章说明。

1.13：默认行格式改变，由innodb_default_row_format控制，5.7.9之后默认DYNAMIC，之前默认COMPACT。更多信息见官方文档。

1.14：支持创建全局通用表空间，全局表空间可以被所有的数据库的表共享，而且相比于独享表空间使用手动创建共享表空间可以节约元数据方面的内存。可以在创建表的时候，指定属于哪个表空间，也可以对已有表进行表空间修改，具体的信息可以查看官方文档。

复制代码

mysql> create tablespace dxy add datafile 'dxy.ibd' file_block_size=16k;  #创建名为dxy的共享表空间
Query OK, 0 rows affected (0.10 sec)

mysql> create table t1(id int,name varchar(10))engine = innodb default charset utf8mb4 tablespace dxy; #指定表空间
Query OK, 0 rows affected (0.09 sec)

# ls -lh
-rw-r----- 1 mysql mysql  96K  7月 14 12:11 dxy.ibd   #查看表空间文件

mysql> alter table ttt tablespace dxy;                #指定表空间

# ls -lh dxy.ibd           
-rw-r----- 1 mysql mysql 244M  7月 14 12:18 dxy.ibd   #表空间文件变大

复制代码

如何删除创建的共享表空间？因为是共享表空间不能直接通过drop table tbname来删除，也不能回收空间。当确定共享表空间的数据都没用，并且依赖该表空间的表均已经删除，则可以通过drop tablespace来删除共享表空间来释放空间，如果依赖该共享表空间的表存在则会删除失败。

复制代码

# ls -lh dxy.ibd 
-rw-r----- 1 mysql mysql 452M  7月 14 12:27 dxy.ibd

mysql> drop table t1;                #删除依赖该表空间的表
Query OK, 0 rows affected (0.16 sec)

mysql> drop tablespace dxy;          #删除表空间
ERROR 1529 (HY000): Failed to drop TABLESPACE dxy   #删除失败，因为还有表依赖这个表空间。

mysql> drop table ttt;
Query OK, 0 rows affected (0.04 sec)
mysql> drop tablespace dxy;          #成功删除自建的共享表空间
Query OK, 0 rows affected (0.03 sec)

# ls -lh dxy.ibd 
ls: cannot access dxy.ibd: No such file or directory     #ibd文件被删除，空间回收。

复制代码

1.15：I nnoDB Tablespace Encryption，支持对独享表空间的InnoDB数据文件加密，其依赖keyring plugin来进行秘钥的管理，开启加密功能需要启动参数--early-plugin-load。

[mysqld]
early-plugin-load=keyring_file.so

开始参数后，查看是否支持：

复制代码

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS where PLUGIN_NAME LIKE 'keyring%';
+--------------+---------------+
| PLUGIN_NAME  | PLUGIN_STATUS |
+--------------+---------------+
| keyring_file | ACTIVE        |
+--------------+---------------+
1 row in set (0.00 sec)

复制代码

创建加密表空间：

复制代码

mysql> create table t(id int,name varchar(10))engine = innodb default charset utf8mb4 encryption='y';
Query OK, 0 rows affected (0.17 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ENCRYPTION='y'
1 row in set (0.00 sec)

复制代码

1.16：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操作。

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

1.18：支持在一个table对象上建多个trigger。

持续更新...

...

2，sys schema

MySQL 5.7.7版本之后新增了sys 数据库，该库通过视图的形式把information_schema和performance_schema结合起来，查询出更加令人容易理解的数据，帮助dba和开发人员收集的数据库性能信息，快速定位性能瓶颈。sys下的一共包括三种对象：1. view，2. procedure 3 function
这些对象都是基于performance_schema下的表，进行了可读性的聚合，没有真正存储数据，只存储了定义。默认通过账号'mysql.sys'@'localhost'收集信息，之前是root@localhost进行收集操作。安装时可以通过--skip-sys-schema跳过安装。后面会另起一篇文章对sys schema进行说明，也可以先看DBA的好帮手——sys schema和MySQL 5.7系列之sys schema(2) by吴炳锡。

3，被删除和被修改默认值的参数和特性

参数移除：

3.1：MySQL 5.7.4之后innodb_additional_mem_pool_size参数被移除。原来是存放数据字典和内部数据结构，要是表很多，需要分配更多的内存。若缓冲池的内存溢出则需要从系统分配内存，但会写一个warning到错误日志。

3.2：MySQL5.7.4之后innodb_use_sys_malloc参数被移除。原来表示InnoDB使用操作系统内存分配器还是自己的内存分配器，默认是使用操作系统内存分配器。3.1和3.2被移除的原因，是因为内部实现不如外部的实现，可以见http://www.kancloud.cn/taobaomysql/monthly/143931。

3.3：innodb 存储引擎不能被禁用，系统表有innodb表。 --innodb=OFF and --skip-innodb没有作用，未来会移除该参数。

3.4：innodb_file_format、innodb_file_format_check、innodb_file_format_max在MySQL5.7.7被弃用，未来会被移除。

3.5：innodb_large_prefix在MySQL5.7.7中被弃用，将来会移除。该参数表示当innodb为字段创建索引时，限制的字节长度。关闭时，字节长度大于767则会报warnings。开启时，则会报错，创建不成功。

3.6：innodb_locks_unsafe_for_binlog在MySQL5.6.3被弃用，将来会移除。该参数表示innodb锁和二进制的安全问题，当ON的时候可以避免gap lock的问题，但是会引起binlog写入顺序出问题，导致主从数据不一致。建议关闭。

3.7：innodb_support_xa在MySQL5.7.1中被弃用，将来会移除。MySQL5.7.1之后不能禁用innodb_support_xa。虽然它会导致一次额外的磁盘flush(prepare阶段flush redo log). 但是我们必须启用，而不能关闭它。因为关闭会导致binlog写入的顺序和实际的事务提交顺序不一致，会导致崩溃恢复和slave复制时发生数据错误。如果启用了log-bin参数，并且不止一个线程对数据库进行修改，那么就必须启用innodb_support_xa参数。

3.8：@@session.gtid_executed 不建议使用，将来会移除。

3.9：binlog_format在MySQL5.7.7之后，默认值变成ROW。

3.10：slave_net_timeout在MySQL5.7.7之后，默认改成60秒。该参数定义了从库从主库获取数据等待的秒数，超过这个时间从库会主动退出读取，中断连接，并尝试重连。即：设置在多久没收到数据后认为网络超时，之后 Slave 的 IO 线程会重新连接 Master。此前的默认值是3600秒，长时间的复制延迟很可能是网络瞬断造成的。可以用一个心跳时间master_heartbeat_period来避免，具体可以看MySQL复制心跳的详细说明。大致的流程如下：

复制代码

说明：
在MySQL的复制协议里，由 Slave 发送一个 COM_BINLOG_DUMP 命令后，就完全由 Master 来推送数据，Master、Slave 之间不再需要交互。如果 Master 没有更新，也就不会有数据流，Slave 就不会收到任何数据包。但是如果由于某种原因造成 Master 无法把数据发送到 Slave ，比如发生过网络故障或其他原因导致 Master 上的 TCP 连接丢失，Slave 没有机会得到通知，所以也没法知道收不到数据是因为 Master 本来就没有更新呢还是由于出了故障。

stop slave;
change master to master_heartbeat_period = 10;
set global slave_net_timeout = 25;
start slave;

心跳会让Master 在没有数据的时候，每10秒发送一个心跳包。这样 Slave 就能知道 Master 是不是还正常。slave_net_timeout是设置在多久没收到数据后认为网络超时，之后 Slave 的 IO 线程会重新连接 Master 。
结合这两个设置就可以避免由于网络问题导致的复制延误。master_heartbeat_period 单位是秒，可以是个带上小数，如 10.5。最高精度为 1 毫秒。

需要注意的是：默认情况heartbeat_period的值是slave_net_timeout的一般。在从上通过show global status like 'slave%'来查看心跳信息。
要是heartbeat_period设置大于slave_net_timeout会报warnings：
The requested value for the heartbeat period exceeds the value of `slave_net_timeout' seconds. A sensible value for the period should be less than the timeout.

复制代码

关于从库重连主库的相关信息：

复制代码

MySQL 可以指定三个参数，用于复制线程重连主库： --master-retry-count ，  --master-connect-retry ，  --slave-net-timeout 。
其中 master-connect-retry 和 master-retry-count 需要在 Change Master 搭建主备复制时指定，而 slave-net-timeout 是一个全局变量，可以在 MySQL 运行时在线设置。

具体的重试策略为：备库过了slave-net-timeout秒还没有收到主库来的数据，它就会开始第一次重试。然后每过 master-connect-retry 秒，备库会再次尝试重连主库。直到重试了 master-retry-count 次，它才会放弃重试。如果重试的过程中，连上了主库，那么它认为当前主库是好的，又会开始 slave-net-timeout 秒的等待。
slave-net-timeout 的默认值是 60 秒， master-connect-retry 默认为 60 秒， master-retry-count 默认为 86400 次。也就是说，如果主库一分钟都没有任何数据变更发送过来，备库才会尝试重连主库。

如果主库上变更比较频繁，可以考虑将 slave-net-timeout 设置的小一点，避免主库Binlog dump（IO）线程终止，无法将最新的更新推送过来，及时发现问题而不是等到超时之后才收到报错去处理。
当然 slave-net-timeout 设置的过小也有问题，这样会导致如果主库的变更确实比较少的时候，备库频繁的重新连接主库，造成资源浪费。

复制代码

3.11：sync_binlog在MySQL5.7.7之后，默认值改成了1，更安全。因为MySQL5.6支持了组提交的功能，所以值1不像5.6之前的说的binlog提交一次就写一次磁盘，而是表了在把binlog刷新到磁盘前，提交的组的数量。

...

特性移除：

3.1：innodb_table_monitor特性在MySQL 5.7.4中被移除。通过参数innodb_status_output、innodb_status_output_locks动态修改成on，把show engine innodb status打印到error log中；通过information_schema中INNODB_SYS%开头的表可以把innodb_table_monitor的信息显示出来，对比说明下他们的差异，方便自己查阅。

复制代码

####通过innodb_table_monitor打印到error log 中的信息，关于说明请见innodb_table_monitor的介绍
TABLE: name jute/jute_vote, id 149, flags 1, columns 8, indexes 1, appr.rows 182198
  COLUMNS: surveyId: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; userId: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; choiceId: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; voteTime: DATA_FIXBINARY DATA_BINARY_TYPE DATA_NOT_NULL len 5; comment: DATA_BLOB len 11; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
  INDEX: name PRIMARY, id 324, fields 2/7, uniq 2, type 3
   root page 3, appr.key vals 182198, leaf pages 472, size pages 545
   FIELDS:  surveyId userId DB_TRX_ID DB_ROLL_PTR choiceId voteTime comment

#####通过information_schema中各相关表(7张表)的说明，和上面对比
information_schema 12:31:21>select * from INNODB_SYS_TABLES where NAME='jute/jute_vote';
+----------+----------------+------+--------+-------+-------------+------------+---------------+
| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
+----------+----------------+------+--------+-------+-------------+------------+---------------+
|      149 | jute/jute_vote |    1 |      8 |   143 | Antelope    | Compact    |             0 |
+----------+----------------+------+--------+-------+-------------+------------+---------------+
1 row in set (0.00 sec)

information_schema 12:32:30>select * from INNODB_SYS_DATAFILES where SPACE=143;
+-------+----------------------+
| SPACE | PATH                 |
+-------+----------------------+
|   143 | ./jute/jute_vote.ibd |
+-------+----------------------+
1 row in set (0.00 sec)

information_schema 12:32:44>select * from INNODB_SYS_TABLESPACES where SPACE=143;
+-------+----------------+------+-------------+----------------------+-----------+---------------+
| SPACE | NAME           | FLAG | FILE_FORMAT | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE |
+-------+----------------+------+-------------+----------------------+-----------+---------------+
|   143 | jute/jute_vote |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
+-------+----------------+------+-------------+----------------------+-----------+---------------+
1 row in set (0.01 sec)

information_schema 12:32:55>select * from INNODB_SYS_TABLESTATS where table_id=149;
+----------+----------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME           | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+----------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|      149 | jute/jute_vote | Initialized       |   182198 |              545 |                0 |                0 |       0 |         1 |
+----------+----------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.01 sec)

information_schema 12:38:33>select * from INNODB_SYS_INDEXES where TABLE_ID=149;
+----------+---------+----------+------+----------+---------+-------+
| INDEX_ID | NAME    | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE |
+----------+---------+----------+------+----------+---------+-------+
|      324 | PRIMARY |      149 |    3 |        2 |       3 |   143 |
+----------+---------+----------+------+----------+---------+-------+
1 row in set (0.01 sec)

information_schema 12:33:25>select * from INNODB_SYS_COLUMNS where TABLE_ID=149;
+----------+----------+-----+-------+---------+-----+
| TABLE_ID | NAME     | POS | MTYPE | PRTYPE  | LEN |
+----------+----------+-----+-------+---------+-----+
|      149 | surveyId |   0 |     6 |    1283 |   4 |
|      149 | userId   |   1 |     6 |    1283 |   4 |
|      149 | choiceId |   2 |     6 |    1283 |   4 |
|      149 | voteTime |   3 |     3 |  525580 |   5 |
|      149 | comment  |   4 |     5 | 2949372 |  11 |
+----------+----------+-----+-------+---------+-----+
5 rows in set (0.01 sec)

information_schema 12:33:37>select * from INNODB_SYS_FIELDS where INDEX_ID in (324);
+----------+----------+-----+
| INDEX_ID | NAME     | POS |
+----------+----------+-----+
|      324 | surveyId |   0 |
|      324 | userId   |   1 |
+----------+----------+-----+
2 rows in set (0.00 sec)

复制代码

也可以通过一条sql进行对innodb_table_monitor的展示，里面各参数的意义看官方文档：

复制代码

SELECT  
    a.PATH datafile,b.NAME name,b.TABLE_ID table_id,b.FLAG flags,b.N_COLS columns,count(distinct c.INDEX_ID) indexes,d.NUM_ROWS `appr.rows`,group_concat(distinct e.NAME) column_name,substring_index(group_concat(e.LEN),',',b.N_COLS-3) column_len,c.NAME index_name,c.INDEX_ID index_id,group_concat(distinct g.NAME) index_fields,d.CLUST_INDEX_SIZE,d.OTHER_INDEX_SIZE,f.PAGE_SIZE,b.FILE_FORMAT,b.ROW_FORMAT 
FROM 
    INNODB_SYS_TABLES b,INNODB_SYS_DATAFILES a,INNODB_SYS_INDEXES c,INNODB_SYS_TABLESTATS d,INNODB_SYS_COLUMNS e,INNODB_SYS_TABLESPACES f,INNODB_SYS_FIELDS g 
WHERE 
    a.SPACE=b.SPACE and b.TABLE_ID=c.TABLE_ID and b.TABLE_ID=d.TABLE_ID and b.TABLE_ID=e.TABLE_ID and b.SPACE=f.SPACE and c.INDEX_ID=g.INDEX_ID and b. NAME='jute/jute_vote' 
GROUP BY c.TABLE_ID,c.INDEX_ID;

复制代码

效果如下：

复制代码

*************************** 1. row ***************************
        datafile: ./jute/jute_vote.ibd
            name: jute/jute_vote
        table_id: 149         ###表id
           flags: 1
         columns: 8           ###表只有5列，为什么显示8列？因为包含了隐藏列：DB_ROW_ID(6)、DB_TRX_ID(6)、DB_ROLL_PTR(7)
         indexes: 1
       appr.rows: 182198      ###表行的估算值
     column_name: surveyId,choiceId,voteTime,userId,comment  ###表列
      column_len: 4,4,5,4,4   ###对应表列的长度
      index_name: PRIMARY     ###索引名
        index_id: 324         ###索引id
    index_fields: surveyId,userId    ###索引列
CLUST_INDEX_SIZE: 545         ###聚集索引的页数
OTHER_INDEX_SIZE: 0           ###二级索引的页数，表中没有二级索引
       PAGE_SIZE: 16384       ###页的单位，16K
     FILE_FORMAT: Antelope    ###文件格式
      ROW_FORMAT: Compact     ####行格式

复制代码

从上面看出虽然能用information_schema中的各表展示，但还是少了一些信息，比如隐藏字段和索引包括主键的完整字段。

3.2：alter ignore table 语法被移除。

3.3：SHOW PROFILE 语法将被移除。通过information_schema.profiling或则performance_schema相关表来代替，如：

information_schema显示：

##老
show profile cpu,block io for query 2;
##information_schema
SELECT query_id,state,DURATION,CPU_USER,CPU_SYSTEM,BLOCK_OPS_IN,BLOCK_OPS_OUT FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 36;

performance_schema显示：

复制代码

官方文档已经出了说明，这里大致讲下如何通过performance_schema查看，关于performance_schema的说明可以看这里
1）setup_actors：配置用户纬度的监控，默认监控所有用户
performance_schema 01:40:04>SELECT * FROM setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | YES     | YES     |
+------+------+------+---------+---------+
默认是监控所有用户的操作，会有一定性能的损耗，所以只需要开启指定用户的即可：
performance_schema 01:40:23>UPDATE performance_schema.setup_actors SET ENABLED = 'NO', HISTORY = 'NO' WHERE HOST = '%' AND USER = '%';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

performance_schema 01:40:40>SELECT * FROM setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | NO      | NO      |
+------+------+------+---------+---------+
1 row in set (0.17 sec)

##开启对dba用户的监控：
performance_schema 01:40:42>INSERT INTO performance_schema.setup_actors (HOST,USER,ROLE,ENABLED,HISTORY)  VALUES('192.168.100.%','dba','%','YES','YES');

performance_schema 01:43:08>SELECT * FROM setup_actors;
+---------------+------+------+---------+---------+
| HOST          | USER | ROLE | ENABLED | HISTORY |
+---------------+------+------+---------+---------+
| %             | %    | %    | NO      | NO      |
| 192.168.100.% | dba  | %    | YES     | YES     |
+---------------+------+------+---------+---------+

2）打开所有需要监控的选项：
performance_schema 01:51:35>UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%statement/%';

performance_schema 01:57:01>UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%stage/%';

performance_schema 01:57:09>UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%';

performance_schema 01:57:27>UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%';

3）执行需要监控的语句
select .....

4）查看：
①老的：
performance_schema 01:42:06>show profile cpu,block io for query 6;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000178 |     NULL |       NULL |         NULL |          NULL |
| checking permissions | 0.000012 |     NULL |       NULL |         NULL |          NULL |
| Opening tables       | 0.000050 |     NULL |       NULL |         NULL |          NULL |
| init                 | 0.000065 |     NULL |       NULL |         NULL |          NULL |
| System lock          | 0.000017 |     NULL |       NULL |         NULL |          NULL |
| optimizing           | 0.000022 |     NULL |       NULL |         NULL |          NULL |
| statistics           | 0.001057 |     NULL |       NULL |         NULL |          NULL |
| preparing            | 0.000135 |     NULL |       NULL |         NULL |          NULL |
| executing            | 0.000009 |     NULL |       NULL |         NULL |          NULL |
| Sending data         | 1.265446 |     NULL |       NULL |         NULL |          NULL |
| end                  | 0.000013 |     NULL |       NULL |         NULL |          NULL |
| query end            | 0.000393 |     NULL |       NULL |         NULL |          NULL |
| removing tmp table   | 0.000033 |     NULL |       NULL |         NULL |          NULL |
| query end            | 0.000004 |     NULL |       NULL |         NULL |          NULL |
| closing tables       | 0.000013 |     NULL |       NULL |         NULL |          NULL |
| freeing items        | 0.000056 |     NULL |       NULL |         NULL |          NULL |
| logging slow query   | 0.000006 |     NULL |       NULL |         NULL |          NULL |
| logging slow query   | 0.000202 |     NULL |       NULL |         NULL |          NULL |
| cleaning up          | 0.000042 |     NULL |       NULL |         NULL |          NULL |
+----------------------+----------+----------+------------+--------------+---------------+

②新的：
先找出EVENT_ID:
SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%18,19,20,21%';
...
...

通过上面找到的EVENT_ID再执行：
SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=231;
+--------------------------------+----------+
| Stage                          | Duration |
+--------------------------------+----------+
| stage/sql/starting             | 0.000213 |
| stage/sql/checking permissions | 0.000009 |
| stage/sql/Opening tables       | 0.000049 |
| stage/sql/init                 | 0.000065 |
| stage/sql/System lock          | 0.000016 |
| stage/sql/optimizing           | 0.000022 |
| stage/sql/statistics           | 0.001060 |
| stage/sql/preparing            | 0.000136 |
| stage/sql/executing            | 0.000003 |
| stage/sql/Sending data         | 1.265452 |
| stage/sql/end                  | 0.000006 |
| stage/sql/query end            | 0.000412 |
| stage/sql/removing tmp table   | 0.000011 |
| stage/sql/closing tables       | 0.000012 |
| stage/sql/freeing items        | 0.000058 |
| stage/sql/logging slow query   | 0.000203 |
| stage/sql/cleaning up          | 0.000002 |
+--------------------------------+----------+

复制代码

3.4：采用ALTER USER来为用户修改密码，不建议再使用SET PASSWORD修改密码。

...

4，总结

在官方测试里看到，MySQL5.7在功能、性能、可用性、安全和监控上又提升了很高。如上面介绍的这些，特别是复制方面的改进：多线程、多源、GTID，半同步等都做了很大改进，以及中文分词的支持还有sys schema上面的数据库性能信息状态收集等。后续会详细介绍这几方面的知识，加强对MySQL5.7的认识，本文只是说了一些自己比较关注的，比较详细的请见 What’s New in MySQL 5.7，后续会持续更新说明。

5，参考文档：

What Is New in MySQL 5.7

MySQL5.7中InnoDB不可不知的新特性

转载地址：http://www.cnblogs.com/zhoujinyi/p/5634382.html

l_215851356

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL 5.7 学习：功能性能的提升

背景：继上次介绍初识 MySQL 5.6 新功能、参数完之后，刚好MySQL 5.7又GA了，在官方测试里看到，MySQL5.7在功能、性能、可用性、安全和监控上又提升了很高。现在看看和MySQL5.6对比，之前介绍了新增配置参数和安全相关特性。本文来说明MySQL5.7关于功能和性能提升的方面（持续更新）。1，功能性能上的提升1.1：复制功能的提升。①支持并行复制。slave-...
复制链接

扫一扫