施磊

05日志

2025年3月24日 19:14 周一
学习
施磊, MySQL高级

undolog #

作用 #

  1. 恢复某条记录原始状态
  2. 记录修改过程，MVCC的原理，结合事务id知道哪些数据可见 undo log的修改本身会被记录到redo log中。即使undo log未刷盘，崩溃恢复时也可以通过redo log重建undo log。

redolog #

  1. 数据持久性–事务提交后，buffer pool一些脏页没有写入数据库磁盘文件。重启时，利用redolog恢复(表空间、页号、偏移量、数值)的数据（磁盘数据）
  2. 由于redolog有几种策略时机刷入磁盘。另有额外线程每隔1s不断刷入redolog buffer pool数据到redolog磁盘日志文件中，如果事务未提交但是刷入了redolog日志文件也无妨。可以根据一些标识，找到哪个事务是未提交的，然后再用undolog恢复原始状态。
root@db211:/var/lib/mysql# ls
auto.cnf	 ibdata1	     public_key.pem
ca-key.pem	 ib_logfile0 (redolog)	     server-cert.pem
ca.pem		 ib_logfile1 (redolog)		 server-key.pem
client-cert.pem  ibtmp1		     sys
client-key.pem	 mysql		     test
db211-slow.log	 performance_schema  xx
ib_buffer_pool 	 private_key.pem

update语句执行流程 #

流程 #

  1. 事务开始
    1. 写Undo Log到Undo Log Buffer（记录旧值）
    2. 修改Buffer Pool中的数据页（生成脏页）
    3. 写Redo Log到Redo Log Buffer（记录物理变更） 修改BufferPool后，还会MySQL还会生成对应的Binlog 事件逻辑操作记录），先写入线程的私有内存缓冲区（Binlog Cache）
  2. 事务提交
    1. Redo Log 标记为 Prepare
    2. 写Binlog并刷盘
    3. Redo Log Commit阶段（标记提交）

redolog的刷盘策略 #

  1. Redo Log Buffer 空间不足：当 Redo Log Buffer 的写入速度超过刷盘速度时，InnoDB 会强制刷盘以释放空间。
  2. 后台线程定期刷盘：InnoDB 的后台线程（如 log_writer 和 log_flusher）会周期性刷盘（默认每秒一次，由 innodb_flush_log_at_timeout 控制）。
  3. 参数配置触发
    1. 0：设置为 0 的时候，表示每次事务提交时不进行刷盘操作。这种方式性能最高，但是也最不安全，因为如果 MySQL 挂了或宕机了，可能会丢失最近 1 秒内的事务。
    2. 1：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作。这种方式性能最低，但是也最安全，因为只要事务提交成功，redo log 记录就一定在磁盘里，不会有任何数据丢失。
    3. 2：设置为 2 的时候，表示每次事务提交时都只把 log buffer 里的 redo log 内容写入 page cache（文件系统缓存）。page cache 是专门用来缓存文件的，这里被缓存的文件就是 redo log 文件。这种方式的性能和安全性都介于前两者中间。

这里其实有个疑问，就是redolog并不是事务提交后才刷盘的，而是很有可能事务提交前就刷盘了。如果提交前刷盘了，之后系统宕机了，那么redolog磁盘文件就多出了一些未提交事务的日志。解决办法：可以通过一些属性，在undolog中找到未提交事务的id，然后通过undolog回滚未提交事务。

...

04事务

2025年3月21日 16:28 周五
学习
施磊, MySQL高级

关闭自动提交 #

在MySQL命令行的默认设置下，事务都是自动提交的，即执行SQL语句后就会马上执行COMMIT操作。因此开始一个事务，必须使用BEGIN、START TRANSACTION，(显示开启事务）或者执行SET AUTOCOMMIT=0，以禁用当前会话的自动提交。

  1. select语句一般用来输出用户变量，比如select @变量名，用于输出数据源不是表格的数据。
  2. 系统变量在变量名前面有两个@
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
# 修改变量
set autocommit=0;

本文中autocommit=0，开始事务需要使用begin显式开启

事务并发存在的问题 #

没有脏读问题，如果两个事务同时修改一条数据，那么另一个事务的修改操作会被阻塞
【隔离性】脏读（读到了还没有提交的数据）

【隔离性】不可重复读（同一事务中，第1次读的数据和第2次读的数据不一样。读到了事务操作过程中其他事务提交的数据，也不应该，这个没法保证一致性。比如我第一次根据某个事务做了一个操作，第二次想同样的逻辑做一个判断操作，但是数据变了，导致同一个事务中对同一个(其实不是了）数据做的操作居然不一致

【隔离性】虚读、幻读（同一事务中：我第一次读到某一条数据，但是第二次没读到；或者第一次没读到，第二次居然读到了。条数问题）

事务的隔离级别 #

READ_UNCOMMITTED 读未提交
READ_COMMITTED 读已提交
REPEATABLE_READ 重复读 SERIALIZABLE 串行化(行锁) 修改事务隔离级别：

set tx_isolation='SERIALIZABLE'; (可以加global，让新开的session也使用该事务隔离级别)

经测试，如果有三个session（mysql客户端），其中一个(a)是SERIALIZABLE,而其他两个(b,c)是READ_UNCOMMITTED，那么客户端b,c之间不会因为锁挂起，而ab或ac会导致(行)锁挂起

REPEATABLE_READ重复读 #

REPEATABLE_READ重复读的隔离级别只能在一定程度上防止幻读 如果事务a(插入一条新数据，或者删除一条数据)后提交。那么变动的数据不会在事务b被查出来。但是如果插入的这条数据(在事务b，注意 不是事务a)被修改了，那么之后它会在事务b被查出来

新增后修改 #

事务a新增数据后提交，在事务b执行中修改了该数据，则该数据会显示出来（影响了）

...

03存储引擎

2025年3月19日 09:52 周三
学习
施磊, MySQL高级

表结构、数据、索引

文件目录 #

 root@db211:/var/lib/mysql/mysql# ls | tail -16
tables_priv.frm
tables_priv.MYD
tables_priv.MYI
time_zone.frm
time_zone.ibd
time_zone_leap_second.frm
time_zone_leap_second.ibd
time_zone_name.frm
time_zone_name.ibd
time_zone_transition.frm
time_zone_transition.ibd
time_zone_transition_type.frm   #INNODB存储引擎-表结构
time_zone_transition_type.ibd   #INNODB存储引擎-表数据+表索引（包括所有索引）
user.frm   #MyISAM存储引擎-表结构
user.MYD   #MyISAM存储引擎-表数据
user.MYI   #MyISAM存储引擎-表索引

INNODE存储引擎的表主键聚簇索引和数据在同一个文件（所以即使没有设置主键，innodb也会为每一行自动生成一个默认的隐藏主键列，用来形成B+树）

索引 #

索引创建 #

建表时创建 #

create table index1( id int, name varchar(20), index idx_id_name (id,name));

后续添加 #

create index idx_name on bank_bill (name);

B树 #

m阶B树 ,m个分支（或者说m个区间）
x个元素就会有x+1个区间

B+树和B树区别 #

从B树考虑 #

log2 2000万≈24.3 log500 2000万≈3 (m阶平衡树) 如果是500阶B树，那么2000万个节点最多只需要3层

从B+树考虑 #

MySQL中，一个节点1页，也就是16KB（可以改），而一个节点可以放（主键bigint(8字节)+页号地址 6字节)单元1170个，假设是1000，那两层可以放100 0000万个节点。由于1个节点16KB，如果一条数据用1KB的话，那么最底层每个叶子节点就是放16条数据。那就是3层可以放1600万条数据。这是估算，细算的话应该是2000万左右

...

02增删改查

2025年3月16日 17:04 周日
学习
施磊, MySQL高级

分类 #

  • DDL 数据定义语言(create,drop,alter)
  • DML 数据操纵语言(insert,delete,update,select)
  • DCL 数据控制语言(grant,revoke)

删除 #

mysql> select * from user;
+----+----------+-----+-----+
| id | name     | age | sex |
+----+----------+-----+-----+
|  1 | zhangsan |  20 | M   |
|  2 | wuqi     |  34 | M   |
|  5 | baobaoi  |  45 | W   |
|  6 | lalal    |  44 | W   |
+----+----------+-----+-----+
4 rows in set (0.00 sec)
mysql> delete from user where id = 6
#如果之后重启了mysql（且id最大为5，则再插入的数据id为6），（如果id最大为10，则插入的数据id为11）；如果没有重启，则再插入的数据id为7
mysql> nsert into user(name,age,sex) values('hah',35,'W');

新增 #

一次新增多条数据和多次新增单条数据区别

搜索 #

limit可以优化sql语句，不过要看数据的位置

mysql> select * from t_user  limit 1999999 ,1;
+---------+------------------+------------+
| id      | email            | password   |
+---------+------------------+------------+
| 2000000 | cwng78@yahoo.com | h4HcxZKBNQ |
+---------+------------------+------------+
1 row in set (0.52 sec)
#这条数据在最后一条，所以优化效果不怎么样
mysql> select * from t_user where email='cwng78@yahoo.com';
+---------+------------------+------------+
| id      | email            | password   |
+---------+------------------+------------+
| 2000000 | cwng78@yahoo.com | h4HcxZKBNQ |
+---------+------------------+------------+
1 row in set (0.47 sec)

在第一条或者前面，则优化效果显著

...

01基础知识

2025年3月15日 19:46 周六
学习
施磊, MySQL高级

本课程大纲 #

部分扩展知识 #

MySQL分为服务端和客户端（两进程），SQLite是进程程序，客户端和服务端都在同一个进程操作数据

安装(windows) #

安装目录下包括数据目录(Data/)和配置文件(my.ini)

安装(debian) #

数据目录 #

root@db211:/etc/mysql# ls -l /var/lib/mysql | awk '{print $1,$3,$4,$9}'
total   
-rw-r----- mysql mysql auto.cnf
-rw------- mysql mysql ca-key.pem
-rw-r--r-- mysql mysql ca.pem
-rw-r--r-- mysql mysql client-cert.pem
-rw------- mysql mysql client-key.pem
-rw-r----- mysql mysql ib_buffer_pool
-rw-r----- mysql mysql ibdata1
-rw-r----- mysql mysql ib_logfile0
-rw-r----- mysql mysql ib_logfile1
-rw-r----- mysql mysql ibtmp1
drwxr-x--- mysql mysql mysql
drwxr-x--- mysql mysql performance_schema
-rw------- mysql mysql private_key.pem
-rw-r--r-- mysql mysql public_key.pem
-rw-r--r-- mysql mysql server-cert.pem
-rw------- mysql mysql server-key.pem
drwxr-x--- mysql mysql sys
drwxr-x--- mysql mysql xx

配置目录 #

root@db211:/etc/mysql# ls -l | awk '{print $1,$9,$10,$11}'
total   
drwxr-xr-x conf.d  
lrwxrwxrwx my.cnf -> /etc/alternatives/my.cnf
-rw-r--r-- my.cnf.fallback  
-rw-r--r-- mysql.cnf  
drwxr-xr-x mysql.conf.d

再进一步查看

...