第四章 逻辑架构（1）https://developer.aliyun.com/article/1530666
2.2 MySQL8中SQL执行原理
不同的DBMS的SQL的执行原理是想通的
- 确认profiling 是否开启
查看是否开启计划,开启他可以让mysql收集在SQL执行时所使用的的资源情况
mysql> select @@profiling; mysql> show variables like 'profiling';
profiling=0 代表关闭，我们需要把 profiling 打开，即设置为 1：
mysql> set profiling=1;
- 多次执行相同SQL查询
然后我们执行一个 SQL 查询（你可以执行任何一个 SQL 查询）
mysql> select * from employees;
- 查看profiles
查看当前会话所产生的所有 profiles
mysql> show profiles; # 显示最近的几次查询
- 查看profile
显示执行计划，查看程序的执行步骤：
当然你也可以查询指定的 Query ID，比如：
查询 SQL 的执行时间结果和上面是一样的。
此外，还可以查询更丰富的内容：
继续：
mysql> show profile;
当然你也可以查询指定的 Query ID，比如：
mysql> show profile for query 7
查询 SQL 的执行时间结果和上面是一样的。
此外，还可以查询更丰富的内容
mysql> show profile cpu,block io for query 6;
2.3 MySQL5.7中SQL执行原理
上述操作在MySQL5.7中测试，发现前后两次相同的sql语句，执行的查询过程仍然是相同的。不是会使用
缓存吗？这里我们需要 显式开启查询缓存模式 。在MySQL5.7中如下设置：
- 配置文件中开启查询缓存
在 /etc/my.cnf 中新增一行：
query_cache_type=1
- 重启mysql服务
systemctl restart mysqld
- 开启查询执行计划
由于重启过服务，需要重新执行如下指令，开启profiling
mysql> set profiling=1
- 执行语句两次
mysql> select * from locations; mysql> select * from locations;
- 查看profiles
- 查看profile
显示执行计划，查看程序的执行步骤：
mysql> show profile for query 1;
mysql> show profile for query 2;
结论不言而喻。执行编号2时，比执行编号1时少了很多信息，从截图中可以看出查询语句直接从缓存中
获取数据。
2.4 SQL语法顺序
随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同
而动态调整执行顺序。
需求：查询每个部门年龄高于20岁的人数且高于20岁人数不能少于2人，显示人数最多的第一名部门信息
下面是经常出现的查询顺序：
3. 数据库缓冲池(buffer pool)
InnoDB 存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页
面（包括读页面、写页面、创建新页面等操作）。而磁盘 I/O 需要消耗的时间很多，而在内存中进行操
作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS 会申请 占用内存来作为
数据缓冲池 ，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的 Buffer Pool 之后才可以访
问。
这样做的好处是可以让磁盘活动最小化，从而 减少与磁盘直接进行 I/O 的时间 。要知道，这种策略对提
升 SQL 语句的查询性能来说至关重要。如果索引的数据在缓冲池里，那么访问的成本就会降低很多。
3.1 缓冲池 vs 查询缓存
缓冲池和查询缓存是一个东西吗？不是。
- 缓冲池（Buffer Pool）
首先我们需要了解在 InnoDB 存储引擎中，缓冲池都包括了哪些。
在 InnoDB 存储引擎中有一部分数据会放到内存中，缓冲池则占了这部分内存的大部分，它用来存储各种
数据的缓存，如下图所示
从图中，你能看到 InnoDB 缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应 Hash 和数据字典
信息等。
缓存池的重要性：
缓存原则：
“ 位置 * 频次 ”这个原则，可以帮我们对 I/O 访问效率进行优化。
首先，位置决定效率，提供缓冲池就是为了在内存中可以直接访问数据。
其次，频次决定优先级顺序。因为缓冲池的大小是有限的，比如磁盘有 200G，但是内存只有 16G，缓冲
池大小只有 1G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会 优先对使用频次高
的热数据进行加载 。
缓冲池的预读特性：
2. 查询缓存
那么什么是查询缓存呢？
查询缓存是提前把 查询结果缓存 起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在
MySQL 中的查询缓存，不是缓存查询计划，而是查询对应的结果。因为命中条件苛刻，而且只要数据表
发生变化，查询缓存就会失效，因此命中率低。
3.2 缓冲池如何读取数据
缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先会判断该页面
是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面存放到缓冲池中再进
行读取。
缓存在数据库中的结构和作用如下图所示：
如果我们执行 SQL 语句的时候更新了缓存池中的数据，那么这些数据会马上同步到磁盘上吗？
3.3 查看/设置缓冲池的大小
如果你使用的是 InnoDB 存储引擎，可以通过查看 innodb_buffer_pool_size 变量来查看缓冲池的大
小。命令如下：
show variables like 'innodb_buffer_pool_size';
你能看到此时 InnoDB 的缓冲池大小只有 134217728/1024/1024=128MB。我们可以修改缓冲池大小，比如
改为256MB，方法如下
set global innodb_buffer_pool_size = 268435456;
或者：
[server] innodb_buffer_pool_size = 268435456
3.4 多个Buffer Pool实例
[server] innodb_buffer_pool_instances = 2
这样就表明我们要创建2个 Buffer Pool 实例。
我们看下如何查看缓冲池的个数，使用命令
show variables like 'innodb_buffer_pool_instances';
那每个 Buffer Pool 实例实际占多少内存空间呢？其实使用这个公式算出来的：
innodb_buffer_pool_size/innodb_buffer_pool_instances
也就是总共的大小除以实例的个数，结果就是每个 Buffer Pool 实例占用的大小。
3.5 引申问题
Buffer Pool是MySQL内存结构中十分核心的一个组成，你可以先把它想象成一个黑盒子。
黑盒下的更新数据流程
我更新到一半突然发生错误了，想要回滚到更新之前的版本，该怎么办？连数据持久化的保证、事务回
滚都做不到还谈什么崩溃恢复？
iables like ‘innodb_buffer_pool_instances’;
[外链图片转存中...(img-Sp3okaTz-1694064293379)] 那每个 Buffer Pool 实例实际占多少内存空间呢？其实使用这个公式算出来的：
innodb_buffer_pool_size/innodb_buffer_pool_instances
也就是总共的大小除以实例的个数，结果就是每个 Buffer Pool 实例占用的大小。 ### 3.5 引申问题 Buffer Pool是MySQL内存结构中十分核心的一个组成，你可以先把它想象成一个黑盒子。 黑盒下的更新数据流程 [外链图片转存中...(img-kWjehRAe-1694064293381)] 我更新到一半突然发生错误了，想要回滚到更新之前的版本，该怎么办？连数据持久化的保证、事务回 滚都做不到还谈什么崩溃恢复？ 答案：Redo Log & Undo Log
