MySQL面经整理

MySQL面经整理一、MySQL存储引擎1.1MySQL支持哪些存储引擎默认使用哪一个1.2MyISAM和InnoDB的区别二、MySQL 事务2.1事务的四大特性ACID2.2事务并发带来哪些问题不可重复读和幻读的区别脏读不可重复读幻读2.3MySQL 事务隔离级别默认是什么级别RURCRR串行化2.4MySQL的隔离级别是基于锁实现的么2.5 InnoDB对MVCC的具体实现InnoDB聚簇索引记录中的隐藏列Undo Log 保存历史版本Read View(一致性视图)三、MySQL索引3.1为什么索引能够提高查询效率3.1.1红黑树3.1.2 Hash3.1.3 B-树3.2聚簇索引和非聚簇索引的区别非聚簇索引一定回表查询么3.2.1 为什么非聚簇索引存储主键值而不是数据地址?3.3最左匹配法则怎么查看某条SQL语句是否使用了索引一、MySQL存储引擎1.1MySQL支持哪些存储引擎默认使用哪一个MySQL采用插件式存储结构不同的存储引擎负责数据的存储和读取方式因此他们在事务支持、锁机制、性能等方面不同。通过show engines可以查看当前数据库支持的存储引擎。Mysql5.5之后默认存储引擎就是 InnoDB。1.2MyISAM和InnoDB的区别首先InnoDB支持事务能够保证数据一致性MyISAM不支持事务。其次InnoDB支持行级锁和MVCC而MyISAM只支持表级锁因此在高并发写入场景下InnoDB的性能更好。第三InnoDB支持外键约束可以维护表之间的数据完成性而MyISAM不支持外键。第四InnoDB 具有崩溃数据恢复能力即使数据库异常宕机也可以通过Redo Log恢复数据。而MyISAM不支持。此外InnoDB使用聚簇索引数据存储在主键索引叶子节点中MyISAM使用非聚簇索引索引和数据分离存储。二、MySQL 事务2.1事务的四大特性ACIDMySQL事务具有ACID四大特性原子性事务中的操作是一个不可分割的整体要么全部成功要么全部失败通过Undo Log实现。一致性事务执行前后数据库始终保持合法状态。隔离性 多个事务并发执行时互不干扰主要通过锁机制和MVCC实现。持久性事务提交后的数据永久保存即使数据库宕机也不会丢失通过Redo Log 实现。2.2事务并发带来哪些问题不可重复读和幻读的区别事务并发执行时可能产生三类问题脏读读取到其他事务未提交的数据不可重复读同一事务多次读取同一条记录结果不一致幻读同一事务中多次查询符合条件的记录记录数量发生变化。MySQL通过事务隔离级别来解决这些问题。InnoDB默认采用RR(Repeatable Read可重复读)隔离级别结合MVCC和Next-key Lock 机制解决大部分并发一致性问题。幻读是查询结果集的行数发生变化不可重复读是同一行数据值被修改。脏读一个事务读取到了另一个事务未提交的数据如果对方回滚那么读取到的数据就是无效的。比如事务A修改了id 1的一条数据但是并未提交事务。此时事务B读取的id 1 的这条数据之后事务A回滚了修改的数据从未真正存在过却被事务B读取到了。不可重复读同一事务中两次读取同一条数据由于其他事务提交了更新操作导致两次读取结果不同。比如 事务A开启第一次读取id 1的数据此时事务B开启修改了id 1的这条数据并提交事务。事务A再次读取id 1的数据两次结果不一样。幻读同一个事务中两次按照相同条件查询数据由于其他事务插入或删除了满足条件的记录导致查询结果集的数量发生变化。比如一张用户表idage120221事务A开启事务查询age 18的所有数据结果有2条。接着事务B开启事务插入一条数据322并提交事务。此时事务A再次查询 age 18 的所有数据结果有3条。2.3MySQL 事务隔离级别默认是什么级别MySQL事务隔离级别有四种读未提交Read Uncommitted读已提交Read Committed, 可重复读Repeatable Read)串行化Serializable。RU最低的隔离级别会产生脏读RC解决了脏读但是会出现不可重复读和幻读问题RR解决了脏读和不可重读读的问题MySQL InnoDB默认的隔离级别Serializable隔离级别最高但是性能最差另外需要注意按照 SQL 标准可重复读仍可能出现幻读但 MySQL InnoDB 通过 MVCC 和 Next-Key Lock 机制在大多数场景下避免了幻读。这样兼顾了数据一致性和并发性能。RU最低的隔离级别。一个事务可以读取到其他事务尚未提交的数据脏读。RC只能读取已经提交的的数据。解决了脏读问题。RRMySQL InnoDB默认的隔离级别。保证同一事务内多次读取结果一致。解决了脏读、不可重复读的问题。SQL标准下可重复读不能完全解决幻读但MySQL InnoDB通过MVCC和临键锁机制在很大程度上避免了幻读问题。串行化最高的隔离级别。所有事务排队执行。解决了脏读、不可重复读、幻读问题但是性能最差。2.4MySQL的隔离级别是基于锁实现的么不完全是。InnoDB 的事务隔离级别主要通过 MVCC 和锁机制共同实现。Read Committed 和 Repeatable Read 的一致性读主要依赖 MVCC通过 Read View 读取数据快照而为了解决幻读问题Repeatable Read 在当前读场景下会结合 Next-Key Lock。最高级别 Serializable 则主要依赖锁机制实现事务串行执行。因此不能简单地说 MySQL 的隔离级别完全基于锁实现。2.5 InnoDB对MVCC的具体实现InnoDB聚簇索引记录中的隐藏列trx_id 最后修改该记录的事务IDroll_pointer 指向undo log的指针row_id 隐藏主键没有主键时生成Undo Log 保存历史版本举个例子初始数据id 1 namejack; trx_id 1事务2update user set name tom where id 1更新后当前数据id 1 , name tom trx_id 2 roll_pointer -- undo1,undo1name jack trx_id 1如果之后事务3再修改update user set nameLucy where id1;,当前数据nameLucy trx_id3undo log :undo2 → nameJack trx_id101 ↓ undo1 → nameTom trx_id100Read View(一致性视图)数据存在多个版本但是当前事务应该看到哪个版本Read View来解决这个问题。其中Read View中比较重要的几个字段m_ids :创建Read View 时当前系统中活跃事务ID的集合。min_trx_id:活跃事务中最小事务的ID。max_trx_id:下一个将要分配的事务ID。creator_id创建Read View的事务ID。判断规则假设某个版本的trx_id xx min_trx_id ,说明事务在生成Read View前已经提交。该版本可见x max_trx_id ,不同隔离级别下Read View的生成时机RC 每一次执行select语句都会创建Read ViewRR 第一次快照读生成Read View后续都复用这一份Read ViewxInnoDB 的 MVCC 是通过隐藏字段、Undo Log 和 Read View 实现的。每条记录都会保存一个 trx_id 和指向 undo log 的 roll_pointer。数据更新时不会覆盖旧数据而是把旧版本保存到 undo log 中通过 roll_pointer 形成版本链。当执行快照读时会生成一个 Read View里面记录当前活跃事务集合 m_ids、最小事务 ID 和最大事务 ID。查询时根据记录版本的 trx_id 和 Read View 的可见性规则判断该版本是否可见如果不可见就沿着 undo log 继续查找旧版本直到找到满足条件的版本。在 Read Committed 隔离级别下每次查询都会生成新的 Read View在 Repeatable Read 下只在第一次快照读时生成 Read View因此能够保证可重复读。普通 select 属于快照读会使用 MVCC而 select for update、update、delete 等当前读则通过加锁机制保证并发安全。三、MySQL索引3.1为什么索引能够提高查询效率MySQL索引的本质是一种帮助数据库快速定位数据的数据结构。通过减少磁盘IO和需要扫描的数量量提高查询速率。没有索引时MySQL只能从第一条开始逐条查询这种方式成为全表扫描。建立索引后MySQL会通过B树结构查找。MySQL 索引能够提升查询效率本质上是因为它利用 B 树这种数据结构建立了键值和数据之间的映射关系使数据库不需要进行全表扫描而是能够快速定位到目标记录。由于 B 树是多叉平衡树树高很低百万甚至亿级数据通常只需要 3~4 次磁盘 I/O 就能完成查找从而将查询复杂度由 O(n) 降低到 O(log n)。同时 B 树叶子节点有序并通过链表连接因此还能高效支持范围查询和排序操作。为什么选择B树优势在哪里3.1.1红黑树红黑树是一种二叉平衡树每一个节点最多只有两个子节点。而B树是多叉树每一个节点可以存储上千个key。同等数据量下红黑树的树高要远高于B树的树高。3.1.2 HashHash索引通过哈希函数定位等值查询很快O(1),但是不支持范围查询、排序。3.1.3 B-树B-树和B树的最大区别 B-树非叶子节点存储key 和data.B树的非叶子节点只存储Key不存储data所有数据都存储在叶子节点中叶子节点通过双向链表连接。存储效率因为B树的非叶子节点不存储数据所以数据量相同的情况下B树的每一个节点可以存储更多的Key比B-树更”矮胖“。查询效率在单点查询中B树所有元素查询效率一致必须查询到叶子节点。而B-树只要找到匹配元素即可。在范围查询中B-树需要通过复杂的中序遍历来确定左右边界的位置。因为B树叶子节点及建立了双向链表只需要找到左边界的位置而后遍历链表即可。因此B树的相较于B-树的优势IO次数更少查询效率更稳定范围查询效率更高。3.2聚簇索引和非聚簇索引的区别非聚簇索引一定回表查询么首先从定义入手什么是聚簇索引什么是非聚簇索引聚簇索引索引和数据放在一起叶子节点存储完整的数据一张表只能由一个聚簇索引。非聚簇索引 索引和数据分开叶子节点保存的是索引列值主键值一张表可以由多个非聚簇索引。查询过程因为聚簇索引叶子节点保存的完整的数据所以当找到叶子节点后即可返回。而非聚簇索引得到的是主键值则需要再次根据主键值通过聚簇索引查询数据这个过程就是回表.也有例外覆盖索引。当索引列就是要查询的列时则不需要进行回表操作。在MySQL InnoDB中主键默认就是聚簇索引而普通索引、唯一索引等都是非聚簇索引。聚簇索引和非聚簇索引最大的区别在于叶子节点存储的内容不同。聚簇索引的叶子节点直接保存完整的数据行因此索引和数据存储在一起一张表只能有一个聚簇索引而非聚簇索引的叶子节点保存的是索引列值和主键值查询时通常需要先通过非聚簇索引找到主键再根据主键到聚簇索引中查找完整数据这个过程称为回表。InnoDB 中主键索引就是聚簇索引普通索引、唯一索引等都属于非聚簇索引。如果查询的字段都包含在非聚簇索引中则可以利用覆盖索引避免回表提高查询效率。3.2.1 为什么非聚簇索引存储主键值而不是数据地址?避免数据移动导致大量索引失效InnoDB的数据存储在聚簇索引中数据的改动可能会导致数据存储的物理地址发生改变。如果存储物理地址改动一条数据且引发物理地址改变则所有个这条数据有关的二级索引都要 修改。保证事务和MVCC的一致性。统一访问方式。3.3最左匹配法则定义联合索引在使用的时会从最左侧的字段开始匹配只有连续匹配才能利用索引中间不能跳过某一列。失效场景对索引列进行计算、函数操作隐士类型转换不符合最左匹配法则范围查询后面的列失效like “%XXX”怎么查看某条SQL语句是否使用了索引可以通过 EXPLAIN 查看 SQL 的执行计划判断 SQL 是否使用了索引。重点关注 key、type、rows 和 Extra 等字段。如果 key 不为空说明实际使用了索引如果 type 为 ALL通常表示进行了全表扫描没有利用索引。key实际使用索引type:ALL : 全表扫描index : 全索引扫描range : 范围查询ref 等值查询eq_ref 唯一索引等值匹配possible_keys 优化器认为可能使用的索引