标签：log MySQL 基础 索引 SQL 日志 数据

MySQL基础

MySQL的整体架构

从上往下看，依次会分为网络连接层、系统服务层、存储引擎层、以及文件系统层，往往编写SQL后，都会遵守着MySQL的这个架构往下走。

• 连接层：主要是指数据库连接池，会负责处理所有客户端接入的工作。
• 服务层：主要包含SQL接口、解析器、优化器以及缓存缓冲区四块区域。
• 存储引擎层：这里是指MySQL支持的各大存储引擎，如InnoDB、MyISAM等。
• 文件系统层：涵盖了所有的日志，以及数据、索引文件，位于系统硬盘上。

网络连接层

MySQL与客户端之间会采用半双工的通讯机制工作，与之对应的还有“全双工、单工”的工作模式：

• 全双工：代表通讯的双方在同一时间内，即可以发送数据，也可以接收数据。
• 半双工：代表同一时刻内，单方要么只能发送数据，要么只能接受数据。
• 单工：当前连接只能发送数据或只能接收数据，也就是“单向类型的通道”。

MySQL也会“安排”一条线程维护当前客户端的连接，这条线程也会时刻标识着当前连接在干什么工作，可以通过show processlist;命令查询所有正在运行的线程。

数据库连接池

show VARIABLES LIKE '%MAX_CONNECTIONS';查看当前DB的最大连接数。

set GLOBAL max_connections = 200; 修改数据库的最大连接数。

show status like 'Thread%'; 对客户端的连接数的统计。

系统服务层

1. Sql接口：会作为客户端连接传递SQL语句时的入口，并且作为数据库返回数据时的出口。
2. 解析器：作用主要是做词法分析、语义分析、语法树生成
3. 优化器：生成执行计划，比如选择最合适的索引，选择最合适的join方式等。
4. 缓存&缓冲
   • show GLOBAL VARIABLES like '%query_cache_type%'; 是否开启
   • show GLOBAL VARIABLES like '%query_cache_size%'; 缓存大小

存储引擎层

存储引擎是MySQL数据库中与磁盘文件打交道的子系统，不同的引擎底层访问文件的机制也存在些许细微差异，引擎也不仅仅只负责数据的管理，也会负责库表管理、索引管理等，MySQL中所有与磁盘打交道的工作，最终都会交给存储引擎来完成。

文件系统层

日志模块

①binlog二进制日志，主要记录MySQL数据库的所有写操作（增删改）。

②redo-log重做/重写日志，MySQL崩溃时，对于未落盘的操作会记录在这里面，用于重启时重新落盘（InnoDB专有的）。

③undo-logs撤销/回滚日志：记录事务开始前[修改数据]的备份，用于回滚事务。

④error-log：错误日志：记录MySQL启动、运行、停止时的错误信息。

⑤general-log常规日志，主要记录MySQL收到的每一个查询或SQL命令。

⑥slow-log：慢查询日志，主要记录执行时间较长的SQL。

⑦relay-log：中继日志，主要用于主从复制做数据拷贝。

数据模块

db.opt文件：主要记录当前数据库使用的字符集和验证规则等信息。

.frm文件：存储表结构的元数据信息文件，每张表都会有一个这样的文件。

.MYD文件：用于存储表中所有数据的文件（MyISAM引擎独有的）。

.MYI文件：用于存储表中索引信息的文件（MyISAM引擎独有的）。

.ibd文件：用于存储表数据和索引信息的文件（InnoDB引擎独有的）。

.ibdata文件：用于存储共享表空间的数据和索引的文件（InnoDB引擎独有）。

.ibdata1文件：这个主要是用于存储MySQL系统（自带）表数据及结构的文件。

.ib_logfile0/.ib_logfile1文件：用于故障数据恢复时的日志文件。

.cnf/.ini：MySQL的配置文件，Windows下是.ini，其他系统大多为.cnf。

一条SQL的执行流程

读sql：

①先将SQL发送给SQL接口，SQL接口会对SQL语句进行哈希处理。

②SQL接口在缓存中根据哈希值检索数据，如果缓存中有则直接返回数据。

③缓存中未命中时会将SQL交给解析器，解析器会判断SQL语句是否正确：

• 错误：抛出1064错误码及相关的语法错误信息。
• 正确：将SQL语句交给优化器处理，进入第④步。

④优化器根据SQL制定出不同的执行方案，并择选出最优的执行计划。

⑤工作线程根据执行计划，调用存储引擎所提供的API获取数据。

⑥存储引擎根据API调用方的操作，去磁盘中检索数据（索引、表数据....）。

⑦发生磁盘IO后，对于磁盘中符合要求的数据逐条返回给SQL接口。

⑧SQL接口会对所有的结果集进行处理（剔除列、合并数据....）并返回。

写sql：

①先将SQL发送给SQL接口，SQL接口会对SQL语句进行哈希处理。

②在缓存中根据哈希值检索数据，如果缓存中有则将对应表的所有缓存全部删除。

③经过缓存后会将SQL交给解析器，解析器会判断SQL语句是否正确：

• 错误：抛出1064错误码及相关的语法错误信息。
• 正确：将SQL语句交给优化器处理，进入第④步。

④优化器根据SQL制定出不同的执行方案，并择选出最优的执行计划。

⑤在执行开始之前，先记录一下undo-log日志和redo-log(prepare状态)日志。

⑥在缓冲区中查找是否存在当前要操作的行记录或表数据（内存中）：

• 存在：
  • ⑦直接对缓冲区中的数据进行写操作。
  • ⑧然后利用Checkpoint机制刷写到磁盘。
• 不存在：
  • ⑦根据执行计划，调用存储引擎的API。
  • ⑧发生磁盘IO，对磁盘中的数据做写操作。

⑨写操作完成后，记录bin-log日志，同时将redo-log日志中的记录改为commit状态。

⑩将SQL执行耗时及操作成功的结果返回给SQL接口，再由SQL接口返回给客户端。

日志：

大部分日志记录也是采用先写到缓冲区中，然后再异步刷写到磁盘中。

1. 那内存中的日志数据何时会刷写到磁盘呢？对于这点则是由刷盘策略来决定的，redo-log日志的刷盘策略由innodb_flush_log_at_trx_commit参数控制，而bin-log日志的刷盘策略则可以通过sync_binlog参数控制：

• innodb_flush_log_at_trx_commit
  

  • 0：有事务提交的情况下，每间隔一秒时间刷写一次日志到磁盘。
  • 1：每次提交事务时，都刷写一次日志到磁盘（性能最差，最安全，默认策略）。
  • 2：每当事务提交时，把日志记录放到内核缓冲区，刷写的时机交给OS控制（性能最佳）。

• sync_binlog
  

  • 0：同上述innodb_flush_log_at_trx_commit参数的2，交给OS控制（默认）。
  • 1：同上述innodb_flush_log_at_trx_commit参数的1，每次提交事务都会刷盘。

数据库表设计

三大范式

• 第一范式：原子性，每个字段的值不能再分。

• 第二范式：唯一性，表内每行数据必须描述同一业务属性的数据。

• 第三范式：独立性，表中每个非主键字段之间不能存在依赖性。

• 巴斯范式：主键字段独立性，联合主键字段之间不能存在依赖性。

• 第四范式：表中字段不能存在多值依赖关系。

• 第五范式：表中字段的数据之间不能存在连接依赖关系。

• 域键范式：试图研究出一个库表设计时的终极完美范式。

索引

分类

1. 数据结构层次：B+Tree，B-Tree，Hash，R-Tree；

2. 字段数量：

   • 单列索引：唯一索引，主键索引，普通索引等。

   • 多列索引：联合索引等。

   • 前缀索引。

3. 功能层次：

   • 普通索引
   • 唯一索引
   • 主键索引
   • 全文索引
   • 空间索引

4. 存储方式：

   • 聚簇索引：索引数据和表数据在磁盘中的位置是一起的
   • 非聚簇索引：

各类索引的优劣分析

• 主键索引为什么使用自增ID（一般）；
• 联合索引命中率
• 唯一索引的快慢

如何创建索引

• ①经常频繁用作查询条件的字段应酌情考虑为其创建索引。
• ②表的主外键或连表字段，必须建立索引，因为能很大程度提升连表查询的性能。
• ③建立索引的字段，一般值的区分性要足够高，这样才能提高索引的检索效率。
• ④建立索引的字段，值不应该过长，如果较长的字段要建立索引，可以选择前缀索引。
• ⑤建立联合索引，应当遵循最左前缀原则，将多个字段之间按优先级顺序组合。
• ⑥经常根据范围取值、排序、分组的字段应建立索引，因为索引有序，能加快排序时间。
• ⑦对于唯一索引，如果确认不会利用该字段排序，那可以将结构改为Hash结构。
• ⑧尽量使用联合索引代替单值索引，联合索引比多个单值索引查询效率要高。

同时，除开上述一些建立索引的原则外，在建立索引时还需有些注意点：

• ❶值经常会增删改的字段，不合适建立索引，因为每次改变后需维护索引结构。
• ❷一个字段存在大量的重复值时，不适合建立索引，比如之前举例的性别字段。
• ❸索引不能参与计算，因此经常带函数查询的字段，并不适合建立索引。
• ❹一张表中的索引数量并不是越多越好，一般控制在3，最多不能超过5。
• ❺建立联合索引时，一定要考虑优先级，查询频率最高的字段应当放首位。
• ❻当表的数据较少，不应当建立索引，因为数据量不大时，维护索引反而开销更大。
• ❼索引的字段值无序时，不推荐建立索引，因为会造成页分裂，尤其是主键索引。

索引失效问题

1. sql语句查询条件带有‘OR’
2. sql语句中含有计算
3. 模糊查询like前加上了%
4. 联合索引未满足最左匹配原则
5. 反向的范围操作导致的索引失效。例如：not in，not like 等

索引下推

就是将Server层筛选数据的工作，下推到引擎层处理。

为什么是B+Tree

1. 二叉树不适合作为索引结构的原因：

   • ①如果索引的字段值是按顺序增长的，二叉树会转变为链表结构。
   • ②由于结构转变成了链表结构，因此检索的过程和全表扫描无异。
   • ③由于树结构在磁盘中，各节点的数据并不连续，因此无法利用局部性原理。

2. 红黑树不适合作为索引结构的原因：

   • ①虽然对比二叉树来说，树高有所降低，但数据量一大时，依旧会有很大的高度。
   • ②每个节点中只存储一个数据，节点之间还是不连续的，依旧无法利用局部性原理。

3. B树不适合作为索引结构的原因：

   • 虽然对比之前的红黑树更矮，检索数据更快，也能够充分利用局部性原理减少IO次数，但对于大范围查询的需求，依旧需要通过多次磁盘IO来检索数据。

MySQL事务

数据库的事务一般也要求满足ACID原则，ACID是关系型数据库实现事务机制时必须要遵守的原则。

ACID主要涵盖四条原则，即：

• A/Atomicity：原子性
• C/Consistency：一致性
• I/Isolation：独立性/隔离性
• D/Durability：持久性

1.0 事务回滚点

假设目前有一个事务，由很多条SQL组成，但是我想让其中一部分执行成功后，就算后续SQL执行失败也照样提交，这样可以做到吗？从前面的理论上来看，一个事务要么全部执行成功，要么全部执行失败，似乎做不到啊，但实际上是可以做到的，这里需要利用事务的回滚点机制

在某些SQL执行成功后，但后续的操作有可能成功也有可能失败，但不管成功亦或失败，你都想让前面已经成功的操作生效时，此时就可在当前成功的位置设置一个回滚点。当后续操作执行失败时，就会回滚到该位置，而不是回滚整个事务中的所有操作，这个机制则称之为事务回滚点。

在MySQL中提供了两个关于事务回滚点的命令：

• savepoint point_name：添加一个事务回滚点
• rollback to point_name：回滚到指定的事务回滚点

2.0 MySQL事务的隔离机制

在MySQL中，事务隔离机制分为了四个级别：

• ①Read uncommitted/RU：读未提交
• ②Read committed/RC：读已提交
• ③Repeatable read/RR：可重复读
• ④Serializable：序列化/串行化

2.1、脏读、幻读、不可重复读问题

• 脏读：意思是指一个事务读到了其他事务还未提交的数据
• 不可重复读：在一个事务中，多次读取同一数据，先后读取到的数据不一致。（修改）
• 幻读：指同一个事务内多次查询返回的结果集不一样。（增加）

2.2、事务的四大隔离级别

• ①读未提交：这种隔离级别是基于「写互斥锁」实现的，当一个事务开始写某一个数据时，另外一个事务也来操作同一个数据，此时为了防止出现问题则需要先获取锁资源，只有获取到锁的事务，才允许对数据进行写操作，同时获取到锁的事务具备排他性/互斥性，也就是其他线程无法再操作这个数据。但读操作却并不是互斥的，也就是当一个事务在写某个数据时，就算没有提交事务，其他事务来读取该数据时，也可以读到未提交的数据。

• ②读已提交：在这个隔离级别中，对于写操作同样会使用「写互斥锁」，也就是两个事务操作同一数据时，会出现排他性，而对于读操作则使用了一种名为MVCC多版本并发控制的技术处理，也就是有事务中的SQL需要读取当前事务正在操作的数据时，MVCC机制不会让另一个事务读取正在修改的数据，而是读取上一次提交的数据（也就是读原本的老数据）。

• ③可重复读：在可重复读级别中，是在一个事务只有第一次执行查询会创建一个ReadView，在这个事务的生命周期内，所有的查询都会从这一个ReadView中读取数据，从而确保了一个事务中多次读取相同数据是一致的，也就是解决了不可重复读问题。

• ④序列化/串行化：是将所有的事务按序排队后串行化处理，也就是操作同一张表的事务只能一个一个执行，事务在执行前需要先获取表级别的锁资源，拿到锁资源的事务才能执行，其余事务则陷入阻塞，等待当前事务释放锁。

2.3.3、事务隔离机制的命令

简单认识MySQL事务隔离机制后，接着来看看一些关于事务隔离机制的命令：

-- 方式①：查询当前数据库的隔离级别
SELECT @@tx_isolation;
-- 方式②：查询当前数据库的隔离级别
show variables like '%tx_isolation%';

-- 设置隔离级别为RU级别（当前连接生效）
set transaction isolation level read uncommitted;
-- 设置隔离级别为RC级别（全局生效）
set global transaction isolation level read committed;
-- 设置隔离级别为RR级别（当前连接生效）
-- 这里和上述的那条命令作用相同，是第二种设置的方式
set tx_isolation = 'repeatable-read';
-- 设置隔离级别为最高的serializable级别（全局生效）
set global.tx_isolation = 'serializable'; 

3.0 事务实现原理

MySQL的事务机制是基于日志实现的

undo-log：主要记录SQL的撤销日志，比如目前是insert语句，就记录一条delete日志。

redo-log：记录当前SQL归属事务的状态，以及记录修改内容和修改页的位置。

bin-log：记录每条SQL操作日志，主要是用于数据的主从复制与数据恢复/备份。

MySQL锁

1.1、MySQL锁机制的分类

MySQL的锁机制与索引机制类似，都是由存储引擎负责实现的，这也就意味着不同的存储引擎，支持的锁也并不同，这里是指不同的引擎实现的锁粒度不同。但除开从锁粒度来划分锁之外，其实锁也可以从其他的维度来划分，因此也会造出很多关于锁的名词，下面先简单梳理一下MySQL的锁体系：

• 以锁粒度的维度划分：
  • ①表锁：
    • 全局锁：加上全局锁之后，整个数据库只能允许读，不允许做任何写操作。
    • 元数据锁 / MDL锁：基于表的元数据加锁，加锁后整张表不允许其他事务操作。
    • 意向锁：这个是InnoDB中为了支持多粒度的锁，为了兼容行锁、表锁而设计的。
    • 自增锁 / AUTO-INC锁：这个是为了提升自增ID的并发插入性能而设计的。
  • ②页面锁
  • ③行锁：
    • 记录锁 / Record锁：也就是行锁，一条记录和一行数据是同一个意思。
    • 间隙锁 / Gap锁：InnoDB中解决幻读问题的一种锁机制。
    • 临建锁 / Next-Key锁：间隙锁的升级版，同时具备记录锁+间隙锁的功能。
• 以互斥性的维度划分：
  • 共享锁 / S锁：不同事务之间不会相互排斥、可以同时获取的锁。
  • 排他锁 / X锁：不同事务之间会相互排斥、同时只能允许一个事务获取的锁。
  • 共享排他锁 / SX锁：MySQL5.7版本中新引入的锁，主要是解决SMO带来的问题。
• 以操作类型的维度划分：
  • 读锁：查询数据时使用的锁。
  • 写锁：执行插入、删除、修改、DDL语句时使用的锁。
• 以加锁方式的维度划分：
  • 显示锁：编写SQL语句时，手动指定加锁的粒度。
  • 隐式锁：执行SQL语句时，根据隔离级别自动为SQL操作加锁。
• 以思想的维度划分：
  • 乐观锁：每次执行前认为自己会成功，因此先尝试执行，失败时再获取锁。
  • 悲观锁：每次执行前都认为自己无法成功，因此会先获取锁，然后再执行。

放眼望下来，是不是看着还蛮多的，但总归说来说去其实就共享锁、排他锁两种，只是加的方式不同，加的地方不同，因此就演化出了这么多锁的称呼。

MVCC机制

MVCC机制的全称为Multi-Version Concurrency Control，即多版本并发控制技术，主要是为了提升数据库并发性能而设计的，其中采用更好的方式处理了读-写并发冲突，做到即使有读写冲突时，也可以不加锁解决，从而确保了任何时刻的读操作都是非阻塞的。

MVCC机制在MySQL中，仅有InnoDB引擎支持，而在该引擎中，MVCC机制只对RC、RR两个隔离级别下的事务生效。当然，RC、RR两个不同的隔离级别中，MVCC的实现也存在些许差异。

实现原理

1. 隐藏字段：隐藏主键，删除标识，最近更新事务id，回滚指针。
2. undo-log日志，记录旧数据，由指针连接
3. ReadView（当前事务id，活跃事务id列表，最小活跃事务id，下一个要分配的事务id）

具体实现

• RU：一个事务可以读到其他事务未提交的数据，但同时要求解决脏写（更新覆盖）问题。
  • 即写操作加排他锁，读操作不加锁！
• RC：事务中，不允许读另一个事务还未提交的数据。
  • 写操作会加排他锁，而读操作会使用MVCC机制。每次select时都会生成ReadView快照。
• RR：解决不可重复读问题
  • 写操作加排他锁，对读操作依旧采用MVCC机制，但RR级别中，一个事务中只有首次select会生成ReadView快照。
• Serializable：串行化，解决幻读问题
  • 所有写操作加临键锁（具备互斥特性），所有读操作加共享锁。

日志

一、Undo-log 回滚日志

事务回滚：当一条写入类型的SQL执行时，都会记录Undo-log日志，会生成相应的反SQL放入到Undo-log中。

MVCC机制：Undo-log中记录的旧数据并不仅仅只有一条，一条相同的行数据可能存在多条不同版本的Undo记录，内部会通过roll_ptr回滚指针，组成一个单向链表，而这个链表则被称之为Undo版本链。

当一条写SQL执行时，不会直接去往磁盘中的xx.ibdata文件写数据，而是会写在undo_log_buffer缓冲区中。

Undo-log相关的参数

最后再来看看关于Undo-log的一些参数，其实在MySQL5.5之前没有太多参数，如下：

• innodb_max_undo_log_size：本地磁盘文件中，Undo-log的最大值，默认1GB。
• innodb_rollback_segments：指定回滚段的数量，默认为1个。

除开上述两个参数外，其他参数基本上是在MySQL5.6才有的，如下：

• innodb_undo_directory：指定Undo-log的存放目录，默认放在.ibdata文件中。
• innodb_undo_logs：指定回滚段的数量，默认为128个，也就是之前的innodb_rollback_segments。
• innodb_undo_tablespaces：指定Undo-log分成几个文件来存储，必须开启innodb_undo_directory参数。
• innodb_undo_log_truncate：是否开启Undo-log的在线压缩功能，即日志文件超过大小一半时自动压缩，默认OFF关闭。

在MySQL5.5版本以后，Undo-log日志支持单独存放，并且多出了几个参数可以调整Undo-log的区域。

二、Redo-log 重做日志

Redo-log则用来实现数据的恢复。

工作线程执行SQL前，写的Redo-log日志，也是写在了内存中的redo_log_buffer缓冲区。

刷盘策略

• redo-log日志的刷盘策略由innodb_flush_log_at_trx_commit参数控制：

  0：有事务提交的情况下，每间隔一秒时间刷写一次日志到磁盘。

  1：每次提交事务时，都刷写一次日志到磁盘（性能最差，最安全，默认策略）。

  2：每当事务提交时，把日志记录放到内核缓冲区，刷写的时机交给OS控制（性能最佳）。

Redo-log相关的参数

这里也列举出几个Redo-log日志中，较为重要的系统参数：

• innodb_flush_log_at_trx_commit：设置redo_log_buffer的刷盘策略，默认每次提交事务都刷盘。
• innodb_log_group_home_dir：指定redo-log日志文件的保存路径，默认为./。
• innodb_log_buffer_size：指定redo_log_buffer缓冲区的大小，默认为16MB。
• innodb_log_files_in_group：指定redo日志的磁盘文件个数，默认为2个。
• innodb_log_file_size：指定redo日志的每个磁盘文件的大小限制，默认为48MB。

其中主要讲一下Redo-log的本地磁盘文件个数，为啥默认是两个呢？因为MySQL通过来回写这两个文件的形式记录Redo-log日志，用两个日志文件组成一个“环形”，如下：

先来简单解释一下图中存在的两根指针：

• write pos：这根指针用来表示当前Redo-log文件写到了哪个位置。
• check point：这根指针表示目前哪些Redo-log记录已经失效且可以被擦除（覆盖）。

两根指针中间区域，也就是图中的红色区域，代表是可以写入日志记录的可用空间，而蓝色区域则表示日志落盘但数据还未落盘的记录，这句话怎么理解呢？

当一个事务写了redo-log日志、并将数据写入缓冲区后，但数据还未写到本地的表数据文件中，此时这个事务对应的redo-log记录就为上图中的蓝色，而当一个事务所写的数据也落盘后，对应的redo-log记录就会变为红色。

当write pos指针追上check point指针时，红色区域就会消失，也就代表Redo-log文件满了，再当MySQL执行写操作时就会被阻塞，因为无法再写入redo-log日志了，所以会触发checkpoint刷盘机制，将redo-log记录对应的事务数据，全部刷写到磁盘中的表数据文件后，阻塞的写事务才能继续执行。

触发checkpoint刷盘机制后，随着数据的落盘，check point指针也会不断的向后移动，红色区域也会不断增长，因此阻塞的写事务才能继续执行。

OK~，再补齐一些关于checkpoint机制的系统参数：

• innodb_log_write_ahead_size：设置checkpoint刷盘机制每次落盘动作的大小，默认为8K，如果你要设置，必须要为4k的整数倍。
• innodb_log_compressed_pages：是否对Redo日志开启页压缩机制，默认ON，这跟InnoDB的页压缩技术有关。
• innodb_log_checksums：Redo日志完整性效验机制，默认开启，必须要开启，否则有可能刷写数据时，只刷一半，出现类似于“网络粘包”的问题。

Redo-log的两阶段提交

如果redo-log只写一次，那不管谁先写（与bin-log日志），都有可能造成主从同步数据时的不一致问题出现，为了解决该问题，redo-log就被设计成了两阶段提交模式，设置成两阶段提交后，整个执行过程有三处崩溃点：

• redo-log(prepare)：在写入准备状态的redo记录时宕机，事务还未提交，不会影响一致性。
• bin-log：在写bin记录时崩溃，重启后会根据redo记录中的事务ID，回滚前面已写入的数据。
• redo-log(commit)：在bin-log写入成功后，写redo(commit)记录时崩溃，因为bin-log中已经写入成功了，所以从机也可以同步数据，因此重启时直接再次提交事务，写入一条redo(commit)记录即可。

通过这种两阶段提交的方案，就能够确保redo-log、bin-log两者的日志数据是相同的，bin-log中有的主机再恢复，如果bin-log没有则直接回滚主机上写入的数据，确保整个数据库系统的数据一致性。

三、bin-log 变更日志

Bin-log日志也被称之为二进制日志，作用与Redo-log类似，主要是记录所有对数据库表结构变更和表数据修改的操作，对于select、show这类读操作并不会记录。bin-log是MySQL-Server级别的日志，也就是所有引擎都能用的日志，而redo-log、undo-log都是InnoDB引擎专享的，无法跨引擎生效。

3.1、bin-log的缓冲区

为啥要单独把bin-log的缓冲区拎出来讲呢？因为它跟redo-log、undo-log的缓冲区并不同，前面的两种日志缓冲区，都位于InnoDB创建的共享BufferPool中，而bin_log_buffer是位于每条线程中的。

3.2、Bin-log本地日志文件的格式

日志记录共有Statment、Row、Mixed三种格式。

Statment：每一条会对数据库产生变更的SQL语句都会记录到bin-log中。

Row：这种模式就是为了解决Statment模式的缺陷，Row模式中不再记录每条造成变更的SQL语句，而是记录具体哪一个分区中的、哪一个页中的、哪一行数据被修改了。

Mixed：这种被称为混合模式，即Statment、Row的结合版，因为Statment模式会导致数据出现不一致，而Row模式数据量又会很大，因此Mixed模式结合了两者的优劣势，对于可以复制的SQL采用Statment模式记录，对于无法复制的SQL采用Row记录。

3.3、Redo-log、Bin-log两者的区别

对于Redo-log、Bin-log两者的区别，主要可以从四个维度上来说：

• ①生效范围不同，Redo-log是InnoDB专享的，Bin-log是所有引擎通用的。
• ②写入方式不同，Redo-log是用两个文件循环写，而Bin-log是不断创建新文件追加写。
• ③文件格式不同，Redo-log中记录的都是变更后的数据，而Bin-log会记录变更SQL语句。
• ④使用场景不同，Redo-log主要实现故障情况下的数据恢复，Bin-log则用于数据灾备、同步。

3.4、bin-log相关的参数

• log_bin：是否开启bin-log日志，默认ON开启，表示会记录变更DB的操作。
• log_bin_basename：设置bin-log日志的存储目录和文件名前缀，默认为./bin.0000x。
• log_bin_index：设置bin-log索引文件的存储位置，因为本地有多个日志文件，需要用索引来确定目前该操作的日志文件。
• binlog_format：指定bin-log日志记录的存储方式，可选Statment、Row、Mixed。
• max_binlog_size：设置bin-log本地单个文件的最大限制，最多只能调整到1GB。
• binlog_cache_size：设置为每条线程的工作内存，分配多大的bin-log缓冲区。
• sync_binlog：控制bin-log日志的刷盘频率。
• binlog_do_db：设置后，只会收集指定库的bin-log日志，默认所有库都会记录。

四、其他日志

error-log：MySQL线上MySQL由于非外在因素（断电、硬件损坏...）导致崩溃时，辅助线上排错的日志。涵盖了MySQL-Server的启动、停止运行的时间，以及报错的诊断信息，也包括了错误、警告和提示等多个级别的日志详情。

slow-log：系统响应缓慢时，用于定位问题SQL的日志，其中记录了查询时间较长的SQL。

• slow_query_log：设置是否开启慢查询日志，默认OFF关闭。
• slow_query_log_file：指定慢查询日志的存储目录及文件名。
• 通过开启和分析general log（查询日志） 来设置long_query_time参数，指定查询SQL的阈值（即sql查询时间超过多久认为是慢查询）。

relay-log：搭建MySQL高可用热备架构时，用于同步数据的辅助日志。 relay log在单库中是见不到的，该类型的日志仅存在主从架构中的从机上，主从架构中的从机，其数据基本上都是复制主机bin-log日志同步过来的，而从主机复制过来的bin-log数据放在哪儿呢？也就是放在relay-log日志中，中继日志的作用就跟它的名字一样，仅仅只是作为主从同步数据的“中转站”。

MySQL的内存

1.1、MySQL Server - 工作组件

数据库的连接池中，存的到底是什么？存的实际上就是数据库连接对象，MySQL内部的连接对象，其中包含了客户端连接信息，如客户端IP、登录的用户、所连接的DB....等这类信息，同时这些连接对象在内部会绑定一条工作线程，因此你也可以将它理解成是一个线程池！MySQL复用连接的本质，实则是在复用线程，出现一个新的客户端连接时，首先会根据客户端信息为其创建连接对象，然后再复用连接池中的空闲线程。

1.2、工作线程的本地内存

工作线程的本地内存区域，也被称之为线程私有区，即MySQL在创建每条线程时，都会为其分配这些内存：

thread_stack：线程堆栈，主要用于暂时存储运行的SQL语句及运算数据，和Java虚拟机栈类似。

sort_buffer：排序缓冲区，执行排序SQL时，用于存放排序后数据的临时缓冲区。

join_buffer：连接缓冲区，做连表查询时，存放符合连表查询条件的数据临时缓冲区。

read_buffer：顺序读缓冲区，MySQL磁盘IO一次读一页数据，这个是顺序IO的数据临时缓冲区。

read_rnd_buffer：随机读缓冲区，当基于无序字段查询数据时，这里存放随机读到的数据。

net_buffer：网络连接缓冲区，这里主要是存放当前线程对应的客户端连接信息。

tmp_table：内存临时表，当SQL中用到了临时表时，这里存放临时表的结构及数据。

bulk_insert_buffer：MyISAM批量插入缓冲区，批量insert时，存放临时数据的缓冲区。

bin_log_buffer：bin-log日志缓冲区，bin-log的缓冲区被设计在工作线程的本地内存中。

1.3、MySQL共享内存区

Key Buffer：MyISAM表的索引缓冲区，提升MyISAM表的索引读写速度。

Query Cache：查询缓存区，缓冲SQL的查询结果，提升热点SQL的数据检索效率。

Thread Cache：线程缓存区，存放工作线程运行期间，一些需要被共享的临时数据。

Table Cache：表数据文件的文件描述符缓存，提升数据表的打开效率。

Table Definition Cache：表结构文件的文件描述符缓存，提升结构表的打开效率。

1.4、存储引擎缓冲区

Data Page：写入缓冲区，主要用来缓冲磁盘的表数据，将写操作转移到内存进行。

Index Page：索引缓冲页，对于所有已创建的索引根节点，都会放入到内存，提升索引效率。

Lock Space：锁空间，主要是存放所有创建出的锁对象。

Dict Info：数据字典，主要用来存储MySQL-InnoDB引擎自带的系统表。

redo_log_buffer：redo-log缓冲区，存放写SQL执行时写入的redo记录。

undo_log_buffer：undo-log缓冲区，存放写SQL执行时写入的undo记录。

Adaptivity Hash：自适应哈希索引，InnoDB会为热点索引页，创建相应的哈希索引。

Insert Buffer：写入缓冲区，对于insert的数据，会先放在这里，然后定期刷写磁盘。

Lru List：内存淘汰页列表，对于整个缓冲池的内存管理列表。

Free List：空闲内存列表，这里面记录着目前未被使用的内存页。

Flush List：脏页内存列表，这里主要记录未落盘的数据。

虽然MySQL是基于磁盘存储数据的，但总不能每次读写操作都走磁盘吧？这样绝对会导致资源开销极大，同时性能也极低，因此各引擎都在内存中设计了一个缓冲池，用来提升数据库整体的读写性能。

二、InnoDB的核心 - Buffer Pool

在MySQL5.6版本以下，默认大小为42MB，而MySQL5.6以后的版本中，默认大小为128MB，这块内存是MySQL启动时向OS申请的一块连续空间。当然，我们也可以手动调整innodb_buffer_pool_size参数来控制，一般建议设置为机器内存的60~80%。

2.1、数据页（Data Page）

InnoDB引擎为了方便读取，会将磁盘中的数据划分为一个个的「页」，每个页的默认大小为16KB，以页作为内存和磁盘交互的基本单位，而InnoDB的缓冲池也会以页作为单位，也就意味着：当InnoDB拿到申请的连续内存后，会按照16KB的尺寸将整块空间，划分成一个个的缓冲页。

2.2、索引缓冲页（Index Page）

Buffer Pool中有一块专门的区域：Index Page，专门用来存放载入的索引数据，存储这些数据的缓冲页，则被称之为索引页。随着运行时间的增长，也会将一些非根节点的索引页载入内存中，这是一种对于访问频率较高的索引页，专门推出的优化机制。

2.3、日志缓冲区（Log Buffer）

InnoDB的缓冲池中，主要存在两个日志缓冲区，即undo_log_buffer、redo_log_buffer，分别对应着撤销日志和重做日志，它俩的作用主要是用来提升日志记录的写入速度，因为日志文件在磁盘中，执行SQL时直接往磁盘写日志，其效率太低了，因此会先写缓冲区，再由后台线程去刷写日志。

三、InnoDB缓冲池的内存是如何管理的

InnoDB虽然在启动时，会将连续的内存划分为一块块的缓冲页，但这仅是逻辑上的划分，本质上所有的缓冲页之间，也是连续的内存。但随着MySQL在线上运行的时间越来越长，自然会导致这片连续的缓冲页变得七零八落，如下：

当从磁盘加载一个数据页时，总不能将所有的缓冲页全部遍历一次，然后找到其中的空闲页放数据吧？这样难免有些影响性能，所以为了更好的管理缓冲池，InnoDB会为每个缓冲页创建一个控制块。

3.1、缓冲页的控制块是是个啥？

控制块是专门用于管理缓冲页而设计的一种结构，其中会包含：数据页所属的表空间、页号、缓冲页地址、链表节点指针等信息，所有的控制块都会放在缓冲池最前面，如下：

当然，控制块也会占用缓冲池的内存空间，InnoDB会为每一个缓冲页都分配一个对应的控制块，后续InnoDB可以基于控制块去管理每一块缓冲页。

3.2、空闲页的管理

对于空闲缓冲页的管理，为了能够更快的找到缓冲池中的空闲页，InnoDB会以控制块作为节点，将所有空闲的缓冲页组成一个空闲链表，也就是之前的Free链表，

3.3、标记页的管理

InnoDB同样又创造了一个Flush链表，它的结构和Free链表一模一样。

3.4、内存中的数据页是如何淘汰的？

末尾淘汰机制: LRU，

造成的问题：

• ①利用局部性原理预读失效时，会导致数据页常驻缓冲区。（分区，young和old）
• ②查询数据量过大时，会导致缓冲区中的热点数据全部被替换，导致缓冲池被“污染”。（晋升条件）。

Innodb 与 MyISAM引擎对比

①存储方式：MyISAM引擎会将表数据和索引数据分成两个文件存储。

②索引支持：因为MyISAM引擎的表数据和索引数据是分开的，因此不支持聚簇索引。

③事务支持：由于MyISAM引擎没有undo-log日志，所以不支持多条SQL组成事务并回滚。

④故障恢复：MyISAM引擎依靠bin-log日志实现，bin-log中未写入的数据会永久丢失。

⑤锁粒度支持：因为MyISAM不支持聚簇索引，因此无法实现行锁，所有并发操作只能加表锁。

⑥并发性能：MyISAM引擎仅支持表锁，所以多条线程出现读-写并发场景时会阻塞。

⑦内存利用度：MyISAM引擎过于依赖MySQL Server，对缓冲池、异步IO技术开发度不够。

标签：log,MySQL,基础,索引,SQL,日志,数据
From： https://www.cnblogs.com/strind/p/18428416