MySql执行Sql流程
MySql主要可以分为Server层和存储引擎两部分。
server层
主要包括连接器、查询缓存、分析器、优化器、执行器。所有内置的函数（日期、时间、数学、加密函数等），跨存储引擎的功能都在Server层实现，比如存储过程、触发器、视图等。
连接器
MySql有很多客户端，常用的有jdbc、navicat、MySql front等，这些客户端要向MySql发送命令，首先要跟Server建立通信连接，这个建立连接的工作就是连接器来完成的。
连接命令：
mysql -h host -P 3306 -u root -p[密码]
连接过程：
-
校验用户名密码，账户不正确直接退出；
-
去mysql库的user表中查询用户的权限；
-
将读到的权限信息缓存到用户空间中，用户连接之后，MySql给每个用户建立一个用户空间，用来保存一些信息，其中就缓存了用户权限信息；
正是因为缓存了用户权限信息，所以只要用户建立了连接，就算修改了该用户的权限，如果不重启服务或者用户没有重新建立连接，那么用户的新权限不会生效。
为什么要缓存权限信息？
当你的用户与数据库存在很多连接，如果你要修改用户权限后实时的更新权限，那么你就需要暂停该用户所有的连接，然后把权限数据刷新，然后再重新让这些连接恢复连接状态，如果MySql真的这样做，我相信没几个人会用它。
MySql账户相关sql命令
-- 创建新用户
mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password';
-- 赋权限,%表示所有(host)
mysql> grant all privileges on *.* to 'username'@'%';
-- 刷新权限
mysql> flush privileges;
-- 设置用户名密码
mysql> update user set password=password("123456") where user='root';
-- 查看当前用户的权限
mysql> show grants for root@"%";
-- 查看用户连接信息
show processlist;
mysql> show processlist;
+----+------+----------------------+-------+---------+-------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+----------------------+-------+---------+-------+----------+------------------+
| 81 | root | 192.168.17.1:11613 | mysql | Sleep | 9345 | | NULL |
| 82 | root | 192.168.17.1:10485 | sys | Sleep | 96 | | NULL |
| 84 | root | 192.168.17.128:37614 | NULL | Query | 0 | starting | show processlist |
+----+------+----------------------+-------+---------+-------+----------+------------------+
-- 如果发现有一个用户一直在blocking，那可以kill掉
kill id;
长连接
长连接最大控制时长
Client基于TCP/IP协议与MySql Server建立长连接，长连接默认最大空闲时长8个小时，也就是说超过8个小时该连接都没有与Server进行交互，MySql Server就会踢掉这个连接。
当连接被剔除，如果Client再次请求Server，就会收到错误“Lost connection to MySQL server during query”，这时候需要重连MySql，然后再操作。
-- 查看连接最大空闲时长参数，单位秒
show global variables like "wait_timeout";
-- 设置连接最大空闲时长
set global wait_timeout=28800;
MySql所有的variables参数都可以在my.cnf配置文件中进行配置。
连接管理
长链接建立成功后，如果Client一直有请求，那么会一直使用这个长链接，短连接在执行几次请求后很快就会断开连接，下次请求需要重新建立连接。所以使用长连接，并用连接池管理。
实际开发中我们都是用连接池管理连接，MySql在执行sql时临时使用的内存是保存在连接对象中的，这些内存资源在连接断开是才会释放掉。如果长连接一直没有断开过，可能会导致内存占用太大，发生OOM，被系统杀掉，现象就是MySql异常重启。
解决
- 定期断开长连接，在执行过一个占用大内存的查询后或者使用一段时间后，断开连接，再重连；
- MySql 5.7以后的版本，可以通过mysql_reset_connection来重新初始化连接。连接将恢复到刚创建时的状态，不需要重连和权限验证。
查询缓存 SQL_CACHE
当开启查询缓存时，MySql会以sql语句作为key，查询结果作为value，以key-value的形式将查询数据缓存在内存中，当下次执行同样的查询语句时，MySql就直接从内存中得到数据进行返回了，效率很高。
不过，这个功能在MySql8以后被移除掉了。
因为查询缓存的失效概率非常大，只要对表有更新，这个表的所有查询缓存都会被清除，对于更新频率高的表使用查询缓存，查询缓存的命中率很低，弊大于利。
不过一些静态表就是基本上不会更新的表可以使用查询缓存，比如字典表、系统配置表、地理位置表等。
开启查询缓存
在my.cnf配置文件中：
#query_cache_type有3个值
# 0代表关闭查询缓存OFF；
# 1代表开启ON；
# 2（DEMAND）代表当sql语句中有SQL_CACHE关键词时才缓存；
query_cache_type=2
-- 使用查询缓存
select SQL_CACHE * from user where id = 1;
查询缓存相关Sql命令
-- 查看查询缓存类型
show global variables like 'query_cache_type';
-- 查看查询缓存命中信息
show status like'Qcache%';
mysql> show status like'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 67091584 | -- 查询缓存剩余可用空间
| Qcache_hits | 0 | -- 命中缓存的次数，值越大，缓存效果越好
| Qcache_inserts | 0 | -- 未命中缓存然后插入数据的次数，值越大，说明缓存命中率不高，需要从存储引擎中查询数据，然后再insert到查询缓存中
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1184 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
在InnoDB中有一个Buffer Pool已经实现了缓存功能，LRU缓存淘汰策略。
分析器
分析器主要做以下这些工作：
-
词法分析
-
语法分析
校验sql语法正确性
-
语义分析
-
构造执行树
-
生成执行计划
-
计划的执行
Antlr语法解析器
应用场景：
-
mysql分库分表，Antlr可以分析语法得到id的值，根据id取模，定位到数据库，然后进去操作；
-
分布式事务
当一个事务开启时，需要对事务中的cud操作记录反向的操作以进行回滚，比如insert一条数据，那么就需要记录insert的反向操作delete这条数据。
当事务失败时，或者其他事务失败了，就按照反向操作进行回滚。
那Antlr可以分析语法，得到你的操作类型时insert、delete还是update，然后记录反向操作。
如果反向操作也失败了就重试，直到成功，如果到达限定重试次数，那就人工干预。
优化器
优化器主要有两个工作：
-
索引选择
当表中有多个索引时，根据效率决定使用哪个索引；
-
执行计划生成，多表join联合查询时，决定各个表的连接顺序；
比如这个查询
select * from t1 join t2 using(id) where t1.name = 'ming' and t2.name = 'zhang';这个查询sql有两个执行方案：
- 先从t1中找出name='ming’的记录，然后根据id依次关联t2，再根据t2.name='zhang’的条件对记录进行筛选
- 流程和第一种一样，不过是从t2到t1
所以联合查询时用小表（小数据集）驱动大表（大数据集），效率会高，因为一般情况下小表可以过滤掉更多记录。
执行器
执行器会验证当前用户对该表是否有相应的权限，有就调用存储引擎的接口去操作数据。
InnoDB引擎
InnoDB是MySql存储引擎的一种，其他还有MyISAM、Memory（基于内存）。从MySql 5.5.5开始，InnoDB成为MySql默认存储引擎。
整体流程如下，请放大观看：
Buffer Pool缓存机制
磁盘文件的随机读写性能很差，MySql没有直接将我们提交后的数据写入磁盘，而是提供了一套机制保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件（binlog、undo、redo日志都是顺序写），同时还保证了各种异常情况下数据的一致性。
顺序写磁盘文件效率远高于随机读写磁盘文件。
Kafka也是顺序写消息。
redo日志
undo日志用于回滚，那redo日志其实就是记录每个修改操作，当事务提交成功后，如果Buffer Pool中的缓存数据还没有写入磁盘就宕机了，可以用redo日志来恢复buffer pool里面的缓存数据。
binlog
binlog记录了CUD操作的执行逻辑，是一个二进制的日志。
binlog的特点：
- server层实现，可以跨存储引擎；
- binlog顺序写入，效率很高，只会追加记录，不会删除或者覆盖之前的日志；
- 存储sql语句的逻辑日志；
binlog用途
binlog一般用于数据恢复和数据同步。比如MySql的主从数据同步，canal同步数据等。
在生产环境中，一般都会有定时任务备份binlog日志。
开启binlog
配置my.cnf文件：
# 配置开启binlog
log-bin=/data/binlog/mysql-bin
# 5.7以及更高版本需要配置本项（自定义,保证唯一性）：
server-id=111;
# binlog格式，有3种statement,row,mixed
binlog-format=ROW
# 表示每执行1次写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定
sync-binlog=1
查看binlog
可以使用bin/mysqlbinlog命令查看binlog：
#查看binlog内容
mysqlbinlog /binlog/mysql-bin.00001
BEGIN
/*!*/;
# at 25071
#210909 17:15:01 server id 1 end_log_pos 25180 CRC32 0xf1c2bef1 Query thread_id=64 exec_time=0 error_code=0
SET TIMESTAMP=1631178901/*!*/;
update user set age = 30 where id = 1
/*!*/;
# at 25180
#210909 17:15:18 server id 1 end_log_pos 25211 CRC32 0xe780d185 Xid = 3432
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
binlog里面包含很多begin、commit这种关键词信息，begin和commit之间的信息就是一个完整的事务执行的逻辑。
binlog还包括：
-
操作发生的时间
at 25180 210909 17:15:18 server id 1 end_log_pos 25211 CRC32 0xe780d185 Xid = 3432 -
位置信息position
end_log_pos 25211 -
该操作上一个操作的位置信息
at 25180 -
该操作发生的server-id，此server-id就是my.cnf中配置的（自定义，唯一）
server id 1
使用binlog日志同步数据或者恢复数据
#恢复全部数据
bin/mysqlbinlog --no-defaults /binlog/mysql-bin.000001 |mysql -uroot -p dbName(数据库名)
#恢复指定位置数据
bin/mysqlbinlog --no-defaults --start-position="100" --stop-position="500" /binlog/mysql-bin.000001 |mysql -uroot -p dbName(数据库名)
#恢复指定时间段数据
bin/mysqlbinlog --no-defaults --start-date= "2020-01-01 00:00:00" --stop-date= "2021-01-01 00:00:00" /binlog/mysql-bin.000001 |mysql -uroot -p dbName(数据库名)
binlog三种格式
比如你执行了一条update语句，
-
statement
记录这条update语句
效率高、占用资源小、安全性低，有可能产生主从数据不一致
主从数据不一致场景：
update user set age = 1 where id = 1 or name = ‘’ limit 1;
主从数据库走的索引可能不一致，导致修改的数据不一致。 -
row
记录这条update语句执行后影响的结果
效率低、占用资源大、但安全性高，不会产生主从数据不一致
canal就是通过row格式解析binlog的。 -
mixed
statement和row的两种方式的结合，优化器优化语句分析之后，认定sql不会导致主从不一致，那就使用statement格式，效率高；
如果可能出现主从不一致，那就是使用row格式，保障安全性；
binlog相关命令
-- 查看bin-log是否开启以及binlog日志存储位置
mysql> show variables like '%log_bin%';
-- 生成一个最新的bin-log日志
mysql> flush logs;
-- 查看最后一个bin-log日志的相关信息
mysql> show master status;
-- 清空所有的bin-log日志
mysql> reset master;
扫一扫
7271
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
