mysql基本情况_Mysql 基础知识（上）

1. Mysql 基础知识汇总

1.1. Mysql 的数据结构

1.1.1. 什么是 B 树(B-Tree)

1970 年，R.Bayer 和 E.mccreight 提出了一种适用于外查找的树，它是一种平衡的多叉树，称为 B 树，其定义如下

1. 根结点至少有两个子女。

2. 每个中间节点都包含k-1个元素和k个孩子，其中 m/2 <= k <= m

3. 每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m

4. 所有的叶子结点都位于同一层。

5. 每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划。

b_tree1.png

1.1.2. 什么是 B+树(B+Tree)

B+ 树是一种树数据结构，是一个 n 叉树，每个节点通常有多个孩子，一棵 B+树包含根节点、内部节点和叶子节点。根节点可能是一个叶子节点，也可能是一个包含两个或两个以上孩子节点的节点。

一个 m 阶的 B+树具有如下几个特征：

1. 有k个子树的中间节点包含有k个元素(B树中是k-1个元素)，每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。

2. 所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3. 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大(或最小)元素。

b+tree.png

B+Tree 相对于 B-Tree 不同：

1. 非叶子节点只存储键值信息。

2. 所有叶子节点之间都有一个链指针。

3. 数据记录都存放在叶子节点中。

1.1.3. Mysql 为什么使用 B+树(B+Tree)作为存储的数据结构？

B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对 B 树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对 IO 读写次数就降低了。

B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

由于 B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是 B 树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以 B+树更加适合在区间查询的情况，所以通常 B+树用于数据库索引。

1.1.4. 小结

B树和B+树都是一种平衡的多叉树，根节点最少两个孩子，中间节点和叶子节点都包含k-1个元素，所有节点可以有k孩子，所有叶子节点都处于同一层。其中M/2<=k<=M,假设在一个M=3阶的B树，则有1.5<=k<=3，所以有所有节点包含的数据个数范围是(k-1)1~2,非叶子节点的孩子个数(k)2~3

B+树相对于B树来说，非叶子节点只存储键值信息，数据记录只放在叶子节点里面，叶子节点之间是一个链表

相对于B树来说，Mysql使用B+树好处是，1.非叶子节点不存数据，减少数据检索时候磁盘的IO读写次数。2.B+树查询效率更加稳定，每次查询的路径长度都一样。3.B+树所有数据都存在叶子节点中，然后叶子节点是一个链表，方便按范围查询数据。

1.1.5. 参考

1.2. Mysql 数据是如何读取、插入和删除的

1.2.1. 什么是“磁盘块”？

系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB 存储引擎中有页(Page)的概念，页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为 16KB，可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K，在 MySQL 中可通过如下命令查看页的大小：

mysql> show variables like 'innodb_page_size';

而系统一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘 I/O 次数，提高查询效率。

1.2.2. Mysql 的 B+Tree 非叶子节点有多少数据，一般有几层。

通常在 B+Tree 上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

InnoDB 存储引擎中页的大小为 16KB，一般表的主键类型为 INT(占用 4 个字节)或 BIGINT(占用 8 个字节)，指针类型也一般为 4 或 8 个字节，也就是说一个页(B+Tree 中的一个节点)中大概存储 16KB/(8B+8B)=1K 个键值(因为是估值，为方便计算，这里的 K 取值为〖10〗^3)。也就是说一个深度为 3 的 B+Tree 索引可以维护 10^3 _ 10^3 _ 10^3 = 10 亿 条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在2~4层。mysql 的 InnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘 I/O 操作。

1.2.3. B 树和 B+树是如何插入和删除数据的(可以理解 Mysql 数据删除和插入的相关操作)？

1.2.4. 小结

系统读取数据是以磁盘块为单位，Mysql的InnoDB有页的概念，默认每个页大小是16KB，按指针加BIGINT(8个字节+8个字节)算，一个节点可以存储1K个键值，也就是说一个深度为 3 的 B+Tree 索引可以维护 10^3 _ 10^3 _ 10^3 = 10 亿 条记录。实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在2~4层。mysql 的 InnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘 I/O 操作。

1.2.5 参考

1.3. Mysql 物理文件组成详解

1.3.1. 日志文件

通过 show variables like 'log_%'; 可以看到 mysql 的日志配置信息

错误日志(Error Log)

记录了 mysql 运行过程中较为严重的警告和错误信息以及 Mysql 每次启动和关闭的详细信息，利用 MySQL 的 FLUSH LOGS 命令来告诉 MySQL 备份旧日志文件并生成新的日志文件。 备份文件名以“.old”结尾。

二进制日志(binlog)：Binary Log & Binary Log Index

打开了记录的功能之后，MySQL 会将所有修改数据 库数据的 query 以二进制形式记录到日志文件中。当然，日志中并不仅限于 query 语句简单，还包括每一条 query 所执行的时间，所消耗的资源，以及相关的事务信息，所以 binlog 是事务安全的。

更新日志：update log

更新日志是 MySQL 在较老的版本上使用的，类似 binlog,但以简单文本格式记录内容

查询日志：query log

查询日志记录 MySQL 中所有的 query，开启后对性能影响比较大，一般只用于跟踪某些特殊的 sql 性能问题才会短暂打开

慢查询日志：slow query log

慢查询日志中记录的是执行时间较长的 query，慢查询日志采用的是简单的文本格式，其中记录了语句执行的时刻，执行所消耗的时间、执行用户、连接主机等，MySQL 还提 供了专门用来分析满查询日志的工具程序 mysqlslowdump

1.3.2. Binlog、Redolog、Undolog

Binlog

即二进制日志,它记录了数据库上的所有改变，并以二进制的形式保存在磁盘中；它可以用来查看数据库的变更历史、数据库增量备份和恢复、Mysql的复制(主从数据库的复制)。语句以“事件”的形式保存，它描述数据更改。

因为有了数据更新的binlog，所以可以用于实时备份，与master/slave复制。高可用与数据恢复。

恢复使能够最大可能地更新数据库，因为二进制日志包含备份后进行的所有更新。

在主复制服务器上记录所有将发送给从服务器的语句。

Binlog几种存储方式:

STATEMENT模式(SBR)，每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

ROW模式(RBR)，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

MIXED模式(MBR),以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

Redolog

记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，

但是RedoLog已经持久化。系统可以根据RedoLog的内容，将所有数据恢复到最新的状态。

InnoDB有buffer pool(简称bp)。bp是数据库页面的缓存，对InnoDB的任何修改操作都会首先在bp的page上进行，然后这样的页面将被标记为dirty并被放到专门的flush list上，后续将由master thread或专门的刷脏线程阶段性的将这些页面写入磁盘(disk or ssd)。这样的好处是避免每次写操作都操作磁盘导致大量的随机IO，阶段性的刷脏可以将多次对页面的修改merge成一次IO操作，同时异步写入也降低了访问的时延。然而，如果在dirty page还未刷入磁盘时，server非正常关闭，这些修改操作将会丢失，如果写入操作正在进行，甚至会由于损坏数据文件导致数据库不可用。为了避免上述问题的发生，Innodb将所有对页面的修改操作写入一个专门的文件，并在数据库启动时从此文件进行恢复操作，这个文件就是redo log file。这样的技术推迟了bp页面的刷新，从而提升了数据库的吞吐，有效的降低了访问时延。带来的问题是额外的写redo log操作的开销(顺序IO，当然很快)，以及数据库启动时恢复操作所需的时间。

Undo Log

Undo Log是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用UndoLog来实现多版本并发控制(简称：MVCC)。

-事务的原子性(Atomicity)

事务中的所有操作，要么全部完成，要么不做任何操作，不能只做部分操作。如果在执行的过程中发了错误，要回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过。

Binlog 和 Redolog 记录如何保持一致

MySQL为了保证master和slave的数据一致性，就必须保证binlog和InnoDB redo日志的一致性(因为备库通过二进制日志重放主库提交的事务，而主库binlog写入在commit之前，如果写完binlog主库crash，再次启动时会回滚事务。但此时从库已经执行，则会造成主备数据不一致)。所以在开启Binlog后，如何保证binlog和InnoDB redo日志的一致性呢？为此，MySQL引入二阶段提交(two phase commit or 2pc)，MySQL内部会自动将普通事务当做一个XA事务(内部分布式事物)来处理：

自动为每个事务分配一个唯一的ID(XID)。

COMMIT会被自动的分成Prepare和Commit两个阶段。

Binlog会被当做事务协调者(Transaction Coordinator)，Binlog Event会被当做协调者日志。

总结一下，基本顶多会出现下面是几种情况：

当事务在prepare阶段crash，数据库recovery的时候该事务未写入Binary log并且存储引擎未提交，将该事务rollback。

当事务在binlog阶段crash，此时日志还没有成功写入到磁盘中，启动时会rollback此事务。

当事务在binlog日志已经fsync()到磁盘后crash，但是InnoDB没有来得及commit，此时MySQL数据库recovery的时候将会读出二进制日志的Xid_log_event，然后告诉InnoDB提交这些XID的事务，InnoDB提交完这些事务后会回滚其它的事务，使存储引擎和二进制日志始终保持一致。

有了Redolog，为啥还需要Binlog呢？

redo log的大小是固定的，日志上的记录修改落盘后，日志会被覆盖掉，无法用于数据回滚/数据恢复等操作。

redo log是innodb引擎层实现的，并不是所有引擎都有。

基于以上，Binlog必不可少

binlog是server层实现的，意味着所有引擎都可以使用binlog日志

binlog通过追加的方式写入的，可通过配置参数max_binlog_size设置每个binlog文件的大小，当文件大小大于给定值后，日志会发生滚动，之后的日志记录到新的文件上。

binlog有两种记录模式，statement格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。

1.3.3. 数据文件

默认情况下，在MySQL的data目录下会有以数据库名字命名的文件夹，用来存放该数据库中各种表数据文件，不同的 MySQL 存储引擎有各自不同 的数据文件，存放位置也有区别。多数存储引擎的数据文件都存放在和 MyISAM 数据文件位 置相同的目录下，但是每个数据文件的扩展名却各不一样。如 MyISAM 用“.MYD”作为扩展 名，Innodb 用“.ibd ” ，Archive 用“.arc ” ，CSV 用“.csv” ，等等

MyISAM

.frm文件：存储与表相关的元数据(meta)信息，包括表结构的定义信息等

.MYD文件：是 MyISAM 存储引擎专用，存放 MyISAM 表的数据。每一个 MyISAM 表都会 有一个 “.MYD ” 文件与之对应

.MYI文件：专属于 MyISAM 存储引擎，主要存放MyISAM表的索引相关信息

Innodb

.ibd文件和ibdata文件：存放Innodb数据的文件，Innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。

独享表空间存储方式使用.ibd文件，每个表一个".ibd"文件

共享存储表空间则使用ibdata文件来存放，所有表共同使用一个(或多个，可自行配置)ibdata文件

1.3.4. Replication 文件

master.info:存在于Slave端的数据目录，存放了该Slave的Master端的相关信息，包括Master的主机地址，连接用户，连接端口，当前日志位置，已经读取到的日志位置等

relay log和 relay log index

sql-relay-bin.xxxxxn 文件用于存放 Slave 端的 I/O 线程从 Master 端所读取到的 Binary Log 信息，然后由 Slave 端的 SQL 线程从该 relay log 中读取并解析相应的日志信息，转化成 Master 所执行的 SQL 语句，然后在 Slave 端应用。

mysql-relay-bin.index 文件的功能类似于 mysql-bin.index ，同样是记录日志的存放位置的绝对路径，只不过他所记录的不是 Binary Log，而是 Relay Log。

relay-log.info 文件:类似于master.info,他存放通过Slave的I/O线程写入到本地的relay log的相关信息，供Slave端的SQL线程以及某些管理操作随时能够获取当前复制的相关信息

1.3.5. 系统配置文件文件

system config file(my.cnf):包含多重参数选项组(group),每一种参数组都通过中括号给定了固定的组名，常见的有:

[mysqld]：包括了 mysqld 服务启动时候的初始化参数

[client]：包含客户端工具程序可以读取的参数

1.3.6. 小结

每个MyISAM在磁盘上存储成三个文件。1).frm 用于存储表的定义。2).MYD 用于存放数据。 3).MYI 用于存放表索引。InnoDB 的数据文件本身就是索引文件，所有的表都保存在同一个数据文件中(也可能是多个文件，或者是独立的表空间文件)

binlog是MySQL Server层记录的日志， redo log是InnoDB存储引擎层的日志。 两者都是记录了某些操作的日志(不是所有)自然有些重复(但两者记录的格式不同)，两者通过XA事务来保持一致。

1.3.7. 参考