一、基础架构

1.整体架构

先看看mysql的整体架构图，如下

image-20230407151051373

即主要分为三层，最上面的连接层，中间的服务层与最底层的存储引擎层

2.连接层

MySQL的连接层是指位于MySQL架构中的一个组件，负责管理客户端与MySQL服务器之间的连接，它主要负责以下职责：

1）管理连接：连接层管理客户端与MySQL服务器之间的连接，包括建立、维护和断开连接。连接层能够管理连接池，使得MySQL服务器可以更有效地处理连接请求。

2）安全性：连接层可以提供安全性功能，如SSL/TLS协议和数据加密，以确保连接的安全性。这是特别重要的，因为MySQL通常用于存储敏感信息，如用户密码和财务数据。

3）协议处理：连接层能够处理多种协议，如TCP/IP、Unix套接字等，以适应不同的操作系统和网络环境。

3.服务层

缓存

mysql的服务层缓存主要包括三个部分：查询缓存、InnoDB缓存和键值缓存

1）查询缓存

最早的缓存机制，只能匹配完全匹配的sql查询语句，语句稍变就无法匹配

而且存在表更新操作会清除查询缓存，缓存占用内存过大等问题

因此在MySQL8.0版本中，已经废弃了查询缓存

2）InnoDB缓存

InnoDB缓存是InnoDB存储引擎中使用的缓存，它存储表和索引数据，用于加速查询和数据操作

InnoDB缓存使用LRU算法进行缓存管理，可以根据需要调整缓存大小，以满足不同的应用需求

3）键值缓存

键值缓存是MySQL 8中新加入的功能，它是一个基于Redis协议的缓存服务，支持主从架构和高可用性，用于存储短期缓存数据，如会话信息、配置信息等

解析器

主要工作是将SQL语句解析成MySQL服务器内部的数据结构

1）语法分析

对SQL语句进行语法检查，确保语句符合MySQL的语法规范

2）语义分析

对SQL语句进行语义检查，确保语句中使用的数据库、表、列等存在，以及是否具有正确的权限

3）转换SQL语句

将SQL语句转换成MySQL服务器内部的数据结构，方便后续的查询优化、执行计划生成等操作

简单点想，可以认为是将sql语句生成了一颗解析树，方便后续生成执行计划

优化器

负责对SQL查询进行优化，选择最优的执行计划

怎么理解上面这句话呢？下面看看优化器具体的职责吧

  • 查询重写：优化器会根据查询语句的特点，对查询进行重写，以便更好地进行优化。
  • 执行计划生成：优化器会生成多个可能的执行计划，并对这些执行计划进行成本估算，选择成本最小的执行计划作为最终的执行计划。
  • 索引选择：优化器会根据查询语句的条件，选择最优的索引，以便更快地查询数据。
  • 连接顺序选择：优化器会根据表的大小、索引使用情况等因素，选择最优的连接顺序，以便更快地连接多个表。
  • 其他优化：优化器还会根据查询语句的特点，进行其他的优化操作，比如子查询展开、常量合并、条件推导等

由此可见，一条sql，在优化器这里可能会生成多个执行计划，但是经过一系列代价计算步骤之后，优化器选择出一条它认为代价最小的执行计划作为最终的执行计划

这也是很多慢sql出现的原因，即优化器经过优化后选择了自己认为最优的执行计划，但是实际上和开发者所预期的执行步骤不相符，也并不是最优的

执行器

负责执行SQL语句，将查询结果返回给客户端

它还负责连接、认证、锁及事务管理等，不是重点，不再赘述

4.存储引擎层

是实际上执行sql语句，并负责将数据存储在磁盘上，提供对数据的读写操作的最终执行部分

最常见的存储引擎有innoDB、myisam和memory

1）InnoDB

InnoDB是MySQL默认的事务型存储引擎，支持事务、行级锁和外键约束等高级特性

  • 支持事务和行级锁：InnoDB是一个支持事务和行级锁的存储引擎，可以提供高并发和数据安全性

  • 支持外键约束：InnoDB支持外键约束，可以保证数据的完整性和一致性

  • 适合高并发场景：由于支持事务和行级锁，因此InnoDB适合高并发场景，可以提供高效的并发访问

2）MyISAM存储引擎

MyISAM是MySQL早期版本的默认存储引擎，不支持事务和行级锁，但具有快速的读取和写入性能，适合于一些非事务性应用场景

  • 支持全文索引：MyISAM支持全文索引，适合需要进行全文搜索的应用场景。
  • 适合读取频繁的应用场景：由于具有快速的读取和写入性能，因此MyISAM适合读取频繁的应用场景，如日志记录等。

3）Memory存储引擎

Memory是一种将数据存储在内存中的存储引擎，适用于临时表和缓存等应用场景

  • 存储在内存中：Memory存储引擎将数据存储在内存中，因此具有快速的读写性能。
  • 不支持持久化存储：由于数据存储在内存中，因此Memory存储引擎不支持持久化存储。
  • 适合临时数据和缓存：Memory存储引擎适合存储临时数据和缓存，如存储用户登录状态等

后面讲到索引，事务及锁的时候，都会再度比较这三个存储引擎的不同表现，此处不再进行更详细比较

5.Innodb

1）思考

首先思考一个问题，那就是对于InnoDB存储引擎来说，数据是放在磁盘上的，那么每次对数据进行操作，InnoDB都要去磁盘上寻找数据吗？是不是每次操作多少数据，InnoDB就从磁盘加载多少数据使用呢？

这很明显不可能，因为磁盘的随机寻址速度是很慢的，那面对这种情况，根据计算机常用的策略，肯定要加缓存了

事实上，InnoDB确实采用了缓冲池的设计理念，配合预读取的思路，在每次对数据操作时，会先去缓冲池中寻找，找不到再去磁盘读取，而且根据局部性原理，会多读取一部分到缓冲池里，这样当相邻数据被操作时，也能够快速在缓冲池中寻找到

这也就是InnoDb所谓的Buffer Pool与预读取了

2）数据页

在操作系统上，也存在预读取理念与设计，在数据存储的时候，操作系统将4KB数据划分为一页；而在InnoDB里，一页的大小则被划分为16KB，读取的时候就是按页读取的

在InnoDB的设计中，一次读取会将相邻的8页数据都读取到缓存里，这样是为了避免频繁访问磁盘，从而提升mysql速率；这个8页可以通过参数配置修改，但是不建议改的过大，原因也很简单，这是要占用内存的，缓冲池就那么大，一次读太多页进去可能很快就满了

3）数据写入

缓冲池不仅负责读取，在写入时也是先写入到缓冲池，再统一向磁盘进行写入，内存中与磁盘中页数据不一致的被称为脏页，那脏页何时写回磁盘呢？

还是前面提到的问题，如果每个修改都直接向磁盘写入，会导致磁盘每次都要随机寻址，导致写入效率低下，降低吞吐量，因此不如先存到缓存里，待系统没那么繁忙时再写回磁盘

这里写入的是缓冲池里一个称为Change Buffer的地方，属于Buffer Pool一部分

到这里也能看出了，Buffer Pool的存在就是为了增加读与写的速度，提高吞吐量

4）Redo Log

上面将修改的数据暂时存到了内存里，等待到达某一条件时统一写入磁盘，这样就会存在一个类似redis持久化时的问题—>还没写到磁盘，服务挂了怎么办？

因此，mysql采用了Redo Log日志来解决这个问题

Redo log中记录的是修改操作的物理数据，包括修改的数据库页、偏移量、修改前的数据、修改后的数据等信息。这些记录可以用来重做在数据文件上的操作，以保证数据库的一致性和完整性

这里就又有个问题了，日志肯定也要进行持久化，也就是意味着也要和磁盘进行io操作，那和直接把数据修改写入磁盘有什么区别？

  • 数据量：redo log记录的只是对物理数据的操作动作，并没有去操作，所以它的数据量是很小的，也就说明它的写入操作是比直接操作数据要快
  • 顺序IO：redo log的写入是顺序IO写入，一次寻址即可，而直接操作数据是随机寻址，会慢很多

此外，只有在redo log写入磁盘成功后才会触发数据到磁盘的修改操作

而且redo log也有自己的缓冲池，称为Redo log buffer，这是为了防止频繁io导致的上下文切换，统一将redo log写入磁盘

Redo log带来磁盘写入速度提升的同时，也带来了数据丢失与数据不一致的风险

数据不一致：redo log中数据持久化成功了，还没来得及将修改持久化到磁盘，系统就挂了；这种情况很好解决，只要redo log写成功了，机器重启后会再读取redo log进行数据的同步

数据丢失：这是由于redo log也采用了缓冲池技术带来的隐患

写入时机是什么时候呢？

在配置文件中有一个参数innodb_change_buffer_max_size可以控制，默认是1，即每次事务提交都会将log buffer 写入磁盘

5）Undo Log

Undo log记录了事务发生之前的数据状态，这样如果数据的修改过程出现了异常，可以根据undo log来进行数据的回滚操作

有了Undo Log之后，这里看一下以下更新语句会执行哪些步骤

1
update user set name = 'zhangsan' where id = 1;
  • 事务开始，从内存或磁盘取到包含这条数据的数据页，返回给server层的执行器
  • server层的执行器修改数据页的这一行数据为zhangsan
  • 记录name=zhangsan到undo log
  • 记录name=zhangsan到redo log
  • 调用存储引擎接口，记录数据页到Buffer Pool（修改name=zhangsan）
  • 事务提交

6）整体架构图

了解了上面的知识后，看一下innodb整体架构图

Innodb 逻辑存储结构 – EPHUIZI HOME

左边即上面说的缓冲部分，可见有Buffer Pool、Change Buffer和Log Buffer

既然是缓冲，那么就存在缓冲池满了的情况，那么在InnoDB中，缓冲池满了怎么处理呢？

在redis之中，内存淘汰策略有六七种，详情见redis淘汰策略

在InnoDB之中，内存淘汰策略采用的是变种的LRU策略，分为了young和old区域

这里的区域划分和JVM中的不一样，注意区分

7）LRU

正常的LRU是采用数组+双向链表来实现的

在InnoDB里面则采用了一个叫LRU List的结果来实现LRU，里面存储的是指向数据页的指针，并不存储真正的数据页

上面有提到过InnoDB的预读取机制，即一次并不只是读取需要的数据页，而是读取多个相邻的数据页，那么如果读取到的相邻数据页其实没什么用呢？如何淘汰这些使用频率不高的数据页呢？

LRU list采用了冷热分离的策略来实现淘汰，即把LRU list分成两部分，靠近head的地方叫做new sublist，用来存放数据，也就是热区；靠近tail的地方称为old sublist，也就是冷区；中间使用midpoint分割，如下图

img

执行流程：

  • 所有新数据加入到buffer pool的时候，一律先放到冷数据区的head，不区分预读还是普通读
  • 因此如果有些预读数据后面没用到，会在old sublist区直接被淘汰
  • 而放到LRU list之后，如果再次访问，就被移到热区的head
    • 为了防止热区数据被污染，只有在1s内再次访问的才能移到热区
  • 如果热区数据长时间没被访问，就会先移动到冷区的head部，最后慢慢移动到tail淘汰

此外还有很多优化操作，这里不再讨论

8）双写缓冲

由于InnoDB是16KB一页，而操作系统是4KB一页，那么在写入一半的时候，系统挂了怎么办？由于这时候是写入的页被损坏了，redo log无法恢复这种数据

双写缓冲就是解决这个情况的，它设置了一个双写缓冲区，在将数据写入磁盘之前会先写入到这里，待这里写入成功了再向磁盘写入，这样即使磁盘写入出错，也可以利用双写缓冲区进行数据恢复

9）binlog

在 MySQL 中，Binlog（Binary Log）是一种二进制日志格式，用于记录数据库的所有修改操作。它记录了所有的 DML（数据操作语言）和 DDL（数据定义语言）语句，包括 INSERT、UPDATE、DELETE、CREATE、ALTER 等操作。

Binlog 有两个主要的作用：

  • 数据恢复：当出现数据丢失或者其他意外情况时，可以使用 Binlog 进行数据恢复。通过 replay binlog 的方式，可以重放所有的修改操作，以恢复到丢失数据之前的状态。
  • 数据复制：Binlog 也是 MySQL 实现主从复制的核心组件之一。主库将修改操作写入 Binlog，从库通过读取 Binlog 并重放这些操作，以保持与主库的数据同步。

在 MySQL 中，可以通过配置 binlog_format 参数来设置 Binlog 的格式，主要有以下两种：

  • STATEMENT：记录 SQL 语句本身，对于相同的语句可以减少 Binlog 的大小，但是可能会出现不一致的情况，比如 UUID() 函数、NOW() 函数等在从库和主库执行的结果不一致。
  • ROW：记录 SQL 语句执行后的行数据变更，相对于 STATEMENT 格式，Binlog 的大小会更大，但是可以保证从库的数据与主库完全一致。同时，ROW 格式也可以避免因为 SQL 语句执行时出现的隐式转换等问题导致的不一致

二、索引

1.引言

索引是一种数据结构，用于提高数据库的查询效率

索引可以看作是对数据库中的某个列或多个列的值进行排序和分组的数据结构，它可以帮助数据库系统快速地定位到符合查询条件的数据行，从而提高查询的速度

常见的有B+树索引，哈希索引和全文索引

B+树索引：最常见，支持范围查询，排序和分组等操作

哈希索引：适合等值查询

全文索引：适合文本数据的全文搜索

2.索引类型

在创建索引时，会选择索引类型，那么不同类型的索引，都有什么性质呢？

在InnoDB里面，索引有三种类型

  • 普通索引：普通索引没有唯一性限制，可重复
  • 唯一索引：不能有重复的值，可为空
    • 主键索引是特殊的唯一索引，不允许有空值
  • 全文索引：用于文本类型数据全文检索

此外，索引还可以分为聚簇索引和非聚簇索引

  • 聚簇索引：将表的数据存储和索引存储在一起
    • 每张表唯一
    • 一般默认主键索引作为唯一索引
  • 非聚簇索引：包括普通索引，唯一索引，全文索引等，用于加速查找

关于聚簇索引后面还会详细分析，这里不再讨论

3.B+树

在InnoDB中，采用的是B+树来构造索引，但是它经常被拿来问与B树的区别，那就先来看下B树的结构

image-20230407210607863

由上图可以看出来，B树的非叶子节点也会进行数据的存储，并且叶子节点是独立的，下面分析下它的优劣

优点：

  • 减少IO：相比二叉树，B树每个节点分出的叉更多，因此可以保证更低的IO次数查询到数据，从而提高磁盘读写效率
  • 性能稳定：高度较低，遍历层数较少，从而性能稳定
  • 支持动态扩容：可在数据增加时自动调整结构

缺点：

  • 维护成本高
  • 插入和删除相对较慢

尽管B树的表现已经很优秀了，但是还是存在一些问题的，下面看下B+树的结构

B+树

由图可知，B+树的叶子节点使用双向链表连接在一起，因此支持范围查询

B+树的非叶子节点仅存储索引数据，因此每个非叶子节点处都能存储更多的索引数据

对比B树如下：

  • 更少的IO：B+树每个非叶子节点仅存储索引数据，因此可以放下更多的子节点，整体上能够使得B+树的高度更低，从而较少IO，提升查询效率
  • 更适合范围查询：B+树的叶子节点使用双向链表串联在一起，因此更适合范围查询
  • 更适合高并发插入：B树的所有数据都存储在叶子节点上，因此对于插入操作来说，只需要更新叶子节点上的链表即可，不需要更新非叶子节点，从而减少锁的粒度，提高并发性能

4.索引落地

此处仅对比InnoDB和MyISAM中的索引实现

1）MyISAM

它其中有两个文件，分别是.MYD和.MYI文件

.MYD：数据文件，存放数据记录

.MYI：索引文件，存放索引

那么如何根据索引找到数据呢？

MyISAM的B+树，叶子节点存储的是数据文件对应的磁盘记录，然后到数据文件里面获取数据

2）InnoDB

与MyISAM不同的是，InnoDB的叶子节点里面直接存储了数据

所以有个说法，即在InnoDB里面索引即数据，数据即索引，就是这个意思

这里就有个问题出现了，索引那么多，数据存在哪个索引的叶子节点呢？

  • 聚簇索引：在InnoDB中，聚簇索引就是索引键值的逻辑顺序跟表数据行的物理存储数据是一致的

  • 非聚簇索引/二级索引：InnoDB之中，不是聚簇索引的索引，都是非聚簇索引

而在InnoDB之中，为了防止每次更新索引后都要更新所有的索引叶子节点，所以只在聚簇索引的叶子节点之中存储了真正的数据值，而其他二级索引的叶子节点都是指向聚簇索引的叶子节点

哪些索引能称为聚簇索引呢？

  • 主键索引：当表中存在主键索引时，主键索引就是聚簇索引
  • 唯一索引：如果没有定义主键，那么InnoDB会选择第一个不包含NULL值的唯一索引作为主键索引
    • 不包含NULL值也是主键索引和唯一索引的最明显区别
  • 隐藏列：如果上面两者都没有，那么InnoDB就会创建一个6字节长的ROWID列作为隐藏的聚集索引，它是随着行记录的写入而递增的

回表查询

相比主键索引成为的聚簇索引，二级索引叶子节点存储的往往是主键的id值，因此使用二级索引查找数据时，需要再根据id值走一遍主键索引，这就是所谓的回表查询

5.索引使用原则

什么样的数据列适合建立索引呢？

1）离散度

离散度公式：

count(distinct(column_name))/count(*)

即去重后数据/总数据行

离散度越大越适合创建索引

2）联合索引最左匹配

所谓联合索引，即使用多个数据列创建一个索引

联合索引生效的前提是要按照创建联合索引的顺序，从左向右匹配才能使得索引生效

最左匹配原则：最左匹配原则是指，在一个联合索引（复合索引）中，如果查询条件涉及到联合索引的多个列，那么查询时应该优先使用联合索引的最左边的列。

例如，对于联合索引 (a, b, c)，如果查询条件中包含 a、b、c 三个列，那么应该以 a 作为第一关键字进行匹配，只有在 a 相等的情况下，才会去匹配 b，以此类推。如果查询条件只包含 b、c 两个列，那么联合索引就无法使用。

3）覆盖索引

覆盖索引指的是在查询语句中，可以直接通过索引来获取所需的数据，而不必访问数据表。具体来说，如果一个查询涉及的列都在同一个索引中，那么这个索引就是一个覆盖索引。

使用覆盖索引的好处是可以减少查询所需的 IO 操作和 CPU 计算，从而提高查询性能。因为不需要访问数据表，所以查询所需的时间和资源就会更少。

在使用覆盖索引时，需要注意以下几点：

  • 索引要包含所有需要查询的列，而且要尽量少地包含其他列，因为不需要的列会增加索引的大小，导致查询性能降低
  • 使用 SELECT * 或 SELECT col1, col2 … 语句时，不能使用覆盖索引，因为这些语句需要返回表中所有列的值
  • 覆盖索引对于频繁更新、插入或删除的表来说，并不是最优的选择，因为每次操作都需要更新索引

4）索引下推（ICP）

索引下推（Index Condition Pushdown，简称ICP）是MySQL查询优化器的一项优化技术，用于减少查询所需的IO操作，提高查询性能

在传统的查询中，MySQL会首先根据索引查找到满足条件的数据行，然后再从磁盘中读取这些数据行的其他列，最后再进行筛选和聚合等操作。这个过程中，需要进行大量的磁盘IO操作，耗费大量的时间和资源

而索引下推则是将筛选和聚合等操作尽可能地推到索引层进行处理，避免了不必要的磁盘IO操作，从而提高了查询性能。具体来说，MySQL查询优化器会根据查询条件，判断哪些条件可以直接在索引上进行计算，哪些条件需要在数据行中计算。然后，MySQL会将可以在索引上计算的条件转化为对应的索引扫描条件，以减少数据行的读取，提高查询效率

说人话，就是把过滤的动作交给存储引擎做完，而不是在服务层过滤

ICP默认开启

举个例子，假如有联合索引(last_name,fast_name)

执行以下查询语句

1
select * from employees where last_name = 'wang' and first_name like '%zi';

这个语句如何执行呢？

第一种查询方式

  • 假如根据联合索引查出所有姓wang的二级索引数据三个主键值：6，7，8
  • 回表，到主键索引上查询所有符合条件的数据
  • 把这3条数据返回给server层，在server层过滤出以zi结尾的名字

这种方式存在问题，此时是只用到了wang这个索引，查到3条数据给server层，然后再在server层进行过滤%zi，获得最后两条数据，那假如wang过滤出来1000w条数据，实际上%zi只有一条数据符合呢？这个任务都交给server层是很不合理的

第二种查询方式

  • 根据联合索引查出所有姓wang的二级索引数据：3个索引
  • 然后从二级索引中筛选出所有first_name以zi结尾的索引—1个索引，id为8
  • 然后回表查询，到主键上找所有符合条件的数据返回

很明显，第二种查询方式效果更好，而这也就是所谓的索引下推

6.索引的创建与使用

索引创建需要遵循一定的原则，而不是可以随意创建的，因为随着数据结构的变更，维护索引也需要成本的

以下是一些创建索引尽量遵循的原则

  • 在用于where判断order排序和join的on，group by的字段上创建索引
  • 索引的个数不要太多
  • 过长的字段，使用前缀索引
  • 区分度太低的字段，不要建立索引，例如性别
  • 频繁更新的值不要建立索引
    • 可能导致页分裂
  • 随机无序的值，不建议作为索引，如身份证
    • 无序，分裂
  • 组合索引把散列度高的放在前面
  • 创建复合索引，而不是修改单列索引

7.索引失效

那么什么时候建立的索引使用不到呢？

索引失效多是因为一些操作导致结果不确定，从而无法使用索引

  • 索引列上使用函数和表达式，逻辑运算符进行计算
  • 字符串不加引号，出现了隐式转换
  • like前面用了%
  • 负向查询
    • 如NOT LIKE
  • ‘!= ‘和 ‘<>’还有’NOT IN’这三者在某些情况下可以
    • 跟数据库版本，数据量，数据选择度都有关系

其实，是否使用索引，最终都是由优化器决定