首页  思维导图  详情

Mysql基础知识

2023-01-17 11:19:36   0  举报





AI智能生成

Mysql基础知识。

mysql

作者其他创作

大纲/内容

"异常读"

脏读

可重复读

幻读

Buffer Pool组成

InnoDB caches table

InnoDB caches index data

索引

数据结构

B+树

优化原理

覆盖索引

最左匹配原则

索引下推

加索引

直接创建完整索引，这样可能比较占用空间；创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

高级命令

alter table T engine=InnoDB

重建表的流程
1.创建一个临时表,扫描主键索引得到行数据,根据数据页生成B+树,存储到临时文件中(此时页中数据变的紧凑,不会存在空洞)
2.新数据写入原表的同时记录到row_log中(这样保证拷贝过程数据不丢失)
3.临时文件生成完之后,在将row_log操作到临时文件中,这样两者的数据则一致了
4.最后临时文件替换原表的数据文件

整个过程是online,获取MDL写锁->降级为MDL读锁->DDL->升级为MDL写锁->释放MDL锁

analyze table t

总结

count(字段)<count(主键 id)<count(1)≈count(*)

mysql 文件结构

表结构是存在以.frm 为后缀的文件里

ib_logfile redo log

mysql-bin.index bin log

*.ibd undo log

隔离级别

change buffer

因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

收益

节省随机读IO的消耗

问题

处于 prepare 阶段的 redo log 加上完整 binlog，重启就能恢复，MySQL 为什么要这么设计?

其实，这个问题还是跟我们在反证法中说到的数据与备份的一致性有关。在时刻 B，也就是 binlog 写完以后 MySQL 发生崩溃，这时候 binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用。

redo log 和 binlog 是怎么关联起来的?

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

MySQL 怎么知道 binlog 是完整的?

statement 格式的 binlog，最后会有 COMMIT；row 格式的 binlog，最后会有一个 XID event。

正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？

这个问题其实问得非常好。这里涉及到了，“redo log 里面到底是什么”的问题。实际上，redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由 redo log 更新过去”的情况。如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与 redo log 毫无关系。在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。

order by如何工作的

mysql的三种log

redo log

记录了这个页做了什么改动

收益

节省随机写磁盘的IO消耗

第一种是“redo log 写满了，要 flush 脏页”，这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为 0。

第二种是“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：第一种是，还没有使用的；第二种是，使用了并且是干净页；第三种是，使用了并且是脏页。

连坐行为

写入机制

事务在执行过程中，生成的 redo log 是要先写到 redo log buffer 的。

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

一种是，redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。

并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。

innodb_flush_log_at_trx_commit

设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;

设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；

设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

bin log

sync_binlog

sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；

sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；

sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

写入机制

事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了 binlog cache 的保存问题。