1、mysql架构与sql执行流程

零、执行sql的流程
app--->缓存（默认不开启）--->sql解析--->sql预处理--->sql执行优化器--->sql查询执行引擎--->存储引擎

一、通信类型
同步、异步，一般用的都是同步

二、连接方式
1、长连接
连接可以被多个客户端复用，缺点是会消耗服务端内存。
（1）、一个连接多久不被使用，会被mysql关闭呢？
a、show global variables like 'wait_timeout'; --非交互式超时时间，如JDBC
b、show global variables like 'interactive_timeout'; --交互式超时时间，如navicat
（2）查询mysql当前连接数：show global status like 'Thread%';
cached：服务端缓存的连接数
connected：服务端当前打开的连接数
created：服务端创建过的连接数
running：服务端正在运行的连接数
注：服务端是创建线程去建立连接。
（3）、查询mysql当前打开连接数的状态：show processlist;
sleep：等待连接
query：正在查询的连接
（4）、mysql最大的连接数：show variables like 'max_connections';
注：mysql所有参数都有两种级别的：global和session，默认是session。可修永久改配置：vim /etc/my.cnf

2、短连接
操作完毕，连接就会close

三、通信协议
1、Unix Socket
如本机登录：mysql -u(用户名) -p(密码) 
2、TCP/IP
如非本机登录：mysql -h(ip) -u(用户名) -p(密码)

四、通信方式
1、单工: A只能主动给B发，B不能主动发给A
2、半双工：A可以主动给B发，B也可以主动给A发，不可以同时发
3、全双工：A可以主动给B发，B也可以主动给A发，可以同时发

注：sql语句最大的限制，默认为4MB: show variables like 'max_allow%';

五、缓存功能
1、mysql默认是将缓存关闭的
相关配置：show variables like 'query_cache%';

六、解析器（parser）
1、词法解析

2、语法解析
解析树（如图）

 

七、预处理
比如：校验表名是否存在

八、优化器（query optimizer）
1、sql执行成本的度量：show status like 'Last_query-cost';
2、每个sql可能有多种执行计划（execution plan），每个计划有一定的成本（execution plan cost）。优化器会选出成本低的执行计划
3、优化器的追踪，默认是关闭的。show variables like 'optimizer_trace';
4、开启优化追踪：set optimizer_trace='enabled=on';
   查看优化器过程：select * from infomation_schema.optimmizer_trace\G;--->结果是一个json串
   
八、存储引擎
1、查看表的存储引擎： show table status from 库名;
注：每个表可以用不同的存储引擎。
2、查看数据存储目录：show variables like 'datadir';   ---->可以看到不同的存储引擎，有不同的文件和数量
3、myisam：只支持表级别的锁，不支持事务，插入和查询的效率较高
4、innodb：支持表级别锁，
5、memory：将数据放在内存中，机器一旦重启就数据消失了
6、CSV：纯文本的文件，不支持索引，
7、Archive（归档）：不支持update和delete

 

九、innodb architecture

0、由change buffer、log buffer组成
1、用户--->buffer pool(默认128MB)--->DBfile(page 16kb)
2、查看buffer_pool：show status like '%innodb_buffer_pool%'
3、buffer pool内存采用LRU算法
4、insert、delete、update等操作先更新change buffer。change buffer再merge到DBfile。不需要判断数据唯一性的时候会进入change buffer
5、什么时候触发merge？a、访问数据页  b、后台线程定时同步
6、查看change_buffer：show variables like '%change_buffer%';  change buffer默认占用buffer pool大小的25%
7、crash save：利用redo log(ib_logfile0、ib_logfile1，默认是48MB)。崩溃恢复----------->仅innodb有
8、WAL技术（write ahead logging:先写日志，再写磁盘）buffer pool--->log buffer--->os cache(系统缓存)--->redo log
9、log buffer什么时候写入到redo log？show variables like '%innodb_flush_log_at_trx_commit%'; 默认为1
    0、每秒1次；1、每次提交事务时；2、~

 

十、磁盘

1、System Tablespace  系统表空间（iddetail）
2、File-Per-Table Tablespace 独占表空间（对于innodb的表，每个表都有*.idb文件，存储数据和索引）
show variables like '%innodb_file_per_table%'
3、General Tablespace 通用表空间
create tablespace ts2673 add datafile '/var/lib/mysql/ts2673.ibd' file_block_size=16K engine=innodb;
crete table t2673(id integer) tablespace ts2673; 
4、Temporary Tablespace 临时表空间
5、Undo Tablespace：回滚使用，实现事务的原子性----------->仅innodb有

 

十一、bin log（server 层日志）

1、记录DDL DML逻辑日志
2、用于数据恢复和主从复制

 

十二、update执行过程

0、update table set a = '12' where 1=1;
1、从mysql（先buffer-pool，再磁盘）查出该数据，并存到undo log(更新前的数据)
2、将更新后的数据保存在buffer pool中
3、将更新后的数据记录到log-buffer中，并标记为prepare。
4、通知server层可以commit了。
5、server层记录binlog，之后执行commit
6、将log-buffer中的数据标记为commit
7、将log-buffer中的数据同步到redo log（更新后的数据）
8、后台线程定时将 buffer-pool中的数据 merge到磁盘