• 基本概念
  • MySQL 基本框架图
• SQL 语句
  • 数据库连接
  • 语句执行流程
    • 查询语句执行流程
      • 查询状态
      • 查询优化
    • update 语句执行流程
    • insert 语句执行流程
    • delete 语句执行流程
  • 语句实现原理
    • select 工作原理
    • 关联查询（join）
    • order by 工作原理
    • group by 工作原理
    • where 和 having 的区别
• 事务机制
  • 衡量事务的四个特性（ACID）
    • undo log 实现原子性
    • redo log 实现持久性
    • 锁和 MVCC 机制实现隔离性
• 日志文件
  • 日志落魄策略
  • 数据丢失的场景
  • 主从同步
  • 存储过程
  • 视图
• 索引
  • 不走索引的情况
• 存储引擎

本文主要介绍 MySQL 相关的一些理论知识、实践经验和验证实验。

基本概念

MySQL 是一个开源免费的关系数据库软件。 数据库相关的基础可以参考博文数据库基础。

MySQL 基本框架图

框架图可以帮助我们从宏观上了解 MySQL 的大致全貌，有助于我们从关联的角度理解其如此设计的背后逻辑。 同时也可以帮助我们从外部寻找线索窥探其中的设计原理和实现细节。以下几张架构图试图从不同的详略角度 刻画 MySQL 的基本架构。其中较为底层的原理可参见一篇文章让你搞懂MYSQL底层原理

SQL 语句

MySQL 语句可以分为以下几类(更详细的可以参考这篇博文)：

类型	包含的指令
DDL((Data Definition Language，数据定义语言))	create: 创建数据库及其对象(包括新建表，索引，视图，存储过程等)
	alter: 改变现有数据库的结构 (包括修改索引，修改字段类型，删除索引)
	truncate: 删除表中所有记录，并删除已分配的空间
	comment: 添加注释
	rename: 重命名，其中包括索引、表名等。
DML (Data Manipulation Language，数据操作语句)	select: 从数据库中检索数据
	insert: 新增数据到数据库中
	update: 更新表格中现有数据
	delete: 删除现有数据
	explain: 查看语句的执行计划
	lock table: 锁表操作
DCL (Data Control Language，数据控制语句)	grant: 允许用户访问数据库的权限
	revoke: 撤销用户访问数据库的权限
TCL (Transactional Control Language，事务控制语言)	commit: 提交事务
	rollback: 回滚事务
	set transaction: 设置事务隔离级别

数据库连接

当你在终端敲下该命令 mysql -h 主机名(ip) -u 用户名 -P 端口 -p密码 数据库名 --default-character-set=编码类型 回车之后发生了什么？

• TCP 三次握手
• mysql 客户端与服务端握手认证（三次握手）
  • 服务器 -> 客户端：握手初始化消息
  • 客户端 -> 服务器：登陆认证消息
  • 服务器 -> 客户端：认证结果消息
• 命令执行阶段（上述阶段完成之后）
  • 客户端 -> 服务器：执行命令消息
  • 服务器 -> 客户端：命令执行结果
• 退出命令
• TCP 四次挥手

具体的协议包分析可参考mysql的tcp链接过程_实战解析丨如何对Mysql连接请求的tcpdump内容进行分析

语句执行流程

了解 MySQL 语句执行流程有助于我们优化和选择恰当的语句，以提供性能。每个具体执行流程中执行 的操作不相同，所涉及到的数据量不同，有的可能使用临时表等，针对这些特点可以采取更换顺序、 减少数据量等手段进行优化。

查询语句执行流程

• DML语句首先进行语法分析，对使用sql表示的查询进行语法分析，生成查询语法分析树。
• 语义检查：检查sql中所涉及的对象以及是否在数据库中存在，用户是否具有操作权限等
• 视图转换：将语法分析树转换成关系代数表达式，称为逻辑查询计划；
• 查询优化：在选择逻辑查询计划时，会有多个不同的表达式，选择最佳的逻辑查询计划；
• 代码生成：必须将逻辑查询计划转换成物理查询计划，物理查询计划不仅能指明要执行的操作，也给出了这些操作的执行顺序，每步所用的算法，存储数据的方式以及从一个操作传递给另一个操作的方式。
• 将DML转换成一串可执行的存取操作的过程称为束缚过程，

--查询组合字段
(6)select (6-2) distinct(6-3) top(<top_specification>)(6-1)<select_list>
--连表
(1)from (1-J)<left_table><join_type> join <right_table> on <on_predicate>
        (1-A)<left_table><apply_type> apply <right_table_expression> as <alias>
        (1-P)<left_table> pivot (<pivot_specification>) as <alias>
        (1-U)<left_table> unpivot (<unpivot_specification>) as <alias>
--查询条件
(2)where <where_pridicate>
--分组
(3)group by <group_by_specification>
--是否对分类聚合后的结果进行再汇总
(4)with <cube|rollup> 
--分组条件
(5)having<having_predicate>
--排序
(7)union(all)
(8)order by<order_by_list>
(9)limit <limit_number>

--说明：
--1、顺序为有1-6，6个大步骤，然后细分，6-1，6-2，6-3，由小变大顺序，1-J，1-A，1-P，1-U，为并行次序。如果不够明白，接下来我在来个流程图看看。
--2、执行过程中也会相应的产生多个虚拟表（下面会有提到），以配合最终的正确查询。
--参考自博客：https://blog.csdn.net/bitcarmanlee/article/details/51004767?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-6.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-6.control

查询状态

对于 MySQL 连接，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。使用 show full processlist 命令查看当前状态。 在一个查询生命周期中，状态会变化很多次，下面是这些状态的解释：

• sleep：线程正在等待客户端发送新的请求；
• query：线程正在执行查询或者正在将结果发送给客户端；
• locked：在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态；
• analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划；
• copying to tmp table：线程在执行查询，并且将其结果集复制到一个临时表中，这种状态一般要么是做group by操作，要么是文件排序操作，或者union操作。如果这个状态后面还有on disk标记，那表示MySQL正在将一个内存临时表放到磁盘上；
• sorting result：线程正在对结果集进行排序；
• sending data：线程可能在多个状态间传送数据，或者在生成结果集，或者在想客户端返回数据。

查询优化

MySQL Query Optimizer 通过执行explain命令来获取一个 Query 在当前状态的数据库中的执行计划。 expain 出来的信息有10列，分别是 id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra。 可以通过这个线上实验网站进行 expain 实验，示例可参见Mysql Explain 详解。

字段名	描述	值
id	SELECT 识别符。这是 SELECT 的查询序列号	id 相同时，执行顺序由上至下
		如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
		id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行
select_type	示查询中每个 select 子句的类型	SIMPLE(简单SELECT,不使用UNION或子查询等)；
		PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)；
		UNION(UNION中的第二个或后面的SELECT语句)；
		DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)；
		UNION RESULT(UNION的结果)；
		SUBQUERY(子查询中的第一个SELECT)；
		DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)；
		DERIVED(派生表的SELECT, FROM子句的子查询)；
		UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)。
table	表示 explain 的一行正在访问哪个表	有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称
type	表示MySQL在表中找到所需行的方式	常用的类型有：ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
		ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
		index: Full Index Scan，index 与 ALL 区别为 index 类型只遍历索引树
		range:只检索给定范围的行，使用一个索引来选择行
		ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行
		eq_ref: 类似 ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
		const、system: 当 MySQL 对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
		NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
possible_keys	显示查询可能使用哪些索引来查找	explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，MySQL认为索引对此查询帮助不大，选择了全表查询。
		如果该列是 NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果
key	显示 MySQL 实际决定使用的键（索引）	如果没有选择索引，键是 NULL。要想强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX。
key_len	表示索引中使用的字节数	可通过该列计算查询中使用的索引的长度（key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的）
		不损失精确性的情况下，长度越短越好。
ref	这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量	常见的有：const（常量），func，NULL，字段名（例：film.id）
rows	估计要读取并检测的行数	这个不是结果集里的行数
filtered	返回结果的行数占读取行数的百分比	值越大越好
Extra	额外信息	distinct: 一旦mysql找到了与行相联合匹配的行，就不再搜索了
		Using index: 所有列都覆盖索引时， 不需要返回表中的行记录
		Using where: 在存储引擎检索行后再进行过滤
		Using temporary: 表示MySQL需要使用临时表来存储结果集，常见于 distinct、排序和分组查询。这个需要优化，比如是否有必要使用该语句、新建索引等
		Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”，对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时 mysql 会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。
		Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
		Impossible where：这个值强调了 where 语句会导致没有符合条件的行。

offset limit 优化

mysql查询使用select命令，配合limit，offset参数可以读取指定范围的记录,但是offset过大影响查询性能

• 通过二级索引查到主键值（找出所有gender=1的id)。
• 再根据查到的主键值通过主键索引找到相应的数据块（根据id找出对应的数据块内容）。
• 根据offset的值，查询300001次主键索引的数据，最后将之前的300000条丢弃，取出最后1条。

所以，mysql 查询时，offset 过大影响性能的原因是多次通过主键索引访问数据块的I/O操作。优化方法有以下几种：

• 自增 ID 范围： 如 select * from table_name where (id >= 10000) limit 10
• in 主键： 如 Select * From table_name Where id in (Select id From table_name where ( user = xxx )) limit 10000, 10;
• inner join: 如 select * from table_name inner join ( select id from table_name where (user = xxx) limit 10000,10) b using (id)
• 提前算出边界，使用 between and 代替

update 语句执行流程

以语句 UPDATE test SET c = c + 1 WHERE id = 1; 为例简单绘制成下图（详见mysql update语句的执行过程详解）

当事务提交的时候，innodb不会立即删除undo log，因为后续还可能会用到undo log，如隔离级别为repeatable read时， 事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即undo log不能删除。 但是在事务提交的时候，会将该事务对应的undo log放入到删除列表中，未来通过purge来删除。 并且提交事务时，还会判断undo log分配的页是否可以重用， 如果可以重用，则会分配给后面来的事务，避免为每个独立的事务分配独立的undo log页而浪费存储空间和性能。

通过undo log记录delete和update操作的结果发现：(insert操作无需分析，就是插入行而已) delete操作实际上不会直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的。 update分为两种情况：update的列是否是主键列。

• 如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。
• 如果是主键列，update分两部执行：先删除该行，再插入一行目标行。

insert 语句执行流程

这里只给出简约的流程图（详见insert 语句_图解MySQL | [原理解析] MySQL insert 语句的磁盘写入之旅）：

delete 语句执行流程

这里只给出简约的流程图：

删除优化

根据不同的需求选择恰当的删除数据的方式：

• drop： 是直接将表格删除（包括表结构），无法找回
• truncate： 是删除表中所有数据（保留表结构）
• delete： 也是删除表中数据，但可以与 where 连用，删除特定行；
• 逻辑珊瑚：使用标记位
• 当待删除的数据比要保留的数据量大得多时：可以先转移要保留的数据，truncate 之后再转回

delete 和 truncate 的区别

• DELETE 是可以带 WHERE 的，所以支持条件删除；而 TRUNCATE 只能删除整个表
• 由于 DELETE 是数据操作语言（DML - Data Manipulation Language），操作时原数据会被放到 rollback segment中，可以被回滚；而 TRUNCATE 是数据定义语言（DDL - Data Definition Language)，操作时不会进行存储，不能进行回滚
• 在数据量比较小的情况下，DELETE 和 TRUNCATE 的清理速度差别不是很大。 但是数据量很大的时候就能看出区别。由于第二项中说的，TRUNCATE 不需要支持回滚， 所以使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项， 固然会慢，但是相对来说也较安全
• 随着不断地进行表记录的 DML 操作，会不断提高表的高水位线（HWM），DELETE操作之后虽然表的数据删除了，但是并没有降低表的高水位， 随着 DML 操作数据库容量也只会上升，不会下降。所以如果使用 DELETE，就算将表中的数据减少了很多， 在查询时还是很和 DELETE 操作前速度一样。 而 TRUNCATE 操作会重置高水位线，数据库容量也会被重置，之后再进行 DML 操作速度也会有提升。

语句实现原理

select 工作原理

可参见 MySQL select实现原理

关联查询（join）

join 实现原理可参见 MySQL 的Join及底层实现原理、 数据库基础（七）Mysql Join算法原理， 各种join可参见 图解MySQL里的各种 JOIN，看完不懂来找我！

order by 工作原理

可参见 MySQL order by、group by底层实现及优化（非常详细）、 MySQL order by实现原理分析和Filesort优化、 Mysql学习之order by的工作原理、 mysql ORDER BY,GROUP BY 和DISTINCT原理

group by 工作原理

可参见 MySQL分组查询Group By实现原理详解

where 和 having 的区别

可参见 mysql having和where的区别、 mysql where和having的区别、 Mysql-where子句与having子句的区别

事务机制

我们可以把事务理解为一组sql语句的集合，事务可以只包含一条sql语句，也能包含多条复杂的SQL语句， 事务中的所有SQL语句被当作一个操作单元，也就是说，事务中的SQL语句要么都执行成功，要么全部执行失败， 事务内的SQL语句被当做一个整体，被当做一个原子进行操作。MySQL 典型的事务语句使用方法可参见 mysql事务（二）——控制语句使用，详细用法见 MySQL事务控制语句（学习笔记） ，事务处理实例可参考 mysql事务处理语句及使用

衡量事务的四个特性（ACID）

按照严格的标准，只有同时满足ACID特性才是事务；但是在各大数据库厂商的实现中，真正满足 ACID 的事务少之又少。 例如 MySQL 的 NDB Cluster 事务不满足持久性和隔离性；InnoDB 默认事务隔离级别是可重复读，不满足隔离性； Oracle 默认的事务隔离级别为 READ COMMITTED，不满足隔离性……因此与其说 ACID 是事务必须满足的条件， 不如说它们是衡量事务的四个维度

事务特性	描述	实现原理
原子性（Atomicity，或称不可分割性）	原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做；如果事务中一个sql语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。	undo log保证了事务的原子性
一致性（Consistency）	一致性是指事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。数据库的完整性约束包括但不限于：实体完整性（如行的主键存在且唯一）、列完整性（如字段的类型、大小、长度要符合要求）、外键约束、用户自定义完整性（如转账前后，两个账户余额的和应该不变）。	原子性、持久性和隔离性都是为了实现事务的一致性
隔离性（Isolation）	事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰	(一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性；(一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性。InnoDB默认的隔离级别是RR，RR的实现主要基于锁机制（包含next-key lock）、MVCC（包括数据的隐藏列、基于undo log的版本链、ReadView）
持久性（Durability）	持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响	MySQL的innoDB存储引擎，使用Redo log保证了事务的持久性

数据库中的一致性可以分为数据库外部的一致性和数据库内部的一致性。前者由外部应用的编码来保证， 即某个应用在执行转帐的数据库操作时，必须在同一个事务内部调用对帐户A和帐户B的操作。 如果在这个层次出现错误，这不是数据库本身能够解决的，也不属于我们需要讨论的范围。后者由数据库来保证， 即在同一个事务内部的一组操作必须全部执行成功(或者全部失败)。这就是事务处理的原子性。

如果在事务中第一次读取采用非加锁读，第二次读取采用加锁读，则如果在两次读取之间数据发生了变化， 两次读取到的结果不一样，因为加锁读时不会采用 MVCC

undo log 实现原子性

MySQL 的日志有很多种，如二进制日志、错误日志、查询日志、慢查询日志等， 此外 InnoDB 存储引擎还提供了两种事务日志：redo log(重做日志)和undo log(回滚日志)。 其中 redo log用于保证事务持久性；undo log则是事务原子性和隔离性实现的基础。

实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的 sql 语句。InnoDB 实现回滚， 靠的是 undo log：当事务对数据库进行修改时，InnoDB 会生成对应的 undo log； 如果事务执行失败或调用了 rollback，导致事务需要回滚， 便可以利用 undo log 中的信息将数据回滚到修改之前的样子。

undo log 属于逻辑日志，它记录的是 sql 执行相关的信息。当发生回滚时， InnoDB 会根据 undo log的内容做与之前相反的工作：对于每个 insert，回滚时会执行 delete； 对于每个 delete，回滚时会执行 insert；对于每个 update，回滚时会执行一个相反的 update，把数据改回去。

redo log 实现持久性

InnoDB 作为 MySQL 的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘 IO，效率会很低。 为此，InnoDB 提供了缓存(Buffer Pool)，Buffer Pool 中包含了磁盘中部分数据页的映射， 作为访问数据库的缓冲：当从数据库读取数据时，会首先从 Buffer Pool 中读取，如果 Buffer Pool 中没有， 则从磁盘读取后放入 Buffer Pool；当向数据库写入数据时，会首先写入 Buffer Pool， Buffer Pool 中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。

Buffer Pool 的使用大大提高了读写数据的效率，但是也带了新的问题：如果 MySQL 宕机， 而此时 Buffer Pool 中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。

于是，redo log 被引入来解决这个问题：当数据修改时，除了修改 Buffer Pool 中的数据， 还会在 redo log 记录这次操作；当事务提交时，会调用 fsync 接口对 redo log 进行刷盘。 如果 MySQL 宕机，重启时可以读取 redo log 中的数据，对数据库进行恢复。 redo log 采用的是 WAL（Write-ahead logging，预写式日志），所有修改先写入日志， 再更新到 Buffer Pool，保证了数据不会因 MySQL 宕机而丢失，从而满足了持久性要求。

在这个事务提交前，将 redo log 的写入拆成了两个步骤，prepare 和 commit，这就是”两阶段提交”。

为什么要采用两阶段提交呢？

实际上，两阶段提交是分布式系统常用的机制。MySQL 使用了两阶段提交后，也是为了保证事务的持久性。 redo log 和 bingo 有一个共同的数据字段叫 XID, 崩溃恢复的时候，会按顺序扫描 redo log。

• 假设在写入 binlog 前系统崩溃，那么数据库恢复后顺序扫描 redo log，碰到只有 parepare、 而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务，而且 binlog 也没写入，所以事务就直接回滚了。
• 假设在写入 binlog 之后，事务提交前数据库崩溃，那么数据库恢复后顺序扫描 redo log， 碰到既有 prepare、又有 commit 的 redo log，就直接提交，保证数据不丢失。

插入数据的过程中，生成的日志都得先写入 redo log buffer ，等到 commit 的时候，才真正把日志写到 redo log 文件。 （当然，这里不绝对，因为redo log buffer可能因为其他原因被迫刷新到redo log）。而为了确保每次日志都能写入日志文件， 在每次将 重做日志缓冲 写入 重做日志文件 后，InnoDB 存储引擎都需要调用一次 fsync 操作，确保写入了磁盘。

而为了确保每次日志都能写入日志文件，在每次将重做日志缓冲 写入 重做日志文件 后，InnoDB存储引擎都需要调用一次fsync操作，确保写入了磁盘。

锁和 MVCC 机制实现隔离性

MySQL 的锁机制可以参见MySQL锁总结，结合案例分析可 参见MySQL锁详解。MVCC 可参见 正确的理解MySQL的MVCC及实现原理，其案例分析可参见 MYSQL MVCC实现原理，版本链形象化描述可参见 mvcc原理详解 MVCC 具象化描述可参见 MySQL MVCC底层原理详解MySQL MVCC底层原理详解

日志文件

• 日志文件的类型可参见 MySQL不会丢失数据的秘密，就藏在它的 7种日志里、MySQL系列之十一 日志记录 、MySQL中的日志文件 你全都了解吗？ 或 详细分析MySQL的日志(一)
• binlog、undo log、redo log 参见彻底搞懂mysql日志系统binlog,redolog,undolog、必须了解的mysql三大日志,你知道几个？
• binlog 详解可参见 MySQL 数据库之Binlog日志使用总结
• 事务日志详解可参见 详细分析MySQL事务日志(redo log和undo log)、说说MySQL中的Redo log Undo log都在干啥 、Redo与Undo的理解

redo 和 bin 日志区别(联系见该文)

• 产生位置不同： redo log是属于innoDB层面，binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。
• 记录内容不同： redo log是物理日志，记录该数据页更新的内容；binlog是逻辑日志，记录的是这个更新语句的原始逻辑
• 生成方式不同： redo log是循环写，日志空间大小固定；binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。
• 使用目的不同： binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。
• 写入时间不同： binlog仅仅在事务提交前，只写磁盘⼀次，不论这时该事务多⼤；重做⽇志，在事务进⾏过程中，会发⽣不断地写⼊

redolog 和 undolog的区别

• 作用不同： redolog即重做⽇志，⽤来保证事务的原⼦性和持久性。正常情况⽤不到，只有 mysql 实例所在的主机断电了， Innodb存储引擎会使⽤redolog恢复到掉电前的时刻，保证数据完整性；undolog⽤来保证事务的⼀致性，当事务需要回滚时，就会⽤到undolog
• 位置不同： redolog存储在重做⽇志⽂件中， undo放在数据库内部的⼀个特殊的段，这个段被称 为undo段。 undo段位于共享表空间内
• 类型不同： redo是物理⽇志，记录的是⻚的物理修改操作。undo是逻辑⽇志，根据每⾏记录进⾏记录。 当事务回滚时， undolog回滚⾏记录到某个特定版本。只是将数据库逻辑地恢复到原来的样⼦， ⽽不是完整的将数据库回滚到事务开始前的样⼦。 事务回滚可能有各种原因，⽐如死锁回滚，⽐如⽤⽤户主动rollback等.

日志落魄策略

Mysql 普通落盘策略可参见 MySQL InnoDB的磁盘文件及落盘机制 和 mysql数据落盘详解_MySQL的Flush-List和脏页的落盘机制 redo 日志刷盘策略可参见 redo日志的刷盘策略

数据丢失的场景

可参见 MySQL数据库丢失数据场景分析、 [MySQL] 如何做到不丢数据

主从同步

可参见 Mysql主从同步的原理、 小白都能懂的Mysql主从复制原理（原理+实操）、 深度探索MySQL主从复制原理、

主从存在的疑问

MySQL主备、主从、读写分离详解

存储过程

存储过程是⼀个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建⼀次，以 后在该程序中就可以调⽤多次。如果某次操作需要执⾏多次SQL，使⽤存储过程⽐单纯SQL 语句执⾏要快。

优点

• 1）存储过程是预编译过的，执⾏效率⾼。
• 2）存储过程的代码直接存放于数据库中，通过存储过程名直接调⽤，减少⽹络通讯。
• 3）安全性⾼，执⾏存储过程需要有⼀定权限的⽤户。
• 4）存储过程可以重复使⽤，减少数据库开发⼈员的⼯作量。

缺点

• 1）调试麻烦，但是⽤ PL/SQL Developer 调试很⽅便！弥补这个缺点。
• 2）移植问题，数据库端代码当然是与数据库相关的。但是如果是做⼯程型项⽬，基本不存在移植问题。
• 3）重新编译问题，因为后端代码是运⾏前编译的，如果带有引⽤关系的对象发⽣改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运⾏时刻⾃动编译）

存储过程的具体细节可参见 mysql存储过程详细教程

视图

所谓视图，本质上是⼀种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含⼀系 列带有名称的列和⾏数据。但是，视图并不在数据库中以储存的数据值形式存在。⾏和列数 据来⾃定义视图的查询所引⽤基本表，并且在具体引⽤视图时动态⽣成。

为了提⾼复杂SQL语句的复⽤性和表操作的安全性， MySQL数据库管理系统提供了视图特 性。视图使开发者只关⼼感兴趣的某些特定数据和所负责的特定任务，只能看到视图中所定 义的数据，⽽不是视图所引⽤表中的数据，从⽽提⾼了数据库中数据的安全性。

视图相关的一些具体细节可参见 MySQL视图详解

索引

可参见 彻底搞懂MySQL的索引、 彻底搞懂MySQL的索引、 MySQL索引到底怎么回事、 mysql 索引是怎么实现的？、 MySQL索引完全解读 彻底理解 MySQL 的索引机制，终于不再因为 MySQL 优化而被面试官鄙视了

不走索引的情况

可参见 mysql中的索引使用以及索引失效及索引常见面试题、 详解mysql什么时候不走索引、 总结mysql索引失效的N种情况、 MySQL中有哪些情况下数据库索引会失效详析、 mysql什么时候需要建索引，什么是后不要建索引？

存储引擎

MyISAM 和 InnoDB 对比可参见 MySQL存储引擎详解， 各大存储引擎简介可参见 MySQL 常用存储引擎详解和区别、 MySQL 各种存储引擎详解， 各大引擎的适用场景可参见 mysql的存储引擎详解_Mysql存储引擎详解

【注意】本文属于作者原创，欢迎转载！转载时请注明以下内容:

（转载自）ShengChangJian's Blog编程技术文章地址:

https://ShengChangJian.github.io/2016/07/mysql.html

主页地址：https://shengchangjian.github.io/