1. InnoDB事务的ACID如何保证

1.0 概念

image.png

1.1 redo功能

1.1.1 redo简介

重做日志，记录数据页的变化，存储在ib_logfile0，ib_logfile1，

• 保证ACID中的“D”持久化功能，对于“AC”也有相应的保证。
• 加快了commit命令的速度，提高了事务的并发。
• 并且实现了，在MySQL Crash(异常宕机)时，ACSR中前滚的功能。

1.2 undo功能

1.2.1 undo 简介

回滚日志，5.7版本存储在ibdata1,8.0版本 undo单独存储。

• 记录逆操作
• 保证了ACID中“A”的特性，“CI”也有相应的功能

1.3 隔离级别和锁机制(I的特性)

1.3.1 功能

主要提供了ACID中的"I"隔离性的功能，C的功能受到一定影响。

1.3.2 隔离级别介绍

>>查询隔离级别
select @@transaction_isolation;
select @@tx_isolation;
show variables like '%tx%';

>>设置默认隔离级别
vim /etc/my.cnf
transaction_isolation=REPEATABLE-READ                    RR(默认模式)
transaction_isolation=READ-COMMITTED                     RC

(1) RU：读未提交

• 脏读，读取脏页数据
• 违反了不可重复读
• 幻读

>>测试：
--- 修改mysql配置文件
vim /etc/my.cnf
autocommit=0                             修改默认不自动提交
transaction_isolation=READ-UNCOMMITTED   修改隔离级别为RU
>>PC1：
mysql[world]>begin;
Query OK, 0 rows affected (0.00 sec)

mysql[world]>update city set population=100 where id=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

>>PC2:
mysql[world]>select * from city where id=10;
+----+---------+-------------+---------------+------------+
| ID | Name    | CountryCode | District      | Population |
+----+---------+-------------+---------------+------------+
| 10 | Tilburg | NLD         | Noord-Brabant |        100 |
+----+---------+-------------+---------------+------------+
1 row in set (0.01 sec)

(2) RC：读已提交

• 防止脏读
• 会出现，不可重复读
• 会出现，幻读

(3) RR：可重复读

• 防止脏读
• 防止不可重复读(快照<MVCC>)
• 可能会出现幻读(GAP间隙锁，next-lock下一键锁)

(4) SR：可串行化

1.3.3 InnoDB的锁（主要解决）

record lock 记录锁，行级锁
gap
next-lock

2. InnoDB 核心参数介绍

2.1 默认存储引擎

>>查看默认存储引擎命令
mysql[world]>select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

percona : XtraDB (默认)
MariaDB: InnoDB (默认 myrocks tokudb)

2.2 innodb_buffer_pool_size(缓冲区大小)

>>查看数据缓冲区大小
mysql[world]>select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 134217728 |
+---------------------------+
1 row in set (0.00 sec)

>>如何设置
innodb_buffer_pooll_size=2G
官方建议：最多物理内存的95%
生产建议：不超过80%，50-70%

>>查看内存是否够用的命令
mysql[world]>show engine innodb status\G
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992           内存总大小   
Dictionary memory allocated 126822
Buffer pool size   8191                          总共数据页
Free buffers       7734                          剩余的数据页
Database pages     457                           使用数据页
select 8192*16*1024;
<1. 业务够用
2. 公司硬件有预留
3. MySQL还有可能额外使用到其他内存结构
4. 还可以做多实例>

2.3 innodb_log_buffer_size(redo log 缓冲区大小)

select @@innodb_log_buffer_size;
show variables like '%log_buffer%';
>>修改方法vim /etc/my.cnf

2.4 redo的参数

mysql[world]>show variables like '%innodb_log_file%';
+---------------------------+----------+
| Variable_name             | Value    |
+---------------------------+----------+
| innodb_log_file_size      | 50331648 |
| innodb_log_files_in_group | 2        |
+---------------------------+----------+
2 rows in set (0.00 sec)
>>修改方法vim /etc/my.cnf

2.5 relog的刷写策略("双一")

mysql[world]>select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)
>>默认：
    1.在事务提交时，立即刷写redo buffer中日志到OS buffer，立即FSYNC到磁盘.
    0.每秒，刷写redo buffer中日志到OS buffer，每秒FSYNC到磁盘.
    2.在事务提交时，立即刷写redo buffer中日志到OS buffer，每秒FSYNC到磁盘

2.6 innodb_flush_method

mysql[world]>select @@innodb_flush_method;
+-----------------------+
| @@innodb_flush_method |
+-----------------------+
| NULL                  |
+-----------------------+
1 row in set (0.00 sec)
>>参数作用：控住buffer pool和redo buffer的刷写策略
fsync(默认值)：buffer pool和redo buffer先写入到OS buffer，在写入到磁盘.
o_direct：buffer pool直接写入到磁盘，redo buffer走OS buffer(性能最好).
o_dsync：buffer pool走OS buffer，redo buffer直接写入到磁盘.