MySQL8.0新特性(三)InnoDB增强

1. 自增变量持久化

在MySQL8.0之前的版本中，自增列计数器置于内存中，并不会持久化到磁盘，当数据库重启后，会根据select max(id)+1 from xxx重新计算当前自增列值，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。自增主键重启重置的问题很早就被发现Innodb autoincrement stats 重启时丢失。在MySQL8.0版本中对AUTO_INCREMENT值进行持久化，每次值更改时，当前最大自动增量计数器值将写入重做日志，并保存到每个检查点上的引擎专用系统表中，这些更改使当前最大自动增量计数器值在服务器重新启动时保持不变。

举个栗子：

5.7版本的情况：

mysql> create table t1(id int auto_increment primary key);
Query OK, 0 rows affected (0.17 sec)

mysql> insert into t1 values(null),(null),(null);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

mysql> delete from t1 where id = 3;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  4 |
+----+
3 rows in set (0.00 sec)

mysql> delete from t1 where id = 4;
Query OK, 1 row affected (0.00 sec)

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

#重新启动数据库之后查看
mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.01 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

可以看到，新插入的null值分配的是3，按照重启前的操作逻辑，此处应该是5。这就是自增主键没有持久化的bug。究其原因，在于自增主键的分配，是由InnoDB数据字典内部一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会通过下面这种方式初始化：select max(id)+1 from xxx。

8.0版本：

mysql> create table t1(id int auto_increment primary key);
Query OK, 0 rows affected (0.13 sec)

mysql> insert into t1 values(null),(null),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.01 sec)

mysql> delete from t1 where id = 3;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  4 |
+----+
3 rows in set (0.00 sec)

mysql> delete from t1 where id = 4;
Query OK, 1 row affected (0.00 sec)

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

#重启MySQL数据库：
root@MYSQL:[DB((none))]> use dkf;
Database changed

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  5 |
+----+
3 rows in set (0.00 sec)

同样的逻辑，在8.0版本中自增计数器的值是没有问题的。

2. 禁用死锁检测

在MySQL8.0中引入了新的动态变量innodb_deadlock_detect用来禁用死锁检测。在高并发系统上，当多线程同时在等待同一个锁时，死锁检测的速度会变慢。有的时候，禁用死锁检测可能更加高效，并且在innodb_lock_wait_timeout发生死锁时，依赖于事务回滚的设置。该参数的默认值为ON，即打开死锁检测。

会话A:

mysql> set global innodb_deadlock_detect=off;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'innodb_lock_wait_timeout';

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 10    |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE id = 1 FOR SHARE;
Empty set (0.10 sec)

mysql> SELECT * FROM t1 WHERE id = 1 FOR SHARE;

+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> DELETE FROM t1 WHERE id = 1;

Query OK, 1 row affected (0.00 sec)

会话B：

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t1 WHERE id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

会话B会等待超时时间之后再报错退出，如果是没有启用死锁检查机制innodb_deadlock_detect，则会直接报错如下：

mysql> DELETE FROM t1 WHERE id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql>

通常来说，应该启用死锁检测，并且在应用程序中尽量避免产生死锁，同时对死锁进行相应的处理，例如重新开始事务。

3. 跳过锁等待

InnoDB支持NOWAIT和SKIP LOCKED选项SELECT ... FOR SHARE以及SELECT ... FOR UPDATE锁定读取语句。

NOWAIT如果请求的行被另一个事务锁定，则会导致语句立即返回。
SKIP LOCKED从结果集中删除锁定的行。

select ... for update，select ... for share(8.0新增语法) 添加NOWAIT、SKIP LOCKED语法，跳过锁等待，或者跳过锁定。而之前的版本中，如果获取不到锁，会一直等待，直到innodb_lock_wait_timeout超时时间。

会话A：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id = 5 for update;

+----+
| id |
+----+
|  5 |
+----+

1 row in set (0.00 sec)

会话B：

mysql> select * from t1 where id = 5 for update nowait;
ERROR 3572 : Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

会话B在获取不到锁的情况下会立即报错退出，不再等待。

4. 系统表全部换成InnoDB表

mysql系统表和数据字典表现在在MySQL数据目录中InnoDB命名的单个表空间文件中创建mysql.ibd，并且系统表全部换成事务型的innodb表，默认的MySQL实例将不包含任何MyISAM表，除非手动创建MyISAM表。以前的版本中，这些表是InnoDB在mysql数据库目录中的各个表空间文件中创建的。

5.7版本

mysql> select count(1) from information_schema.tables where engine='MyISAM' and TABLE_SCHEMA='mysql';

+----------+
| count(1) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

8.0版本：

mysql> select count(1) from information_schema.tables where engine='MyISAM' and TABLE_SCHEMA='mysql';

+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

5. 新增innodb_dedicated_server参数

MySQL8.0中通过新增参数innodb_dedicated_server，能够让InnoDB根据服务器上自动检测到的主机内存大小，自动配置innodb_buffer_pool_size，innodb_log_file_size，innodb_flush_method三个参数值，适用于在专用服务器上运行的MySQL服务器实例。

1. innodb_buffer_pool_size 自动调整规则 ：

专用服务器内存大小	buffer_pool_size大小
小于1G	128MB（MySQL缺省值）
1G to 4G	OS内存*0.5
大于4G	OS内存*0.75

2. innodb_log_file_size 自动调整规则 ：

buffer_pool_size大小	log_file_size 大小
小于8G	512MB
8G to 128G	1024MB
大于128G	2048MB

3. innodb_flush_method 自动调整规则 ：

该参数调整规则直接引用官方文档的解释：

The flush method is set to O_DIRECT_NO_FSYNC when innodb_dedicated_server is enabled. If the O_DIRECT_NO_FSYNC setting is not available, the default innodb_flush_method setting is used.

如果系统允许设置为O_DIRECT_NO_FSYNC；如果系统不允许，则设置为InnoDB默认的Flush method。

6. DDL原子化操作

该InnoDB存储引擎现在支持原子DDL，这保证了DDL操作要么完全提交或回滚，即使服务器在操作时停止。

5.7版本：

mysql> show tables;
+---------------+
| Tables_in_dkf |
+---------------+
| z             |
+---------------+
1 rows in set (0.00 sec)

mysql> drop table z, zz;
ERROR 1051 (42S02): Unknown table 'dkf.zz'
mysql> show tables;
Empty set (0.00 sec)

8.0版本：

mysql> drop table z, zz;
ERROR 1051 (42S02): Unknown table 'dkf.zz'
mysql> show tables;
+---------------+
| Tables_in_dkf |
+---------------+
| z             |
+---------------+
1 rows in set (0.00 sec)

7. 默认字符集由latin1变为utf8mb4

默认字符集已从更改latin1为utf8mb4，并且utf8mb4字符集新增了几个新的排序规则，包括utf8mb4_ja_0900_as_cs，用于Unicode的第一个日语特定排序规则。

8. undo空间自动回收

innodb_undo_log_truncate参数在8.0.2版本默认值由OFF变为ON，默认开启undo日志表空间自动回收。innodb_undo_tablespaces参数在8.0.2版本默认为2，当一个undo表空间被回收时，还有另外一个提供正常服务。innodb_max_undo_log_size参数定义了undo表空间回收的最大值，当undo表空间超过这个值，该表空间被标记为可回收。

9. redo&undo日志加密

该InnoDB表空间加密功能支持重做日志的加密和撤消日志数据。从MySQL 8.0.13开始，静态InnoDB数据加密功能支持通用表空间。以前，只能对每个表的文件表空间进行加密。通过增加以下两个参数，用于控制redo、undo日志的加密。

innodb_undo_log_encrypt
innodb_undo_log_truncate

总结

自从ORACLE收购了MySQL之后，MySQL数据库的功能越来越向ORACLE数据库的各种强大功能靠近了，这对于使用MySQL数据库的用户来说越来越可靠了，也希望MySQL越来越强大。

参考文章

InnoDB增强

posted @ 2022-11-18 15:49 夏尔_717 阅读(565) 评论(0) 收藏举报

刷新页面返回顶部

ciel_717

MySQL8.0新特性(三)InnoDB增强

1. 自增变量持久化

2. 禁用死锁检测

3. 跳过锁等待

4. 系统表全部换成InnoDB表

5. 新增innodb_dedicated_server参数

6. DDL原子化操作

7. 默认字符集由latin1变为utf8mb4

8. undo空间自动回收

9. redo&undo日志加密

总结

参考文章

公告