SQL执行顺序
连接
MySQL服务监听的端口默认是3306，客户端连接服务端的方式有很多种。可以是异步，同步，短连接，长连接，可以是TCP也可以是Unix Socket。
如何查看当前MySQL当前有多少个连接？
可以使用show global status ，模糊匹配Thread：
show global status like 'Thread%';
| 字段 | 含义 |
|---|---|
| Threads_cached | 缓存中的线程连接数 |
| Threads_connected | 当前打开的连接数 |
| Threads_created | 为处理连接创建的线程数 |
| Threads_running | 非睡眠状态的连接数，通常指并发连接数 |
为什么查看连接数是查看线程？客户端连接和服务端的线程是什么关系？
客户端每产生一个连接或者一个会话，在服务端就会创建一个线程来处理。想要杀死会话就要Kill一个线程。
mysql 有两个参数来管理不活动的连接。
show global variables like 'wait_timeout'; -- 非交互式超时时间，如JDBC
show global variables like 'interactive_timeout'; -- 交互式超时时间，如数据库工具
默认是28800秒，8个小时。
MySQL默认的连接数（并发数）是多少？
5.7版本中默认是151个，最大值可以设置为十万。
show variables like 'max_connections';
MySQL参数的作用域
有全局(Global)和会话(Session)基本，分别作用于全局和当前会话。并不是所有参数都拥有两种作用域。比如说，max_connections就只有全局级别。
当语句中没有Global时候，默认是Session级别。
比如下面这个只是临时修改，建议修改为session级别。如果需要在其他会话中生效，必须显式加上Global参数。
show variables like 'autocommit';
set autocommit = true;
建立完连接之后该怎么做呢？
查询缓存
mysql的默认缓存是关闭的。
show variables like 'query_cache%';
| Variables | Value |
|---|---|
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
为什么默认关闭呢？
使用场景极其有限，SQL语句必须一模一样，中间不允许多一个空格，而且大小写敏感；表里有任何一条数据的变化产生的时候，这张表的缓存都会失效。对于有大量数据更新的应用，也不适合；
8.0版本中已经移除
语法解析和预处理（Parser & Preprocessor)
为什么一条SQL能被正确识别呢？
这是由Parser解析器和Preprocessor预处理来完成的。这一步主要就是对语句基于SQL语法进行词法和语法分析语义分析。
词法分析
把一个完整的SQL语句打散为一个个单词。
select name from `user` where id = 1;
这段SQL会被打散为8个符号，每个符号的类型，起始的位置。
语法分析
对SQL做一些语法检查，比如单引号是否闭合，再根据MySQ定义的语法规则，根据SQL语句生成一个数据结构。叫做解析树（select_lex)。
词法语法分析-解析树
词法语法分析，是一个非常基础的功能，Java的编译器、百度搜索引擎如果要识别语句，也必须要有词法语法分析的功能。
任何数据库的中间件，要解析SQL完成路由功能，也必须要有词法和语法分析的功能，比如MyCat，Sharding-JDBC（Druid Parser）。在市面上也有很多的开源词法解析工具（LEX，YACC）。
预处理器
问题：如果我写了一个条SQL，但是表名或者字段不存在，会在哪里报错？在数据库的执行层还是解析器？
select xxx from userxxxx;
其实还是在解析器这里报错，解析SQL的环节里面有一个预处理器。
预处理器会检查生成的解析树，解决解析器无法解析的语义。检查表和列名是否存在，检查名字和别名，保证没有歧义。
预处理之后，得到一个新的解析树。
查询优化器（Query Optimizer)与查询执行计划。
思考一下：得到解析树之后，是否就直接执行SQL语句了？一条SQL是否只有一种执行方法？数据库执行的SQL是否就是我们发送的SQL？
答案是否定的，一条SQL可以有很多种执行方式，最终返回相同的结果，他们是等价的。
多种的执行方式，如何得到，如何选择那种进行执行，根据什么标准判断?
这些都是MySQL的查询优化器模块Optimizer去完成的。
查询优化器根据解析树生成不同的执行计划（Execution Plan），然后选择一种最优的执行计划，MySQL里面使用的是基于开销（cost）的优化器，哪一种执行计划的开销最小，就选择哪种。
使用这个命令查看查询的开销：
show status like 'Last_query_cost';
MySQL优化器可以做什么？
1.多表关联查询，以哪个表作为基准表。
2.多个索引时候，使用哪个索引。
优化器也不是万能的，并不是再垃圾的SQL都能够优化，也不是每次都能选择到最优的执行计划。
MySQL提供了一个执行计划工具，只需要在SQL语句前面加上EXPLAN。
EXPLAIN select xxx from xxx where xx =xx;
---------格式化输出
EXPLAIN FORMAT=JSON select xxx from xxx where xx =xx;
存储引擎
顾名思义，存储数据的引擎，存储引擎包含了存储方式、存储结构、检索方式等。
MySQL5.5之后默认的存储引擎是INNODB，除此之外还有 MYISAM、MEMORY等等。
创建表的时候我们可以指定引擎，使用ENGINE关键字。
CREATE TABLE `user_innodb` (
`id` int(11) PRIMARY KEY AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`gender` tinyint(1) DEFAULT NULL,
`phone` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
默认情况下，每个数据库都有自己的文件夹，任何一个存储引擎都有一个frm文件，这个是表结构定义文件。
存储方式
不同的存储引擎产生的文件不一样，INNODB是一个，MEMORY没有，MYISAM是两个。
不同的存储引擎是为了应对不一样的业务场景：
一张表，需要很高的访问速度，而不需要考虑持久化问题，可以选择把数据放在内存。
一张表，是用来做历史数据存档，不需要修改，也不需要索引，需要支持数据压缩等等。
一张表，用于读写并发很多的业务中，是不是需要支持读写不干扰，需要保证较高的数据一致性？
存储引擎的比较
MYISAM(三个文件）
应用范围较小。表级别的锁定限制了读写的性能，在WEB中尝尝用于只读或者以读为主的功能。
特点：
支持表级别锁（插入和更新会锁表）。不支持事务。
拥有较高的插入和查询速度。
存储了表的行数(count速度快）。
（如何快速向数据库插入100W行数据？表的引擎先用MYISAM插入数据，然后修改引擎为INNODB操作）。
场景
只读之类的数据分析。
INNODB
MySQL的默认引擎，INNODB是一个事务安全（acid兼容)的存储引擎，具有提交、回滚、崩溃恢复功能来保护用户数据。INNODB行级别的锁（不升级为更粗粒度的锁）和ORACLE风格一致非锁读提高了多用户并发和性能。INNODB将用户数据存储在聚集索引中，以减少基于主键的IO查询，为了保证数据完整性，INNODB还支持外键引用完整性约束。
特点
支持事务、外键。数据的完整性，一致性高。
支持行级别的锁和表级别的锁。
支持读写并发，写不阻塞读（MVCC)。
特殊的索引存放方式，可以减少IO，提升查询效率。
场景
经常更新，存在并发读写或者有事务处理的业务系统。
MOMERY
所有数据都存放于内存中，提供快速查找（非关键数据）的场景下使用。INNODB及其缓冲内存区域，提供一种通用、持久的方法将大部分数据保存在内存中，ndbcluster为大型分布式系统数据集提供了快速的键值查找。
正在淘汰。
特点：
数据存放于内存中，读写速度很快，但是数据库重启或者崩溃，数据会完全消失。适合做临时表。
场景
不重要的数据存储。
临时表。
CSV
他的表其实是带有逗号分割值的文本文件。允许CSV格式的数据导入或者转存数据。
特点
不允许空行，不支持索引。格式通用，可以直接编辑，适合不同数据库之间导入导出。
ARCHIVE(两个文件)
这些紧凑的未索引的表，用于存储和检索大量很少引用的历史数据、存档和安全审计信息。
特点
不支持索引，不支持update delete。
如何选择存储引擎？
对数据库一致性要求较高，需要事务支持。innodb
查询多更新少，对查询性能要求高。myisam
用于查询的临时表。memory
如果都满足不了，自己用C写一个。
执行引擎（Query Execution Engine），返回结果。
执行引擎负责执行（执行计划）最后返回数据给客户端。
MYSQL架构分层
总体分层：连接池、服务层、存储引擎层
mysql架构.jpg
连接层
客户端需要连接到MYSQL服务器3306端口，必须要跟服务端建立连接，那么管理连接和验证连接都在连接层完成。
服务层
通过连接层获得session，发送sql语句给服务层。
比如查询缓存，根据SQL调用对应接口，词法解析，语法分析，执行计划，索引选择等等。
然后就是优化器，得到执行计划，交给执行器去执行。
存储引擎
真正存储数据的地方。
Buffer pool
MsqlBufferPool.jpg
MySQL的数据库文件都是存储在磁盘的。
写
内存写向磁盘是IO操作，比较耗费时间，MySQL提出来一个叫缓冲区的概念，先写入缓冲区再由缓冲区写入磁盘，这个操作我们称之为刷脏。
读
在操作系统、存储引擎，都有一个预读概念。当磁盘上的一块数据被读取时候，其他附近位置的数据也会马上被读到，这个就叫局部性原理。
INNODB设定了一个存储引擎从磁盘读取数据到内存的最小单位是页，在操作系统中页的大小为4kb，在INNODB里面这个最小的默认单位为16KB。
Redo log
刷脏并不是实时的，如果Buffer Pool的脏页没有刷完，数据库宕机或者停电了，数据会丢失。
这个时候引入了一个 redo log的持久化措施。
Redo Log.jpg
为了避免这个问题，mysql会把所有对页面修改操作专门写入一个日志文件（RedoLog）。
如果有未同步到磁盘的数据，数据库会在启动的时候，从这个日志文件进行恢复操作（实现crash-safe）。我们数据库事务的持久性就是用它来实现。
写日志文件和写到数据文件的区别是什么？
写入速度
硬盘物理上主要是盘片、机械手臂、磁头、和主轴等组成。
磁盘物理结构
在盘片逻辑划分上又分为磁道、扇区，例如下图：
磁道，扇区
磁盘的写入是需要寻址的，磁盘是一个圆，磁头固定旋转后产生磁道，然后每个磁道根据半径的划分的区域就是扇区，磁盘写入的就是需要找到这些扇区然后进行写入。
如果数据是随机散落在不同的扇区，那么需要磁头旋转找到对应的数据页，然后盘片找到对应扇区才能找到一块数据，一次次进行这个过程直到找到所有数据为止。
刷盘是随机IO，而记录日志是顺序IO(连续读写)，顺序IO的效率上更高。本质上就是数据集中处理和分散存储的区别。因此如果写入日志文件能够大大的保证数据的安全性，可以延迟刷盘 ，提高吞吐。
redolog 位于/var/lib/mysql目录下的ib_logfile0和ib_logfile1 默认两个文件每个48M。
show global variables like 'innodb_log%';
特点
redo log是INNODB存储引擎的实现，支持崩溃恢复的INNODB的一个特性。
redo log不是记录数据页更新之后的状态，而是记录”在某项数据页上做了什么修改“。属于物理日志
redo log的大小是固定的，前面的内容会被覆盖，一旦写满会触发buffer pool到磁盘同步，以便腾出空间记录后面的修改。
Undo Log
Undo log（撤销或者回滚日志）记录了事务发生之前的数据状态，分别为 inser undo log和update undo log。如果修改数据发生异常，可以使用undo log来实现回滚。（保证原子性）
可以理解undo log记录的是反向操作，比如insert会记录delete，update 会记录update 原来的值，跟redolog记录在哪个物理页面操作不一样，所以叫做逻辑日志。
show global variables like '%undo%';
一条更新语句的过程
update user set name = 'dia' where id = 1;
1.事务开启，从(buffer pool)或者磁盘读取（datafile）包含这条数据的页，返回给Server的执行器。
2.Server的执行器修改数据页的这一行数据为 dia。
3.记录name = 旧值 到undolog。
4.记录name = 新值 到redolog。
5.调用存储引擎接口，记录数据页到buffer pool（修改name=dia）。
6.事务提交。
INNODB架构图
image.png
内存结构
Buffer Pool主要分为三个部分：Buffer Pool、Change Buffer、Adaptive HashIndex，另外还有一个（redo）log buffer。
Buffer Pool
Buffer Pool缓存的是页面信息，包括数据页，索引页。
Buffer Pool默认大小是128m。（可以调整）
show global variables like '%innodb_buffer_pool_size%';
这个是会问到Buffer Pool写满了怎么办（Rdis设置的内存满了怎么办？）LRU
算法来管理缓冲池（链表实现，不是传统的LRU，分成了young和old）经过淘汰的数据就是热点数据。
LRU
传统LRU，可以用Map+链表实现。value存的是链表中的地址。
mysql double link.jpg
mysql innodb使用双向链表，LRU list。但是这个LRUlist 不是存放datapage，而是指向缓存页面的指针。
如果buffer pool的时候发现没有空闲页了，就要从buffer pool中淘汰数据页了。
为什么这么设计？
因为innodb的预读机制，数据页并不是在被访问的时候才缓存到buffer pool。设计者认为，访问某个page页的数据时候，相邻的page 可能也会很快被访问到，所以先把这些page先缓存起来。
缓存机制又分为两种类型
一种叫线性预读（异步）（Linear read-ahead)。
innodb 把64个相邻的page叫做一个extent区，如果顺序访问了一个extent的56个page，这个时候innodb就会把下一个extent区缓存到buffer pool中。顺序访问了多少个page 才缓存下一个extent，由一个参数控制：
show variables like 'innodb_read_ahead_threshold';
一种叫随机预读（Random read-ahead）
如果buffer pool，已经缓存了同一个extent区的数据页个数超过13时候，就会把这个extent剩余的所有page 全部缓存到buffer pool。
但是随机预读功能是不开启的，由一个参数控制
show variables liek 'innodb_random_read_ahead';
线性预读可以提高IO性能，但是也会带来占用空间多的副作用。
如果buffer pool size 不是很大，而且预读的数量很多，很可能那些真正被需要缓存的数据被预读数据挤出buffer pool。
这个问题mysql 通过冷热数据区来解决。
image.png
所有数据加入到buffer pool 的时候，一律放在冷区head，不管是预读还是普通读操作。所以如果预读数据没有被读取，会在old sublist冷区直接淘汰。
放入LRU List以后，如果再次被访问，都会把它移动到热区的Head。
如果热区的数据没有被访问，会被移动冷区head，然后慢慢被淘汰。
热区5/8，冷区3/8，这个值由innodb_old_blocks_pct控制。
它代表的是old区的大小，默认是37%。如果这个值太小，old区没有被访问的数据淘汰会更快。
这样是否没有问题了？
如果在同一时间很多冷区数据被访问，会导致大量的数据都移动到了热区，有可能会导致大量的热区数据失效。这个问题怎么解决？
加大加入冷区后的访问间隔，INNODB_OLD_BLOCKS_TIME（默认1s)这个参数来控制，加入冷区多少S后被访问才放入热区数据。
Change buffer
Change Buffer 是Buffer pool的一部分。
如果这个数据页不是唯一索引，不存在数据重复的情况，也就不需要从磁盘判断数据是否唯一（唯一性检查）。这种情况可以先把修改记录和内存的缓存池中，从而提升（inser，delete，update）性能。
最后把Change Buffer记录到数据页的操作叫做merge。什么时候发生merge？有几种情况：在访问这个数据页的时候，或者用过访问后台线程、或者数据库shutdown、redolog写满 时候触发。
可以通过以下这个值，改变change 大小，以支持写多读少的业务场景。
show variables like 'innodb_change_buffer_max_size';
代表Change Buffer占Buffer Pool的比例，默认25%。
Adaptive HASH INDEAX
哈希索引放内存。为甚？ 我也不知道
Redo log Buffer
RedoLog 也不是每次都写入磁盘，在Buffer pool里面有一块内存区域（Log Buffer)专门用于保存 将要写入的内存文件的数据，默认是16m，它一样可以节省磁盘io。
show variables like 'innodb_log_buffer_size' ;
需要注意：redo log的内容主要是用于崩溃数据的恢复。磁盘文件的数据文件，数据来自于buffer pool，redo log 写入磁盘，而不是写入文件。
写入时间是参数配置的，默认是1
show variables like 'innodb_flush_log_at_tx_commit';
log buffer 刷入时机
log buffer 刷入区别
刷盘越快，越安全，也越消耗性能。
磁盘结构
表空间可以看做是INNODB的存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。INNODB的表空间分为五大块。
系统表空间
默认情况下，innodb存储引擎有一个共享表空间(/var/lib/mysql/ibdata1)系统表空间。
INNODB系统表空间包括innodb数据字典和双写缓冲区，changebuffer和undologs，如果没有指定file-per-table，也包含用户创建的表和索引数据。
1.undo（不详），也可以设置为单独表空间。
2.数据字典：由内部表构成，存储表和索引的元数据（定义信息）。
3.双写缓冲（INNODB的特性）。
INNODB页结构
如图，innodb的页大小为16，操作系统的页大学为4k，一页数据需要写四次。
在存储引擎写的过程如果宕机，可能出现页只写了一部分的情况（partial page write部分写失效）可能会导致数据丢失。
show variables like 'innodb_doublewrite';
这里就是双写缓冲的配置了。
为什么需要双写缓冲？
如果页崩溃之前它已经损坏了，那么用来做崩溃恢复没有任何意义。所以在运用redo log的时候需要一个页副本，如果出现部分写失效就用页的 副本还原页再完成崩溃恢复。这个页的副本就是double write，innodb的双写技术。
默认情况下，所有表共享一个表空间，这个文件会越来越大，而且不会收缩。
独占表空间（file-per-talbe tablespace)
我们可以让每个表都独占一个表空间。
show variables like 'innodb_file_per_table';
开启后，每个表都会开辟一个表空间，这个文件就是数据目录下的ibd文件，存放表的索引和数据。
但是其他类的数据，如回滚(undo)信息，插入缓冲索引页、系统事务信息，双写缓冲还是放在原来的表空间。
通用表空间 （general tablespaces)
也是 一种共享表空间，跟ibdate1类似。
可以创建一个通用的表空间，用来存储不同数据库的表，数据路径文件和自己定义。
create tablespace xx_tbspace add data file '/var/lib/mysql/xxx_tbspace.ibd' file_block_size=16k engine = innodb;
临时表空间 temporary tablespaces
存储临时表的数据，包括用户创建的临时表，和磁盘的内部临时表，对应ibtmp1文件，当数据库服务关闭时候，该表空间删除，下次重新产生。
redo log
看上面
undo log
undo log的数据默认在系统表空间ibdata1文件中，因为共享表空间是不自动收缩的，也可以单独创建表空间。
后台线程
主要负责刷新内存池中的数据和修改的数据页刷新到磁盘。
master thread 负责刷新缓存数据到磁盘并协调调度其他线程。
IO thread 分别为insert buffer、log、read、write进程，分别处理insert buffer、重做日志、读写请求的IO回调。
purge thread 用于回收undo 页
page cleanner thread 用来刷新脏页
mysql server 层 还有一个 binlog，它可以被所有存储引擎使用。
BinLog
SQL语言分为3种: DDL, DML, DCL
DML（data manipulation language）是数据操纵语言：它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。
DDL（data definition language）是数据定义语言：DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。
DCL（DataControlLanguage）是数据库控制语言：是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。
binlog 以事件的形式记录所有的DDL和DML语句（因为它记录的是操作而不是数据值，属于逻辑日志）可以用来做主从复制和数据恢复。
跟redo log不一样，内容是可以追加的，没有固定大小限制。
开启了binlog功能的情况下，我们可以把binlog导出成SQL语句，把所有的操作重放一遍，来实现数据恢复。
还有一种功能就是主从复制，原理就是从服务器中读取主服务器的binlog，然后执行一遍。
binlog.jpg
执行:update user set name = 'dia' where id = 1;
1.先查到这条数据，如果有缓存则使用。
2.把name值修改，然后调用引擎api接口，写入这条数据到内存中。这个时候redolog进入prepare状态，告诉执行器执行完成，可以提交。
3.执行器收到后通知binlog，然后调用存储引擎接口提交，并这是redolog为commit状态。
4.更新完成。
重点：
1.先记录到内存，再到文件。
2.记录redo log两个状态两个阶段。
3.存储引擎和server 记录不一样的日志。
4.先记录redo再记录binlog。
为什么需要两个阶段提交？
在存储引擎写的过程如果宕机，可能出现页只写了一部分的情况（partial page write部分写失效）可能会导致数据丢失。
比如我们执行把name改为dia，如果写完了redo log，还没有写入binlog的时候mysql 重启了。
redolog 可用于数据恢复，所以写入磁盘的dia，然后binlog没有这个逻辑日志，如果这个时候出现主从同步，就会出现数据不一致的情况。
所以在写两个日志的情况下，binlog就充当一个事务的协调者。通过innodb来执行prepare或者commit、rollback。如果 binlog写入失败就不会提交。
在崩溃的时候判断事务是否需要提交：
1.binlog无记录，redolog无记录：在redolog写之前crash。恢复操作：回滚。
2.binlog无记录，redolog状态prepare：在binlog写之前crash。恢复操作：回滚。
3.binlog有记录，redolog状态prepare：在binlog写完提交事务之前的crash 。恢复操作：提交。
4.binlog有记录，redolog状态commit:无须操作。
网友评论