同事删库跑路 莫名查询慢 表结构不知道怎样设计 执行计划看不懂 咋办？ 爆肝七七四十九天总结 ! Mysql 性能优化 就决定用你了 ！

一、MySQL架构分析与sql执行流程

1.1 发展历史

1.2 流行分支/存储引擎

上面看到2000年开源，那么自然就有了许多分支产品

1.3 一条查询语句是如何执行的

比如你去餐厅点菜，你只跟服务员说了一道菜名，不久后菜就端到你桌上了

从你点击查询按钮，到返回数据 后面到底做了些啥，有哪些角色 下面来看一看？

1.3.1 客户端 连接服务端

首先第一步当然是建立连接，

通信类型通常用同步，异步的话编程会比较复杂，而且可能产生数据问题，这个由你客户端代码决定

连接方式通常用长连接,长连接好处就是这个客户端用完了，别的客户端还可以继续用，短连接就是用完就关了，后面要用还得重开

协议一般使用TCPIP协议，在linux环境上，本机客户端连接本机服务端 不指定host时这时候使用一个Unix Socket的文件来进行连接

服务端的线程与客户端的连接有什么关系，客户端没创建一个连接，服务端需要一个线程处理，

那么长时间不活跃的连接，是需要被回收的，毕竟连接数有限，如下为超时时间，如果需要尽快回收连接，那么是可以将他们调小的

那么服务端允许的最大连接数是多少呢？

从mysql官网中可以看到，默认是151，最大10万，然后他的作用域是Global，全局的，

说到作用域，还有个级别是会话(session)，比如下面这个窗口，你想要在当前会话(session) 也就是当前窗口修改某些配置时，是不需要在 set 后加 global的

当你想要修改全局变量时，这个时候需要加上global，修改后所有客户端都会生效，生产环境此操作需谨慎

1.3.2 mysql自带的缓存

500万条数据

第一次查询，用时4秒左右

第二次查询 时间与第一次相差无几 那么，缓存呢？

可以看到mysql默认的缓存是关闭的，并且mysql也不推荐大家用，当一张表一条数据发生改变，这张表的数据相关的所有缓存都会被清空，另外 sql语句中大小写 或者是空格的添加也会导致无法命中缓存，所以 不建议用，且因此功能极度鸡肋，8.0版本被移除

1.3.3 解析器 与 预处理器

当我随便输入点啥然后执行的时候，如下，mysql怎样判断我是否输入正确的呢

解析器会进行词法分析与语法分析

然后 预处理器 会做预处理

比如你查询一张不存在的表，比如你查询的属性名写错了

进行上面的分析与预处理后会生成下面这样一个解析树

1.3.4 优化器

查询条件中的某字段有多个索引时，使用哪个，比如你查询中有多张表关联，先查询哪个？

这就是我们说的一条sql语句是多执行路径的，这个时候优化器就派上用场了

主要就如下这些优化

在mysql中它的这些优化叫做基于cost(成本)的优化器

在mysql中，生成执行的路径后，会计算每条路径成本的数量，来比较选择成本最小的路径也可以

说执行计划，来执行它，但是这并不代表你sql可以随便写，一切交给有优化器，这也是我们后面要学习优化的主要原因

怎样看执行计划呢，只需在sql后面加上explain关键字即可模拟一个执行的路径,路径信息

大概包含查询的类型，表名，type(这个很关键)怎样访问表的，可能用到的索引，索引，索引的长度，预估扫描的行数 ，预估过滤的百分比等等，这里只大概介绍下，后面会讲

如果想要看json版本的，如下即可

如何开启optimizer_trace

为什么要开启它，如下图，可以知道它优化过程中有哪些行为

用完了记得关，毕竟还是会耗点性能

1.3.5 存储引擎

数据在数据库存哪，表？ 好，下面对它动手了

存储引擎？可能这玩意很多人都听过，但是说不出个所以然，不急，往下看

首先，我这里创建了三张表，表结构一样，但请看下面三张图黄色部分，他们的表类型不同，分别

是InnoDB，MEMORY,MyISAM 三种

那么这三种表类型（存储引擎）怎么来的？有什么区别？最常用的是哪一个？我什么场景选择哪一个呢？表类型能修改吗

输入如下语句查看表结构存放的位置

可以看到不同表类型(存储引擎)的表的文件数量是不同的分别是2，1，3个文件

默认的存储引擎自5.5版本后就是InnoDB了，包含字符编码也是有默认值

关键问题来了，为什么要有这些个存储引擎，一个不行吗，这还是根据业务场景来的

比如我有如下三种业务场景

1.想要快速访问数据，不考虑持久化

2.存历史数据，比如银行的账户的交易，不做物理删除，只做逻辑删除，绝对不可能修改，甚至查询都很少，不需要索引支持，数据量太大所以需要支持压缩的特性

3.常规的业务，有读写并发 保证数据的一致性

现在大致了解了来源么，我们在不同的业务场景，对数据的访问的要求，存储的要求，管理的要求，都可能不同，所以才会有这么多存储引擎

下面来看看官网上对这些存储引擎的介绍

我简要概括下

InnoDB: 事务安全ACID，提供提交，回滚，崩溃恢复等能力来保证数据安全。支持行级锁。

一致性非锁定读MVCC。聚集索引，大大降低使用主键的查询的IO。支持外键

MyISAM: 只支持表级锁，全部为读，或者大部分场景为读时候可以用它

MEMORY:数据放在内存中存储，所以读写快，但是数据崩溃无法恢复，所以一般可以用于一些临时数据的存储

CSV：纯文本或者用逗号换行的数据

后面的就不多说了

存储引擎介绍链接 不同的存储引擎支持的索引啊，锁啊也基本不同

如果以上都不满足你的业务需求，你也可以用C语言自己写一个，具体步骤可看mysql官网...

为什么不同的存储引擎可以切换：这是由于存储引擎的开发都有一套规范，跟插件样，

记住，存储引擎只负责管理数据，你一坨数据在那，放哪，以怎样的形式放这些东西是存储引擎的活

1.3.6 执行引擎

故名思意，它就是执行sql的东东，它会调用执行引擎的接口得到数据，再根据执行计划进行一顿操作，最后返回结果

好，到这里应有个小结，关于一条sql语句从输入到返回数据，这中间到底经历了啥，看到这你应该多少能说点啥了，

总结： 先是客户端选择连接方式（见前面三个参数）建立连接，然后解析器对你输入的slelect语句进行词法分析与语法分析通过了的话再做预处理 预处理也通过就生成了解析树，然后是优化器对解析树进行优化，优化后这个时候就生成执行计划，然后给执行器，执行器从存储引擎中拿到数据，一般是从磁盘中一次性获取到内存，然后通过执行计划进行处理，处理完后就返回我们想要的数据了，当然如果你开了mysql自带的缓存，那么有可能在解析器阶段就判断命中缓存然后直接给你返回缓存数据，当然这个缓存一般不开启

下图与本文无关，可以看到mysql跟innoDB都被Oracle收购了，而上面说的mysql分支marialDB的推出也是mysql作者防止mysql被收购后不再开源而采取的措施

1.4 一条更新语句是如何执行的

这里为什么我只说更新，因为更新就包含了删除与新增，了解过mybatis源码（只有doUpdate）的人或许能比较理解，不理解也没关系，继续看下去就懂了

这样一条sql语句，大致的流程与上面查询都是一致的，不同的只有执行引擎处理的那小部分

1.4.1 预读取

关于预读取的概念，这里有必要提一提，如下图 使用innodb管理的数据放在磁盘，在磁盘中处理数据是很慢的，所以要把它加载到内存来处理，从磁盘获取数据到内存也是很慢的，所以一般一次性获取固定量 那么一次性获取多少数据呢，比如我们需要10kb的数据，那么一次性只会加载10kb么，不是，当我读取到磁盘上的一块数据时，我认为下一块相邻的数据也会马上被读取到，所以一起给你读取到内存来，这就是预读取（思想：局部性原理）

那么不管你要读取多少，每次操作系统会给你加载一定的数量，就比如说，你要一瓶啤酒，但是这店只论箱卖

那么这个量是多少呢，这个时候又牵扯到 一个数据页的定义，默认16KB大部分情况都够用

InnoDB分页大小介绍

这玩意是可以修改的（表格后面五个数为其他有效值），但是修改比较麻烦，需要数据清空然后初始化mysql服务

1.4.2 内存缓冲区

放在内存中的一个缓存，已经从磁盘中获取过某数据页的数据，会缓存到缓冲区中，下次再次获取时看看缓冲区中有么有，有的话从缓冲区中拿，写入的时候也一样，放在缓冲区中的数据可能因为断电等原因而丢失，所以缓冲区中的数据又称为脏页，而同步到磁盘后的才称为干净的页，这个动作（刷脏）是由一个后台线程 默默的处理的，如果刷脏完成之前由于重启宕机等原因数据丢失，那么咋办呢 看下一个概念

InnoDB_buffer_pool的内存越大，对我们系统读写性能提升也就越大，所以生产环境一般都要尽可能调大

那InnoDB_buffer_pool 内存这么重要，那它满了咋办，没办法还是要回收，

这里使用LRU算法（redis也是用的这），我简单介绍下 传统的LRU包含一个双向链表与一个哈希表，哈希表放双向列表的下标，然后在双向链表中最新访问或者新增的value都会放到双向链表的head上，然后这链表长度又是设计的有限的 对此算法感兴趣的话可以看看别的资料

然后innodb的bufferpool采用的是优化后的LRU算法，它不想来的一大块数据一下就把尾部的大块数据给挤掉，优化细节：

如下图，看起来跟jvm那张图是不是有点像，哈哈 简要来说就是，数据分为了俩部分 热数据区，与冷数据区 ，热数据区是在前面，新来的数据是放在冷数据区的head中，当访问后才会被移入热数据区的头中，分区比例大小默认为5:3

1.4.3 redo log

重启服务的时候看看redo log中 有哪些数据没有被刷脏，然后刷下，那这样的设计有啥毛病么，想着是不是有点麻烦，为啥我不直接写到磁盘呢，而要又写内存缓冲区，又写磁盘的redo log

这个时候又要说到一个概念了，顺序IO与随机IO，我们写数据或者加载数据都是磁壁转到某个扇区后获取（寻址）的，如果操作的数据存在随机的不连续的扇区那么寻址花费的时间会比较长，而将数据先写到缓冲区这样提升了操作的效率，而再写到redo log 又保证了持久性，不会丢失，同时因为顺序的写入redo log 又保证了顺序IO 这样看来这种设计反而很巧妙

可以看到redo log文件大小默认是 48兆的不会变 如果满了的话就不能提供其功能而必须先刷盘了，所以有时候可以适当调大点

redo log的作用概括如下，记住，他是InnoDB独有

1.4.4 undo log

通俗来说，如果一个操作内包含多个sql语句，执行到一般发生了异常，那前面的那些怎么办呢，就必须要还原成原来的状态，这个时候undo log就派上用场了

这个玩意不像redo log 有个独立的文件，是存在系统表空间内的，磁盘内的结构后面再说一下

redo log 与 undo log都是与事务密切相关的，所以又称他们为事务日志

更新流程梳理

inno DB 架构图 稍微看看吧 继续看下去后面会对这张图越来越清晰

表空间 下面不多说，有undo 表空间 保证原子性的 还有私有的，通用的临时的表空间 感兴趣的可以去查资料了解，下面我只讲讲重要部分

1.4.5 Change Buffer

这玩意是包含在前面说的buffer pool中的，默认占比 25% 如果你的系统的业务场景是写多读少

并且大部分索引都是非唯一性索引时可以调大这个值，一般都不需要

1.4.6 log buffer 及 刷盘时机

前面说到，数据一般先在内存中(buffer pool)处理,不会立即同步到磁盘 因为效率太差了 这时候他是保存在一个缓冲区也就是log buffer中，等待合适的时机由一个默默无闻的线程刷盘 到 磁盘中

刷盘时机，一秒钟刷盘一次的话，那么系统宕机或者重启最多丢失一秒钟的数据

刷盘频率越高的话 性能降低，安全性提升 刷盘频率越高的话 性能提升，安全性降低

那么怎样控制系统的刷盘频率呢？上图刷盘时机有三种，下图默认值为1，大部分情况默认就好

1.4.7 Doublewrite buffer

前面晒了一张这个图，没有细讲，现在继续 Innodb数据页与os的 分别为 16k，4k而写入数据写入磁盘又是要一页页写入的，所以innodb的一页实际上要分四部分写入到操作系统 由操作系统写入到磁盘，那么

如果某一次吸入失败的话怎么办呢？这个时候由于扇区数据已被修改，无法恢复，而Doublewrite buffer（双写缓冲）就是起到这样一个备份的作用，扇区数据未写入完成出现异常时找到双写缓存恢复即可

1.4.8 bin log

因为是在server中 所以 所有的存储引擎都可以使用

比如说凌晨九点，某人删库跑路了，咋办，这个时候我们需要找到备份的数据，然后执行备份时间节点到删库时间中间bin log记录的语句 来恢复数据

如果 bin log也被删了咋办，凉拌。。

放到一个只有某些人有权限访问的地方把

1.4.9 一条更新语句的执行流程

为了保证数据一致性 看下图，简而言之就是binlog有记录 则提交 没有就回滚 来保证数据恢复与主重复制的一致性

二、Mysql 索引 深度剖析

2.1 索引初体验

2.1.1 简单用法示例

图一为查询 查询条件字段 name 没有索引 查询时间为2秒左右

图二 为给name字段添加索引 添加索引时间消耗 15秒 左右

图三 为 添加索引后的再次查询 这次 只消耗了 30ms左右

2.1.2 官方定义

简单介绍下，索引就相当与目录，下图左侧 map结构 键为索引字段的值 值为该行记录存储在磁盘上的地址 看不懂没关系，继续看着

2.1.3 怎样创建索引

除了上面图中 alter表 以语句形式添加外 还可以使用你用的客户端工具

指定 索引名称，添加索引的目标字段(可以为多个) 然后是索引类型

这里稍微提下 索引类型

如下图，有三种选项，

Normal就不说了，普通索引

Unique是唯一索引 添加后除了具备索引的特性，还不能重复，他与主键索引类似，不同的是主键是不能为bull的

Full Text 这个比较鸡肋 限制比较多 现在多采用收索引擎框架替代了 比如 ES 与 SOLR 可自行去了解

2.2 Mysql索引数据模型推演

本节大概看看就好了，只需要知道对于其他数据模型，B+树好在哪，为什么mysql索引数据结构要采用B+树 就好

2.2.1 有序数组与单链表

为啥索引数据 不存在 有序数组与单链表中呢，数据量越大时，层级越深 检索效率不稳定

2.2.2 二叉查找树

为什么不用二叉查找树呢，比上面俩哥们好点，但是还是老毛病 数据量越大时，层级越深 检索效率不稳定 而且左右子树层级可能相差很大

2.2.3 平衡二叉查找树

AVL Tree 保证左右子树深度差值不超过1

左子树 深度为2时减去 右子树深度 0 等于 2 大于 最大值1 所以右旋

数据单元的内容有四部分 索引字段的值，该值所处行在磁盘的地址 左子树指针 右子树指针

AVL一个节点存16Kb可达到IO效率最大化，可是现在只存一个单元，极大浪费了IO性能，而且树的层级会因此变得很深，查询效率会很低

2.2.4 B树

为啥不用B树呢，查询效率还是不稳定 虽然层级变少了 但是 检索时，有可能第一次IO就检索到 也有可能最大深度次数IO时才检索到

  

如下图，如果最大度数为3(也就是)一个节点最多三个分支时，也就是一个节点内最多存储俩单元的数据 那么在 此树新增与删除的同时

为了保持平衡 就会涉及到 分裂与合并

 

2.2.5 B+树

最终方案 B+树 B+树在B树上又做了优化

1.所有 数据都保存在了叶子节点 因此使用改索引树的检索效率稳定

2.叶子节点直接又添加了双向的指针 因此 在范围性检索时 不需要通过非叶子节点一层层遍历 直接 可以检索出某大小范围内的数据

3.同时要扫库扫表的话 也可以直接将叶子节点从头遍历到尾，而不需要从根节点一个个遍历下来

 

关于每个非叶子节点内的索引单元数量怎样计算出来呢？

如下图 我一次 为了尽量减少IO次数 从磁盘中检索的肯定 一页 16K的大小的数据 如图 索引单元数量 = 16K/(键值+指针大小) ，正常情况下千万上亿级别的表

建立索引后 度数也就2到3 所以IO次数特别稳定

简单比较下几种树的特点

其实还有个hash索引 这里稍微讲讲 它能保证检索次数为O(1) 但是无序 所以无法支持范围性查询 而且 hash 的键 不能重复 所以也不能加在 值可能重复的 字段上

而且Innodb是不支持hash索引显式创建的，使用MEMORY存储引擎时才可以显示创建，如下图指定即可

2.3存储引擎中索引如何落地

2.3.1表文件分析

首先找到文件

如下是三种存储引擎管理的表对应的文件，前面也晒了，下面来说说他们是啥

frm是定义表结构的文件，memory存储引擎只有这个文件 是 因为其数据放在内存中

ibd 文件 d 是 data 是存放数据的文件

myi 文件 i 是 index 是索引文件

那为什么 innodb 只有数据文件而没有索引文件呢？往下继续看你会找到答案

 2.3.2 MyISAM 索引结构

从如下俩图可以看出来，无论主键索引还是 其他索引 叶子节点都是 存储的数据的 磁盘地址 这里由于不是重点，简单看看就好

主键索引

 其他索引

2.3.3 InnoDB 索引结构

索引即数据，数据即索引 可以看到主键索引的叶子节点已经存放了所有数据，这下知道为什么上面为啥 InnoDB管理的表文件数量会少一个了吧

聚集索引

 站在索引设计者的角度，你觉得其他索引的叶子节点还会存放数据吗？显然不会 磁盘空间吃不消啊 真当某一天磁盘空间不要钱了 估计就会放了

这个时候就牵扯出一个概念了 ——》 聚集索引 :键值的顺序与 数据行的物理存储顺序一致时 该索引就可以作为聚集索引，而只有聚集索引的叶子节点才会存放

数据 聚集索引一般 是主键，如果没有主键的情况，后面会说 而除了聚集索引外的其他索引 都称为二级索引

如下为某二级索引 ，可以看到下图 二级索引的叶子节点存放的是索引字段的值 与 主键的值 那么为什么存放是主键的值呢？而不是数据的磁盘地址呢？因为新增修改删除时 数据行所处的地址可能会变 而 数据行的主键是不可修改不会变的

而下面那条红线 就是通过二级索引查到 数据行所在主键后再次 到聚集索引去查到 具体的数据行 这也就是 我们说的 回表

那么问题来了，我创建的表没有 主键 那数据放哪呢？

这个时候 如果你创建了不包含空值的唯一索引，那么聚集索引就是它

如果也没有，那么 这个时候又要说到一个_rowid的概念了

 2.3.4 索引的使用原则

前面我们看到往name字段上建立索引后，查询速度快了六十倍

2.3.4.1 索引越多越好吗

那么我们现在往 一张表上的所有字段都添加索引 可以吗？

 答案是不行

索引文件会比你的数据文件还要大很多 如下图 数据文件200多兆 而只建了俩索引的索引长度 就占了4G 这点在以后磁盘容量不要钱后无限大后或许可忽略把，

然后是 索引会在 增删改操作后进行修改 如果 你增删改很少 当然也可以建

 2.3.4.2 加了索引查询就会变快吗？

另外，不是加了索引查询就会变快

下图，

图一是没建索引前的查询 查询条件是gender (性别字段) 花费 3秒左右 ,

图二是花了8秒建了个索引

图三是建了索引后的查询，查询时间反而变成了30多秒 ，这是为啥呢？ 原因是我们 你想想，一个满是 0 1的树 该怎样寻找呢？该走哪条路呢？ 这样的索引反而会影响优化器的一个判断

那么到底该往哪些字段添加索引呢？ 依据之一为下图

离散度 其实只计算前面部分就可 因为 后面的count(*) 分母都是一样的

而gender 的离散度 在上表中 只有 2/5000000 而 phone明显高很多 1左右了

2.3.4.3 最左匹配原则

何为最左匹配原则 先简单看看下图 创建 ('name','phone') 的联合索引后 B+树中的键也是 按 联合索引的顺序 排列 他们索引对应字段的值 继续往下看

 那么现在 我添加 索引后

问题：下面 四条sql 哪些会使用到索引呢？

其实就跟过桥一样 桥上有 a,b,c三点 必须要 先经过a点才能到b点 经过 b点才能到 c点 ，若是没有b点那么你自然到不了c点 只能到达a点

再看下图 答案显然就是 前面三条sql语句 ？ 这里你或许有疑问了，第二条不是反的么 ，是的 ，但是优化器会进行优化 实际上 第一条与第二条语句 是一样的

2.3.4.4 不要建立冗余索引

如下图 联合索引 (name,phone) 其实就包含了 (name,phone) 与 (name）两条索引 ，那么第一个索引就是冗余的

 2.3.4.5 联合索引的建立的注意事项

作为查询或者分组或者排序 的字段 频率越高的 放联合索引最左边 按优先级来

2.3.4.6 覆盖索引

什么是覆盖索引？

如果select 的列 在二级索引中被包含，并且 该二级索引有被使用到 因此不需要回表查询 那么该查询就会覆盖索引

以下哪条语句 用到了覆盖索引 ？

第一第二条很明显都覆盖索引，第四条很明显不是的 第三条也是覆盖索引 执行计划中可以看到 、

为什么呢？查询条件中不是没有name 字段吗 ？ 这其实也是优化器的 优化 当看到 name字段在se'lect 中时 然后比较 全表扫描 会发现 使用 (name,phone) 的联合索引更快时 这个时候 会命中索引 所以 是覆盖索引

 2.3.4.7 索引下推

我们都知道 mysql有连接，server ，存储引擎三层

正常来说 下面语句 因为 like 后的值的 % 是在前面 而 在树中查找 键 的时候又是从前往后的 所以 前% 无法使用索引

那么正常来说这条语句的查询就是先通过二级索引查询到符合条件的叶子节点 然后通过这些叶子节点上的主键 去回表查询 到完整的行数据 然后返回给 server层去进行数据的筛选 符合 like ’%zi‘ 的数据

 但是 这里mysql又做了个优化 如果通过上面的方式的话 在server层 执行引擎调用 存储引擎接口可能会获取大量数据 大量数据的传输显然影响性能，这个时候

它会在 存储引擎中就先完成数据的筛选 然后 将最终的结果直接返回到 server层 这就是 索引下推

而执行计划中你看到Extra中 出现下面这玩意

那么它就是索引下推

 2.3.4.8 索引使用整理与前缀索引说明

再补充一些使用索引的细节 上面大部分都说了

过长的字段如果作为索引放到磁盘中 那么显然很占磁盘空间 可能导致 B+树的层级变多 这个时候 如果这个字段用的也多的话 可以给他建立前缀索引

 前缀索引建立语法如下 如下表示 用content 前6个字符作为 前缀索引

2.3.4.9 索引失效的场景分析

这里不多说，优化器会制定执行计划 当它 经过成本分析算法 得到的cost 如果使用索引会更高的话，那么索引就会失效

常见的失效场景为如下

 除了 Cost Based Optimizer外还有个 Rule Based .. 这里不多说 后者是根据规则，死的来 而 前者会根据数据情况等等来通过算法综合分析，感兴趣可以自行去了解

看完上面的内容，想必面试遇到下面这俩题 你应该没啥问题了

1.AVL树 解决了二叉查找树的不平衡问题，不平衡就以为着查找遍历的层级可能很深或者很浅

B树 在一个节点内 存放 经过数据页 与 键值对加指针组成的 单元的数据大小 确定的能存放尽可能的单元 来达到了空间的最大化利用 使得 树的层级很低

B+树 对比 B树又做了优化 B+树中将 数据都放在叶子节点中 并且 数据中都用 指针相连 算是个双向链表 这样 树中数据查找的IO次数尽可能稳定并且 当范围性查询的时候可以直接通过叶子节点返回数据而不需要通过从根节点一层层遍历比较

1. 使用递增的ID的原因是 ID作为主键 会 形成聚集索引 而 连续的ID意味着 数据存放的物理地址也是连续 如果不连续的话 那么 数据可能存放在不连续的各个物理地址 造成空间的不充分利用与碎片话

三、Mysql事务与锁详解

3.1 什么是数据库的事务

3.1.1 添加事务的场景与方式：

一般我们想到的 一个方法内 如果多次增删改 比如转账，这个用户减少 那个用户增加 必须同时成功或失败 那么 自觉 就给他添上事务了，现在添加事务很简单 @Transactional 还可以使用jdbc的代码

甚至是hibernate中使用配置文件

3.1.2哪些存储引擎支持事务

从下图就可以看到InnoDB支持事务，还有一个没听说过的NDB支持，这也是为啥InnoDB成为默认存储引擎的重要的原因之一

3.1.3四大特性

可以看到mysql的自动提交是开启的，所以我们在客户端的窗口上执行一条sql语句后，事务就自动提交了

在关闭自动提交后 我们就需要自己手动开启与提交事务 如下 begin commit

3.1.4事务的并发可能出现三种问题

在同一事务内两次相同查询 因为 读到其它事务未提交的数据而造成的问题 ：脏读

读到 已提交的数据 而这数据是 更新或者删除后提交的数据 时 产生的数据不一致问题 ：不可重复读

读到 已提交的数据 而这数据是 新增后提交的数据 时 产生的数据不一致问题 ：幻读

从如下几张图可以看到 使用 默认存储引擎 Innodb然后使用默认隔离级别 可重复读 就不会出现 上面三种问题

 3.1.5 四种隔离级别

默认隔离级别

那么，他是怎样做到可重复读的呢？

第一种加锁显然不符合 ，读取数据前就加锁 太不合理了， 极大影响性能

 3.1.6 MVCC

重点说第二种 MVCC

初步了解 是 有隐藏字段 记录操作事务的版本号 比如

事务1中 执行了两次查询 在 第一次查询完后

事务2 进行了提交 事务2 要保证他的提交不会影响事务1的第二次查询，那么他提交后 在当前行后记录的事务版本 就是他自己的事务版本号2

而事务1仍然查询事务版本号为1的数据 这些信息记录在undo log中

  

四、 Mysql中的锁

4.1 锁的力度

前面看了官网上的介绍，前者只支持表锁，而后者除了表锁之外还支持 行锁

 那么表锁与行锁有啥区别呢？看下图

我锁一张表粒度肯定更大把，效率肯定更快把，为啥呢？因为我锁某行要先找到这行记录才能锁，然后冲突概率肯定更大，

因为锁一行记录的话 只有 可能 操作这一行记录产生冲突 并发性能那肯定 加行锁 后更高

 有兴趣可以看看官网中的介绍MySQL :: MySQL 5.7 Reference Manual :: 14.7.1 InnoDB Locking

那么 表锁怎样加 与 解呢？ 如下 read write 分别是读写锁 继续看会说明 读写锁到底是啥

 4.2行锁

今天我们主要说行锁

行锁之共享锁

 共享锁 示例

可以看到我在一个记录中给某行记录加了共享锁，另外一个事务中还是能够加共享锁 加共享锁成功的标志是你成功查出了数据

 行锁之排他锁

 排他锁示例

如下三图，图一是给 该条数据加上排他锁 （增删改自动 加 排他锁 ）

 那么因排他锁引起的阻塞会持续多久呢？默认50秒

共享锁 与 排他锁 的总结

给某表加了读锁后 其他事务还能 读 但是不能写 但是如果加了写锁 那么其他事务对这张表啥都不能干 读写都不行 一个字 独

为啥这样呢？想想你的事务里读某条数据，别人的事务里也读某条数据 有必要排他么 没必要把 读出来都一样

但是你的事务里加 排他锁 说明你正在增删改 那么 其他事务里能读这条数据么，显然是不能的，除非你事务回滚或者提交 不然 别人就是读了你未提交的数据

 意向锁的作用，我们判断一张表又没加行 共享锁 或者 行 排他锁 要从上往下去看每条记录么？意向锁 简单来说就是个标记 flag 当这张表的某行被加上了共享锁时就会在该表上加意向共享锁， 这张表的某行被加上了排他锁时就会在该表上加意向排他锁 这俩锁里会有我们之前说到聚集索引的信息 来确定是哪些行

4.3 锁到底锁住了什么

锁的作用，到现在你应该知道了 解决的是并发的一个问题，那么锁是怎样实现的，到底是锁的啥？前面我有所提示，接着看

 错，锁住的是索引

记住当你使用 for update 时 一定要带条件，并且 条件得是索引字段 你可以用一个非 索引字段查询 然后加 for update 加锁，这个时候你会发现全表都被锁住了

如下图

4.4锁 锁住了什么范围

前面说的是针对某一条记录的锁 那么 现在要说的就是 使用 范围查询 加 for update 时 锁住的到底是什么范围

先看看这张图 看不懂没关系 继续往下看

4.4.1记录锁

上一小节已经说过，这里看看就好

4.4.2间隙锁

间隙锁是用来阻塞插入的

当加了for update 的查询范围中不包含 表中记录时 如下 会自动加间隙锁 而且间隙锁都是锁的一整个间隙 比如 (4,7) 不可能只锁一部分 （5，6）这种

4.4.3临键锁

当加了for update 的查询范围中包含 表中记录时 如下 会自动加临键锁 而且临键锁都是锁的一整个临键 比如 (4,7] 与 (7,10] 不可能只锁一部分 (4,7] 与 (7,9] 这种

4.4.4 注意

同一时刻，只可能存在一种锁 不可能又存在记录锁 又存在 临键锁 这样

4.4.5 四种隔离级别怎样实现的

看到这，你应该知道数据库的四种隔离级别是怎样实现的了，如下面四张图 还不懂的话我再来说说

图一 RU 我每次读都是直接从数据库读 那就不需要加锁

图二 串行化 相当于让 每条 语句按顺序执行 实现是 我事务里 读的时候就加共享锁 这样其他事务 写的时候就被阻塞 也就不存在 并发问题 但是效率极低

 图三 对比了 RR(可重复读) 与 RC(读已提交) RR是在 我事务的第一条语句执行前 就生成快照 而 RC是 每次都生成快照 这样 经undo log 辅助，RC由于没有临键锁

与 间隙锁 所以解决不了 插入带来的 幻读问题

 RC 与 RR选哪个

4.4.6 死锁的避免

4.4.7 死锁发生了怎样排查（看看就好，重在避免）

五、优化如何入手

5.1.重启数据库

如果你的数据库 为你不停息战斗了 几年 那么 可怜可怜它 趁夜深人静的时候 重启就完事了 怕有人看到 挂个系统维护的牌子

想必前面讲那么多是干啥的？ 就是为了 这一刻做铺垫 懂 一条select 语句 他执行到返回经历了啥 你才更能 入手优化不是

5.2 从连接上入手

5.2.1改配置

记住 配置的修改 都是为了更好的发挥硬件的性能

正常 公司现在都加 连接池了 那些用完的长连接 就会放到 连接池中 这里 好像最近最火的 是 hikari 连接池 说是从汇编语言代码层面做到了极致 速度很快 没选它的赶紧换成他吧

那么连接数 与超时时间怎样设置呢

连接数的话 看你服务器是几核的 hikari官网上 给四核推荐的 就是 9 为啥不越大越好呢 多了的话性能一定越高？ 线程的上下文之间切换也是要时间的

然后是等待的超时时间 根据需要调整把

当然大部分情况下使用默认的就可以了，然后要改的话一般也不需要你改 db工作 如果你身兼 开发运维产品多职...

5.2.2 基于主从复制的读写分离

如果你的项目 读多写少 然后 体量大 或者舍得花钱 那么 试试读写分离把

5.2.3分库分表

5.2.3.1 垂直分库

跟 微服务似的 按 业务主题的维度拆分

5.2.3.2 水平分库分表

5.3 加缓存

数据库自带的缓存比较鸡肋 ，经常用到 并且修改频率很低的数据 可以使用第三方 缓存 比如 redis

5.4 解析器与预处理器

这个没啥好说的，你sql执行 没错误就好了

5.5 优化器(慢查询日志)

优化器的作用是优化你的sql语句，然后是根据你表跟sql语句 生成多个执行路径 选个一个他认为 成本 （cost）最小的 执行计划 交给执行器去执行

如果公司有DBA的话 那么每隔一段时间 会把执行慢的sql 发你 分析慢的原因 告诉你要做啥调整

你自己想要看咋办 打开 cnf 文件 黄色标记与上面两行分别 是慢查询日志开关 多少秒以上算慢查询 与 慢查询日志记录文件存储的地方

 要咋看呢？

慢查询 你可以看慢查询的日志文件 但是 mysql有自带工具 mysqldumpslow 你可以用 --help 命令先看看有哪些参数

下面就能看到几条慢查询的sql记录

5.6机器的性能

示例问题描述

环境 MySQL5.7 CPU:4核 内存:16384 M 最大连接数:8000 最大IOPS:16800 空间：已使用 118.46G ( 共 500G )

table_open_cache:3000 table_open_cache_instances:16

Open_tables:2992 Opened_tables:27622

innodb_buffer_pool_size 3/4 调整到5/8， 重启后 内存从80%下降到70%，一两天后又继续飙升会了80%以上

参考答案

如果你关注的是为什么系统启动一段时间后，增加了10%（1.6G）的内存消耗，这个似乎并不是关键。
如果是的话，那就分析一下其他哪些进程占用了内存 top 。
​
否则的话就是关注MySQL。buffer pool已经用掉了62.5%（10G）的内存，剩下的内存本来也不多了。
如果对实际性能影响不大，这个值可以调低。
​
关注一下这些跟内存有关的参数：
show variables like ‘innodb_buffer_pool_size’;
show variables like ‘table_open_cache’;
show variables like ‘key_buffer_size’;
show variables like ‘query_cache_size’;
show variables like ‘tmp_table_size’;
show variables like ‘innodb_buffer_pool_size’;
show variables like ‘innodb_log_buffer_size’;
show variables like ‘max_connections’;
​
还有服务状态：
show full processlist;
show status;
show engine innodb status;
​
你的max_connections是否设置过大？这个本来指的是数据库的并发连接数，你的数据库是否有连接池？是否需要这么大的并发连接？
​
如果你去问阿里云客服，很有可能会让你升级内存……

5.7 表结构 与 存储 引擎

存储引擎的选择： 根据不同的业务场景可以选择不同存储引擎 比如 是否可以不用 innodb 用memory 还是 myisam 根据他们的特性来选择

表结构：字段类型 尽量采用最小的单位 比如性别就 tinyint 这种毕竟占空间，

然后 能 not null 就not null 前面说个 允许null 的 索引会多个字节 多点判断

图片视频存 相对路径 前缀放配置文件中

字段冗余 减少关联表查询 用的多并且基本不改的字段 可以加冗余字段 比如姓名啥的

比如交易历史表tran_seq 每天晚上定时任务 会把 tran_seq 中 数据放到 tran_seq_month中 而 tran_seq_month 中超过一个月之前的再放到

tran_seq_history中 这种 然后 tran_seq_history表如果是在oracle中可以分区

5.8从业务上入手

降级：比如双十一当天只能查询当天交易

削峰: 比如双十一预售 从十天前就开始

限流: 使用MQ

还有比如 引入 收索引擎 ES 啊

或者 使用 一些分布式的方案啊比如 腾讯那啥 TDSQL 对一系列功能优化进行了打包

比如关闭 某些功能，特殊时间段 为了提升核心功能的算力

六、Explain 执行计划分析

本来属于上面优化器部分 但是因为内容不少并且作为一个优化者必不能缺的一课 所以我把它单独出一大节了

6.1 学习前准备

建表+测试数据插入

先如下建表 然后插入测试用的数据

下面会按顺序说到这些 explain 中的字段 先稍微看看

6.2 id

执行计划中的id 有啥用呢？ 如下嵌套俩子查询 最内层的先执行

图一中 执行计划中的 id越大的 越先执行 执行顺序从大到小

图二中 id都相等 这个时候从上往下执行

如果既有相等id又有不等的 那么 先大后小 然后相等的 从上往下

而先执行查出来的数据要放在临时表内存储 所以 先执行的 查询 过滤出的数据 越小越优先执行，比如 图二 可以看到过滤后的数据量最大的是 teacher表

所以teacher表 放最后面 看着调就好

 

6.3 select type

有下面这些个类型，看下就好，优化谈不上

6.4table

表名

6.5partitions

如果用了mysql 的分区表的话 会告诉你在哪个分区下

6.6 type

这个字段对优化 来说参考意义很大 我下面只列了常见的，想要看全部可以去官网

如下从上到下性能越来越差 特别 是最后一个 全表扫描 看到了必须要优化

 前三种都没有优化空间了

system: 查系统表 且只有一条记录 这里针对InnoDB存储引擎

const: 针对唯一性索引 或者主键进行查询 只有一条记录

 eq_ref ：作为被驱动表 而且关联条件是唯一性索引 这种

ref ：查询条件为 非唯一性索引

 range ：对于索引的范围性查询

像下面这三种，大于 小于 between and in 都是

 full_index :对于索引的全部查询 不带过滤条件 如下 一般遇到也要优化

 all : 全表扫描 必须要做优化

 6.7possible keys

可能用到的索引 可能为多个 也可能为空

6.8key

最多一个 当 possible keys 为空时 经过优化器优化后 它可能有值 比如前面说的 覆盖索引 与 索引条件下推

key_len ：索引的长度

可以看到name字段为 255个字符

 因为 用的编码 是utf8mb4 所以一字符 4字节 然后 需要俩字节存储长度 最后 因为 这个字段 可能为null 还需要一个字节

所以 1023 = 255*4+2+1

所以 若是 一个字段 逻辑上不可能为空 那么 请建表时给它加上 not null 约束

6.9 ref

你在过滤的时候用的常量还是哪个字段呢 比如下图就是 const 常量

 6.10 rows

预计可能扫描多少行数据

当你就要查一条数据 但是预估行数 为 很多时 这个时候就是提醒你 可能要建索引了

6.11 filtered

当这个字段为100的时候没啥问题 这代表 从 调用 存储引擎接口 返回到 server层 的数据 100% 都是有用的 如果很低 这个时候就要想着加索引优化了

因为存储引擎中只能通过索引过滤

 6.12 extra 额外信息

 using filesort 是排序或者分组字段不是索引 不要看名字想着与文件有关

using temporary 是你查询 需要用到临时表 比如 distinct 每次遇到 不同的值就会添加到临时表

看到这俩 如果嫌慢的话 一般可以加索引 还有 using where 慢的话 也可以加 索引

七、实际慢查询优化案例分享

案例1

通过explain 发现 vso表进行了全表扫描，行数20万行，然后发现这张表跟vi.venue_id关联，再venue_id上，加个索引后，vso扫描行数为5千行，优化后0.5s

全表扫描在数据量大的情况下往往是 造成查询慢的主要原因之一，这里通过加上索引来优化

SELECT
    count(*) AS online_count,
    (
        SELECT
            count(vso.order_id)
        FROM
            venue_sub_order vso
        WHERE
            vi.venue_id = vso.venue_id
        AND vso. STATUS = 2
    ) AS orderCount,
    (
        SELECT
            count(ic.comment_id)
        FROM
            inter_comment ic
        WHERE
            vi.venue_id = ic.object_id
        AND ic. STATUS = 1
    ) AS interCount,
    (
        SELECT
            IFNULL(round(AVG(ic.grade)), 0)
        FROM
            inter_comment ic
        WHERE
            vi.venue_id = ic.object_id
        AND ic. STATUS = 1
        AND ic.srv_type = 0
    ) AS avg_num,
    bp.prov_name AS province_name,
    bci.city_name AS city_name,
    bco.county_name AS county_name,
    (
        SELECT
            count(1)
        FROM
            venue_notice vn
        WHERE
            vn.venue_id = vi.venue_id
        AND vn.`status` = 1
        AND vn.is_delete = 0
        AND now() < vn.expire_date
    ) AS notice_num
FROM
    venue_info vi
LEFT JOIN base_province bp ON vi.province_id = bp.prov_id
LEFT JOIN base_city bci ON vi.city_id = bci.city_id
LEFT JOIN base_county bco ON vi.county_id = bco.county_id
LEFT JOIN venue_item vit ON vit.venue_id = vi.venue_id
WHERE
    (
        vi.is_delete IS NULL
        OR vi.is_delete = 0
    )
AND vi. STATUS = 2
AND now() < vi.expiration_date
AND vi.venue_name LIKE CONCAT(CONCAT('%', '健身'), '%')
AND vit.sub_mode = 1
ORDER BY
    vi.audit_date DESC

案例2

总而言之 这次优化是 将多次连接后的查询变为一次性的

然后 将高频访问数据添加到缓存中 然后 建立了联合索引

1、业务场景 app获取用户通讯录并全量上传至服务端，服务端进行数据筛选并入库。app获取通讯录列表； 2、表结构与数据情况

DROP TABLE
IF EXISTS `tmx_base_address_book`;
​
CREATE TABLE `tmx_base_address_book_copy1` (
    `pid` VARCHAR (40) CHARACTER
    SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    `client_address_book_id` VARCHAR (200) CHARACTER
SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
 `company` VARCHAR (80) CHARACTER
SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
 `create_by` VARCHAR (255) CHARACTER
SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
 `create_time` datetime (6) NULL DEFAULT NULL,
 `delete_by` VARCHAR (255) CHARACTER
SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
 `delete_time` datetime (6) NULL DEFAULT NULL,
 `department` VARCHAR (80) CHARACTER
SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
 `is_soft_del` INT (11) NULL DEFAULT NULL,
 `name` VARCHAR (40) CHARACTER
SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
 `phone` VARCHAR (200) CHARACTER
SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
 `real_name` VARCHAR (40) CHARACTER
SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
 `update_by` VARCHAR (255) CHARACTER
SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
 `update_time` datetime (6) NULL DEFAULT NULL,
 `user_pid` VARCHAR (40) CHARACTER
SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
 PRIMARY KEY (`pid`) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;

相关表的数据量情况，索引情况：数据库日增长100万。发现问题时数据量220万。无索引

3、优化之前的SQL，结果 昨天晚上发现程序频繁报警连接池中获取不到连接。通过监控发现是查询这个表的数据特别慢造成的 4、优化方案 1）修改了代码逻辑。原有的代码逻辑判断通讯录是否更改是一条一条查询 然后赋值的。更改成获取一次在程序中进行判断之后统一存储 2）对用户的通讯录列表增加了redis缓存 3）增加了 user_pid和client_address_book_id的联合索引

优化后 数据库连接正常，接口平均响应时间2秒以内 数据库列表查询时间0.8秒

欢迎投稿你的性能优化案例

投稿后我看到了 会添加到文章中 请像上面两个案例一样按下面形式 投稿方式可以 私发 或者 评论区留言

 当你遇到一个慢查询后咋解决呢？一是有足够的理论基础 对执行计划进行分析 避免一些不必要的 问题 导致的全表扫描啊啥的

然后 是 不断的尝试 比如 子查询 改为 关联查询 or 改为 union 等等等等

另外 附一张总结图

如果能看到这里 你是真滴秀 欢迎关注 B站 请叫我觉哥   我会定期在B站直播陪伴学习