哪些调查网站可以做问卷赚钱,wordpress博客 知乎,wordpress怎么用外链,创口贴设计网站官网存储引擎
这一张是关于整个存储引擎的汇总知识了。 MySQL体系结构
这里是MySQL的体系结构图#xff1a; 一般将MySQL分为server层和存储引擎两个部分。 其实MySQL体系结构主要分为下面这几个部分#xff1a; 连接器#xff1a;负责跟客户端建立连 接、获取权限、维持和管理…存储引擎
这一张是关于整个存储引擎的汇总知识了。 MySQL体系结构
这里是MySQL的体系结构图 一般将MySQL分为server层和存储引擎两个部分。 其实MySQL体系结构主要分为下面这几个部分 连接器负责跟客户端建立连 接、获取权限、维持和管理连接。 缓存存储一些查询语句的数据但是在MySQL8.0后已经被删除了 分析器包括两个主要功能 词法分析MySQL进行识别字符串是什么代表什么 语法分析根据词法分析的结构语法分析器判断SQL语句是否能满足MySQL语法。 在语法分析的时候会检查词法分析后的表和列是否存在数据库中 优化器对一个SQL语句的执行提出多种方案选择一种最佳的方案 一般是索引的选择和使用、连接的优化 执行器这里就是调用存储引擎来执行SQL语句了 注意执行器一般是负责协调和控制查询执行的过程实际的数据遍历和检索操作大多是由存储引擎来完成 我再来简化一下上述部分的用处 需求接收用户-连接器 场景: 想象您的“上司”用户有一个具体的需求SQL查询他通过“手机消息”连接器如MySQL的网络接口通知您MySQL数据库。 动作: MySQL的连接器负责接收用户的SQL请求建立连接并验证用户身份及权限。 快速检查过往经验查询缓存 场景: 在动手做事之前您先看看是否之前遇到过相同或相似的问题即检查“网上有没有相同的需求”查询缓存。 动作: MySQL会检查查询缓存看是否有相同的SQL语句及其结果已经存储。如果有则直接返回缓存的结果省去后续步骤。 深入理解需求分析器 场景: 如果没有现成的答案您需要仔细分析上司的具体要求确保理解无误。 动作: MySQL的分析器会对SQL语句进行语法解析和语义分析确保其符合SQL规范并理解查询的目的比如要查询哪些表、列以及条件是什么。 策划解决方案优化器 场景: 明确需求后您思考多种完成任务的方式从中选出最高效的方法。 动作: MySQL的优化器会基于分析器提供的信息考虑多种执行计划评估每种计划的成本如执行时间、所需资源等并选择最优的执行策略。 执行并交付成果执行器 场景: 最后一步是根据选定的方案编写代码并执行完成后将结果反馈给上司。 动作: MySQL的执行器根据优化器制定的计划调用存储引擎执行SQL语句读取或修改数据并将操作结果返回给用户。 存储引擎
这里一般就是三种最常见的存储引擎了 InnoDB是MySQL的默认存储引擎支持事务、外键。如果应用对事务的完整性要求较高在并发条件下要求数据的一致性数据操作除了插入和查询外还有很多更新、删除操作那么InnoDB是比较合适的。 MyISAM如果应用是以读和插入操作为主很少有更新和删除操作并对事务完整性和并发性要求不高那么选择这个存储引擎非常合适已经被Mongodb所替代了 Memory将所有数据保存在内存中访问速度快通常用于临时表及缓存。Memory缺陷就是对表的大小由限制太大的表无法存储在内存中而且无法保证数据安全性。(被Redis所替代了) 但是个人感觉一般面试题是围绕下面两种InnoDB和MyISAM进行对比的 事务支持: InnoDB: 支持事务处理ACID兼容适合需要高可靠性和数据一致性的应用场景如银行系统、电子商务等。 MyISAM: 不支持事务处理适合读取密集型应用对数据一致性要求不高的场景。 行级锁与表级锁: InnoDB: 采用行级锁可以更细粒度地控制并发访问减少了锁争用提高了并发性能适合写操作频繁的场景。 MyISAM: 使用表级锁当执行写操作时会锁定整张表导致在有写操作时其他读写操作必须等待不适合高并发写入场景。 数据存储与索引: InnoDB: 数据文件和索引文件存储在一起默认使用聚集索引数据和主键索引存放在一起支持外键约束有利于提高相关查询效率。 MyISAM: 数据文件和索引文件分开存储不支持外键非聚集索引索引文件只包含索引指向数据的指针这使得某些查询可能效率较低。 崩溃恢复: InnoDB: 支持自动崩溃恢复通过事务日志实现即使数据库发生异常关闭也能保证数据的一致性和完整性。 MyISAM: 崩溃恢复能力较弱如果数据库在写操作过程中崩溃可能会导致数据损坏需要手动修复。 索引 索引结构
MySQL的索引是存储引擎层实现的不同的存储引擎其实由不同的结构。
但是我们这里只对BTree 索引进行来介绍其他的还有Hash索引、R—Tree空间索引等等。 下面是一个B树的结构 一般这里会提出一个非常常见的问题为什么采用的是B树索引 相对二叉树层次更少搜索效率高。 相对红黑树因为红黑树是一种大致的平衡会导致树的高度变高性能下降所以不采用这种。 图出处JavaGuide 对于B树因为B树的叶子节点和非叶子节点都会存储数据导致一页中的数据存储较少只能通过增加树的高度来实现但这样会降低性能所以不推荐这个。 相对Hash索引B树支持范围匹配及排序操作。 索引分类
基本的索引有以下这些 主键索引 针对主键创建的索引 默认自动创建只有一个 关键字Primary 唯一索引 某列中数据不能重复 可以有多个 关键字unique 普通索引 快速定位特定属性数据 可以有多个 全文索引 全文索引是查找文本的关键字而不是比较索引的值 可有多个 fulltext 在InnoDB中根据索引存储形式可以分为下面两种 聚集索引将数据存储和该索引放到了一块索引结构的叶子节点保存的是行数据必须有且只有一个 二级索引数据存储与索引分块存储。索引结构的叶子节点关联的是对于主键可以有多个 个人理解可以将聚集索引理解成主键索引其他索引就是二级索引。 接下来由于二级索引的叶子节点关联的是主键而不是行数据这里会出现一个叫回表查询的知识点面试常问 前提我们已经对name这个创建了索引。
开始我们执行查询语句
select * from user where name Arm;
下面是执行过程这个过程称为回表查询 我们先根据name的二级索引查询到该索引的叶子节点中的数据——该数据的主键值 得到主键后我们根据主键再到聚集索引中查询到该行数据。 explain执行计划
explain命令获取MySQL如何执行Select语句执行过程中如何连接和连接顺序 比如说下面就是对一个sql语句的分析 索引使用
这里只挑选重点讲了可以去看我之前的文章在“MySQL个人总结——索引”篇章中有具体讲述下面仅个人的理解
最左前缀法则
最左前缀法则查询从索引建立时候的最左列开始并且不跳过索引的列。当跳过一列的时候索引将部分失效后面字段的索引失效 索引失效情况 范围索引联合索引中出现范围查询或)范围查询右侧的列索引失效。 如果使用 或 的话索引还是会存在。 索引列上进行运算操作 字符串类型字段不加引号 头部采用模糊匹配 类似这种语句 like %adf or连接的时候只有两端都有索引索引才生效 使用索引比全表查询慢不采用索引 覆盖索引
覆盖索引是什么查询使用了索引并且需要返回的列在索引中已经全部能找到。 为什么说要尽量使用索引覆盖 因为一般采用select * 的话容易出现回表查询导致查询效率变低 为了看看你是否理解了覆盖索引现在提出一个思考题 如果存在一张表有四个字段(id, username, password, status)由于数据量巨大需要对下面SQL语句进行优化该怎么进行设计才是最优方案呢 select id,username,password from user where username xxx; 回答 我们可以通过对username和password两个字段设置联合索引实现最优方案。 因为使用了这种方案我们可以直接进行一次的辅助索引就可以拿到username和password。一般查询的时候还会遵循最左前缀法则一般查询的都是username索引也不会失效。 索引设计原则
其实这个也是一个面试题可以将这个知识点改动一下
在工作中怎么来合理的设计索引呢 针对数据量大的、且查询比较频繁的表建立索引。 针对常作为查询条件where、order bygroup by操作的字段建立索引 尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高 如果字符串类型的字段字段的长度较长可以针对字段的特点建立前缀索引 尽量使用联合索引减少单列索引。查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率 要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价越大会影响增删改的效率 如果索引列不能存储NULL值在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含NULL值时可以更好的确定哪个索引最有效的用于查询 索引下推
这是一个补充的知识点纯个人理解
就是在执行二级索引的时候同时MySQL执行SQL语句中的条件判断将一些不符合条件的情况剔除最后进行回表查询的时候会减少一些不必要的数据使得效率更高。 二级索引查询过程当使用二级索引非聚簇索引通常索引非主键列进行查询时MySQL首先通过索引找到满足索引列条件的记录然后根据这些记录的主键值或聚簇索引键去聚簇索引中查找完整的行数据这个过程被称为“回表查询”。 索引下推优化在没有索引下推的情况下MySQL先通过二级索引定位到所有匹配索引列的行然后再对这些行进行条件过滤那些不在WHERE子句中的其他条件。而启用索引下推后MySQL能够在扫描二级索引的过程中就对WHERE子句中的其他条件进行判断直接在索引层就筛选掉不满足条件的记录减少需要回表查询的行数。 效率提升通过这种方式索引下推显著减少了需要从二级索引返回并进行回表查询的记录数量从而减少了磁盘I/O操作提高了查询效率尤其是在那些二级索引列匹配很多但经过进一步条件过滤后实际符合条件的记录较少的场景中效果尤为明显。 SQL优化
SQL优化实际上就是对索引的合理使用发挥出优势。
这里直接用一张图总结了如果要深度了解的可以看看之前写的“MySQL进阶---SQL优化” 插入数据 insert批量插入、手动控制事务、主键顺序插入、 大批量插入load data local infile 主键优化 主键长度尽量短顺序插入 AUTO_INCREMENT UUID order by优化 using index直接通过索引返回数据性能高 using filesort需要将返回的结果在排序缓冲区排序 gourp by 索引多字段分组满足最左前缀法则 limit优化 覆盖索引 子查询 count优化 count(*) count(1) count(主键) count(字段) update优化 尽量根据主键/索引字段进行数据进行更新 锁
作用保证数据一致性、完整性提供并发安全、控制访问顺序。 全局锁
先说说我对这个理解吧下面是简单讲述 全局锁就是将整个数据库锁起来只能进行读取操作。一般使用在全局备份的情况当备份的时候创建一个当前数据的视图类似隔离级别中的可重复读然后将这个视图中的数据进行备份 全局锁的概念
全局锁作用于整个数据库实例的一个锁它限制了对数据库的写入操作。
当全局锁生效时所有需要修改数据的事务或语句如INSERT、UPDATE、DELETE都会被阻塞直到锁释放。这意味着全局锁的主要目的是为了在特定操作期间保护数据库的一致性状态而不仅仅是为了支持读取。 使用场景
全局锁最典型的使用场景之一是数据库备份尤其是逻辑备份例如使用mysqldump工具。逻辑备份过程中为了保证备份数据的一致性通常需要在备份开始前锁定整个数据库防止备份过程中数据发生变化。 FLUSH TABLES WITH READ LOCK (FTWRL)
在MySQL中实现全局锁的一种常见命令是FLUSH TABLES WITH READ LOCK (FTWRL)。这个命令的作用是 关闭所有打开的表这会强制执行任何未提交的事务确保所有表处于一致状态。 对整个数据库加读锁阻止新的写操作但允许已有的读操作继续。
使用FTWRL进行备份的流程大致如下 执行FLUSH TABLES WITH READ LOCK获取全局读锁。 启动备份进程比如运行mysqldump。 备份完成后释放锁通过执行UNLOCK TABLES。 表级锁
这是我对表级锁的理解 表级锁分为表锁和元数据锁表锁的话就是直接锁主了一张表元数据锁的话就是当查询的时候可以默认的加上元数据锁对于读操作可以任意的进行但是当要进行对表结构修改的时候要等到之前读取的所有事务全部关闭的时候才可以拿到元数据锁同时在修改表结构的这个事务之后的请求都要等到这个修改的事务结束 表级锁
作用于整个表的锁。MySQL中的MyISAM存储引擎主要使用这种类型的锁。 表锁有两种模式 读锁Shared Locks / S-Locks允许其他事务读取被锁定的表但会阻止任何写操作包括更新、插入、删除。 写锁Exclusive Locks / X-Locks独占访问不仅阻止其他事务写入也阻止其他事务读取。当一个事务获取了表的写锁其他事务既不能读也不能写该表。 缺点粒度较大可能导致并发性能下降尤其是在高并发读写场景下 元数据锁MDLMetedata Locks
元数据锁是MySQL为保护表的元数据即表的结构定义如列定义、索引等而引入的一种锁机制适用于所有存储引擎。 主要用途 防止并发的DDL操作如ALTER TABLE、DROP TABLE相互冲突确保数据定义的一致性。 保护查询的一致性确保查询看到的数据结构不会在查询执行过程中被DDL操作改变。 这里是一个例子 session A先启动这时候会对表t加一个MDL读锁。由于session B需要的也是 MDL读锁因此可以正常执行。 之后session C会被blocked是因为session A的MDL读锁还没有释放而session C需要MDL写 锁因此只能被阻塞。 如果只有session C自己被阻塞还没什么关系但是之后所有要在表t上新申请MDL读锁的请求也 会被session C阻塞。前面我们说了所有对表的增删改查操作都需要先申请MDL读锁就都被 锁住等于这个表现在完全不可读写了。 总结事务中的MDL锁在语句执行开始时申请但是语句结束后并不会马上释 放而会等到整个事务提交后再释放 意向锁
这是一个补充的内容。
前提介绍意向锁是在事务准备获取表级锁或行级锁时加入的它们并不是实际的锁而是一种锁定意图的标识用于帮助协调并发事务之间的锁定操作。 原理
举一个例子说说意向锁的原理 当事务A想要对表中的任意行加行级锁共享锁S或排他锁X时InnoDB会自动在表级别加上意向锁如果是读操作则加意向共享锁IS如果是写操作则加意向排他锁IX。 如果事务B随后尝试对整个表加表锁X锁它不需要检查每行的具体行锁状态只需要查看表上的意向锁。如果发现表上有意向排他锁IX事务B就知道有其他事务打算或已经在表中的某些行上执行写操作此时事务B将被阻塞等待所有持有意向排他锁的事务完成。 这个机制有效地避免了事务B需要逐一检查表中每一行的锁状态从而提高了并发处理的效率。意向锁作为表级的信号锁提供了足够的信息来快速决定是否可以安全地授予表锁而无需深入到行级细节的检查。 什么时候会给表加上意向锁呢 意向锁是在事务试图获取行级锁之前由数据库系统自动在表级别加上的一种轻量级锁。它的主要目的是作为行锁的一个前置信号告诉其他事务该表内有行可能即将被锁定或者已经被锁定从而帮助快速判断是否可能存在锁冲突特别是在有事务尝试对整个表进行加锁操作时。 行级锁
在此之前我先要介绍一下两阶段锁协议这里通过举例说明 当一个A事务进行对id为12 的行数据进行修改的时候分别对两个数据加上了行锁只有等到事务结束后才会释放两个行级锁而不是不使用时将锁释放 等同下面 加锁阶段事务开始后当需要访问某个数据项时会先请求并获取相应的锁读锁或写锁。在这个阶段事务可以根据需要不断申请锁但不会释放任何已经获得的锁。 解锁阶段事务只有在所有操作完成准备提交时才会进入第二阶段开始释放所有之前获取的锁。这个阶段是不可逆的一旦开始释放锁事务就不能再申请新的锁。 死锁
由于我们知道当一个事务不会在开始的时候自动对所有要操作的行数据加上行锁而是动态的按需进行。所以就有可能出现死锁情况下面举例说明死锁 事务A锁定了表中的一行记录R1准备更新。 事务B锁定了表中的另一行记录R2也准备更新。 事务A接下来尝试锁定事务B已经锁定的记录R2因为需要执行某个逻辑或保持数据一致性。 事务B同时尝试锁定事务A已经锁定的记录R1原因类似。 这时事务A在等待事务B释放R2的锁而事务B在等待事务A释放R1的锁形成了相互等待的循环即死锁。 有两种策略可以解决死锁 设置进入等待超时时间 通过参数innodb_lock_wait_timeout来设置。 发起死锁检测发现死锁后主动回滚死锁链条中的某一个事务让其他事 务得以继续执行。 将参数innodb_deadlock_detect设置为on表示开启这个逻辑。 一般采用的是第二种策略主动检测死锁。 其实这里还是会出现一个问题就是说如果100万个并发线程要同时更新一行数据在执行的时候每个新来的线程都要进行死锁检测这样会消耗大量的CPU资源但是却执行不了不多的事务我们该怎么解决这个问题呢 问题的核心就是对并发的控制。 解决思路对相同行的更新在进入引擎前排队 具体实现利用消息队列Message Queue, MQ来实现对相同行更新请求的排队是一个有效的方法可以显著减少数据库层面的压力避免高并发导致的死锁检测开销 行锁
作用操作锁住对应的行数据锁定粒度最小发生锁冲突的概率最低 InnoDB引擎 逻辑存储结构 表空间ibd文件一个MySQL实例可以对应多个表空间用于存储记录、索引等数据。 段 分为数据段Leaf node segment、索引段Non-leaf node segment、回滚段Rollback segment。 InnoDB是索引组织表数据段就是B树的叶子节点索引段即为 B树的非叶子节点段用来管理多个Extent区 区表空间单元结构每个区的大小为1M。默认情况下InnoDB存储引擎页大小为16K即一个区中共有64个连续的页。 页是InnoDB存储引擎磁盘管理的最小单位每个页的大小默认为16KB。为了保证页的连续性InnoDB存储引擎每次从磁盘申请4-5个区。 行InnoDB存储引擎数据是按行进行存放的。 Trx_id每次对某条记录进行改动时都会把对应的事务id赋值给trx_id隐藏列。最后一次操作时id Roll_pointer每次对某条记录进行改动时都会把旧的版本写入undo日志中然后这个隐藏列就相当一个指针可以通过它来找到该记录修改前的信息。通过这个指针可以改动前的数据 事务原理
特性AICD 原子性Atomicity事务是不可分割的最小操作单元。 一致性Consistency事务完成时必须使所有数据都保持一致状态 隔离性Isolation数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行。 持久性Durability事务一旦提交或回滚它对数据库中的数据的改变是永久的。 事务隔离级别
当数据库上有多个事务同时执行的时候就可能出现脏读dirty read、不可重复读non- repeatable read、幻读phantomread的问题为了解决这些问题就有了“隔离级别”的概念。 读未提交是指一个事务还没提交时它做的变更就能被别的事务看到。 读提交是指一个事务提交之后它做的变更才会被其他事务看到。 可重复读是指一个事务执行过程中看到的数据总是跟这个事务在启动时看到的数据是一 致的。当然在可重复读隔离级别下未提交变更对其他事务也是不可见的。 串行化顾名思义是对于同一行记录“写”会加“写锁”“读”会加“读锁”。当出现读写锁冲突 的时候后访问的事务必须等前一个事务执行完成才能继续执行。
用视图的方式说 在“可重复读”隔离 级别下这个视图是在事务启动时创建的整个事务存在期间都用这个视图。 在“读提交”隔离级 别下这个视图是在每个SQL语句开始执行的时候创建的。 这里需要注意的是“读未提交”隔离 级别下直接返回记录上的最新值没有视图概念 “串行化”隔离级别下直接用加锁的方式来避 免并行访问。 redo log重做日志实现持久性 用户提交修改到Buffer Pool中并将数据页的变化写入到Redolog Buffer中。 当前页不会立即进行刷新而是有规律时间的刷新。前面讲过如果提交到Buffer Pool后页变成脏页了 Buffer Pool将内存中的信息存入磁盘中先将Redolog 信息先写入然后再是Buffer Pool中的信息 顺利进行插入成功。 如果失败就要用到Redolog中的保留的日志了通过日志重新写入数据。 系统会过期清理不需要的在磁盘上的redolog文件。 undo log回滚日志实现原子性
两个作用 回滚 MVCC
就是当用户进行一个delete记录时undo log中会多出一条之前行数据的记录用于进行回滚。 Undo log销毁undo log在事务执行时产生事务提交时并不会立即删除undo log因为这些日志可能还用于MVCC。 Undo log存储undo log采用段的方式进行管理和记录存放在前面介绍的rollback segment回滚段中内部包含1024个undo log segment。 MVCC
了解三个基本概念 当前读每次读取的都是当前数据库中的最新版本数据 快照读读取是某一个视图中的数据记录可能是之前事务开启的那个版本的 MVCC维护一个数据的多个版本使得在读写操作时没有冲突 在MVCC中我们要搞清楚下面这些概念 undo loginsert、update、delete的时候产生的便于数据回滚的日志 undo log 版本链不同事务或相同事务对同一条记录进行修改会导致该记录的undolog生成一条记录版本链表链表的头部是最新的旧数据尾部是最早的旧数据。 readView是快照读SQL执行时候创建的一个视图记录并维护当前活跃的事务id也可以说是未提交事务的id MVCC执行过程 事务开始当一个事务开始时InnoDB会为该事务分配一个唯一的事务IDtransaction ID这个ID在事务的整个生命周期中保持不变是MVCC机制中的重要标识。 数据读取 快照读在可重复读Repeatable Read隔离级别下默认的SELECT查询采用快照读即事务看到的是事务开始时数据库状态的一个快照。InnoDB通过read view读视图来确定事务能看到哪些数据版本。read view包含了当时活跃事务的ID列表事务只能看到这些ID之前提交的版本。就是说仅在事务第一次执行快照时生成一个ReadView之后都是复用这个ReadView 当前读对于需要读取最新数据的查询如SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE会执行当前读直接读取最新已提交的数据版本。一般是RC隔离级别下在事务中每次执行快照读都会生成ReadView 数据写入 当事务执行INSERT、UPDATE或DELETE操作时InnoDB并不会直接修改原数据而是 为修改前的数据生成undo log记录包含旧数据的完整信息用于事务回滚和历史版本查询。 在数据页中插入新的数据版本并标记旧版本为已删除但实际并未立即物理删除而是等待purge线程清理。 更新数据的隐藏列如row_id、事务ID、删除标记等以指向新版本和关联undo log。 事务提交与回滚 提交事务时InnoDB会记录事务的提交ID但已修改的数据版本并不会立即清理以维持 MVCC 的多个版本。 回滚事务时使用undo log恢复数据到事务开始前的状态。 版本清理 Purge线程会周期性地检查数据页根据当前活跃事务的read view和已提交事务的信息清理不再需要的旧版本数据和对应的undo log以回收空间。 通过了解了这些知识点后期我会总结一下对MySQL中常见的面试题的汇总个人感觉如果掌握了上述知识点了基本的八股文都可以拿下了。
