数据库：mysql特性和术语

站内链接:

mysql 优化

新特征

5.6 新特征

支持只读事务

从 MySQL 5.6 开始，内部存在两个事务链表：只读事务链表，正常事务链表，这样做的好处在于：其他事务利用MVCC机制读取数据时，生成的ReadView读视图中的活跃事务链表会小很多很多，因此遍历的速度更快，同时也无需为其分配回滚段，从而进一步提升了MySQL整体的查询性能。
InnoDB 存储引擎增强

BufferPool缓冲池相关的改进项，主要有两点：
- 刷盘策略改进：引入专门的线程用于处理 BufferPool，提高了缓冲池的刷盘效率，无需跟其他工作一起排队刷写；
- 缓冲池预热：每次关闭MySQL时都会将内存中的热点数据页保存到磁盘中，当重启时会直接从磁盘中载入之前的热点数据；
新增 performance_schema 库
- 已有库 information_schema：记录视图管理、临时表管理、会话管理、触发器管理、表分区管理等信息
- 新库 performance_schema：数据库整体的监控信息，比如事务监控信息、最近执行的SQL信息、最近连接的客户端信息、数据库各空间等
索引下推机制（ICP）：
Multi-Rang Read（MRR）：针对于辅助索引的回表查询，减少离散IO，并且将随机IO转换为顺序IO，从而提高查询效率
主从数据同步的复制改进：引入 GTID 复制、无损复制（增强半同步）、延时复制、并行复制技术
其他特征：索引增强、表分区增强、增强日期类型、日志增强、支持在线 DDL

5.7 新特征

引入共享排他锁：在 5.7 之前只有共享锁和排他锁，在该版本引入了 SX（共享排他），其通过优化排他锁，减小的锁的影响粒度从而减少了锁范围，进而提高效率

执行一条 SQL 语句可能有如下三种影响索引的行为：
- 读取操作：基于B+Tree去读取某条或多条行记录
- 乐观写入：不会改变B+Tree的索引键，仅会更改索引值，比如主键索引树中不修改主键字段，只修改其他字段的数据，不会引起节点分裂。
- 悲观写入：会改变B+Tree的结构，也就是会造成节点分裂，比如无序插入、修改索引键的字段值。
在引入 SX 之后，在B+树结构改变进行了更加精细化的控制而并非直接对整颗树上锁
支持 JSON 数据格式并提供了一系列 API：支持事务、在一行中同时存储格式化和非格式化数据
其他优化：临时表操作不记录到 redo-log 中、优化多节点的主从复制和并行复制、推出 mysqlpump 和 mysqlsh 工具

8.0 新特征

性能的巨大提升：只要将 mysql 从 5.7 升级到 8.0 则性能会提升 200%
移除查询缓存（Query Cache）：命中率低、内存占用高、增加查询步骤、缓存维护成本高、Redis 业务缓存替代
锁机制优化：使用更加方便
在线修改 MySQL 配置支持持久化到本地而不是重启就丢失配置：set persist global.XX=""
增强多表连接查询，在交叉连接、内连接、左外连接、右外连接的基础上增加了哈希连接、反连接
增强索引机制
CTE 通用表表达式：将一条查询语句的结果保存到一个变量里面，后续在其他语句中允许直接通过变量名来使用该结果集
窗口函数

日志类别

mysql 的日志在数据库管理和故障排查中起着重要作用，其中 binlog、relaylog 等在主从复制中起到关键作用，下面先简单列出不同日志类型的用途：

错误日志（Error Log）：记录 MySQL 服务器在启动、运行过程中发生的错误和警告信息。用于帮助诊断和解决问题，以便性能分析和调优。
查询日志（General Query Log）：记录所有客户端连接到 MySQL 服务器的查询语句。主要用于调试和性能分析，但在生产环境中一般不建议开启，因为会对性能产生较大影响。
慢查询日志（Slow Query Log）：记录执行时间超过设定阈值的查询语句。用于识别慢查询并进行性能优化。
二进制日志（Binary Log）：记录所有对数据库进行更改的操作，包括增、删、改等。用于数据恢复、主从复制以及数据库的备份和恢复。
事务日志（Transaction Log）：也称为重做日志（Redo Log），记录对数据库进行的修改操作，以支持事务的原子性、一致性和持久性。
中继日志（Relay Log）：用于 MySQL 主从复制中的从服务器，记录从主服务器接收到的二进制日志事件，以便在从服务器上重放这些事件。
Undo 日志（Undo Log）：记录事务中发生的数据修改操作的逆操作，用于回滚事务和 MVCC（多版本并发控制）。
回滚日志（Rollback Log）：记录事务回滚的操作，用于实现事务的回滚操作。

要查看 MySQL 中这些日志的位置，可以通过以下方式进行：

错误日志（Error Log）：可以通过查看 MySQL 配置文件（my.cnf 或 my.ini）中的log_error参数来确定错误日志的位置。也可以通过执行以下命令来查看日志文件路径：
1
SHOW VARIABLES LIKE 'log_error';
查询日志（General Query Log）：可以通过查看 MySQL 配置文件中的general_log和general_log_file参数来确定查询日志是否启用以及日志文件的位置。也可以通过执行以下命令来查看日志文件路径：
1
2
SHOW VARIABLES LIKE 'general_log';
SHOW VARIABLES LIKE 'general_log_file';
慢查询日志（Slow Query Log）：可以通过查看 MySQL 配置文件中的slow_query_log和slow_query_log_file参数来确定慢查询日志是否启用以及日志文件的位置。也可以通过执行以下命令来查看日志文件路径：
1
2
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
二进制日志（Binary Log）：可以通过查看 MySQL 配置文件中的log_bin和log_bin_basename参数来确定二进制日志是否启用以及日志文件的位置。也可以通过执行以下命令来查看日志文件路径：
1
2
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'log_bin_basename';
其他日志如事务日志、中继日志、Undo 日志、回滚日志等，它们的位置和相关参数也可以通过类似的方式进行查询。具体的参数名称和命令可以参考 MySQL 的官方文档或相关文档资源。

请注意，具体日志文件的位置可能会因为操作系统、MySQL 版本和配置的不同而有所差异。因此，最准确的方式是查看 MySQL 的配置文件或执行相应的命令来获取日志文件的路径信息。

查询日志

功能

查询日志

查询日志分为：general log，slow log，这两者都是用于调试和性能分析，其中查询日志一旦开启，则MySQL会向其中写入所有收到的查询命令，该操作一般在测试阶段进行，通过分析日志中的平均耗时，再根据正常的SQL执行时间，设置一个偏大的慢查询阈值即可。

注意，开启 general log 全量日志是非常耗费系统资源的, 大概需要消耗 MySQL 中 5%~10%的资源，所以线上环境开启是需要进行多方考量的。

慢查询日志

当一条SQL执行的时间超过规定的阈值后，那么这些耗时的SQL就会被记录在慢查询日志中，当线下出现响应缓慢的问题时，可以直接通过查看慢查询日志定位问题，定位到产生问题的SQL后，再用explain这类工具去生成SQL的执行计划，然后根据生成的执行计划来判断为什么耗时长，是由于没走索引，还是索引失效等情况导致的。

命令

查看日志位置以及查看是否开启

# 查看输出日志格式: FILE, TABLE
show variables like 'log_output';
# 查看是否开启慢查询日志
show global variables like '%general%';

注意，如果log_output的值是 TABLE，则所有的日志会输出到mysql.general_log表中（一般不建议），如果值是 FILE，则输出到指定文件中，这点非常重要。

在线配置查询日志

# - 直接配置my.cnf
# - 在线更改：general_log表示是否开启查询日志，general_log_file表示指定查询日志的存储路径和文件名
set global general_log=1
set global general_log_file=/tmp/general_log.log

慢查询日志

# a. 查询慢查询日志信息
show variables like 'slow_query_log%';
# 查询默认的慢查询时间界值，一般是10S
show variables like 'long_query_time%';

# b. 在线更改，slow_query_log是否开启，slow_query_log表示慢查询日志的存储路径
set global slow_query_log=1
set global slow_query_log_file=/tmp/general_log.log

如果希望测试一个慢查询的日志，可以使用select sleep(11)命令（该命令非常有用）测试，此时在慢查询日志中就会有一个记录：

Time                 Id Command    Argument
# Time: 2023-06-28T07:24:50.372657Z
# User@Host: root[root] @  [172.14.230.1]  Id:     9
# Query_time: 11.005828  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1687937079;
select sleep(11)
LIMIT 0, 100;

Binlog

说明

在 MySQL 中，binlog（二进制日志）是一种记录数据库变更操作的日志文件。它包含了在数据库中执行的所有修改操作，例如插入、更新和删除数据的语句，以及数据库结构变更的语句（如创建表、修改表结构等）。

binlog 以二进制格式记录，可以通过解析和回放 binlog 中的日志事件来重现数据库的变更历史。它在主从复制、数据恢复和数据备份等场景中起着重要的作用，其主要用途包括：

主从复制：在主从复制中，主服务器将变更操作记录到 binlog 中，然后从服务器通过读取 binlog 并应用其中的事件来复制主服务器的操作，从而保持两个服务器之间的数据同步。
数据恢复：通过解析 binlog 中的日志事件，可以回放和还原数据库的历史状态。在某些情况下，可以使用 binlog 来恢复因误操作或故障导致的数据丢失或错误。
数据备份：binlog 可以作为增量备份的一部分，用于记录自上次备份以来的数据库变更操作。通过结合完整备份和 binlog，可以还原到任意时间点的数据库状态。
审计和故障排查：binlog 可以用于跟踪数据库的操作历史，用于审计目的或帮助排查数据库问题。

binlog 的配置和使用可以在 MySQL 的配置文件中进行设置。可以指定 binlog 文件的名称、位置、日志格式等参数。根据配置的不同，binlog 可以包含不同的事件信息，例如语句格式（记录每个 SQL 语句的文本）、行格式（记录每个行的变更）或混合格式（同时记录语句和行格式）。需要注意的是，binlog 是特定于 MySQL 数据库的功能，并且在不同的 MySQL 版本和配置中可能有所不同。

命令

binlog 目录: 一般和 mysql data directory 一起存放

某一天的日志文件: 按照时间查找

命令: mysqlbinlog mysql-bin.000050 –start-datetime=’2015-12-25 10:00:00’

undoLog

RedoLog

辅助日志

说明

mysql 中最终的三个日志分别是：bin-log、undo-log、redo-log，这三个日志是辅助 mysql 正常运行的，任何一个出现问题都会导致 mysql 无法正常运行，除此之外的几个日志都是辅助性日志（relay log 在主从复制中也是必须的）：

error log: MySQL线上MySQL由于非外在因素（断电、硬件损坏…）导致崩溃时，辅助线上排错的日志。
slow log: 系统响应缓慢时，用于定位问题SQL的日志，其中记录了查询时间较长的SQL，见 2.2 节内容。
relay log：搭建MySQL高可用热备架构时，用于同步数据的辅助日志

错误日志

通过错误日志，一方面可以用来监控MySQL的运行状态，便于预防故障、发现故障，同时也可以在出现问题时，用来辅助排查问题、修复故障，因为MySQL-Server的错误日志是默认开启的，并且无法手动关闭。

# 查看错误日志输出文件路径，对于docker环境，该值一般为stderr
show variables like 'log_error';
# 查看错误日志级别：0：仅错误，1：错误和警告，2：错误、警告和通知
show variables like 'log_error_verbosity';
# 查看日志时间戳格式：SYSTEM：使用系统时间
show variables like 'log_timestamps';

中继日志

关于中继日志和 binlog 日志在主从复制中的作用见容灾-mysql 主从配置中的说明。默认情况下，在单库中是见不到 relay log 的，该类型的日志仅存在主从架构中的从机上，主从架构中的从机，其数据基本上都是复制主机bin-log日志同步过来的，在存储到从库的 binlog（看是否开启热备，冷备可以不设置）以及落库之前，这些数据需要经过中转站，即 relay log 中。

当主机的增量数据被复制到中继日志后，从机的线程会不断从relay-log日志中读取数据并更新自身的数据，relay-log的结构和bin-log一模一样，同样存在一个xx-relaybin.index索引文件，以及多个xx-relaybin.00001、xx-relaybin.00002....数据文件。