Draymonders commented 2 years ago

数据库

[x] 锁
[x] insert buffer & change buffer
- 索引是辅助索引（二级索引），索引不是 unique 的
[x] 索引
[x] Log
- slow log，二进制文件，记录查询较慢的sql，借助 mysqldumpslow 分析，MySQL本身的文件
- binlog，逻辑日志，记录数据库执行更新的sql，供salve复制数据，MySQL本身的文件
- redolog，物理日志，WAL日志，保证持久性，InnoDB特有
- undo log，逻辑日志，记录事务执行过程中的相反操作（insert 变为 delete），（update 变为反向update）
[x] 事务
[ ] 数据库相关的问题及处理
[ ] 查询优化
[ ] 提升下sql能力

读了《高性能MySQL(第3版) 》第一章。但不是讲解实现的。

转向《MySQL技术内幕 InnoDB存储引擎第2版》

-> MVCC （多版本并发控制）提升了并发性能，可以认为是行级锁的一种变种，但在很多情况下避免了加锁，因此开销更低。

Draymonders commented 2 years ago

InnoDB

docker-compose --file docker-compose.yml up -d 启动mysql容器

docker-compose.yml文件如下，volumes里面挂载了两个目录，一个是配置~/data/mysql/conf/my.cnf，一个是数据目录~/data/mysql/data，方便在宿主机看到数据

version: "3"

services:
  mysql:
    image: mysql:5.7
    command: --default-authentication-plugin=mysql_native_password --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    restart: always
    ports:
      - 3306:3306
    environment:
      MYSQL_ROOT_PASSWORD: root
    volumes:
      - ~/data/mysql/conf/my.cnf:/etc/mysql/my.cnf
      - ~/data/mysql/data:/var/lib/mysql

mysql -u root -p root 连接到mysql

查看innodb引擎情况

> show engine INNODB STATUS\G

***************************[ 1. row ]***************************
Type   | InnoDB
Name   |
Status |
=====================================
2022-06-04 14:51:57 0x7faf007f8700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 53 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2 srv_active, 0 srv_shutdown, 267 srv_idle
srv_master_thread log flush and writes: 269
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2
OS WAIT ARRAY INFO: signal count 2
RW-shared spins 0, rounds 4, OS waits 2
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 4.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 1795
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421865085982560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:

bufferPoolSize

> show variables like 'innodb_buffer_pool_size'\G
(END)
***************************[ 1. row ]***************************
Variable_name | innodb_buffer_pool_size
Value         | 134217728     => 128MB

查看mysql相关配置

cd / 
find . -name "*mysql*" | grep "log"

或者用 mysqlCli 查询
mysqlCli> show variables like '%datadir%'\G

查询得知，mysql容器相关的文件存放在 /var/lib/mysql

Draymonders commented 2 years ago

Binlog

为了取得更好的读写性能，InnoDB会将数据缓存在内存中（InnoDB Buffer Pool），对磁盘数据的修改也会落后于内存，这时如果进程或机器崩溃，会导致内存数据丢失，为了保证数据库本身的一致性和持久性，InnoDB维护了REDO LOG。修改Page之前需要先将修改的内容记录到REDO中，并保证REDO LOG早于对应的Page落盘，也就是常说的WAL，Write Ahead Log。当故障发生导致内存数据丢失后，InnoDB会在重启时，通过重放REDO，将Page恢复到崩溃前的状态。

开启binlog需要在my.cnf配置

[mysqld]
log_bin=mysql-bin
server-id=1

查看状态

> show master status\G

***************************[ 1. row ]***************************
File              | mysql-bin.000003
Position          | 856
Binlog_Do_DB      |
Binlog_Ignore_DB  |
Executed_Gtid_Set |

查看binlog格式

> select @@session.binlog_format;
+-------------------------+
| @@session.binlog_format |
+-------------------------+
| ROW                     |
+-------------------------+

建表并初始化数据

CREATE TABLE `t_sample` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

insert into t_sample(name) values("bing"),("draymonder");

更新数据

update t_sample set name ='amor' where id=2;

使用mysql自带的binlog查看，用cat不行，因为binlog是二进制文件

> mysqlbinlog --start-position=856 mysql-bin.000003 -v

# at 993
#220605  6:16:18 server id 1  end_log_pos 1047 CRC32 0x62752495     Table_map: `amor`.`t_sample` mapped to number 110
# at 1047
#220605  6:16:18 server id 1  end_log_pos 1117 CRC32 0xdcaf7236     Update_rows: table id 110 flags: STMT_END_F

BINLOG '
MkqcYhMBAAAANgAAABcEAAAAAG4AAAAAAAEABGFtb3IACHRfc2FtcGxlAAIIDwJAAACVJHVi
MkqcYh8BAAAARgAAAF0EAAAAAG4AAAAAAAEAAgAC///8AgAAAAAAAAAKZHJheW1vbmRlcvwCAAAA
AAAAAARhbW9yNnKv3A==
'/*!*/;
### UPDATE `amor`.`t_sample`
### WHERE
###   @1=2
###   @2='draymonder'
### SET
###   @1=2
###   @2='amor'
# at 1117
#220605  6:16:18 server id 1  end_log_pos 1148 CRC32 0x24efb3f3     Xid = 42
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

删除数据

delete from t_sample where name='amor';

> mysqlbinlog --start-position=856 mysql-bin.000003 -v

# at 1285
#220605  6:27:50 server id 1  end_log_pos 1339 CRC32 0x5567ded4     Table_map: `amor`.`t_sample` mapped to number 110
# at 1339
#220605  6:27:50 server id 1  end_log_pos 1388 CRC32 0x07ba59c7     Delete_rows: table id 110 flags: STMT_END_F

BINLOG '
5kycYhMBAAAANgAAADsFAAAAAG4AAAAAAAEABGFtb3IACHRfc2FtcGxlAAIIDwJAAADU3mdV
5kycYiABAAAAMQAAAGwFAAAAAG4AAAAAAAEAAgAC//wCAAAAAAAAAARhbW9yx1m6Bw==
'/*!*/;
### DELETE FROM `amor`.`t_sample`
### WHERE
###   @1=2
###   @2='amor'
# at 1388
#220605  6:27:50 server id 1  end_log_pos 1419 CRC32 0x79c74b7b     Xid = 59
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

binlog有三种格式STATEMENT，ROW，MIX

Draymonders commented 2 years ago

Binlog

逻辑日志，用于主从同步

开启binlog需要在my.cnf配置

[mysqld]
log_bin=mysql-bin
server-id=1

查看状态

> show master status\G

***************************[ 1. row ]***************************
File              | mysql-bin.000003
Position          | 856
Binlog_Do_DB      |
Binlog_Ignore_DB  |
Executed_Gtid_Set |

查看binlog格式

> select @@session.binlog_format;
+-------------------------+
| @@session.binlog_format |
+-------------------------+
| ROW                     |
+-------------------------+

建表并初始化数据

CREATE TABLE `t_sample` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

insert into t_sample(name) values("bing"),("draymonder");

更新数据

update t_sample set name ='amor' where id=2;

使用mysql自带的binlog查看，用cat不行，因为binlog是二进制文件

> mysqlbinlog --start-position=856 mysql-bin.000003 -v

# at 993
#220605  6:16:18 server id 1  end_log_pos 1047 CRC32 0x62752495     Table_map: `amor`.`t_sample` mapped to number 110
# at 1047
#220605  6:16:18 server id 1  end_log_pos 1117 CRC32 0xdcaf7236     Update_rows: table id 110 flags: STMT_END_F

BINLOG '
MkqcYhMBAAAANgAAABcEAAAAAG4AAAAAAAEABGFtb3IACHRfc2FtcGxlAAIIDwJAAACVJHVi
MkqcYh8BAAAARgAAAF0EAAAAAG4AAAAAAAEAAgAC///8AgAAAAAAAAAKZHJheW1vbmRlcvwCAAAA
AAAAAARhbW9yNnKv3A==
'/*!*/;
### UPDATE `amor`.`t_sample`
### WHERE
###   @1=2
###   @2='draymonder'
### SET
###   @1=2
###   @2='amor'
# at 1117
#220605  6:16:18 server id 1  end_log_pos 1148 CRC32 0x24efb3f3     Xid = 42
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

删除数据

delete from t_sample where name='amor';

> mysqlbinlog --start-position=856 mysql-bin.000003 -v

# at 1285
#220605  6:27:50 server id 1  end_log_pos 1339 CRC32 0x5567ded4     Table_map: `amor`.`t_sample` mapped to number 110
# at 1339
#220605  6:27:50 server id 1  end_log_pos 1388 CRC32 0x07ba59c7     Delete_rows: table id 110 flags: STMT_END_F

BINLOG '
5kycYhMBAAAANgAAADsFAAAAAG4AAAAAAAEABGFtb3IACHRfc2FtcGxlAAIIDwJAAADU3mdV
5kycYiABAAAAMQAAAGwFAAAAAG4AAAAAAAEAAgAC//wCAAAAAAAAAARhbW9yx1m6Bw==
'/*!*/;
### DELETE FROM `amor`.`t_sample`
### WHERE
###   @1=2
###   @2='amor'
# at 1388
#220605  6:27:50 server id 1  end_log_pos 1419 CRC32 0x79c74b7b     Xid = 59
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

binlog有三种格式STATEMENT，ROW，MIX

RedoLog

为了取得更好的读写性能，InnoDB会将数据缓存在内存中（InnoDB Buffer Pool），对磁盘数据的修改也会落后于内存，这时如果进程或机器崩溃，会导致内存数据丢失，为了保证数据库本身的一致性和持久性，InnoDB维护了REDO LOG。修改Page之前需要先将修改的内容记录到REDO中，并保证REDO LOG早于对应的Page落盘，也就是常说的WAL，Write Ahead Log。当故障发生导致内存数据丢失后，InnoDB会在重启时，通过重放REDO，将Page恢复到崩溃前的状态。

root@8a83121d6e85:/var/lib/mysql# ls -lsh | grep "ib"
4.0K -rw-r----- 1 mysql mysql 1.4K Jun  5 06:05 ib_buffer_pool
 48M -rw-r----- 1 mysql mysql  48M Jun  5 06:29 ib_logfile0
 48M -rw-r----- 1 mysql mysql  48M Jun  5 06:05 ib_logfile1
 76M -rw-r----- 1 mysql mysql  76M Jun  5 06:29 ibdata1
 12M -rw-r----- 1 mysql mysql  12M Jun  5 06:26 ibtmp1

innodb 存储引擎至少有1个重做日志文件组（group），每个group至少有2个文件，如默认的ib_logfile0和ib_logfile1，InnoDB先写ib_logfile0，写满了后，切换到ib_logfile1，再写满后，再继续写ib_logfile0

show variables like 'innodb%log%'\G

***************************[ 8. row ]***************************
Variable_name | innodb_log_file_size
Value         | 50331648    => 48MB
***************************[ 9. row ]***************************
Variable_name | innodb_log_files_in_group
Value         | 2                    => 2个

> show variables like '%flush%'\G

***************************[ 7. row ]***************************
Variable_name | innodb_flush_log_at_trx_commit
Value         | 1           =>  每提交一个事务，就刷盘 redo log buffer 到 log文件里

> show engine innodb status\G;  （插入大量的数据）
---
LOG
---
Log sequence number 127664222
Log flushed up to   120711324
Pages flushed up to 70622487
Last checkpoint at  62890079

log sequence number: 代表当前的重做日志redo log(in buffer)在内存中的LSN log flushed up to: 代表刷到redo log file on disk中的LSN pages flushed up to: 代表已经刷到磁盘数据页上的LSN last checkpoint at: 代表上一次检查点所在位置的LSN

log sequence number >= log flushed up to >= pages flushed up to >= last checkpoint at

大概格式

（Page ID，Record Offset，(Filed 1, Value 1) … (Filed i, Value i) … )

其中，PageID指定要操作的Page页，Record Offset记录了Record在Page内的偏移位置，后面的Field数组，记录了需要修改的Field以及修改后的Value。

redo log 大小设置

设置过大，恢复需要花很长时间。
设置过小，一个事务可能被记录在了不同的 redo log子log上。设置的太小会导致频繁的 async checkpoint，频繁刷盘

redo log、bin log 区别

binlog是mysql有的，和存储引擎无关，redo log是 InnoDB特有的。
binlog记录的逻辑日志，redo log记录的物理日志（关于每个页的更改的物理情况）
redo log是InnoDB实现的wal日志。二进制日志只在每次事务提交的时候一次性写入缓存中的日志文件，redo log一直会记录。
为了保证存储引擎层和上层二进制日志的一致性，二者之间使用了 两阶段事务

redo log 补充

InnoDB中通过min-transaction实现，简称mtr，需要原子操作时，调用mtr_start生成一个mtr，mtr中会维护一个动态增长的m_log，这是一个动态分配的内存空间，将这个原子操作需要写的所有REDO先写到这个m_log中，当原子操作结束后，调用mtr_commit将m_log中的数据拷贝到InnoDB的Log Buffer。

Undo Log

逻辑日志，记录事务执行过程中的相反操作（insert 变为 delete），（update 变为反向update）
实现MVCC的非锁定读。读快照的能力

Draymonders commented 2 years ago

Buffer Pool

github是真滴强，随手一搜，就能搜到一些简单的实现

按照 file -> shard -> buffer page的结构，shard内部对page进行缓存管理，采用LRU策略。
每个shard最多有 pgsize 个page，如果page满了，会触发lru，如果清理的page是dirty的，则落盘

https://github.com/Draymonders/bfile

Draymonders commented 2 years ago

InnoDB中的锁

行锁

共享锁（S Lock）：允许事务读一行数据
排它锁（X Lock）：允许事务删除 or 更新数据

意向锁：对一行进行加锁，需要对数据库、表、页加粗粒度的锁

InnoDB实现锁的算法

Record lock、Gap lock、Next-key lock

参考阅读

一致性读

一致性非锁定读

在 READ COMMITED 事务隔离级别下，对于快照数据，非一致性读总是读取最新的行数据版本。
在 REPEATABLE READ 事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

查看隔离级别

select @@tx_isolation
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

只读，不加锁

-- 
事务1
begin;
select id, name from amor.t_sample where id = 100;

=>
+-----+------+
| id  | name |
+-----+------+
| 100 | amor |
+-----+------+
---
事务2
begin;
update amor.t_sample set name = 'tr 2' where id = 100;
commit;
--- 
事务1
select id, name from amor.t_sample where id = 100;

=> 
+-----+------+
| id  | name |
+-----+------+
| 100 | amor |
+-----+------+
commit;
---

一致性锁定读

S锁 lock in share mode


-- 
事务1
begin;
select id, name from amor.t_sample where id = 100 lock in share mode;

=> +-----+------+ | id | name | +-----+------+ | 100 | amor | +-----+------+

事务2 begin; update amor.t_sample set name = 'tr 3' where id = 100; commit; => 因为事务1加了读锁，所以这里是阻塞的，等事务1 commit/rollback 才能完成事务2的commit

事务1 select id, name from amor.t_sample where id = 100;

=> +-----+------+ | id | name | +-----+------+ | 100 | amor | +-----+------+ commit;


- X锁 `for update`
```sql
-- 
事务1
begin;
select id, name from amor.t_sample where id = 100 for update;

=>
+-----+------+
| id  | name |
+-----+------+
| 100 | tr 3 |
+-----+------+
---
事务2
begin;
select id, name from amor.t_sample where id = 100;  => 这里是用的非锁定读，可以正常读数据

=> 
+-----+------+
| id  | name |
+-----+------+
| 100 | tr 3 |
+-----+------+

select id, name from amor.t_sample where id = 100 lock in share mode;  因为事务1加了X锁，所以这里是阻塞的，等事务1 commit/rollback 才能完成这里的查询

update amor.t_sample set name = 'tr 4' where id = 100;
commit;
--- 
事务1
select id, name from amor.t_sample where id = 100;

=> 
+-----+------+
| id  | name |
+-----+------+
| 100 | tr 3 |
+-----+------+
commit;
---

Draymonders commented 2 years ago

事务

事务实现

redolog 保证事务原子性和持久性
undolog 保证事务一致性

Draymonders commented 2 years ago

踩坑 & 处理

上游无限重试，重复执行update语句，锁等待严重导致数据库排队现象严重，数据库最终OOM

场景是

faas -> 请求算法服务返回相似商品 -> 相似商品召回送审

触发背景

faas执行失败会无限重试，直到成功
算法服务返回相似商品过多，1000个，请求算法服务的时间+相似召回送审的时间超过faas的设置时间，稳定超时，因此触发了无限重试
在送审阶段，有一个更新语句如下，有热点行的异步更新，该表有不到1000条数据，因为一直异步更新，一直在申请连接池的连接，开事务进行更新，最终导致DB OOM

> 异步执行的语句
update t_dimension_daily_count set count = count + 1 
where daily_time = ? and stype = ? and sub_type = ?

> 数据库条数
select count(1) from t_dimension_daily_count;
count(1) = 942

报错从 1205 变成了 1105

Error 1205: Lock wait timeout exceeded; try restarting transcation

Error 1105: get master conn error，Error 1040: Too many connections

插入数据过大

str: field varchar(5)

insert into checkVarcharLimit(`id`,`str`) values(10, '244444');

ERROR 1406 (22001): Data too long for column 'str' at row 1

查询超时

背景

该店铺是top店铺，送审了很多次
detail表有好几个大字段（LongText类型），使用select *会拿所有的数据，查询超时

解决方案最终选择的是 select id, shop_id, task_id，速度很快

索引是这个 KEY `idx_shop_id` (`shop_id`)

select * from t_access_shop_audit_detail where shop_id = 22713444 and commit_type = -70 limit 0, 1;   // 执行超时

SQL分析

explain select * from t_access_shop_audit_detail where shop_id = 22713444 and commit_type = -70 limit 0, 1;

key 是使用了 idx_shop_id，走了索引，但是执行还是会超时

数据库事务使用不当

背景

数据库事务里面执行了数据更新，然后发了个消息到mq，最后事务commit。
消费者消费消息，读master节点，还是老数据

解决方法：先commit事务，再发消息

Draymonders / bingo

数据库 #3

数据库

InnoDB

Binlog

Binlog

RedoLog

redo log 大小设置

redo log、bin log 区别

redo log 补充

相关文章

Undo Log

Buffer Pool

InnoDB中的锁

InnoDB实现锁的算法

一致性读

一致性非锁定读

一致性锁定读

=> +-----+------+ | id | name | +-----+------+ | 100 | amor | +-----+------+

=> +-----+------+ | id | name | +-----+------+ | 100 | amor | +-----+------+ commit;

事务

事务实现

踩坑 & 处理

上游无限重试，重复执行update语句，锁等待严重导致数据库排队现象严重，数据库最终OOM

插入数据过大

查询超时

数据库事务使用不当