面试专题--Mysql体系

指针

慢查询日志（slow query log）

2

9

6

7

16个节点

Management Serveices & Utilities

1

InnoDB

男，12

font color=\"#e74f4c\

数据页 16kb

3

段

21

5

1170个节点

Optimizer

重做日志（redo log）

25

10

5（主键地址）

索引的优点1：可以大大加快数据的检索速度，这也是创建索引的最主要的原因。2：通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。索引分类B+ 树索引Hash 索引全文索引索引的缺点:1：时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；2：空间方面：索引需要占物理空间 索引有哪几种类型1：主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。2：唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。3：普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。4：全文索引： 是目前搜索引擎使用的一种关键技术。创建索引时需要注意什么1：非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；2：取值离散大的字段，比如性别就不适合，离散太小3：索引字段越小越好，数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。什么是聚簇索引，何时使用聚簇索引与非聚簇索引聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据非聚簇索引：将数据存储于索引分开结构，存储的不再是行的物理位置，而是主键值。非聚簇索引一定会回表查询吗不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引（索引覆盖），那么就不必再进行回表查询。联合索引是什么，为什么需要注意联合索引中的顺序MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。font color=\"#e74f4c\

行

页3

页1

18

11

8

Mysql 数据区

2（主键地址）

索引

一般查询日志（general log）

processer

4

Client Connectors（Java、.Net、Other）

Mysql事务

对于InnoDB来说，每条记录不仅包括了自身的数据，还包含了几个隐藏列：1：DB_ROW_ID：InnoDB为没有主键和没有唯一索引的表自动添加的隐藏主键；2：DB_TRX_ID：更改当前记录的事务id；3：DB_ROLL_PTR：回滚指针，指向undo log的指针。

第二层（1170个数据页，每个数据页1170个节点，总节点1170*1170个）

对于Mysql Innodb存储引擎而言，每次修改后，不仅需要记录Redo log还需要记录Binlog，而且这两个操作必须保证同时成功或者同时失败，否则就会造成数据不一致。为此Mysql引入两阶段提交。

Parser

Redo Log

Storage Engine

回滚日志（undo log）

页2

MySQL Server

id

年龄

性别

指标1

指标2

指标3

男

女

12

14

........

16

Client Connectors      所有与MySQL进行sql交互的终端font color=\"#e74f4c\

15

1：错误日志（error log）    MySQL的错误日志用于记录MySQL服务进程mysqld在启动/关闭或运行过程中遇到的错误信息。  MySQL的错误日志通常由mysqld或mysqld_safe程序产生2：一般查询日志（general log） 查询日志分为一般查询日志和慢查询日志，它们是通过查询是否超出变量 long_query_time 指定时间的值来判定的。 在超时时间内完成的查询是一般查询，可以将其记录到一般查询日志中，但是建议关闭这种日志（默认是关闭的），超出时间的查询是慢查询，可以将其记录到慢查询日志中。3：慢查询日志（slow query log）　  查询超出变量 long_query_time 指定时间值的为慢查询。但是查询获取锁(包括锁等待)的时间不计入查询时间内。  mysql记录慢查询日志是在查询执行完毕且已经完全释放锁之后才记录的，因此慢查询日志记录的顺序和执行的SQL查询语句顺序可能会不一致(例如语句1先执行，查询速度慢，语句2后执行，但查询速度快，则语句2先记录)。4: 二进制日志（binlog）  二进制日志包含了引起或可能引起数据库改变(如delete语句但没有匹配行)的事件信息，但绝不会包括select和show这样的查询语句。语句以\"事件\"的形式保存，所以包含了时间、事件开始和结束位置等信息。  二进制日志是以事件形式记录的，不是事务日志(但可能是基于事务来记录二进制日志)，不代表它只记录innodb日志，myisam表也一样有二进制日志。 binlog的录入格式 ●statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法   被记录复制。 ●row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。 ●mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。 5：中继日志（relay log）从数据库Slave服务的I/O线程从主数据库Master服务的二进制日志中读取数据库的更改记录并写入到中继日志中，然后在Slave数据库执行修改操作。这就是中继日志Relay Log。

Mysql InnoDB引擎的4大特性

MySQL体系结构

Cache、Buffer

3（主键地址）

锁和索引的关系没有索引时，MySQL 可能会进行全表扫描，导致表级锁，增加锁的范围，降低并发性能。有索引时，MySQL 能够通过精确的查询定位数据，使用行级锁，从而提高并发性，减少锁竞争和死锁的风险。按照锁的粒度分数据库锁有哪些 在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。从锁的类别上分MySQL都有哪些锁呢共享锁: 又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。排他锁: 又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。行级锁，表级锁和页级锁对比行级锁 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。表级锁 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。页级锁 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般意向锁InnoDB支持多粒度锁定，允许行锁和表锁共存，为了使多粒度级别的锁变得实用，InnoDB使用 意图锁。意向锁是表级的锁，分为意向共享锁（IS）、意向排他锁（IX）。意向锁的意义？意向锁为啥是表级的？假如没有意向锁，事务T1对表t1的记录r1加s锁，T2对t1准备加表级的X锁，那么T2需要一行行遍历t1看看有没有其他的行锁存在，遍历到r1发现持有s锁，T2加锁失败等待中。如果t1数据量非常大则非常耗性能。那么有人说了，直接对t1加表级的s锁不就行了吗不用遍历了，但是innodb是支持不同的行持有不同类型的锁，所以这种方式也不合理。于是就出了意向锁： 意向锁顾名思义代表有加锁的意向，想要去加锁。针对表级锁。比如：意向共享锁（IS）可以理解为是共享锁的前提，比如事务T1想要给表t1中的记录r1加共享锁，那么它会先给表t1加IS锁，然后再给记录r1加S锁，如果此时事务T2想要给表t1加表级X锁，会发现t1已有IS，则加锁等待中，也不用去每行遍历（因为意向锁是表级锁），提升性能。MySQL中InnoDB引擎的行锁是怎么实现的？答：InnoDB是基于索引来完成行锁例: select * from tab_with_index where id = 1 for update;for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起InnoDB存储引擎的锁Record lock：单个行记录上的锁Gap lock：间隙锁，锁定一个范围，不包括记录本身Next-key lock：record+gap 锁定一个范围，包含记录本身Record lock记录锁是对索引记录的锁。例如， SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 阻止任何其他事务插入、更新或删除值为 的t.c1行 10。记录锁总是锁定索引记录，即使定义的表没有索引。对于这种情况， InnoDB创建一个隐藏的聚集索引并将该索引用于记录锁定。Gap lock先复习一下幻读的概念：幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。幻读仅专指“新插入的行在可重复读隔离级别(RR)下，由于mvcc机制，普通的查询是快照读，是不会看到别的事务插入的数据的。但是当前读（比如select.....for update）会看到别的事务的数据，因此，幻读在“当前读”下才会出现。为了解决当前读出现幻读，innodb搞出了间隙锁。由mvcc+间隙锁解决RR隔离级别下的幻读。间隙锁：顾名思义就是两个值之间的间隙的锁，左右都是开区间。Next-key lock间隙锁+行锁组合next-key lock。加锁的过程是首先是先加间隙锁，然后才加行锁。对没有索引的查询，会锁全表。原则   1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。   2：查找过程中访问到的对象才会加锁。优化   1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。（需要命中对应的行，唯一索引）   2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。（非唯一索引）

年龄索引（非聚簇索引）

一：概念undo log是innodb引擎的一种日志，在事务的修改记录之前，会把该记录的原值（before image）先保存起来（undo log）再做修改，以便修改过程中出错能够恢复原值或者其他的事务读取。为了更好的处理回滚，undo log和之前说的redo log记录物理日志不一样，它是逻辑日志，可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。二：Undo Log的类型在InnoDB存储引擎中，Undo Log分为：insert Undo Loginsert Undo Log是指在insert操作中产生的Undo Log。因为insert操作的记录，只对事务本身可见，对其他事务不可见(这是事务隔离性的要求)，故该Undo Log可以在事务提交后直接删除。不需要进行purge操作。update Undo Logupdate Undo Log记录的是对delete和update操作产生的Undo Log。该Undo Log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入Undo Log链表，等待purge线程进行最后的删除。 三：作用1.为事务提供回滚2.多版本并发控制(MVCC)

7（主键地址）

9（主键地址）

redo log的刷盘时机redo log听着挺厉害的，但是刚开始也是在内存中的一个东西，万一，还没有持久化到磁盘就发生了系统崩溃怎么处理。这个便于redo log的刷盘时机有关：通过一个参数控制：innodb_flush_log_at_trx_commit●innodb_flush_log_at_trx_commit=1  commit的时候进行刷盘：这也是最保险的，因为如果这个时候崩溃了代表没有commit成功，因此，也不用恢复什么数据。●innodb_flush_log_at_trx_commit=2  commit的时候，只是刷新近os的内核缓冲区，具体的刷盘时机不确定。●innodb_flush_log_at_trx_commit=0  后台线程，每s刷新一次到磁盘中。 

MVCC

extent（区）1m

数据页

根节点，一个数据页，一共1170个节点

MySQL复制过程分成三步1). MySQL master 将数据变更写入二进制日志( binary log)2). slave将master的binary log拷贝到它的中继日志（relay log）3). slave重做中继日志中的事件，将数据变更反映它自己的数据MySQL主从复制的几种复制方式1：异步复制MySQL的复制默认是异步的，主从复制至少需要两个MYSQL服务，这些MySQL服务可以分布在不同的服务器上，也可以在同一台服务器上。主库和从库的数据之间难免会存在一定的延迟，这样存在一个隐患：当在主库上写入一个事务并提交成功，而从库由于某些原因尚未得到主库的BINLOG日志时，从库就会损失这个事务，从而造成主从不一致。 2：半同步复制MASTER节点在执行完客户端提交的事务后不是立刻返回结果给客户端，而是等待至少一个SLAVE节点接收并写到relay log中才返回给客户端。MySQL从5.5开始就支持半同步复制 ，半同步策略为 AFTER_COMMIT 。优点●半同步相对于异步复制而言，提高了数据的安全性，只有收到SLAVE确认后再处理客户端的请求。 缺点●半同步造成了一定程度的延迟，这个延迟最少是一个TCP往返的时间。所以，半同步复制最好在低延时的网络中使用●如果SLAVE节点由于网络等原因暂时未收到MASTER节点传递过来的事务，而MASTER节点此刻crash了。HA进行故障转移，客户端都连到SLAVE节点上，这时先前在MASTER节点看到的事务在SLAVE节点并未看到，就会发生事务丢失的情况。3：多线程复制●多线程复制是MySQL 5.6版本引入的功能，旨在提高主从复制的性能和效率。●在多线程复制中，MySQL从服务器可以使用多个I/O线程同时从主服务器读取二进制日志文件，并使用多个SQL线程并行地将这些日志应用到从服务器的数据库中。●多线程复制可以提高从服务器的复制速度，特别是在高负载的情况下，可以更有效地利用系统资源，减少复制延迟。●然而，多线程复制也可能会增加系统的复杂性和资源消耗，并且在一些情况下可能会导致数据不一致或复制延迟问:

第三层（一共1170*1170个数据页，每个数据页16个节点，总数据量1170 * 1170 * 16=21902400）

MyISAM

MySQL 日志

二进制日志（binlog）

Other

Connection Pool

女，22

B+树

众所周知在mysql中，存储引擎Innodb中索引是使用的B+树。B+Tree是在B-Tree基础上的一种优化。而每一个页的存储空间是有限的（16KB），如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。font color=\"#e74f4c\

页4

中继日志（relay log）

事物的四大特性(ACID)原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。什么是脏读？幻读？不可重复读？脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。什么是事务的隔离级别？MySQL的默认隔离级别是什么？SQL 标准定义了四个隔离级别：READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。 Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别

在MySQL的设定中，同一个表空间内的一组连续的数据页为一个extent（区），默认区的大小为1MB，页的大小为16KB。16*64=1024，也就是说一个区里面会有64个连续的数据页。连续的256个数据区为一个段font color=\"#e74f4c\

SQL Interface

1：重做日志（redo log）  undo log 和 redo log 其实都不是 MySQL 数据库层面的日志，而是 InnoDB 存储引擎的日志。二者的作用联系紧密，事务的隔离性由锁来实现，原子性、一致性、持久性通过数据库的 redo log 或 undo log 来完成。redo log 又称为重做日志，用来保    证事务的持久性，undo log 用来保证事务的原子性和 MVCC。  如果每一次更新都写磁盘，然后磁盘也要找到对应记录，再更新，整个过程的IO成本和查找成本都很高。所以InnoDB采取WAL（Write-Ahead Logging）技术，即先写日志，再写磁盘。  当有记录需要更新时，InnoDB引擎先记录redo log，再更新内存。然后找适当时机将该操作写到磁盘（比如系统空闲时）。  redo log的大小是固定的（可配置），比如4个文件，每个文件大小1GB，从第一个文件开始写，写到第四个文件末尾再从头循环。  确保事务的持久性。redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。 当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。 2：回滚日志（undo log）保证数据的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性

id主键索引（聚簇索引）

为什么使用B+树而不是B树1：B树只适合随机检索，而B+树同时支持随机检索和顺序检索，所有叶子节点之间都有一个链指针，顺序存储。2：B+树空间利用率更高，非叶子节点只存储索引不存储数据，树的深度更低，可减少I/O次数，磁盘读写代价更低。。3:  增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率

指标联合索引（非聚簇索引）

1:插入缓冲 （Insert Buffer/Change Buffer）插入缓存之前版本叫insert buffer，现版本 change buffer，主要提升插入性能，change buffer是insert buffer的加强，insert buffer只针对insert有效，change buffering对insert、delete、update、purge都有效对于非聚集索引来说，比如存在用户购买金额这样一个字段，索引是普通索引，每个用户的购买的金额不相同的概率比较大，这样导致可能出现购买记录在数据在数据里的排序可能是1000，3，499，35…，这种不连续的数据，一会插入这个数据页，一会插入那个数据页，这样造成的IO是很耗时的，所以出现了Insert Buffer。Insert Buffer是怎么做的呢？mysql对于非聚集索引的插入，先去判断要插入的索引页是否已经在内存中了，如果不在，暂时不着急先把索引页加载到内存中，而是把它放到了一个Insert Buffer对象中，临时先放在这，然后等待情况，等待很多和现在情况一样的非聚集索引，再和要插入的非聚集索引页合并，比如说现在Insert Buffer中有1，99，2，100，合并之前可能要4次插入，合并之后1，2可能是一个页的，99，100可能是一个页的，这样就减少到了2次插入。这样就提升了效率和插入性能，减少了随机IO带来性能损耗。综合上述，Insert Buffer 只对于非聚集索引（非唯一）的插入和更新有效，对于每一次的插入不是写到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，如果在则直接插入；若不在，则先放到Insert Buffer 中，再按照一定的频率进行合并操作，再写回disk。这样通常能将多个插入合并到一个操作中，目的还是减少了随机IO带来性能损耗。2:双写机制（Double Write）innodb页的大小是16k，相对来说还是比较大的，所以当将脏页写会到磁盘中时，可以发送断电宕机等问题，导致写入了一半，这个时候就没法恢复了，所以使用了两次写这样的机制当页需要写回数据库时，首先把页备份到内存中的doublewrite buffer，然后每次1M，写入到共享表空间中，共享表空间也是在磁盘上，因为是顺序写，所以很快，然后再将这些页写入到真的数据文件中，就算这个时候服务器出了问题，也是可以用共享表空间中的数据进行还原的3:自适应哈希索引（Adaptive Hash Index，AHI）当某个非聚集索引被等值查询的次数很多时，就会为这个非聚集索引再构造一个hash索引，hash索引对呀等值查询是很快的，这个hash索引会放在缓存中注意：hash查询是等值查询，例如模糊查询、范围查找，是不能使用hash索引的。用户可以根据实际场景去权衡是否要开启AHI。4.预读 （Read Ahead）innodb中将64个页划分为一个extent，当一个extent中的页，被顺序读超过了多少个，比如50个，这个值是可以通过nnodb_read_ahead_threshold设置的，那么就会认为顺序读到下一个extent的可能性很大，会提前将下一个extent中的所有页都加载到buffer pool中，这叫线性预读如果某一个extent中，有多个页被读到，就会认为读到这个extent中其他页的可能性也很大，就会把该extent中的其他页也都提前读到buffer pool中

redo log叫做重做日志，属于innodb存储引擎层的，属于物理层的日志，是保证事务持久性的重要机制。当mysql服务器意外崩溃或者宕机后，保证已经提交的事务，确定持久化到磁盘中的一种措施。innodb是以页为单位来管理存储空间的，任何的增删改差操作最终都会操作完整的一个页，会将整个页加载到buffer pool中，然后对需要修改的记录进行修改，修改完毕不会立即刷新到磁盘，因为此时的刷新是一个随机io，而且仅仅修改了一条记录，刷新一个完整的数据页的话过于浪费了。但是如果不立即刷新的话，数据此时还在内存中，如果此时发生系统崩溃最终数据会丢失的，因此权衡利弊，引入了redo log，也就是说，修改完后，不立即刷新，而是记录一条日志，日志内容就是记录哪个页面，多少偏移量，什么数据发生了什么变更。这样即使系统崩溃，再恢复后，也可以根据redo日志进行数据恢复。另外，redo log是循环写入固定的文件，是顺序写入磁盘的。  

错误日志（error log）

Mysql 主从复制

files & logs 

Undo Log

InnoDB 

MySQL 服务端

Mysql锁