面经·MySQL

1. mvcc
   1. 参考链接：大厂最爱问的MVCC，到底是个啥？
   2. MVCC，即Multi-Version  Concurrency Control （多版本并发控制）。它是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
   3. 通俗的讲，
      1. 数据库中同时存在多个版本的数据，并不是整个数据库的多个版本，而是某一条记录的多个版本同时存在，在某个事务对其进行操作的时候，需要查看这一条记录的隐藏列事务版本id，比对事务id并根据事物隔离级别去判断读取哪个版本的数据。
   4. 数据库隔离级别读已提交、可重复读 都是基于MVCC实现的，相对于加锁简单粗暴的方式，它用更好的方式去处理读写冲突，能有效提高数据库并发性能。
   5. 关键知识点
      1. 事务版本号
         1. 事务每次开启前，都会从数据库获得一个自增长的事务ID，可以从事务ID判断事务的执行先后顺序。这就是事务版本号。
      2. 隐式字段
         1. 对于InnoDB存储引擎，每一行记录都有两个隐藏列trx_id、roll_pointer，如果表中没有主键和非NULL唯一键时，则还会有第三个隐藏的主键列row_id。

• • 1. undo log
       1. undo log，回滚日志，用于记录数据被修改前的信息。在表记录修改之前，会先把数据拷贝到undo log里，如果事务回滚，即可以通过undo log来还原数据。
       2. 可以这样认为，当delete一条记录时，undo log 中会记录一条对应的insert记录，当update一条记录时，它记录一条对应相反的update记录。
       3. undo log有什么用途呢？

• • • • 1. 事务回滚时，保证原子性和一致性。
        2. 用于MVCC快照读。

• • 1. 版本链
       1. 多个事务并行操作某一行数据时，不同事务对该行数据的修改会产生多个版本，然后通过回滚指针（roll_pointer），连成一个链表，这个链表就称为版本链
       2. 流程
          1. 假设现在有一张core_user表，表里面有一条数据,id为1，名字为孙权：
          2. 现在开启一个事务A：对core_user表执行update core_user set name ="曹操" where id=1,会进行如下流程操作
             1. 首先获得一个事务ID=100
             2. 把core_user表修改前的数据,拷贝到undo log
             3. 修改core_user表中，id=1的数据，名字改为曹操
             4. 把修改后的数据事务Id=101改成当前事务版本号，并把roll_pointer指向undo log数据地址。
    2. 快照读和当前读
       1. 快照读
          1. 读取的是记录数据的可见版本（有旧的版本）。不加锁,普通的select语句都是快照读,如：select * from core_user where id > 2
       2. 当前读
          1. 读取的是记录数据的最新版本，显式加锁的都是当前读
          2. select * from core_user where id > 2 for update;
          3. select * from account where id>2 lock in share mode;
    3. Read View
       1. Read View是什么呢？ 它就是事务执行SQL语句时，产生的读视图。实际上在innodb中，每个SQL语句执行前都会得到一个Read View。
       2. Read View有什么用呢？ 它主要是用来做可见性判断的，即判断当前事务可见哪个版本的数据~
  • MVCC 实现原理
    1. 获取事务自己的版本号，即事务ID
    2. 获取Read View
    3. 查询得到的数据，然后Read View中的事务版本号进行比较。
    4. 如果不符合Read View的可见性规则， 即就需要Undo log中历史快照;
    5. 最后返回符合规则的数据
       1. InnoDB 实现MVCC，是通过Read View+ Undo Log 实现的，Undo Log 保存了历史快照，Read View可见性规则帮助判断当前版本的数据是否可见。
• int(1)、int(10)区别
  1. INT(1) 和 INT(10)本身没有区别，但是加上(M)值后，会有显示宽度的设置
  2. M的值与int(M)所占多少存储空间没有任何关系，int(1)、int(10)、int(11) 在磁盘上都是占用 4 btyes 的存储空间。
  3. 与int类型的字段能存储的数据最大最小值也没有任何关系，int类型的字段能存储的数据上限还是2147483647(有符号型)和4294967295(无符号型)
• 组合索引创建规则，检索规则
  1. 最左前缀
• 事务隔离级别可重复读
  1. 是通过MVCC，进行版本控制，查看数据权限。是否可以查看已提交、未提交的数据
• Mysql主从
  1. 主从延迟
     1. 主从配置不一致，会导致主从延迟
     2. 慢SQL，阻塞SQL执行
     3. 大批量数据写入，触发刷脏页
• binlog 监听
  1. Canal
  2. 高可用基于 ZK
• 存储引擎 - innodb 与 mysiam
  1. mysiam 不支持事物、不支持行锁
  2. innodb 没有存储总行数
  3. innodb 支持读写并发，写不阻塞读（MVCC）。
• 聚簇索引和非聚簇索引的区别
  1. innodb 聚簇索引，叶子结点，寸了数据，不需要回表
     1. innodb 非主键索引，普通索引，如果查询的字段不包含在索引树上，也需要回表。非聚簇索引
  2. mysiam 非聚簇索引，需要回表查询
• 使用行锁的条件
• 回表
  1. 索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。

• 1. 非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。
• 索引失效的场景
  1. 优化器选错索引
     1. 采样基数，预估扫描行数较多
     2. 在回表操作较多和 直接在主键上查找更快上，进行判断
     3. 是否有order by
  2. 选错索引解决办法
     1. force index 强制走索引
     2. 重新分析基数 analyze table tableName;
     3. 修改sql，引导走索引
     4. 删除索引（有些索引，真的是没有必要存在的索引）
• sql优化

• 1. 慢SQL日志
  2. explian
  3. profile
  4. 外部工具
     1. soar

1. SQL 执行过程
   1. 客户建立连接
      1. 连接器：管理连接，权限验证
      2. 分析器：词法分析，语法分析
      3. 查询缓存：如果有，则直接返回
      4. 优化器：执行计划生成，索引选择
      5. 执行器：操作引擎，返回结果
2. 事务的特性
   1. ACID
      1. 原子性
      2. 一致性
      3. 隔离性
      4. 持久性
3. 事务的隔离级别
   1. 读未提交
      1. 一个事务还未提交，它所做的变更就可以被别的事务看到
   2. 读已提交
      1. 一个事务提交之后，它所做的变更才可以被别的事务看到
   3. 可重复读
      1. 一个事务执行过程中看到的数据是一致的。未提交的更改对其他事务是不可见的
   4. 串性化
      1. 对应一个记录会加读写锁，出现冲突的时候，后访问的事务必须等前一个事务执行完成才能继续执行
4. 事务并发存在的问题
   1. 脏读
   2. 不可重复读
   3. 幻读
5. 事务嵌套
   1. 考察 MVCC
   2. 事务隔离级别
   3. 事务并发存在的问题
6. B+Tree
   1. Mysql是一种关系型数据库，区间访问是常见的一种情况，B+树叶节点增加的链指针,加强了区间访问性，可使用在范围区间查询等
   2. B+树的高度一般为2-4层，所以查找记录时最多只需要2-4次IO，相对二叉平衡树已经大大降低了。
   3. 除了B+tree，还有那些索引
      1. B+tree
      2. 全文索引
      3. HASH索引
7. group by之后order by失效的原因
   1. 通俗解释：
      1. 因为group by 和 order by 一起使用时，会先使用group by 分组，并取出分组后的第一条数据，所以后面的order by 排序时根据取出来的第一条数据来排序的，但是第一条数据不一定是分组里面的最大数据
   2. 官方解释：
      1. mysql5.7版本 order by 如果没有加limit 就会失效(半链接优化子查询)
      2. 查看链接：MySQL :: MySQL 5.7 Reference Manual :: 8.2.2.1 Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations

参考链接：

1. MySQL 索引最左前缀原则 - 《MySQL 面试题》 - 技术池(jishuchi.com)
2. 事务、MVCC 大厂最爱问的MVCC，到底是个啥？

我为人人，人人为我，美美与共， 天下大同。