mysql 8.0新特性

最新推荐文章于 2024-08-08 21:10:03 发布

xiaoxiao树

最新推荐文章于 2024-08-08 21:10:03 发布

阅读量654

点赞数

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

本文链接：https://blog.csdn.net/u010719917/article/details/103769673

版权

mysql 专栏收录该内容

78 篇文章 0 订阅

订阅专栏

What Is New in MySQL 8.0.13

用户安全管理:

.新认证插件caching_sha2_password默认认正插件，

比mysql_native_password 更安全

比sha256_password更好的性能

root@localhost 14:53:53 [(none)]>show variables like 'default_authentication_plugin' ;

+-------------------------------+-----------------------+

| Variable_name | Value |

+-------------------------------+-----------------------+

| default_authentication_plugin | caching_sha2_password |

+-------------------------------+-----------------------+

ALTER USER user IDENTIFIED WITH caching_sha2_password BY 'password';

支持role

CREATE ROLE 'app_developer', 'app_read', 'app_write';

GRANT ALL ON app_db.* TO 'app_developer';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'Dev1pass@123';
CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'Read_user1pass@123';
CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'Read_user2pass@123';
CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'Rw_user1pass@123';

GRANT 'app_developer' TO 'dev1'@'localhost';
GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';

Show grants for 'dev1'@'localhost';

托管角色：

自动赋给所有用户，由系统变量mandatory_roles设置，设置权限ROLE_ADMIN

[mysqld]
mandatory_roles='role1,role2@localhost,r3@%.example.com'

SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com';

撤销只能drop role 或 drop user

角色权限的检查：

SHOW GRANTS FOR 'dev1'@'localhost'

SHOW GRANTS FOR 'dev1'@'localhost' USING 'app_developer';

SHOW GRANTS FOR 'read_user1'@'localhost' USING 'app_read';

激活角色：

系统变量：activate_all_roles_on_login

操作：SET ROLE

当前用户的角色

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE |
+----------------+

SET DEFAULT ROLE ALL TO
'dev1'@'localhost',
'read_user1'@'localhost',
'read_user2'@'localhost',
'rw_user1'@'localhost';

mysql> SELECT CURRENT_ROLE();
+--------------------------------+
| CURRENT_ROLE() |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+

ALTER USER 'joe'@'10.0.0.1' DEFAULT ROLE administrator, developer;

REVOKE role FROM user;

REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM 'app_write';
DROP ROLE 'app_read', 'app_write';

查看当前激活role信息：

Select * from role_edges ;

Select * from default_roles;

资源管理

创建管理资源组，允许把特定服务进程分配到资源组。

管理权限RESOURCE_GROUP_ADMIN 或 RESOURCE_GROUP_USER

root@localhost 16:42:54 [information_schema]>select * from resource_groups ;

+---------------------+---------------------+------------------------+----------+-----------------+

+---------------------+---------------------+------------------------+----------+-----------------+

| USR_default | USER | 1 | 0-0 | 0 |

| SYS_default | SYSTEM | 1 | 0-0 | 0 |

+---------------------+---------------------+------------------------+----------+-----------------+

User_defaut 和 sys_default 不能被删也不能被修改

CREATE RESOURCE GROUP Batch TYPE = USER VCPU = 0 THREAD_PRIORITY = 10;

SET RESOURCE GROUP Batch FOR thread_id;

SET RESOURCE GROUP Batch;

INSERT /*+ RESOURCE_GROUP(Batch) */ INTO t2 VALUES(2);

ALTER RESOURCE GROUP Batch VCPU = 0 THREAD_PRIORITY = 19;

InnoDB 增强

自增增长序列持久化:

.max auto-increment counter valuer的值每次更改会写入 redo log 并且每次checkpoint时会保存在engine-private 系统表中。保证当前最大的auto-increment值重启时持久化

..重启时，不影响表的AUTO_INCREMENT = N

..重启服务器后的rollback操作，不会导致重用auto-increment

..更新auto-increment更大值，会持久保存，随后的值在新值上增加

root@localhost 18:12:59 [test]>show variables like 'innodb_autoinc_lock_mode' ;

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| innodb_autoinc_lock_mode | 2 |

+--------------------------+-------+

innodb_deadlock_detect

动态更新，死锁探测。高并发系统，开启时，会影响系统性能；低并发时，关闭，会影响死锁的响应时间（innodb_lock_wait_timeout）

root@localhost 23:15:04 [mysql]>show variables like 'innodb_deadlock_detect';

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| innodb_deadlock_detect | OFF |

+------------------------+-------+

新增INFORMATION_SCHEMA.INNODB_CACHED_INDEXES

显示每个index有多少index page在buffer pool中

临时表被创建在共享临时表空间ibtmp1(工艺里有，innodb_temp_data_file_path)

root@localhost 23:18:02 [mysql]>show variables like '%innodb_temp_data_file_path%' ;

+----------------------------+--------------------------------+

| Variable_name | Value |

+----------------------------+--------------------------------+

| innodb_temp_data_file_path | ibtmp1:512M:autoextend:max:32G |

+----------------------------+--------------------------------+

innodb 表空间加密特征支持 redo log 和 undo log 加密

root@localhost 23:18:08 [mysql]>show variables like 'innodb_redo_log_encrypt' ;

+-------------------------+-------+

| Variable_name | Value |

+-------------------------+-------+

| innodb_redo_log_encrypt | OFF |

+-------------------------+-------+

1 row in set (0.00 sec)

root@localhost 23:19:01 [mysql]>show variables like 'innodb_undo_log_encrypt' ;

+-------------------------+-------+

| Variable_name | Value |

+-------------------------+-------+

| innodb_undo_log_encrypt | OFF |

+-------------------------+-------+

1 row in set (0.01 sec)

Innodb 支持 NOWAIT和 SKIP LOCKED操作

NOWAIT：要求行被另外一个事务锁，立即返回

SKIP LOCKED：从结束集中移除被锁定的行返回

Of tabled : for update ,for share 指定表被锁

Session 1

root@localhost 23:20:12 [test]>CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

Query OK, 0 rows affected (0.14 sec)

root@localhost 23:20:13 [test]>INSERT INTO t (i) VALUES(1),(2),(3);

Query OK, 3 rows affected (0.09 sec)

Records: 3 Duplicates: 0 Warnings: 0

root@localhost 23:20:25 [test]>START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

root@localhost 23:20:32 [test]>SELECT * FROM t WHERE i = 2 FOR UPDATE;

+---+

Sssion 2

root@localhost 23:21:27 [test]>START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

root@localhost 23:21:30 [test]>SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;

ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

root@localhost 23:21:33 [test]>START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

root@localhost 23:21:53 [test]> SELECT * FROM t FOR UPDATE SKIP LOCKED;

+---+

| i |

+---+

| 1 |

| 3 |

+---+

2 rows in set (0.00 sec)

root@localhost 23:22:03 [test]>START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

root@localhost 23:22:07 [test]> SELECT * FROM t for update ;

.partition表支持本地in-place 的add /drop/COALESCE/REORGANIZE/REBUILD PARTITION ALTER TABLE

.数据字典用innodb engine

. mysql system table 和 data dictionary tables 创建在一个innodb 表空间文件中，文件名为mysql.ibd.之前是在mysql目录下创建每个单独的innodb表空间文件

root@localhost 23:35:11 [information_schema]>select table_schema ,table_name ,table_type ,engine from tables where table_schema = 'mysql' ;

+--------------+---------------------------+------------+--------+

+--------------+---------------------------+------------+--------+

.innodb_dedicated_server

默认是关闭，能够自动分配以下内存：

Innodb_buffer_pool_size

Innodb_log_file_size

Innodb_flush_method=O_DIRECT_NO_FSYNC(系统不适用时，转换为默认值fsync)

root@localhost 23:35:51 [information_schema]>show variables like 'innodb_dedicated_server' ;

+-------------------------+-------+

| Variable_name | Value |

+-------------------------+-------+

| innodb_dedicated_server | OFF |

+-------------------------+-------+

root@localhost 23:43:16 [information_schema]>show variables like 'innodb_buffer_pool_size' ;

+-------------------------+------------+

| Variable_name | Value |

+-------------------------+------------+

| innodb_buffer_pool_size | 4294967296 |

+-------------------------+------------+

root@localhost 23:43:30 [information_schema]>show variables like 'innodb_log_file_size' ;

+----------------------+------------+

| Variable_name | Value |

+----------------------+------------+

| innodb_log_file_size | 1073741824 |

+----------------------+------------+

root@localhost 23:43:41 [information_schema]>show variables like 'innodb_flush_method' ;

+---------------------+----------+

| Variable_name | Value |

+---------------------+----------+

| innodb_flush_method | O_DIRECT |

+---------------------+----------+

在my.cnf中关闭innodb_buffer_pool_size、innodb_log_file_size、innodb_flush_method ，开启innodb_dedicated_server

select * from metrics where VARIABLE_NAME in ('innodb_buffer_pool_size','innodb_log_file_size','innodb_flush_method');

root@localhost 23:58:34 [(none)]>show variables like 'innodb_buffer_pool_size' ;

+-------------------------+-----------+

| Variable_name | Value |

+-------------------------+-----------+

| innodb_buffer_pool_size | 134217728 |

+-------------------------+-----------+

root@localhost 23:58:58 [(none)]>show variables like 'innodb_log_file_size' ;

+----------------------+----------+

| Variable_name | Value |

+----------------------+----------+

| innodb_log_file_size | 50331648 |

+----------------------+----------+

root@localhost 00:01:35 [(none)]>show variables like 'innodb_flush_method';

+---------------------+-------+

| Variable_name | Value |

+---------------------+-------+

| innodb_flush_method | fsync |

+---------------------+-------+

. innodb_directories

迁移和恢复

重启时，innodb_data_home_dir, innodb_undo_directory，datadir参数自动添加到innodb_directories

迁移到新的位置，添加到innodb_directories。重启时，innodb会去扫描innodb_directories，而不参考data dictionary ,并更新data dictionary.

CREATE TABLESPACE ts3 ADD DATAFILE 'ts3.ibd' Engine=InnoDB;

use test

CREATE TABLE t31 (c1 INT PRIMARY KEY) TABLESPACE ts3 ROW_FORMAT=COMPACT;

insert into t31 select 1 ;

root@localhost 00:51:22 [test]>select file_name ,tablespace_name from information_schema.files ;

+--------------------------+------------------+

| FILE_NAME | TABLESPACE_NAME |

+--------------------------+------------------+

| ./ts3.ibd | ts3 |

+--------------------------+------------------+

./是datadir=/mysqldata/data指定的目录

在/etc/my.cnf中添加

innodb_directories=/mysqldata/data2

迁移ts3的文件到/mysqldata/data2，然后重启db

mv /mysqldata/data/ts3.ibd /mysqldata/data2/

service mysqld restart

root@localhost 00:58:12 [test]>select file_name ,tablespace_name from information_schema.files ;

+--------------------------+------------------+

| FILE_NAME | TABLESPACE_NAME |

+--------------------------+------------------+

| /mysqldata/data2/ts3.ibd | ts3 |

+--------------------------+------------------+

数据字典自动更改

use test

root@localhost 00:58:27 [test]>insert into t31 select 2 ;

Query OK, 1 row affected (0.04 sec)

Records: 1 Duplicates: 0 Warnings: 0

root@localhost 00:59:09 [test]>commit ;

迁移后生效

迁移时注意：

停服务
移表空间文件或目录
创建新目录
1. 如果是file-per-table或general tablespace 文件，添加未知目录到innodb_directories
  1. 被innodb_data_home_dir, innodb_undo_directory，datadir配置定义的目录，会自动添加到innodb_directories
  2. 单个file-per-table 表空间文件仅能移到与schema一样的目录
  3. General tablespace 文件不能移到data directory 或 data directory的子目录
2. 如果移 system tablespace files,undo tablespaces 或 data directory ,必须更新参数innodb_data_home_dir, innodb_undo_directory 和datadir 设置
重启服务

. innodb_max_dirty_pages_pct_lwm

默认值10(5.7默认0)，当脏的百分比

缓冲池中的页超过10%。启用预刷新可提高性能一致性。

. innodb_max_dirty_pages_pct 默认值90（5.7默认75）

. innodb_autoinc_lock_mode 默认2 （5.7 默认1 ）

允许并发多行插入，row-baseed replication 对sql执行顺序并不是很敏感

“Simple inserts”：插入行先前决定，eg:insert /replace

“Bulk inserts”：插入行未知,eg:insert ... select /replace ..select /load data

“Mixed-mode inserts”:insert 伴随update,分配的auto-increment值不确认会不会用。eg:insert .. on duplicate key update

innodb_autoinc_lock_mode = 0

“INSERT-like” statement：获得table-level AUTO-INC lock，制并发性和扩展性，直到语句的结束。Auto-increment value是连续的。如果多个insert 并且auto-increment是空白，在statement-based replication时是不可靠的。

innodb_autoinc_lock_mode = 1

“simple insert” statements：避免AUTO-INC table-level lock，只在auto-increment value分配时生成轻量级的mutex锁，并不需要等到语句结束才释放。

“bulk inserts” statements: AUTO-INC table-level lock ,直到语句结束。

如果源表的bulk insert操作与目标表是不同的表，源表的第一行被持shared lock后，目标表就被AUTO-INCR lock

如果源表的bulk insert操作与目标表是同的表，源表的所有行被持shared lock后，目标表才被AUTO-INCR lock

“INSERT-like”：生成的auto-incr值是连续，statement-based replication复制是安全。

“mixed-mode inserts”：生成auto-incr值会出现大于实际insert行数，过多分配的值是损失

innodb_autoinc_lock_mode = 2：

“INSERT-like” statements：没有table-level AUTO-INC lock，更好的并发性和最可扩展性的锁模式。statement-based binlog 的复制和恢复不安全。多并发时，生成的auto-inc value不连续。

“simple inserts”：不会生成gap

“mixedmode inserts”:会成生gap

“bulk inserts”:会生成gap

undo tablespace更新：

可以在线时更改undo tablspace和rollback segment,通过参数innodb_undo_tablespaces

root@localhost 01:33:08 [test]>show variables like 'innodb_undo_tablespaces' ;

+-------------------------+-------+

| Variable_name | Value |

+-------------------------+-------+

| innodb_undo_tablespaces | 4 |

+-------------------------+-------+

root@localhost 01:35:30 [test]>show variables like 'innodb_undo_directory' ;

+-----------------------+-----------------+

| Variable_name | Value |

+-----------------------+-----------------+

| innodb_undo_directory | /mysqldata/data |

+-----------------------+-----------------+

. innodb_undo_log_truncate默认开启（5.7默认关闭，工艺是开启的）

. innodb_undo_tablespaces默认2（5.7默认0，工艺是4）

.undo space（NNN) 数，由undoNNN 改为undo_NNN

. innodb_rollback_segments

每个undo tablespace的rollback segment数

（5.7 rollback segment为每个实例的总数），增加并发事务的有效rollback segment数，减少资源竞争

. innodb_undo_logs（工艺没添加，无影响）被移除，innodb_rollback_segments 可以执行同样的功能

.Undo logging支持对large object的小更新，100bytes或更小,之前版本LOB更新最小是一个large object 页

.表空间重命名

eg:alter tablespace ... rename to

root@localhost 01:45:38 [information_schema]>alter tablespace ts3 rename to ts4 ;

root@localhost 01:45:53 [information_schema]>select file_name,tablespace_name from files ;

+--------------------------+------------------+

| FILE_NAME | TABLESPACE_NAME |

+--------------------------+------------------+

| /mysqldata/data2/ts3.ibd | ts4 |

+--------------------------+------------------+

.新加INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF 视图

（space,name,path,flag,space type data）

+------------+-----------------+--------------------------+-------+------------+

+------------+-----------------+--------------------------+-------+------------+

| 6 | ts4 | /mysqldata/data2/ts3.ibd | 18432 | General |

+------------+-----------------+--------------------------+-------+------------+

SDI

Serialized Dictionary Information (SDI)提交表与表空间对像的元数据冗余，通过ibd2sdi工具可以从表空间数据文件抽取元数据

支持：

不支持：

temporary tablespaces

undo tablespaces file-per-table tablespace files (*.ibd files),

general tablespace files (*.ibdfiles),

system tablespace files (ibdata* files),

and the data dictionary tablespace (mysql.ibd)

用法：

ibd2sdi ts3.ibd

atomic ddl ，

保全整个ddl操作过程要不commit 要不rollback.

root@localhost 02:12:01 [test]>drop table t,t1,t9 ;

ERROR 1051 (42S02): Unknown table 'test.t9'

root@localhost 02:12:18 [test]>show tables ;

+----------------+

| Tables_in_test |

+----------------+

| t |

| t1 |

| t3 |

| t31 |

+----------------+

atomic ddl包含：

the data dictionary updates

storage engine operations

binary log writes

.. Supported DDL Statements:

仅innodb engine 支持 atomic ddl

...table ddl statements:

create,alter,drop for databases,tablespaces,tables and indexs and truncate tables

...non-table ddl statements:

Create and drop statement:atler for stored programs ,triggers,views, user-defined function(UDFS)

Account management statment:create ,alter,drop, rename for user/role,grant/revoke

不支持atomic ddl:

都是innodb engine

Install plugin /uninstall plugin

Install component /uninstall component

Create server ,alter server and drop server

Atomic DDL特征：

.metadata updates, binary log writes, storage engine operation, where applicable 在一个事务里

.在ddl 操作期间，没有commit的中间状态

. where applicable:

.. data dictionary、routine、event 和 UDF 的cache的状态与ddl 操作状态是一致，cache 状态反应ddl operation 是完全commit 或 rollback

.. storage engine methodss在ddl操作中并没有commit 的中间状态，作为ddl事务的一部分

.. 在ddl operation中，storage engine支持post-ddl的commit 和 rollback

.atomic ddl operation 的行为是可以视化的

注意：ddl statements ，atomic 隐式提交当前会话一个活动事务。

Changes in DDL Statement Behavior：

.drop table：要不成功删除，要不rollback

mysql> CREATE TABLE t1 (c1 INT);
mysql> DROP TABLE t1, t2;
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+

. DROP DATABASE: 要不删除所有对像成功，要不rollback。但移除database directory不是atomic事务一部分，当移除database directory 失败时，drop database 不会rollback

.删除表或库有不支持atomic ddl storage engine时，这些表会在drop事务外先被删除

.create table,alter table,rename table,truncate table ,create tablespace 和drop database 支持atomic ddl storage engine 异常终断操作要不成功，要不rollback;不支持的，在storage engine,data dictionary ,binary log 有不一致，或者留下orphan文件。

.drop views 如果有views不存在，就失改，不在有任何更改。

mysql> CREATE VIEW test.viewA AS SELECT * FROM t;
mysql> DROP VIEW test.viewA, test.viewB;
ERROR 1051 (42S02): Unknown table 'test.viewB'
mysql> SHOW FULL TABLES IN test WHERE TABLE_TYPE LIKE 'VIEW';
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| viewA | VIEW |
+----------------+------------+

.用户管理语句不允许部分成功，要不成功要不rollback

mysql> CREATE USER userA;
mysql> CREATE USER userA, userB

ERROR 1396 (HY000): Operation CREATE USER failed for 'userA'@'%'
mysql> SELECT User FROM mysql.user WHERE User LIKE 'user%';
+-------+
| User |
+-------+
| userA |
+-------+

atomic ddl实现

为了支持redo和rollback ddl的操作，innodb写ddl log 到mysql.innodb_ddl_log 表。一个隐藏的data dictinoary table 在表空间 mysql.ibd中。

为了能可视化ddl log,需要开启innodb_print_ddl_logs参数

注意：redo log 对mysql.innodb_ddl_log的更改是立即flush disk,不管innodb_flush_log_at_trx_commit的设置

ddl 执行阶段：

Prepare:创建需求对像并写ddl logs到mysql.innodb_ddl_logs. Ddl log记录ddl操作怎么前滚和回滚。

Perform:perform the ddl operation

Commit:更新数据字典和提交数据字典的事务

Post-ddl:从mysql.innodb_ddl_log中replay和移除 ddl log ，确保rollback能安全执行，不会产生不一致。例如：rename file和remove file 在最后阶段执行。这阶段也从mysql.innodb_dynamic_metadata数据字典中移除动态的元数据。例如：drop table ,truncate table 和重建表

在恢复情况，当服务器重启时，ddl事务可能commit或rollback.如果在ddl operation的commit阶段，被执行data dictionary 的事务已经存在redo log 和 binary log,这个操作被认为是成功的并前滚。否则，当innodb 重现data dictincary 的redo log时，未完成的data dictionary 事务执行回滚并且ddl operation 也执行回滚。

可视化 DDL Logs：

SET GLOBAL innodb_print_ddl_logs=1;

SET GLOBAL log_error_verbosity=3;

SET GLOBAL innodb_print_ddl_logs=1;

root@localhost 02:33:37 [test]>CREATE TABLE t112 (c1 INT) ENGINE = InnoDB;

Query OK, 0 rows affected (0.05 sec)

root@localhost 02:33:44 [test]>CREATE TABLE t112 (c1 INT) ENGINE = InnoDB;^C

root@localhost 02:35:20 [test]>drop table t112 ;

Query OK, 0 rows affected (0.13 sec)

mysql@node1:/mysqldata/logs$ tail -f /mysqldata/logs/mysql_error.log

2020-02-22T00:57:48.017012+08:00 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

2020-02-22T00:57:48.052962+08:00 0 [System] [MY-010931] [Server] /home/db/mysql/product/bin/mysqld: ready for connections. Version: '8.0.11' socket: '/home/db/mysql/product/mysql.sock' port: 13306 MySQL Community Server - GPL.

2020-02-22T02:33:44.923607+08:00 11 [Note] [MY-012473] [InnoDB] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=27, thread_id=11, space_id=8, old_file_path=./test/t112.ibd]

2020-02-22T02:33:44.923678+08:00 11 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 27

2020-02-22T02:33:44.938412+08:00 11 [Note] [MY-012477] [InnoDB] InnoDB: DDL log insert : [DDL record: REMOVE CACHE, id=28, thread_id=11, table_id=1066, new_file_path=test/t112]

2020-02-22T02:33:44.938473+08:00 11 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 28

2020-02-22T02:33:44.942871+08:00 11 [Note] [MY-012472] [InnoDB] InnoDB: DDL log insert : [DDL record: FREE, id=29, thread_id=11, space_id=8, index_id=143, page_no=4]

2020-02-22T02:33:44.942930+08:00 11 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 29

2020-02-22T02:33:44.963534+08:00 11 [Note] [MY-012485] [InnoDB] InnoDB: DDL log post ddl : begin for thread id : 11

2020-02-22T02:33:44.963574+08:00 11 [Note] [MY-012486] [InnoDB] InnoDB: DDL log post ddl : end for thread id : 11

2020-02-22T02:35:26.877389+08:00 11 [Note] [MY-012475] [InnoDB] InnoDB: DDL log insert : [DDL record: DROP, id=30, thread_id=11, table_id=1066]

2020-02-22T02:35:26.877472+08:00 11 [Note] [MY-012473] [InnoDB] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=31, thread_id=11, space_id=8, old_file_path=./test/t112.ibd]

2020-02-22T02:35:26.891965+08:00 11 [Note] [MY-012485] [InnoDB] InnoDB: DDL log post ddl : begin for thread id : 11

2020-02-22T02:35:26.892024+08:00 11 [Note] [MY-012479] [InnoDB] InnoDB: DDL log replay : [DDL record: DELETE SPACE, id=31, thread_id=11, space_id=8, old_file_path=./test/t112.ibd]

2020-02-22T02:35:26.897296+08:00 11 [Note] [MY-012479] [InnoDB] InnoDB: DDL log replay : [DDL record: DROP, id=30, thread_id=11, table_id=1066]

2020-02-22T02:35:26.902179+08:00 11 [Note] [MY-012486] [InnoDB] InnoDB: DDL log post ddl : end for thread id : 11

Redo logging 的优化：

.User threads 能并发写log buffer ，而不必同步写

.user threads 能散列地添加dirty pages 到flush list

.新加一个专有log thread 负责写log buffer 到 system buffer,刷system buffer 到disk,通知user threads关于redo的write 和 flush ,为维护散列的顺序flush list的滞后需求，和 checkpoint 时wirte

优化器使用自旋延迟（spin delay）的方式等待redo的刷新。自旋延迟有利于减少延迟。在低并发的时候，减少延迟并没有太大效果，并且在此期间避免使用来减少性能消耗。在高并发期间，您可能希望避免在自旋延迟上消耗处理能力，以便它可以用于其他工作。以下系统变量允许设置高水位值和低水位值，这些值定义了使用自旋延迟的边界。

————————————————

原文链接：https://blog.csdn.net/ciqingloveless/article/details/84101474

User thread 用spin delay（自旋延迟）等待flush redo的参数：

innodb_log_wait_for_flush_spin_hwm：定义最大平均日志刷新时间，超过该时间，用户线程在等待刷新的重做时不再旋转。默认值为400微秒。

innodb_log_spin_cpu_abs_lwm：当cpu使用量低于这个值时，flush redo的自旋待待不再等待，而是condition wait

innodb_log_spin_cpu_pct_hwm:当cpu的使用量高于这个值时，flush redo的自旋等待不再等待，而是 condition wait

https://www.jianshu.com/p/6f4254a2902e

. innodb_log_buffer_size

修改是动态的，允许服务在线更改

. ALTER TABLE操作支持ALGORITHM=INSTANT

Adding a column

Adding or dropping a virtual column

Adding or dropping a column default value

Modify column operations

Changing index options

Renaming a table

ALGORITHM=INSTANT更新仅更数据字典的元数据。表不会持有MDL,不修改表数据

Json增强

.优化器

invisible index

支持invisible index,invisible index 优化器不可用，但可以用来测试Index性能

CREATE TABLE t1 (
i INT,
j INT,
k INT,
INDEX i_idx (i) INVISIBLE
) ENGINE = InnoDB;
CREATE INDEX j_idx ON t1 (j) INVISIBLE;
ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;

ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

mysql> SELECT INDEX_NAME, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 't1';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| i_idx | YES |
| j_idx | NO |
| k_idx | NO |
+------------+------------+

desc index

支持desc index ,desc index 能向前顺序查询比反向查询效率更好

.. Common table expressions

.. Window functions e.g: RANK(), LAG()

.. Regular expression support

TempTable存储引擎

Internal temporary tables: TempTable 存储引擎替换in-memory internal 临时表的存储引擎memory。TempTable 存储对varchar和varbinary提供更有效的存储

internal_tmp_disk_storage_engine 5.7是innodb ,8.0是TempTable

Backup lock.

在线备份时，允许DML操作

改变特性：

参数transaction_isolation 替换tx_isolation, tx_isolation将被移除

参数transaction_read_only 替换tx_read_only，tx_read_only将被移除

sql_log_bin 移除global级别，只能会话级别的

5.7升级8.0：

移除表分区

更改存储引擎为innodb

表INFORMATION_SCHEMA.data_locks 替换INFORMATION_SCHEMA.INNODB_LOCKS

表INFORMATION_SCHEMA. data_lock_waits 替换INFORMATION_SCHEMA.INNODB_LOCK_WAITS

. innodb 不再支持数据文件软链接。innodb_directories实现数据文件的移动

8.0 将被删除的参数

expire_logs_days 将被删除，用binlog_expire_logs_seconds替换，两个参数同时设置，只有binlog_expire_logs_seconds参数生效。

innodb_undo_tablespaces

log_syslog

symbolic-links

8.0 被删除的参数

Slave_heartbeat_period:

Slave_last_heartbeat

Slave_received_heartbeats

innodb_checksums

innodb_file_format

innodb_large_prefix

innodb_locks_unsafe_for_binlog

innodb_support_xa

innodb_undo_logs

tx_isolation

tx_read_only

time_format

8.0新增MGR参数：

• group_replication_communication_debug_options: Added in MySQL 8.0.3.
• group_replication_flow_control_hold_percent: Added in MySQL 8.0.2.
• group_replication_flow_control_max_commit_quota: Added in MySQL 8.0.2.
• group_replication_flow_control_member_quota_percent: Added in MySQL 8.0.2.
• group_replication_flow_control_min_quota: Added in MySQL 8.0.2.
Options and Variables Introduced in MySQL 8.0
30
• group_replication_flow_control_min_recovery_quota: Added in MySQL 8.0.2.
• group_replication_flow_control_period: Added in MySQL 8.0.2.
• group_replication_flow_control_release_percent: Added in MySQL 8.0.2.
• group_replication_member_weight: Added in MySQL 8.0.2.
• group_replication_recovery_get_public_key: Added in MySQL 8.0.4.
• group_replication_recovery_public_key_path: Added in MySQL 8.0.4.
• group_replication_unreachable_majority_timeout: Added in MySQL 8.0.2.