60 MySQL架构与执行流程原理

mysql底层通讯协议：
mysql通讯类型： 同步/异步。
同步调用： 基于请求和响应
异步调用： 服务器端单独开启一个线程处理比较耗时的代码；
优点： 防止客户端阻塞
缺点：1 客户端不能及时获取响应的结果
2， 单独开启个线程处理，有可能会消耗cpu资源，小项目处理用线程，大项目用mq

连接方式：长连接与短链接
长连接： http协议底层基于tcp封装，tcp 三次握手和四次握手
tcp三次握手确认server端在，保证客户端传输消息给服务器 可靠传输

注意： 如果频繁发送http请求的时候，每个请求每次都会做tcp三次握手和四次握手 发送效率极低；

长连接：每次发送请求后会把连接保存起来实现复用。不会频繁创建连接，避免tcp三次握手和四次挥手。

优点： 避免重复创建tcp三次握手和四次挥手 ；
缺点： 有可能浪费服务器端资源。

短连接：每次发送完请求后都会把连接关闭；
优点： 避免浪费我们服务器的资源
缺点： 每次建立连接的时候需要经历tcp三次握手，如果在频繁发送请求的情况下效率会非常低。

mysql jdbc 长连接。
28800秒
mysql客户端发出jdbc请求连接到mysql 服务器的情况下 ，空闲时间8个小时，如果在8个小时以内没有发送任何参数的情况下，认为该连接超时，自动会断开该连接。

#非交互式超时时间，如 JDBC 程序
show global variables like 'wait_timeout'; 
#交互式超时时间，如数据库工具
show global variables like 'interactive_timeout';

MySQL服务器默认的“wait_timeout”是28800秒即8小时，意味着如果一个连接的空闲时间超过8个小时，MySQL将自动断开该连接，这也是大家在刚学习mysql遇到的“MySql连接空闲8小时自动断开引起的问题”
mysql连接空闲8小时自动断开引起的问题 JDBC

1.定时发送jdbc语句 （不合理）

  1. 增加mysql服务器空闲超时时间（不合理） jdbc
    修改mysql安装目录下的配置文件 my.ini文件（如果没有此文件，复制“my-default.ini”文件，生成“复件 my-default.ini”文件。将“复件 my-default.ini”文件重命名成“my.ini” ），在文件中设置：
    wait_timeout=31536000
    interactive_timeout=31536000
    这两个参数的默认值是8小时(60608=28800)。 注意： 1.wait_timeout的最大值只允许2147483 （24天左右）
    也可以使用mysql命令对这两个属性进行修改
    3.使用数据库连接池 自带功能 定时清理空闲超时的jdbc连接
    数据库连接池：提前帮创建5个连接 最小连接数5 最大连接数10
    缓存每个连接请求 定时器检查jdbc连接在设定空闲超时时间如果还是没有被使用的情况下，自动回销毁。
    设定空闲超时时间比如6个小时 一定保证有5个连接一直存在
    mysql数据库空闲超时时间8个小时

定期使用连接池内的连接，使得它们不会因为闲置超时而被 MySQL 断开。并且每次使用连接前检查连接是否可用，定期回收空闲的连接。

mysql状态分析之show global status
show global status like 'Thread%';
Threads_cached--- 服务器端缓存连接；
Threads_connected ---当前打开的连接数
Threads_created ---创建的线程数
Threads_running---正在运行的线程
数据库连接池和线程池原理基本上一样：

show PROCESSLIST; 查询当前mysql服务器接收所有的连接信息
状态详细描述：
sleep：线程正在等待客户端发送新的请求；
query：线程正在执行查询或者正在将结果发送给客户端；
locked：在mysql服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态。
analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划；
copying to tmp table：线程在执行查询，并且将其结果集复制到一个临时表中，这种状态一般要么是做group by操作，要么是文件排序操作，或者union操作。如果这个状态后面还有on disk标记，那表示mysql正在将一个内存临时表放到磁盘上。
sorting Result：线程正在对结果集进行排序。
sending data：线程可能在多个状态间传送数据，或者在生成结果集，或者在想客户端返回数据。

项目中可能会遇到MySQL: ERROR 1040: Too many connections”的异常情况，造成这种情况的一种原因是访问量过高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力；另一种原因就是MySQL配置文件中max_connections值过小。
首先，首先我们来看下mysql的最大连接数：
Mysql允许最大的连接数：
show variables like '%max_connections%';
max_connections 100
1.创建一个容器缓存连接；
2.提前设定创建好一定的连接缓存到该容器中；
3.数据结构模型采用链表
链表：
获取连接时候：
每次在获取连接的时候，获取当前的链表的头结点，并且删除该头结点引用关系；
释放连接的时候：
会将该连接放入到我们链表的后面。

链表前面：最近少使用连接；
链表后面：最近有被使用连接；


package com.taoto.jdbcs;

import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.*;
import java.util.LinkedList;
import java.util.logging.Logger;

/**
 *@author tom
 *Date  2020/10/13 0013 9:14
 *
 */
public class MKDataSource implements DataSource {
    private String driverClassName = "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=true";
    private String username = "root";
    private String password = "root";
    private LinkedList<Connection> connections = new LinkedList<Connection>();


    public MKDataSource(int initialSize) throws ClassNotFoundException, SQLException {
        Class.forName(driverClassName);//执行驱动
        for (int i = 0; i < initialSize; i++) {
            Connection connection = DriverManager.getConnection(url, username, password);
            connections.add(connection);

        }
    }
//获取连接
    @Override
    public Connection getConnection() throws SQLException {
         /* Connection first=connections.getFirst();
          connections.remove(first);*/
        Connection first=connections.removeFirst();
        return first;
    }

    //关闭连接
    public  void closeConnection(Connection connection){
              connections.add(connection);
    }
    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return null;
    }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        return null;
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return false;
    }

    @Override
    public PrintWriter getLogWriter() throws SQLException {
        return null;
    }

    @Override
    public void setLogWriter(PrintWriter out) throws SQLException {

    }

    @Override
    public void setLoginTimeout(int seconds) throws SQLException {

    }

    @Override
    public int getLoginTimeout() throws SQLException {
        return 0;
    }

    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        return null;
    }

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        MKDataSource mkDataSource=new MKDataSource(2);

        for (int i = 0; i <10 ; i++) {

            Connection connection= mkDataSource.getConnection();
            PreparedStatement pstmt=connection.prepareStatement("SELECT * from  order_info WHERE 1=1");
            ResultSet resultSet=pstmt.executeQuery();
            System.out.println("发送结果"+connection);
            if(resultSet==null){
                return;
            }
            if(resultSet.next()){
                String name=resultSet.getNString("name");
                System.out.println("查询结果："+name);
            }
        if(resultSet !=null)
            resultSet.close();
        if(pstmt!=null){
            pstmt.close();
        }
        mkDataSource.closeConnection(connection);

        }


    }
}

Mysql通讯协议： Unix Socket/TCIP

Unix 在linux 操作系统中 客户端和服务器端都在同一台电脑上 客户端访问mysql使用Unix 协议非网络协议。
TCP/IP套接字 客户端与服务器不在同一台电脑上 采用网络方式实现通讯
命名管道和内存共享
在window系统中客户端和Mysql服务器在同一台电脑上，可以使用命名管道和共享内存的方式，
命名管道开启：–shared-memory=on/off；
共享内存开启：–enable-named-pipe=on/off;
MYSql底层通讯协议：半双工机制：

单工： 数据单向发送（遥控器）
半双工： 数据双向发送。但是不同时运输 mysql 采用半双工模式（对讲机）
全双工：数据双向运输，可以同时运输。

·max_allowed_packet 是mysql中一个设定参数，用于设定所接受的包的大小，根据情形不同，其缺省值可能是1m或者4m，比如是4m 的情况下，这个值的大小即为：4 10241024= 4194304 max_allowed_packet 最大值是1G(1073741824)，如果设置超过1G，查看最终生效结果也只有1G。

show variables like '%max_allowed_packet%';

mysql缓存机制原理：

在mysql中也自带对查询语句实现内部缓存，缓存的key就是为sql语句 value 就是为结果，但是在mysql8.0直接去除了自带缓存因为作用不是很大，因为它缓存的key sql语句不能够发生什么改变，连空格也不允许，字母大小写必须要统一，当对该表数据实现修改的时候，也会清理缓存，所以mysql8.0直接去除了缓存，后期可以使用Redis作为缓存。
query_cache_type=OFF 关闭 ON开启 no没有
show variables like '%query_cache%';

mysql自带缓存机制：
读取内存数据一定比读取磁盘io数据效率要高。
用户空间切换到内核状态过程。
缓存key：查询语句
缓存value内容： 查询结果
注意： mysql8.0去除了自带缓存机制
原因： sql语句多了空格或者大小写变化 都会清理缓存重新缓存

查询sql语句执行的原理：
sql 查询底层如何实现的？
1，先查询mysql 服务器端自带缓存
2，解析
语法/词法
词法：
先将sql语句拆分成n多个不同的单词，根据不同的单词规则匹配语法，验证sql语句写法是否正确 生成解析树。
3，处理器:
解析表名称和列名称是否正确。
4，执行 优化器，
数据库存储引擎 当前表的数据结构采用什么规则进行存放。
存储引擎是和表关联。
存放在硬盘
5， 查询该表对应的存储引擎

6，查询数据返回给客户端。

innodb 与 myisam 引擎之间的区别。

image.png

1.CSV存储引擎 [图片上传失败...(image-8a321c-1602633157278)]

数据存储以CSV文件 特点： 不能定义没有索引、列定义必须为NOT NULL、不能设置自增列 -->不适用大表或者数据的在线处理 CSV数据的存储用,隔开，可直接编辑CSV文件进行数据的编排 -->数据安全性低 注：用vi或文本编辑器编辑之后，要生效使用flush table XXX 命令 应用场景： 数据的快速导出导入 表格直接转换成CSV

2.Archive存储引擎 压缩协议进行数据的存储，数据存储为ARZ文件格式 特点： 只支持insert和select两种操作 只允许自增ID列建立索引 行级锁 不支持事务 数据占用磁盘少（较其他存储引擎小的多，基本1:8-1:9的比例） 应用场景： 日志系统 大量的设备数据采集

3.Memory（heap）存储引擎 因为现在nosql已经非常成熟了，所以一般不会在生产上使用memory存储引擎。但是他是临时表默认的存储引擎。 数据都是存储在内存中，IO效率要比其他引擎高很多 服务重启数据丢失，内存数据表默认只有16M 特点： 支持hash索引，B tree索引，默认hash（查找复杂度0(1)） 字段长度都是固定长度varchar(32)=char(32) 不支持大数据存储类型字段如 blog，text（如果超过的话，会选择myisam引擎） 表级锁 应用场景： 等值查找热度较高数据 查询结果内存中的计算，大多数都是采用这种存储引擎 作为临时表存储需计算的数据

4.myisam存储引擎（MySQL8.0被废弃掉了） Mysql5.5版本之前的默认存储引擎 较多的系统表也还是使用这个存储引擎 系统临时表也会用到Myisam存储引擎 特点： a，select count(*) from table 无需进行数据的扫描，他有一个专门计算数据的函数，InnoDB需要一行行的扫描，计算出来。 b，数据（MYD）和索引（MYI）分开存储 c，表级锁 d，不支持事务

myisam实现B+树的体现 [图片上传失败...(image-78efc0-1602633157278)]

数据和索引分别存储，不管用哪个存储引擎，都会生成一个.frm文件（表定义文件），数据保存在myd文件，索引保存在myi文件里面。在myisam里面，叶子节点的数据区保存的是.myd的内存地址，在.myi通过索引找到这条数据的内存地址，再通过这个地址去.myd里面找到对应的数据。

5.InnoDB Mysql5.5及以后版本的默认存储引擎特点： a.事务ACID b.行级锁 c.聚集索引（主键索引）方式进行数据存储 d.支持外键关系保证数据完整性（不常用）

InnoDB在MySQL中的体现 [图片上传失败...(image-96f5b0-1602633157268)]1.CSV存储引擎 [图片上传失败...(image-8c72ce-1602633163048)]

数据存储以CSV文件 特点： 不能定义没有索引、列定义必须为NOT NULL、不能设置自增列 -->不适用大表或者数据的在线处理 CSV数据的存储用,隔开，可直接编辑CSV文件进行数据的编排 -->数据安全性低 注：用vi或文本编辑器编辑之后，要生效使用flush table XXX 命令 应用场景： 数据的快速导出导入 表格直接转换成CSV

2.Archive存储引擎 压缩协议进行数据的存储，数据存储为ARZ文件格式 特点： 只支持insert和select两种操作 只允许自增ID列建立索引 行级锁 不支持事务 数据占用磁盘少（较其他存储引擎小的多，基本1:8-1:9的比例） 应用场景： 日志系统 大量的设备数据采集

3.Memory（heap）存储引擎 因为现在nosql已经非常成熟了，所以一般不会在生产上使用memory存储引擎。但是他是临时表默认的存储引擎。 数据都是存储在内存中，IO效率要比其他引擎高很多 服务重启数据丢失，内存数据表默认只有16M 特点： 支持hash索引，B tree索引，默认hash（查找复杂度0(1)） 字段长度都是固定长度varchar(32)=char(32) 不支持大数据存储类型字段如 blog，text（如果超过的话，会选择myisam引擎） 表级锁 应用场景： 等值查找热度较高数据 查询结果内存中的计算，大多数都是采用这种存储引擎 作为临时表存储需计算的数据

4.myisam存储引擎（MySQL8.0被废弃掉了） Mysql5.5版本之前的默认存储引擎 较多的系统表也还是使用这个存储引擎 系统临时表也会用到Myisam存储引擎 特点： a，select count(*) from table 无需进行数据的扫描，他有一个专门计算数据的函数，InnoDB需要一行行的扫描，计算出来。 b，数据（MYD）和索引（MYI）分开存储 c，表级锁 d，不支持事务

myisam实现B+树的体现 [图片上传失败...(image-86cd66-1602633163047)]

数据和索引分别存储，不管用哪个存储引擎，都会生成一个.frm文件（表定义文件），数据保存在myd文件，索引保存在myi文件里面。在myisam里面，叶子节点的数据区保存的是.myd的内存地址，在.myi通过索引找到这条数据的内存地址，再通过这个地址去.myd里面找到对应的数据。

5.InnoDB Mysql5.5及以后版本的默认存储引擎特点： a.事务ACID b.行级锁 c.聚集索引（主键索引）方式进行数据存储 d.支持外键关系保证数据完整性（不常用）

InnoDB在MySQL中的体现 [图片上传失败...(image-5ce4af-1602633163046)] 只有.ibd和.frm两个文件，他的index和数据放在了一起，在InnoDB中，以主键为索引来组织数据的存储，如果没有明确指定一个主键（ID）索引，他会默认的生成一个隐藏的6byte的Int型的索引来作为他的主键索引，只是这个隐式的索引看不到而已。 注意：MySQL每个版本都是略微差异，比如在MySQL5.7之前都有.frm文件，而在mysql8.0之后就将该文件移除掉了。
只有.ibd和.frm两个文件，他的index和数据放在了一起，在InnoDB中，以主键为索引来组织数据的存储，如果没有明确指定一个主键（ID）索引，他会默认的生成一个隐藏的6byte的Int型的索引来作为他的主键索引，只是这个隐式的索引看不到而已。 注意：MySQL每个版本都是略微差异，比如在MySQL5.7之前都有.frm文件，而在mysql8.0之后就将该文件移除掉了。

读取io 操作触发 的问题？
用户状态到内核状态实现切换。
InnnoDb 存储引擎中 BufferPool缓冲池 主要缓存硬盘数据减少io的操作。
概念：读取io的数据以页(page)访问
一页中默认占16kb 多行数据
mysql 底层存储的数据以页为单位
以页访问数据的情况下好处： 如果访问热点数据，‘局部性原则’
访问page页以什么数据库存放？ 以链表方式存放
链表结构存放的好处是：
链表增删效率比较高 缓存淘汰算法。

缓存的缺点：
数据一致性的问题：

缓存池将数据写入到硬盘中的时候，为了防止数据丢失，会先将日志写入到redoLog中，在将数据写入到硬盘中
下次当mysql启动的时候，读取redolog数据等待恢复。

mysqlInnodb 底层 写入 redolog 采用顺序io
mysqlInnodb 底层 将数据写入到硬盘中采用随机io。

顺序IO与随机IO的区别
对于从磁盘中读取数据的操作，叫做IO操作，这里有两种情况：
第一种随机IO：假设我们所需要的数据是随机分散在磁盘的不同页的不同扇区中的，那么找到相应的数据需要等到磁臂（寻址作用）旋转到指定的页，然后盘片寻找到对应的扇区，才能找到我们所需要的一块数据，一次进行此过程直到找完所有数据，这个就是随机IO，读取数据速度较慢。
第二种顺序io：假设我们已经找到了第一块数据，并且其他所需的数据就在这一块数据后边，那么就不需要重新寻址，可以依次拿到我们所需的数据，这个就叫顺序IO。

顺序ｉｏ效率比随机ＩＯ效率要高，

为什么记录 redo log的日志 采用顺序io？而我们的写入磁盘的数据采用随机io
因为记录日志不需要建立索引目录结构，直接追加日志文件后面即可，而我们写入磁盘物理存储的数据，需要建立索引的文件
索引文件排列数据都不在同一个扇区中，所以写入磁盘的数据采用随机io。

为什么需要设计缓存池
在innodb中我们的数据是存放在硬盘中，为了能够减少磁盘io操作，在innodb中引入缓存池技术，会将从磁盘中读取的数据（page页）放入到缓冲池中。如果客户端查询数据（page页） 如果在缓存池的存在的情况下，可以不需要查询磁盘io操作，从而提高效率。

undolog/redlog/Binlog之间的区别？
1 redlog 主要用于数据库宕机数据恢复；
2.undolog 事务回滚的日志
3， Binlog 底层mysql 实现 对sql 语句实现增量日志 ，主从复制，集群

redlog undolog 是Innodb 实现的。

脏页： 缓冲区的页数据与硬盘中数据不一致 redlog 日志恢复：
1，读取当前磁盘io的数据，放入到缓冲区中。
2， 将原来的数据记录到undo_do日志中；
3，修改缓冲池中的数据
4， 将数据记录到redo_log 日志中。（顺序io）
5，记录binlog日志文件
6， 提交事务后，后台会开启n多个io线程将缓冲区的数据，刷新到硬盘（随机io）

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,793评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,567评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,342评论 0赞 338
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,825评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,814评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,680评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,033评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,687评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 42,175评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,668评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,775评论 1赞 332
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,419评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,020评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,978评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,206评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,092评论 2赞 351
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,510评论 2赞 343