mysql高阶

岳大博

已于 2022-09-13 15:20:27 修改

阅读量307

点赞数 1

分类专栏：数据库文章标签： mysql 数据库

于 2022-08-27 16:42:51 首次发布

本文链接：https://blog.csdn.net/qq_28613337/article/details/126445120

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

mysql高阶

mysql底层数据结构

有序二叉树
有序二叉树的前序遍历为有序的，一般为升序
红黑树
本质仍然是二叉树，类似AVL树也是自平衡树，但是自平衡的临界值不一样。如果其节点val为依次提高那么每层为两个节点，高度仍然不可控，查询效率低。
B树
在红黑树的基础上将只能存一个键值对的节点空间扩展为可以存储多个键值对，默认一个节点16KB，被称为一个page。操作系统中默认4KB为一页。因此修改数据库页的配置的时候，一般为4的倍数。
叶子节点具有相同的深度，叶节点的指针为空。
所有索引元素不重复。
节点中的数据索引从左到右递增排列。

B+树
可以为每个节点设置大小。如果该节点的数量大小超过，那么将该节点索引分叉为两个节点。
每层的所有节点中存储的索引都是从大到小。
非叶子节点不存储data,值存储索引（冗余，为了快速定位数据位置），可以放更多的索引。
叶子节点包含索所有索引字段
叶子节点之间用指针链接，提高区间访问的性能。
每个页节点（page）默认16KB，假设索引数据为Integer时为8B，指针大小为6B，所以没有存数据的page可以存储 16000 / (8 + 6) = 1170，假设叶子节点索引+数据为1KB，那么每个叶子节点可以存16个数据。因此高度为3可以存储 1170 * 1170 * 16 = 2千多万。一般单表数量超过1000万需要分库分表。

mysql从磁盘中取数据的最小单元是页。
mysql中的B+树中的指针我双向指针，学术中仅仅是单向指针。
存储引擎MyISAM（数据和文件分开存储，非聚集索引）
分为三个文件存储，打开数据库文件夹，MYD结尾的文件存储表的数据，MYI结尾的文件存储数据索引，frm结尾存储表结构。索引为b+树。索引叶节点data中存储的是数据在MYD中存储的位置。查询的数据不仅仅是索引数据就需要回表。效率低一些。
存储引擎innodb（主键为聚集索引，非主键索引为非聚集索引）
有两个文件，.frm文件存储表的结构，.ibd文件存储表的主键索引及所有数据。
仅仅有索引的数据一般会被load进内存中。
.ibd文件叶节点存储该主键对应的所有数据。
回表指的是通过非主键索引查找之后，仅仅获取了主键id，还需要通过主键id从主键索引树中将其他数据查出来。避免回表的方式有 1. 建立联合索引；2. 在大量数据查询时先使用子查询将主键查出来，之后一次回表将数据查询出来。
如果该表中没有主键
数据库会给建立一个隐藏列（唯一），用于.idb文件的组织。会浪费数据库的性能。
主键建议使用整形自增主键？
如果使用uuid作为主键，索引的检索都会慢，因为字符串的大小比较相对于数值要慢。
非主键索引结构叶子节点存储的是主键值，为了保证一致性和节省存储空间。并且因为生成的uuid是无序的，之前排好序的索引就会被打乱重新排序，插入数据速度慢。
mysql主键的设计参考链接

如果是分片数据库，可以采用有
b+树范围查询
先通过索引查找到范围小的一头，然后顺着叶节点组成的有序链表，直到找到范围上限，就找到了所有目标
hash索引
查找单个元素速度非常快。但不支持范围查找
联合索引
先按照第一个字段排序，如果第一个元素一样，再按照第二个，依次类推

mysql架构

buffer_poll

将热数据缓存到innoDB引擎的位置。会有一定的淘汰策略（LRU）；
free链表记录buffer_poll中空闲的数据块
数据的更新和写入操作会首先写入到buffer_poll中，并且该内存块会被标记为脏页，mysql中会有单独的进程定期的将脏页中的数据更新到磁盘中。
flush链表记录所有脏页。
lru链表（最近最少使用的排在最后），最新加入的链表放在最前面，链表中有最新更新的也重新放到最前面
5/8的为热数据，如果两次page的访问大于1s，那么说明为正常的多次sql语句的访问，并不是一次扫描，这样解决了过多数据加载后将热数据更新掉的问题。
redoLog文件（innodb引擎层）
redolog详情参考
物理日志，记录内容是"在某个数据页上做了什么修改"
文件数据内容：标记每个数据块需要做的操作。如果一条语句涉及到多个数据块会被记录为对多个数据块的操作。方便后续恢复操作时候的并行处理，速度更快。
作用：

保证了事务的持久性（只要提交就会永久保存到数据库）
保证了事务的原子性（一次事务产生相同分组的多条redolog，在事务提交之前该分组状态为prepare，在提交之后状态变为commit）
数据是先写入到bufferpoll中，一定周期后写入到磁盘，但是如果此时数据库挂了数据就丢失了，答案是我们现在将写入的操作临时存储到redolog中就可以恢复数据。
顺序写入速度更快。
两次提交的顺序 begin --> 执行ddl写入redolog（prepare阶段）–> 提交事务 --> 写入binlog --> redolog阶段更新为commit。如果记录写入binlog该条记录被当做已提交。保证了主节点重启进行数据恢复后，数据与从节点一致。（通过已经数据同步的binlog来判断是否该数据应该被恢复）
工作过程：1. 在mysql启动的时候就会创建N个redolog文件（ib_logfile0, ib_logfile1）2. 在执行DDL操作时就写入到logbuffer中，此时该次事务组中的redolog记录都是prepare状态。在commit之后会将状态修改为commit状态。logbuffer中的内容会根据配置的flush_log策略定期持久化到redolog文件。
未提交的事务中sql也会写入logbuffer。之后持久化到redolog，保证数据库操作的持久性。如果事务回滚会通过undolog日志进行回滚。
innodb_flush_log_at_trx_commit
默认为1，提交之后，写入os buffer并调用fsync()刷到log file，安全等级最高，性能最差。
设置为2，每次提交都会写到os buffer，然后每秒调用fsync()将os buffer中的日志写入redo log file.
设置为0，表示每秒执行dolog buffer->os buffer->dolog file，系统崩溃，会丢失1钟数据。速度最快
因为是提前创建好的指定大小的文件，所以为顺序写，避免了低效的随机写。相当于一个缓冲区
当两个redolog文件存满之后第一个redolog文件对应的buffer_poll中的脏页仍然未被更新到磁盘中就会触发一个checkpoint。数据库就会降低mysql的执行，用更多的资源来讲redolog及buffer_poll中中的数据写入到数据文件中。
binlog文件
逻辑日志，类似于sql语句
主要用于主从同步及数据恢复。
默认存储一个月的记录。
查看binlog文件。mysqlbinlog或/var/lib/binlog命令
使用/var/lib/binlog还可以执行两个position之间或两个时间戳之间的日志
每次重启都会重新创建一个binlog文件。手动执行flush log也会重新建立一个binlog文件。
purge 删除旧日志文件
undolog文件，记录之前的数据，sql语句用于回滚。
checkpoint
除了上面提到的redolog文件中要被覆盖了，但该位置的数据仍然没有被持久化，那么就会触发一次checkpoint。
如果脏页数据超过buffer_poll一定比例，也会触发一次checkpoint。
每次刷盘的检查点都会被记录下来，用于恢复数据的时候知道从哪个位置开始执行恢复。
double_write_buffer,因为操作系统一个page为4KB，但是msyql的page为16KB,在将脏页持久化到磁盘一半的时候如果数据库挂了，这时候执行redolog文件去重新写入会多执行，不执行会少执行。因此有了双写buffer文件，先写入到该文件成功然后将redolog记录删除，就完美了。

change_buffer
一个更新操作会操作很多磁盘，为了加快更新操作的速度。
避免数据脏读的工作过程，1. 一个update操作，会将更新的数据记录在change_buffer中，如果此时读取一个数据发现在change_buffer中存在，会先经过change_buffer的更新然后再记录在buffer_poll中，并且该数据为脏页。