[Day8] 两天复习完Mysql [1/2]

2025 年 3 月 4 日 Day 8

两天复习完 Mysql 冲冲冲

Mysql 基础

一条 SQL 执行的流程

我从一条 SQL 语句从 java 建立链接开始讲起; 首先 java 会通过 TCP 协议通过三次握手讲连接数据库的信息用户名密码认证信息提交 mysql 然后建立连接 Session; 上面这一步也可以通过连接池来实现复用; 然后一条数据就会被发到 mysql 中准备执行; 在 mysql 8.0 之前会先在缓存中查找, 这里的缓存是 mysql 层面的, 但是因为效率低而且 mysql 语句 key 的命中比较低所以在 8.0 后取消了 (命中效率低是指可能相同的 sql 语句因为两个字段的顺序交换了就无法被识别成一条 sql); 然后 mysql 会将语句交给解析器, 解析器会解析语句的语法、关键字登西然后生成一个解析树; 再然后解析器会将解析树交给优化器进行优化, 检查表、字段、类型等信息, 优化后会生成执行计划; 将执行计划交给执行器进行执行, 执行器会调用存储引擎执行指令; 最后将结果反应时还会做日志记录;

所以一条 SQL 的执行流程就是

建立连接

解析器处理语句生成解析树

优化器进行表、字段、类型等检查并对树优化生成执行计划

执行器会调用存储引擎执行指令

最后记录日志响应结果

什么是 buffer pool？

alt

buffer pool 的出现是为了解决内存的数据吞吐速度远远大于硬盘读写速度的中间缓冲层; 他介于执行器和硬盘之间;

buffer pool 的读写过程是怎么样的？

innodb 在读取某页数据之前会查看 buffer pool 中是否已经存在; 如果已经有了那么就直接读取; 如果不在则从硬盘中读取到 buffer pool 再进行操作；数据页的是最基本的读取单位大小是 16 KB;

读操作很好理解, 但是写操作就会复杂一点; 所有的写操作同样都会在 buffer pool 中进行操作, 那么就会出现 buffer pool 中的数据和硬盘中存储数据页不一致的问题, bufferpool 中的数据就是脏页; mysql 会开启一条后台线程不断地将数据从 buffer pool 中同步到硬盘中实现持久化; 为了确保在同步过程中 mysql 发生了奔溃等错误后依然能及时地恢复未同步的脏页; 就出现了 redo log;

完整地流程如下:

update 语句执行

在 buffer pool 中查找数据页, 不在的数据页从硬盘在加载

修改之前将原值记录在 undo log 中

更新 buffer pool 页中的值

在 redo log 中做记录; 同时后台线程将脏页刷新到硬盘中

redo log 写盘准备，记录在 bin log 日志，bin log 记录完成后 redo log 再将事务标记为已提交

alt

事务

什么是数据库事务机制？ACID 是什么? 如何实现的?

数据库事务时值一组数据库操作要么同时执行成功, 要么同时不执行; 一个事务就是数据操作的最小单元不可分割; 事务开始到事务结束中的所有数据操作就组成了事务事务有这四大特性, 也就是我们常说的 ACID A (atomicity)原子性: 指这组操作不能被分割, 执行结果只有同时成功和同时回滚; 是通过 undo log 实现的 C (Concisitency) 一致性: 一致性是其他特性和事务的共同目的; 我理解的这个一致性是 SQL 语句和数据的一致和业务逻辑和数据的一致; I (Isolation) 隔离性: 指的是在多线程多事务的场景下各个事务之间是相互隔离的, 彼此的操作不能影响到彼此的数据一致性; 实现隔离性可以通过锁和 MVCC 等机制 D (Duability) 持久性: 指的是事务一旦提交那么数据就能持久在数据库中进行保持; 具体实现是通过 redo log, 保证提交后的数据不会丢失

mysql 的事务隔离级别

事务的隔离级别指的是多个事务同时执行过程中对彼此数据的可见性和一个事务对数据的操作对其他事务的印象程度; 事务的隔离级别由高到低可以分为: 串行操作，可重复度，读已提交，读未提交 串行操作 seriazable: 同时只有一个事务可以进行操作共享数据; 一般用锁来实现, 隔离性最好但是效率最低; 不存在任何并发问题 可重复读 RR Repeatable Reads: 可重复读可以会出现幻读的问题 读已提交 RC Read comitted: 一个事务可以读取到其他线程已经提交的数据; 存在不可重复读问题和幻读问题 读未提交 RU Read Uncomitted: 一个事务可以读取到其他事务未提交的数据, 隔离最差, 会出现脏读、幻读、不可重复读

Mysql 默认的隔离级别是可重复读

什么是脏读、幻读、不可重复读？

脏读: 指一个事务读取到其他事务未提交的数据, 这种数据随时可能被回滚; 比如: 事务 A 判断一个用户是否是会员, 如果是会员就赠送积分; 事务 B 将用户状态改成了会员, 这个时候线程 A 发生读取到了这个信息, 给用户送出了积分, 但是 B 事务可能因为各种原因比如用户取消支付, 导致用户变会员的操作被撤回了; 但是 A 事务无法感知没有保证一致性

不可重复读: 指一个事务中不同位置执行相同的语句得到的结果却不同; 比如: 事务 A 是购买操作, 在购买前会先判断用户余额是否可以买得起物品, 读取用户余额为 100, 可以购买一个余额为 80 的物品; 但是在事务 A 进行购买之前事务 B 将金额改成了 0 并且将 B 事务提交这个时候 A 进行扣减时就会出现负数的情况; 这是因为其他事务的操作导致当前事务不能保证自己之前的读到的数据是否依然正确

幻读: 幻读是指同一个事务中不同位置执行同样的查询语句得到的结果数不一致; 幻读也是不可重复读的一种特殊情况; 比如: 事务 A 查询用户中女性的人数, 然后生成等量的优惠券; 读取后生成了 n 张优惠券但是在生成的过程中又有几名女性用户注册; 最终导致数据不一致

问题类型操作类型数据状态隔离级别影响解决机制

脏读	读取未提交	单行数据值变化（未提交）	Read Uncommitted → 允许	提升到 Read Committed
不可重复读	读取已提交	单行数据值变化（已提交）	Read Committed → 允许	提升到 Repeatable Read
幻读	范围操作	结果集行数变化（已提交）	Repeatable Read → 部分允许	间隙锁或 Serializable

介绍一下 MVCC

MVCC 是通过不同的事务隔离级别, 限制每个事务的数据可是范围, 实现在无锁环境下多个事务可以同时进行多谢操作, 减少锁的竞争提高并发效率; MVCC 通过隐藏列+undolog+readView 实现; 隐藏列: mysql 表中的每条记录上都有三个隐藏的字段, 如果没有主键会有默认的主键字段、一个字段是存储最后更新当前行的事务 id, 第三个字段是存储当前行上个版本在 undo log 中的地址; undo log: undo log 中存储这记录被修改的记录, 可以通过每条记录上的上版本地址和最后修改事务 id 依次回滚到自己需要的版本; ReadView: 包含一下字段当前所有活动事务 (未提交事务)的事务 id 列表；活跃事务中的最小值；未分配的事务 id 的最小值, 就是下个即将被分配的事务 id；以及当前 readView 所属的事务 id

通过三者的配合就可以实现对不同级别下事务的可是范围；比如现在记录的 DB_TRX_ID 字段>=max_trx_id: 不能读, 通过 undo log 回滚到可读版本; 如果 DB_TRX_ID < min_trx_id 可以读; （说明当前事务创建前这个线程已经提交）如果 DB_TRX_ID == create_trx_id 可以读 (说明时本事务在操作) 如果 min_trx_id =< DB_TRX_ID < create_trx_id 分情况;

如果在 m_ids 中不能读 (说明这个是当前事务创建后才提交的, 读的话会出现不可重复读问题; 如果隔离级别是 RC 那么就随便读)

如果不在 m_ids 中可以读 (说明也当前线程创建之前已经提交了)

如果隔离级别是 RC 只需要在每次操作后都刷新生成新的 ReadView 即可; 如果是 RR 那么就一直使用创建时的 ReadView 直到当前事务提交, 这样就能确保可重复读;

alt

上面图片中的关系 27 不能读继续回滚, 10 不能读 (RC 的话可以读) 继续回滚, 16 可以读;

如果数据库奔溃, 如何保证未提交事务的数据不丢失?

数据库重启后会由下面三步进行数据的恢复

扫描分析; 通过查看日志中的最后一个 checkPoint, 得到它之后的所有事务都需要进行恢复; 如果是未提交事务那么就要回滚; 如果是已提交事务那么就要进行重做;

重做阶段; 通过 Redo log 中已经提交事务的修改; 将数据恢复到 buffer pool 中继续刷盘; 因为 buffer pool 是内存空间, 奔溃后就丢失数据了;

回滚阶段: 将未提交事务做的操作都回滚到事务创建之前; 保证事务的原子性和一致性

今日算法

*******

class Solution {
    public boolean isMatch(String s, String p) {
        if (p.equals(".*")) return true;

        int sl = s.length();
        int pl = p.length();
        boolean[][] dp = new boolean[sl + 1][pl + 1];

        dp[0][0] = true;
        for (int j = 1; j < pl + 1; j++) {
            if (p.charAt(j - 1) == '*')
                dp[0][j] = dp[0][j - 2];
        }

        for (int i = 1; i < sl + 1; i++) {
            for (int j = 1; j < pl + 1; j++) {
                char sc = s.charAt(i - 1);
                char pc = p.charAt(j - 1);
                if (sc == pc || pc == '.')
                    dp[i][j] = dp[i - 1][j - 1];
                else if (pc == '*') {
                    /*匹配0次 直接跳过*/
                    dp[i][j] = dp[i][j - 2];
                    if (p.charAt(j - 2) == sc || p.charAt(j - 2) == '.')
                        dp[i][j] = dp[i][j] || dp[i - 1][j];
                }
            }
        }

        return dp[sl][pl];
    }
}

*********

class Solution {
    public List<String> letterCombinations(String digits) {
        List<String> res = new ArrayList<>();
        if (digits.length() == 0)
            return res;
        Map<Character, String> keys = new HashMap<>();
        keys.put('2', "abc");
        keys.put('3', "def");
        keys.put('4', "ghi");
        keys.put('5', "jkl");
        keys.put('6', "nmo");
        keys.put('7', "pqrs");
        keys.put('8', "tuv");
        keys.put('9', "wxyz");


        nextLevel(keys, digits, 0, res, new StringBuilder(""));
        return res;
    }

    public void nextLevel(Map<Character, String> keys, String digits, int curIndex, List<String> res, StringBuilder path) {
        if (digits.length() == curIndex) {
            res.add(path.toString());
            return;
        }

        String s = keys.get(digits.charAt(curIndex));
        for (int i = 0; i < s.length(); i++) {
            nextLevel(keys, digits, curIndex + 1, res, path.append(s.charAt(i)));
            path.deleteCharAt(path.length() - 1);
        }
    }
}

**************

class Solution {
    public ListNode removeNthFromEnd(ListNode head, int n) {
        if (head.next == null) return null;
        ListNode temp = new ListNode();
        temp.next = head;
        head = temp;
        ListNode slow = temp;
        ListNode fast = temp;
        
        for (int i = 0; i < n; i++) {
            fast = fast.next;
        }
        while (fast.next != null) {
            fast = fast.next;
            slow = slow.next;
        }
        slow.next = slow.next.next;
        return head.next;
    }
}

*****

class Solution {
    public boolean isValid(String s) {
        if (s.length() % 2 == 1) return false;

        LinkedList<Character> stack = new LinkedList<>();
        HashMap<Character, Character> map = new HashMap<>();
        map.put(']', '[');
        map.put('}', '{');
        map.put(')', '(');
        Set<Character> right = map.keySet();

        for (int i = 0; i < s.length(); i++) {
            char cur = s.charAt(i);
            if (right.contains(cur)) {
//                判断是否能和栈中最后一个节点匹配
                if (stack.size() == 0 || !map.get(cur).equals(stack.getLast()))
                    return false;
                stack.removeLast();
            } else {
//                左括号 直接入栈
                stack.addLast(cur);
            }
        }

        return stack.size() == 0;
    }
}

********

class Solution {
    public ListNode mergeTwoLists(ListNode list1, ListNode list2) {
        ListNode res = new ListNode();
        ListNode cur = res;
        ListNode l1 = list1;
        ListNode l2 = list2;
        
        while (l1 != null && l2 != null) {
            if (l1.val < l2.val) {
                cur.next = l1;
                l1 = l1.next;
            } else {
                cur.next = l2;
                l2 = l2.next;
            }
            cur = cur.next;
        }
        while (l1 != null){
            cur.next = l1;
            l1 = l1.next;
            cur = cur.next;
        }
        while (l2 != null){
            cur.next = l2;
            l2 = l2.next;
            cur = cur.next;
        }
        return res.next;
    }
}

#java##面试#