这是我参与「第五届青训营 」伴学笔记创作活动的第 8 天
MySQL 一条语句的生命过程
-
诞生
-
生成方式
- 开发者编写
- ORM框架自动生成,如Java中的MyBatis、Hibernate框架等
- 生成时机——与用户请求有关
-
-
执行前
-
数据库连接池(如如Java中的C3P0、Druid、DBCP)——将数据库连接这种较为珍贵的资源，利用池化技术对这种资源进行维护
- MySQL连接池维护的是工作线程
- 客户端连接池则维护的是网络连接
-
-
执行
-
查询select
-
步骤
- ①先将SQL发送给SQL接口，SQL接口会对SQL语句进行哈希处理。
- ②SQL接口在缓存中根据哈希值检索数据，如果缓存中有则直接返回数据。
-
③缓存中未命中时会将SQL交给解析器，解析器会判断SQL语句是否正确：
- 错误：抛出1064错误码及相关的语法错误信息。
- 正确：将SQL语句交给优化器处理，进入第④步。
- ④优化器根据SQL制定出不同的执行方案，并择选出最优的执行计划。
- ⑤工作线程根据执行计划，调用存储引擎所提供的API获取数据。
- ⑥存储引擎根据API调用方的操作，去磁盘中检索数据（索引、表数据....）。
- ⑦发生磁盘IO后，对于磁盘中符合要求的数据逐条返回给SQL接口。
- ⑧SQL接口会对所有的结果集进行处理（剔除列、合并数据....）并返回。
-
优化器 一些优化准则
- ❶多条件查询时，重排条件先后顺序，将效率更好的字段条件放在前面。
- ❷当表中存在多个索引时，选择效率最高的索引作为本次查询的目标索引。
- ❸使用分页Limit关键字时，查询到对应的数据条数后终止扫表。
- ❹多表join联查时，对查询表的顺序重新定义，同样以效率为准。
-
❺对于SQL中使用函数时，如count()、max()、min()...，根据情况选择最优方案。
- max()函数：走B+树最右侧的节点查询（大的在右，小的在左）。
- min()函数：走B+树最左侧的节点查询。
- count()函数：如果是MyISAM引擎，直接获取引擎统计的总行数。
- ......
- ❻对于group by分组排序，会先查询所有数据后再统一排序，而不是一开始就排序。
- ❼......
-
-
写入
-
步骤
- ①先将SQL发送给SQL接口，SQL接口会对SQL语句进行哈希处理。
- ②在缓存中根据哈希值检索数据，如果缓存中有则将对应表的所有缓存全部删除。//确保缓存的强一致性
-
③经过缓存后会将SQL交给解析器，解析器会判断SQL语句是否正确：
- 错误：抛出1064错误码及相关的语法错误信息。
- 正确：将SQL语句交给优化器处理，进入第④步。
- ④优化器根据SQL制定出不同的执行方案，并择选出最优的执行计划。
- ⑤在执行开始之前，先记录一下undo-log日志和redo-log(prepare状态)日志。
-
⑥在缓冲区中查找是否存在当前要操作的行记录或表数据（内存中）：
-
存在：
- ⑦直接对缓冲区中的数据进行写操作。
- ⑧然后利用Checkpoint机制刷写到磁盘。
-
不存在：
- ⑦根据执行计划，调用存储引擎的API。
- ⑧发生磁盘IO，对磁盘中的数据做写操作。
-
- ⑨写操作完成后，记录bin-log日志，同时将redo-log日志中的记录改为commit状态。
- ⑩将SQL执行耗时及操作成功的结果返回给SQL接口，再由SQL接口返回给客户端。
-
不同之处
- 唯一性判断 主要是针对插入、修改语句来说的，因为如果表中的某个字段建立了唯一约束或唯一索引后，当插入/修改一条数据时，就会先检测一下目前插入/修改的值，是否与表中的唯一字段存在冲突，如果表中已经存在相同的值，则会直接抛出异常，反之会继续执行。
-
缓冲区
- 在真正调用存储引擎的API操作磁盘之前，首先会在「缓冲区」中查找有没有要操作的目标数据/目标表，如果存在则直接对缓冲区中的数据进行操作，然后MySQL会在后台以一种名为Checkpoint的机制，将缓冲区中更新的数据刷回到磁盘。只有当缓冲区没有找到目标数据时，才会去真正调用存储引擎的API，然后发生磁盘IO，去对应磁盘中的表数据进行修改。
- note:虽然缓冲区中有数据时会先操作缓冲区，然后再通过Checkpoint机制刷写磁盘，但这两个过程不是连续的！也就是说，当线程对缓冲区中的数据操作完成后，会直接往下走，数据落盘的工作则会交给后台线程。
-
日志
- 读 只有慢查询
-
所有的写SQL在执行之前都会生成对应的撤销SQL，撤销SQL也就是相反的操作，比如现在执行的是insert语句，那这里就生成对应的delete语句....，然后记录在undo-log撤销/回滚日志中。但除此之外，还会记录redo-log日志。
- redo-log日志是InnoDB引擎专属的，主要是为了保证事务的原子性和持久性，这里会将写SQL的事务过程记录在案，如果服务器或者MySQL宕机，重启时就可以通过redo_log日志恢复更新的数据。在「写SQL」正式执行之前，就会先记录一条prepare状态的日志，表示当前「写SQL」准备执行，然后当执行完成并且事务提交后，这条日志记录的状态才会更改为commit状态。
- 同时还会记录bin-log日志，这个日志和redo-log日志很像，都是记录对数据库发生更改的SQL，只不过redo-log是InnoDB引擎专属的，而bin-log日志则是MySQL自带的日志。
-
先写到缓冲区中，然后再异步刷写到磁盘
-
刷盘策略
-
redo-log日志的刷盘策略由innodb_flush_log_at_trx_commit参数控制
- 0：间隔一段时间，然后再刷写一次日志到磁盘（性能最佳）。
- 1：每次提交事务时，都刷写一次日志到磁盘（性能最差，最安全，默认策略）。
- 2：有事务提交的情况下，每间隔一秒时间刷写一次日志到磁盘。
-
bin-log日志的刷盘策略则可以通过sync_binlog参数控制：
- 0：同上述innodb_flush_log_at_trx_commit参数的2。
- 1：同上述innodb_flush_log_at_trx_commit参数的1，每次提交事务都会刷盘，默认策略。
-
-
-
-
-
返回(SQL接口)
-
读类型
- 逐条返回
-
写类型
- 仅会返回执行状态、受影响的行数以及执行耗时
-
执行结果是如何返回给客户端的
- 由于执行当前SQL的工作线程，本身也维护着一个数据库连接，这个数据库连接实际上也维持着客户端的网络连接
- 当结果集处理好了之后，直接通过Host中记录的地址，将结果集封装成TCP数据报，然后返回即可
- 数据返回给客户端之后，除非客户端主动输入exit等退出连接的命令，否则连接不会立马断开.如果要断开客户端连接时，又会经过TCP四次挥手的过程。
- 不过就算与客户端断开了连接，MySQL中创建的线程并不会销毁，而是会放入到MySQL的连接池中，等待其他客户端复用当前连接。一般情况下，一条线程在八小时内未被复用，才会触发MySQL的销毁工作。
-
