Databases

三大核心日志

MySQL 任意一条写SQL（不包括读SQL）的执行都会记录三个核心日志：undo-log、redo-log、bin-log。

Undo Log（撤销日志）

作用：记录SQL操作的撤销日志，用于事务回滚和MVCC
存储机制：
- 使用共享Buffer Pool中的undo_log_buffer缓冲区
- 事务写数据前，先将旧数据拷贝到Undo Log。行数据的隐藏字段roll_ptr回滚指针指向Undo Log中的旧数据。
特性：
- 属于"数据页"，受Redo Log保护，即写入 Undo Log 也会记录 Redo Log。
- InnoDB引擎特有

Redo Log（重做日志）

作用：记录数据页的物理变化，用于崩溃恢复
存储机制：记录的是“数据页的物理变化”，例如记录"表空间X、页面Y、偏移Z处的值从A改为B"
刷盘控制：
- 参数innodb_flush_log_at_trx_commit控制刷盘时机
- 默认值1：每次提交事务时刷盘
特性：InnoDB引擎特有

Bin Log（二进制日志）

作用：记录SQL操作日志，用于主从复制和数据恢复
存储机制：
- 使用位于线程的工作内存中的缓冲区bin_log_buffer。
- 追加存储模式，支持保留所有 bin-log。
格式类型：
- STATEMENT：记录SQL语句（默认）
- ROW：记录行数据变化
- MIXED：混合模式
刷盘控制：
- 参数sync_binlog控制刷盘时机
- 默认值1：每次提交事务时刷盘
特性：MySQL Server级别，所有引擎可用

崩溃恢复机制

两阶段提交与崩溃恢复

如果redo-log只写一次，那不管redo-log和 bin-log 谁先写，都有可能造成主从同步数据时的不一致问题出现。

MySQL数据类型隐式转换

隐式转换是 MySQL 在比较或操作不同数据类型时自动进行的类型转换，主要发生在比较运算、算术运算和函数处理等场景。

规则

两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
两个参数都是字符串，会按照字符串来比较，不做类型转换
两个参数都是整数，按照整数来比较，不做类型转换
十六进制的值和非数字做比较时，会被当做二进制串
有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
所有其他情况下，两个参数都会被转换为浮点数再进行比较

MySQL 的隐式转换虽然方便，但容易导致：

基本配置信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- 切换数据库
USE test;

-- 查询数据库版本
SELECT VERSION();

-- 查询当前数据库
SELECT DATABASE();

-- 查询当前用户
SELECT USER();

-- 查询对象名称大小写敏感性
-- 0 Linux 敏感
-- 1 Windows 不敏感
-- 2 Mac
SHOW VARIABLES LIKE '%lower_case_table_names%';
SELECT @@SESSION.lower_case_table_names;

-- 查询字符集与排序规则
SHOW VARIABLES LIKE '%character%';
SHOW VARIABLES LIKE '%collation%';

-- 查询严格模式
SHOW VARIABLES LIKE '%innodb_strict_mode%';

-- 查询 SQL 模式
SHOW VARIABLES LIKE '%sql_mode%';
SHOW VARIABLES LIKE '%mode%';
SELECT @@SESSION.sql_mode;

-- 默认存储引擎
SHOW VARIABLES LIKE 'default_storage_engine';
-- InnoDB 状态
SHOW ENGINE INNODB STATUS;

-- 系统时区
SHOW VARIABLES LIKE 'system_time_zone';
-- 当前时区
SHOW VARIABLES LIKE 'time_zone';

事务与锁相关

事务

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 查询自动提交模式
SHOW VARIABLES LIKE '%autocommit%';
SELECT @@SESSION.autocommit;

-- 查询事务 ID
SELECT trx_id FROM information_schema.innodb_trx WHERE trx_mysql_thread_id = CONNECTION_ID();

-- 查看全局和会话隔离级别
SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;

-- 查看所有支持的隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';

-- 查看隔离级别相关统计
SELECT * FROM performance_schema.events_transactions_summary_global_by_event_name;

-- InnoDB 重做日志(Redo Log)刷新控制
-- 0: 每秒写入并刷新日志到磁盘(性能最好，安全性最低)
-- 1: 每次事务提交都写入并刷新日志(默认值，最安全，性能较差)
-- 2: 每次事务提交写入日志，但每秒刷新一次到磁盘(折中方案)
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit%';

-- 二进制日志(Binlog)刷新控制
-- 0: 依赖操作系统刷新(性能最好，安全性最低)
-- 1: 每次事务提交都同步(默认值，最安全)
-- N: 每N次事务提交同步一次
SHOW VARIABLES LIKE 'sync_binlog%';

锁检测与死锁

锁超时

结论

存储过程（Stored Procedure）与存储函数（Stored Function）在事务控制方面有较大差异。

对于存储过程，如果自动提交是开启的，并且你没有在存储过程中显式地控制事务，那么每条独立的 SQL 语句执行完毕后都会立即提交。

SELECT语句执行顺序

WITH (CTE, COMMON TABLE EXPRESSIONS)
FROM
ON
JOIN
WHERE
GROUP BY
HAVING
SELECT, WINDOW FUNCTION
SELECT DISTINCT
UNION, INTERSECT, EXCEPT
ORDER BY
LIMIT, OFFSET
FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE

Window functions are permitted only in the select list and ORDER BY clause. Query result rows are determined from the FROM clause, after WHERE, GROUP BY, and HAVING processing, and windowing execution occurs before ORDER BY, LIMIT, and SELECT DISTINCT.

Gdev Blog

MySQL-知识概览