MySQL学习及原理浅析

介绍

从今天开始，我们将学习目前最流行的一个数据库MySQL。

这篇文章，带大家走进Mysql的世界。

• MySQL的底层结构
• MySQL的查询过程
• 索引结构
• MySQL的三种Log
• MySQL的存储引擎

MySQL的底层结构

MySQL我们可以主要分成三层：

1. 客户端连接层。主要负责处理客户端的连接，用户的账号密码校验，权限等功能。
2. 核心服务层。主要功能有缓存，对SQL语句的解析和优化，对底层API的调用。
3. 存储引擎。 对数据对管理，存储，查询，事务，索引等等功能。

MySQL的查询过程

一个SQL的查询过程可以分成六步。 如图：

1. 客户端和MySQL建立连接。
2. 查询缓存，如果缓存命中，直接返回。但是不建议使用缓存，命中率低，弊大于利。
3. 解析SQL，生成语法解析树。如果SQL写的有问题，这一步会报错。
4. 查询优化，通过语法树，生成执行计划，计划可能不止一个，优化器会找到成本最小的一个执行计划。
5. 执行计划，查询执行引擎拿到了执行计划，调用对应的存储引擎的接口，来执行查询，得到结果。
6. 返回结果，缓存结果。如果结果集很大（1W条），不会等到全部结果出来再返回，会在第一条结果出来的时候，就开始返回结果。

MySQL的三种Log

MySQL有三种Log，各司其职。分别是binlog、redo log、undo log。

binlog

binlog的主要作用是记录数据库的每一步变化。由MySQL核心服务层控制。

可以让别的服务来监听，实现数据同步。

或者再数据库损坏之后，恢复数据。

数据库的每一次变化，比如某一个表新增了一条数据，就会生成一个对应的binlog。
另外的服务监听到了之后，可以做出对应的操作，把数据同步一份。

可以做主备，可以做读写分离。

redo log

首先我们先要了解到一点，磁盘的IO昂贵，数据库再做数据变更的时候，都不会一有变更，就立即更新磁盘数据。

做法都是先在内存中进行操作，等到合适的时候（配置决定），再一次性的刷到磁盘当中。

但是如果数据在内存当中变更了之后，数据库崩溃了，这个时候内存中的数据就会丢失。

MySQL就会有一个redo log来保证上述情况的数据不丢失。

redo log由存储引擎控制。

当MySQL修改数据的做法如下：

1. 把对应数据的页，查询出来，并保存到内存中
2. 修改内存中的数据，同时生成redo log（哪一页，修改了那些内容）。原子操作
3. 如果这时MySQL崩溃，可以根据redo log恢复内存中的数据。
4. 如果没有崩溃，会在合适的时候，把内存中的数据落到磁盘上，同时删除对应的redo log。

undo log

undo log和事务有关。InnoDB存储引擎特有。

在一个事务中，每一次操作，都会生成一个log，所有的log会组成一个数据链。

可以用来控制回滚，和在不同的事务级别下，所展示的数据的版本。

这就是MVCC，多版本并发控制。我们在事务那一章的时候，还会详细说明。

MySQL的存储引擎

MySQL的存储引擎的设计是MySQL的一大特点。
它可以让我们灵活的使用不同的存储引擎来应对不同的需求。

我们可以使用：

SELECT ENGINE, SUPPORT FROM INFORMATION_SCHEMA.ENGINES;
-- 或者
SHOW ENGINES;

来查询存储引擎列表，和当前MySQL是否支持。

我们可以查看官方文档

我们在这里也做一下简单的介绍：

InnoDB

我们最常用的存储引擎当属InnoDB。

它支持事务，符合ACID，支持行级锁，
支持B树索引，新版版还支持全文索引，
使用MVCC多版本并发控制，支持崩溃恢复，
还支持外键。

它是我们一般情况下的首选。

在需要事务的场景中，我们更是无脑选择InnoDB。

MyISAM

MyISAM它的特点是不支持事务。当时它的优势是结构简单，比InnoDB稍快。

读效率比写更快。适合需要大量读的场景中。

支持B树索引和全文索引。如果小项目不想使用ES的话，可以考虑使用MyISAM做视图。

Memory

看名字就知道，它的特点是把数据存储在内存中的，
所以没有持久化，断电崩溃数据会丢失。

但是就是因为使用的内存，它的速度非常快。可以部分替代Redis的功能。

Archive

如果你需要保存日志信息，存档信息等一些需要快速查询的场景。
你可以使用Archive。

它支持数据压缩，不支持事务、索引。

可以部分替代MongoDB的功能。

CSV

CSV的结构很简单，它会生成一个.cvs的文件来存储数据。

我们可以使用Excel来打开它。一般很少使用。

其他

除了上述的存储引擎，剩下还有一些存储引擎。
比如说：Merge、Federated、Blackhole、NDB、Example
具体参考官方文档

索引结构

索引是为了提高查询效率的一种手段。

如果我们的数据一本新华字典的话，索引就是字典的目录。可以大大提高我们查询的效率。

但是索引是有代价的，它会提高修改数据的成本，也需要单独的空间来存放索引数据。

常见的索引结构有以下几种；

• Hash
• B tree
• 全文索引

本文，我们会重点说说B树这种结构。

什么是B树

B树是一种多叉树，一个根节点下面一般有很多叶子节点，所以B树的高度一般不高。

使用B树存储的数据都是有序的。

如下图：

B+树

B+树是B树的一种升级版。

它的特点是，只有叶子节点存储数据。非叶子节点只存储索引。

叶子节点（数据节点）同样也是有序的，并且相邻的两个叶子节点使用双向链表连接起来，这样范围查询的效率非常高。

可参考下图：

MySQL的InnoDB存储引擎使用的就是B+树作为索引结构。

每一个节点的大小固定，迎合一次磁盘IO，大小应该是16KB。

而非叶子上存储的都是索引，所以一个节点上可以存储非常多的索引数据。
再通过二分查找，来找到对应的数据地址。

聚簇索引

在InnoDB存储引擎中，表中的数据都是通过一个B+树来维护的，
同时表中所有的数据，都存放在B+树的叶子节点上。

而非聚簇索引，B+树的叶子节点上，存储的都是主键ID。
如果需要找到详情的数据信息，需要回表查询，即拿到主键ID，到主B+树（聚簇索引）上再查询。

如何通过索引实现范围查询

如果我们有一个这样的查询语句：

# 查询今年以来的订单
select * from order where create_time > '2021-01-01 00:00:00';

为了加快查询效率，我们会在create_time字段上添加索引。
这样就有了一个按照时间排序好的索引B+树了。

我们的查询会从全表扫面，变成通过索引查询。

我们会先通过索引找到第一create_time为’2021-01-01 00:00:00’的订单索引，

再通过B+树叶子节点间的链表指针，往后扫描，找到满足条件的所有的订单索引。

再拿到满足条件的订单主键ID，去订单主聚簇索引中回表查询详细信息，返回结果。

联合索引、索引下推、覆盖索引

联合索引

我们可以给一个字段添加索引，如上述的订单创建时间。

但是很多情况下的业务复杂，筛选条件众多，我们会为几个字段一起添加一个联合索引。

比如订单的买家、订单创建时间和订单状态可以作为一个联合索引。

这样在应用程序的客户端当中，买家查询自己的订单列表，并按照订单创建时间倒排，并可以通过订单状态做筛选条件。
这种情况下的查询效率会很高效。

索引下推

索引下推只会作用在联合索引上。并且会发生在范围查询上。

一句话说明：索引下推会尽量使用索引来判断where条件，而减少回表次数。

还是以订单表为列子，联合索引：客户姓名、订单时间

如果我们要查询：今年以来，姓王的客户的订单

select * from order where create_time > '2021-01-01 00:00:00' and customer_name like '王%';

而我们都知道，范围查询会中断索引。

如果我们不使用索引下推的话：

我们会在客户姓名、订单时间索引B+树中找出满足客户姓名姓氏的所有订单ID

拿到所有订单ID去主键聚簇索引中回表查询数据，再判断今年以来的订单数据。

这种情况下，回表查询的订单ID会非常多。

而如果我们使用索引下推：

我们会在索引的B+树中，提前查询、筛选中满足姓氏和时间的订单ID。

再回表查询。

这种情况下，回表查询的订单ID是提前筛选好的，会少很多。

覆盖索引

还是上述情况，订单表的联合索引客户姓名、订单时间。有这样的查询语句：

select id from order where create_time > '2021-01-01 00:00:00' and customer_name like '王%';

SQL语句中的所有的查询条件和返回数据，在联合索引的B+树中都有，

我们只需要通过索引查询出来主键ID，就可以直接返回结果了，不需要再回表查询。

这就是覆盖索引。

总结

本次我们探讨了一种目前最流行的关系型数据库MySQL。

带大家了解了下MySQL的基本结构，它的查询过程。

通过三种Log来了解了一些MySQL的内部原理。

还有索引相关的一些内容。

希望大家都有所收获。

我们下次还会讲到MySQL的事务和MySQL调优相关的内容。