MySQL存储引擎笔记day08
locak tables city read; 🔒
unlock table; (open)🔓
select * from city
InnoDB事务的ACID如何保证？
1.0一些概念
redo log:重做日志,记录数据页的变化，ib_logfile0,ib_logfile1 🌟🌟🌟🌟🌟
redo log buffer :redo log的缓冲区（内存区） 🌟🌟🌟🌟🌟
ibd：表空间的数据文件，以段区页 🌟🌟🌟🌟🌟
buffer pool:数据页缓冲区 🌟🌟🌟🌟🌟
LSN: Log seq num 日志序列号,redo log\logbuffer\ibd\buffer pool
WAL:write ahead log 日志优先写磁盘
脏页:dirty page 在内存中被修改的数据页
CKPT: checkpoint,将内存脏页回写到磁盘的动作
TXID: transaction_id,事务ID,伴随着事务的整个生命周期.
undo log: 回滚日志,ibdata1
1.1 redo功能
1.1.1.1 redo简介
重做日志,ib_logfile0,ib_logfile1
1.1.2
记录数据页的变化
1.1.3
主要 保证ACID中的"D"，持久化功能，对于AC也有相应的保证
加快了commit命令的速度，提高了事务的并发。
并且实现了在mysql crash时，ACSR中前滚的功能。
Undo Log的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。
Redo Log- 原理
和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，
不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。
1.2 undo功能
1.2.1 undo简介
回滚日志5.7,存储在ibdata1,8.0 undo单独存储。
1.2.2 undo记录的内容。
记录的是逆操作。
1.2.3 作用
主要保证了A的特性,CI也有相应的功能。
1.3 隔离级别和锁机制
1.3.1 功能
主要提供了ACID中的“I”的隔离性的功能，C的功能受到一定影响。
1.3.2 隔离级别介绍
RU:读未提交
脏读
不可重复读
幻读:开两个窗口A和B,A开启begin模式,A窗口新建一个表，然后插入一条语句，把其中一项的所有值修改掉，然后切换B窗口,B窗口执行插入一条在A窗口中执行外的语句，然后查看，发现新插入的数据已提交成功，再切换A窗口commit提交，查看数据，发现修改的只是之前A窗口设定的条件，而B窗口新添加的语句并未执行。
transaction_isolation
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)
mysql> show variables like "%tx%";
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
| tx_read_only | OFF |
+---------------+-----------------+
2 rows in set (0.01 sec)
设置
vim /etc/my.cnf
tranaction_isolation=REPETABLE-READ
transaction_isolation=READ-COMMITTED
RC : 读已提交
transaction_isolation=READ-COMMITTED
A:
mysql -uroot -p
use world
1. oldguo[world]>begin;
2. oldguo[world]>update city set population=1000 where id=10;
4. oldguo[world]>commit;
B:
mysql -uroot -p
use world
3. select * from city where id=10;
5. select * from city where id=10;
防止脏读.
会出现,不可重复读.
会出现,幻读.
A:
create table t1 (id int ,name varchar(20));
insert into t1 values(1,'a'),(2,'b'),(4,'c'),(6,'d');
commit;
B:
(3) RR : 可重复读
防止脏读
防止不可重复读 (MVCC,一致性快照读)
可能会出现幻读 (GAP ,next-lock)
(4) SR : 可串行化
1.3.3 Innodb的锁（主要解决I）
record lock 记录锁 行级锁
AB两个窗口同时执行一个修改数据命令，A窗口不提交，B窗口执行语句后会造成锁等待现象。一直提交一直不释放。
2.InnoDB核心参数介绍
2.1 默认存储引擎
percona: XtraDB 默认
mariaDB:InnoDB 默认 ，my rocks tokudb
2.2 innodb_buffer_pool_size
mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
mysql> show variables like '%buffer_pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+
10 rows in set (0.01 sec)
设置
vim /etc/my.cnf
innodb_buffer_pool_size=2G
官方建议:最多95%
生产建议:不超过80%,50-70%
1.业务够用的
2.公司硬件有预留
3.MySQL还有可能额外使用到其他的内存结构
4.公司做的多实例
查看缓存
show engine innodb status\G;
2.3 redo innodb log_buffe_sizer
mysql> show variables like '%log_buffer%';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.01 sec)
2.4 redo的参数 🌟 🌟 🌟 🌟 🌟
mysql> 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)
2.5 redo log的刷写策略（双一） 🌟 🌟 🌟 🌟 🌟🌟
mysql> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.01 sec)
默认:
1⃣️.在事务提交时，立即刷写redo buffer中的日志到OS buffer,立即FSYNC到磁盘。
0⃣️.每秒 刷写redo buffer中的日志到OS buffer,每秒FSYNC到磁盘。
2⃣️.在每个事务提交时,立即刷写redo buffer中日志到OS buffer，每秒FSYNC到磁盘。
The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
2.6 Innodb_flush_method
查询方法
控制buffer pool,和redo buffer的刷盘策略。
mysql> select @@innodb_flush_method;
+-----------------------+
| @@innodb_flush_method |
+-----------------------+
| O_DIRECT |
+-----------------------+
默认:
O_DIRECT :数据缓冲区写磁盘,不走OS buffer
fsync :日志和数据缓冲区写磁盘,都走OS buffer
O_DSYNC :日志缓冲区写磁盘,不走 OS buffer
最高安全模式
innodb_flush_log_at_trx_commit=1
Innodb_flush_method=O_DIRECT
最高性能:
innodb_flush_log_at_trx_commit=0
Innodb_flush_method=fsync
