Mysql 的分层
1651051193(1).png

  1. 客户端
  2. server层
  3. 引擎层

引擎层：
innodb myisam memeory ; 或自研，插拔式的额



Server 层

连接器，查询缓存，分析器，优化器，执行器，内置函数，存储过程，触发器，视图等

store层

存储引擎，负责数据的存储和提取，
支持 InnoDB , MyISAM , Memery ,等多个存储引擎，
5.5.5 变成了默认引擎

连接器

socket 通信， 管理链接，与权限校验
链接名

[root@192 ~]# mysql -h host[数据库地址] -u root[用户] -p root[密码] -P 3306 1：如果不对，就不对咯 2：通过后会从权限表中查询当前用户的权限，后续操作权限，会根据这个权限判断

长连接：8个小时

mysql 系统库， 系统user表，存储权限信息
1651054083(1).png

  1. mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password'; //创建新用户
  2. mysql> grant all privileges on *.* to 'username'@'%'; //赋权限,%表示所有(host)
  3. mysql> flush privileges //刷新数据库
  4. mysql> update user set password=password(”123456″) where user=’root’;(设置用户名密码)
  5. mysql> show grants for root@"%"; 查看当前用户的权限

创建链接，开辟一个session 空间，存储链接信息，存储权限信息，

root改权限后，不会实时刷新 已建立链接的session中的权限信息
修改权限如果同步权限 会阻塞已建立的链接

show processlist 查看建立的链接
1651041178(1).png
kill id 可以关掉链接

wait_timeout : 最长链接时间默认8小时

show global variables like “wait_timeout”;

mysql> show global variables like "wait_timeout";
mysql>set global wait_timeout=28800``设置全局服务器关闭非交互连接之前等待活动的秒数

断开链接再发请求的错误提示： Lost connection to MySQL server during query

大多数的长连接，都放在pool内管理，
但长连接会导致MySQL内存涨的快，临时的内存管理内存在链接对象里面，只有链接释放才会释放内存
如何解决问题；

1：定期断开长连接， 2：如果是5.7 可以在较大的操作后，通过执行MySQL reset connection 重置链接，不需要重连不需要重新权限校验

曹勇操作：

mysql>show databases; 显示所有数据库 mysql>use dbname； 打开数据库： mysql>show tables; 显示数据库mysql中所有的表； mysql>describe user; 显示表mysql数据库中user表的列信息）；

如何链接：
先去查询缓存，默认是关闭的，8以后的版本取消掉了

/etc/my.cnf 文件中配置缓存
query_cache_type = 0 :

my.cnf

query_cache_type有3个值

0代表关闭查询缓存OFF，

1代表开启ON，

2（DEMAND）代表当sql语句中有SQL_CACHE关键词时才缓存

query_cache_type=2

可以显示指定缓存
mysql> select SQL_CACHE * from test where ID=5；
mysql> show status like'%Qcache%'; //查看运行的缓存信息
缓存命中的信息
select 语句为key ,结果为value

查询缓存命中率

show status like %Qcache%

1651041720(1).png

  • Qcache_free_blocks:表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理。
  • Qcache_free_memory:查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA可以根据实际情况做出调整。
  • Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
  • Qcache_inserts: 表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数，次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
  • Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。
  • Qcache_not_cached: 表示因为query_cache_type的设置而没有被缓存的查询数量。
  • Qcache_queries_in_cache:当前缓存中缓存的查询数量。
  • Qcache_total_blocks:当前缓存的block数量。

缓存很鸡肋，8.0后取消了

引擎层，LRU ,buffer-pool 已做了缓存，还哟LRU 策略

分析器

分析语句是否正确，
把sql语句分析查语法树，
结果话的数据，

词法分析器原理

1、词法分析 2、语法分析 3、语义分析 4、构造执行树 5、生成执行计划 6、计划的执行

词法分析过程步骤
1651054916(1).png
词法分析结束：https://en.wikipedia.org/wiki/LR_parser
词法分析插件： antlr4 ,把 一个sql语句分析成语法树
可以在IDEA工具当中安装插件：antlr v4 grammar plugin。插件使用详见课程

saga两段式提交

  1. commit
  2. roll back

根据语法树，生成一个逆操作的SQL,

语法树

image.png

优化器

比如 where 后的索引，优化器会节点用哪个索引执行效率高
mysql> select * from test1 join test2 using(ID) where test1.name=yangguo and test2.name=xiaolongnv;

具体分析走那个条件更好，

执行器：

调用引擎

bin-log

server层实现的
记录update的逻辑
二进制文件，基础cud操作
1、Binlog在MySQL的Server层实现（引擎共用）
2、Binlog为逻辑日志,记录的是一条语句的原始逻辑
3、Binlog不限大小,追加写入,不会覆盖以前的日志

开启bin-log
在配置文件中，配置目录，
配置my.conf

  1. 配置开启binlog
  2. log-bin=/usr/local/mysql/data/binlog/mysql-bin
  3. 注意5.7以及更高版本需要配置本项：server-id=123454（自定义,保证唯一性）;
  4. #binlog格式，有3种statement,row,mixed
  5. binlog-format=ROW
  6. #表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定
  7. sync-binlog=1

格式：

  1. statement 记录的是 语句 效率高，内存小，压力小，主从会不一致 ，
  2. row 建议是这，记录的那个语句影响的记过

  3. mixed ： 混合，方式的结合体

    canal 就是基于row 同步的，
    binlog命令：

    1. mysql> show variables like '%log_bin%'; 查看bin-log是否开启
    2. mysql> flush logs; 会多一个最新的bin-log日志
    3. mysql> show master status; 查看最后一个bin-log日志的相关信息
    4. mysql> reset master; 清空所有的bin-log日志

    查看bin-log 文件：

    1. mysql> /usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 查看binlog内容

事务的记录，

inset会记录在一个文件中，
delete会记录在另一个文件中
恢复的语句
恢复的范围， end_log_pos 位置，
还可以通过时间段恢复
恢复的名称：

  1. 从bin-log恢复数据
  2. 恢复全部数据
  3. /usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p tuling(数据库名)
  4. 恢复指定位置数据
  5. /usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position="408" --stop-position="731"  /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p tuling(数据库)
  6. 恢复指定时间段数据
  7. /usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 --stop-date= "2018-03-02 12:00:00"  --start-date= "2019-03-02 11:55:00"|mysql -uroot -p test(数据库)