MySQL学习-非事务相关的六大日志、InnoDB的三大特性以及主从复制架构

一. 六大日志

• 慢查询日志:记录所有执行时间超过long_query_time的查询，方便定位并优化。

# 查询当前慢查询日志状态
SHOW VARIABLES LIKE 'slow_query_log';
#启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
#设置慢查询文件位置
SET GLOBAL slow_query_log_file = '/path/to/your/slow-query.log';
#设置慢查询的阈值 ,单位秒
SET GLOBAL long_query_time = 2
#记录所有未走索引的查询到慢查询日志
SET GLOBAL log_queries_not_using_indexes = 'ON';

• 通用查询日志：记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给MySQL数据库服务器的所有SQL指令等，如select、show等，无论SQL的语法正确还是错误、也无论SQL执行成功还是失败，MySQL都会将其记录下来。通用查询日志用来还原操作时的具体场景，可以帮助我们准确定位一些疑难问题，比如重复支付等问题。

# 查询通用查询日志状态
show variables like '%general_log%';
# 打开通用查询日志
SET GLOBAL general_log=on;
# 设置通用查询日志文件位置
SET GLOBAL general_log_file='/path/to/your/general.log';

3.错误日志：记录了数据库启动和停止，以及运行过程中发生任何严重错误时的相
关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。
在MySQL数据库中，错误日志功能是默认开启的，而且无法被关闭。

# 查看错误日志存放位置
show variables like '%log_error%';

4.二进制日志：即binlog日志，记录了所有执行过的修改操作语句，不保存查询语句。可用于数据库灾备和主从复制。

# 查看binlog相关参数
show variables like '%log_bin%';

打开或关闭binlog文件需要在mysql配置文件中修改（Mysql5.7默认关闭，Mysql8.0默认开启），然后重启数据库

# log‐bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，则binlog文件默认会放在data数据目录下
log‐bin=mysql‐binlog
# Server Id是数据库服务器id，随便写一个数都可以，这个id用来在mysql集群环境中标记唯一mysql服务器，集群环境中每台mysql服务器的id不能一样，不加启动会报错
server‐id=1
# 其他配置
binlog_format = row # 日志文件格式
expire_logs_days = 15 # 执行自动删除binlog日志文件的天数， 默认为0， 表示不自动删除
max_binlog_size = 200M # 单个binlog日志文件的大小限制，默认为 1GB

binlog日志格式
在Mysql配置文件中可以设置binlog_format不同的参数来设置不同的binlog日志记录格式，MySQL支持三种binlog日志格式：

• statement:基于sql语句的复制，每一条修改语句都会记录到binlog日志中，这样的记录方式数据量小，节省I/O开销，但是遇到一直执行时才能确定结果的函数，例如：UUID(),RAND(),SYSDATE()等函数，如果使用binlog恢复数据或者从机通过binlog同步数据，则会出现不一致的问题。
• row:基于行的复制，日志中会记录成每一条记录的修改语句，可以解决函数、存储过程等问题，但是日志里较大，没有statement性能好。假设update语句更新10行数据，statement方式就记录这条update语句，row方式会记录被修改的10行数据。
• mixed:混合模式，两种方式的结合，MySQL会根据执行语句的不同来选择不同的记录方式，也就是statement和row中选一种，如果sql里有需要执行才能确定的函数，那么就用row的形式记录，其他情况就用statement。推荐用这种方式。

binlog写入磁盘的机制
binlog写入磁盘的方式由sync_binlog参数控制，默认是0

• 为0的时候，表示每次提交事务都只 write 到page cache，由系统自行判断什么时候执行 fsync 写入磁盘。虽然性能得到提升，但是机器宕机，page cache里面的 binlog 会丢失。
• 也可以设置为1，表示每次提交事务都会执行 fsync 写入磁盘，这种方式最安全。
• 还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write 到page cache，但累积N个事务后才 fsync 写入磁盘，这种如果机器宕机会丢失N个事务的binlog。

5. 中继日志：MySQL8.0加入的日志，用于主从服务器架构中，从服务器存放主服务器二进制日志的一个中间文件，从服务器通过读取中继日志，来同步主服务器的操作。
6. 数据定义语句日志：MySQL8.0加入的日志，记录数据定义语句执行的元数据操作。

InnoDB的三大特性

双写缓冲区

innodb的基本操作单位是数据页，一个页是16KB，而系统和磁盘间的数据操作是以4KB为操作单位进行的，这样一个数据页写入对应到磁盘可能需要执行多次，极端情况下，可能会出现数据页只写前面一部分，然后发生系统宕机的问题。
对于这样的极端情况就可以使用双写缓冲区来恢复

• 在执行脏页刷盘时MySQL实际会写两份,一份通过顺序写写到双写缓冲区文件,一份随机写写到磁盘也就是数据的实际落盘
• 通过fsync多写一次数据到双写缓冲区,虽然顺序写大概还是会造成5%-10%的性能消耗,但是能保证数据的安全
• 数据写入双写缓冲区成功,真实落盘失败时,会通过双写缓冲区拿到写入的数据重新落盘
• 数据写入双写缓冲区如果失败,MySQL会通过B+树载入原始数据,通过redoLog恢复原始数据

自适应的hash索引

Innodb存储引擎会监控对表上二级索引的查找,如果发现某二级索引被频繁访问,这个二级索引建立对应的哈希索引提高性能(这个hash索引时存在BufferPool的)

BufferPool