暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 玩转MySQL8.0新特性课程笔记-第6章 Innodb增强

玩转MySQL8.0新特性课程笔记-第6章 Innodb增强

Tonyhacks 2023-06-27
262

6-1 集成数据字典

查看mysql5.7mysql库的数据文件

[root@MySQL57 mysql]# ls
columns_priv.frm   help_relation.ibd       slave_master_info.frm
columns_priv.MYD   help_topic.frm          slave_master_info.ibd
columns_priv.MYI   help_topic.ibd          slave_relay_log_info.frm
db.frm             innodb_index_stats.frm  slave_relay_log_info.ibd
db.MYD             innodb_index_stats.ibd  slave_worker_info.frm
db.MYI             innodb_table_stats.frm  slave_worker_info.ibd
db.opt             innodb_table_stats.ibd  slow_log.CSM
engine_cost.frm    ndb_binlog_index.frm    slow_log.CSV
engine_cost.ibd    ndb_binlog_index.MYD    slow_log.frm
event.frm          ndb_binlog_index.MYI    tables_priv.frm
event.MYD          plugin.frm              tables_priv.MYD
event.MYI          plugin.ibd              tables_priv.MYI

mysql8.0数据字典全部采用InnoDB存储引擎
查看mysql8.0mysql库的数据文件

[root@MySQL8 mysql]# ls
general_log_213.sdi  general_log.CSV   slow_log.CSM
general_log.CSM      slow_log_214.sdi  slow_log.CSV

数据字典全部存储在mysql.ibd里
image.png

  • MySQL 8.0删除了之前版本的元数据文件，例如 .frm，.opt 等。
  • 将系统表(mysql)和数据字典表全部改为lnnoDB 存储引擎。
  • 支持原子 DDL语句。
  • 简化了INFORMATION SCHEMA的实现，提高了访问性能。
  • 提供了序列化字典信息 (SDI) 的支持，以及ibd2sdi 工具。
  • 数据字典使用上的差异，例如innodb read only 影响所有的存储引擎;数据字典表不可见，不能直接查询和修改。
[root@MySQL8 tonydb]# ibd2sdi emp.ibd >emp.sdi
[root@MySQL8 tonydb]# more emp.sdi 
["ibd2sdi"
,
{
        "type": 1,
        "id": 394,
        "object":
                {
    "mysqld_version_id": 80031,
    "dd_version": 80023,
    "sdi_version": 80019,
    "dd_object_type": "Table",
    "dd_object": {
        "name": "emp",
        "mysql_version_id": 80031,
        "created": 20230612070406,
        "last_altered": 20230612070406,
        "hidden": 1,
        "options": "avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=
0;stats_auto_recalc=0;stats_sample_pages=0;",
        "columns": [
            {
                "name": "emp_no",
                "type": 4,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,

6-2 原子DDL操作

  • MySQL 8.0 开始支持原子 DDL 操作，其中与表相关的原子 DDL 只支持InnoDB存储引擎。
  • 一个原子 DDL 操作内容包括: 更新数据字典，存储引擎层的操作在 binlog 中记录DDL操作。
  • 支持与表相关的 DDL:数据库、表空间、表、索的 CREATEALTER、DROP以及 TRUNCATE TABLE。
  • 支持的其他 DDL:存储程序、触发器、视图、UDF的 CREATEDROP以及ALTER 语句。
  • 支持账户管理相关的 DDL:用户和角色的CREATE、ALTERDROP 以及适用的 RENAME，以及 GRANT 和 REVOKE 语句。

在MySQL5.7中同时删除两张表，一张表不存在报错，但是另外一张表还是删除了。

mysql5.7>select count(*) from t1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql5.7>select count(*) from t888;
ERROR 1146 (42S02): Table 'test.t888' doesn't exist
mysql5.7>drop table t1,t888;
ERROR 1051 (42S02): Unknown table 'test.t888'
mysql5.7>select count(*) from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql5.7>

而在MySQL8.0中同时删除两张表，一张表不存在报错，另外一张表也不会被删除。要成功都成功要失败都失败，这就是DDL的原子性。

mysql8.0>select count(*) from t1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.37 sec)

mysql8.0>select count(*) from t888;
ERROR 1146 (42S02): Table 'tonydb.t888' doesn't exist
mysql8.0>drop table t1,t888;
ERROR 1051 (42S02): Unknown table 'tonydb.t888'
mysql8.0>select count(*) from t1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

通过增加if exists语句和使用和MySQL5.7一样的效果，删除t1表。

mysql8.0>drop table if exists t1,t888;
Query OK, 0 rows affected, 1 warning (0.16 sec)

mysql8.0>show warnings;
+-------+------+-----------------------------+
| Level | Code | Message                     |
+-------+------+-----------------------------+
| Note  | 1051 | Unknown table 'tonydb.t888' |
+-------+------+-----------------------------+
1 row in set (0.00 sec)

mysql8.0>select count(*) from t1;
ERROR 1146 (42S02): Table 'tonydb.t1' doesn't exist
mysql8.0>

6-3 自增列持久化

在MySQL5.7中执行如下测试

mysql5.7>select * from t1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙少安    |
|  2 | 孙少平    |
|  3 | 田润叶    |
+----+-----------+
3 rows in set (0.00 sec)

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

mysql5.7>select * from t1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙少安    |
|  2 | 孙少平    |
+----+-----------+
2 rows in set (0.00 sec)

正常重启MySQL后

mysql5.7>use test;
Database changed
mysql5.7>select * from t1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙少安    |
|  2 | 孙少平    |
+----+-----------+
2 rows in set (0.01 sec)

mysql5.7>insert into t1 (name) values ('田晓霞');
Query OK, 1 row affected (0.00 sec)

mysql5.7>select * from t1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙少安    |
|  2 | 孙少平    |
|  3 | 田晓霞    |
+----+-----------+
3 rows in set (0.00 sec)

可见3这个id在数据库重启后还是继续使用。
在MySQL8.0中执行如下测试

mysql8.0>select * from t1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙少安    |
|  2 | 孙少平    |
|  3 | 田润叶    |
+----+-----------+
3 rows in set (0.00 sec)

mysql8.0>delete from t1 where id=3;
Query OK, 1 row affected (0.02 sec)

mysql8.0>select * from t1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙少安    |
|  2 | 孙少平    |
+----+-----------+
2 rows in set (0.00 sec)

正常重启MySQL数据库

mysql8.0>select * from t1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙少安    |
|  2 | 孙少平    |
+----+-----------+
2 rows in set (0.00 sec)

mysql8.0>insert into t1 (name) values ('田晓霞');
Query OK, 1 row affected (0.00 sec)

mysql8.0>select * from t1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙少安    |
|  2 | 孙少平    |
|  4 | 田晓霞    |
+----+-----------+
3 rows in set (0.00 sec)

可见3这个id在数据库重启后已不再使用。

继续MySQL5.7上做测试，

mysql5.7>select * from t1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙少安    |
|  2 | 孙少平    |
|  3 | 田晓霞    |
+----+-----------+
3 rows in set (0.00 sec)

mysql5.7>update t1 set id=5 where name='孙少安';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql5.7>select * from t1;
+----+-----------+
| id | name      |
+----+-----------+
|  2 | 孙少平    |
|  3 | 田晓霞    |
|  5 | 孙少安    |
+----+-----------+
3 rows in set (0.00 sec)
mysql5.7>insert into t1 (name) values ('孙玉厚');
Query OK, 1 row affected (0.00 sec)

mysql5.7>select * from t1;
+----+-----------+
| id | name      |
+----+-----------+
|  2 | 孙少平    |
|  3 | 田晓霞    |
|  4 | 孙玉厚    |
|  5 | 孙少安    |
+----+-----------+
4 rows in set (0.00 sec)

mysql5.7>insert into t1 (name) values ('田福堂');
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

oops！尴尬了插不进数据了。
在mysql8.0上做测试

mysql8.0>select * from t1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙少安    |
|  2 | 孙少平    |
|  4 | 田晓霞    |
+----+-----------+
3 rows in set (0.00 sec)

mysql8.0>update t1 set id=6 where name='孙少安';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql8.0>select * from t1;
+----+-----------+
| id | name      |
+----+-----------+
|  2 | 孙少平    |
|  4 | 田晓霞    |
|  6 | 孙少安    |
+----+-----------+
3 rows in set (0.00 sec)

mysql8.0>insert into t1 (name) values ('孙玉厚');
Query OK, 1 row affected (0.01 sec)

mysql8.0>select * from t1;
+----+-----------+
| id | name      |
+----+-----------+
|  2 | 孙少平    |
|  4 | 田晓霞    |
|  6 | 孙少安    |
|  7 | 孙玉厚    |
+----+-----------+
4 rows in set (0.00 sec)

mysql8.0>insert into t1 (name) values ('田福堂');
Query OK, 1 row affected (0.00 sec)

mysql8.0>select * from t1;
+----+-----------+
| id | name      |
+----+-----------+
|  2 | 孙少平    |
|  4 | 田晓霞    |
|  6 | 孙少安    |
|  7 | 孙玉厚    |
|  8 | 田福堂    |
+----+-----------+
5 rows in set (0.00 sec)

MySQL8.0测试通过，数据插入没问题。

  1. innodb_autoinc_lock_mode=0：表示使用旧的锁定方式。在插入新记录时，会锁定整个自增主键索引，保证生成唯一的自增值。这种锁定方式可以防止并发插入导致自增值冲突，但可能影响并发性能。
  2. innodb_autoinc_lock_mode=1：表示使用更宽松的锁定方式。在插入新记录时，只会锁定少数几行，而不是整个自增主键索引。这种方式提高了并发性能，但也增加了稍微可能存在自增值冲突的风险。
  3. innodb_autoinc_lock_mode=2：表示使用严格的锁定方式。在插入新记录时，会锁定自增主键的最大值，以确保生成唯一的自增值。这种方式可以避免自增值冲突，但可能对并发性能产生较大的影响。

选择合适的innodb_autoinc_lock_mode取决于应用程序的需求。如果并发性能是首要考虑因素，可以选择使用更宽松的锁定方式（1），但需要注意可能存在的自增冲突。如果数据一致性和唯一性是更重要的考虑因素，可以选择使用严格的锁定方式（2），但需要承担并发性能下降的风险。

MySQL5.7默认值是1 MySQL8.0默认值是2
复制基于语句的话用 innodb_autoinc_lock_mode=1，复制基于行的话用 innodb_autoinc_lock_mode=2

6-4 死锁检测控制

什么是死锁
死锁是指两个或多个事务在执行过程中，因争夺资源而造成的一种互相等待的现象。当多个事务并发执行时，如果每个事务都占用着其他事务需要的资源，那么就会导致死锁问题。死锁问题会导致数据库性能下降，甚至出现错误。

死锁产生的四个必要条件

  1. 互斥条件：资源只能被一个事务占用，其他事务需要等待释放。
  2. 不可抢占条件：已被分配的资源不能被强制性地抢占。
  3. 占有和等待条件：事务已经持有一个资源，并等待获取其他事务占有的资源。
  4. 循环等待条件：存在一个事务链，每个事务都在等待下一个事务所持有的资源。

MySQL8.0(MySQL 5.7.15) 增加了一个新的动态变量，用于控制系统是否执行InnoDB 死锁检查innodb deadlock detect
对于高并发的系统，禁用死锁检查可能带来性能的提高。

mysql8.0>show variables like "%innodb_deadlock_detect%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+
1 row in set (0.00 sec)

mysql8.0>create table t(i int);
Query OK, 0 rows affected (1.13 sec)

mysql8.0>insert into t values(1);
Query OK, 1 row affected (0.07 sec)

mysql8.0>start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql8.0>select * from t where i=1 for share;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql8.0>

mysql8.0>select * from t;
+------+
| i    |
+------+
|    2 |
|    3 |
+------+
2 rows in set (0.00 sec)

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

mysql8.0>select * from t where i=2 for share;
+------+
| i    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql8.0>delete from t where i=3;
Query OK, 1 row affected (7.24 sec)

mysql8.0>

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

mysql8.0>select * from t where i=3 for share;
+------+
| i    |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

mysql8.0>
mysql8.0>
mysql8.0>
mysql8.0>delete from t where i=2;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql8.0>

当关闭死锁检测 innodb_deadlock_detect=off ，当出现死锁时其中一个事务不会报错立即结束，而是会根据innodb_lock_wait_timeout的值进行锁等待，等待超时然后报错。所以关闭死锁检测要慎重。

mysql8.0>show variables like "innodb_lock_wait_timeout";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

6-5 锁定语句选项

SELECT … FOR SHARE和 SELECT …FOR UPDATE 中支持NOWAIT、SKIP LOCKED 选项
对于 NOWAIT，如果请求的行被其他事务锁定时，语句立即返回
对于 SKIP LOCKED，从返回的结果集中移除被锁定的行。

业务应用场景：查看剩余车票

mysql8.0>create table t (i int primary key);
Query OK, 0 rows affected (0.19 sec)

mysql8.0>insert into t values (1),(2),(3);
Query OK, 3 rows affected (0.14 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

mysql8.0>update t set i=0 where i=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql8.0>begin;

mysql8.0>select * from t where i=1 for update;


^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql8.0>select * from t where i=1 for update nowait;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

mysql8.0>select * from t where i=1 for update SKIP LOCKED;
Empty set (0.00 sec)

mysql8.0>select * from t for update  SKIP LOCKED;
+---+
| i |
+---+
| 2 |
| 3 |
+---+
2 rows in set (0.00 sec)

注意事项
NOWAIT、SKIP LOCKED 选项针对行级锁，主从语句级别的复制会造成不一致。

6-6 其他改进功能

支持部分快速 DDL，ALTER TABLE …ALGORITHM=INSTANT；
InnoDB 临时表使用共享的临时表空间ibtmp1。
新增静态变量innodb dedicated server，自动配置InnoDB 内存参数:innodb_buffer_pool size/innodb log file size等。
新增表INFORMATION SCHEMAINNODB CACHED INDEXES显示每个索引缓存在InnoDB缓冲池中的索引页数。
新增视图INFORMATION SCHEMAINNODB TABLESPACES BRIEF，为InnoDB 表空间提供相关元数据信息。
默认创建2个UNDO表空间，不再使用系统表空间。
支持ALTER TABLESPACE … RENAME TO 重命名通用表空间支持使用innodb directories 选项在服务器停止时将表空间文件移动到新的位置。
InnoDB表空间加密特性支持重做日志和撤销日志。

墨力计划mysql8.0新特性innodb增强
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

玩转MySQL8.0新特性课程笔记（共8篇）
玩转MySQL8.0新特性课程笔记

评论