河北王校长MySQL

索引

• b+树数据结构\数量级\存储内容
• b+树优势、对比哈希表\数组\b树\跳表
  • 跳表不适用磁盘存储介质、指针管理难以适应联合索引、出现时间晚
  • hash不支持最左前缀和范围查找
  • 数组成本过高
  • b树树型过高，io多
• 最左前缀原则
• 索引覆盖
• 索引下推
• 扩充
  • show index 命令后查看 Cardinality 可以了解离散程度

索引失效

• 索引失效
  • %like
  • or条件有非索引
  • 联合查询不满足最左前缀原则
  • 运算
  • isnot != <>成本过高
  • is null/is not null成本过高
  • 函数操作
  • 类型隐式转换(convert)
• 补充
  • 对于null值优化 强制非空\默认值
  • select * from table where index > 3
    • MySQL5.5 由于回表离散读的问题，索引可能会失效
    • MySQL5.5+ mrr优化解决离散读，一般会使用索引

mvcc、隔离级别、问题、锁

• mvcc
  • 一致性非锁定读
  • MySQL基于自己回滚机制为并发常场景的优化
• 隔离级别中的mvcc
  • ru：直接返回记录上的最新值，没有视图概念。
  • rc：视图是在每次SELECT时生成的。（违背了事务ACID里的隔离性）
  • rr：视图是在第一次SELECT时创建的，事务读取期间的SELECT都用这个视图。（此隔离级别下需要利用间隙锁来解决幻读问题）
  • s：直接用加锁的方式来避免并行访问，没有视图概念。
• 隔离级别中的问题
  • ru：脏读、幻读、不可重复读
  • rc：幻读、不可重复读
  • rr：幻读
• 锁
  • Record Lock：单个行记录上的锁
  • Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
  • Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

日志

• MySQL插入瓶颈
  • 自增id（id并发瓶颈）
    • 5.1.22前 auto inc locking
    • 5.1.22后 多模式 全互斥量\全锁\混合
    • 主从同步SBR会导致数据不一致，需要修改为RBR
  • commit（磁盘写入瓶颈）
    • 事务提交时会调用 fsync 对 redo log 进行刷盘，将redo log buffer刷新到redo log file
      • 事务提交时必须调用一次fsync操作
      • 表示事务提交时不进行写入重做日志操作，这个操作仅在master thread中完成，而在master thread中每1秒会进行一次重做日志文件的fsync操作。
      • 事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行fsync操作
    • 批量插入
• redo log
• uodo log
• bin log

分布式

• 隔离级别
  • rc
    • 违背隔离性，可接受，体现真实数据变迁
    • 其他情况代码处理
    • 推荐
• MySQL5.0
  • rC隔离级别，bin log只有statment模式，会导致主从复制数据不一致，被迫使用rr
• MySQL5.1
  • rC隔离级别，bin log引入row、mix模式
• MySQL5.1.22
  • 自增主键引入互斥量，配合rc的rbr，主从复制插入性能最佳
• MySQL5.5
  • 主从复制异步bin log同步，半同步复制，至少保证一个从服务器接受bin log
• MySQL5.6
  • mrr解决离散读问题
• MySQL5.6
  • 主从复制异步bin log同步，增强半同步复制，至少保证一个从服务器将bin log写入relay log
• MySQL5.7
  • 多线程复制技术（从库）
• MySQL8.0
  • 同事务多线程复制技术
• MySQL集群架构
  • 多主互为主从，每个主服务器挂载多个从服务器
    • 无法再挂一层，将binlog写入relaylog再执行将自身binlog传递成本太高
  • 数据库分片中间件，多主互为补充，每个主服务器挂载多个从服务器

补充

• flc
• Online DDL
• 倒排索引
• 自增主键实现
• 自适应哈希索引
• 更新丢失
• 死锁
  • 超时回滚
  • 等待图
• 分布式事务
• MySQL语句执行原理