MySQL

MySQL知识思维导图，包括：架构、执行流程、索引结构、内部索引、存储引擎、日志、主键ID/雪花算法。

MySQL

• MySQL
  • 架构
    • Service
      • 连接器
      • 分析器
      • 优化器
      • 执行器
    • 存储引擎层
  • 执行流程
    • 查询流程
    • 修改流程
      • 扩展一下两阶段提交
  • 索引结构
    • 哈希
      • 把每个数据都hash出一串key之后，然后存在数据页的某一个位置
    • 链表
      • 物理存储单元上非连续、非顺序的存储结构
    • 二叉树
    • 红黑树
      • 红黑树是一种 特定类型的二叉树，是一种平衡二叉查找树的变体
    • B+树
  • 内部索引
    • 聚簇索引
      • 可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次分裂操作。页分裂会导致表占用更多的磁盘空间。
    • 非聚簇索引（二级索引）
      • 存储的不是数据，而是数据行的地址
        • 如果主键比较大的话，那非聚簇索引将会变得更大，因为非聚簇索引的叶子节点存储的是主键值，过长的主键值，会导致非叶子节点占用更多的物理空间
    • 普通索引
      • 加快对数据的访问速度。
    • 唯一索引
      • 与普通索引类型，不同的是唯一索引的列值必须唯一。但允许为空值。主键索引是特殊的唯一索引，不允许有空值。
    • 联合索引
    • 索引下推
      • 在5.6引入。可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。
  • 存储引擎
    • innodb
      • innodb是支持事务的存储引擎，合于插入和更新操作比较多的应用，设计合理的话是行锁（最大区别就在锁的级别上），适合大数据，大并发。
    • myisam
      • MyISAM 是非事务的存储引擎，适合用于频繁查询的应用。表锁，不会出现死锁，适合小数据，小并发
  • 日志
    • redo log
      • 重做日志
        • redo log buffer日志缓存（物理上这是MySQL的进程内存）
        • redo log file日志文件
      • 写入机制
        • 事务未提交之前，会先写入redo log buffer，等commit之后，再刷到redo log
      • 写入策略
        • 参数（innodb_flush_log_at_trx_commit）
          • 0，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
          • 1，表示每次事务提交时都将 redo log 直接持久化到磁盘；
          • 2，表示每次事务提交时都只是把 redo log 写到 page cache。
        • InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。
      • 刷新策略
        • 参数（innodb_log_buffer_size）
          • redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。
          • 并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘
      • 组提交机制
        • redo log中有一个事务，先到达之后，会选出一个组长，由这个组长进行持久化到磁盘，他会连带着把整个组都持久化。
          • 这里可以了解一下LSN
      • 持久化磁盘
        • 从mysql的redo log buffer 调用write写入FS page cache 再调用 hard disk 持久化到磁盘（这里涉及磁盘io所以fsync速度慢的很多）
    • binlog
      • 归档日志
      • 写入机制
        • 提到binlog，必然提到binlog cache，它是一个二进制日志文件的缓冲区，他是由一个参数 binlog_cache_size 控制大小的缓冲区。
          • 事务在执行时是不允许被拆开的，必须一次执行完成，如果事务的大小超过了binlog_cache_size 就会暂存磁盘，提交的时候把binlog_cache 提交到binlog中，并清空binlog_cache
        • 何时write，何时fsync。这个就可以研究一下sync_binlog
          • 当它等于0时，每次提交事务都只 write，不 fsync
          • 当它等于1时，每次提交事务都会执行 fsync
          • 当它大于1时， 每次提交事务都 write，但累积 N 个事务后才 fsync
          • 因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。
      • 租提交机制
        • 白话文概念就不介绍了。
          • binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync
    • 慢日志
      • 运行时间超过long_query_time值的SQL，则会被记录到慢查询日志（默认值10，不开启）
      • 参数
        • slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。
        • long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
      • 工具
        • mysqldumpslow
    • undolog
      • 回滚日志
        • 存储着与执行日志相反逻辑的日志
    • 事务日志
      • 事务日志主要围绕的就是 binlog和redolog
  • 主键ID/雪花算法
    • 优缺点
    • 存放位置
      • 自增值是保存在表结构定义里的，实际上表结构定义是存放在后缀名为.frm 文件中，但不会保存自增值。
    • 保存策略
      • MyISAM 引擎的自增值保存在数据文件中。
      • innodb5.7及之前的版本，自增值都是保存在内存中，没有持久化每次重启后，第一次打开表的时候都会去找自增值的最大值然后将最大值+1作为当前的自增值ID。
      • innodb8.0版本，将自增值保存在了redolog中，重启的时候依靠redolog恢复重启之前的值
    • 修改机制
      • 自增值生成算法是：从 auto_increment_offset 开始，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值。默认值都是 1
    • ID不连续的问题
      • 插入不成功自增值没有被改回去，导致不连续
      • 事务回滚问题
    • ID上限后如何处理
      • 主键ID自增上限后，就会出现覆盖掉原数据的情况。上线是4294967295，近43亿。
    • 雪花算法
      • 第一个部分，是 1 个 bit：0，这个是无意义的。
      • 第二个部分是 41 个 bit：表示的是时间戳。
      • 第三个部分是 5 个 bit：表示的是机房 id，10001。
      • 第四个部分是 5 个 bit：表示的是机器 id，1 1001。
      • 第五个部分是 12 个 bit：表示的序号，就是某个机房某台机器上这一毫秒内同时生成的 id 的序号，0000 00000000。
  • 事务
    • 特性
      • 通过set transaction isolationlevel //设置隔离级别，设置隔离级别会在下一个事务开始的时候生效
      • A（原子性）
        • 要不全部成功，要不全部失败，不可能只执行其中一部分操作，这就是事务的原子性
      • C（一致性）
        • 一致性主要体现在数据一致性，事务最终没有提交，事务所修改的数据不会保存在数据库中
      • I（隔离性）
        • 当前事务执行的修改在最终提交之前，对其他事务是不可见的。
      • D（持久性）
        • 一旦事务提交，将修改的数据持久化到数据库中就算数据库断电崩溃也不会丢失。
    • 隔离级别
      • read uncommitted（未提交读）：修改即使没有提交对其他事务都是可见的，也可以称为脏读
      • reda committed（提交读）：从执行到提交前，其他事务都是不可见的
      • repeatable read（可重复读）：repeatable read解决了read committed脏读的问题。保证了同一个事务多次执行可以读取同样的数据，但是存在幻读！幻读就是当事务在某个范围内读取数据时，这时另一个事务在这个范围插入了数据，当读取的事务再次读取该范围时会产生幻行。通过MVCC解决了幻读的问题。
      • serializable（可串行化）：通过强制事务在从串行上执行，避免了前面说的幻读问题，简单来说就在在读取数据时加一个锁，大量的加锁会导致出现争锁超时的问题
    • 解决方案
      • 如果是新插入的数据可以采用间隙锁的方式解决幻读的问题。
      • 如果是修改一个数据的话可以采用加锁的方式解决幻读的问题。
  • MVCC
    • 概念
      • MVCC是多版本并发控制。通过保存数据在某一个时间点的快照来实现的。也就是说不管需要执行多长时间。每次事务执行的数据都是一致的
    • 实现
      • 乐观锁并发控制
      • 悲观锁并发控制
    • 原理
      • MVCC通过每行记录后面保存两个隐藏的列来实现的，一个是保存行的创建时间，一个是保存行的过期时间。存储的不是时间值，而是系统的版本号。每开始一个新的事务，系统版本号会自动增加。事务开始时刻的系统版本号也就是事务的版本号，用来查询到每行记录的版本号进行对比。
    • 优点
      • 保存这两个额外的系统版本号的好处就是 操作数据的时候不需要单独上锁，这样设计使得数据操作很简单，性能也很好。并且也能保证只会读取到符合标准的行。
    • 缺点
      • 每行记录都需要额外的存储空间，需要做更多的检查行的操作，以及额外的维护工作
    • 注意！MVCC只在可重复读和提交读两种隔离级别下工作！
  • 锁
    • 全局锁
      • 间隙锁
      • 读写锁
      • 共享锁
      • 排他锁
      • 意向锁
      • 元数据锁
    • 表级锁
    • 行级锁
  • 主从库
    • 主从同步原理
      • row
      • statement
      • mixed
    • 主从同步扩展的问题
      • 主从库切换时，我们把从库设置成readonly模式，可以防止切换时，造成一些逻辑BUG。
      • 主从同步的循环复制问题
        • 参数 log_slave_updates 设置为 on，表示备库执行 relay log 后生成 binlog。
        • 规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
        • 一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；
        • 每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。
  • SQL优化，调优
    • SQL调优
      • 最左匹配原则：SQL语句中的对应条件的先后顺序无关。只要出现最左侧的索引树就为最左匹配原则。在explain执行计划中，可以通过key这一列查看是否命中，是否符合最左匹配原则。
      • 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
    • 参数调优
      • 长连接与短连接
        • 总结：数据库建立连接这个过程是比较复杂的，所以建立尽量减少使用短连接的方式，也就是尽量使用长连接。但是长连接是比较容易涨内存的。也会被系统误认为内存占用过大强行杀死
        • 优化方案： 可以采用定期断开长连接的方法优化。还可以通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证。
      • IO瓶颈场景的优化
        • 出现 IO 瓶颈的场景，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。
    • 索引失效
      • like查询以%开头
      • where 条件 <> !=
      • OR语句前后没有同时使用索引
      • 数据类型出现隐式转换
      • 使用is null函数时，不能利用索引，只能全表扫描。（其他函数也要注意）
      • SQL中有or，也会失效
      • in 和 not in 也要慎用
  • 高可用
    • 可靠性优先策略
      • 判断从库B的seconds_behind_master 是否小于某个值，如果大于某个值的话延迟太大会影响业务数据的，所以一定要小于某个值的时候才可以继续下一步
      • 把主库A改成只读状态，readonly改为true（主库A改成了只读模式，这是不可用的时间，这段时间都是不可写的，有数据的话只能等待。）
      • 再判断seconds_behind_master的值，直到这个值变成0为止。（因为只有延时足够低，数据才足够安全）
      • 把从库B改成读写状态，也就是把readonly改为flase
      • 最后把业务的请求都打到B上
    • 可用性优先策略
      • 不等同步完成之后再切换过去和状态修改。而是直接把一系列步骤一次性到位。这样是比较危险的，主备切换的可用性优先策略会导致数据不一致。
  • 分库分表
    • 水平拆分
      • 如果表中有原数据，可以采用把ID取模处理。偶数去A表，基数去B表。
      • 优点
        • 表关联基本能够在数据库端全部完成。不会存在某些超大型数据量和高负载的表遇到瓶颈的问题；
        • 应用程序端整体架构改动相对较少； 事务处理相对简单；
        • 只要切分规则能够定义好，基本上较难遇到扩展性限制；
      • 缺点
        • 切分规则相对更为复杂，很难抽象出一个能够满足整个数据库的切分规则
        • 后期数据的维护难度有所增加，人为手工定位数据更困难；
        • 应用系统各模块耦合度较高，可能会对后面数据的迁移拆分造成一定的困难。
    • 垂直拆分
      • 就是根据不同的业务进行拆分的，拆分成不同的数据库，比如会员数据库、订单数据库、支付数据库、消息数据库等，垂直拆分在大型电商项目中使用比较常见。
      • 优点：拆分后业务清晰，拆分规则明确，系统之间整合或扩展更加容易。
      • 缺点：部分业务表无法join，跨数据库查询比较繁琐(必须通过接口形式通讯(http+json))、会产生分布式事务的问题,提高了系统的复杂度。
    • 拆分解决方案
      • 分片策略
        • 求模算法
        • 分片枚举
        • 范围约定
        • 日期指定
        • 固定分片hash算法
        • 通配取模
        • ASCII码求模通配
        • 编程指定
        • 字符串拆分hash解析
  • 刷脏页机制
    • 概念
      • 内存上的数据和磁盘上的数据页的内容一致时，称为 “干净页”。
      • 内存上的数据和磁盘上的数据页的内容不一致时，称为 “脏页”。
    • 参数
      • innodb_io_capacity
      • innodb_max_dirty_pages_pct（控制刷脏页的比例，默认值为75%）
    • 刷脏页时的连坐机制
      • 1启用0禁用
      • 如果刷一个脏页为AA，AA的旁边的数据页也是脏页，那么刷到AA的同时会把旁边的BB也一起刷掉。
  • 查询数据大于本机内存会不会宕机
    • 如果Sever层在本地就会宕机，如果不在就不会，MySQL是边读边发的