阿里P8架构师私人珍藏的MySQL笔记，学到就是赚到

基本架构

MySQL 基本架构示意图如下：

MySQL 大体可以分为两部分：Server 层和存储引擎层（功能跟日常开发中的 Service 层和与 DAO 层有点像，可以对比理解）。

Server 层

主要有连接器（Connector）、查询缓存（Cache）、分析器（Parser）、优化器（Optimizer）和执行器（Executor）等，包括了 MySQL 的大部分核心功能以及所有内置函数（日期、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，例如存储过程、触发器、视图等。

存储引擎层

存储引擎（Storage Engine）层主要负责数据的存储和提取，它是直接和磁盘打交道的，以插件形式存在，例如 InnoDB、MyISAM、Memory 等多种存储引擎。

从 MySQL 5.5.5 开始，InnoDB 成为了默认的存储引擎。

Server 层

连接器

主要功能：跟客户端建立（TCP）连接、获取权限、维持和管理连接。

若用户认证通过，连接器会查询权限列表获取该用户的权限，之后该连接的权限判断都基于此（因此，一个用户建立连接后，即使被修改了权限也不会影响已存在连接的权限，只有重新建立连接后才生效）。

客户端建立连接示例（分别为失败和成功）：

连接失败（密码错误）

$ mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)

连接成功

$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright © 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

进入 MySQL 后可以用如下命令查看连接状态：

查看连接状态

mysql> show processlist;
±—±-----±----------±-----±--------±-----±---------±-----------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
±—±-----±----------±-----±--------±-----±---------±-----------------+
|  6 | root | localhost | NULL | Sleep   |   81 |          | NULL             |
|  7 | root | localhost | NULL | Query   |    0 | starting | show processlist |
±—±-----±----------±-----±--------±-----±---------±-----------------+
2 rows in set (0.00 sec)

在 Command 列中，Sleep 表示该连接是空闲的。

连接成功后是有超时时间的，若太长时间没有操作会断开连接，由参数 wait_timeout 控制，默认为 8 小时，

查询缓存

主要功能：缓存查询结果。

连接建立之后，就可以进行查询了。

在一个查询语句中，会先到缓存中查询之前是否查询过该语句，若存在则直接返回对应的结果；否则继续执行后面的流程。

PS: 此处理流程可以类比我们在项目中使用 Redis 等作为缓存的操作，即先查缓存，再查 DB。

也可以通过使用 SQL_CACHE 显式指定使用查询缓存（这里的 id 并非主键），例如：

SELECT SQL_CACHE * FROM t1 WHERE id=10;

查询缓存的优缺点：

1. 优点：查询命中缓存时效率很高。

2. 缺点：缓存失效非常频繁，只要有对一个表的更新，该表所有的查询缓存都会被清空。

由于上述优缺点，可以发现缓存适用于静态表或更新较少的表，对于更新较频繁的表并不适用。值得一提的是，MySQL 8.0 版本已删除了查询缓存功能，可见该功能比较鸡肋。对于新手小白想更轻松的学好Java提升，Java架构，web开发、大数据，数据分析,人工智能等技术，这里给大家分享系统教学资源，加下我尉（同英）：CGMX9880 内容包括：redis、高并发、多线程、分布式、MySQL、数据库、线程、锁、dubbo、jvm、Java虚拟机、spring、memcached等。

分析器

主要功能：对 SQL 语句进行词法分析和语法分析。

1. 词法分析：分词操作，由于我们传递给 MySQL 的 SQL 语句实质上就是一个字符串，MySQL 需要将其拆分成一个个的分词（语法树）并进行识别，例如识别“SELECT”、“UPDATE”等关键字，将 t1 识别为一张表，将 id 识别为一列等。

2. 语法分析：拿到词法分析的结果，并根据语法规则判断 SQL 语句是否合法。若语法错误，则会收到如下错误提示：

You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near …

优化器

主要功能：优化 SQL 语句。

经过了分析器之后，MySQL 已经知道了我们提交的 SQL 语句是干嘛的。但为了提高执行效率，它并非完全按照我们的 SQL 语句执行，而要进行一系列优化。例如，当表中有多个索引时决定使用哪个索引；多表关联（JOIN）查询时决定表连接的顺序等等。

PS: 有点类似于 JVM 执行 Java 代码时的操作。即，JVM 并非完全按照代码的先后顺序来执行的，它会调整一些代码的执行顺序以提高效率，只是保证最终结果与代码顺序执行的效果一致。

执行器

主要功能：执行 SQL 语句。

MySQL 知道了我们要做什么，并且进行了优化，接下来就要开始执行了。执行之前，会判断你对该表是否有查询的权限，若有权限则继续执行；否则会返回如下错误（这里以 SELECT 操作为例，其他类似）：

SELECT command denied to user ‘user’@‘localhost’ for table ‘t1’

为什么到这一步才进行权限检查呢？

是因为有时候 SQL 语句要操作的表不只是 SQL 字面上的那些（例如触发器要在执行过程中才能确定），因此权限检查在这里进行。

存储引擎

以上述 SELECT 语句为例，执行步骤如下：

1. 调用 InnoDB 引擎接口取 t1 表的第一行，判断 id 是否为 10，若不是则跳过；否则将这一条记录存在结果集中；

2. 调用存储引擎接口读取“下一行”，判断逻辑同步骤 1，直至读取到表的最后一行；

3. 执行器将上述遍历过程中所有满足条件的记录作为结果集返回给客户端。

MySQL 查看所有存储引擎：

mysql> show engines;
±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+
9 rows in set (0.00 sec)

其中 Support 表示该引擎是否可用（DEFAULT 表示默认值）；Comment 是描述信息；Transactions 表示是否支持事务；XA 表示是否支持分布式事务；Savepoints 表示是否支持回滚。

其中最为常用的就是 InnoDB 引擎，而且它也是默认的。其他常见的还有 MyISAM 和 Memory 引擎，这三种引擎简单对比如下：

MyISAMInnoDBMemory存储限制256TB64TB有事务❌✅❌索引✅✅✅锁表锁行级锁表锁外键❌✅❌

小结

MySQL 的整体架构主要分为两部分：Server 层和存储引擎层。

Server 主要有连接器、查询缓存、分析器、优化器和执行器等，包括了 MySQL 的大部分核心功能以及所有内置函数，所有跨存储引擎的功能都在这一层实现，例如存储过程、触发器、视图等。

存储引擎层：负责数据的存储和提取，以插件形式存在，例如 InnoDB (MySQL 5.5.5 以后默认的存储引擎)、MyISAM、Memory 等多种存储引擎。

PS: 发现一个现象，许多东西在学的时候觉得不难，但是如果自己不去做笔记的话就很容易忘记。因此就需要用自己的语言去表达出来，这样更能促进思考，也能进一步“内化”到自己的知识体系中。

索引

简单来说，索引的出现是为了提高查询效率，就像书的目录一样。MySQL 的索引是在「存储引擎」层实现的，因此没有统一的标准，同一种类型的索引，在不同存储引擎之间实现可能也不同。本文主要分析 InnoDB 存储引擎的索引结构。

索引模型

索引模型就是索引的实现形式（也可以理解为索引的数据结构），常见的索引模型有下面三种：

1. 哈希表（散列表）

键值对形式（类似 Java 中的 HashMap）

优点：新增速度快；

缺点：无序，区间查询速度很慢（全表扫描）。

适用场景：只有等值查询的情况（例如 Memcached 等一些 NoSQL 引擎）。

2. 有序数组

优点：等值查询和范围查询速度都很快。

缺点：更新成本太高（插入的记录在中间时，需要移动后面的所有记录，可类比在数组中间位置插入元素的操作）。

适用场景：静态存储引擎（比如不再修改的历史数据）。

3. 搜索树（N 叉树）

优点：读写快，适配磁盘的访问模式。

B+ 树就是其中的一种，也是 InnoDB 存储引擎的索引模型。

InnoDB 记录的存储结构

数据页

在 InnoDB 引擎中，会将数据划分为若干个「页」，「页」是磁盘和内存之间交互的基本单位，页的大小一般为 16KB。即：一般情况下，一次最少从磁盘中读取 16KB 的数据到内存中，一次至少把内存中 16KB 的数据刷新到磁盘中。

向一个数据页中插入记录的过程如图所示：

数据页中分为几个部分，其中 User Records 部分为存储记录的空间（其他部分存储数据页的其他信息，这里暂不详述），插入过程大致如下：

1. 未插入记录时，User Records 部分不存在；

2. 当插入记录时，会从 Free Space 部分划分出空间存储记录；

3. 当 Free Space 空间用完时，也就是该数据页的空间用完了，需要分配新的数据页存储（页分裂）。

记录的结构

在 InnoDB 引擎中，一条记录的存储结构如图所示：

PS: 其中橙色部分 (c1, c2, c3) 是表中的列，且 c1 为主键，下图亦是如此。

也就是说，数据页中记录的数据，除了一条记录本身，还有变长字段列表、NULL 值列表、记录头信息等其他信息，这样才是在数据页中的一条完整记录。

数据页中多条记录之间的关系示意图：

即，每个页中保存了许多条记录，并且每条记录指向下一条记录（根据主键顺序，类似单链表结构）。此外还记录了该页中的最小和最大记录（也是根据主键顺序）。

不仅如此，这些记录还会几条（1~8）分为一个组，并且把组内最大的主键值提取到一个槽（slot）中，用来实现快速（二分）查找，示意图如下：

页内查找记录

以上面的数据页为例，若要查找主键值为 5 的记录，过程如下（二分查找）：

1. 计算中间槽的位置：(0+4)/2=2，因此查找槽 2，而它对应记录的主键为 8，5<8，重新计算；

2. 重新计算，(0+2)/2=1，查找槽 1，对应记录的主键值为 4，5>4，因此查找的记录在槽 2 中；

3. 遍历槽 2 对应的分组，查找主键为 5 的记录。

因此在一个数据页中查找指定主键值的记录过程大致分为两步：

1. 通过二分查找确定记录所在的槽；

2. 遍历该槽所在组中的各个记录（通过记录的 next_record）。

由于槽内数据很少（不超过 8 条），因此遍历的成本较低。

聚簇索引&二级索引

根据叶子节点的内容，索引类型可分为「聚簇索引」（Clustered Index）和「二级索引」（Secondary Index）。

1. 聚簇索引

在 InnoDB 存储引擎中，聚簇索引也称为「主键索引」，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织（Index Organized Table）表（索引即数据，数据即索引）。一张表只能有一个主键索引。

聚簇索引的示意图如下（该结构就是一棵 B+ 树）：

图中结构分为三层，其中上面的两层（即非叶子节点，页 33、页 30 和页 32）为索引层，保存的是索引信息；第三层（叶子节点）为数据层。在主键索引中，叶子节点保存的是完整的记录（以数据页为单位）。

PS: 存储节点的空间可能是不连续的，但是，同一层的节点是有前后顺序的，它们之间以「双向链表」的形式连接。

在索引树中查找一条记录的大致过程如下（仍以查找主键值为 5 的记录为例）：

1. 先查找根节点，即页 33，页 30 中的主键范围是 [1, 320)，而页 32 中主键大于等于 320，因此定位到 页 30；

2. 再查找页 30，同样的方法定位到页 28；

3. 根据上面「页内查找记录」的方式在页 28 中查找。

2. 二级索引

InnoDB 中，二级索引的叶子节点存储的是主键的值。二级索引也称为「非聚簇索引」、「非主键索引」。一张表可以有多个二级索引。其中，以单列作为二级索引的又称「单列索引」，以多列作为索引的又称「联合索引」或者「组合索引」。

二级索引的示意图如下：

该结构与聚簇索引类似，也是一棵 B+ 树。

与聚簇索引的不同之处主要在于第三层，也就是叶子节点，在二级索引中，叶子节点保存的是主键的值。

二级索引中的查找过程与聚簇索引中查找类似。

不同的是，由于二级索引保存的是索引列和主键列，若查找的数据包含索引和主键之外的内容，则需要先找出主键值，然后再根据主键的值到聚簇索引中查找完整记录，该过程称为「回表」。

值得注意的是，上述查找都是在有索引的情况下进行的，如果没有索引呢？则会进行全表扫描，这样当数据量较大时，效率会非常低。这也是索引出现的主要原因。对于新手小白想更轻松的学好Java提升，Java架构，web开发、大数据，数据分析,人工智能等技术，这里给大家分享系统教学资源，加下我尉（同英）：CGMX9880 内容包括：redis、高并发、多线程、分布式、MySQL、数据库、线程、锁、dubbo、jvm、Java虚拟机、spring、memcached等。

区别与联系（InnoDB 存储引擎）

1. 聚簇索引和二级索引都需要占用磁盘空间，每一个索引都对应一棵索引树；

2. 二者都是 B+ 树结构，数据都存储在叶子节点（非叶子节点不保存数据）；

3. 聚簇索引的叶子节点保存的是完整记录，二级索引保存的是主键的值；

4. 在一张表中，聚簇索引只能有一个，二级索引可以有多个（即多个索引树）。

根据这几点比较也可以发现，索引虽然可以提高查找效率，但也有缺点。如果有多个索引，当修改数据时索引也要同步进行更新，这样会降低操作的效率；而且索引也会占用磁盘空间。因此，索引并非越多越好。

InnoDB 引擎主键选择

在 InnoDB 中，每张表都有个主键（Primary Key），如果在建表时没有显式地定义主键，则 InnoDB 引擎会按照如下方式选择或创建主键：

1. 首先判断表中是否有非空的唯一索引（Unique NOT NULL），若有，则该列即为主键（当表中有多个非空唯一索引时，InnoDB 存储引擎将选择建表时第一个定义的非空唯一索引为主键）；

2. 若不符合上述条件，InnoDB 存储引擎自动创建一个 6 字节大小的隐藏列（row_id）作为主键。

因此，建表时最好显式指定主键。

索引优缺点

主要优缺点如下（可通过上述存储结构分析理解）：

优点

1. 可以提高数据检索效率，降低数据库 IO 成本；

2. 对记录进行排序，降低 CPU 消耗（被索引的列会自动进行排序），可以提高排序和分组查询的效率。

缺点

1. 索引会占用磁盘空间；

2. 降低更新效率（更新操作会同步更新索引）。

索引使用场景

需要创建索引的场景

1. 主键自动建立唯一索引；

2. 频繁作为查询条件的字段应该创建索引；

3. 多表关联查询中，关联字段应该创建索引（ON 两边都要创建）；

4. 查询中排序的字段，应该创建索引；

5. 统计或者分组。

不需要使用索引的场景

1. 表记录太少；

2. 频繁更新；

3. 查询字段使用频率不高。

PS: 这里只是概括了一些常见的优缺点和使用场景，可以根据前面对索引的结构和特点的分析对比理解。

小结

简单来说，索引可以理解为书的目录。

索引的主要作用是为了提高查找效率；但索引也有缺点，并非越多越好，需要根据实际情况决定如何创建合适的索引。
最新BAT java经典必考面试题链接：https://pan.baidu.com/s/1B_Lc1tluda0fbdrFnexOMQ
提取码：gw8d