山网站建设,个人网站怎么做游戏,深圳坑梓网站建设,直播电商的发展趋势系列综述#xff1a; #x1f49e;目的#xff1a;本系列是个人整理为了秋招面试的#xff0c;整理期间苛求每个知识点#xff0c;平衡理解简易度与深入程度。 #x1f970;来源#xff1a;材料主要源于拓跋阿秀、小林coding等大佬博客进行的#xff0c;每个知识点的修… 系列综述 目的本系列是个人整理为了秋招面试的整理期间苛求每个知识点平衡理解简易度与深入程度。 来源材料主要源于拓跋阿秀、小林coding等大佬博客进行的每个知识点的修正和深入主要参考各平台大佬的文章其中也可能含有少量的个人实验自证。 结语如果有帮到你的地方就点个赞和关注一下呗谢谢 【C】秋招实习面经汇总篇 文章目录 基本篇索引篇事务篇锁篇日志篇内存篇Buffer Pool其他之其他 参考博客 点此到文末惊喜↩︎ 基本篇
关系型数据库 定义是一种基于ACID事务模型的数据库采用表格形式存储数据通过表格中的关系连接实现数据的查询和管理。 模型采用ACID事务模型强调数据的完整性和一致性数据存储采用表格形式存储数据每一行表示一条记录每一列表示一个属性数据结构清晰易于理解和维护。数据管理通过表格中的关系连接进行数据查询和管理主要有索引、视图、触发器等技术 非关系型数据库NoSQL 定义是一种基于BASE模型的数据库采用多种形式存储数据通过分布式计算技术实现高可扩展性、高性能和高可用性 模型采用BASE模型强调高可用性和性能的平衡数据存储采用多种形式的数据存储模型例如键值对、文档、列族、图形等数据管理通过分布式计算技术实现高可扩展性、高性能和高可用性 MySQL 执行一条 SQL语句的流程 / MySQL的内部构造 客户端发送请求与连接器进行 TCP 三次握手建立长连接 服务层Server 连接器主要用于连接管理如连接的创建、维护、销毁、身份验证和权限控制等问题查询缓存存在则返回结果不存在则继续执行解析器对SQL语句进行进行词法分析、语法分析并构建语法树预处理器检查 SQL 查询语句中的表或者字段是否存在并展开*优化器确定SQL语句的执行方案、选择索引执行器根据SQL语句读取存储引擎数据返回结果给客户端 存储引擎负责数据的存储和读写。是插件式的可以使用多种引擎其中InnoDB是MySQL 默认的存储引擎 客户端和数据库的TCP三次握手连接方式 长连接 问题减少连接建立和释放的开销但可能会占用过多内存被系统强制杀掉。解决通过TCP心跳机制进行连接的维护。 // 短连接
连接 mysql 服务TCP 三次握手
执行sql
断开 mysql 服务TCP 四次挥手// 长连接
连接 mysql 服务TCP 三次握手
执行sql
执行sql
执行sql
....
断开 mysql 服务TCP 四次挥手数据库文件的存放 MySQL 存储的行为是由存储引擎实现的在这里只讨论MySQL的默认数据库数据库文件存在于/var/lib/mysql/ 自定义数据库名目录下 db.opt存储当前数据库的默认字符集和字符校验规则。自定义表名.frm存储每个表的表结构定义自定义表名.ibd参数 innodb_file_per_table为1存放在该独占表空间文件中为0则存储在该共享表空间文件中 表空间文件.ibd的结构 段segment 索引段存放 B 树的非叶子节点的区的集合数据段存放 B 树的叶子节点的区的集合回滚段存放的是回滚数据的区的集合 区extent 将连续的64个页划分到一个区链表中相邻的页的物理位置也相邻物理相邻的页可以使用顺序 I/O提高范围查询的扫描性能 页page innoDB数据库一次读写的单位是页16KB 行row 数据库表中的记录都是按行row进行存放的每行记录根据不同的行格式有不同的存储结构。 compact行格式是如何存储数据的 控制部分 变长字段长度列表逆序存储变长字段实际存储的数据的长度NULL 值列表非必须逆序存储代表列是否为NULL的二进制位记录头信息 delete_mask 标识此条数据是否被逻辑删除物理上还存在next_record下一条记录的位置。指向的是下一条记录的控制部分和数据部分之间的位置方便向左读控制部分向右读数据部分提高cache命中率record_type表示当前记录的类型0表示普通记录1表示B树非叶子节点记录2表示最小记录3表示最大记录 数据部分 row_id如果我们建表的时候指定了主键或者唯一约束列那么就没有 row_id 隐藏字段了。如果既没有指定主键又没有唯一约束那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的占用 6 个字节。trx_id事务id表示这个数据是由哪个事务生成的。 trx_id是必需的占用 6 个字节。roll_pointer这条记录上一个版本的指针。roll_pointer 是必需的占用 7 个字节。 在MySQL中varchar(n) 字段类型 组成存储头部变长字段长度列表 NULL值列表 数据部分该字段类型一行最大为65535字节容量其中n为字符个数具体大小与字符集有关 MySQL 的行溢出 原因 MySQL 中磁盘和内存交互的基本单位是页一个页的大小一般是 16KB一个页无法存储一个大对象的一条记录会发生行溢出 解决将溢出的数据存储到溢出页中使用原页中的20字节存储溢出页地址 索引篇
索引 定义提升查询性能的数据结构。优点和缺点 加速查询适合WHERE 查询的字段、 GROUP BY 和 ORDER BY 作用的字段增加数据处理的灵活性将随机IO变为顺序IO本质是数据索引是形式索引的创建和维护需要耗费时间所以经常更新的字段不适合建立索引 索引分类 按「数据结构」分类Btree索引、Hash索引、Full-text索引。按「物理存储」分类聚集索引聚簇索引、非聚集索引辅助索引按「字段特性」分类主键索引、唯一索引、普通索引、前缀索引。按「字段个数」分类单列索引、联合索引。 其他 每一种存储引擎支持的索引类型不一定相同InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎BTree 索引类型也是 MySQL 存储引擎采用最多的索引类型。 BTree 数据结构MySQL InnoDB 的默认索引数据结构 定义B树可以分两部分进行理解 由内部结点组成的多路平衡查找树用于快速查找操作由叶子结点组成的按主键顺序的双链表可以进行高效的数据的增删操作和基于范围的顺序查找 多路平衡查找树 多路表示是一棵多叉树m2每个非叶子结点由两部分组成 按主键顺序的子节点索引链表每个子节点索引指向一个子节点且索引结点键值等于索引子节点的最小键值类似目录查找。最大和最小键值主要用于快速定位和过滤查询请求的。 平衡表示B树的每个子树的高度是相等的尽可能“矮胖”查找表示B树的快速查找能力较强。 相比B树非叶子结点不存放数据可以存放更多的索引使得树更加“矮胖”极大减少比较耗时的I/O操作。搜索复杂度为O(logdN)当最大分支数d大于100时千万级数据量的查询操作也只需做 3~4 次的磁盘 I/O 操作即树的高度只有3~4层。 双链表 数据记录的快速定位一个叶子结点通常存储多个数据记录的物理地址数据存在在数据页中页目录由槽组成每个槽索引数据记录分组后的最后一条。数据记录分组是有序的所以通过二分法可以进行快速查找。提高磁盘IO效率数据页是磁盘IO的基本单位默认为16KB通常与叶子结点一一对应但可以根据业务场景进行调节顺序查找和增删快BTree 的层内结点均按指定键顺序进行双链表链接。可以高效满足数据的增删操作和基于范围的顺序查找 其他数据记录的索引和存储 B树索引中每个叶子节点存储一个或多个数据记录索引而每个数据记录实际上是存储在数据页中的。一个数据页中包含了多条数据记录每条数据记录都对应着B树中的一个叶子节点。当进行索引查询时InnoDB会在B树的叶子节点中查找对应的数据记录而这些数据记录实际上都存储在数据页中。通过将多个数据记录存储在一个数据页中InnoDB能够更高效地利用磁盘和内存空间提高数据存储和查询性能。构建位置在磁盘上构建B树时它的节点通常被分成多个磁盘块每个磁盘块可以被读写到内存中进行操作因此磁盘I/O是构建和查询B树的瓶颈。为了提高查询性能B树通常使用缓存机制将部分磁盘块缓存在内存中以便在查询时快速访问。需要注意的是尽管B树通常是在磁盘中构建的但是在某些情况下也可以将B树存储在内存中用于处理小规模数据或需要快速更新的数据结构。 按存储方式划分的索引聚集索引聚簇索引和非聚集索引辅助索引 聚集索引 数据存储聚集索引的索引行和数据行存储在一起在一个表中只能有一个聚集索引性能增删慢查询快 非聚集索引 数据存储非聚集索引的索引行是单独存储的需要通过索引找到数据行性能增删快查询慢 按字段特性分类 主键索引 值唯一的聚簇索引用于索引主键字段。一张表只能有一个主键索引查询性能高值唯一并按主键自动排序可以使用二分法进行查找 唯一索引 值唯一的非聚簇索引可以包含null值 普通索引 用于数据库的查询加速的非聚集索引并且值可以重复 前缀索引 支持基于前缀搜索的模糊匹配 空间复杂度低搜索效率高。 按字段个数分类 单列索引建立在单列上的索引联合索引建立在多列上的索引遵循最左匹配原则 最左匹配原则查询条件中没有联合索引的第一个字段则索引不会被使用。遇到范围查询如 、的时候会停止匹配区间匹配需要扫描整个索引而且这种操作代价相当高因此会使用其他方式。下推优化在联合索引遍历过程中对联合索引中包含的字段先做判断直接过滤掉不满足条件的记录减少回表次数区分度优化建立联合索引时要把区分度大的字段排在前面这样区分度大的字段越有可能被更多的 SQL 使用到。 索引优化的方法 前缀索引优化 减小索引字段大小但order by 就无法使用前缀索引也无法把前缀索引用作覆盖索引。 覆盖索引优化 直接操作已索引字段减少读取数据页的磁盘I/O操作 自增主键索引 在尾部追加式的插入可以避免索引表的分裂和维护提高插入性能 防止索引失效 索引列要设置为 NOT NULL 约束否则引擎会放弃索引进行全表扫描MySQL 索引使用的注意事项 MySQL 索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度不要在列上使用函数和计算这将导致索引失效而进行全表扫描尽量避免使用 ! 或 not in或 等否定操作符都会导致索引失效而进行全表扫描尽量避免使用or进行条件连接会导致索引失效而进行全表扫描MySQL 只能使用一个索引多列索引可以使用复合索引复合索引遵守最左匹配原则不是按照索引的最左列开始查找则无法使用索引。索引不会包含有NULL值的列若复合索引中有列含有NULL值会导致该索引失效。尽量不要让字段默认值为NULL查询条件左右两侧类型不匹配的时候会发生隐式转换隐式转换带来的影响就是可能导致索引失效而进行全表扫描like %value匹配值第一个为%会导致全表查询删除长期未用的索引避免检索的额外损耗 事务篇
事务的四个特性ACID 原子性Atomicity一个事务中的所有操作要么全做要么全不做。如果在中间状态发生错误会回滚到开始状态。一致性Consistency事务执行前后数据均处于合法的状态完整性约束隔离性Isolation事务提交前更新的数据对其他事务不可见。持久性Durability已提交的事务的更新是持久的发生故障可恢复 数据库如何保证原子性 实现Innodb 的回滚日志undo log负责记录事务执行的反向操作约束undo log必须先于数据持久化到磁盘谋而后动优先保证回滚日志的完整性 数据库如何保证一致性 锁机制保证并发读写共享数据的正确性触发器机制通过约束避免违反数据一致性的修改类似断言机制事务机制通过事务执行的原子性和隔离性保证修改操作的一致性 数据库如何保证隔离性 使用四种隔离级别保证事务的隔离使用锁机制保证数据的隔离使用MVCC多版本并发控制保证并发的访问隔离 数据库如何保证持久性 undo log回滚日志存储执行中事务的修改操作用于事务回滚的撤销操作redo log重做日志循环存储已提交事务的修改操作循环区满会被覆盖用于短时的故障恢复bin log 归档日志存储所有已提交事务的修改操作通常来源于重做日志用于完整的数据恢复和主从复制 事务的四个隔离级别自顶向下性能下降安全性增加 未提交读read uncommitted 概述事务未提交修改即可见被其他事务读到原理事务开始时数据库为该事务创建一个独立工作区。对于读操作直接从数据库中读取。对于写操作修改到工作区中而不提交到数据库中其他并发事务读取工作区中的数据但是如果该事务未提交而回滚会导致其他并发事务的脏读。 提交读read committed 概述事务提交时修改才可见原理事务修改前先获取写锁并将修改保存到事务日志中然后再提交从而保证其他事务只能读取已经提交的事务所修改的数据。 可重复读repeatable read 概述事务执行期间多次读到的相同数据是一致的是MySQL InnoDB 引擎的默认隔离级别原理事务通过读写锁共享访问数据事务修改数据前要使用写锁保证其他事务无法同时修改数据。事务读取数据前加一个读锁保证其他事务无法修改数据 串行化serializable 概述并发事务的执行结果与各事务按序单独执行结果相同可线性化原理MVCC可以通过为每个修改操作创建一个版本并在读取时根据版本来确定读取的数据是否被修改过从而实现串行化。在MVCC中每个事务都可以看到自己开始之前的数据库状态但是不能看到其他事务的更新。 事务为什么要隔离 / 并发事务执行导致的问题严重程度自上而下 脏读A事务执行更新数据操作后发生了回滚而B事务读到了更新后的数据但是由于A事务的回滚导致B事务读取数据和数据库中的不一致。 不可重复读在一个事务内多次读取同一个数据如果出现前后两次读到的数据不一样的情况 幻读一个事务多次查询某个符合条件的「记录数量」出现前后两次查询「记录数量」不一致的情况 不同事务隔离级别的能力 在「读未提交」隔离级别下可能发生脏读、不可重复读和幻读现象在「读提交」隔离级别下可能发生不可重复读和幻读现象但是不可能发生脏读现象在「可重复读」隔离级别下可能发生幻读现象但是不可能脏读和不可重复读现象在「串行化」隔离级别下脏读、不可重复读和幻读现象都不可能会发生。 锁篇
全局锁 锁的生命周期执行flush tables with read lock加锁会话结束或者执行unlock tables会自动释放加锁后阻塞修改命令 对数据的增删改操作比如 insert、delete、update等语句对表结构的更改操作比如 alter table、drop table 等语句。 应用场景 数据库的逻辑备份备份期间只读会影响业务。 可重复读隔离MVCC备份数据前创建快照并锁定数据库只读其他事务执行期间使用该快照进行可重复读隔离的数据处理最后再将快照合并到数据库中。 表级锁 表级读写锁读锁会阻塞写写锁会阻塞读和写。生命周期从加锁到释放锁或会话结束粒度较大影响并发性能元数据锁对表操作加读锁对表结构操作加写锁。事务执行期间一直持有直到事务提交。由于执行的写优先机制如果出现写锁等待会阻塞其后的读操作导致线程栈溢出所以对表结构变更时先将其他执行中的长事务kill掉。意向锁对表中的记录加读锁前先对整个表加意向读锁。对表中的记录加写锁前先对整个表加意向写锁。可以快速判断表里是否有记录被加锁。AUTO-INC 锁5.1.22后变成字段锁插入数据前被 AUTO_INCREMENT 修饰的字段加上轻量级锁然后给该字段赋值一个自增的值后即释放而不需要等待整个插入语句执行完后才释放锁 行级锁innoDB支持基于索引实现 Record Lock针对一条记录的读写锁Gap Lock用于可重复读隔离级别解决下幻读问题是对一个范围的读写锁Next-Key LockRecord Lock Gap Lock 的组合锁定一个范围并且锁定记录本身 日志篇
MySQL的关键日志 undo log回滚日志保证事务执行的原子性主要用于事务回滚和MVCC。redo log重做日志实现了事务中的持久性主要用于掉电等故障恢复bin log 归档日志是 Server 层生成的日志主要用于数据备份和主从复制 回滚日志undo log 事务回滚 原理 记录未提交事务在数据库中的修改操作若发生数据库崩溃或执行ROLLBACK语句通过undo log将数据库恢复到事务开始前的状态。先思后做回滚日志必须先于数据持久化到磁盘 具体操作 插入记录时存储记录的主键回滚时只需要删除主键对应的记录删除记录时存储记录的全部内容回滚时将该记录再插入更新记录时存储记录的旧值回滚时再更新为旧值 MVCC多版本并发控制原理 原理通过undo log记录的数据版本链保存事务对数据记录修改的多个版本每个事务通过隔离级别和快照时间选择合适的版本读取。具体操作 「读提交」隔离级别事务执行中对同一数据的selet读都会生成一个快照读「可重复读」隔离级别整个事务执行期间使用同一个快照读slect语句 重做日志 redo log数据结构和算法原理和问题解决方式 基本概念 原理概述记录已提交事务的所有修改操作当数据库发生故障时系统会将重做日志中的操作重新执行直到崩溃前的状态最大限度地减少数据的丢失。crash-safe崩溃恢复避免内存断电导致未刷盘的数据丢失 WAL Write-Ahead Logging技术MySQL 的写操作并不是立刻写到磁盘上而是先写日志然后在合适的时间再写到磁盘上。undo log 和redo log的区别 事务提交之前发生了崩溃重启后会通过 undo log 回滚事务事务提交之后发生了崩溃重启后会通过 redo log 恢复事务 执行流程 执行SQL语句如果在Buffer Pool中未命中缓存页则将数据页从磁盘加载到BufferPool中修改缓存页并置为脏页同时将当前执行事务的修改操作写入redo log buffer中数据库选择一个合适的时机将脏页和redo log buffer刷盘 redo log刷盘效率 redo log buffer 是一个循环链表构成的缓冲区写满时将最早写入的记录刷盘并覆盖写满阻塞问题 原因如果系统并发量比较大会导致redo log 缓冲文件经常满并要进行刷盘而导致MySQL更新操作被阻塞解决方式通过调整innodb_log_Buffer_size 参数增加redo log容量默认16 MB 刷盘方式redo log刷盘是以追加方式的顺序写磁盘执行效率高 redo log 刷盘时机刷盘过多影响效率刷盘过少影响安全 MySQL 正常关闭时当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时会触发落盘InnoDB 的后台线程每隔 1 秒将 redo log buffer 持久化到磁盘。每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘 bin log 定义MySQL 的 Server 层实现的日志用于记录已提交事务的修改操作。写入方式binlog 是追加写写满一个文件就创建一个新的文件继续写保存全部日志执行流程 创建binlog cacheMySQL 中的每个线程都用于缓冲 binlog的 binlog cache缓存由binlog_cache_size 参数控制超过缓存大小就要暂存到磁盘。提交时写入事务执行过程中先把修改记录写入binlog 的Server 层cache事务提交的时候再把 binlog cache中的完整事务执行记录写到 binlog 文件中并清空binlog cache一个事务的binlog不能拆开保证从库执行的原子性层级写入每个线程将自己 binlog cache增加事件头区分然后由binlog dump线程写入同一个 binlog 文件最后通过fsync将数据持久化到磁盘 主从复制机制 原理主数据库将所有修改操作记录到Binlog中从数据库请求并从主数据库获取Binlog并写入本地的中继日志relay log中然后启动一个IO线程不断读取并更新从数据库作用 保证从数据库和主数据库的数据一致性可用于数据备份、灾难恢复等分布式读取写数据时只写主库在读数据时只读从库这样即使写请求会锁表或者锁记录也不会影响读请求的执行 两阶段提交协议two phase commit protocol2PC 定义是一种分布式事务一致性协议原理将事务的提交分成两个阶段 prepare表决阶段协调者将通知所有参与者准备提交或取消事务在表决过程中参与者将告知协调者自己的决策。commit 提交阶段协调者将基于第一个阶段的投票结果进行决策当且仅当所有的参与者同意提交事务协调者才通知所有的参与者提交事务否则协调者将通知所有的参与者取消事务。参与者在接收到协调者发来的消息后将按事务提交顺序执行相应的操作。 作用保证事务提交后的redo log 和 binlog的一致性。 redo log 影响主库的数据binlog 影响从库的数据所以 redo log 和 binlog 必须保持一致才能保证主从数据一致。两段提交的问题及解决方式 磁盘 I/O 次数高每个事务提交都会进行两次 fsync刷盘一次是 redo log 刷盘另一次是 binlog 刷盘。可通过 组提交group commit机制将多个事务binlog刷盘操作合并成一次刷盘锁竞争激烈两阶段提交在「多事务」的情况下需要加一个锁来保证提交的日志提交顺序的一致性。将提交阶段进行细分减小锁的粒度提高并发度。 MySQL 磁盘 I/O 很高有什么优化的方法 设置组提交的两个参数 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数延迟 binlog 刷盘的时机从而减少 binlog 的刷盘次数。这个方法是基于“额外的故意等待”来实现的因此可能会增加语句的响应时间但即使 MySQL 进程中途挂了也没有丢失数据的风险因为 binlog 早被写入到 page cache 了只要系统没有宕机缓存在 page cache 里的 binlog 就会被持久化到磁盘。将 sync_binlog 设置为大于 1 的值比较常见是 100~1000表示每次提交事务都 write但累积 N 个事务后才 fsync相当于延迟了 binlog 刷盘的时机。但是这样做的风险是主机掉电时会丢 N 个事务的 binlog 日志。将 innodb_flush_log_at_trx_commit 设置为 2。表示每次事务提交时都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件注意写入到「 redo log 文件」并不意味着写入到了磁盘因为操作系统的文件系统中有个 Page Cache专门用来缓存文件数据的所以写入「 redo log文件」意味着写入到了操作系统的文件缓存然后交由操作系统控制持久化到磁盘的时机。但是这样做的风险是主机掉电的时候会丢数据 具体更新一条记录 UPDATE t_user SET name ‘xiaolin’ WHERE id 1; 的流程如下: 执行器负责具体执行会调用存储引擎的接口通过主键索引树搜索获取 id 1 这一行记录 如果 id1 这一行所在的数据页本来就在 buffer pool 中就直接返回给执行器更新如果记录不在 buffer pool将数据页从磁盘读入到 buffer pool返回记录给执行器。 执行器得到聚簇索引记录后判断更新前后数据是否相同若相同则不继续执行若不同则将更新前后的记录都当作参数传给 InnoDB 层让 InnoDB 真正的执行更新记录的操作InnoDB 层先将数据库修改操作记录到相应的 undo log因为这是更新操作需要把被更新的列的旧值记下来也就是要生成一条 undo logundo log 会写入 Buffer Pool 中的 Undo 页面不过在内存修改该 Undo 页面后需要记录对应的 redo log。InnoDB 层开始更新记录会先更新内存同时标记为脏页然后将记录写到 redo log 里面这个时候更新就算完成了。为了减少磁盘I/O不会立即将脏页写入磁盘后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术MySQL 的写操作并不是立刻写到磁盘上而是先写 redo 日志然后在合适的时间再将修改的行数据写到磁盘上。在一条更新语句执行完成后然后开始记录该语句对应的 binlog此时记录的 binlog 会被保存到 binlog cache并没有刷新到硬盘上的 binlog 文件在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。事务提交为了方便说明这里不说组提交的过程只说两阶段提交 prepare 阶段将 redo log 对应的事务状态设置为 prepare然后将 redo log 刷新到硬盘commit 阶段将 binlog 刷新到磁盘接着调用引擎的提交事务接口将 redo log 状态设置为 commit将事务设置为 commit 状态后刷入到磁盘 redo log 文件 内存篇Buffer Pool
详细解释MySQL中数据是以页为单位你查询一条记录会从硬盘把一页的数据加载出来加载出来的数据叫数据页会放入到Buffer Pool中。后续的查询都是先从Buffer Pool中找没有命中再去硬盘加载page fault减少硬盘IO开销提升性能。更新表数据的时候也是如此发现Buffer Pool里存在要更新的数据就直接在Buffer Pool里更新。然后会把“在某个数据页上做了什么修改”记录到重做日志缓存redo log buffer里接着刷盘到redo log文件里。
Buffer Pool缓存机制 前提 InnoDB 中的数据以页为基本单位进行磁盘和内存的交互一个页的默认大小为 16KB。初始化MySQL 启动时InnoDB 会为 Buffer Pool 申请一片连续的内存空间。默认配置下为128MB 并默认以16KB为单位大小划分缓冲页。可通过调整innodb_buffer_pool_size参数来设置 Buffer Pool 的大小一般建议设置成可用物理内存的 70%左右访问方式查询一条记录先查找缓存若没有则InnoDB会将整个数据页加载到Buffer Pool中通过索引你定位到具体数据页再使用页目录定位具体的数据记录内存关系MySQL 刚启动时虚拟内存空间很大而使用到的物理内存空间却很小因为只有这些逻辑页被访问发生缺页中断系统才会申请物理内存并建立虚拟地址和物理地址的映射关系。 原理 读取数据若查询的数据记录在Buffer Pool命中则直接使用否则再去磁盘中读取。(先查缓存命中再去磁盘读)修改数据如果数据记录在 Buffer Pool命中则直接修改并设置为脏页再在一个合适的时机将脏页刷盘。 Buffer Pool的页面管理方式 空闲链表连接空闲缓冲页的控制块的索引的双链表Flush链表连接脏页的控制块的索引的双链表后台线程就可以遍历 Flush 链表将脏页写入到磁盘LRU链表每次将即将访问的页移动到链表头部如果该页不在链表中则淘汰链表末尾页脏页同时存在于 LRU 链表和 Flush 链表。 简单的 LRU 算法并没有被 MySQL 使用的原因 预读失效 问题由于空间局部性会将要访问的数据页的相邻页面也加载到LRU链表首部但可能并未被访问导致缓存命中率下降解决方式划分这两个区域后预读的页就只需要加入到 old 区域的头部当页被真正访问的时候才将页插入 young 区域的头部默认young 区域与 old 区域比例是 63:37即old加载进来的局部页拥有更高的淘汰速度。 Buffer Pool 污染分类解决 问题一个扫描大量数据的SQL语句会将LRU内的大量热点数据淘汰当热点数据再次被访问时由于缓存未命中会产生大量磁盘IO导致性能急速下降解决方式将进入到 young 区域条件增加了一个停留在 old 区域的时间判断避免非热点数据的进入目标清晰 脏页什么时候会被刷入磁盘 当 redo log 日志满了的情况下会主动触发脏页刷新到磁盘Buffer Pool 空间不足时需要将一部分数据页淘汰掉如果淘汰的是脏页需要先将脏页同步到磁盘MySQL 认为空闲时后台线程会定期将适量的脏页刷入到磁盘MySQL 正常关闭之前会把所有的脏页刷入到磁盘 Buffer Pool 的主要数据结构 其他之其他
Innodb为什么要用自增id作为主键 提高性能按序插入的自增主键可以减少InnoDB存储引擎中B数的分裂和重建提高插入数据效率避免数据冲突避免主键冲突造成的数据插入失败增加灵活性方便对表的扩展和优化是一种非业务主键 MyISAM和InnoDB实现B树索引方式的区别是什么 MyISAM引擎是非聚集索引数据文件和索引文件分开存储。仅支持表级锁适合读操作多并发度低的场景InnoDB引擎是聚集索引主键索引与数据存储在叶子节点中。支持事务机制和行级锁拥有更强的并发处理能力 说一说Drop、Delete与Truncate的共同点和区别 Drop操作删除整个表包括表结构删除速度快但无法回滚Delete操作根据指定条件进行删除会产生undo日志可以进行回滚操作Truncate 删除表中的所有数据但是会保留表结构、索引和约束等元素 MySQL如何进行性能优化 存储引擎根据业务场景选择合适的存储引擎和配置参数数据结构设计合适的数据库结构允许部分数据冗余并进行分库分表局部性处理对热点数据使用缓存备份和索引使用主从读写分离优化SQL语句避免使用 Select *列出需要查询的字段 B树相比于B树的优点 顺寻查找快B树顺序查找需要进行中序遍历而B树的数据按指定键顺序以双向链表形式存储在叶子节点查询稳定好任何关键字的查找必须走一条从根结点到叶子结点所有关键字查询的路径长度相同磁盘IO次数低B树索引节点可以存储更多的索引指针更加矮胖Hash树适合唯一值查找但是常见业务场景是一次查多值和顺序查找 视图和游标 视图是一个逻辑上的虚拟表由查询语句定义可以将多个表中的数据进行组合游标是一个临时的数据库缓存对象缓存查询的结果集游标可以更方便的执行结果集相关的操作 数据库悲观锁和乐观锁的原理和应用场景分别有什么 悲观锁先获取锁再进行业务操作数据库执行SELECT … FOR UPDATE时会获取被选中的数据行的行锁乐观锁不对数据进行加锁通过时间戳避免ABA问题 MySQL索引类型 哈希索引通过哈希表实现单条记录的快速查询BTree索引通过B树组织索引值实现任意数据记录的查询全文索引FULLTEXT 对文本中的所有单词建立索引大大提高文本搜索效率RTREE索引将空间数据分解成不同层级的矩形区域并将矩形区域组织成树形结构处理高维数据可能出现维度灾难 数据库分表 作用将表细粒度化减少表单的访问负载提高查询性能分表策略 水平拆分解决表中数据量太大的问题 取模分表数据相对比较均匀不容易出现热点和并发访问的瓶颈。连续分表按照时间维度进行划分 垂直拆分解决表间的IO竞争问题 将不同的表放在不同的服务器上 问题数据迁移和扩容需要将表进行汇总和排序会有比较大的开销 数据库中有那些键的类型 超键在关系中能唯一标识元组的属性集在表中元组表示一行属性表示一列候选键可以唯一标识每个元组的一组属性该组属性中删除任一个都无法唯一标识了主键唯一标识每个元组的一个或多个属性集合外键在其他表中是主键可以保证相关表的数据完整性 数据库中的三大范式 1NF原子性不存在多值属性即每个属性值不能再分2NF唯一性1NF且不存在非主属性对主键的部分依赖依赖主键中的一部分3NF主键决定一切1NF且不存在非主属性对主键的传递依赖a依赖bb依赖c则c传递依赖于aBCNF所有属性不存在传递依赖。BCNF一定是3NF MySQL中CHAR和VARCHAR的区别有哪些 存储长度char是固定长度实际字符不足用空白字符填充vachar只占用实际存储长度不占用多余空间访问速度char的访问速度通常比较快内存对齐 数据库结构优化 范式优化 比如消除冗余节省空间。。反范式优化比如适当加冗余等减少join限定数据的范围 务必禁止不带任何限制数据范围条件的查询语句。比如我们当用户在查询订单历史的时候我们可以控制在一个月的范围内。读/写分离 经典的数据库拆分方案主库负责写从库负责读拆分表分区将数据在物理上分隔开不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样当对这个表进行查询时只需要在表分区中进行扫描而不必进行全表扫描明显缩短了查询时间另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。 少年我观你骨骼清奇颖悟绝伦必成人中龙凤。 不如点赞·收藏·关注一波 点此跳转到首行↩︎
参考博客 版权规范 一文讲清楚MySQL事务隔离级别和实现原理开发人员必备知识点 小林coding 待定引用 待定引用 待定引用 待定引用 待定引用
