Mysql都有那些最需要掌握的原理？

如何写好SQL？如何发现日常开发中慢SQL的问题？

关于这些问题的解决，最好的方式当然是去了解MySQL的原理。本文选取MySQL体系中较为核心的部分内容，尽可能详细的介绍这些核心模块及其底层原理，尽可能多的加入实战案例来加深理解，以便于在以后的开发中更好的去避免问题，发现问题，解决问题。
MySQL版本时间线

2010年 MySQL 5.5
2012年 MySQL 5.6
2015年 MySQL 5.7
2016年 MySQL 8.0.1
2018年 MySQL 8.0.11(GA版本)

一、MySQL体系架构

MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层，具体如下图所示：

网络连接层

客户端连接器（ Client Connectors） ：提供与MySQL服务器建立的支持。支持现在主流的编程技术Java、C、Python、PHP等，通过各自的API技术与MySQL建立连接。

服务层（MySQL Server）

概述： Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

• 连接池（Connection Pool） ：负责用户的登录鉴权，执行操作鉴权，存储和管理客户端与数据库的连接
• 系统管理和控制工具（Management Services & Utilities） ：包含例如备份恢复、安全管理、集群管理服务和工具
• SQL 接口（SQL Interface） ：用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等
• 解析器（Parser） ：负责将请求的SQL解析生成一个"解析树"。然后根据一些MySQL规则进一步检查解析树是否合法
• 查询优化器（Optimizer） ：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互
• 缓存（Caches & buffers）： 包括全局和引擎特定的缓存，这个缓存机制是由一系列小缓存组成，如表缓存、记录缓存、key缓存、权限缓存等

存储引擎层（Pluggable Storage Engines）

存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异 。现在有很多种存储引擎，各有各的特点，最常见的是MyISAM和InnoDB，以及支持归档的Archive和内存的Memory等。

系统文件层（File System）

该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid文件，socket文件等。

二、一条查询sql的执行过程

一条查询语句的过程大致如下：

第一步：建立连接

输入用户名、密码进行登录，权限验证后正式建立连接。连接器会到权限表里面查出你拥有的权限，之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout控制的，默认值是 8 小时。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query。

长连接与短连接

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

建立连接的过程通常是比较复杂的，所以建议在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。在客户端体现就是使用线程池，springboot2及公司RDS SDK默认的数据库连接池为HikariCP。

第二步：查询缓存

建立连接后，就可以执行 select 语句了。执行逻辑就会来到查询缓存，MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个value 就会被直接返回给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

MySQL 8.0 版本后移除了该功能，因为查询缓存失效在实际业务场景中可能会非常频繁，假如对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。所以，一般在大多数情况下都是不推荐去使用查询缓存的。

MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

第三步：分析器

MySQL 没有命中缓存，就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：

第一步，词法分析，一条 SQL 语句由多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。

第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。

完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

第四步：优化器

优化器的作用就是以它认为的最优的执行方案去执行（有时候可能也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。

第五步：执行器

当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

更新语句

sql 可以分为两种，一种是查询，一种是更新（增加，更新，删除）。先分析下查询语句，语句如下：

update tb_employee set level = '5' where name='张三'

其实这条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志，这就会引入日志模块，MySQL 自带的日志模块是 binlog（归档日志） ，所有的存储引擎都可以使用，InnoDB 引擎还自带了一个日志模块 redo log（重做日志），就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

先查询到张三这一条数据，如果有缓存，也是会用到缓存。

然后拿到记录，把 level 改为 5，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。

执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。

更新完成。

为什么要用两个日志模块，用一个日志模块不行吗?

这是因为最开始 MySQL 并没有 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入 MySQL 的) ，MySQL 自带的引擎是 MyISAM，但是 redo log 是 InnoDB 引擎特有的，其他存储引擎都没有，这就导致会没有 crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启，之前提交的记录都不会丢失)，binlog 日志只能用来归档。

并不是说只用一个日志模块不可以，只是 InnoDB 引擎就是通过 redo log 来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行，为什么 redo log 要引入 prepare 预提交状态？这里我们用反证法来说明下为什么要这么做：

• 先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
• 先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

如果采用 redo log 两阶段提交的方式就不一样了，写完 binglog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设 redo log 处于预提交状态，binglog 也已经写完了，这个时候发生了异常重启会怎么样呢？ 这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

• 判断 redo log 是否完整 (commit)，如果判断是完整的，就立即提交。
• 如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。

这样就解决了数据一致性的问题。

总结

• 查询语句执行流程如下： 权限校验（如果命中缓存）–> 查询缓存 —> 分析器 --> 优化器 —> 权限校验 —> 执行器 —> 引擎
• 更新语句执行流程如下： 分析器 —> 优化器 —> 权限校验 —> 执行器 —> 引擎 —> redo log(prepare) —> binlog —> redo log(commit)

三、InnoDB VS MyISAM

功能对比 （InnoDB能取代MyISAM的原因）

InnoDB和MyISAM的功能对比如下：

• InnoDB支持ACID的事务4个特性，而MyISAM不支持;
• InnoDB支持行级别的锁粒度，MyISAM不支持，只支持表级别的锁粒度;
• InnoDB支持4种事务隔离级别，默认是可重复读repeatable read，MyISAM不支持;
• InnoDB支持crash安全恢复，MyISAM不支持;
• InnoDB支持外键，MyISAM不支持;
• InnoDB支持MVCC(多版本并发控制)，MyISAM不支持；
• InnoDB特性上，InnoDB表最大可以64TB，支持聚簇索引、支持压缩数据存储，支持数据加密，支持查询/索引/数据高速缓存，支持自适应 hash索引、空间索引，支持热备份和恢复等。

应用场景

MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。

InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

四、索引

一句话简单来说，索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

索引的数据结构

哈希、B树、B+树

哈希

哈希索引结构类似hashmap，仅能满足 等值查询，不支持范围查询。

B树

B树是一个平衡多路查找树，B为Blance，是为磁盘等外存储设备设计的一种平衡查找树。因此在讲B树之前先了解下磁盘的相关知识。

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

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

而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B-Tree的结构如下图：

特点

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

问题点

传统⽤来搜索的平衡⼆叉树有很多，如 AVL 树，红⿊树等。这些树在⼀般情况下查询性能⾮常好，但当数据⾮常⼤的时候它们就⽆能为⼒了。原因当数据量⾮常⼤时，内存不够⽤，无法将全部数据读入内存，⼤部分数据只能存放在磁盘上，只有需要的数据才加载到内存中。⼀般⽽⾔内存访问的时间约为50 ns，⽽磁盘在 10 ms 左右。速度相差了近 5 个数量级，磁盘读取时间远远超过了数据在内存中⽐较的时间。这说明程序⼤部分时间会阻塞在磁盘 IO 上。而B树数据存储在各个节点上，那么每次读入内存的信息就比较有效，一次查询可能产生很多次IO， 那么我们如何减少磁盘 IO 次数，于是有B+树。

B+树

B+树是在B树基础上的一种优化，使其更适合实现存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

B+树相对于B树有几点不同：

• 非叶子节点只存储键值信息。
• 所有叶子节点之间都有一个链指针。
• 数据记录都存放在叶子节点中。

B+树的结构如下图：

总结一下这种结构的优点：

• B+ 树的层级更少： 相较于 B 树 B+ 每个非叶子节点存储的关键字数更多，树的层级更少所以查询数据更快
• B+ 树查询速度更稳定： B+ 所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比B树更稳定
• B+ 树支持范围查询： 叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针
• B+ 树天然具备排序功能： B+ 树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高
• B+ 树全节点遍历更快： B+ 树遍历整棵树只需要遍历所有的叶子节点即可，而不需要像 B 树一样需要对每一层进行遍历，这有利于数据库做全表扫描。

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^{3）。也就是说一个深度为3的B+Tree索引可以维护10}3 * 10^3 * 10^3 = 10亿 条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2 ~ 4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1 ~ 3次磁盘I/O操作。

索引的分类

索引主要有两种分类方式：物理分类和逻辑分类

物理分类：聚集索引与非聚集索引

B+树索引可以分为聚集索引和非聚集索引，这里不是指单独的索引类型，而是一种数据存储的方式。上面的B+树示例图为聚集索引。

聚集索引（聚簇索引）

存储记录是物理上连续存在，物理存储按照索引排序，所以一个表最多只能有一个聚集索引，Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚集索引。聚集索引的B+树中的叶子节点存放的是整张表的行记录数据。

非聚集索引（非聚簇索引）

非聚集索引是逻辑上的连续，物理存储并不连续，数据在物理存储不按照索引排序。

非聚集索引索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据，这个过程称为回表。

PS： Innodb里非主键索引又被称为二级索引、辅助索引，均属于非聚集索引

逻辑分类：主键索引、唯一索引、普通索引

主键索引： 一张表只能有一个主键索引，不允许重复、不允许为 NULL；

ALTER TABLEtable_nameADD PRIMARY KEY (column)

唯一索引： 数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是联合索引，则列值的组合必须唯一。

ALTER TABLEtable_nameADD UNIQUE KEY key_name (column)

普通索引： 一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；

• 单列索引： 一个索引只包含一个列
• 联合/复合/组合索引： 一个组合索引包含两个或两个以上的列

ALTER TABLEtable_nameADD INDEX index_name (column)

Mysql中key和index的区别

在表的定义中经常看到key和index，但是使用中可能压根不会注意这个问题，因为大多数情况下他们展示出来的效果都差不多，但是还是不能将他们划等号（至少理论上是这样）

索引（index）和约束（key）的区别主要在于二者的出发点不同，索引(index)负责维护表的查找和操作速度，约束(key)负责维护表的完整性。

而有这个困惑的话，很可能是由于MySQL中有一个奇怪现象：

• MySQL中的索引是约束性索引（即创建索引自动也会创建约束）
• 并且MySQL中创建约束也会自动附带索引。

背后的原因：

MySQL中的约束效果是通过索引来实现的，MySQL数据库判断是否当前列是否unique就是通过unique索引判断的。

总结一下
key：

• 等价普通索引 key 键名 (列)
  primary key：
• 约束作用（constraint），主键约束（unique，not null，一表一主键，唯一标识记录），规范存储主键和强调唯一性
• 为这个key建立主键索引
  unique key：
• 约束作用（constraint），unique约束（保证列或列集合提供了唯一性）
• 为这个key建立一个唯一索引；
  foreign key：
• 约束作用（constraint），外键约束，规范数据的引用完整性
• 为这个key建立一个普通索引；

最左匹配原则、覆盖索引、索引下推

最左匹配原则

MySQL 建立联合索引有最左匹配的原则，即最左优先：

如果有一个 2 列的索引 (a, b)，则已经对 (a)、(a, b) 上建立了索引；

如果有一个 3 列索引 (a, b, c)，则已经对 (a)、(a, b)、(a, b, c) 上建立了索引。也就是会先以最左边的字段顺序建立索引，再依次建立索引。

以上面的（a，b，c）索引来说，对于最左边字段a来说，a是有顺序索引。b是无序的，但（a，b）是有序的，也就是在a有序的基础上看，b也是有序的。同样（a，b，c）也是这样。

不符合最左原则会导致*****索引失效*

以（a，b，c）索引为例

• 查询条件中没有第一个字段

比如 where b = 2 ，因为建立索引树的时候，a是第一个，没有最左边的字段，即使后面的字段建立了索引，也无法命中。

• 查询条件中，缺少第二个字段

比如where a = 1 and c = 2，通过a 字段可以匹配出一部分数据，但是没有b字段，就无法向下进行匹配。

• 索引顺序（查询优化器）

如果索引顺序是a,b 但是查询语句是 where b=2 and a = 1，这时候索引也能命中。这是由于mysql查询优化器会自动调整where 的条件顺序。

• 范围查询

比如where a = 1 and b > 100 and c = 2，此时 a b会走索引，c 不会走。

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。like 要注意一下：如果通配符% 不出现在开头，则可以走索引。

覆盖索引

如果 select 后面查询的字段都可以从这个索引的树中获取，而不用回表，这种情况一般可以说是用到了覆盖索引。在执行计划的 extra列里会有using index。

假设你定义一个联合索引CREATE INDEX idx_name_age ON t(name,age);

SELECT name,age from t where name='张三'

查找的字段 name 和 age 都包含在联合索引 idx_name_age 的索引树中，这样的查询就是覆盖索引查询。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

索引下推

索引下推优化（index condition pushdown），是MySQL5.6引入的一个优化，它可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

还以t表的联合索引（name, age）为例。

如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写为：

select * from t where name like '张%' and age = 10 and ismale=1;

无索引下推执行流程为

索引下推执行流程

联合索引的优点

介绍了上述的最左匹配原则、覆盖索引、索引下推，共同点都是基于联合索引，由此总结一下联合索引的优点：

减少开销： 建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

覆盖索引： 对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

筛选效率高： 索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w。

有序： 索引本身是有顺序的，当要对索引字段排序时，那么查询到的数据天然就是有顺序的，减少了排序的开销

索引的维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护，当插入入一个新记录时，如果新插入的ID值在数据中间，就需要逻辑上挪动后面的数据，空出位置。

而更糟的情况是，所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能就会受影响。

页分裂会产生表空间碎片

• 影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。
• 可能导致查询扫描的IO成本提升，效率查询降低；

当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，可将数据页做合并。即通过 optimize table 来重组文件。

产生表空间碎片的其他常见的原因

• 记录被Delete，且原空间无法复用；
• 记录被Update（通常出现在变长字段中），原空间无法复用；

自增主键 VS 非自增主键

自增主键的插入数据模式，正符合了递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键，由于每次插入主键的值近似于随机，则会产生很多移动数据，页分列，进而造成了大量的碎片，大大影响性能。

应该创建索引的列

• 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
• 在经常需要搜索的列上，可以加快搜索的速度
• 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
• 在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度
• 在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
• 在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

索引失效的场景

CREATE TABLE `t` (
        `id` INT ( 11 ) NOT NULL,
        `city` VARCHAR ( 16 ) NOT NULL,
        `name` VARCHAR ( 16 ) NOT NULL,
        `age` INT ( 11 ) NOT NULL,
        `addr` VARCHAR ( 128 ) DEFAULT NULL,
        `id_card` INT ( 11 ) NOT NULL,
        PRIMARY KEY ( `id` ),
        KEY `city_name_age` ( city,name,age ) ,
        UNIQUE KEY `unique_id_card` ( id_card )
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4

• 不满足最左匹配原则

Select * from

• 使用了select *
• 索引列上有计算
• 索引列使用了函数
• 字段类型不同
• like左边包含%
• 列对比
• 使用or关键字
• Not in和not exists
• Order by的坑

EXPLAIN

作用

Explain可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析所查询的语句或者表结构的性能瓶颈。

explain中的列

1. id列

该列为执行的顺序，每个号码，表示一趟独立的查询，id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行

2. select_type列

查询分为简单查询(SIMPLE)和复杂查询(PRIMARY)。

复杂查询分为三类：简单子查询、派生表（from语句中的子查询）、 union 查询

• SIMPLE：简单查询。不包含子查询和union

• PRIMARY：复制查询中的最外层的select
• DERIVED：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表

• SUBQUERY：包含在 select 中的子查询（不在 from 子句中）

• UNION：在 union 中的第二个和随后的 select

• UNION RESULT：从union临时表检索结果的result

union结果总是放在一个匿名临时表中，临时表不在SQL中出现，因此它的id是NULL。

3. table列

这一列表示 explain 的一行正在访问哪个表。

当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。

当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

4. type列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。

依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL

性能优化的目标，得保证查询至少达到range级别，最好达到ref

• NULL： mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

• const： 表示通过索引一次就找到了，用于primary key 或者unique key的列与常量比较时，所以表中只有一条记录，查询速度快

• system： 表只有一行记录，const类型的特例，一般很少出现，可以忽略

• eq_ref：唯一性索引扫描，primary key 或者unique key 索引的所有部分被连接使用，最多只返回一条符合条件的记录。

• ref： 非唯一索引说明，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行

• range： 范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

• index： 扫描全索引，一般是扫描某个二级索引，比all会快一些（index是从索引中读取的，而all是从磁盘中读取）

• all： 全表扫描

5. possible_keys列

显示可能应用在这张表中的索引，一个或多个。

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

6. key列

实际使用的索引。如果为NULL，则没有使用索引。explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

7. key_len列

这表示用到的索引字段的字节数，通过这个值可以算出具体使用了索引中的哪些列。

计算规则如下：

• 先看索引上字段的类型+长度比如 int=4 ; varchar(20) =20 ; char(20) =20
• 如果是varchar或者char这种字符串字段，视字符集要乘不同的值，比如utf-8 要乘3，utf8mb4 要乘4，GBK要乘2
• varchar这种动态字符串要加2个字节
• 允许为空的字段要加1个字节

列类型	key_len	备注
int	4+1=5	允许null 要+1
int not null	4
varchar(30)not null utf8	30*3+2=92	动态列类型 +2

如下city_user为city列和name列构成的联合索引，key_len=66（即4*16+2）可推断出查询使用了第一个列：city列来执行索引查找。

key_len=66（即416+2+416+2）可推断出查询使用了第一列和第二列：city、name列来执行索引查找。

8. ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名，指的是 “=”号后面的东西。

[图片上传失败...(image-903104-1659512077422)]

9. rows列

检查的行数，读取的行数越少越好。

10. filterd列

表示存储引擎返回的数据在server层（及其他过滤条件）过滤后，剩下多少满足查询的记录数量的比例

例如如下表数据：

name上无索引，需要扫描全表，共3行，rows=3，过滤后剩下1条，filtered=1/3

一个比较低filtered值表示可能需要有一个更好的索引

11. Extra列

这一列展示的是额外信息。常见的重要值如下：

• Using index： 查询的列被索引覆盖，及覆盖索引的场景，不用回表

• Useing where： 查询的where条件列未被索引覆盖

• Using filesort： mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种无法利用索引完成的排序操作称为“文件排序”。这种情况下一般也是要考虑使用索引来优化的。

如按照age排序时，age无索引，会文件排序，按照city则不会产生文件排序

• NULL： 查询的列未被索引覆盖，查询的where条件走了索引

• Useing index condition： 查询的列不完全被索引覆盖，条件使用索引，是一个范围

• Using temporary：mysql需要创建一张临时表来处理查询。

Mysql的锁

加锁目的是为了解决事务的隔离性问题，让事务之间相互不影响，每个事务进行操作的时候都必须先对数据加上一把锁，防止其他事务同时操作数据

按锁的粒度可以分为全局锁、表级锁、行锁、间隙锁、临间锁。

按锁的属性可以分为共享锁、排他锁

全局锁

全局锁就是对整个数据库实例加锁，使用场景比较少。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可

以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。全局锁的典型使用场景是，做全库逻辑备份。备份要加锁的原因主要是获得统一的视图，保证数据的逻辑一致，比如有一个用户余额表，与用户商品表，余额减了，那相应的用户会增加商品，也就是要保证全局的统一视图。

对于备份的场景补充说明：官方自带的逻辑备份工具是 mysqldump。导数据之前就会启动一个事务，通过MVCC（多并发版本控制）来拿到一致性视图。对于MyISAM这种不支持事务的引擎，则需要FTWRL命令。

表级锁

表级锁：又分为 表锁和****元数据****锁（meta data lock，MDL）

• 表锁语法是lock tables … read/write，用
• 元数据锁（metadata lock)，由server层实现，不需要显示的加，增删改查会加mdl读锁，对表结构进行变更时会加mdl写锁，读读不互斥，读写、写写互斥。

对表结构进行变更时会加mdl写锁，而执行一个ddl操作需要扫描全表的数据，那么对于很大的表进行变更的时候，是否会对服务产生影响呢？

先给出如下结论：

• 创建二级索引、删除索引、重命名索引、改变索引类型——不“锁表” 。
• 添加字段、删除字段、重命名字段、调整字段顺序、设置字段默认值、删除字段默认值、修改auto-increment值、调整字段允许NULL、调整字段不允许NULL —— 不“锁表” 。
• 扩展Varchar字段大小——不“锁表”
• 更改字段数据类型，如varchar改成text——“锁表”

实现机制：主要是依靠MySQL5.6.7 的新特性Online DDL ， 该特性解决了早期版本MySQL进行DDL操作同时带来锁表的问题，在DDL执行的过程当中依然可以保证读写状态，不影响数据库对外提供服务，大大提高了数据库和表维护的效率，当然Online DDL 并不是绝对安全，更不是可以随意的执行。线上操作还是需要在业务低峰期谨慎操作。

Online DDL这个新特性解决了早期版本MySQL进行DDL操作同时带来锁表的问题，在DDL执行的过程当中依然可以保证读写状态，不影响数据库对外提供服务，大大提高了数据库和表维护的效率。

• 早期实现方式(MySQL5.6.7之前版本)

早期版本MySQL执行DDL语句时主要通过以下方式进行：

COPY方式： 这是InnoDB最早期支持的方式，主要实现步骤：

1. 创建与原表结构定义一致的临时表；
2. 对原表加锁，不允许执行DML，但允许查询；
3. 在临时表上执行DDL语句；
4. 逐行拷贝原表数据到临时表；
5. 原表与临时表进行RENAME操作，此时会升级原表上的锁，不允许读写，直至完成DDL操作；

INPLACE方式： INPLACE方式也称为InnoDB fast index creation，是MySQL5.5及之后版本为了提高创建二级索引效率的方式，所以INPLACE方式仅限于二级索引的创建跟删除，主要实现步骤：

1. 创建临时的frm文件；
2. 对原表加锁，不允许执行DML，但允许查询；
3. 根据聚集索引的顺序，构造新的索引项，按照顺序插入新索引页；
4. 升级原表上的锁，不允许读写操作；
5. 进行RENAME操作，替换原表的frm文件，完成DDL操作。

相对于COPY方式，INPLACE方式在原表上进行，不会生成临时表，也不会拷贝原表数据，减少了很多系统I/O资源占用，但还是无法进行DML操作，也只适用于索引的创建与删除，并不适用于其他类型的DDL语句。

• Online DDL 方式(MySQL5.6.7及之后版本，8.0 新增了 INSTANT，但是使用范围较小)

Online DDL特性是基于MySQL5.5的InnoDB fast index creation上改进增强的。Online DDL同样包含两种方式：

1. COPY方式；
2. INPLACE方式。
   其中，某些DDL语句不支持Online DDL的就采用COPY方式，支持Online DDL的则采用INPLACE方式，因为Online DDL是对早期INPLACE方式的增加，所以INPLACE方式根据是否涉及到记录格式的修改又分为如下两种情形：
3. Rebuilds Table；
4. No-Rebuilds Table。
   Rebuilds Table操作是因为DDL有涉及到行记录格格式的修改，如字段的增、删、类型修改等； No-Rebuilds Table则不涉及行记录格式的修改，如索引删除、字段名修改等。
   相关过程概况如下：
   首先是Inplace方式创建索引，无需使用临时表。在遍历聚簇索引，收集记录并插入到新索引的过程中，原表记录可修改。而修改的记录保存在Row Log中。当聚簇索引遍历完毕，并全部插入到新索引之后，重放Row Log中的记录修改，使得新索引与聚簇索引记录达到一致状态。

与Inplace方式相比，Online Add Index吸收了Inplace方式的优势，只有在重放Row Log最后一个Block时锁表，减少了锁表的时间却减少了锁表的时间。

行级锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

InnoDB实现了如下两种标准的行级锁：

• 共享锁（Share Lock即S Lock）: 允许事务对一条行数据进行读取，显示加锁格式如下

SELECT * FROM t WHERE ... LOCK IN SHARE MODE

• 排他锁（eXclusive Lock即X Lock）: 允许事务对一条行数据进行删除或更新，显示加锁格式如下

SELECT * FROM t WHERE ... FOR UPDATE

默认情况下innodb用的是隐式加锁。另外，对于普通SELECT语句，InnoDB不会加任何锁。

InnoDB****行锁是给索引项加锁来实现的。这样的实现方式意味着当一个事务对表的某一行加锁后，后面的每个需要对该表加持表锁的事务都需要遍历整个索引树才能知道自己是否能够进行加锁，这样就会很浪费时间和损耗数据库性能。

于是有了意向锁(Intention locks)的概念：如果当事务A加锁成功之后就设置一个状态告诉后面的人，已经有人对表里的行加了一个排他锁了，你们不能对整个表加共享锁或排它锁了，那么后面需要对整个表加锁的人只需要获取这个状态就知道自己是不是可以对表加锁，避免了对整个索引树的每个节点扫描是否加锁，而这个状态就是我们的意向锁。

意向共享锁 IS/意向排他锁 IX 属于表锁，取得意向共享锁/意向排他锁是取得共享锁/排他锁的前置条件。

	S	X	IS	IX
S	兼容	互斥	兼容	互斥
X	互斥	互斥	互斥	互斥
IS	兼容	互斥	兼容	兼容
IX	互斥	互斥	兼容	兼容

间隙锁与临键锁

• 间隙锁（Gap lock）

间隙锁是在事务加锁后其锁住的是表记录的某一个区间（开区间），当表的相邻ID之间出现空隙则会形成一个区间，比如表里面的数据id 为 1,7,10 ，那么会形成以下几个间隙区间，(负无穷，1)，(1，7)(7，10)(10，正无穷)。

间隙锁作用： 用于阻止其他事务在该间隙内插入新记录，而自身事务是允许在该间隙内插入数据的，防止幻读问题。也就是说间隙锁的应用场景包括并发读取、并发更新、并发删除和并发插入。

触发条件： 查询条件必须命中索引，范围查询，等值查询未命中记录（若命中，则会升级为行锁）。间隙锁只会出现在REPEATABLE_READ（重复读)的事务级别中。在RU和RC隔离级别下没有间隙锁。

• 临键锁（Next-Key Lock）

临键锁是行锁+间隙锁，即临键锁是是一个左开右闭的区间，比如（3，5]。

InnoDB在RR隔离级别下，如果你使用select ... in share mode或者select ... for update语句，那么InnoDB会使用临键锁，因而可以防止幻读；但即使你的隔离级别是RR，如果你这是使用普通的select语句，那么InnoDB将是快照读，不会使用任何锁，因而还是无法防止幻读。

Order by 的工作原理

有如下表结构

CREATE TABLE `t` (
        `id` INT ( 11 ) NOT NULL,
        `city` VARCHAR ( 16 ) NOT NULL,
        `name` VARCHAR ( 16 ) NOT NULL,
        `age` INT ( 11 ) NOT NULL,
        `addr` VARCHAR ( 128 ) DEFAULT NULL,
        PRIMARY KEY ( `id` ),
        KEY `city` ( city ) 
) ENGINE = INNODB

查询语句

SELECT city,name,age from t where city='杭州' ORDER BY name LIMIT 1000

使用explain命令可以看到

Extra 字段中的Using filesort表示需要排序， MySQL会给每个线程分配一块内存用于排序，称为 sort_buffer，sort_buffer的大小由由参数 sort_buffer_size 控制

全字段排序

city 索引的示意图如下

上面查询语句的执行流程如下

1. 初始化 sort_buffer，确定放入 name、city、age 这 3 个字段
2. 从普通索引 city 中找到第一个满足 city = ’杭州’ 的主键 ID（ID_x）
3. 到主键索引树中找到 ID_x，取出该整行数据，取 name、city、age 这 3 个字段的值，存入 sort_buffer
4. 从普通索引 city 取下一个满足 city = ’杭州’ 的主键 ID
5. 重复 3、4 步，直到 city 值不满足条件
6. 对 sort_buffer 中的数据按照 name 做快速排序

把排序结果中的前 1000 行返回给客户端

这个排序过程叫做全字段排序，因为需要返回的字段都放入了 sort_buffer 参与排序过程

Rowid排序

排序可能是在 内存 中完成，也可能需要 外部 排序，这取决于排序所需要的内存和 sort_buffer_size 参数值。如果排序的数据量小于sort_buffer_size，排序就在内存中完成，否则会利用磁盘临时文件来辅助排序。

上面的查询中只返回 3 个字段，不会太长，可以一起都放在 sort_buffer 中，但如果排序的单行长度太大，MySQL会怎么做？

假设 name、city、age 这 3 个字段定义的总长度为 36，而 max_length_for_sort_data = 16，就是单行的长度超了，MySQL 认为单行太大，需要换一个算法。此时，放入 sort_buffer 的字段就会只有要排序的字段 name 和主键 id，那么排序的结果中就少了 city 和 age，需要回表了。

排序流程变为：

1. 初始化 sort_buffer，确定放入 2 个字段，name 和 id
2. 从普通索引 city 中找到第一个满足 city = ’杭州’ 的主键 ID（ID_x）
3. 到主键索引树中取出整行，把 name、id 这 2 个字段放入 sort_buffer
4. 从普通索引 city 取下一个满足 city = ’杭州’ 的的主键 ID
5. 重复 3、4 步，直到 city 值不满足条件
6. 对 sort_buffer 中的数据按照 name 做快速排序
7. 取排序结果中的前 1000 行，并按照 id 的值到原表中取出 name、city、age 这 3 个字段的值返回给客户端

这种排序过程称为 rowid 排序

全字段排序 vs Rowid排序

分析两个执行流程，可以得出：

• 如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。
• 如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问

对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

优化

由上面分析可以看出 MySQL 做排序是一个成本比较高的操作。那么，是不是所有的 order by 都需要排序操作呢？如果不排序就能得到正确的结果，那对系统的消耗会小很多，语句的执行时间也会变得更短。

从上面分析的执行过程，可以看到，MySQL 之所以需要生成临时表，并且在临时表上做排序操作，

原因是原来的数据都是无序的。

所以，我们可以在这个表上创建一个 city 和 name 的联合索引，alter table t add index city_user(city, name);

在这个索引里面，我们依然可以用树搜索的方式定位到第一个满足 city='杭州’的记录，并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要 city 的值是杭州，name 的值就一定是有序的。

这样整个查询过程的流程就变成了：

1. 从索引 (city,name) 找到第一个满足 city='杭州’条件的主键 id；
2. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；
3. 从索引 (city,name) 取下一个记录主键 id
4. 重复步骤 2、3
5. 直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束

查看执行计划，没有Using filesort

进一步优化

走覆盖索引，不再回表

count(*)的工作原理

COUNT() 函数，可以用来统计某个列值的数量，也可以用来统计行数。

拿 count(*) 为例，它在 MySQL 中不容的引擎有着不同的的实现方式，例如语句 select count(*) from t ，（注意这里不带任何的 where 条件）。

• 在 MyISAM 引擎 中，每个表的总行数都会在内存和磁盘文件中进行保存，当执行 count(*) 语句的时候，会直接将内存中保存的数值返回，所以执行非常快。
• 而在InnoDB 引擎中，当执行 count(*) 的时候，它需要一行一行的进行统计和计数，并将最终的统计结果返回。

也就是说，MyISAM 引擎中 count() 的时间复杂度是 O(1)，InnoDB 引擎中 count() 的时间复杂度是O(N)。

所以随着表中数据越来越多，使用InnoDB 引擎的表，这条语句执行得也会越来越慢。

那为什么 InnoDB 引擎就不能像 MyISAM 引擎一样，也把总行数保存到内存和磁盘文件中呢？

这是因为 InnoDB 引擎实现了多版本并发控制（MVCC）的原因：对同一个表，不同事物在同一时刻，看到的数据可能是不一样的。

InnoDB做的优化

InnoDB是索引组织表，主键索引的叶子节点是数据，而普通索引的叶子节点是主键值，所以普通索引树比主键索引树小很多。

对于COUNT(*)来说，遍历哪颗树都一样，所以mysql优化器会选择最小的树进行遍历。

在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

其他方式

SHOW TABLE STATUS命令中有个字段TABLE_ROWS，表示行数

实际上，TABLE_ROWS 就是从这个采样估算得来的(与索引统计的值类似)，因此它也很不准。有多不准呢，官方文档说误差可能达到 40% 到 50%。所以，show tablestatus 命令显示的行数也不能直接使用。

count()、count(1)、count(字段)之间的区别*

• count(1)： innodb引擎会扫描整个表，但不取数据。server层对于每一行放个1进去，判断不可能为NULL，逐行累计。
• count(字段)： 计算所有column字段为"非null"值的总数 。 若该字段声明为NOT NULL ，server直接累计得出数量。若可以为NULL，则server层还要判断每一行的值，不为NULL的进行累计。
• count()：* 专门做了优化，不取值，判断不可能为NULL，逐行累计。

性能：count(字段)<count(1)≈count(*)

作者：字节跳动技术团队
链接：https://juejin.cn/post/7127243755249205279
来源：稀土掘金