MySQL

  • MySQL

    redo log 和 binlog 的区别

    • redo log 是 InnoDB 特有的, binlog 是 Server 层的每个数据引擎都有
    • redo log 记录的是物理日志, 记录的是在某个数据页做的修改, 比如”对 xxx 表中 yyy 数据页 zzz 偏移量的地方坐了 aaa 更新” binlog 是逻辑日志, 记录的是这个语句的原始逻辑, 比如”给 id = 2 的字段 c 加 1”
    • redo log 是循环写的, 空间会用完, binlog 是追加写的

    聚簇索引和二级索引

    • InnoDB 使用了 B+ 树索引模型
    • 主键索引的叶子节点存储的是整行数据, 在 InnoDB 中, 主键索引也被称为聚簇索引
    • 非主键索引的叶子节点存储的是主键值, 在 InnoDB 中, 非主键索引也被称为二级索引

    为什么用 B+ 树做索引

    什么样的数据结构是好的索引?

    • MySQL 是持久化到磁盘的, 因此当我们通过索引查找某行数据的时候, 需要先从磁盘读取索引到内存, 再通过索引从磁盘中找到某行数据, 然后读入内存, 也就是说查询的过程中会发生多次 IO 操作, IO 操作次数越多, 性能损耗越大
    • MySQL 是支持范围查找的, 所以索引数据不仅能高效查找某一个记录, 而且也要能高效执行范围查找
    • 所以一个合适的索引要满足
      • 尽可能少的进行 IO 操作
      • 能高效查找某一个记录, 也能高效进行范围查找

    各个数据结构对比

    • 顺序结构, 可以用二分来查找, 速度快, 但是插入性能太低
    • 二叉搜索树, 不支持范围查找, 而且有退化成链表的风险
    • 平衡二叉树, 不支持范围查找, 并且由于是二叉树, 随着元素增多, 树的高度变高, 磁盘 IO 次数增加
    • B 树, B 树可以是多叉树, 但是每个节点都存储索引+数据, 而数据的大小可能远远超过索引大小
    • B+树, 叶子结点才会存放实际数据, 非叶子节点只会存放索引, 叶子节点构成一个有序链表

    InnoDB 中的 B+ 树

    • 叶子节点用双向链表进行连接, 既能向左遍历, 也能向右遍历
    • 叶子节点内容是数据页, 数据页存放了数据以及各种信息, 每个数据页默认大小 16KB

    什么是两阶段锁

    • 在 InnoDB 事务中, 行锁是在需要的时候加上的, 但并不是不需要了就立即释放, 而是等到事务结束才释放

    快照读和当前读

    • 快照读中, 事务读取的是数据快照, 即事务开始时的数据状态
    • 当前读中, 事务读取的是数据库当前数据状态, 即最新数据, 如果有锁的话阻塞等待
    • 可重复读的隔离级别下
      • select 默认快照读
      • select 加锁是当前读
        • select a from t where id = 1 lock in share mode 共享锁
        • select a from t where id for update 排他锁
      • update 语句是当前读

    索引创建原则

    • 数据量大, 且查询频繁的时候要创建索引
    • 为常作为查询条件, 排序, 分组的字段创建索引
    • 字段的内容区分度要高
    • 内容较长的情况下, 使用前缀索引
    • 尽量使用联合索引, 这样可以进行覆盖查询, 方式回表
    • 要控制索引的数量

    什么情况下索引会失效

    • 联合索引中违反最左前缀法则
    • 联合索引中范围查询的右边的列, 不能使用索引
    • 索引列上进行运算操作, 索引失效
    • 索引列进行类型转换, 比如字符串不加单引号, 索引失效
    • 以%开头的 like 模糊查询, 索引失效

    全文索引

    • 是一种快速搜索文本数据的索引, 适用于需要处理大量自然语言文本的应用场景
    • 底层由倒排索引实现
      • 词典: 存储每一个词
      • 倒排列表: 记录每个词在文档中出现的位置

    慢查询出现的原因

    • 没有使用索引或者索引不当
    • 复杂的 join 操作, 尤其涉及大量数据的表
    • 子查询过多
    • 表结构设计不合理, 过多的冗余设计
    • 数据量过大
    • 服务器的硬件资源不粗

    优化慢查询

    • 先运行看看是否真的很慢，注意设置 SQL_NO_CACHE
    • WHERE 条件单表查，锁定最小返回记录表
      • 在涉及多个表的查询中，应该从返回记录最少的表开始查询，以减少数据处理的开销
      • 对于单表查询，通过 WHERE 条件逐个应用到表中的每个字段，找出区分度最高的字段，这样可以更高效地过滤数据
        1
        2
        3
        4
        5
        6
        -- 假设有两个表
        SELECT * FROM A, B WHERE A.id = B.id AND A.status = 'active' AND B.type = 'premium';
        -- 分析单标 where 条件效果
        SELECT * FROM A WHERE status = 'active'; -- 结果记录数较多
        SELECT * FROM B WHERE type = 'premium';  -- 结果记录数较少
        -- 优先从记录较少的 B 表开始查
    • 使用 EXPLAIN 查看查询的执行计划，确认查询是从记录较少的表开始的，并检查索引的使用情况
    • 在 ORDER BY … LIMIT … 的查询中，应该优先处理排序的表，这样可以减少排序和截取结果的开销

    事务的特性

    • A 原子性: 事务是不可分割的最小单位, 要么全部成功, 要么全部失败
    • C 一致性: 事务完成时, 必须使所有的数据都保持一致状态
    • I 隔离性: 数据库系统提供的隔离机制, 保证事务在不收外部并发操作的影响的独立环境下运行
    • D 持久性: 事务一旦提交或回滚, 它对数据库的改变是永久的

    并发事务带来了哪些问题

    • 脏读
    • 不可重复度
    • 幻读: 一个事务按照条件查询, 没有对应的记录, 但是插入的时候发现记录已存在, 好像出现了幻影

    MVCC

    • 多版本并发控制, 主要是通过隐藏字段(事务 id, roll_pointer), undo log 版本链和 ReadView组成的
    • 它维护了一条数据的多个版本, 用 roll_pointer 进行连接, 形成一个 undo log 链
    • ReadView 快照读
      • 读已提交: 每次 select 都会生成快照
      • 可重复读: 第一次 select 生成快照, 之后复用

    对于一个事务视图来说, 除了自己更新总是可见的以外, 还有 3 种情况

    • 版本未提交, 不可见
    • 版本已提交, 但是在视图创建后提交的, 不可见
    • 版本已提交, 而且实在视图创建前提交的, 可见

    幻读

    当同一个查询在不同的时间产生不同的结果集时, 事务中就会出现所谓的幻象, 例如

    1
    select * from t where id > 100;

    在 T1 时刻和 T2 时刻分别执行以下语句, 得到的结果集是不相同的
    例如 T1 时刻有 5 条记录, 而 T2 时刻有 6 条记录
    在可重复度级别下
    image.png
    前后两次查询结果是一样的, 没有出现幻读, 因为在可重复度级别下 select 执行的时候会创建一个快照, 在快照上读肯定没有幻读
    但是当前读会出现幻读, 因为当前读都会查询最新版本数据, 然后再做进一步操作

    • update
    • insert
    • delete
    • select … for update 都是当前读
      我们假设 select … for update 是不会加锁, (实际上是会加锁的)
      image.png
      由于 select … for update 是当前读, 所以就会出现两次查询结果不一样的情况
      所以 InnoDB 为了解决可重复读使用当前读而造成的幻读问题, 引入了间歇锁
      image.png
      事务 A 执行语句后就会在 id 范围为(2, +∞] 的 next-key lock(间歇锁 + 记录锁)
      事务 B 在执行插入语句的时候, 判断插入位置被 A 加上了 next-key lock, 于是事务 B 会生成一个插入意向锁, 等待

    还有几种情况
    详见小林 coding https://xiaolincoding.com/mysql/transaction/phantom.html

    主从同步原理

    • 核心是 binlog 日志
    • 从库读取主库的 binlog, 写入到从库的中继日志 Relay Log 中
    • 从库执行中继日志的事务, 同步数据

    分库分表

    • 水平分库
    • 水平分表
    • 垂直分库
    • 垂直分表: 冷热数据分离, 多表互不影响(把表中某些字段分出去)

    范围查找流程

    有索引

    比如找到 id >= 20 and < 49的数据
    1、加载根数据页到内存
    2、在内存中做二分，找到对应的子页
    3、在子页做二分，找到对应的子页
    4、现在到了叶子节点页，在页中做二分，找到第一条满足的数据，这里是 id = 20
    5、一直通过叶子节点的链表指针，找到第一条不满足的为止，这里是 id = 49
    6、结束查找，返回数据

    没有索引

    • 扫描全表

    如何避免主从延迟

    • 强制将读请求路由到主库处理, 对于必须获取最新数据的请求, 都交给主库处理
    • 延迟读取, 对于一些对数据比较敏感的场景, 可以在完成写请求之后, 避免立刻进行请求操作
      • 比如支付成功之后, 跳转到支付成功的页面, 当点击返回的时候才返回自己的账户

    MySQL 主键自增策略

    • 自增
    • UUID
    • 雪花算法

    MySQL 的索引类型

    • 普通索引
    • 唯一索引
    • 主键索引, 一般在创建表的时候指定
    • 联合索引
    • 全文索引

    间隙锁导致死锁

    1
    2
    3
    4
    5
    6
    7
    -- 事务A
    delete from course where user_id = 3;
    insert into course(id, user_id, no) VALUE (3,3,'3');

    -- 事务B
    delete from course where user_id = 4;
    insert into course(id, user_id, no) VALUE (4,4,'4');

    日志有哪些

    • BinLog 日志
    • 慢查询日志
    • Relay Log
    • Undo Log
#八股
MySQL
http://showyoubug.cn/2024/05/27/MySQL/
作者
Dong Su
发布于
2024年5月27日
许可协议