MySQL之SQL的执行流程
MySQL之SQL的执行流程
MySQL架构
从整体上，可以把MySQL分成三层：
客户端对接的连接层
真正执行操作的服务层
跟硬件打交道的存储引擎层
连接层
客户端要连接MySQL服务器3306端口，必须跟服务端建立连接。
管理所有的连接，验证客户端的身份和权限，这些功能在连接层完成。
服务层
连接层会把SQL语句交给服务层，这里面又包含一系列的流程：
查询缓存的判断
根据SQL调用相应的接口
对SQL语句进行词法和语法的解析
然后就是优化器，MySQL底层会根据一定的规侧对SQL语句进行优化，最后再交给执行器去执行。
存储引擎
存储引擎就是数据真正存放的地方，在MySQL里面支持不同的存储引擎。再往下就是内存或者磁盘。
连接
MySQL服务监听端口默认是3306
客户端连接服务端：可以是同步、异步的、长连接、短连接、TCP、Unix Socket等方式
MySQL有专门处理连接的模块，连接的时候需要验证权限
查看连接
查看MySQL当前有多少个连接，使用show status命令，模糊匹配Thread：
mysql> show global status like'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 1 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.01 sec)
参数说明：
Threads_cached：缓存中的线程连接数
Threads_connected：当前打开的连接数
Threads_created：为处理连接创建的线程数
Threads_running：非睡眠状态的连接数，通常指并发连接数
连接与线程
客户端连接和服务端线程的关系：
客户端每产生一个连接或者一个会话，在服务端就会创建一个线程来处理
分配线程话，保持连接会消耗服务端的资源。MySQL会把长时间不活动的(SLEEP)连接自动断开
连接超时
连接超时参数：
默认28800 秒，8 小时。
# 非交互式超时时间， 如JDBC 程序
show global variables like'wait _timeout';
# 交互式超时时间， 如数据库工具
show global variables like'interactive_ timeout';
最大连接
MySQL服务允许最大连接数(并发数)，在5.7版本中默认是151个，最大可以设置成100000
mysql> show variables like'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 500 |
+-----------------+-------+
1 row in set (0.01 sec)
会话与全局
MySQL中的参数(变量)分为`session`和`global`级别，分别是在当前会话中生效和全局生效
并不是每个参数都有两个级别，比如max_connections就只有全局级别
当没有带参数的时候， 默认是session级别，包括查询和修改。如修改—个参数以后，在本窗口查询生效， 但其他窗口不生效
因此，如果是临时修改，则修改session级别，如果需要在其他会话中生效，必须显式地加上global参数
查询缓存
MySQL内部自带了一个缓存模块，MySQL缓存默认是关闭状态，即不推荐使用。
mysql> show variables like 'query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 33554432 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
5 rows in set (0.01 sec)
MySQL自带缓存应用场景有限
要求SQL语句必须一模一样，中间多一个空格，字母大小写不同都被认为是不同的的SQL
表里面任何一条数据发生变化的时候，这张表所有缓存都会失效
在MySQL8.0中，查询缓存已经被移除
通常缓存这一块还是交由ORM框架(如yBatis默认开启一级缓存)、或使用独立缓存服务
语法解析和预处理
语法解析使用
Parser解析器模块，预处理使用Preprocessor预处理模块
语法解析和预处理就是：对语句基于SQL语法进行词法和语法分析和语义的解析。
词法解析
词法解析就是把一个完整的SQL语句打碎成一个个的单词，记录每个单词/符号是什么类型，从哪里开始到哪里结束。
语法解析
语法解析会对SQL做一些语法检查，如单引号有没有闭合，然后根据MySQL定义的语法规则，根据SQL语句生成一个数据结构。这个数据结构叫做解析树
预处理
预处理会检查生成的解析树，解决解析器无法解析的语义。如检查表和列名是否存在，检查名字和别名，保证没有歧义。预处理之后得到一个新的解析树。
查询优化器
优化器
查询优化器使用MySQL的查询优化器的模块(Optimizer)
一条SQL语句是可以有很多种执行方式的，最终返回相同的结果，他们是等价的。
查询优化器的目的就是根据解析树生成不同的执行计划(Execution Plan)，然后选择一种最优的执行计划，MySQL里面使用的是基于开销(coSt)的优化器，那种执行计划开销最小，就用哪种。
查看查询开销：
mysql> show status like 'Last_query_cost';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 0.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
查询执行计划
经过优化器优化完之后，优化器最终会把解析树变成一个查询执行计划，查询执丸行计划是一个数据结构。
M小ySQL提供一个执行计划的工具。在SQL语句前面加上EXPLAIN，就可以看到执行计划的信息。
mysql> EXPLAIN select * from user where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
如果需要得到更详细的信息，可以使用：FORMAT=JSON
mysql> EXPLAIN FORMAT=JSON select * from user where id=1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.00"
},
"table": {
"table_name": "user",
"access_type": "const",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.20",
"prefix_cost": "0.00",
"data_read_per_join": "776"
},
"used_columns": [
"id",
"name",
"age"
]
}
}
} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
存储引擎
存储引擎概述
默认情况下，每个数据库有一个自己文件夹。创建表后，在该文件夹下会产生与表名相关的文件。任何一个存储引擎都有一个fm文件，这个是表结构定义文件。不同的存储引擎存放数据的方式不一样，产生的文件也不一样。
innodb存储引擎是1个
memory存储引擎没有
myisam存储引擎是2个
查看数据库数据存放路径
mysql> show variables like 'datadir';
+---------------+-------------------+
| Variable_name | Value |
+---------------+-------------------+
| datadir | /www/server/data/ |
+---------------+-------------------+
1 row in set (0.01 sec)
创建数据库与不同存储引擎的数据表
CREATE DATABASE mydb;
CREATE TABLE `user-InnoDB` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `user-MyISAM` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `user-MEMORY` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8
查看验证
[root@administrator mydb]# pwd
/www/server/data/mydb
[root@administrator mydb]# ls
db.opt user@002dInnoDB.frm user@002dInnoDB.ibd user@002dMEMORY.frm user@002dMyISAM.frm user@002dMyISAM.MYD user@002dMyISAM.MYI
常用存储引擎
MyISAM
MyISAM应用范围比较小。表级锁定限制了读/写的性能，通常用于只读或以读为主的工作。
特点：
支持表级别的锁（插入和更新会锁表）
不支持事务
拥有较高的插入(insert)和查询(select)速度
存储了表的行数(count速度更快
InnoDB
InnoDB是mysq5.7中的默认存储引擎。
InnoDB是一个事务安全的MySQL存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。
InnoDB使用行级锁，提高多用户并发性和性能。
InnoDB将用户数据存储在聚集索引中，以减少基于主键的常见查询的I/O。
为了保持数据完整性，InnoDB还支持外键引用完整性约束
适合经常更新的表，存在并发读写或者有事务处理的业务系统
特点：
支持事务，支持外键，因此数据的完整性、一致性更高
支持行级别的锁和表级别的锁
支持读写并发，写不阻塞读
特殊的索引存放方式，可以减少O,提升查询效率
MEMORY
MEMORY将所有数据存储在RAM中，以便在需要快速查找非关键数据的环境中快速访问。
InnoDB及其缓冲池内存区域提供了一种通用、持久的方法来将大部分或所有数据保存在内存中，而ndbcluster为大型分布式数据集提供了快速的键值查找。
特点：
把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失。只适合做临时表。
将表中的数据存储到内存中
存储引擎的选择
MySQL支持多种存储引擎，主要使用三种：
InnoDB、AyISAM、MEMORY
具体参考：https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html
一张表的存储引擎，是在创建表的时候指定的，使用ENGINE关键字，如：ENGINE=InnoDB。没有指定的时候，数据库使用默认的存储引擎
5.5.5之前，默认的存储引擎是AyISAM
5.5.5之后，默认的存储擎是InnoDB
MySQL之所以支持这么多的存储引擎，就是因为有不同的业务需求，一种存储引擎不能提供所有的特性。
如果对数据致性要求比较高，需要事务支持，可以选择InnoDB
如果数据查询多更新少，对查询性能要求比较高，可以选择MyISAM
如果需要一个用于查询的临时表，可以选择MEMORY
当所有存储引擎不满足需求，可自定义存储引擎：https://dev.mysql.com/doc/internals/en/custom-engine.html
注意：每个存储引擎都有自己对应的服务
show engine innodb status;
执行引擎
执行引擎是利用存储引擎提供的相应的AP来完成操作，完成后把数据返回给客户端。
即使修改表的存储引擎，由于不同功能的存储引擎实现的API是相同的，因此操作方式也不需要做任何改变
更新SQL的执行流程
上述一系列过程就是一条查询SQL会经历的流程。
更新流程和查询流程的基本流程是一致的，它也要经过解析器、优化器的处理，最后交给执行器。区别在于拿到符合条件的数据之后的操作。
缓存池
缓存池即
Buffer Pool，作用是为了提高读写的效率
页
对于InnoDB存储引擎，数据是放在磁盘上的，存储引擎要操作数据，必须先把磁盘里面的数据加载到内存里面才可以操作。
在操作系统、存储引擎有一个预读取的概念：
当磁盘上的一块数据被读取的时候，很有可能它附近的位置也会马上被读取到，这个就叫做局部性原理。
InnoDB设定了一个存储引擎从磁盘读取数据到内存的最小的单位，叫做页。
操作系统也有页的概念。操作系统的页大小一般是4K。
在InnoDB里面，这个最小的单位默认是16KB大小。如果要修改这个值的大小，需要清空数据重新初始化服务
要操作的数据就在这样的页里面，数据所在的页叫数据页
Buffer Pool
InnoDB设计了一个内存的缓冲区。读取数据的时候，先判断是不是在这个内存区域里面，如果是，就直接读取，然后操作，不用再次从磁盘加载。如果不是，读取后就写到这个内存的缓冲区。
这个内存区域有个专属的名字，叫Buffer Pool。
修改数据时，是先写入到buffer pool，而不是直接写到磁盘。
当内存的数据页和磁盘数据不一致时，把它叫做脏页。
脏页同步磁盘
InnoDB有专门的后台线程把Buffer Pool的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。
Redo log
概述
由于刷脏不是实时的，如果Buffer Pool里面的脏页还没有刷入磁盘时，数据库宕机或者重启，这些数据就会丢失。所以内存的数据必须要有一个持久化的措施。
为了避免这个问题，InnoDB把所有对页面的修改操作专门写入一个日志文件。如果有未同步到磁盘的数据，数据库在启动的时候，会从这个日志文件进行恢复操作（实现crash-safe)。平时说的事务的ACID里面D(持久性），就是用它来实现的。
这个日志文件就是磁盘的
redo log，也叫做重做日志
特点
redo log是InnoDB存储引擎实现的，并不是所有存储引擎都有。支持崩溃恢复是InnoDB的一个特性。
redo log不是记录数据页更新之后的状态，而是记录的是“在某个数据页上做了什么修改”。属于物理日志。
redo log的大小是固定的，前面的内容会被覆盖，一旦写满，就会触发buffer pool到磁盘的同步，以便腾出空间记录后面的修改。
除了redo log之外，还有一个跟修改有关的日志，叫做undo log。redo log和undo log与事务密切相关，统称为事务日志。
redo log位置
redo log文件通常位于
var/lib/mysql/目录下的ib_logfile0和ib_logfile1，默认2个文件，每个48M。
查询redo log位置：find / -name '*_logfile*'
[root@administrator mysql]# find / -name '*_logfile*'
/www/server/data/ib_logfile1
/www/server/data/ib_logfile0
查询innodb_log相关变量
mysql> show variables like 'innodb_log%';
+-----------------------------+------------------+
| Variable_name | Value |
+-----------------------------+------------------+
| innodb_log_buffer_size | 33554432 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 134217728 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | /www/server/data |
| innodb_log_write_ahead_size | 8192 |
+-----------------------------+------------------+
7 rows in set (0.00 sec)
redo相关的参数说明：
innodb_log_file_size：指定每个文件的大小，默认48M
innodb_log_files_in_group：指定文件的数量，默认为2
innodb_log_group_home_dir：指定文件所在路径，相对或绝对。如果不指定，则为datadir路径
undo log
概述
undo log(澈销日志或回滚日志)记录了事务发生之前的数据状态，分为insert undo log和update undo log。
如果修改数据时出现异常，可以用undo log来实现回滚操作(保持原子性)。
可以理解为undo log记录的是反向的操作，比如insert会记录delete,update会记录update原来的值，跟redolog记录在哪个物理页面做了什么操作不同，所以叫做逻辑格式的日志。
参数配置
redo Log和undo Log与事务密切相关，统称为事务日志。
mysql> show global variables like '%undo%';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory | ./ |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
+--------------------------+------------+
5 rows in set (0.00 sec)
innodb_max_undo_log_size：如果innodb_undo_log_truncate设置为true，超过这个大小的时候会触发truncate回收（收缩）动作，如果page大小是16KB,truncate后空间缩小到10M。默认1073741824字节=1G。
innodb_undo_directory：undo文件的路径
innodb_undo_log_truncate：设置为true，即开启在线回收（收缩）undo log日志文件
innodb_undo_logs ：回滚段的数量，默认128，这个参数已经过时。
innodb_undo_tablespaces：设置undo独立表空间个数，范围为0-95，默认为0，0表示不开启独立undo表空间且undo日志存储在ibdata文件中。
更新过程
假设执行如下SQL：
select * from user where id=1;
id name
1 mysql
update user set name='update' where id=1;
则整个SQL更新过程如下：
事务开始，从内存(buffer pool)或磁盘(data file)取到包含这条数据的数据页，返回给Server的执行器
Server的执行器修改数据页的这一行数据的值为update
记录name=mysql到undo log
记录name=update到redo log
调用存储引擎接口，记录数据页到Buffer Pool(修改name=update)
事务提交