MySQL面试总结

目录

MySQL逻辑架构

第一层与传统C/S 架构相似，包含：连接处理、授权认证、安全等。

第二层是MySQL的核心服务 包括：查询解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数）。以及所有的跨存储引擎的功能：存储过程、触发器、视图等。

第三层是存储引擎。存储引擎负责MySQL中数据的存储和提取。服务器通过API和存储引擎进行通信。
不同的存储引擎对外暴露统一封装好的API提供调用，调用者不需要关注底层实现，不同存储引擎之间也不会相互通信，而只是简单的响应上层的服务器请求。

InnoDB和MyISAM的差异

  1. 事务 InnoDB支持，MyISAM不支持。
  2. 外键 InnoDB支持，而MyISAM不支持。
  3. 索引B+叶子阶段存储的信息不一样，InnoDB是聚簇索引叶子节点存储的是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。
  4. InnoDB不保存表的具体行数，执行select count(*) 需要全表扫描。而MyISAM用一个变量保存了整个表的行数。[InnoDB 无内置变量是因为不同事务下的行数不一样]
  5. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁。[InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。update ... where 没命中索引会锁整个表]
  6. InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有

MyISAM：写操作是锁表[排他锁],读操作是共享锁，支持在读的时候插入数据，读性能优秀,不支持事务，崩溃后修复困难。

Select语句的执行过程

  1. 客户端发送一条查询给服务器
  2. 服务器先查询缓存,如果命中了缓存,立刻返回存储在缓存中的结果。否则进入下一阶段。
  3. 服务器进行SQL解析，预处理，染回优化器生产对应的执行计划。
  4. MySQL 根据优化器生成的对应的执行计划，调用存储引擎的API来执行查询。
  5. 将结果返回给客户端。

事务的基本特性

ACID 原子性/持久性/隔离性/一致性

一致性：一个事务必须使数据库从一个一致性状态变换到另一个一致性状态[语义上的合法状态]，原子性/持久性/隔离性都是为了来保障一致性的。

ACID实现原理

  1. C 一致性：
    • 数据库层面：数据库通过A原子性、I隔离性、D持久性来保证一致性。
    • 应用层：通过代码保障逻辑上的一致，人为决定是否回滚和提交事务。
  2. A 原子性： 事务要么成功要么失败，不允许部分成功失败，通过undo log回滚来保障。
  3. D 持久性： 持久是通过redo log
  4. I 隔离性： 锁+MVCC机制来保障不同事务之间不出现相互影响。

MySQL隔离级别

读未提交/读已提交/可重复读/串行化

MySQL默认是 可重复读，实际项目中一般使用读以及提交

  • 在RR隔离级别下，存在间隙锁，出现死锁的概率比RC的大。
  • 在RR隔离级别下，列条件没中索引会锁表，RC这种情况下锁行。

脏读/不可重复读/幻读

  1. 脏读：读出来其他事物还未提交的数据
  2. 不可重复读：在同一个事务中，针对相同的行，2次查询的数据内容不一致。
  3. 幻读： 在同一个事务中的 相同条件的 多次查询出现了行的增减。

不可重复读侧重的是数据内容的修改，幻读侧重的是数据行的增删。

redolog/undolog/binlog

Redolog：来记录某数据块被修改后的值，可以用来恢复未写入 datafile 的已成功事务的数据。[事务At提交成功 但未写入datafile，系统重启，重启后会根据redolog执行剩下的更新操作]

Undolog：也就是我们常说的回滚日志文件 主要用于事务中执行失败，进行回滚，以及MVCC中对于数据历史版本的查看。

binlog:是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志文件。

MVCC机制

  • trx_id 记录最新修改该行的事务号
  • roll_pointer 存储的是指针指向上一个版本的位置信息.[指向undoLog，插入操作时无上个版本 该字段为空]
  • ReadView 用来存储当前活跃的事务[记录的是trx_id]，也就是开始还未提交的事务。如果当前行的trx_id小于Rv中的最小值，说明事务以提交当前行可展示，若trx_id在RV的范围内，说明当前行正在进行事务中，不能被展示，则根据roll_point找到上一行的信息，并且判断trx_id和RV的关系来辨别是否可以展示，若不可以则重复以上操作。

RC级别下每次查询都会使用一个最新的ReadView，RR级别下第一次读会生成一个ReadView，之后的读都复用之前的ReadView。

MVCC只在RR RC2个级别下工作，Read Uncommit 总是读取到最新的行而不是符合当前事务版本的数据行，Serializable会对读取的所有行加锁，也用不上多版本并发控制.

快照读和当前读

  1. 快照读：读取的是记录数据的可见版本（可能是过期的数据），不用加锁
    • 一般select是快照读，所以在事务A中修改数据，在事务B中读取该数据依然能够读取，是因为读取的快照。而不是违背了X锁的原则。
    • 事务A还未提交，那么select出来的数据都是read view版本链中的数据，不会因为其它事务的提交或者未提交影响事务对同一张表的查询结果（遵循多版本并发控制规则）
  2. 当前读： 读取的是记录数据的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录。
    • update、insert、delete 当前读

MySQL的锁

  1. Intention Locks(意向锁)

    • 直接根据意向锁是否存在而判断是否有锁冲突。可以避免进行数据扫描到具体行来判断是否存在锁冲突。

  2. 共享锁/排他锁

  3. Gap Locks(间隙锁)：间隙锁可以被多个事务同时锁定。

    • 只在RR & Serializable 2个级别下生效。

  4. Next-Key Locks : 索引记录上的记录锁定和索引记录之前的间隙上的间隙锁定的组合。[ record lock + Cap lock 锁住当前记录及记录前后最近的间隙防止其他事务插入数据出现幻读的现象。]

  5. Record Locks(记录锁)：也就是常说的行锁。

  6. 表级别锁:

    • Lock Table...Read / Lock Table...Write 方式开启表锁。
    • 意向锁Intention Locks也是表级锁。
    • 其他默认行级锁。

官方文档上标注的锁

锁的开启方式

  1. for update: 加上排他锁，持有排他锁的事务可以进行读写，其他事务不可以进行读写。
    • 另一个非锁定读的操作来读取该行那么数据不会阻塞，读取的是该行的快照版本，而不是当前版本，当前版本读会被阻塞。
    • 指改行之前的数据版本,也就会出现脏读，所以也称为非锁定读，因为不需要等待被访问行的锁的释放。非锁定读的方式极大提高了数据库的并发性。在InnoDB存储引擎中，这是默认的读取方式。
  2. lock in share model: 加上共享锁，持有意向锁的事务可以进行读写，其他事务可读不可写。

索引的数据为什么是B+树

MySQL查询效率中有个很关键的指标 磁盘IO次数，且磁盘IO一次读出的数据量是固定的，B-数非叶子节点上比b+数多了数据域data，会使当前IO下的磁盘IO次数增多，且B+数的所有数据都在叶子节点且存在指向下一个叶子节点的指针便于范围查询，B-树的范围查询需要遍历整个树。

红黑树一般是在内存中使用的数据结构，若用在数据库索引中会出现数的深度过大导致的磁盘IO读写过于频繁。

  1. Hash索引 ： 使用hash表实现的，无法保障顺序性，区间查询无法使用索引，需要扫描全表。等值查询的效率较高。
  2. 二叉查找树：可以解决排序问题，极端情况下会退化为链表。
  3. 平衡二叉树：旋转操作效率低
    • 非叶子节点最多拥有两个子节点
    • 非叶子节值大于左边子节点、小于右边子节点
    • 树的左右两边的层级数相差不会大于1
    • 没有值相等重复的节点
  4. B树 ：B树和平衡二叉树稍有不同的是B树属于多叉树又名平衡多路查找树（查找路径不只两个）
  5. 红黑树： 对严格的平衡做了取舍和引入红黑节点，解决了平衡二叉树旋转效率过低的问题，树依然太深在磁盘场景下IO次数太多不符合预期。

AVL树/红黑树

红黑树和AVL树都是最常用的平衡二叉搜索树，它们的查找、删除、修改都是O(lgn) time

AVL树和红黑树有几点比较和区别：

  1. AVL树是更加严格的平衡，因此可以提供更快的查找速度，一般读取查找密集型任务，适用AVL树。
  2. 红黑树更适合于插入修改密集型任务。
  3. 通常，AVL树的旋转比红黑树的旋转更加难以平衡和调试。

总结：

  1. AVL以及红黑树是高度平衡的树数据结构。它们非常相似，真正的区别在于在任何添加/删除操作时完成的旋转操作次数。
  2. 两种实现都缩放为a O(lg N)，其中N是叶子的数量，但实际上AVL树在查找密集型任务上更快：利用更好的平衡，树遍历平均更短。另一方面，插入和删除方面，AVL树速度较慢：需要更高的旋转次数才能在修改时正确地重新平衡数据结构。
  3. 在AVL树中，从根到任何叶子的最短路径和最长路径之间的差异最多为1。在红黑树中，差异可以是2倍。
  4. 两个都给O（log n）查找，但平衡AVL树可能需要O（log n）旋转，而红黑树将需要最多两次旋转使其达到平衡（尽管可能需要检查O（log n）节点以确定旋转的位置）。旋转本身是O（1）操作，因为你只是移动指针。

索引失效

  1. 模糊匹配前边带百分号 %like
  2. 索引列参与计算,使用了函数
  3. 非最左前缀顺序
  4. 使用is not null 或者 is null
  5. 使用不等于(!= 或者<>) [<>走全表扫描一般会更快]
  6. or操作有至少一个字段没有索引
  7. 回表查询的结果集过大 [超过配置的最大范围]

explain命令

  1. id:选择标识符
  2. select_type:表示查询的类型
  3. table:输出结果集的表
  4. partitions:匹配的分区
  5. type:表示表的连接类型
  6. possible_keys:表示查询时，可能使用的索引
  7. key:表示实际使用的索引
  8. key_len:索引字段的长度
  9. ref:列与索引的比较
  10. rows:扫描出的行数(估算的行数)
  11. filtered:按表条件过滤的行百分比
  12. Extra:执行情况的描述和说明

select_type/查询类型

  1. SIMPLE(简单SELECT，不使用UNION或子查询等)
  2. PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
  3. UNION(UNION中的第二个或后面的SELECT语句)
  4. DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
  5. UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
  6. SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
  7. DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
  8. DERIVED(派生表的SELECT, FROM子句的子查询)
  9. UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

type

表示这个查询访问数据的方式，或者说MySQL查找行的方式，MySQL手册中称为连接方式。
常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

Extra

MySQL where语句的几种应用方式

从好到坏依次为：

  • 在索引中使用Where来过滤不匹配的记录 这是在存储引擎层完成的
  • 使用索引覆盖来扫描所需要的数据[Extra 中出现using index],直接从索引过滤不需要的记录并返回命中结果，这是在MySQL服务器层完成的事情.
  • 从数据表中返回数据，然后过滤不满足要求的记录[Extra列中出现 Using where]，这是在MySQL服务器层完成的事情，MySQL需要先从数据表读出记录然后过滤。

where后可以使用聚合函数吗

不能，聚合函数也叫列函数，是基于确定的结果集的计算函数. where是对数据行进行过滤的。where的执行顺序在聚合函数的前面，处于确定结果集的过程中，聚合函数依赖where筛选的确定的结果集进行的计算。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/threecha/p/14016759.html