「八股浓缩」- MySQL

Tim   in Shanghai

# 索引

  • [ ] 索引的分类

    数据结构：B+, hash, full-text

    物理存储：聚簇索引（叶子是数据），辅助索引（叶子是主键）

    字段特性：主键、唯一、前缀、普通

    字段个数：单个、联合

  • [ ] 什么时候不用建立索引？

    频繁更新的字段（余额），不用定位的字段，分布均匀的字段（性别男女），表数据不多时也不用

  • [ ] 索引失效有哪些？

    1. 使用左或左右模糊匹配
    2. 使用函数
    3. 使用表达式计算
    4. where时的隐藏类型转换
    5. 联合索引非最左匹配
    6. where中的OR

  • [ ] 为什么用B+树做索引？

    1. 非叶子节点仅存放索引，使得矮胖的B+树大概3~4层，能够减少磁盘I/O的开销
    2. 通过二分查找树和叶子节点的双向链表机制，支持高效单点查询和范围查询
    3. 存在大量冗余节点，插入/删除仅影响1条路径，而不用向上递归调整树的变化

  • [ ] count(*) = count(1) > count(主键) > count(其他字段）

    因为count(*)和count(1)不会读取详细的数据，而后两者会读取并过滤NULL记录

# 事务

  • [ ] 事务特性

    Atomic：原子：要么全部执行，要么全部不执行：undo log

    Consistence：一致： 事务操作前后，数据状态满足完整性要求，保持一致性状态：A+I+D

    Isolation：隔离：允许多个事务并发，相互隔离使冲突事务以正确的顺序执行：MVCC

    Durability：持久：一旦事务完成，将永久保存，即使故障也不会丢失：redo log

  • [ ] 并发引起的事务问题（低 -> 高）

    「脏读 」：读取到未提交事务修改的数据

    「不可重复读 」：同一事务多次读取数据，出现不同结果

    「幻读 」：同一事务查询数据记录数量，出现不一致

  • [ ] Read View

    <trx_id, m_ids, min_trx_id, max_trx_id>

    undo链保存所有事务操作的版本

  • [ ] 事务的隔离级别（低 -> 高）

    级别：特性：实现方式：存在的问题

    「读未提交」：事务会读取到未提交事务的修改记录：什么也不做：脏读，不可重复读，幻读

    「读提交」：事务只能读取到已提交事务的修改记录：每次读取前创建一个新的Read View：不可重复读，幻读

    「可重复读」：事务内可以重复读取数据：事务开始时创建一个Read View：幻读

    「串行化」：冲突事务之间完全隔离：加读写锁，当读写和写写冲突时，会阻塞未获得锁的事务：无

  • [ ] 为什么MVCC不能完全解决幻读？

    1. 快照读场景下：若事务更新了其他事务插入的记录，则会把该记录的trx_id修改为自己的，即可查询到该记录
    2. 当前读场景下：若事务在「当前读」前执行了「快照读」，由于没有加next-key lock，若别的事物此时插入或删除数据，则会读取到不一样数量的记录。

# 锁

  • [ ] MySQL有哪些锁？
    1. 全局锁
    2. 表级锁：表锁（X和S）、元数据（MDL）锁、意向锁（快速判断表里是否有记录被加锁）、AUTO-INC锁（保证主键自增）
    3. 行级锁：Record Lock（X和S），Gap Lock（多个锁可以有交集），临键锁Next-Key Lock（需考虑Record Lock的冲突情况），插入意向锁

  • [ ] MySQL是怎么加锁的？

    加锁的对象是索引而不是数据（若是二级索引加锁，会传递并对主键索引加Record Lock），基本单位是Next-key Lock，会根据情况退化

    1. 唯一索引「等值查询」：（存在：Record Lock ）（不存在：Gap Lock）
    2. 唯一索引「范围查询」：
    3. 非唯一索引「等值查询」：
    4. 非唯一索引「范围查询」：
    5. 没有索引的update，delete，select for update：next-key 锁全表

  • [ ] 死锁的产生？
    1. 间隙锁的意义只在于阻止区间被插入，因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁。
    2. 两个事务获取的间隙锁存在交集，插入记录时插入意向锁，都需要等待对方间隙锁的释放。

  • [ ] 如何避免死锁？
    1. 设置事务等待锁的超时时间：innodb_lock_wait_timeout
    2. 开启主动死锁检测：innodb_deadlock_detect

# 日志

  • [ ] 有哪些日志？

    1. undo log（回滚日志）：引擎层：实现事务回滚和MVCC，保证原子性
    2. redo log（重做日志）：引擎层：由于内存Buffer Pool优化磁盘顺序读写（单位为16KB/页），存在丢失风险，需要WAL技术记录redo log保证持久性（循环写），保证持久性
    3. binlog：Server层：用于备份恢复、主从复制（追加写）；

    PS：redo log 和 binlog也有buffer，可以调整刷盘的参数，trade off数据安全性和写入性能。为保证一致性，redo log和binlog需要执行两阶段提交


  • [ ] MySQL磁盘I/O很高，有什么优化的办法？
    1. 优化查询和索引设计，尽量使用索引而不是全表扫描
    2. 延迟binlog和redo log的刷盘时机，减少磁盘I/O频率


# 内存

索引
事务
锁
日志
内存