MySQL

事务

概念

一个数据库事务通常包含对数据库进行读或写的一个操作序列。它的存在包含有以下两个目的：
    1. 为数据库操作提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
    2. 当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

特性

• 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
• 一致性**（Consistency）**：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
• 隔离性**（Isolation）**：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
• 持久性**（Durability）**：一个事务一旦提交，他对数据库的修改应该永久保存在数据库中。

数据库的读现象浅析

"读现象"是多个事务并发执行时，在读取数据方面可能碰到的状况。

脏读

脏读又称无效数据的读出，是指在数据库访问中，事务T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是无效的。

事务一读取到事务二未提交数据。

不可重复读

在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据。那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。

在事务二提交之前，事务一不可重复读。

幻读

第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的"全部数据行"。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入"一行新数据"。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样.一般解决幻读的方法是增加范围锁RangeS，锁定检锁范围为只读，这样就避免了幻读。

返回数据变多，幻读。

"幻读(phantom read)“是不可重复读(Non-repeatablereads)的一种特殊场景：当事务没有获取范围锁的情况下执行SELECT … WHERE操作可能会发生"幻影读(phantom read)”。

Mysql中的行级锁、表级锁、页级锁

行级锁

行级锁是Mysql中锁粒度最细的锁，表示只针对当前操作的行为加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。

特点：开销大，加锁慢；会出现死锁；锁粒度最小，发生锁冲突的概率最低，并发度也最高。

Innodb默认使用行级锁

• 读锁（read lock）：也叫共享锁（shared lock），允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
• 写锁（write lock）：也叫排他锁（exclusive lock），允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他锁

行锁的实现算法

1. Record Lock 锁

   单个行记录上的锁Record
   Lock总是会去锁住索引记录，如果InnoDB存储引擎表建立的时候没有设置任何一个索引，这时InnoDB存储引擎会使用隐式的主键来进行锁定

2. Gap Lock 锁

   当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引加锁，对于键值在条件范围内但并不存在的记录。
   优点：解决了事务并发的幻读问题不足：因为query执行过程中通过范围查找的话，他会锁定争个范围内所有的索引键值，即使这个键值并不存在。
   间隙锁有一个致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成锁定的时候无法插入锁定键值范围内任何数据。在某些场景下这可能会对性能造成很大的危害。

3. Next-key Lock 锁

   同时锁住数据+间隙锁(1+2),在Repeatable Read隔离级别下，Next-key Lock
   算法是默认的行记录锁定算法。

表级锁

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

MyISAM默认使用表级锁

• 读锁（read lock），也叫共享锁（shared lock）针对同一份数据，多个读操作可以同时进行而不会互相影响（select）

• 写锁（write lock），也叫排他锁（exclusive lock）当前操作没完成之前，会阻塞其它读和写操作（update、insert、delete）

• 意向共享锁（IS）：一个事务给一个数据行加共享锁时，必须先获得表的IS锁

• 意向排它锁（IX）：一个事务给一个数据行加排他锁时，必须先获得该表的IX锁

读锁会阻塞写，但不会阻塞读。而写锁则会把读和写都阻塞。

表锁分析：

查看哪些表被锁了：show open tables;

分析表锁定：show status like ‘table%’;

页锁

开销和加锁时间介于行锁和表锁之间，会出现死锁，锁定粒度介于行锁和表锁之间，并发度一般。

Innodb中的行锁与表锁

InnoDB行锁是通过给索引上的索引项加锁来实现的，所以只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！(索引失效，行锁变表锁)

当两个事务同时执行，一个锁住了主键索引在等待其他相关索引，一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。

发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。

有多种方法可以避免死锁，这里只介绍常见的三种

1. 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3. 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

MySQL中的共享锁与排他锁

共享锁(Share Lock)

共享锁又称为读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事物都不能对数据进行修改，直到已释放所有共享锁。

• 显式加锁 锁行：SELECT … LOCK IN SHARE MODE; 锁表：LOCK TABLE XXX READ;(解锁UNLOCK TABLES)

排他锁（Exclusive Lock）

排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的读写。获准排他锁的事务既能读数据，又能修改数据。

• 显式加锁 锁行：SELECT … FOR UPDATE; 锁表：LOCK TABLE XXX WRITE;(解锁UNLOCK TABLES)

InnoDB的间隙锁：

• 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做"间隙（GAP)"，InnoDB也会对这个"间隙"加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

深入理解乐观锁与悲观锁

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁

通过开启排他锁的方式实现了悲观锁

乐观锁

实现数据版本有两种方式，第一种是使用版本号，第二种是使用时间戳。

深入分析事务的隔离级别

未提交读(Read uncommitted)

读未提交是最低级的隔离级别。在这种事物隔离级别下，一个事务可以读到另一个事务为提交数据。

未提交读会导致脏读

未提交读的数据库锁情况（实现原理） 事务在读数据的时候并未对数据加锁。
务在修改数据的时候只对数据增加行级共享锁。

提交读(Read committed)

在一个事务修改数据过程中，如果事务还没提交，其他事务不能读该数据。

提交读不能解决不可重复读的读现象（重复读数据会变化）

提交读的数据库锁情况 事务对当前被读取的数据加行级共享锁（当读到时才加锁），一旦读完该行，立即释放该行级共享锁；
事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。

可重复读(Repeatable reads)

这种隔离级别就叫可重复读（mysql默认隔离级别）

可重复读不能解决幻读

MySQL如何解决幻读？

SERIALIZABLE 串行化

MVCC + Next-Key Lock

可重复读的数据库锁情况
事务在读取某数据的瞬间（就是开始读取的瞬间），必须先对其加行级共享锁，直到事务结束才释放；
事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。

可序列化(Serializable)

可序列化(Serializable)是最高的隔离级别，前面提到的所有的隔离级别都无法解决的幻读，在可序列化的隔离级别中可以解决。

可序列化的数据库锁情况
事务在读取数据时，必须先对其加 表级共享锁，直到事务结束才释放； 事务在更新数据时，必须先对其加 表级排他锁，直到事务结束才释放。

MySQL性能优化

单条SQL运行慢

创建并正确使用索引

数据拆分

1. 垂直拆分：常用字段、不常用字段拆分

2. 水平拆分：一张表的数据拆分成多张表存放

部分SQL运行慢

慢查询分析：开启慢查询日志，分析日志进行优化

• 查询是否开启慢查询日志 SHOW VARIABLES LIKE “%slow_query_log%”;
• 开启慢查询日志(重启失效) SET GLOBAL slow_query_log=1;
• 查询慢查询时间阈值 show VARIABLES LIKE “%long_query_time%”;
• 设置慢查询时间阈值(重启失效，需要重新开启一个会话才生效) SET GLOBAL long_query_time=3;
• 慢查询次数 show GLOBAL STATUS LIKE ‘%Slow_queries%’;

使用mysqldumpslow做日志分析：

• s：是表示按照何种顺序排序

• c：访问次数

• l：锁定时间

• r：返回记录

• t：查询时间

• al：平均锁定时间

• ar：平均返回记录数

• at：平均查询时间

• t：即为返回前面多少条数据

• g：后边搭配正则表达式，大小写不敏感

  1. 得到返回记录集最多的10个SQL mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log
  2. 得到访问次数最多的10个SQL mysqldumpslow -s c -t 10 /var/lib/mysql/xxx-slow.log
  3. 得到按照时间排序的前10条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/xxx-slow.log
  4. 另外建议在使用这些命令时结合 | more使用，避免有太多数据 mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log | more

使用show profile分析SQL：

• 查询是否开启 SHOW VARIABLES LIKE ‘profiling’; #开启 SET profiling=on;
• 查询最近执行sql语句(查询) show PROFILES; #查询某一条sql执行过程 show PROFILE cpu,block io for query 26;

全局日志查询：

开启全局日志 SET GLOBAL general_log=1; 
记录在表中 SET GLOBAL log_output=\'TABLE\'; 
查询日志 SELECT \* FROM mysql.general_log;

整个SQL运行慢

读写分离：

1. 应用层解决方案：通过应用层对数据源做路由来实现读写分离。

2. 中间件解决方案：通过 MySQL 的中间件做主从集群。

EXPLAIN命令：

• id：相同按顺序执行，不同id越大优先级越高，越先执行

• select_type:

  • SIMPLE：简单的select查询，查询中不包含子查询或者union

  • PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY

  • SUBQUERY：在select或where列表中包含了子查询

  • DERIVED：在from列表中包含子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

  • UNION：使用了UNION，第二个select则被标记为UNION，若UNION包含在FROM子查询中，外层SELECT将被标记为：DERIVED

  • UNION RESULT：使用UNION获取结果的查询

• table：表名

• partitions：表分区

• type：表示查询类型，从最好到最差：system>const>eq_ref>ref>range>index>ALL

  • system：表示只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个可以忽略不计

  • const：表示通过索引一次就找到了，const出现在primary key或者unique索引，因为只匹配一条数据，所以很快，如将主键置于WHERE条件中查询，MYSQL就能将改查询转换为一个常量

  • eq_ref：唯一性索引扫描，常见于主键或唯一索引扫描

  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行

  • range：检索指定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般出现在where中使用between,<,>,in等范围查询

  • index：全索引扫描，index和all的区别为index类型只遍历索引树。这通常比all快，因为索引文件通常比数据文件小。

  • all：全表扫描，性能最差

• possible_key：可能使用的索引

• key：实际使用的索引，查询中若使用了覆盖索引，则该索引仅出现在key列表中。（覆盖索引：查询的是索引字段）

• key_len：表示索引中使用的字节数，长度越短越好

• ref：显示索引的哪一列被使用了，列与索引的比较

• rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

• Extra：额外信息

  • Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MYSQL中无法利用索引完成的排序操作称为"文件排序"。

  • Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order
    by和分组查询group by

  • Using Index：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错,如果同时出现了using where，表明索引被用来执行索引键值查找。如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

  • Using where：使用where子句

  • Using join buffer： 使用了连接缓存

  • Using impossible where：表示where子句总是false，查询不到任何数据

  • select tables optimized away：在没有group by子句的情况下基于索引优化MIN/MAX；对于MYISAM储存引擎优化COUNT(*)操作，不必等到执行阶段再计算，查询执行计划生成的阶段即完成优化。

  • distinct：优化distinct操作，在找到第一匹配的元组后即停止查找同样值的动作

  

高频面试题

什么是索引

索引是一种数据结构，可以帮助我们快速进行数据查找。

索引是个什么样的数据结构

索引的数据结构和具有储存引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现为：B+树索引。

Hash索引和B+树索引有什么区别或者说优劣

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据；

B+的底层现实是多路平衡查找树，对于每一次查询都是从根节点出发，查找叶子节点方可以获取的所查键值，然后根据查询判断是否需要回表查询数据。

不同：

hash索引进行等值查询(where xx=xx)更快（一般情况下），但是却无法进行范围查询(模糊查询…)。

因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询，而B+树的所有节点皆遵循（左节点小于父节点，右节点大于父节点，多叉树也类似），天然支持范围查询。

hash索引不支持使用索引排序，原理同上。

hash索引不支持模糊查询以及多列索引的最左前缀原则，原理也是因为hash函数的不可预测AAAA和AAAAB的索引没有相关性。

hash索引任何时候都避免不了回表查询数据，而B+树索引在符合某些条件下(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。

hash索引在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差，而B+树的查询比较稳定，对于所有查询都是从根节点到叶子节点，而树的高度较低。

因此，在大多数情况下，直接使用B+树索引可以获得稳定且较好的查询速度，而不需要使用hash索引。

什么是聚簇索引

在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行数据，这就是聚簇索引和非聚簇索引。

在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引，如果没有唯一键，则隐式的生成一个键来建立聚簇索引。

当查询使用聚簇索引时，在对应的叶子节点，可以获取整行数据，因此不用再次进行回表查询。

非聚簇索引一定要回表查询？

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，那么就不必再进行回表查询。

select age from employee where age <
20;在age字段建立索引，查询age，无需回表查询，在索引的叶子节点上，已经包含了age信息。

在建立索引的时候，都需要考虑哪些因素

字段使用的频率。经常作为条件进行查询的字段比较合适，如果需要建立联合索引的话，还需要考虑联合索引的顺序。

联合索引是什么？为什么需要注意联合索引中的顺序？

mysql可以使用多个字段同时建立一个索引，叫做联合索引，在联合索引中，如果想要命中索引，需要按照建立索引时的顺序挨个使用，否则无法命中索引。

怎么知道索引是否被使用？或者说怎么才能知道sql执行慢的原因？

explain查看执行计划。

什么时候索引会失效

1. 使用不等号查询

2. 列参与了数学运算或者函数

3. like ‘%xxx’

4. 当mysql分析全表扫描比使用索引快的时候

5. 当使⽤联合索引,前⾯⼀个条件为范围查询,后⾯的即使符合最左前缀原则,也⽆法使⽤索引

索引有什么劣势

1. 实际上索引也是张表，该表保存了主键和索引字段，并指向实体表记录，所以索引也是要占用空间的。

2. 虽然索引大大的提高查询速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MYSQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引类的字段，都会调整因为更新所带来的键值变化后的索引信息。

表结构设计

尽量设置一个主键

主键使用自增ID

字段不要定义为null

存储密码使用char而不是使用varchar

存储引擎相关

MySQL支持哪些存储引擎

InnoDB、MyISAM等等。

InnoDB和MyISAM的区别

1. InnoDB支持事务MyISAM不支持。

2. InnoDB支持行级锁，而MyISAM只支持表级锁

3. InnoDB支持MVCC，MyISAM不支持

4. InnoDB支持外键，MyISAM不支持

5. InnoDB不支持全文索引，而MyISAM支持

零散问题

MySQL中varchar和char的区别

char是一个定长字段，假如申请了char(10)的空间，那么无论实际存储多少内容，该字段都占用10个字符；

而varchar是变长的，也就是说申请的只是最大长度，占用空间为实际字符长度+1，最后一个字符存储使用了多长空间

在检索效率上，char>varchar，所以如果确定某个字段是定长的，可以使用char，否则尽量使用varchar。例如MD5密码，则应该使用char。

varchar(10)和int(10)的区别

varchar的10代表了申请的空间⻓度,也是可以存储的数据的最⼤⻓度,⽽int的10只是代表了展⽰的⻓度,不⾜10位以0填充。

也就是说,int(1)和int(10)所能存储的数字⼤⼩以及占⽤的空间都是相同的,只是在展⽰时按照⻓度展⽰。

MySQL的binlog有几种录入格式？

1）statement：

2）row：

3）mixed：

超大分页怎么处理？

select * from table where age > 20 limit 1000000,10

select * from table where id in (select id from table where age > 20
limit 1000000,10)

同时如果ID连续的好,我们还可以 select * from table where id > 1000000
limit 10

数据库三大范式

• 第一范式：每个列都不可拆分。

• 第二范式：非主键列完全依赖于主键，而不能是依赖于主键的一部分。

• 第三范式：非主键只依赖于主键，不依赖于其他非主键。

在设计数据库结构式，要尽量遵守三范式，如果不遵守，必须有足够的理由。

MySQL读写分离

如何实现MySQL的读写分离

一个主库，多个从库。在主库写，然后主库会自动把数据同步到从库。

MySQL主从复制的原理

主库将变更写入binlog日志，然后从库连接主库后，从库有一个IO线程，将主库的binlog日志拷贝到自己本地，写入一个relay中继日志中。接着从库中有一个SQL线程会从中继日志中读取binlog，然后执行binlog日志中的内容，也就是在自己本地执行一遍SQL，

这样就可以保证主从一致。

##从库执行SQL是串行的，高并发下会有延迟问题

MySQL主从同步延时问题

1. 分库，将一个主库拆分为多个从库。

2. 打开MySQL支持的并行复制。

3. 重写代码，不要在写入之后马上查询。

4. 如果必须要写入之后马上查询，对这个查询设置直连数据库。

MySQL优化

【优化总结口诀】

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

Like百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用；

VARCHAR引号不可丢，SQL高级也不难！

EXPLAIN

1. SQL语句中IN包含的值不应过多

2. SELECT语句务必指明字段名称

3. 当只需要一条数据的时候，使用limit 1

4. 如果排序字段没有用到索引，就尽量少排序

5. 如果限制条件中其他字段没有索引，尽量少用or

6. 尽量用union all代替union

7. 区分in和exists、not in和not exists(小表驱动大表)

8. B为小表：select * from 表A where id in (select id from 表B)

   A为小表：select * from 表A where exists(select * from 表B where 表B.id=表A.id)

9. 使用合理的分页方式以提高分页效率

   select id,name from product limit 866613, 20;

   优化方法：可以将上一页排序字段最大值作为下一页的起点。

   select id,name from product where id> 866612 limit 20;

10. 分段查询

    分段查询，最终合并输出。

11. 避免在where子句中进行null值判断

12. 不建议使用%前缀模糊查询

    例如 LIKE ‘%name%’

解决办法：

1. 使用全文索引:

    创建全文索引SQL：ALTER TABLE \`dynamic_201606\` ADD FULLTEXT INDEX \`idx_user_name\` (\`user_name\`);

    使用全文索引SQL：select id,fnum,fdst from dynamic_201606 where match(user_name) against(\'zhangsan\' in boolean mode);

2. 使用覆盖索引：查询的字段建立索引

13. 避免在where子句中对字段进行表达式操作

14. 避免进行隐式转换（字符串不加单引号导致索引失效）

    SELECT * FROM table WHERE type=1;（type为varchar）

15. 对于联合索引，要遵守最左前缀原则

    举例来说联合索引含有字段id,name,school，可以直接用id字段，也可以id,name这样的顺序，name,school无法使用这个索引,id,school只能使用id索引。（必须是连续的，中间不能断，WHERE子句中MYSQL优化器会优化顺序）。

16. 注意范围查询语句

    对于联合索引，如果存在范围查询，比如between、>、<等条件，会造成后面索引字段失效。

17. 关于join优化

    

    1）MySQL没有full join,可以使用union来实现。

    2）尽量使用inner join，因为inner join会自动选择小表作为驱动表。

    3）合理运用索引

    4）利用小表去驱动大表。

    5）巧用STRAIGHT_JOIN：只能在inner join下使用,STRAIGHT_JOIN左边表为驱动表。

18. ORDER BY优化：（MYSQL支持两种排序方式，filesort和Index）

    • ORDER BY子句，尽量使用INDEX（索引）方式排序，避免使用filesort方式排序

    • 尽可能在索引列上完成排序操作，遵循索引的最左前缀原则

    • ORDER BY子句的列满足索引最左前缀原则

    • WHERE子句列和ORDER BY子句列满足索引最左前缀原则

    • 无法使用INDEX，FileSort有两种算法：

      双路排序

      单路排序

19. GROUP BY优化：（和order by几乎相同）

    group by的实质是先排序后分组，遵循索引最左前缀原则

    当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数设置

    where优先级高于having，能在where限定的条件就不要用having了

MySQL日志

MySQL逻辑架构

MySQL逻辑架构

MySQL的逻辑架构大致可分为三层：

第一层：处理客户端连接，授权认证，安全校验。

第二层：服务端server层，负责对SQL解释、分析、优化、执行操作引擎等。

第三层：存储引擎，负责MySQL中数据的存储和提取。

MySQL日志

redo log（重做日志）

redo log属于MySQL存储引擎innoDB的事务日志。

MySQL的数据是存放在磁盘中的，每次读写数据都需要做磁盘IO操作，如果并发场景下性能就会很差。为此MySQL提供一个优化手段，引入缓存buffer pool。这个缓存中包含了磁盘中部分数据页（page）的映射，以此来缓解数据库的磁盘压力。

当从数据库读数据时，首先从缓存中读取，如果缓存中没有，则从磁盘读取后放入缓存；当向数据库写数据时，先向缓存写入，此时缓存中的数据页变更，这个数据页称为脏页，buff pool中修改完数据后会按照设定的更新策略，定期刷到磁盘中，这个过程称为刷脏页。

MySQL宕机

如果刷脏页还未完成，可MySQL由于某些原因宕机重启，此时buffer pool中修改的数据还没有来得及刷到磁盘中，就会导致数据丢失，无法保证事务的持久性。

为了解决这个问题引入了redo log，redo Log如其名侧重于重做！它记录的是数据库中每个页的修改，而不是某一行或某几行修改成怎样，可以用来恢复提交后的物理数据页，且只能恢复到最后一次提交的位置。

redo log用到了**WAL（Write-Ahead Logging）**技术，这个技术的核心就在于修改记录前，一定要先写日志，并保证日志先落盘，才能算事务提交完成。

有了redo log再修改数据时，InnoDB引擎会把更新记录先写在redo log中，在修改Buffer Pool中的数据，当提交事务时，调用fsync把redo log刷入磁盘。至于缓存中更新的数据文件何时刷入磁盘，则由后台线程异步处理。

注意：此时redo log的事务状态为prepare，还未真正提交成功。要等到bin log日志写入磁盘完成才变更为commit，事务才算真正提交完成。

这样一来即使刷脏页之前MySQL意外宕机也没关系，只要在重启时解析redo log中更改记录进行重放，重新刷盘即可。

大小固定

redo log采用固定大小，循环写入的格式，当redo log写满之后，重新从头开始如此循环写，形成一个环状。

如此设计的原因：

因为redo log记录的是数据页上的修改，如果Buffer Pool中数据页已经刷磁盘了，那么这些日志记录就失效了，新日志会将这些失效的记录进行覆盖擦除。

• 图中的write pos表示redo log当前记录的日志序列号LSN(log sequence number)，写入还未刷盘，循环往后递增；check point表示redo log中修改记录已刷入磁盘后的LSN，循环往后递增，这个LSN之前的数据已经全落盘。
• write pos到check point之间的部分是redo log空余的部分（绿色），用来记录新的日志；check point到write pos之间是redo log已经记录的数据页修改数据，此时数据页还未刷回磁盘的部分。当write pos追上check point时，会先推动check point向前移动，空出位置（刷盘）再记录新的日志。

注意：
redo log日志满了，在擦除之前，需要确保这些要被擦除记录对应在内存中的数据页都已经刷到磁盘中了。擦除旧记录腾出新空间这段期间，是不能再接收新的更新请求的，此刻MySQL的性能会下降。所以在并发量大的情况下，合理调整redo log的文件大小非常重要。

crash-safe
因为redo log的存在使得Innodb引擎具有了crash-safe的能力，即MySQL宕机重启，系统会自动去检查redo log，将修改还未写入磁盘的数据从redo log恢复到MySQL中。

MySQL启动时，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。会先检查数据页中的LSN，如果这个LSN 小于 redo log 中的LSN，即write pos位置，说明在redo log上记录着数据页上尚未完成的操作，接着就会从最近的一个check point出发，开始同步数据。

简单理解，比如：redo log的LSN是500，数据页的LSN是300，表明重启前有部分数据未完全刷入到磁盘中，那么系统则将redo log中LSN序号300到500的记录进行重放刷盘。

undo log（回滚日志）

undo log

undo log也是属于MySQL存储引擎InnoDB的事务日志。

undo log属于逻辑日志，如其名主要起到回滚的作用，它是保证事务原子性的关键。记录的是数据修改前的状态，在数据修改的流程中，同时会记录一条与当前操作相反的逻辑日志到undo log中。

同一个事物内的一条记录被多次修改，不会把每次数据修改前的状态都写入undo log；undo log只负责记录事务开始前要修改数据的原始版本，当我们再次对这行数据进行修改，所产生的修改记录会写入到redo log，undo log负责完成回滚，redo log负责完成前滚。

回滚

未提交的事务，即事务未执行commit。但该事务内修改的脏页中，可能有一部分脏块已经刷盘。如果此时数据库实例宕机重启，就需要用回滚来将先前那部分已经刷盘的脏块从磁盘上撤销。

前滚

未完全提交的事务，即事务已经执行commit，但该事务内修改的脏页中只有一部分数据被刷盘，另外一部分还在buffer pool缓存上，如果此时数据库实例宕机重启，就需要用前滚来完成未完全提交的事务。将先前那部分由于宕机在内存上的未来得及刷盘数据，从redo log中恢复出来并刷入磁盘。

数据库实例恢复时，先做前滚，后做回滚。

bin log（归档日志）

bin log是一种数据库Server层（和什么引擎无关），以二进制形式存储在磁盘中的逻辑日志。bin log记录了数据库所有DDL和DML操作（不包含 SELECT 和 SHOW等命令，因为这类操作对数据本身并没有修改）。

默认情况下，二进制日志功能是关闭的。可以通过以下命令查看二进制日志是否开启：

SHOW VARIABLES LIKE ‘log_bin’;

bin log也被叫做归档日志，因为它不会像redo log那样循环写擦除之前的记录，而是会一直记录日志。一个bin log日志文件默认最大容量1G（也可以通过max_binlog_size参数修改），单个日志超过最大值，则会新创建一个文件继续写。

show binary logs;

bin log日志的内容格式其实就是执行SQL命令的反向逻辑，这点和undo log有点类似。一般来说开启bin log都会给日志文件设置过期时间（expire_logs_days参数，默认永久保存），要不然日志的体量会非常庞大。

show variables like ‘expire_logs_days’;

bin log主要应用于MySQL主从模式（master-slave）中，主从节点间的数据同步；以及基于时间点的数据还原。

bin log和redo log的区别

• 层次不同：redo log 是InnoDB存储引擎实现的，bin log是MySQL的服务器层实现的，但MySQL数据库中的任何存储引擎对于数据库的更改都会产生bin log。

• 作用不同：redo log 用于碰撞恢复（crash recovery），保证MySQL宕机也不会影响持久性；bin log用于时间点恢复（point-in-time recovery），保证服务器可以基于时间点恢复数据和主从复制。

• 内容不同：redo log 是物理日志，内容基于磁盘的页Page；bin log的内容是二进制，可以根据binlog_format参数自行设置。

• 写入方式不同：redo log 采用循环写的方式记录；binlog 通过追加的方式记录，当文件大小大于给定值后，后续的日志会记录到新的文件上。

• 刷盘时机不同：bin log在事务提交时写入；redo log 在事务开始时即开始写入。

bin log 与 redo log功能并不冲突而是起到相辅相成的作用，需要二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

relay log（中继日志）

relay log日志文件具有与bin log日志文件相同的格式，从上边MySQL主从复制的流程可以看出，relay log起到一个中转的作用，slave先从主库master读取二进制日志数据，写入从库本地，后续再异步由SQL线程读取解析relay log为对应的SQL命令执行。

slow query log

慢查询日志（slow query log）: 用来记录在 MySQL中执行时间超过指定时间的查询语句，在 SQL
优化过程中会经常使用到。通过慢查询日志，我们可以查找出哪些查询语句的执行效率低，耗时严重。

出于性能方面的考虑，一般只有在排查慢SQL、调试参数时才会开启，默认情况下，慢查询日志功能是关闭的。可以通过以下命令查看是否开启慢查询日志：

SHOW VARIABLES LIKE ‘slow_query%’;

general query log

一般查询日志（general query log）：用来记录用户的所有操作，包括客户端何时连接了服务器、客户端发送的所有SQL以及其他事件，比如MySQL服务启动和关闭等等。MySQL服务器会按照它接收到语句的先后顺序写入日志文件。

由于一般查询日志记录的内容过于详细，开启后 Log文件的体量会非常庞大，所以出于对性能的考虑，默认情况下，该日志功能是关闭的，通常会在排查故障需获得详细日志的时候才会临时开启。

show variables like ‘general_log’;

error log

错误日志（error log）: 应该是 MySQL 中最好理解的一种日志，主要记录 MySQL服务器每次启动和停止的时间以及诊断和出错信息。

默认情况下，该日志功能是开启的，通过如下命令查找错误日志文件的存放路径。

SHOW VARIABLES LIKE ‘log_error’;

**注意：**错误日志中记录的可并非全是错误信息，像 MySQL 如何启动 InnoDB的表空间文件、如何初始化自己的存储引擎，初始化 buffer pool等等，这些也记录在错误日志文件中。