MySQL运维篇

• 数据库三大范式是什么?
• • 第二范式和第三范式的区别
• bin log/redo log/undo log
• • bin log和redo log有什么区别？
• 大表怎么优化？
• MySQL主从同步
• • 主从同步原理
  • 为什么要做主从同步？
  • MySQL 主从同步延时问题
  • • 原因
    • 解决
• 分库分表
• • 数据拆分方式
  • 分区表
  • 分区表类型

第一范式：原子性，表中每个字段都不能再分。
第二范式：满足第一范式并且表中的非主键字段都依赖于主键字段。
第三范式：满足第二范式并且表中的非主键字段必须不传递依赖于主键字段。

自己理解

• 第一范式：确保数据库表字段的原子性
• 第二范式：首先要满足第一范式，另外包含两部分内容

1.	表必须有一个主键
2.	非主键列必须完全依赖于主键，而不能只依赖于主键的一部分

• 第三范式：首先要满足第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖

即不能存在：非主键列A依赖于非主键列B，非主键列B依赖于逐渐的情况

第二范式依据的是非主键列是否完全依赖于主键，还是依赖于主键的一部分
第三范式依据是非主键列是直接依赖于主键，还是直接依赖于非主键

MySQL日志主要包括查询日志、慢查询日志、事务日志、错误日志、二进制日志等。

• bin log：二进制日志
  是MySQL数据库级别的文件，记录对MySQL数据库执行修改的所有操作，不会记录select和show语句，主要用于恢复数据库和同步数据库。
• redo log：重做日志
  是innodb引擎级别，用来记录innodb存储引擎的事务日志，不管事务是否提交都会记录下来，用于数据恢复。当数据库发生故障，innoDB存储引擎会使用恢复到发生故障前的时刻，以此来保证数据的完整性。将参数设置为1，那么在执行commit时会将同步写到磁盘。
• undo log：回滚日志
  除了记录外，当进行数据修改时还会记录，用于数据的撤回操作，它保留了记录修改前的内容。通过可以实现事务回滚，并且可以根据回溯到某个特定的版本的数据，实现MVCC。

• bin log 会记录所有日志记录，包括InnoDB、MyISAM等存储引擎的日志；
  redo log 只记录innoDB自身的事务日志。
• bin log只在事务提交前写入到磁盘，一个事务只写一次；
  redo log在事务进行过程，会有不断写入磁盘。
• bin log是逻辑日志，记录的是SQL语句的原始逻辑；
  redo log是物理日志，记录的是在某个数据页上做了

• 限定数据的范围。比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内；
• 读写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
• 通过分库分表的方式进行优化，主要有垂直拆分和水平拆分。

数据是一个应用至关重要的一部分；
从目的出发，主从同步有那么点备份的意思；
主库（Master）将自己库中的写入同时同步给自己的从库（Slave），当主库发生某些不可预知的状况，导致整个服务器无法使用时，由于从库中也有一份数据，所以数据可以做到快速恢复，不造成或者减少造成数据的损失。

• Master 数据库只要发生变化，立马记录到bin log 日志文件中
• Slave数据库生成两个线程：I/O线程、SQL线程
  • 启动一个I/O thread连接Master数据库，请求Master变化的二进制日志，将获取到的二进制日志，保存到自己的Relay log （中继日志）日志文件中。同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件
  • SQL 线程定时检查Realy log是否变化，变化那么就更新数据

• 高可用，主从复制实现了数据的热备份，可以有效避免单点故障导致的数据丢失。
• 高性能，可以用来实现读写分离，提高MYSQL服务的并发性能

• MySQL的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高。Slave的SQL Thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随即的，不是顺序的，成本高很多。
• 另一方面，由于SQL Thread也是单线程的，当主库的并发较高时，产生的DML数量超过slave的SQL Thread所能处理的速度，或者当slave中有大型query语句产生了锁等待那么延时就产生了。

• 分库，将一个主库拆分为多个主库，每个主库的写并发就减少了几倍，此时主从延迟可以忽略不计。
• 打开 MySQL 支持的并行复制，多个库并行复制。如果说某个库的写入并发就是特别高，单库写并发达到了 2000/s，并行复制还是没意义。
• 重写代码

• 垂直拆分： 根据业务的维度，将原本一个库中的表拆分多个表，每个库中表与原有的结构不同
• 水平拆分： 根据分片算法，将一个库拆分成多个库，每个库依旧保留原有的结构

• 分区表就是将一个大表分成若干个小表。
• 当查询条件的数据分布在某一个分区的时候，查询引擎只会去某一个分区查询，而不是遍历整个表。在管理层面，如果需要删除某一个分区的数据，只需要删除对应的分区即可。
• 改善大型表以及具有各种访问模式的表的可伸缩性，可管理性和提高数据库效率。

• RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。这些区间要连续且不能相互重叠
• LIST分区：类似于按RANGE分区，区别在于给定的不是连续范围，是离散的值。
• HASH分区：根据用户自定义的表达式的返回值进行分区，返回值不能是负数。
• KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。