mysql记录总结

0814记录

Mysql执行流程:

读的执行流程

 

1建立连接（Connectors&Connection Pool），通过客户端/服务器通信协议与MySQL建立连

接。MySQL 客户端与服务端的通信方式是 “ 半双工 ”。对于每一个 MySQL 的连接，时刻都有一个线程状态来标识这个连接正在做什么

2查询缓存（Cache&Buffer），(8.0以后删除了查询缓存)这是MySQL的一个可优化查询的地方，如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启查询缓存或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成“解析树”。

3解析器（Parser）将客户端发送的SQL进行语法解析，生成"解析树"。分为词法解析和语法解析,预处理器根据一些MySQL规则进一步检查“解析树”是否合法，例如这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义，最后生成新的“解析树”。

4查询优化器（Optimizer）根据“解析树”生成最优的执行计划。MySQL使用很多优化策略生成最优的执行计划，可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）。

5查询执行引擎负责执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存（Cache&Buffer）中，以后若有相同的 SQL 语句执行则直接返回结果。

写的执行流程

 

1 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

3 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redolog处于prepare状态(数据持久化)。然后告知执行器执行完成了，随时可以提交事务。

4 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

5 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。这里我给出这个 update 语句的执行流程图，图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的。

大致就是上面的流程。写的话，也会走上面的流程：连接、缓存、解析器、处理器、优化器、执行器。

不同的是，写会把缓存清空。接下来，解析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用哪个索引。然后，执行器负责具体执行，找到这一行，然后更新。

与读流程不一样的是，写流程还涉及两个重要的日志模块，它们正是：redo log（重做日志）和binlog（归档日志）。

binlog和redolog区别:

binlog属于server层,redolog是innodb独有属于服务层

redolog大小固定是环状的,一边写入内存一边做擦除操作

count(*),count(1),count(id),count(字段)区别:

count (字段）：遍历整张表，需要取值，判断 字段！= null，按行累加；

count (主键) ：遍历整张表，需要取 ID，判断 id !=null，按行累加；

count (1) ：遍历整张表，不取值，返回的每一行放一个数字 1，按行累加；

count (*)：不会把全部字段取出，专门做了优化，不取值。count ( * ) 肯定不是 null，按行累加。

count (主键) 可能会选择最小的索引来遍历，而 count (字段) 的话，如果字段上没有索引，就只能选主键索引，所以性能上 count (字段) < count (主键)

因为 count (*) 和 count (1) 不取字段值，减少往 server 层的数据返回，所以比其他 count (字段) 要返回值的性能较好；

所以结论是：** 按照效率排序的话，count (字段)<count (主键 id)<count (1)≈count ()，建议尽量使用 count ()。

参考链接:

https://mp.weixin.qq.com/s?__biz=MzU2MDY0NDA1MQ==&mid=2247492467&idx=1&sn=d1407d2d809ba27a80b0ecca765b003c&scene=21#wechat_redirect

恢复数据库流程:

1.创建临时库

2.找到最近一次备份记录,sql文件,在临时库中执行

3.记录之后的binlog日志记录文件,执行,将数据重放到临时库

4.将临时库中数据拷贝到生产库中

0828记录

索引

索引数据结构:

哈希表:等值查询快,范围查询需要全表扫描

有序数组:查询快,更新慢

搜索树:

二叉树(红黑树)”:高瘦”IO次数多

多叉树B树B+树:”矮胖”B树索引和数据存储在一起,B+树叶子节点有序并存储数值,非叶子节点只存储索引

主键索引:

字符串做主键索引占用空间大io效率低

数值做索引:占用空间小,io效率高自增

索引下推:

 

问题:innodb中单表删除一条数据时索引如何变化?

总结：delete命令只是把数据页或记录位置标记为可复用，表空间并没有被回收，该现象我们称之为”空洞“

https://cloud.tencent.com/developer/article/1854043?from=article.detail.1856225

问题:新增数据时,自增id用完会出现什么情况?

有主键，报主键冲突

无主键，InnDB 会自动生成一个全局的 row_id。它到达最大值后会从 0 开始算，遇到 row_id 一样时，新数据覆盖旧数据。所以，我们还是尽量给表设置主键。