目录

  • 一条查询语句的执行流程
  • 1. 连接
  • 2. 查询缓存
  • 3. 语法解析和预处理
  • 4.查询优化和查询执行计划
  • 5.存储引擎
  • MyISAM
  • InnoDB
  • 一条更新语句是如何执行的
  • 执行流程简述
  • 缓冲池 Buffer Pool
  • redo log
  • undo log

mysql官方文档 https://dev.mysql.com/doc/refman/

一条查询语句的执行流程

mysql server 线程结构 mysql线程数_mysql server 线程结构

1. 连接

Mysql服务监听的端口默认为3306，有专门负责处理连接的模块，连接是需要权限验证。

如何查看mysql的连接数？

show global status 'Thread%';

mysql server 线程结构 mysql线程数_存储引擎_02

字段

含义

Threads_cached

缓存中的线程连接数

Threads_connected

当前打开的连接数

Threads_created

为处理连接创建的线程数

Threads_running

非睡眠状态的连接数，通常指并发连接数

为何查看mysl的连接数是“show Thread”查看线程数呢？

因为客户端每产生一个连接或一个会话，在服务器端就会创建一个线程来处理。反过来，如果要结束会话，就需要杀死进程。

每一个连接都分配线程的话，毋庸置疑是需要消耗服务端资源的，所以在连接时长和连接数（并发量）上mysql就做了些处理。

  1. 连接时长：mysql会把长时间不活动（sleep）的连接自动断开。
show variables like 'wait_timeout';  --非交互式超时时间，如JDBC程序

mysql server 线程结构 mysql线程数_mysql server 线程结构_03

show variables like 'max_connections';   --交互式超时时间，如数据库工具

mysql server 线程结构 mysql线程数_mysql_04

交互式和非交互式的默认连接超时时长都是28800秒（8小时）。

  1. 连接数：mysql服务允许最大的连接数（并发数）是多少？
show variables like 'max_connections';

下图中的最大连接数为200（这里是我自己做了修改），在mysql5.7和目前的mysql8.0的版本中，mysql的默认最大连接数为151，最大可支持设置成100000（10w）

mysql server 线程结构 mysql线程数_存储引擎_05

mysql8.0官网关于max_connections的描述

mysql server 线程结构 mysql线程数_数据_06

2. 查询缓存

mysql中查询缓存默认为关闭状态（不推荐使用），且mysql8.0中已经将查询缓存移除了。需要缓存还是交给ORM(如：mybatis默认开启一级缓存)框架或redis等第三方服务来实现。

show variables like 'query_cache%';

3. 语法解析和预处理

主要是对sql语句基于SQL语法进行词法分析和语法分析以及语义解析。

3.1 词法分析：就是把一条sql语句分成一个个单词。
如

select * from student where student = '1';

会分成select、*、from、student、where、student、=、'1'八个单词，每个单词从哪开始从哪结束，是什么类型。

3.2 语法分析
及对SQL做一些语法检查，比如单引号是否闭合、识别关键字等，然后根据SQL语法规则，生成解析树（select_lex）。

3.3 预处理器
在语法分析的基础上（解决语法分析无法解析的语义），对表名、列名是否存在、别名是否异常等问题进行解析处理，进一步生成一个新的解析树。

4.查询优化和查询执行计划

4.1 查询优化器
查询优化器的目的就是根据解析树生成不同的执行计划（Execution Plan），然后选择一种最优的执行计划，MySQL里面使用的是基于开销（cost）的优化器，哪种计划开销最小，就用哪种。
查看查询的开销：

show status like 'Last_query_cost';

4.2 优化器都做哪些优化？
如：
两表关联查询时，以哪个表为基准表；
多个索引可以使用时，使用哪个索引等等。

以下来自《数据库查询优化器艺术-原理解析与SQL性能优化》
4.2.1 子查询优化
4.2.2 等价谓词重写
4.2.3 条件简化
4.2.4 外连接消除
4.2.5 嵌套连接消除
4.2.6 连接的消除
4.2.7 语义优化
4.2.8 非SPJ优化

优化完之后，优化器会把解析树变成一个查询执行计划，查询执行计划是一个数据结构。
可以通过在sql语句前加上explain来查看执行计划的信息
如：

EXPLAIN select name from student where id = 1;

获取详细信息：

EXPLAIN FORMAT=JSON select name from student where id = 1;

5.存储引擎

mysql支持多种存储引擎，常用的有MyISAM和InnoDB，5.5.5之前mysql默认的存储引擎为MyISAM,5.5.5之后mysql默认的存储引擎为InnoDB。

常见的存储引擎
5.7 https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html 8.0 https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html

MyISAM
  • 通常用于只读或以读为主的工作，表级锁定限制了读写性能。
    特点：
  • 支持表级别的锁（插入和更新会锁表）。不支持事物。
  • 拥有较高的插入（insert）和查询（select）速度。
  • 存储了表的行数（count速度更快）。

tips: 怎么快速向数据库插入100w条数据？

  • 可以先用MyISAM插入数据，然后修改存储引擎为InnoDB。
InnoDB

5.7、8.0版本中默认的存储引擎，适合经常更新的表，存在并发读写或者有事务处理的业务系统

  • 支持事务，支持外键，因此数据的完整性，一致性更高
  • 支持行级别的锁和表级别的锁
  • 支持读写并发，写不阻塞读（MVCC）。啥是MVCC?以后再说。
  • 特殊的索引存放方式，可以减少IO，提升查询效率

mysql server 线程结构 mysql线程数_mysql_07

一条更新语句是如何执行的

执行流程简述

一个简化后的过程（因为更新操作涉及到事务，这里先记一个大概的流程示例）

要将student表中id=1的学生姓名（原为lisi）修改为zhangsan，执行sql语句

update student set name='zhangsan' where id=1；

  1. 事务开始，从内存（buffer pool）或磁盘取到包含这条数据的数据页，返回给Server的执行器;
  2. 执行器修改数据页的一行数据;
  3. 记录修改之前的内容到undo log,如update student set name='lisi' where id=1；;
  4. 记录要修改的操作到redo log，如update student set name='zhangsan' where id=1；
  5. 调用存储引擎接口，记录数据页到buffer pool
  6. 事务提交。

缓冲池 Buffer Pool

InnoDB设定了一个存储引擎从磁盘读取数据到内存的最小单位，叫做页。

操作系统也有页的概念。操作系统的页大小一般是4k（传闻中的4k对齐），在InnoDB中，这个最小的单位默认是16KB大小。若需要修改这个值的话，修改后需要清空数据重新初始化服务。

也就是说InnoDB存储引擎从磁盘读取数据的时候，每次最少读16KB的数据，我们所需要的操作的数据就在这样的页里面，也就是常常说的数据页。

而我们每次拿数据如果都从磁盘中取出来放入内存的话，还是避免不了频繁io消耗资源的问题，这里就还是需要一个缓存的思想，把读取过的数据页缓存起来。

InnoDB设计了一个内存的缓冲区。读取数据的是会，先判断缓冲区内是否存在，若存在则直接取用，不存在则从磁盘读取后将数据放入这个内存的缓冲区内。这个缓冲区就叫做Buffer Pool。

修改数据时，也是先写到buffer pool，而不是直接写到磁盘。内存的数据页和磁盘数据不一致的时候，我们把内存取的这部分数据叫做脏页。InnoDB中有专门的后台线程把buffer pool的数据写到磁盘，每隔一段时间就会一次性的把多个修改写入（同步）的磁盘，这个动作就叫做刷脏。

有次可见，Buffer Pool的作用就是为了提高读写的效率。

redo log

因为刷脏不是实时的，如果Buffer Pool里面的脏页没有同步到磁盘时，服务器或者数据库宕机或者重启，这些数据就会丢失。如何避免这部分数据的丢失，实现内存内数据的持久化呢？

InnoDB把所有对“页”的修改操作写入到一个操作日志文件中。如果脏页中的内容没有同步到磁盘时，数据库再启动的时候，会从这个日志文件进行恢复操作（实现crash-safe)。我们说的事务的ACID中的D（持久性），就是用它来实现的。

这个日志文件就叫做redo log（重做日志）。

mysql server 线程结构 mysql线程数_存储引擎_08

  • 既然都要写磁盘，为何不直接写到DBFile里面，还要先写日志再写磁盘呢？
  • 这个与顺序io和随机io有关
  • 如果需要的数据是随机分散在磁盘的不同页的不同扇区中的，那么找到相应的数据需要等磁臂旋转到指定的页，然后盘片寻找到对应的扇区（寻址的过程），才能找到所需要的的一块数据，依次进行此过程（不断地重新寻址）直到找完所有数据，这个就是随机IO。
  • 顺序IO是指读写操作的访问地址连续。如盘片已经找到了第一块数据所在的扇区（寻址成功）后，并且其他所需的数据就在这一块数据后边，那么就不需要重新寻址，可以依次拿到所需的数据，这个就叫顺序IO。
  • 直接写数据文件（写数据（写聚簇索引）、写索引（普通索引））是随机I/O，而记录日志是顺序I/O（不断地追加），因此先把修改写入日志文件，在保证了内存数据安全性的情况下，可以延迟刷盘时机，进而提升系统吞吐量。

mysql server 线程结构 mysql线程数_mysql server 线程结构_09

  • redo log特点
  • 为InnoDB提供了崩溃恢复的特性，实现持久性
  • redo log的大小是固定的，前面的内容会被覆盖，一旦写满，就会触发buffer pool到磁盘的同步，以便腾出空间记录后面的修改。
  • 默认有两个文件ib_logfile0和ib_logfile1，每个48m。

可以通过以下命令查看InnoDB中redo log的相关参数：

show variables like 'innodb_log%';

mysql server 线程结构 mysql线程数_存储引擎_10

参数

含义

innodb_log_size

每个文件的大小，默认48M

innodb_log_files_in_group

文件的数量，默认为2个

innodb_log_group_home_dir

文件所在路径，如果不指定，则为datadir的路径

除了redo log外，还有一个跟修改相关的日志，叫做undo log。redo log和undo log与实务密切相关，统称为事务日志。

undo log

undo log（撤销日志或回滚日志）记录了实务发生之前的数据状态，分为insert undo log和update updo log。如果修改数据时出现异常，可以用undo log来实现回滚操作（保持原子性）。

show variables like '%undo%';

mysql server 线程结构 mysql线程数_mysql_11

参数

含义

innodb_undo_directory

uodo文件的路径

innodb_undo_log_truncate

是否开启在线回收undo log日志文件

innodb_max_undo_log_size

undo文件的大小。如果开启了innodb_undo_log_truncate，超过这个大小的时候就会触发truncate回收动作，如果page大小是16kb，truncate后空间缩小到10M。默认1073741824字节=1G。

innoidb_undo_tablespaces

设置undo独立表空间个数，范围为0-95，默认为0。0表示不开启独立undo表空间，且undo日志存储在ibdata文件中。

innodb_undo_log_encrypt