前言

MySQL 是一款开源软件，凭借其出色的性能，目前已经成为绝大多数互联网公司的首选关系型数据库。因此程序员不能只懂数据库的增删改查和一些简单的使用技巧，更需要熟练掌握 MySQL 的一些原理知识，从而更好地应对实际工作中遇到的问题。

本文为大家介绍 MySQL 的知识点包括：

1. 了解 MySQL 官网以及 MySQL 下载，MySQL 文档
2. 了解 MySQL 的架构
3. 了解 MySQL 的存储引擎
4. 掌握 MyISAM 与 InnoDB 引擎的区别以及数据文件存放路径
5. 了解 InnoDB 的逻辑存储结构
6. 掌握 MySQL 中 SQL 语句的执行过程
7. 了解 MySQL 的缓存机制
8. 认识数据库索引的本质
9. 认识四种树的数据结构（二叉查找树、平衡二叉树、B-Tree、B+Tree）
10. 掌握 MySQL 中 B+Tree 索引的实现方式
11. 深入理解数据库索引的几个概念
12. 掌握索引的创建原则以及注意事项
13. 深入理解数据库的事务
14. 掌握事务的 ACID 特性与事务的隔离级别
15. 了解并发给数据库带来的问题
16. 了解 InnoDB 存储引擎支持的隔离级别
17. 掌握 MySQL 中锁的机制
18. 了解 MySQL 中四个事务隔离级别的实现
19. 掌握 MVCC 的实现原理
20. 理解 Undo Log、Redo Log 的作用
21. 掌握 SQL 语句调优中 explain 的用法
22. 掌握 MySQL 常用的调优策略

适合人群： 有 MySQL 使用经验，想要深入了解 MySQL 的架构、索引、事务、性能优化的程序员。

本文以理论介绍为主，大约 1.8 万字，建议大家分章节阅读。

一、MySQL 官网介绍，了解 MySQL 的整体架构

1、MySQL 官网介绍

学习了解一个技术最好的方式是通过官网去了解，官网上有详细的文档资料可供学习。很对人不习惯从官网上查找资料，甚至不清楚如何在官网上下载程序安装包，如何找到官方的学习文档，下面给大家做个简单的介绍。面对复杂的英文文档，大家不必发憷，看的多了就习惯了，实在看不了，不是还有谷歌翻译吗。

1.1、MySQL 下载

MySQL 官网地址：https://dev.mysql.com/

在这里插入图片描述

打开 DOWNLOADS 页面，找到最下方的 MySQL Community (GPL) Downloads ，下载社区版即可，免费使用。

MySQL 下载地址：https://dev.mysql.com/downloads/mysql/ 打开后点击：MySQL Community Server ，点击 Looking for previous GA versions? 选择 MySQL 的历史通用版本。

在这里插入图片描述

在这里插入图片描述

下载时需要注册 Oracle 的账户。

1.2、MySQL 文档

在这里插入图片描述

在这里插入图片描述

2、了解 MySQL 的架构

在 MySQL5.1 的中文文档资料里，有如下的一张 MySQL 插件式存储引擎的体系结构图，通过这张图（https://www.mysqlzh.com/doc/134.html）， 我们能很好的了解 MySQL 的整体架构。

2.1 MySQL 架构图

在这里插入图片描述

从以上的架构图我们可以看出，最上面的 Connectors 表示客户端，客户端就是一些具体的链接协议；下面的 MySQL Server 表示服务端，其中包括 Connection Pool 、SQL Interface、Parser、Optimizer、Storage Engines、文件系统（Files、Logs）以及管理服务。

2.2、MySQL 架构介绍

从上面的分析可以看出，MySQL 服务器主要包括 sql 层和存储引擎层，主要包括以下 6 个部分：

1、客户端的链接支持的协议很多，比如我们在 Java 开发中经常用到的 JDBC，客户端请求过来首先会由 Connection Pool 进行管理，包括权限认证、链接管理，缓存管理等，Connection Pool 最主要的功能就是接收客户端的请求。

2、客户端一个 sql 语句请求过来之后， SQL Interface 会统一接收用户的 sql 命令，接下来会交给 Parser 来进行解析。

3、Parser 解析器的目的是把 sql 的文本解析成 selectlex 对象，随后把 selectlex 对象提交给优化器 Optimizer。

4、优化器 Optimizer 会进行 sql 执行的成本计算，找到最优的一个执行计划，然后会调用下方存储引擎 Storage Engines 的 API 进行数据的读写。

5、存储引擎 Storage Engines 最终操作的是最下方的文件系统，包括 Files 和 Logs。

6、图中左上角的工具模块包括备份、主从复制等功能。

二、了解 MySQL 的存储引擎

存储引擎的主要工作就是与文件系统进行数据交互，比如我们常用的 InnoDB 引擎。

MySQL 的存储引擎是插件式的，应用程序无需针对不同的存储引擎进行对应的编码操作，MySQL 提供了一套标准的 API 标准，MySQL 服务会自动处理不同的存储引擎与文件系统的读写，用户层面没有感知。

存储引擎是指定在表上面的，每个表都可以指定其自己的引擎；不管使用什么存储引擎，都会产生一个后缀为 frm 的文件，改用来定义表结构。

1、MySQL 的存储引擎介绍

2、掌握 MyISAM 与 InnoDB 引擎的区别

Mysql5.5 版本之前默认的存储引擎就是 MyISAM 存储引擎，MySQL 中比较多的系统表使用 MyISAM 存储引擎，系统临时表也会用到 MyISAM 存储引擎，但是在 Mysql5.5 之后默认的存储引擎就是 InnoDB 存储引擎了。原因主要是 MyISAM 是表级锁定，限制了数据库读/写的性能；另外一个原因 MyISAM 不支持事务，基于以上两点，InnoDB 引擎使用的非常广泛。

3、MyISAM 与 InnoDB 数据文件存放路径

-- 查看数据存储的文件路径
show variables like 'datadir';

4、InnoDB 逻辑存储结构

MySQL 的存储结构分为 5 级：表空间、段、簇、页、行。

表空间（TableSpace）： 表空间可以看做是 InnoDB 存储引擎逻辑结构的存储结构的大容器，所有的数据都存放在表空间中，分为：系统表空间、独占表空间、通用表空间、临时表空间、Undo 表空间。

段（Segment）： 表空间是由各个段组成的，分为：数据段、索引段、回滚段等，段是一个逻辑的概念，一个 .ibd 文件（独立表空间文件）里面会由很多个段组成。创建一个索引会创建两个段，一个是索引段（leaf node segment），索引段管理非叶子节点的关键字和引用数据；一个是数据段：（non-leaf node segment），数据段管理叶子节点的行记录数据。

区/簇（extents）： 一个段由很多的簇（也可以叫区）组成，每个区的大小是 1MB（64 个连续的页），每一个段至少会有一个簇，一个段所管理的空间大小是无限的，可以一直扩展下去，但是扩展的最小单位就是簇。

页/块（page）： 为了高效管理物理空间，簇是由连续的页组成的空间，一个簇中有 64 个连续的页（1MB／16 KB = 64），这些页面在物理上和逻辑上都是连续的。页是 InnoDB 存储引擎磁盘管理的最小单位，每个页默认 16KB，通过 innodbpagesize 设置。

行（Row）： MySQL5.7 以后默认的行格式（Rowformat）为：DYNAMIC，Rowformat 可在创建数据表时指定，大家了解有这么一回事就可以了。

文件系统中，操作系统和内存进行文件交换，最小的单位也是页，不过文件系统的页默认是 4KB，MySQL 数据库的页默认是 16KB，目的是为了一次 IO 操作可以获取更多的数据。

三、了解 SQL 语句在 MySQL 中的执行过程

作为开发人员都非常清楚，当我们写好一个 sql 语句之后，连接到数据库点击执行，数据库就会返回我们要查询或者更新的结果。但是，数据库服务器在接收到一个 sql 请求后内部是如何处理的，可能就不太清楚了。这一节主要为大家讲解从客户端发起一个 sql 语句的查询，数据库服务器内部的一个处理流程。

1、 MySQL 中 SQL 语句的执行过程

在这里插入图片描述

从上图可以看出，MySQL 的处理流程主要分为 4 个步骤：客户端与服务端通信、查询优化处理过程、查询执行引擎、返回结果给客户端。

1.1、客户端与服务端通信

一般通信方式有 3 种：单工，半双工，全双工。单工就是只能单向传输，要么 A 端给 B 端传输，要么 B 端给 A 端传输；半双工是可以双向传输的，但是同一时间只能是一个方向传输，也就是说 A 端给 B 端传输的时候，B 端只能等待，反过来也一样，B 端给 A 端传输的时候，A 端也只能等待；全双工是双向随便传输。

MySQL 客户端与服务器的通信方式是半双工的，也就是说，我们的一个数据库连接在向数据库发送数据的时候，此时这个数据库连接是不能给客户端返回数据的，一定是数据返回完毕以后，客户端才能再次发起查询操作。这也就是我们在做数据查询的时候用 where 条件 和 limit 限制数据结果行数的原因，否则客户端连接需要等到数据库把所有的查询结果返回之后，才能进行下一个操作。

从上面的分析可以看出，MySQL 数据库半双工通信模式的一个重要特点是：客户端一旦开始发送指令，服务端需要接收完毕才能响应，客户端只有在完全接收到服务端响应的数据后，才能再次发送指令。有点像对讲机，这就是为什么电视里看到两个人对讲时，最后要说一句 over 的原因，当听到 over 的时候，另一端的人就可以按对讲键进行说话了。我们在程序开发中，一般会用多个连接进行数据交互，通过数据库连接池来进行管理，因此对这块体会可能不够深刻。

其实 MySQL 的每一个连接都有其对应的状态来标识它目前所处的阶段，和线程类似，我们可以通过下面的命令查看数据库连接的状态：

SHOW [FULL] PROCESSLIST

常用的几个状态描述：

当发现数据库连接长时间占用的时候，可以用 kill 命令杀死线程：

kill processlist_id

1.2、查询优化处理过程

解析器解析 sql 语句：通过 lex 词法分析器（就是把一个完整的 SQL 语句分析成独立的单词 ）、yacc 语法分析器（就是分析是否符合语法规则，比如单引号是否闭合等）进行分析，将 sql 语句按 sql 标准解析成 解析树（select_lex）对象，主要功能是把一个 sql 语句的字符串解析成数据库服务器可以处理的解析树对象，便于后续进行预处理和生成执行计划。

预处理：预处理会根据 mysql 的语法规则对解析树对象进行合法性检查，比如检查表名列名是否存在、检查名字和别名，保证没有歧义，预处理之后得到一个新的解析树。

优化器生成执行计划：优化器的主要作用就是把这个 sql 语句找到最优的执行计划，MySQL 的查询优化器和 Oracle 类似，都是基于成本的计算，优化器会尝试使用不同的执行计划，以便于找到一个最优的执行计划（一般随机读取 4K 的数据库进行分析）。

可以使用以下的命令查看查询的成本：

show status like 'Last_query_cost';

优化器最终会把解析树变成一个查询执行计划。MySQL 提供了一个执行计划的工具，我们在 SQL 语句前面加上 EXPLAIN，就可以看到执行计划的信息。

我们在做 sql 调优的时候主要也就是对这部分进行处理，在《掌握 SQL 语句调优中 explain 的用法》中会详细介绍。

1.3、查询执行引擎

查询执行模块，也就是查询执行引擎，根据优化器生成的最优执行计划调用对应存储引擎的 API 的进行执行计划的执行，并获取查询应该返回的结果集。

1.4、返回结果给客户端

如果没有开启缓存，把查询到的结果集返回到客户端；如果开启了缓存，执行缓存操作，把结果集存入缓存，然后把结果返回给客户端，即使结果集是空的，也要返回。

2、MySQL 的缓存介绍

一般情况下，我们不会用到数据库自带的缓存，所以 MySQL 默认是不开启缓存的。只有以读为主的业务，数据不变化的情况下，可以开启数据库的缓存。

查看缓存是否开启：

show variables like 'query_cache%';

queyrcachetype:on，表示缓存开启，默认是关闭的，可以通过修改 MySQL 配置文件 my.cnf 进行调整，重启服务后生效。

querycachelimit:1048576，表示单词查询缓存的结果集大小 1M，超过 1M 则不会缓存。

querycachesize，表示缓存开辟的空间大小

查看缓存操作情况：

show status like 'Qcache%';

Qcache_hits:表示缓存命中次数

Qcache_inserts:表示缓存写入次数

缓存生效的条件是在缓存开启的情况下，执行的 sql 语句字符串一模一样的时候，可以从缓存直接读取数据，但是当缓存数据相关的表存在数据变化的时候，原有的缓存就会失效，需要重新写人缓存。

MySQL 的缓存开启后，当 sql 查询语句带有 sqlnocache 关键字或者带有函数操作或者单次查询结果集超过 querycachelimit 的设置的值或者查询系统表时，不会用到缓存。

我们在开发中，最好不要开启缓存，将 querycachetype 设置为 off，querycachesize 设置为 0；缓存一般会用 Redis 方案来替代。

四、索引的本质与数据结构

正确合理的创建索引是提升数据库查询性能的基础，因此针对数据库来说，索引是必须要掌握的。

1、数据库索引的本质

我们一说到索引，如果大家想到是一个类似于字典的目录，可以提高数据库查询的性能，那么这个理解还是太表面了，也就是说对索引的作用有了解，对它的本质还是不够了解。

数据库索引的本质是一种数据结构，是数据库管理系统（DBMS）中一个排序的数据结构，目的是为了提升对数据库表中的记录行的检索速度，以协助快速查询、更新数据库表中数据，而创建的一种数据结构。

实际上，建立索引会占用磁盘空间的索引文件，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录地址。

使用索引提升查询效率的 3 个特点：

1、索引能极大的减少存储引擎需要扫描的数据量，从而提升查询效率；

2、索引可以把随机 IO 变成顺序 IO，从而提升查询效率；

3、索引在进行分组、排序操作时避免使用临时表，从而提升查询效率；

应该选择一种什么样的数据结构，才能实现通过索引来实现数据的高效检索呢？MySQL 数据库选择的是 B+ Tree 作为索引的数据结构，下面我们来看看为什么选择 B+Tree。

2、四种数据结构介绍

2.1、二叉查找树（Binary Search Tree）

在这里插入图片描述

在这里插入图片描述

从上面的 2 个图来看，同样是 30 个数字，插入的数据顺序不一样，二叉树的结构完全不一样，图 1 里查找 30 需要 2 次检索的 IO 操作，但是在图 2 里查找 30 需要 7 次检索的 IO 操作，在图 2 里查找 30 类似于全表扫描。

此处为什么说是一次检索就有一次 IO 呢，因为要查找的数据在文件里，需要从文件里读取出来加载到内存里进行数据比较，如果相等就返回，如果不相等，假设比要查找的数字大，需要接着往左边找；假设比要查找的数字小，需要接着往右边找。

以上可以看出，二叉树的数据检索取决于数据的分布情况，不适合用作索引的数据结构，因为二叉树的深度太深的话，IO 操作会特变多，查询效率就会很低，因此若想二叉树的查询效率尽可能高，需要这棵二叉树是平衡的，从而引出新的数据结构，平衡二叉树（Balanced Binary Search Tree）或者完全平衡二叉树（又叫 AVL 树）。

2.2、平衡二叉树

在这里插入图片描述

树节点的数据存储结构示意图：

在这里插入图片描述

每个节点其实就是一个磁盘块，有 3 部分数据区，一个是关键字：用于存放主键或者其他索引的值，一个是数据磁盘块的地址，一个是子节点的引用，分别指向父节点的左子节点的引用和右子节点的引用。

以上面的平衡二叉树的图片为例，通过查找关键字为 10 的数据记录，来说明节点的查找过程说明：

1、先找到节点 15，把节点 15 加载到内存后，与 10 比较，发现比 10 大，那么需要找到 节点 15 的左子节点，也就是节点 8；

2、通过节点 15 的左子节点的引用，找到节点 8，把节点 8 加载到内存后，与 10 比较，发现比 10 小，那么需要找到节点 8 的右子节点，也就是节点 10；

3、通过节点 8 的右子节点的引用，找到节点 10，把节点 10 加载到内存后，与 10 比较，发现正好匹配，那么说明要查询的数据就是节点 10；

4、通过节点 10 的数据磁盘地址，找到数据区，进行数据的获取，返回到客户端即可。

二叉树存在数据分布不均匀，出现查询深度太深，平衡二叉树解决了数据分布相对平衡的事情，但是用平衡二叉树作为索引的话还是有问题，原因主要有两个：

一个就是数据节点的深度决定了磁盘的 IO 操作次数，IO 操作非常消耗时间，从上图可以看出 9 条记录，如果查找 30 就需要 4 次 IO 操作，如果记录成千上万，这种 IO 操作是巨大的灾难。

另外一个就是平衡二叉树的节点磁盘块保存的数据量太少，它没有很好的利用操作系统读取磁盘 IO 的数据交互特性（操作系统去磁盘上读取的数据是 4KB，交互的单位是以页为单位的），以上的二叉树数据结构一个节点不可能用满 4KB 的空间；也没有利用好磁盘 IO 的预读能力（即空间局部性原理）（磁盘预读能力就是当你一次 IO 读取到数据的页后，会认为你马上要读取相邻的数据页，所以磁盘会多返回几个页的数据到内存），从而带来频繁的 IO 操作。

2.3、多路平衡查找树（B-Tree）

多路平衡是指每个节点可以有多个分支，也就是可以有多个子节点，二叉树就是 2 个子节点，有几个子节点就是几路。

多路平衡与二叉平衡相比，可以大大减少树的深度，从而减少 IO 的操作次数。

多路平衡查找树的节点里的关键字最多有（路数-1）个，如果用 N 来表示路数，那么关键字的个数最多就是 N-1 个。

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

在这里插入图片描述

B-Tree 是一个绝对平衡的查找树，也就是所有的子节点都在一个高度上，为了维持树的绝对平衡，在数据插入更新操作时，会通过一些分裂组合的操作来维持，这块也是比较消耗性能的，这也就是人们常说的索引建立够用就行，建立索引不易太多的主要原因。因为索引建多了，当数据插入或者更新的时候，维护索引的成本也是很大的。

B-Tree 是为磁盘等外存储设备设计的一种平衡查找树，B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。关系型数据库一般都会用 B-Tree 的改进版作为索引的数据结构。

2.4、加强版多路平衡查找树（B+Tree）

在这里插入图片描述

在 B+Tree 中，所做的优化是：所有数据记录节点都是按照键值大小顺序存放在叶子节点上，而非叶子节点上只存储关键字和子节点的引用，非叶子节点不存数据块的磁盘地址；这样可以大大加大每个节点存储的 key 值数量，降低 B+Tree 的高深度。

同时最底层的叶子节点之间都有一个指针，把上一个节点的数据块里的最后一个地址指向到下一个节点的数据块里的第一个地址，这样就可以使数据库形成一个有序链表，因此可以做到顺序 IO 操作。

由于以上的特点，B+Tree 的全表扫描的能力更强，因为数据是放到叶子节点上的，只需要读取叶子节点就可以了；同时 B+Tree 的排序能力更强，B+Tree 的查询能力更稳定，每次都需要检索到叶子节点才能拿到数据。

3、MySQL 中 B+Tree 索引的实现方式

3.1、Myisam 存储引擎中的实现

.frm 表定义文件：每一个存储引擎都有这个文件

.myd 数据文件：d 代表 data，数据库表中的数据保存在这个文件

.myi 索引文件：i 代表 Index，索引保存在这个文件

Myisam 引擎的数据文件与索引文件是分开的，在.myi 索引文件中无论是主键索引还是辅助索引，其叶子节点保存的是.myd 数据文件中的数据行的地址指针，因此 Myisam 引擎获取数据记录的过程是先从索引文件中拿到数据区的地址，然后再去数据文件中获取对应地址的数据返回给客户端。

3.2、Innodb 存储引擎中的实现

.frm 表定义文件：每一个存储引擎都有这个文件

.ibd 数据文件：数据库表中的数据保存在这个文件

在 InnoDB 存储引擎里，它是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文件，都在 .ibd 文件里面。

Innodb 以主键为索引来组织数据的存储，如果没有指定主键，那么 InnoDB 会默认增加主键索引，同时，叶子节点存放的是整张表的行记录数据，因为主键对应的行所有的字段都在叶子节点上存在，而且索引键值的逻辑顺序跟数据库表中的数据行内容存储顺序是一致，所以这种主键索引也叫聚集索引。

Innodb 的辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键聚集索引键（即主键的值）,当通过辅助索引来查询数据时，InnoDB 存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

InnoDB 会默认增加主键索引的规则： 1、如果定义了主键（PRIMARY KEY），那么 InnoDB 会选择主键作为聚集索引； 2、如果没有定义主键，那么 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引； 3、如果唯一索引也没有定义，那么 InnoDB 会选择内置 6 个字节的 ROWID 作为默认的聚集索引，采用主键递增的策略生成。

Innodb 引擎的聚集索引把行记录数据与主键全部存起来的原因是考虑到用主键做查询的情况比较多，所以把所有的行记录聚集起来放到主键的叶子节点上。

Innodb 引擎的辅助索引用主键值而不用地址表示，主要原因是当数据变化的时候，如果用的是主键，不是地址的话，不用再维护辅助索引了。

五、深入了解数据库的索引

1、索引的几个概念

单列索引： 索引节点中的关键字是一个列的内容，也叫普通索引，比如：用身份证号做索引，用手机号做索引。

联合索引： 索引节点中的关键字是多个列的内容，经常在需要用多个字段查询的时候用到。比如：用手机号+时间做索引，或者手机号+时间+状态做索引；也就是说单列索引是特殊的联合索引。

唯一索引： 索引列的值必须唯一，但允许有空值；如果是联合索引，则列值的组合必须唯一。

主键索引： 当给这一张表指定主键以后，那么这个主键就会变成一个主键索引，主键索引是唯一性索引，唯一性索引并不一定就是主键。

聚集索引（聚簇索引）： 聚集索引的 B+Tree 中的叶子节点存放的是整张表的行记录数据，而且索引键值的逻辑顺序跟数据库表中的数据行内容存储顺序是一致，辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键的值。

辅助索引： 辅助索引也叫非聚集索引，也就是非主键索引。

回表扫描： 在 InnoDB 中辅助索引里存放的是主键值，假设查询条件不是主键而是辅助索引，那么首先需要从辅助索引里找到对应的主键值，然后从主键索引里找到该主键值对应的数据记录行，这个过程就叫做回表扫描，因为这个过程比基于主键索引的查询多扫描了一棵辅助索引树，这个过程就叫回表扫描，意思就是从辅助索引树里找一圈，然后再回到主键索引里找一圈。

覆盖索引： 在辅助索引里面，不管是单列索引还是联合索引，如果查询的数据列只用从索引节点中的关键字就能够取得，不必从数据区中读取主键值，再回到主键索引查询记录行，这时候使用的索引就叫做覆盖索引，这样就避免了回表扫描，所以说，用到覆盖索引的查询效率会高一些，原因就是不用进行回表扫描操作，IO 操作次数减少了。同时覆盖索引也可以减少数据库 IO，将随机 IO 变为顺序 IO，因此提高数据的查询效率。

了解了覆盖索引的概念，我们就能知道为什么数据库开发一般要求不要用 select * 查询，除了 select * 查询需要返回的字段值多，占用空间以外，更重要的原因就是，如果业务需求只需要查询索引列的时候，直接查索引字段会直接从索引文件返回内容，而不需要回表扫描，这样就大大提升了查询的性能。

2、索引的创建原则

在实际开发过程中，一般人们存在一个误区，就是查询效率低，我们创建索引解决就行了，也有人把“在经常使用的查询条件上都建立索引“作为解决查询缓慢的法则，其实通过索引数据结构的学习，大家应该能有体会，索引不是越多越好，太多的索引反而会影响数据库的性能。

下面介绍索引创建的 3 个原则：

2.1、最少空间原则

最少空间指的是列的长度小，长度越小的列，B+Tree 的路数就会越多，IO 检索的次数就会越少，查询的效率就会越高，也就是说索引的效果越好。如果不理解，请再看一下索引的数据结构部分的内容。

2.2、高离散度列原则

数据表中列的离散度公式：count(distinct(列名称)) / count(*)，也就是列的所有不相同的值和所有数据记录总行数的比，如果列的所有不相同的值和数据记录总行数约接近，那么列的离散度越好，简单来说，如果列的重复值越多，离散度越低，重复值越少，离散度越就越高。

这也就是大家都了解的不要再性别字段上建立索引的主要原因，因为性别字段列的值除了男就是女，列的散型太差，建立索引起不到效果，查询优化器选择执行计划可能觉得走这样的索引还不如走全表扫描。

因此我们在创建索引的时候尽量选择离散度高的列，也就是选择列的值几乎没有重复的列做索引效果最好。

2.3、最左匹配原则

最左匹配原则一般用于联合索引中，联合索引在 B+Tree 中是复合的数据结构，最左匹配原则就是联合索引的检索比对是从左往右匹配的，它是按照从左到右的顺序来建立搜索树的。

假如一张表用手机号和时间联合索引【phone，time】，没有建立 time 的单列索引。

那么联合索引【phone，time】（phone 在左边，time 在右边），就表示：phone 是有序的，time 是无序的；当 phone 相等的时候，time 才是有序的。

这个时候我们使用 phone 和 time 作为查询条件的时候，B+Tree 会优先比较 phone 来确定下一步应该搜索的方向，往左还是往右；如果 phone 相同的时候再比较 time ；但是如果查询条件只有 time，那么就不知道第一步应该查哪个节点，因为建立搜索树的时候 phone 是在左边，所以用 time 查询就不会用到索引。

如果用 phone 作为查询条件，是否可以用到索引呢，答案是肯定的，因为 phone 在联合索引的左边，所以用 phone 独立查询，按照最左匹配原则是可以用到索引的。

从最左匹配原则我们就能发现，如果创建了联合索引【phone，time】，那么就没有必要再去创建单列索引【phone】了，但是如果有用 time 作为查询条件的情况，就需要创建 time 列的索引【time】。

3、索引使用的几个注意事项

• 在 where 判断 order 排序和 join（on）的字段上创建索引

• 索引字段最好不要为 null 值

• 索引列的长度能少则少

• 索引不是查询字段都要建立索引，也不是越多越好

• 注意联合索引的使用最左匹配原则

• 选择索引要选离散度高的列

• 联合索引里最左的列，就不要创建单列索引了，浪费空间不说，还增加了更新操作的索引维护成本

• 频繁更新的值，不要作为主键或者索引

• 索引列上避免使用函数（replace、substr、concat、sum）和运算

• 字符串查询一定要加引号

• like 条件中前面带 %，同时要保证 % 前面的内容离散度要高

• 避免使用 NOT IN 、<>、NOT LIKE 等负向判断

• 可以使用 limit 关键字进行查询记录限制

六、深入了解数据库的事务

1、什么是事务

事务是数据库管理系统（DBMS）执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成，事务是不可以再分的最小的数据库工作单元，它包含了一个或者多个 DML 语句，比如 insert、update、delete。

InnoDB 存储引擎是支持事务的，这也是它成为默认的存储引擎的一个重要原因。

最常见的事务操作就是金融系统的银行转账场景，简单的说 A 账户转出多少金额给 B 账户，那么 B 账户必须完成对应金额的增加，这个转账业务才算成功，否则就出大问题了。

再比如电商系统的下订单场景，订单表和库存表一定是要在一个事务里完成的。

事务里的操作，要么同时成功，要么同时失败，这样才能保证事务操作的一致性。

2、事务的四大特性

事务的四大特性：ACID。

2.1、原子性（Atomicity）

原子性，又叫不可分割性，它是指一个事务（transaction）中的所有操作，要么全部成功，要么全部失败。如果事务在执行过程中发生错误，就会被回滚（rollback）到事务执行之前的状态，也就是说要返回原样，就像这个事务从来没有执行过一样。 原子性的回滚操作在 InnoDB 里面是通过 undolog 来实现的，它记录了数据修改之前的值（逻辑日志），一旦发生异常，就可以用 undo log 来实现回滚操作。

2.2、一致性（Consistent）

一致性，指的是在事务执行前后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。比如主键必须是唯一的，字段长度符合要求等，也不会因为系统出现异常等原因导致数据前后的状态不一致。

除了数据库自身的完整性约束，我们在写代码的时候，还要满足用户自定义的完整性。比如：A 账户要给 B 账户转账 10000 元，如果 A 账户余额减少 10000 元，B 账户余额只增加了 1000 元，这个时候两个 sql 操作都可以成功，因为它是满足原子性的，但是明显存在业务问题；假设 A 账户里没有 10000 元，那么也是不能够执行转账操作的，一般用户自定义的完整性需要在代码中做好判断和控制。

2.3、隔离性（Isolation）

隔离性可以防止多个事务并发的读写操作时，由于交叉执行而导致数据的不一致。因为数据库允许多个并发事务同时去读写或者修改数据库的同一张表或者同一行数据，如果不加控制，数据一定会不一致。隔离性就是指多个并发事务对同一张表或者同一行数据的读写操作，应该是互相不干扰的。主要是指一个事务要操作的数据在提交之前，对其他事务应该是不可见的，从而可以保证业务数据的一致性。

数据库的隔离性是通过事务的隔离级别来实现的，这块在后面介绍。

2.4、持久性（Durable）

持久性是指事务处理（对数据库的任意的增删改操作）结束后，对数据的修改就是永久的，即便数据库宕机也不会丢失（数据库的磁盘文件损坏除外）。

数据库宕机后的恢复是通过 redolog 来实现的数据持久性恢复，数据库在操作数据的时候，会先写到内存的 buffer pool 里面，同时记录 redo log。如果在写到磁盘之前出现异常，那么重启后就可以读取 redo log 的内容，然后再写入到磁盘，从而保证数据的持久性。

其实事务的原子性、隔离性、持久性，最后都是为了实现事务的最终一致性。

3、MySQL 如何开启事务

1、自动开启事务，自动提交或者回滚事务

InnoDB 有一个 autocommit 的参数（分成两个级别， session 级别和 global 级别）。

show variables like 'autocommit';

它的默认值是 ON，如果 autocommit 值是 true/on 的话，我们在操作数据的时候，会自动开启一个事务，和自动提交事务；如果把 autocommit 设置成 false/off，那么数据库的事务就需要我们手动地去开启和手动地去结束。

2、手动开启事务，手动提交或者回滚

手动开启事务可以用 begin，也可以用 start transaction；结束一个事务也有两种方式，可以用 commit 提交一个事务，也可以用 rollback 回滚事务。

4、了解并发给数据库带来的问题

下面我们来分析一下，假设没有事务的隔离性，当两个事务并发地去操作数据库的表或者行的时候，那么会产生什么问题？

• 读脏数据： A 事务修改了一个数据（假设 A 从 1 改为 2），B 事务读取 A 事务修改后的数据（B 读取到了 2），但由于某种原因 A 事务撤销了事务（这个数据回滚到 1），此时导致 B 事务读取到的数据不正确（B 认为数据是 2），这就表示 B 事务读取到了脏数据，一般称为脏读。

• 不可重复读 A 事务读取数据后（假设 A 读到了 1），B 事务执行更新操作（B 把 1 改成了 2），然后 B 事务提交（此时数据的值为 2）；然后 A 事务再次读取数据（A 读到了 2），也就是说 A 事务两次读取操作得到了 2 个结果（第一次是 1，第二次是 2）。说明在一个事务里对同一个数据读取，得到的值不一致，后面的读操作获得了其他它事务提交的结果，这就表示 A 事务不可重复度。

• 幻读 A 事务读取价格小于 10 的产品数据（假设 A 读到了 100 条数据），B 事务往产品表里插了一条价格小于 10 的产品，然后 B 事务提交（此时价格小于 10 的产品有 101 条）；然后 A 事务再次读取价格小于 10 的产品数据（A 读到了 101 条数据），也就是说 A 事务读取到了 B 事务新增的记录（第一次是 100 条，第二次是 101 条）。这就表示 A 事务发生了幻读。

  幻读与不可重复读的区别： 不可重复读是由其他事务的修改操作引发的读不一致，幻读是由于其他事务新增或者删除记录引发的读不一致。从结果上看，不可重复读和幻读差不多。但从控制的角度来看, 两者的区别就比较大。 对于前者, 只需要锁住满足条件的记录。 对于后者, 要锁住满足条件及其相近的记录

以上事务并发带来的三大问题，无论是脏读，还是不可重复读，或者是幻读，它们都是数据库的读一致性的问题，都是在一个事务里面前后两次读取出现了不一致的情况。

5、MySQL 事务的隔离级别

针对并发给数据库带来的三个问题读一致性的问题，SQL92 标准为各个数据库厂商提供一定的事务隔离级别，来解决事务并发的问题。