MySQL 杂谈

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

• 基本概念
  • MySQL 基本框架图
  • 参考文档
• SQL 语句
  • 数据库连接
  • 缓存
  • 分析器或解析器
  • 优化器
  • 语句执行流程
    • 查询语句执行流程
      • 查询状态
      • 查询优化
      • 自增id
    • update 语句执行流程
    • insert 语句执行流程
    • delete 语句执行流程
• 事务机制
  • 衡量事务的四个特性（ACID）
    • undo log 实现原子性
    • redo log 实现持久性
    • 锁机制实现的隔离性
      • MyISAM 表锁
      • InnoDB行级锁和表级锁
      • 死锁
      • 一些优化锁性能的建议
      • 乐观锁、悲观锁
      • 参考文档
    • MVCC 实现隔离性
      • 事务并发场景
      • MVCC 的实现原理
        • undo log
        • ReadView
        • MVCC在四种隔离级别下的区别
        • 参考文档
• 日志文件
  • redo log
    • 为什么需要redo log
    • redo log特性
  • undo log
    • 回滚
    • 前滚
  • bin log(归档日志)
    • 主从同步
    • 基于时间点还原
    • binlog刷盘时机
    • binlog日志格式
    • 删除binlog
  • relay log(中继日志)
  • slow query log
  • general query log
  • error log
  • 重要log的区别
  • 参考文档
• 主从同步
  • 循环复制问题
  • 主备延迟
  • 主备切换策略
    • 可靠性优先策略
    • 可用性优先策略
• 存储过程
• 视图
• 索引
  • 索引概述
    • 索引的分类
    • 各种索引的底层实现概述
    • 常见问题
  • 索引存储类型
    • HASH 索引
    • 全文索引
    • B树
    • B+树索引
  • 索引查询
    • 为什么索引能提高查询速度
    • 使用索引后的查询基本流程
  • mysql索引是怎么实现的
    • 行记录
    • 数据页
    • 快速查询的秘籍：B+树索引
    • 索引的优缺点及建议
  • 不走索引的情况
• 存储引擎

基本概念

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

MySQL 基本框架图

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

参考文档

• 其中较为底层的原理可参见一篇文章让你搞懂MYSQL底层原理，
• 更详细的文档可参考官方文档
• 操作手册
• mysql组成部分总览
• mysql组件间的协同流程
• 有人总结的面试经典
  • 《深入精通Mysql（一）》Mysql整体架构和sql执行过程（面试高频题
  • 《深入精通Mysql（二）》深入底层剖析Mysql索引（面试必问）
  • 《深入精通Mysql（三）》深入底层剖析Mysql各种锁机制（面试必问）
  • 《深入精通Mysql（四）》MySQL 事务机制，中高级开发面试必问！
  • 深入了解MySQL主从复制的原理
  • 《深入精通Mysql（六）》系列之如何通过慢查询日志进行SQL分析和优化
  • 《深入精通Mysql（七）》系列之如何通过EXPLAIN 执行计划分析SQL语句的性能瓶颈
  • 配置可能引发一些问题：MySQL部署和运维、mysql配置和问题、mysql配置参数详解

SQL 语句

MySQL 语句可以分为以下几类，详细的可参考标准SQL。

• mysql支持的数据类型可参考官方文档
• mysql与标准的SQL的关系可参考标准SQL扩展、与标准SQL的差异
• mysql操作可参考官方文档

类型	包含的指令
DDL((Data Definition Language，数据定义语言))	create: 创建数据库及其对象(包括新建表，索引，视图，存储过程，用户等)
	alter: 改变现有数据库或表的结构 (包括修改索引，修改字段类型，删除索引)
	truncate: 删除表中所有记录，并删除已分配的空间
	comment: 添加注释
	rename: 重命名，其中包括索引、表名等。
	use: 切换数据库。
	show: 查看所有的数据库或表或建表语句，后面可接 databases，tables，或者 show create databases或table 库名或表名。
	desc: 查看表结构 desc 表名
	drop: 删除数据库或表或用户，drop database或table 库名或表名
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: 设置事务隔离级别

这里以存储过程的操作为例：

-- 创建存储过程
CREATE PROCEDURE proname() 
BEGIN
select * from table;
END;

-- 执行存储过程
CALL proname();

-- 删除存储过程
DROP PROCEDURE proname;

数据库连接

当你在终端敲下该命令 mysql -h 主机名(ip) -u 用户名 -P 端口 -p密码 数据库名 --default-character-set=编码类型 （详细参数说明可参考官方文档 回车之后发生了什么？

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

连接完成之后如果没有后续的动作，这个时候，mysql会将该链接设置为空闲状态，我们一起来看看我的mysql服务器一共有多少个客户端处于连接状态，输入命令：

sqlshow processlist;

• id：线程的唯一标识。
• User：启动这个线程的用户。
• Host：记录了发送请求的客户端IP和端口信息。
• db：当前执行的命令发生在哪个数据库中，如果没有指定为NULL，比如我刚刚查询的sql：sqlshow processlist 并没有指定数据库，所以展示NULL。
• Command：此刻线程正在执行的命令。
• Time：该线程处于当前状态的时间。
• State：线程状态，与Command对应。
• Info：记录着线程执行的sql语句，默认展示前100个字符，如果需要查看全部，请执行 show full processlist。

我们一起来看看Command一共有哪些值：

• Change user线程正在执行更改用户操作。
• Close stmt线程正在关闭准备好的语句。
• Connect副本连接到其源。
• Connect Out副本正在连接到其源。
• Create DB线程正在执行创建数据库操作。
• Daemon该线程在服务器内部，而不是为客户端连接提供服务的线程。
• Debug该线程正在生成调试信息。
• Delayed insert该线程是一个延迟的插入处理程序。
• Drop DB线程正在执行放置数据库操作。
• Error
• Execute线程正在执行准备好的语句。
• Fetch线程正在从执行准备好的语句中获取结果。
• Field List该线程正在检索表列的信息。
• Init DB线程正在选择默认数据库。
• Kill该线程正在杀死另一个线程。
• Long Data执行准备好的语句的结果是线程正在检索长数据。
• Ping线程正在处理服务器ping请求。
• Prepare该线程正在准备一个准备好的语句。
• Processlist该线程正在生成有关服务器线程的信息。
• Query线程正在执行一条语句。
• Quit线程正在终止。
• Refresh该线程是刷新表，日志或缓存，或者重置状态变量或复制服务器信息。
• Register Slave线程正在注册副本服务器。
• Reset stmt该线程正在重置准备好的语句。
• Set option该线程正在设置或重置客户端语句执行选项。
• Shutdown线程正在关闭服务器。
• Sleep线程正在等待客户端向其发送新语句。
• Statistics线程正在生成服务器状态信息。
• Time没用过。

若客户端一段时间没有使用则会被置为空闲（Sleep）状态，但如果客户端长时间没有操作，那么服务器就会自动将连接断开， 默认时长 8 小时，不过可以通过 wait_timeout 参数设置。

超过时长，连接被断开之后，客户端发起请求，那么该客户端将会收到一个：Lost connection to MySQL server during query， 这个时候就只能通过重新建立连接进行操作。

连接分为两种类型，一个为长连接，一个为短连接，建立连接之后，客户端发送持续请求， 如果一直在同一个连接中，那么这个就是长连接，如果每个请求一个连接，则是短连接， 我们知道连接会有用户信息的校验，权限的验证，比较麻烦，所以推荐使用长连接进行操作， 但是长连接也不是十全十美，有利肯定就有弊，长连接过多时会导致mysql占用的内存过多， 导致内存紧张，极端情况可能导致内存泄漏（OOM），那我们如何解决这个问题呢？

可以，定时清除长连接。通过执行 mysql_reset_connection 来重新初始化连接资源，不过要求mysql的版本在5.7或之上。

连接器会到 MySQL 的权限表中查询当前连接拥有的权限。查询到权限之后，只要这个连接没有断开，则这个连接涉及到的权限操作都会依赖此时查询到的权限。 换句话说，一个用户登录 MySQL 并成功连接 MySQL 后，哪怕是管理员对当前用户的权限进行了修改操作， 此时只要这个用户没有断开 MySQL 的连接，就不会受到管理修改权限的影响。管理员修改权限后，只有对新建的连接起作用。

参考文章

• 具体的协议包分析可参考mysql通信协议抓包分析、Mysql 通讯协议分析、 通信方式类别。 mysql 客户端与服务端的通信协议可参考官方文档。
• 对于 TCP 的连接和断开分析可参考 4个实验，彻底搞懂TCP连接的断开。
• 不用的版本或配置，可能会导致相同的语句的执行结果或报错不一样，配置可参考Mysql数据库配置参数详解大全、mysql配置参数详解

缓存

mysql会在缓存中检测之前是否执行过这条语句，如果被执行过，那么查询的结果将会以key-value的形式存储在缓存中， 这个时候下一次的查询直接命中缓存，直接返回相对应的数据，如果缓存中不存在当前key（sql语句），就会进入下一个阶段 - 分析器。

mysql判断缓存是否命中的方式很简单，mysql将缓存存放在一个引用表中， 通过hash值方式应用，hash值包括：查询的sql、查询的数据库、客户端协议版本等等， mysql在判断是否命中缓存的时候不会提前解析sql的语法，而是直接使用sql语句和客户端的基本信息（协议）等等， 进行hash算法，这里需要特别注意：在编写sql的时候，需要与上一次执行的sql保持完全一致，空格、注释、 编码或者有其他的任何不同的地方都会导致hash出来的结果不同，从而无法命中缓存，所以在操作时需要保持一个统一的编码规范。

除了这个还有很多情况也会导致查询的数据无法缓存，比如聚合函数、自定义函数、存储过程、用户变量、临时表、mysql库中的系统表、权限表。 mysql的缓存虽然能提升查询的性能，但是也会在其他方面造成额外的消耗，具体如下：

查询之前必须先检查是否命中缓存，对于缓存中没有的sql多了一次缓存的查询。 第一次查询或者表中的数据被修改时，当前查询需要将结果写入到缓存中，带来了额外的系统消耗。 mysql在写操作时会将关于当前相关缓存的数据全部清空，如果缓存的数据过大，或者缓存的sql语句过多，可能会导致很大的系统消耗。

所以，缓存的好处可以提升查询的效率，弊端可能给系统带来额外的系统消耗，尤其是在InnoDB中的事务中， 所以在使用的时候需要慎重，不可为了查询效率二盲目的使用缓存，使用不当，可能适得其反。

那mysql如何开启缓存呢？只需要将参数 query_cache_type 设置成 DEMAND 即可，这样会导致整个mysql都是使用缓存， 很明显，这是不被推荐的，所以还有一种方式，那就是按需指定， 什么叫按需指定呢？就是在你需要缓存的sql语句加上 SQL_CACHE 指定使用缓存即可，代码如下：

select SQL_CACHE * from sys_user where id = 1;

在这里有两点需要特别注意：

• mysql在8.0版本直接将缓存模块删除，也就是说，mysql8.0所有的查询都不会走向缓存了，而是直接前往磁盘；
• 查询缓存的返回直接也会校验权限信息的，如果没有权限，就算使用了缓存，也无法查询。

分析器或解析器

mysql在缓存中没有命中之后将会进入流程的下一步，但这里并不会直接进入解析器，而是需要先将查询的sql转换成一个执行计划， 在经过这个执行计划和存储引擎进行交互，这里就包括了：解析器、预处理、优化器、执行器。

生成完执行计划之后，mysql会对sql的关键字进行解析，生成一棵对应的 “解析树”， 在这个解析过程中，mysql解析器会使用语法规则对sql进行解析和校验， 第一步做的时词法分析，mysql执行的并不是你写的sql语句，而是将你写的sql语句解析成mysql可以执行的语句。

生成“解析树“之前还需要校验你的sql语句写的是否有问题，是否满足mysql的语法，如果你输入的sql语句存在问题，这个时候程序将会抛出异常，结束查询。

优化器

分析器完成之后，语法树已经是合法的了，这个时候优化器就登场了，优化器将这条语法树转化成执行计划。 mysql官方也是很为我们开发人员着想，设置了这个优化器，在开发过程中，我们想要查询一条sql语句，执行的方式有很多， 比如是否走索引、走哪条索引、关联查询哪张表做主表等等，这些都是可变的， 而优化器的作用就是根据程序员写的sql语句找到一条它认为最好的执行计划。

经过优化器优化的sql，是否总是最优的执行计划呢？答案是否定的，哪些情况会导致优化器生成的执行计划效果反而更差呢？ 以下七点参考于《高性能mysql》。

• 统计信息不完整或者不准确，比如InnoDB的MVCC多版本并发控制会导致表数据行的统计不准确。
• 执行计划中的成本并不等同于实际执行的成本，这个时候即使统计的信息很准确，优化器给出的执行计划也有可能不是最优的。 举个例子，有些时候某个执行嘉华虽然需要读取更多的页面或者数据，但是它的实际成本可能会很小，为什么呢？ 原因很简单，如果读取的页面都是有序的或者这些页面(数据)已经被加载到内存中了， 这个时候的访问成本比执行计划估计的成本小得多，mysql并不知道哪些数据存在内存，哪些数据存在磁盘中，所以IO的次数也是未知数。
• mysql的最优可能和你想得不一样，你可能希望执行时间越短越好，但是mysql只是基于成本模型选择最优的执行计划， 而有些时候这并不是最快的执行方式。所以这里我们看到的根据执行计划成本来选择执行计划并不是完美的模型。
• mysql从不考虑其他并发执行的查询，这可能影响到当前查询的速度。
• mysql并不是完全基于成本优化，有时候也会给予一些固定的规则，例如存在全文索引的match()子句， 则在存在全文索引的时候就是用全文索引，即使有时候使用别的索引和where条件可以远比全文索引的方式要快很多，但是mysql也会选择使用全文索引。
• mysql不会考虑不受其控制的操作成本，例如存储过程或者用户自定义函数的成本。
• 优化器有时候无法估算所有可能的执行计划，所以它可能错过实际上最优的执行计划。

mysql的优化器是一个非常复杂的组件，算法很多优化的策略也有很多，它会通过自己的优化策略选择出优化器认为最优的一个执行计划，优化策略虽然很多， 但是在大致上可以分为两种：静态优化、动态优化。

• 静态优化：可以直接对解析树进行分析、优化。优化器可以通过代数变换将where条件转换为另外一种等价形态，这个转换不依赖条件的具体数值，即使where条件中的数值发生改变， 静态优化也仍然有效。可以理解为”编译时优化“。
• 动态优化：它与查询的上下文相关，影响动态优化的因素有很多，比如索引对应的数量行数、where条件中的值等等， 这些都会让优化器在执行sql的时候重新进行优化。可以理解为”运行时优化“。

所以优化器对sql进行优化的时候是选择静态优化还是动态优化取决于sql语句，静态优化只需要做一次，而动态优化在每次执行的时候都需要重新评估。

语句执行流程

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

• 连接器：连接mysql服务器，进行权限验证
• 缓存：保存上次查询的结果，提高性能
• 分析器：词法与语法分析
• 优化器：对你的查询语句做出适当的优化
• 执行器：操作存储引擎，读写数据
• 存储引擎：存储数据

mysql基本分为两大组件，一个是server层、一个是存储引擎，为什么server和存储引擎会被单独分开呢？ 看上面的图应该也不难想象，mysql的存储引擎是可以随场景变化的，它可以是MYISAM、InnoDB、也可以是Memory，所以它被设计成了插入式。

查询语句执行流程

首先需要强调的是，实现相同的功能，可以用不同的SQL语句实现，但具体的实现或执行过程有所不同，当然性能和执行效率也是不同的。 这就给优化带来了很大的空间。

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

mysql执行一个查询操作的完整流程为：客户端通过连接器建立连接，这个操作进行权限验证，通过之后会先前往缓存， 根据sql作为key去查询，查到直接返回，否者前往分析器，经过分析器对sql语句的分析、解析， 得到一个mysql可以理解的语法，随后进入优化器，mysql会根据你查询的条件进行适当的优化， 之后在经过执行器，这就真正的开始前往存储引擎查询数据，最后将查询到的数据返回给客户端，顺便写入缓存（不一定）。

--查询组合字段
(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 select实现原理
• join 实现原理可参见 MySQL 的Join及底层实现原理、
• 数据库基础（七）Mysql Join算法原理，
• 各种join可参见 图解MySQL里的各种 JOIN，看完不懂来找我！
• MySQL order by、group by底层实现及优化（非常详细）、
• MySQL order by实现原理分析和Filesort优化、
• Mysql学习之order by的工作原理、
• mysql ORDER BY,GROUP BY 和DISTINCT原理
• MySQL分组查询Group By实现原理详解
• mysql having和where的区别、
• mysql where和having的区别、
• Mysql-where子句与having子句的区别

查询状态

对于 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。 可以通过这个线上实验网站进行 explain 实验。

• 示例可参见官方文档
• 万字SQL优化大全
• MySQL的SQL优化常用30种方法
• 21个SQL语句优化规范方法
• MySQL数据库优化技巧大全
• MySql基础知识总结（SQL优化篇）
• MySQL数据库优化总结
• MySQL定位并优化慢查询sql的详细实例

字段名	描述	值
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 代替

自增id

在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一，单机递增), 而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究竟有什么坏处？

自增的主键的值是顺序的,所以Innodb把每一条记录都存储在一条记录的后面。 当达到页面的最大填充因子时候(innodb默认的最大填充因子是页大小的15/16,会留出1/16的空间留作以后的修改)：

• 下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费
• 新插入的行一定会在原有的最大数据行下一行,mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗
• 减少了页分裂和碎片的产生

那么使用自增id就完全没有坏处了吗？并不是，自增id也会存在以下几点问题：

• 别人一旦爬取你的数据库,就可以根据数据库的自增id获取到你的业务增长信息，很容易分析出你的经营情况
• 对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争
• Auto_Increment锁机制会造成自增锁的抢夺,有一定的性能损失
• Auto_increment的锁争抢问题，如果要改善，需要调优innodb_autoinc_lock_mode的配置

有时候分布式系统需要高性能的全局id，可以参考以下文章的视角：

• MySQL分布式环境下生成全局自增有序ID(雪花算法Snowflake)

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分两部执行：先删除该行，再插入一行目标行。

相关文章

• 一条 update 语句把公司系统干崩溃了

insert 语句执行流程

这里只给出简约的流程图（详见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 操作速度也会有提升。

事务机制

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

• 事务（transaction）指一组SQL语句；
• 回退（rollback）指撤销指定SQL语句的过程；
• 提交（commit）指将未存储的SQL语句结果写入数据库表；
• 保留点（savepoint）指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务处理不同）

-- 使用回退
START TRANSACTION
delete from table  where id=123; --删除的数据 ROLLBACK 后将会还原
ROLLBACK;

-- 使用提交
START TRANSACTION
delete from table  where id=123;
delete from table  where id=123;
COMMIT; 
-- COMMIT语句仅在不出错时写出更改，只要有一条语句错误 ，所有语句将会回退。

-- 使用保留点
-- 使用保留点处理部分提交或回退
SAVEPOINT pointName; -- 定义一个保留点
ROLLBACK TO pointName; -- 回退到指定保留点
RELEASE SAVEPOINT pointName; --明确地释放保留点

衡量事务的四个特性（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操作，确保写入了磁盘。

锁机制实现的隔离性

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。 锁是Mysql在服务器层和存储引擎层的的并发控制。

加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等。 MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现。

根据锁的排他性，可分为：

• 共享锁（读锁）：其他事务可以读，但不能写。
• 排他锁（写锁）：其他事务不能读取，也不能写。

根据锁的粒度或者锁定的资源范围，可分为：

• 表级锁：
  • 开销小，加锁快；不会出现死锁
  • 锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • 这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。
  • 表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用
• 行级锁：
  • 开销大，加锁慢；会出现死锁；
  • 锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
  • 最大程度的支持并发，同时也带来了最大的锁开销。
  • 在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。
  • 行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统
• 页面锁：
  • 开销和加锁时间界于表锁和行锁之间；会出现死锁
  • 锁定粒度界于表锁和行锁之间，并发度一般。

不同的存储引擎，支持的锁机制也不尽相同：

• MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）
• BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁
• InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令。 但是在有的情况下， 用户需要明确地进行锁表或者进行事务的控制， 以便确保整个事务的完整性， 这样就需要使用事务控制和锁定语句来完成。

MyISAM 表锁

MyISAM表级锁模式：

• 表共享读锁（Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
• 表独占写锁（Table Write Lock）：会阻塞其他用户对同一表的读和写操作；

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待，直到锁被释放为止。

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求， 然后再给读锁队列中等候的获取锁请求。

这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁， 从而可能永远阻塞。同时，一些需要长时间运行的查询操作，也会使写线程“饿死” ， 应用中应尽量避免出现长时间运行的查询操作（在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解” ， 使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行， 比如一些定期统计可以安排在夜间执行）。

可以设置改变读锁和写锁的优先级：

• 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
• 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
• 给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后， MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

MyISAM加表锁方法：

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的表加读锁，在执行更新操作 （UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预， 因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。 MyISAM存储引擎支持并发插入，以减少给定表的读和写操作之间的争用：

• 如果MyISAM表在数据文件中间没有空闲块，则行始终插入数据文件的末尾。 在这种情况下，你可以自由混合并发使用MyISAM表的INSERT和SELECT语句而不需要加锁——你可以在其他线程进行读操作的时候， 同时将行插入到MyISAM表中。 文件中间的空闲块可能是从表格中间删除或更新的行而产生的。
• 如果文件中间有空闲快，则并发插入会被禁用，但是当所有空闲块都填充有新数据时，它又会自动重新启用。 要控制此行为，可以使用MySQL的concurrent_insert系统变量。
  • 当concurrent_insert设置为0时，不允许并发插入。
  • 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个线程读表的同时，另一个线程从表尾插入记录。这也是MySQL的默认设置。
  • 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。
• 如果你使用LOCK TABLES显式获取表锁，则可以请求READ LOCAL锁而不是READ锁，以便在锁定表时，其他会话可以使用并发插入。

查询表级锁争用情况

可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁的争夺， 如果 Table_locks_waited 的值比较高，则说明存在着较严重的表级锁争用情况：

mysql> SHOW STATUS LIKE 'Table%';

InnoDB行级锁和表级锁

InnoDB 实现了以下两种类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁

锁模式的兼容情况：

如果一个事务请求的锁模式与当前的锁兼容， InnoDB 就将请求的锁授予该事务； 反之， 如果两者不兼容，该事务就要等待锁释放。

InnoDB加锁方法：

• 意向锁是 InnoDB 自动加的， 不需用户干预。
• 对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB 会自动给涉及数据集加排他锁（X)；
• 对于普通 SELECT 语句，InnoDB 不会加任何锁； 事务可以通过以下语句显式给记录集加共享锁或排他锁：
  • 共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
  • 排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

隐式锁定： InnoDB在事务执行过程中，使用两阶段锁协议。

• 随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；
• 锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

显式锁定 ：

select ... lock in share mode //共享锁 
select ... for update //排他锁

select for update：

• 在执行这个 select 查询语句的时候，会将对应的索引访问条目进行上排他锁（X 锁），也就是说这个语句对应的锁就相当于update带来的效果。
• select *** for update 的使用场景：为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到 for update 子句。
• 性能影响：相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。

select lock in share mode：

• in share mode 子句的作用就是将查找到的数据加上一个 share 锁，这个就是表示其他的事务只能对这些数据进行简单的select 操作，并不能够进行 DML 操作。
• 使用场景：为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。 但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。
• 性能影响：语句是一个给查找的数据上一个共享锁（S 锁）的功能，它允许其他的事务也对该数据上S锁，但是不能够允许对该数据进行修改。 如果不及时的commit 或者rollback 也可能会造成大量的事务等待。

for update 和 lock in share mode 的区别：

• 前一个上的是排他锁（X 锁），一旦一个事务获取了这个锁，其他的事务是没法在这些数据上执行 for update ；
• 后一个是共享锁，多个事务可以同时的对相同数据执行 lock in share mode。

InnoDB 行锁实现方式：

• InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。 InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
• 不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
• 只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的， 如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。 因此，在分析锁冲突时，别忘了检查 SQL 的执行计划（可以通过 explain 检查 SQL 的执行计划），以确认是否真正使用了索引。
• 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。应用设计的时候要注意这一点。

InnoDB的间隙锁：

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

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。 因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

InnoDB使用间隙锁的目的：

• 防止幻读，以满足相关隔离级别的要求；
• 满足恢复和复制的需要：

MySQL 通过 BINLOG 录入执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句，并由此实现 MySQL 数据库的恢复和主从复制。 MySQL 的恢复机制（复制其实就是在 Slave Mysql 不断做基于 BINLOG 的恢复）有以下特点：

• MySQL 的恢复是 SQL 语句级的，也就是重新执行 BINLOG 中的 SQL 语句。
• MySQL 的 Binlog 是按照事务提交的先后顺序记录的， 恢复也是按这个顺序进行的。

由此可见，MySQL 的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。

InnoDB 在不同隔离级别下的一致性读及锁的差异：

锁和多版本数据（MVCC）是 InnoDB 实现一致性读和 ISO/ANSI SQL92 隔离级别的手段。 因此，在不同的隔离级别下，InnoDB 处理 SQL 时采用的一致性读策略和需要的锁是不同的：

对于许多 SQL，隔离级别越高，InnoDB 给记录集加的锁就越严格（尤其是使用范围条件的时候）， 产生锁冲突的可能性也就越高，从而对并发性事务处理性能的 影响也就越大。

因此， 我们在应用中， 应该尽量使用较低的隔离级别， 以减少锁争用的机率。 实际上，通过优化事务逻辑，大部分应用使用 Read Commited 隔离级别就足够了。 对于一些确实需要更高隔离级别的事务， 可以通过在程序中执行 SET SESSION TRANSACTION ISOLATION

LEVEL REPEATABLE READ 或 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE 动态改变隔离级别的方式满足需求。

获取 InnoDB 行锁争用情况：

可以通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'innodb_row_lock%'; 

LOCK TABLES 和 UNLOCK TABLES

Mysql也支持lock tables和unlock tables，这都是在服务器层（MySQL Server层）实现的，和存储引擎无关，它们有自己的用途，并不能替代事务处理。 （除了禁用了autocommint后可以使用，其他情况不建议使用）：

• LOCK TABLES 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止。
• UNLOCK TABLES 可以释放当前线程获得的任何锁定。当前线程执行另一个 LOCK TABLES 时，或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁。

LOCK TABLES语法：

• 在用 LOCK TABLES 对 InnoDB 表加锁时要注意，要将 AUTOCOMMIT 设为 0，否则MySQL 不会给表加锁；
• 事务结束前，不要用 UNLOCK TABLES 释放表锁，因为 UNLOCK TABLES会隐含地提交事务；
• COMMIT 或 ROLLBACK 并不能释放用 LOCK TABLES 加的表级锁，必须用UNLOCK TABLES 释放表锁

例如，如果需要写表 t1 并从表 t 读，可以按如下做：

SET AUTOCOMMIT=0; 
LOCK TABLES t1 WRITE, t2 READ, ...; 
[do something with tables t1 and t2 here]; 
COMMIT; 
UNLOCK TABLES;

使用LOCK TABLES的场景：

给表显示加表级锁（InnoDB表和MyISAM都可以），一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。（与MyISAM默认的表锁行为类似）

在用 LOCK TABLES 给表显式加表锁时，必须同时取得所有涉及到表的锁，并且 MySQL 不支持锁升级。 也就是说，在执行 LOCK TABLES 后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。

其实，在MyISAM自动加锁（表锁）的情况下也大致如此，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。

例如，有一个订单表 orders，其中记录有各订单的总金额 total， 同时还有一个 订单明细表 order_detail，其中记录有各订单每一产品的金额小计 subtotal，假设我们需要检 查这两个表的金额合计是否相符，可能就需要执行如下两条 SQL：

Select sum(total) from orders; 
Select sum(subtotal) from order_detail; 

这时，如果不先给两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中， order_detail 表可能已经发生了改变。因此，正确的方法应该是：

Lock tables orders read local, order_detail read local; 
Select sum(total) from orders; 
Select sum(subtotal) from order_detail; 
Unlock tables;

在 LOCK TABLES 时加了“local”选项，其作用就是允许当你持有表的读锁时， 其他用户可以在满足 MyISAM 表并发插入条件的情况下，在表尾并发插入记录（MyISAM 存储引擎支持“并发插入”。

死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。 在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。

• 死锁产生：
  • 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。
  • 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会。
  • 死锁有双重原因：
    • 真正的数据冲突；
    • 存储引擎的实现方式。
• 检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。
• 死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。 所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。
• 外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁， 或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。
• 死锁影响性能：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。 在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。 有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效， 这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

InnoDB避免死锁：

• 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT … FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、 更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
• 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
• 通过SELECT … LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
• 改变事务隔离级别

如果出现死锁，可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息， 如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

一些优化锁性能的建议

锁级别够用即可，首先要判断是否需要（优化SQL语句，把不必要加锁的部分剔除），再者判断是否够用(不能低也不能高)：

• 尽量使用较低的隔离级别；
• 精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会
• 选择合理的事务大小，小事务发生锁冲突的几率也更小
• 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
• 不要申请超过实际需要的锁级别
• 除非必须，查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能

乐观锁、悲观锁

乐观锁(Optimistic Lock)：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。 乐观锁不能解决脏读的问题。

乐观锁, 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。 乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

悲观锁(Pessimistic Lock)：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

悲观锁，顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。 传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

参考文档

• MySQL 的锁机制可以参见MySQL锁总结
• 结合案例分析可参见MySQL锁详解

MVCC 实现隔离性

MVCC(Multi Version Concurrency Control的简称)，代表多版本并发控制。 与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。 MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

MVCC 在 MySQL InnoDB 中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

事务并发场景

数据库并发场景有三种，分别为：

• 读-读：不存在任何问题，也不需要并发控制
• 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
• 写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

事务的并发问题

• 脏读：
  • 事务A读取了事务B未提交的数据，然后B回滚操作，那么A读取到的数据是脏数据
  • 解决的基础隔离级别：读已提交
• 不可重复读：
  • 事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
  • 解决的基础隔离级别：可重复读
• 幻读：
  • 是针对数据插入操作来说的，假设在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同（事务B在事务A两次查询期间插入符合查询条件的新数据），这种现象称为幻读。
  • 解决的基础隔离级别：串行化（针对一张表的操作，每一个session依次完成自己的操作：行锁 + 间隙锁）MySQL事务隔离级别和实现原理

不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。

为了解决事务并发问题，MySQL引入了事务隔离级别：

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制， 也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联， 读操作只读该事务开始前的数据库的快照。

MVCC可以为数据库解决以下问题：

• 在并发读写数据库时，可以做到在读(select)操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
• 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

我们知道，在内存中解决读写冲突，一般使用读写锁；解决写写冲突，使用互斥锁。有了MVCC之后， 数据库解决并发问题，有了更多的选择：

• MVCC解决读写冲突，悲观锁解决写写冲突
• MVCC解决读写冲突，乐观锁解决写写冲突

其中悲观锁和乐观锁的基本概念为：

• 乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。
  • 实现方式一般有两种：数据版本机制；时间戳机制
  • 可见，MVCC 可以看做是轻量级乐观锁的一种
• 悲观锁认为被它保护的数据是极其不安全的，每时每刻都有可能变动，一个事务拿到悲观锁后（可以理解为一个用户），其他任何事务都不能对该数据进行修改，只能等待锁被释放才可以执行。

假设用版本号来实现读写并发，那读到的数据就有版本关系，据此把读操作区分为当前读和快照读：

• 当前读：就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
  • 像select 语句 lock in share mode(共享锁)
  • select 语句 for update ; 
  • update, insert ,delete(排他锁)这些操作都是一种当前读
• 快照读：快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本
  • 快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读
  • 像不加锁的select * from 操作就是快照读，即不加锁的非阻塞读，不涉及其他锁之间的冲突；
  • 可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销

MVCC就是为了实现读(select)-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现。 MVCC模型在MySQL中的具体实现则是由 3个隐式字段，undo日志 ，Read View 等去完成的。

MVCC 的实现原理

MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。 当然存储的并不是实际的时间值，而是系统版本号（system version number)。每开始一个新的事务，系统版本号都会自动递增。 事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

mysql为了实现以下隔离级别，提出了LBCC(Lock-Based Concurrent Control，基于锁的并发控制)与MVCC(Multi-Version Concurrent Control，基于多版本的并发控制)。

在LBCC中，读写冲突，会使用诸如记录锁、间隙锁与临键锁等锁来实现数据的并发安全，因此读写性能不高。

在MVCC中，读写不冲突，记录每一行的多个版本，来避免在多个事务之间的竞争。以空间换时间的思路，极大地提高了读写性能。 MVCC主要靠undo log版本链与ReadView来实现。

undo log

undo log主要用于事务回滚时恢复原来的数据。 mysql在执行sql语句时，会将一条逻辑相反的日志保存到undo log中。因此，undo log中记录的也是逻辑日志。

• 当sql语句为insert时，会在undo log中记录本次插入的主键id。等事务回滚时，delete此id即可。
• 当sql语句为update时，会在undo log中记录修改前的数据。等事务回滚时，再执行一次update，得到原来的数据。
• 当sql语句为delete时，会在undo log中记录删除前的数据。等事务回滚时，insert原来的数据即可。

数据库事务四大特性中的原子性，即事务具有不可分割性，要么全部成功，要么全部失败，其底层就靠undo log实现。 在某一步执行失败时，会对之前事务的语句进行回滚。

行的隐藏列

在数据库中的每一行上，除了存放真实的数据以外，还存在着3个隐藏列——row_id、trx_id与roll_pointer。

• row_id：行号
  • 如果当前表有整数类型的主键，则row_id就是主键的值。
  • 如果没有整数类型的主键，则mysql会按照字段顺序选择一个非空的整数类型的唯一索引作为row_id。
  • 如果mysql没有找到，则会自动生成一个自动增长的整数作为row_id。
• trx_id：事务号
  • 当一个事务开始执前，mysql会为这个事务分配一个全局自增的事务id。
  • 之后该事务对当前行进行的增、删、改操作时，都会将自己的事务id记录到trx_id中。
• roll_pointer：回滚指针
  • 事务对当前行进行改动时，会将旧数据写入进undo log中，再将新数据写入当前行，且当前行的roll_pointer指向刚才那个undo log，因此可以通过roll_pointer找到该行的前一个版本。
  • 当一直有事务对该行改动时，就会一直生成undo log，最终将会形成undo log版本链。

ReadView

在事务执行每一个快照读或事务初次执行快照读时，会生成一致性视图，即ReadView。 ReadView的作用是，判断undo log版本链中的哪些数据对当前事务可见。 ReadView包含以下几个重要的参数：

• m_ids：在创建ReadView的那一刻，mysql中所有未提交的事务id集合。
• min_trx_id：m_ids中的最小值
• max_trx_id：mysql即将为下一个事务分配的事务id，并不是m_ids中的最大值。
• creator_trx_id：即创建此ReadView的事务id

那么事务在执行快照读时，可以通过以下的规则来确定undo log版本链上的哪个版本数据可见。

• 如果当前undo log的版本的 trx_id<min_trx_id 说明该版本对应的事务在生成ReadView之前就已经提交了，因此是可见的。
• 如果当前undo log的版本的trx_id≥max_trx_id，说明该版本对应的事务在生成ReadView之后才开始的，因此是不可见的。
• 如果当前undo log的版本的trx_id∈[min_trx_id，max_trx_id)，如果在这个范围里，还要判断trx_id是否在m_ids中：
  • 在m_ids中，说明版本对应的事务未提交，因此是不可见的。
  • 不在m_ids中，说明版本对应的事务已经提交，因此是可见的。
• 如果当前undo log的版本的trx_id=creator_trxt_id，说明事务正在访问自己修改的数据，因此是可见的。

当undo log版本链表的头结点数据被判定为不可见时，则利用roll_pointer找到上一个版本，再进行判断。如果整个链表中都没有找到可见的数据，则代表当前的查询找不到数据。

MVCC在四种隔离级别下的区别

• 在Read Uncommitted级别下，事务总是读取到最新的数据，因此根本用不到历史版本，所以MVCC不在该级别下工作。
• 在Serializable级别下，事务总是顺序执行。写会加写锁，读会加读锁，完全用不到MVCC，所以MVCC也不在该级别下工作。
• 真正和MVCC兼容的隔离级别是Read Committed（RC）与Repeatable Read（RR）

MVCC在RC与RR级别下的区别，在于生成ReadView的频率不同。

• 在RC级别下，当前事务总是希望读取到别的事务已经提交的数据，因此当前事务事务会在执行每一次快照读的情况下都会去生成ReadView，实时更新m_ids，及时发现那些已经提交的事务。
• 在RR级别下，当前事务当然也能够读取到别的事务已经提交的数据，但为了避免不可重复读，因此只会在执行第一次快照读的情况下去生成ReadView，之后的快照读会一直沿用该ReadView。

在RC级别下

• 一开始，事务id为1的事务往表里插入了一条数据，版本链如下：
  
• 这个时候，开启事务id为2的事务，关闭自动提交模式。先执行一次 select * 查询，生成的ReadView为：m_ids={2}，min_trx_id=2，max_trx_id=3，creator_trx_id=2

由于该条数据的trx_id<min_trx_id，说明该版本对应的事务在生成ReadView之前就已经提交了，因此是可见的。 因此，事务2能直接查到该数据。

• 现在开启事务3，事务id为3，将该条数据的name改为b，并自动提交，版本链如下：
  
• 这个时候，事务2再次select *查询，由于处于RC级别下，会再次生成ReadView，此时的ReadView如下: m_ids={2}，min_trx_id=2，max_trx_id=4，creator_trx_id=2
• 由于最新版本的trx_id∈[2,4)且trx_id不在m_ids中，说明该版本的数据已经提交，因此是可见的，所以事务2能查到最新的数据。

在RR级下

• 事务2再次select *查询时，不会生成ReadView，而是沿用第一次生成的ReadView：m_ids={2}，min_trx_id=2，max_trx_id=3，creator_trx_id=2
• 由于最新版本的trx_id≥max_trx_id，说明该版本对应的事务在生成ReadView之后才开始的，因此是不可见的。 所以沿着roll_pointer找到上一个版本，上一个版本的trx_id<min_trx_id，说明该版本对应的事务在生成ReadView之前就已经提交了，因此是可见的。

所以，事务2只能查询到旧版本的数据，两次的查询一致，避免了不可重复读。

参考文档

• 正确的理解MySQL的MVCC及实现原理，其案例分析可参见
• MYSQL MVCC实现原理，版本链形象化描述可参见
• mvcc原理详解
• MVCC 具象化描述可参见 MySQL MVCC底层原理详解MySQL MVCC底层原理详解

日志文件

我们要知道MySQL的服务器层是不管理事务的，事务是由存储引擎实现的，而MySQL中支持事务的存储引擎又属InnoDB使用的最为广泛，所以后续文中提到的存储引擎都以InnoDB为主。

上面这张图，她是MySQL更新数据的基础流程，其中包括redo log、bin log、undo log三种日志间的大致关系，好了闲话少说直奔主题。

redo log

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

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

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

如果刷脏页还未完成，可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中数据页已经刷磁盘后，那这些记录就失效了，新日志会将这些失效的记录进行覆盖擦除。

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

write pos到check point之间的部分是redo log空余的部分（绿色）， 用来记录新的日志；check point到write pos之间是redo log已经记录的数据页修改数据， 此时数据页还未刷回磁盘的部分。当write pos追上check point时，会先推动check point向前移动， 空出位置（刷盘）再记录新的日志。

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

因为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的记录进行重放刷盘。

为什么需要redo log

我们都知道，事务的四大特性里面有一个是 持久性 ，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了， 不可能因为任何原因再回到原来的状态。 那么 mysql是如何保证一致性的呢？

最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面： 因为 Innodb 是以 页 为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！

一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！

因此 mysql 设计了 redo log ， 具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。

redo log特性

mysql 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file。这种 先写日志， 再写磁盘 的技术就是 MySQL 里经常说到的 WAL(Write-Ahead Logging) 技术。

redo log 包括两部分：

• 一个是内存中的日志缓冲(redo log buffer)
• 另一个是磁盘上的日志文件(redo logfile)。

在计算机操作系统中，用户空间(user space)下的缓冲区数据一般情况下是无法直接写入磁盘的， 中间必须经过操作系统内核空间(kernel space)缓冲区(OS Buffer)。 因此，redo log buffer 写入 redo logfile 实际上是先写入 OS Buffer ， 然后再通过系统调用 fsync() 将其刷到 redo log file 中。 mysql 支持三种将 redo log buffer 写入 redo log file 的时机，可以通过 innodb_flush_log_at_trx_commit 参数配置。

• 值为 0 表示不刷入磁盘
• 值为 1 表示立即刷入磁盘(默认情况)
• 值为 2 表示先刷到 os cache
  

undo log

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

我们举个例子：假如更新ID=1记录的name字段，name原始数据为小富，现改name为程序员那点事。

事务执行update X set name = 程序员那点事 where id =1语句时，先会在undo log中记录一条相反逻辑的update X set name = 小富 where id =1记录， 这样当某些原因导致服务异常事务失败，就可以借助undo log将数据回滚到事务执行前的状态，保证事务的完整性。

那可能有人会问：同一个事务内的一条记录被多次修改，那是不是每次都要把数据修改前的状态都写入undo log呢？ 其实是不会的。

undo log只负责记录事务开始前要修改数据的原始版本，当我们再次对这行数据进行修改， 所产生的修改记录会写入到redo log，undo log负责完成后回滚(即用于本事务执行成功完成后回滚)，redo log负责完成前滚(即用于本事务执行失败时进行回滚)。

回滚

回滚即回到历史上最近的一个版本。

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

前滚

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

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

如果你仔细看过了下面的图就会发现，undo log、redo log、bin log三种日志都是在刷脏页之前就已经刷到磁盘了的，相互协作最大限度保证了用户提交的数据不丢失。

bin log(归档日志)

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

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

mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+

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

mysql> show binary logs;
+-----------------+-----------+
| Log_name        | File_size |
+-----------------+-----------+
| mysq-bin.000001 |      8687 |
| mysq-bin.000002 |      1445 |
| mysq-bin.000003 |      3966 |
| mysq-bin.000004 |       177 |
| mysq-bin.000005 |      6405 |
| mysq-bin.000006 |       177 |
| mysq-bin.000007 |       154 |
| mysq-bin.000008 |       154 |

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

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set

mysql> SET GLOBAL expire_logs_days=30;
Query OK, 0 rows affected

bin log主要应用于MySQL主从模式（master-slave）中，主从节点间的数据同步；以及基于时间点的数据还原。 当然，也可以通过使用 mysqlbinlog 工具来人工恢复数据。

主从同步

通过下图MySQL的主从复制过程，来了解下bin log在主从模式下的应用。

• 用户在主库master执行DDL和DML操作，修改记录顺序写入bin log;
• 从库slave的I/O线程连接上Master，并请求读取指定位置position的日志内容;
• Master收到从库slave请求后，将指定位置position之后的日志内容，和主库bin log文件的名称以及在日志中的位置推送给从库;
• slave的I/O线程接收到数据后，将接收到的日志内容依次写入到relay log文件最末端，并将读取到的主库bin log文件名和位置position记录到master-info文件中，以便在下一次读取用;
• slave的SQL线程检测到relay log中内容更新后，读取日志并解析成可执行的SQL语句，这样就实现了主从库的数据一致;

基于时间点还原

我们看到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 功能并不冲突而是起到相辅相成的作用，需要二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

binlog刷盘时机

对于 InnoDB 存储引擎而言，只有在事务提交时才会记录biglog ，此时记录还在内存中，那么 biglog是什么时候刷到磁盘中的呢？ mysql 通过 sync_binlog 参数控制 biglog 的刷盘时机，取值范围是 0-N：

• 0：不去强制要求，由系统自行判断何时写入磁盘；
• 1：每次 commit 的时候都要将 binlog 写入磁盘；
• N：每N个事务，才会将 binlog 写入磁盘。

从上面可以看出，sync_binlog 最安全的是设置是 1 ，这也是MySQL 5.7.7之后版本的默认值。 但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大， 牺牲一定的一致性来获取更好的性能。

binlog日志格式

binlog 日志有三种格式，分别为 STATMENT 、 ROW 和 MIXED。 在 MySQL 5.7.7 之前，默认的格式是 STATEMENT ， MySQL 5.7.7 之后，默认值是 ROW。 日志格式通过 binlog-format 指定。

• STATMENT：基于SQL 语句的复制( statement-based replication, SBR )，每一条会修改数据的sql语句会记录到binlog 中 。
  • 优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO , 从而提高了性能；
  • 缺点：在某些情况下会导致主从数据不一致，比如执行sysdate() 、 slepp() 等 。
• ROW：基于行的复制(row-based replication, RBR )，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了 。
  • 优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题 ；
  • 缺点：会产生大量的日志，尤其是alter table 的时候会让日志暴涨
• MIXED：基于STATMENT 和 ROW 两种模式的混合复制(mixed-based replication, MBR )，
  • 一般的复制使用STATEMENT 模式保存 binlog
  • 对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog

删除binlog

删除二进制日志有几种方法。不管哪种方法，都会将删除后的信息同步到二进制index文件中。

• reset master将会删除所有日志，并让日志文件重新从000001开始。

• PURGE { BINARY

  MASTER } LOGS { TO ‘log_name’

  BEFORE datetime_expr }

  • purge master logs to “binlog_name.00000X” 将会清空00000X之前的所有日志文件。例如删除000006之前的日志文件。
  • purge master logs before ‘yyyy-mm-dd hh:mi:ss’ 将会删除指定日期之前的所有日志。但是若指定的时间处在正在使用中的日志文件中，将无法进行purge。
• 使用–expire_logs_days=N选项指定过了多少天日志自动过期清空。

relay log(中继日志)

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

slow query log

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

对于在mysql命令行中执行的命令历史，可在~/.mysql_history中查到。

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

mysql> SHOW VARIABLES LIKE 'slow_query%';
+---------------------+--------------------------------------------------------+
| Variable_name       | Value                                                  |
+---------------------+--------------------------------------------------------+
| slow_query_log      | OFF                                                    |
| slow_query_log_file | /usr/local/mysql/data/iZ2zebfzaequ90bdlz820sZ-slow.log |
+---------------------+--------------------------------------------------------+

通过如下命令开启慢查询日志后，我发现 iZ2zebfzaequ90bdlz820sZ-slow.log 日志文件里并没有内容啊，可能因为我执行的 SQL 都比较简单没有超过指定时间。

mysql>  SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected

上面提到超过 指定时间 的查询语句才算是慢查询，那么这个时间阈值又是多少嘞？我们通过 long_query_time 参数来查看一下，发现默认是 10 秒。

mysql> SHOW VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

这里我们将 long_query_time 参数改小为 0.001秒再次执行查询SQL，看看慢查询日志里是否有变化。

mysql> SET GLOBAL long_query_time=0.001;
Query OK, 0 rows affected

果然再执行 SQL 的时，执行时间大于 0.001秒，发现慢查询日志开始记录了。

general query log

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

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

我们可以通过以下命令查看一般查询日志是否开启，命令如下：

mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+

下面开启一般查询日志并查看日志存放的位置。

mysql> SET GLOBAL general_log=on;
Query OK, 0 rows affected

mysql> show variables like 'general_log_file';
+------------------+---------------------------------------------------+
| Variable_name    | Value                                             |
+------------------+---------------------------------------------------+
| general_log_file | /usr/local/mysql/data/iZ2zebfzaequ90bdlz820sZ.log |
+------------------+---------------------------------------------------+

执行一条查询 SQL 看看日志内容的变化。

mysql> select * from t_config;
+---------------------+------------+---------------------+---------------------+
| id                  | remark     | create_time         | last_modify_time    |
+---------------------+------------+---------------------+---------------------+
| 1325741604307734530 | 我是广播表 | 2020-11-09 18:06:44 | 2020-11-09 18:06:44 |
+---------------------+------------+---------------------+---------------------+

我们看到日志内容详细的记录了所有执行的命令、SQL、SQL的解析过程、数据库设置等等。

error log

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

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

mysql> SHOW VARIABLES LIKE 'log_error';
+---------------+----------------------------------------------------------------+
| Variable_name | Value                                                          |
+---------------+----------------------------------------------------------------+
| log_error     | /usr/local/mysql/data/LAPTOP-UHQ6V8KP.err |
+---------------+----------------------------------------------------------------+

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

重要log的区别

不同的log用途不同，面向的场景不尽相同，甚至是使用方式和时机也不同。

• Buffer Pool 是 MySQL 的一个非常重要的组件，因为针对数据库的增删改操作都是在 Buffer Pool 中完成的
• Undo log 记录的是数据操作前的样子
• redo log 记录的是数据被操作后的样子（redo log 是 Innodb 存储引擎特有）
• bin log 记录的是整个操作记录（这个对于主从复制具有非常重要的意义）

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不会丢失数据的秘密，就藏在它的 7种日志里
• MySQL系列之十一 日志记录
• binlog 详解可参见 MySQL 数据库之Binlog日志使用总结
• 事务日志详解可参见 详细分析MySQL事务日志(redo log和undo log)
• 说说MySQL中的Redo log Undo log都在干啥
• Redo与Undo的理解
• Mysql 普通落盘策略可参见 MySQL InnoDB的磁盘文件及落盘机制
• mysql数据落盘详解_MySQL的Flush-List和脏页的落盘机制
• redo 日志刷盘策略可参见 redo日志的刷盘策略
• 可参见 MySQL数据库丢失数据场景分析、
• [MySQL] 如何做到不丢数据

主从同步

主从复制中分为”主服务器（master）”和”从服务器（slave）”。 这样读写分离的过程能够是整体的服务性能提高，即使写操作时间比较长，也不影响读操作的进行。

• 主服务器负责写，而从服务器负责读
• Mysql的主从复制的过程是一个异步的过程。

当master(主)库的数据发生变化的时候，变化会实时的同步到slave(从)库。 主从同步有什么好处：

• 水平扩展数据库的负载能力。
• 容错，高可用。Failover(失败切换)/High Availability
• 数据备份。

不管是delete、update、insert，还是创建函数、存储过程，所有的操作都在master上。 当master有操作的时候,slave会快速的接收到这些操作，从而做同步。 这个机制是怎么实现的呢？

• 在master机器上，主从同步事件会被写到特殊的log文件中(binary-log)。主从同步事件有3种形式：
  • statement：会将对数据库操作的sql语句写入到binlog中。
  • row：会将每一条数据的变化写入到binlog中。
  • mixed：statement与row的混合。Mysql决定什么时候写statement格式的，什么时候写row格式的binlog。
• 在slave机器上，slave读取主从同步事件，并根据读取的事件变化，在slave库上做相应的更改。

当binlog发生变化时：

• 在master机器上的操作：
  • 当master上的数据发生改变的时候，该事件(insert、update、delete)变化会按照顺序写入到binlog中。
  • 当slave连接到master的时候，master机器会为slave开启binlog dump线程。
  • 当master 的 binlog发生变化的时候，binlog dump线程会通知slave，并将相应的binlog内容发送给slave。
• 在slave机器上的操作：当主从同步开启的时候，slave上会创建2个线程。
  • I/O线程：该线程连接到master机器，master机器上的binlog dump线程会将binlog的内容发送给该I/O线程。该I/O线程接收到binlog内容后，再将内容写入到本地的relay log。
  • SQL线程：该线程读取I/O线程写入的relay log。并且根据relay log的内容对slave数据库做相应的操作。

在master、slave上可通过命令 SHOW PROCESSLIST 查看上述线程。

主从复制的过程有不同的策略方式进行数据的同步，主要包含以下几种：

• 同步策略：Master会等待所有的Slave都回应后才会提交，这个主从的同步的性能会严重的影响。
• 半同步策略：Master至少会等待一个Slave回应后提交。
• 异步策略：Master不用等待Slave回应就可以提交。
• 延迟策略：Slave要落后于Master指定的时间。

对于不同的业务需求，有不同的策略方案，但是一般都会采用最终一致性，不会要求强一致性，毕竟强一致性会严重影响性能。

主从模式对于写少读多的场景确实非常大的优势，但是总会写操作达到瓶颈的时候，导致性能提不上去。

• 这时候可以在设计上进行解决采用分库分表的形式，对于业务数据比较大的数据库可以采用分表，使得数据表的存储的数据量达到一个合理的状态。
• 也可以采用分库，按照业务进行划分，这样对于单点的写，就会分成多点的写，性能方面也就会大大提高。

建议把备库节点，设置成只读模式，有以下几个原因：

• 有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作
• 防止切换逻辑有bug
• 可以用readonly状态，来判断节点的角色
• readonly设置对超级权限用户是无效的，而用于同步更新的线程，就拥有超级权限。因此不影响与主库保持同步更新。

循环复制问题

假设节点A和B互为主备关系。在主备同步时，会引入循环复制问题。

双M结构有一个问题要解决，业务逻辑在节点A上更新了一条语句，然后再把生成的binlog发给节点B，节点B执行完这条更新语句后也会生成binlog。 那么，如果节点A同时是节点B的备库，相当于又把节点B新生成的binlog拿过来执行了一次，然后节点A和B间，会不断地循环执行这个更新语句，也就是循环复制

MySQL在binlog中记录了这个命令第一次执行时所在实例的server id。因此，可以用下面的逻辑，来解决两个节点间的循环复制问题：

• 规定两个库的server id必须不同，如果相同，则它们之间不能设定为主备关系
• 一个备库接到binlog并在重放的过程中，生成与原binlog的server id相同的新的binlog
• 每个库在收到从自己的主库发过来的日志后，先判断server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志

双M结构日志的执行流如下：

• 从节点A更新的事务，binlog里面记的都是A的server id
• 传到节点B执行一次以后，节点B生成的binlog的server id也是A的server id
• 再传回给节点A，A判断这个server id与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了

主备延迟

与数据同步有关的时间点主要包括以下三个：

• 主库A执行完成一个事务，写入binlog，这个时刻记为T1
• 之后传给备库B，备库B接收完这个binlog的时刻记为T2
• 备库B执行完这个事务，把这个时刻记为T3

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是T3-T1。 可以在备库上执行show slave status命令，它的返回结果里面会显示seconds_behind_master，用于表示当前备库延迟了多少秒。 seconds_behind_master的计算方法是这样的：

• 每个事务的binlog里面都有一个时间字段，用于记录主库上写入的时间
• 备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到seconds_behind_master

如果主备库机器的系统时间设置不一致，不会导致主备延迟的值不准。备库连接到主库的时候， 会通过SELECTUNIX_TIMESTAMP()函数来获得当前主库的系统时间。如果这时候发现主库的系统时间与自己不一致， 备库在执行seconds_behind_master计算的时候会自动扣掉这个差值

网络正常情况下，主备延迟的主要来源是备库接收完binlog和执行完这个事务之间的时间差。 主备延迟最直接的表现是：备库消费中转日志的速度，比主库生产binlog的速度要慢。

主备延迟主要来自以下几个方面：

• 有些部署条件下，备库所在机器的性能要比主库所在的机器性能差
• 备库的压力大。主库提供写能力，备库提供一些读能力。忽略了备库的压力控制，导致备库上的查询耗费了大量的CPU资源，影响了同步速度，造成主备延迟。对此可通过以下措施缓解：
  • 一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力
  • 通过binlog输出到外部系统，比如Hadoop这类系统，让外部系统提供统计类查询的能力
• 大事务。因为主库上必须等事务执行完才会写入binlog，再传给备库。所以，如果一个主库上的语句执行10分钟，那这个事务很可能会导致从库延迟10分钟。 典型的大事务场景：一次性地用delete语句删除太多数据和大表的DDL。

主备切换策略

主备切换策略有：

• 可靠性优先策略
• 可用性优先策略

可靠性优先策略

双M结构下，从状态1到状态2切换的详细过程如下：

• 判断备库B现在的seconds_behind_master，如果小于某个值继续下一步，否则持续重试这一步
• 把主库A改成只读状态，即把readonly设置为true
• 判断备库B的seconds_behind_master的值，直到这个值变成0为止
• 把备库B改成可读写状态，也就是把readonly设置为false
• 把业务请求切到备库B
  

这个切换流程中是有不可用的时间的。在步骤2之后，主库A和备库B都处于readonly状态，也就是说这时系统处于不可写状态，直到步骤5完成后才能恢复。 在这个不可用状态中，比较耗时的是步骤3，可能需要耗费好几秒的时间。也是为什么需要在步骤1先做判断，确保seconds_behind_master的值足够小。 系统的不可用时间是由这个数据可靠性优先的策略决定的。

可用性优先策略

可参考以下文档：

• MySQL主备、主从、读写分离详解
• Mysql主从同步的原理、
• 小白都能懂的Mysql主从复制原理（原理+实操）、

存储过程

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

-- 创建存储过程
CREATE PROCEDURE proname() 
BEGIN
select * from table;
END;

-- 执行存储过程
CALL proname();

-- 删除存储过程
DROP PROCEDURE proname;

优点

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

缺点

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

存储过程的具体细节可参见 mysql存储过程详细教程。 不同编程语言编写的应用程序中调用mysql存储过程大体是类似的。

视图

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

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

使用视图的原因可简单总结如下：

• 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
• 可读性高：MySQL视图可以提升SQL查询的可读性和简洁性。在业务逻辑复杂的情况下， 有时候我们需要多重嵌套SELECT语句才能得到所需的结果，这会导致查询语句变得非常冗长，难以理解和维护。 而使用视图可以将复杂的查询逻辑封装成一个独立的虚拟表格，使得查询语句更加清晰明了。
• 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

视图的缺点：

• 性能：从数据库视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的。
• 表依赖关系：将根据数据库的基础表创建一个视图。每当更改与其相关联的表的结构时，都必须更改视图。

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

索引

索引的优点小结：

• 索引大大减小了服务器需要扫描的数据量
• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机IO变成顺序IO
• 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组。 在MySQL5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但在早期的MySQL版本中，InnoDB直到事务提交时才会解锁。
  • 对不需要的元组的加锁，会增加锁的开销，降低并发性。
  • InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。 但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。 一旦索引不允许InnoDB那样做（即索引达不到过滤的目的），MySQL服务器只能对InnoDB返回的数据进行WHERE操作， 此时，已经无法避免对那些元组加锁了。
  • 如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。
  • 关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）

索引的缺点小结：

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
• 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
• 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
• 对于非常小的表，大部分情况下简单的全表扫描更高效；

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。 因此应该只为最经常查询和最经常排序的数据列建立索引。 MySQL里同一个数据表里的索引总数限制为16个。

索引概述

索引是什么？

索引是帮助MySQL高效获取数据的数据结构。

索引能干什么？

• 提高数据查询的效率。
• 索引：排好序的快速查找数据结构！索引会影响where后面的查找，和order by 后面的排序。

索引的分类

分类可以是多维度的：

• 从存储结构上来划分：描述的是索引存储时保存的形式
  • BTree索引（B-Tree或B+Tree索引）
  • Hash索引
  • full-index全文索引
  • R-Tree索引
• 从应用层次来分：
  • 普通索引
  • 唯一索引
  • 复合索引
• 根据数据中的物理顺序与键值的逻辑（索引）顺序关系：
  • 聚集索引
  • 非聚集索引

上述类别中涉及的相关概念简述如下：

• 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
• 唯一索引：索引列的值必须唯一，但允许有空值
• 复合索引：即一个索引包含多个列
• 聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。 具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。
• 非聚簇索引：不是聚簇索引，就是非聚簇索引。

各种索引的底层实现概述

mysql默认存储引擎innodb只显式支持B-Tree( 从技术上来说是B+Tree)索引，对于频繁访问的表， innodb会透明建立自适应hash索引，即在B树索引基础上建立hash索引，可以显著提高查找效率， 对于客户端是透明的，不可控制的，隐式的。

Hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据， 存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中， 同时在索引表中保存指向每个数据行的指针。

B-Tree索引（MySQL使用B+Tree）

B-Tree能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据， 数据分布在各个节点之中。

B+Tree索引

是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。 数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。 相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。 而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

结合存储引擎来讨论（一般默认使用B+Tree），来看一张学生表。

在MyISAM引擎中的实现（二级索引也是这样实现的）

在InnoDB中的实现

常见问题

问：为什么索引结构默认使用B-Tree，而不是hash，二叉树，红黑树？

• hash：虽然可以快速定位，但是没有顺序，IO复杂度高。
• 二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。
• 红黑树：树的高度随着数据量增加而增加，IO代价高。

问：为什么官方建议使用自增长主键作为索引。

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂， 也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。 总之就是减少分裂和移动的频率。

插入连续的数据：

插入非连续的数据:

索引存储类型

• 哈希索引：对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。
• B+树索引：
  • MyISAM： B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值， 然后以 data 域的值为地址读取相应的数据记录（回表查询）。这被称为“非聚簇索引”。
  • InnoDB：其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构， 树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。 这被称为“聚簇索引（或聚集索引）”，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址， 这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

更详细的描述可以参考以下文章：

• 索引总结
• 彻底搞懂MySQL的索引、
• MySQL索引到底怎么回事、

HASH 索引

哈希表也称为散列，是一种以键-值 (key-value) 存储数据的结构。输入查询的 key,就能找到对应的 value。 哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

当然会存在哈希冲突，对个 key 在经过哈希算法处理后可能出现在哈希表中的同一个槽位，当出现哈希冲突的时候，可以使用链表来解决。 这样发生冲突的数据都放入到链表中。在进行数据查询的时候，首先找到哈希表中的槽位，然后在链表中依次遍历找到对应的值。

哈希表的这种结构适合于等值查询的场景，在最优场景的下的时间复杂度能达到 O(1)。 哈希索引的缺点：

• 因为是哈希表存储的是 Hash 运算之后的 Hash值，所以它只能用于等值的查询，范围查询在哈希索引中不支持；
• 无法利用索引排序，索引中存储的只是 Hash 计算之后的 Hash 值，对于排序，索引本身无法支持；
• 组合索引不能利用部分索引，也就是不支持最左匹配原则，对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值， 而不是单独计算 Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用；
• 哈希索引需要进行回表查询，因为哈希索引存储的都是哈希值和行指针，所以不能避免使用索引来避免读取行。

InnoDB 中还有一个特殊的功能叫做”自适应哈希索引(adaptive hash index)”。 当 InnoDB 注意到某些索引值被使用的非常频繁时，它会在内存中基于 B+tree 索引之上在创建一个哈希索引，这样就能让 B+tree 索引也有哈希索引快速查找的优点了， 这是一个完全自动，内部的过程，用户无法控制或者配置，不过这个功能可以手动关闭。

InnoDB 中的自适应 Hash 相当于是“索引的索引”，采用 Hash 索引存储的是 B+ 树索引中的页面的地址。 这也就是为什么可以称自适应 Hash 为索引的索引。采用自适应 Hash 索引目的是可以根据 SQL 的查询条件加速定位到叶子节点， 特别是当 B+ 树比较深的时候，通过自适应 Hash 索引可以提高数据的检索效率。同时对于非聚簇索引中的查询，自适应哈希也能减少回表的次数。

全文索引

全文索引就是将存储于数据库中的整本书或整篇文章中的任意内容信息找出来的技术， 可以根据需求获取全文中的有关文章，节，段，句，词等信息，也能进行统计和分析。

InnoDB 最早是不支持存储全文索引的，想要使用全文索引就要选用 MySIAM 存储引擎,从版本 1.2.x 开始才增加了全文索引支持。 全文索引一般使用倒排索引(inverted index)实现，倒排索引和 B+ 树索引一样，也是一种索引结构。

倒排索引在辅助表 (auxiliary table) 中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。这样当全文索引，匹配到对应的单词就能知道对应的文档位置了。

倒排索引是区别于正排索引的概念：

• 正排索引：是以文档对象的唯一 ID 作为索引，以文档内容作为记录的结构。
• 倒排索引：Inverted index，指的是将文档内容中的单词作为索引，将包含该词的文档 ID 作为记录的结构。

倒排索引中文档和单词的关联关系，通常使用关联数据实现，主要有两种实现方式：

• inverted file index: 会记录单词和单词所在的文档 ID 之间的映射；
• full inverted index: 这种方式记录的更详细，除了会记录单词和单词所在的文档 ID 之间的映射，还会记录单词所在文档中的具体位置。

下面来举个栗子

DocumentId 表示全文索引中文件的 id, Text 表示存储的内容。这些存储的文档就是全文索引需要查询的对象。

inverted file index

可以看到关联中，记录了单词和 DocumentId 的映射，这样通过对应的单词就能找到，单词所在的文档，不用一个个遍历文档了。

full inverted index

这种方式和上面的 inverted file index 一样也会记录单词和文档的映射，只不过记录的更详细了，还记录了单词在文档中的具体位置。 相比于 inverted file index 优点就是定位更精确，缺点也是很明显，需要用更多的空间。

InnoDB 存储引擎支持全文索引采用 full inverted index 的方式，将(DocumentId，Position) 视为一个 ilist。

因此在全文检索的表中，一共有两列，一列是 word 字段，另一个是 ilist，并且在 word 字段上设有索引。 记录单词和 DocumentId 的映射关系的表称为 Auxiliary Table（辅助表）。

辅助表是存在与磁盘上的持久化的表，由于磁盘 I/O 比较慢，因此提供 FTS Index Cache（全文检索索引缓存）来提高性能。 FTS Index Cache 是一个红黑树结构，根据（word, list）排序，在有数据插入时，索引先更新到缓存中，而后 InnoDB 存储引擎会批量进行更新到辅助表中。

B树

一般来说，索引本身的数据量很大，全部放入到内存中是不太现实的，因此索引往往以索引文件的形式存储在磁盘中， 磁盘 I/O 的消耗相比于内存中的读取还是大了很多的，在机械硬盘时代，从磁盘随机读一个数据块需要10 ms左右的寻址时间。

为了让一个查询尽量少地读磁盘，就需要减少树的高度，就不能使用二叉树，而是使用 N 叉树了，这样就能在遍历较少节点的情况下也就是较少 I/O 查询的情况下找到目标值。

B树是一种多路搜索树。

• 定义任意非叶子结点最多只有M个儿子，且M>2。
• 根结点的儿子数为[2, M]。
• 除根结点以外的非叶子结点的儿子数为[M/2, M]。
• 每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）。
• 非叶子结点的关键字个数=指向儿子的指针个数-1。
• 非叶子结点的关键字：K[1], K[2], …, K[M-1]，且K[i] <= K[i+1]。
• 非叶子结点的指针：P[1], P[2], …,P[M]（其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树）。
• 所有叶子结点位于同一层。

下图是一个M=4阶的B树。

B树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的是叶子结点。 下面以查找文件29为例：

• 根据根结点指针找到文件目录的根磁盘块1，将其中的信息导入内存。（磁盘IO操作1次）
• 此时内存中有两个文件名17，35和三个存储其他磁盘页面地址的数据。根据算法我们发现17<29<35，因此我们找到指针p2。
• 根据p2指针，我们定位到磁盘块3，并将其中的信息导入内存。（磁盘IO操作2次）
• 此时内存中有两个文件名26，30和三个存储其他磁盘页面地址的数据。根据算法我们发现26<29<30，因此我们找到指针p2。
• 根据p2指针，我们定位到磁盘块8，并将其中的信息导入内存。（磁盘IO操作3次）
• 此时内存中有两个文件名28，29。根据算法我们查找到文件29，并定位了该文件内存的磁盘地址。

B树的特性小结如下：

• 关键字分布在整颗树的所有节点。
• 任何一个关键字出现且只出现在一个结点中。
• 搜索有可能在非叶子结点结束。
• 其搜索性能等价于在关键字全集内做一次二分查找。

B+树索引

MyISAM和InnoDB的索引均采用B+树数据结构。其特点如下：

• 每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。
• B+Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，很适合查找范围数据
• B-Tree可以对<，<=，=，>，>=，BETWEEN，IN，以及不以通配符开始的LIKE使用索引。

B+树是B树的一种变形树，总结起来，数据库索引的B+树与B树的差异在于：

• 非叶子结点的子树指针与关键字个数相同。
• 非叶子结点的子树指针P[i]，指向关键字值属于[K[i],K[i+1])的子树（注意，区间是前闭后开）。
• 为所有叶子结点增加一个链指针。
• 所有关键字都在叶子结点出现。

B+树的特性小结如下：

• 所有关键字都出现在叶子结点的链表中，且链表中的关键字是有序的。
• 搜索只在叶子结点命中。
• 非叶子结点相当于是叶子结点的索引，叶子结点是存储关键字数据的数据层。

使用B+树做索引的原因有：

• 局部性原理与磁盘预读
• 查找次数和访问磁盘IO次数少

局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分之一，因此为了提高效率， 要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始， 顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：

当一个数据被用到时，其附近的数据也通常会马上被使用——程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。 预读的长度一般为页的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块， 每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时， 会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

查找次数和访问磁盘IO次数少

一般来说，磁盘I/O次数可以用于评价索引结构的优劣。在B-Tree中查找，可知检索一次最多需要访问h个节点（上文举例查找文件29的过程）。 数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。 为了达到这个目的，在实际实现中，B树还使用如下技巧：

• 每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个节点只需一次I/O。
• B树中一次检索最多需要h-1次I/O（根节点常驻内存）。一般实际应用中，出度d（树的分叉数）是非常大的数字，通常超过100；h非常小，通常不超过3。

综上所述，用B树作为索引结构效率是非常高的。 与B树相比，红黑树或者平衡二叉树的其他树结构，对于磁盘存储而言，劣势如下：

• 深度h明显要深的多，执行效率低。
• 逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，
• 每个节点存储的数据量太小了，对磁盘空间造成浪费，带来频繁的IO操作。

相对B树，B+树做索引的优势：

• B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中， 那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
• B+树的查询效率更加稳定：由于所有数据都存于叶子节点。所有关键字查询的路径长度相同，每一个数据的查询效率相当。
• B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。 我们知道InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。 最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

索引查询

利用索引提高效率，需要理解索引的系统理论及实现细节。为此，有人总结了3原则：

• 单行访问是很慢的。特别是在机械硬盘存储中(SSD的随机I/O要快很多，不过这一点仍然成立）。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。 最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引，用以提升效率。
• 按顺序访问范围数据是很快的，这有两个原因。
  • 第一，顺序1/0不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对机械硬盘）。
  • 第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUPBY查询也无须再做排序和将行按组进行聚合计算了。
• 索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问，而上面的第1点已经写明单行访问是很慢的。

为什么索引能提高查询速度

先从 MySQL 的基本存储结构说起。 MySQL的基本存储结构是页(记录都存在页里边)：

其特征说明如下：

• 各个数据页可以组成一个双向链表
• 每个数据页中的记录又可以组成一个单向链表
  • 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录
  • 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以说，如果我们写select * from user where indexname = ‘xxx’这样没有进行任何优化的sql语句，默认会这样做：

• 定位到记录所在的页：需要遍历双向链表，找到所在的页
• 从所在的页内中查找相应的记录：由于不是根据主键查询，只能遍历所在页的单链表了

很明显，在数据量很大的情况下这样查找会很慢！这样的时间复杂度为O（n）。 你有可能说，mysql的有些版本已经对类似的语句进行了优化，但这对系统迁移而言，会埋下隐患（迁移前后的mysql版本可能不一样）。 因此，遵守最小基本的原则是有效和必要的。

使用索引后的查询基本流程

索引做了些什么可以让我们查询加快速度呢？其实就是将无序的数据变成有序(相对)：

要找到id为8的记录简要步骤：

很明显的是：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过 “目录” 就可以很快地定位到对应的页上了！（二分查找，时间复杂度近似为O(logn)）

可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询，但必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。

mysql索引是怎么实现的

对于MYSQL的INNODB存储引擎的索引，大家是不陌生的，都能想到是 B+树结构，可以加速SQL查询。 但对于B+树索引，它到底“长”得什么样子，它具体如何由一个个字节构成的，这些的基础知识鲜有人深究。 本篇文章从MYSQL行记录开始说起，层层递进，包括数据页，B+树聚簇索引，B+树二级索引，最后在文章末尾给出MYSQL索引的建议。 文章涉及较多基础知识，内容较为枯燥，因此采用较多的图片补充说明，希望能对读者有帮助。

行记录

mysql是关系型数据库，每一行记录都是表结构定义的关系的 显示表达。在脑中很直观地想到，记录存储时也可能按行存储。 的确，mysql是这么存储一条行记录的。但会添加一些额外信息，来补充行记录信息。

有一个概念可能大家不熟悉，是【变长字段】。mysql数据库类型中的 VARCHAR(M), VARBINARY(M), 各种TEXT，BLOB类型， 这些类型的数据长度是可变的，称 数据类型为可变长类型的列 为 变长字段。

另外，mysql会默认为行记录添加一些列（隐藏列）。上图补充这些隐藏列之后，完整行记录的结构如：

DB_ROW_ID: 唯一标识一条记录，在表中未设置主键 或 未有不允许为NULL的UNIQUE键时，则MYSQL新增该隐藏列作为主键。 DB_TRX_ID: 事务ID。 DB_ROLL_PTR: 回滚指针。 下面再详细的铺开 ，关于记录的额外信息 的具体内容。

通过真实的数据库表的行数据，来强化下上面的概念。 首先新增一个表，并在表中insert两条记录。

create table record_format_demo(
c1 varchar(10),
c2 varchar(10) not null,
c3 char(10),
c4 varchar(10)
) charset=ascii row_format=compact

insert into record_format_demo(c1, c2, c3, c4) values
("aaaa", "bbb", "cc", "d"),
("eeee", "fff", NULL, NULL);

做一个简单的查询，验证数据正常写入。

分析这两行数据的存储记录。

• 第一行记录：
  
• 第二行记录：
  

应该会注意到，变长字段长度列表 与 NULL值列表都是逆序存放的。 原因是：这样可以使得记录中位置靠前的字段 和 它们对应的字段长度信息在内存中的距离更近，可能会提高 高速缓存的命中率。

数据页

数据页可以形象地比作是装行记录的盒子。 为了更清楚的理解 数据页结构 以及 下文中的索引，更换一个带主键的表。

CREATE TABLE page_demo(
c1 INT,
c2 INT,
c3 VARCHAR(10000),
PRIMARY KEY (c1)
) CHARSET=ascii ROW_FORMAT=Compact;

INSERT INTO page_demo VALUES
(1, 100, 'aaaa'),
(2, 200, 'bbbb'),
(3, 300, 'cccc'),
(4, 400, 'dddd');

做一个简单的查询，验证数据正常写入。

根据行记录结构中的next_recrod属性的含义，多条行记录是以单向链表的形式存储。 mysql为了后续更好的查询，单向链表上的记录是按照主键顺序排列的。 上述这四条记录，可以显示的画成：

假如删除其中c1=2这条数据，则单向链表变更如下。 其中变化点为 c1=2 这条数据中，deleted_flag变更成0， next_record=0， 但并没有从磁盘上直接清除掉，head_no也未清除。第一条记录的next_record 指向了第三条记录。

当我们查询数据时，如果数据以行记录的形式一条一条从磁盘加载到内存中，那么因为IO过多的原因，整体性能肯定较为低效。 因此mysql规定，磁盘与内存交换数据的基本单位是一个页大小。这个页大小 默认是16K。 根据页中存储的数据类型不同，页也分成许多类型。对于存储行记录的页，称为索引页（Index Page），也称为数据页。

那么接下来我们看看，数据页的结构如何，一条条行记录如何存放在数据页中。先上个图。

从上图中，我们可以猜到，数据页总共分成7个模块，目前咱们只关心 User Records 部分，它存储的就是用户真实的行记录。 但是一个数据页的空间总共是16K，不会自动增大空间，随着User Records 模块中的行记录越来越多，那么肯定有其他模块的空间就越来越小。 这个模块是 Free Space，是页中尚未使用的空间。更新一下上面这个图，补充 Free Space的内容。 随着User Records中行记录的增加，Free Space空间则越来越小。

在一个数据页中，除了真实的行记录之外，还有两条固定的伪记录。 其中一条记录称为 infimum 【[ɪnˈfaɪməm] ，下确界】行记录， 规定是数据页中记录的最小值。 infimum记录特别简单，仅包含了 记录头信息(5字节) + 真实记录数据(8字节)， 其中【69 6E 66 69 6D 75 6D 00】16进制转换成对应的单词，就是【infimum】。

另外一条记录称为 supremum【[sə’priməm]，上确界】行记录，规定是数据页中记录的最大值。 supremum记录同样特别简单，仅包含了 记录头信息(5字节) + 真实记录数据(8字节)， 其中【73 75 70 72 65 6D 75 6D】16进制转换成对应的单词，就是【supremum】。

再更新一版数据库页结构， 补充上infimum 与 supremum。

既然规定了页面中的最小记录 与 最大记录，理所应当，上文中串联各个行记录的单向链表，则应该有一个固定的头部 与 固定的尾部。 更新一下单链表的图。注意下infimum 与 supremum中的几个关键值。

• infimum: n_owned=1，表示是某一个分组的最后一条记录，当前分组共1条记录；record_type=2； next_record=第一条真实行记录的真实值的相对位置。
• supremum: n_owned=5，表示是某个分组的最后一条记录，当前分组共5条记录；record_type=3； next_record=0，表示记录是本数据页中最后一条记录。
  

OK，到现在数据页中完整的单链表已经形成了。 思考一个问题，如何根据主键值，在数据页中的单链表如何查找到相应的数据。 最直接的做法是：从 infimum记录开始，沿着单链表的顺序不断的向后查找，直到supremum结束。在这期间，找到满足条件的记录就返回，找不到则不返回。

一个数据页默认大小是16K，用于存储真实行记录的空间超过 98%以上。也就是说，一个数据页中在行记录填充满的情况下，行记录的数量是较多的（当然与行记录大小有关）。 如果每次查询都从单链表的infimum记录 一直到 supremum记录，肯定是无法接受的，很有必要对此现状进行优化。 由此，引出了数据页中的另一个模块，Page Directory，页目录。

首先返回看下上面完整单链表中，infimum记录 与 supremum记录中的两个值，n_owned。这个值分别为 n_owned=1 与 n_owned=5。

参考下n_owned的定义，它是：【页面分组之后，每个组最后一条行记录中该值等于本组内的记录数量。本组内其他记录该值都等于0。】 对于上面的单链表， 其它行记录的owned值 都为零。也就是说，infimum单条记录作为一个组，剩余的四条行记录+supremum记录作为一个组。 mysql规定：

• 对于infimum记录所在的分组只能有1条记录，也就是它本身。
• 对于supremum记录所在的分组的记录数在1~8条之间。
• 其它的分组记录的条数范围，在4~8条之间。

将每个组中最后一条记录在页面中的地址偏移量（该记录的真实数据与数据页中第0个字节之间的距离）单独提取出来，以倒序存储到数据页中的固定模块中。 这个模块，就称为：Page Directory。Page Directory中存储的地址偏移量，也称为Slot 【[slɒt]， 槽】，每个Slot两字节。【可以想想为啥是两字节？】 再次更新下数据页结构图。

目前的只有四条记录，两个分组，数量太少了。我们继续往表中继续增加一些记录。

INSERT INTO page_demo VALUES
(5, 500, 'eeee'),
(6, 600, 'ffff'),
(7, 700, 'gggg'),
(8, 800, 'hhhh'),
(9, 900, 'iiii'),
(10, 1000, 'jjjj'),
(11, 1100, 'kkkk'),
(12, 1200, 'llll'),
(13, 1300, 'mmmm'),
(14, 1400, 'nnnn'),
(15, 1500, 'oooo'),
(16, 1600, 'pppp');
INSERT INTO page_demo VALUES
(5, 500, 'eeee'),
(6, 600, 'ffff'),
(7, 700, 'gggg'),
(8, 800, 'hhhh'),
(9, 900, 'iiii'),
(10, 1000, 'jjjj'),
(11, 1100, 'kkkk'),
(12, 1200, 'llll'),
(13, 1300, 'mmmm'),
(14, 1400, 'nnnn'),
(15, 1500, 'oooo'),
(16, 1600, 'pppp');

在不断的插入新行记录时，因此不同类型分组数量的约束，所以分组也会增加。这个过程如下：

• 在初始情况下，一个数据页中只会有 infimum 与 supremum 这两条记录，它们分别属于两个组。此时Page Directory 也只会有两个slot，分别代表infimum的地址偏移量 与 supremum的地址偏移量。
• 之后每新增一条行记录，都会从Page Directory中找到对应的记录的主键值 比待新增记录的主键值大 并且差值最小的slot【slot是一个组内最大的那条记录在页面中的地址偏移量， 通过slot可以快速找到对应记录的主键值】， 然后把该slot对应的记录的 n_owned值加1，表示本组内新增了一条记录，直到该组中的记录数等于8个。
• 当一个组中的记录数等于8后，再插入一条记录，会将组中的记录拆分成两个组，其中一个组中是4条记录，另外一个组中是5条记录。拆分过程中，会新增一个slot，记录这个新增分组中最大的那条记录的地址偏移量。
• 现在来看看下，目前该数据页中的行记录的分组情况。
  

来演绎一个根据主键查询行记录的例子。假如想查询主键值 ，也就是C1=6的行记录。通过二分法查找，过程如下：

• 设置low=0，high=4。计算中间slot的位置，(0 + 4) / 2 = 2, 通过slot 2 查询到对应的主键值等于8。因为8 > 6， 所以设置high = 2， low = 0不变。
• 重新计算中间slot的位置，（0 + 2）/ 2 = 1, 查看 slot 1对应记录的主键值为4。又因为 4 < 6, 所以设置low = 1，high 不变。
• 此时low = 1， high = 2， 所以确定主键值为6的行记录在 slot2 对应的组中。此时找到slot 2的最小一条记录【通过slot 1 的next_record找到slot 2的最小记录】，遍历slot 2中的所有记录即可。

截止目前，数据页模块中，还要三个模块是未知的。回想一下，对于一条行记录，它有 记录头信息 来描述这条行记录的相关信息，那么对于一个数据页，它有对应的头部来记录数据页的相关信息吗？ 有的，自然是有的，而且还不少。这个模块就称为 Page Header。

Page Header的结构如下：主要作用是标识 数据页中记录了多少条记录，Free Space在页面中的地址偏移量，页目录中包含多少slot等等。 额外说下page_direction 与 page_n_direction的含义。

• page_direction: 假如新插入的一条记录的主键值比上一条记录的主键值比上一条记录大，我们说这条记录的插入方向是右边，反之则是左边。 用来表示最后一条记录插入方向的状态就是page_direction。
• page_n_direction: 假设连续几次插入新记录的方向都是一致的，InnoDB会把沿着同一个方向插入记录的条数记下来，这个条数就用PAGE_N_DIRECTION这个状态表示。 当然，如果最后一条记录的插入方向改变了的话，这个状态的值会被清零重新统计。
  

到此为止，仅剩下两个模块了，加油啊。 上文中的Page Header 是专门针对数据页记录的各种状态信息。但数据页 仅仅是多种页类型中的一种，其它的还有例如undo日志页，溢出页， 存储段信息页，存储区信息页等。 因此mysql 使用了File Header 来描述各种页的通用信息。

从fil_page_prev 与 fil_page_next 两个属性可以联想到，不同页之间是有关联的，而且是以双向链表的形式。

最后一个模块，File Trailer 【 [ˈtreɪlə(r)]，挂车】。 InnoDB存储引擎会把数据存储到磁盘上，但是磁盘速度太慢，需要以页为单位把数据加载到内存中处理，如果该页中的数据在内存中被修改了，那么在修改后的某个时间需要把数据同步到磁盘中。 但是在同步了一半的时候中断电了怎么处理呢？此时就需要靠 File Trailer 模块中数据起作用。

展示下完整的数据页结构。

盗用一下网上的一个很好的数据页图。

快速查询的秘籍：B+树索引

上文在介绍File Header时，我们特别说明了里面的两个数据：FIL_PAGE_PREV，指向前一个页号。FIL_PAGE_NEXT, 指向后一个页号。由此可以得出，多个数据页之间的数据结构是双链表。

上文使用的数据共有16条，为了演示这个双链表的效果，现在假设【仅仅是假设】每个页中存放不超过4条行记录。 则上文的16条记录，形成的数据页双链表结构如下图【此处省略了许多非必要展示的字段】。

从上面这个链表，可以得到以下结论：

• 双向链表的页号并不保证是连续的。
• 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。【在依次写入主键数据不连续的行记录时，会发生页中数据的迁移。】

从目前的双向链表结构中，想要根据主键值查找记录，也只能是从第一页开始，一页一页的依次查找。虽然在一个数据页中，可以根据 Page Directory进行快速的二分查找，但总体效率肯定不尽人意。得优化。

我们注意到，【下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值】。 因此，首先进行第一步改进。 维护一个目录项，目录项中记录某个页中主键的最小值以及页号，各个目录项再以单向链表的形式链接起来。 这样就可以根据主键查询目录项，得到满足的条件页，再进入相应的页中查询行记录即可。

到现在看看，目录项是不是也很像行记录，只是它的列值是主键与页号。那把这些目录项维护成在一个页中看看效果。毫无违和感，浑然天成。现在看到的，就是主键索引的雏形了。

目前数据有些少了，继续补充一些数据，再画个图看看。

INSERT INTO page_demo VALUES
(17, 1700, 'qqqq'),
(18, 1800, 'rrrr'),
(19, 1900, 'sss'),
(20, 2000, 'tttt');

现在看到的，就是一个典型的INNODB的主键索引了。它包含以下特点：

• 整个主键索引的数据结构是一棵树，具体是以B+树实现。
• 叶子节点与非叶子节点中的行记录按照主键大小顺序排成一个单向链表，页内的记录被划分成若干组。可以利用Page Directory进行二分法快速查找到行记录。
• 存放用户记录的数据页，根据页中用户记录的主键大小顺序排成一个双向链表。所有用户记录都存放在叶子节点上。
• 存放目录项记录的数据页都是非叶子节点，分层不同的层级。同一层级中的页也是根据页中目录项记录的主键大小顺序，排成一个双向链表。

通常也把INNODB的B+树索引称为 聚簇索引（clustered/ˈklʌstəd / index），所有的真实数据都存储在聚簇索引中。【索引即数据，数据即索引】。 通常除了主键索引之外，肯定还会建一些普通索引，称为二级索引，或者辅助索引。上面的数据，我们以上文中的数据 C2列建立一个二级索引看看。

现在来看看下，INNODB中 二级索引的特点。

• 二级索引的数据结构 与 聚簇索引一样，是B+树结构。
• 二级索引的所有目录项页存储行记录的真实数据是 索引列+页号。
• 二级索引的非叶子节点存储行记录的真实数据是 索引列+主键值。

因为在二级索引中查询到的是主键值，如果想要得到完整的行记录，则需要拿着主键值再到聚簇索引中再进行一次查询。 这个过程称为【回表】。 【回表的过程，特别要强调下每次对于二级索引来说，每次查询到一条满足二级索引字段条件的记录时， 都进行一次 回表 判断是否满足其他条件，然后将满足所有条件的一条查询结果返回给客户端。】

再讲讲联合索引。现在以C2 与 C3两列作为联合索引，为了更好的展示联合索引的效果，先修改几条行记录。

update page_demo set c3 = 'dddd' where c2 = 100;
update page_demo set c3 = 'cccc' where c2 = 200;
update page_demo set c3 = 'bbbb' where c2 = 300;
update page_demo set c3 = 'aaaa' where c2 = 400;
update page_demo set c2 = 300 where c1 = 4;

给联合索引画个图。

总结下联合索引的特点：

• 联合索引的数据页中记录的排序，默认是按照定义联合索引的第一列排序的，在第一列值相等的情况下，再按照第二列排序。其它的方面均与单列的二级索引无差别。
• 联合索引还有一个比较特殊的使用场景：最左前缀匹配。 例如有联合索引的列包含：C1，C2，C3 三列，而且顺序也是 C1，C2，C3。 则查询语句：
  • select * from page_demo where c1 = x and c2 = y and c3= z, 使用到了C1，C2，C3 列排序的结果。
  • select * from page_demo where c1 = x and c2 = y, 使用到了C1，C2 列排序的结果。
  • select * from page_demo where c1 = x and c3 = z, 仅使用到了C1 列排序的结果。

索引的优缺点及建议

• 优点：
  • 对于等值查询，可快速定位到对于的行记录。
  • 对于范围查询，可辅助缩小扫描区间。
  • 当ORDER BY的列名 与 索引的列名完全一致时，可加快排序的顺序。
  • 当GROUP BY的列名 与 索引的列名完全一致时，可加快分组。
  • 当二级索引列中 包含了 SELECT 关键字后面写明的所有列，则在查询完成二级索引之后无需进行回表操作，直接返回即可。这种情况，称为【覆盖索引】。
• 缺点：
  • 建立索引占用磁盘空间。
  • 对表中的数据进行 增加，删除，修改 操作时，都需要修改各个索引树，特别是如果新增的行记录的主键顺序不是递增的，就会产生页分裂，页回收等操作，有较大的时间成本。
  • 当二级索引列的值 的 不重复值的个数较少时，通过二级索引查询找到的数据量就会比较多，相应的就会产生过多的回表操作。
  • 在执行查询语句的时候，首先要生成一个执行计划。通常情况下，一个SQL在执行过程中最多使用一个二级索引，在生成执行计划时需要计算使用不同索引执行查询时所需的成本， 最后选择成本最低的那个索引执行查询。 因此，如果建立太多的索引，就会导致成本分析过程耗时太多，从而影响查询语句的性能。
• 建议：
  • 只为用于搜索，排序，分组的列创建索引。
  • 索引的列需要有辨识性，尽可能地区分出不同的记录。
  • 索引列的类型尽量小。因为数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以存放更多的记录，磁盘I/O带来的性能损耗也就越小。
  • 如果需要对很长的字段进行快速查询，可考虑为列前缀建立索引。【alter table table_M add index idx_key1(column_n(10)) –> 将table_M表的 idx_key1列的前10个字符创建索引】
  • 覆盖索引，当二级索引列中 包含了 SELECT 关键字后面写明的所有列，则在查询完成二级索引之后无需进行回表操作，直接返回即可。因此，编写【select *】的时候，要想想是否必要。
  • 在查询语句中，索引列不要参与 条件值计算，也是把条件值计算完成之后，再和索引列对比。【否则MYSQL会认为 搜索条件不能形成 合适的扫描区间来减少扫描的记录数量】
• mysql 索引是怎么实现的？、
• MySQL索引完全解读
• 彻底理解 MySQL 的索引机制，终于不再因为 MySQL 优化而被面试官鄙视了

不走索引的情况

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

存储引擎

系统表和用户数据表等是如何存储的？

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

如果所有的存储引擎都不能满足你的需求，并且技术能力足够， 可以根据官网内部手册用C语言开发一个存储引擎。 即自定义存储引擎，以扩展存储引擎来实现某种特殊要求的场景。 自定义实现存储引擎可参考：

• 改写示例
• MYSQL自定义存储引擎api分析
• 16. 编写自定义存储引擎
• 编写自定义存储引擎6

我要评论