MySQL 大纲-面试题

最新推荐文章于 2024-07-25 11:32:53 发布

Damon-Rui

最新推荐文章于 2024-07-25 11:32:53 发布

阅读量60

点赞数

文章标签： mysql java 面试

本文链接：https://blog.csdn.net/weixin_41613490/article/details/131392941

版权

MySQL 大纲-面试题

基本架构

Server层

连接器
- 连接器负责跟客户端建立连接、获取权限、维持和管理连接。
查询缓存
- 连接建立完成后，你就可以执行select语句了，此时会先进行查询缓存(缓存是key-value格式；key是sql语句，value是sql语句的查询结果)。
分析器
- 词法分析：MySQL需要识别出里面的字符串分别是什么，代表什么。
- 语法分析：根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法
优化器
- 优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
执行器
- 调用存储引擎接口，执行sql语句，得到结果

存储引擎

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎
类型
- InnoDB
- MyISAM
- BDBD
- Memory
- Merge
InnoDB与Myisam区别
- InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
- InnoDB支持外键，而MyISAM不支持。
- InnoDB是支持表锁和行级锁，MyISAM只支持表锁
- InnoDB 属于聚簇索引，Myisam属于非聚簇索引
存储引擎的选择
- 默认采用InnoDB
- Myisam：比读为主的应用程序，比如博客系统、新闻门户网站
- InnoDB：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

缓存

好处

使用缓存好处，当mysql接受到客户端的查询sql 后，仅仅只需要通过权限认证后，就会查询出来结果，甚至都不需要经过分析器、优化器、数据存储的交互。
mysql 5.7支持内部缓存，8.0之已废弃

mysql缓存限制

mysql甚至没有手段灵活的管理缓存失效和生效，尤其对于频繁更新的表
Sql必须完全一致才会导致命中缓存
为了节省内存空间，太大的result set 不会被cache
Mysql缓存在分库分表的环境下是不起作用的
执行sql里有触发器，自定义函数时，Mysql缓存也是不起作用的
在表的结构或者数据发生改变时，基于该表的cache 立即全部失效

替代方案

在应用层使用 redis ecache，并且redis的读写性能也是很强的

索引

概念

索引（index）是帮助MySQL高效获取数据的数据结构（有序）

创建索引

在创建表的时候创建索引
使用cteate index语句进行索引创建

索引约束

唯一索引
全文索引
空间索引

索引类型

B-Tree索引
HASH索引

索引失效场景

违背了最左前缀法则
范围查询：范围查询右边的列，不能使用索引
列运算：不要在索引列上进行运算，索引将失效
字符串：字符串不加单引号，造成索引失效
模糊查询：以%开头的like模糊查询，索引失效，如果是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

事务

事务的特性

原子性：就是上面说的,要么全部成功,要么全部失败，不可能只执行一部分操作。
一致性：系统(数据库)总是从一个一致性的状态转移到另一个一致性的状态,不会存在中间状态
隔离性: 通常来说:一个事务在完全提交之前,对其他事务是不可见的.注意前面的通常来说加了红色,意味着有例外情况。
持久性：一旦事务提交,那么就永远是这样子了,哪怕系统崩溃也不会影响到这个事务的结果。

并发事务

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。（读取到过程中的值）
丢失修改（Lost to modify）：指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。(这样第一个事务内的修改结果就被丢失，因此称为丢失修改)
不可重复读（Unrepeatableread）：指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况
幻读（Phantom read）：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，

事务隔离级别

未提交读(READ UNCOMMITTED)：这个隔离级别下,其他事务可以看到本事务没有提交的部分修改。因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚)。这个级别的性能没有足够大的优势,但是又有很多的问题,因此很少使用.
已提交读(READ COMMITTED)：其他事务只能读取到本事务已经提交的部分。这个隔离级别有不可重复读的问题，在同一个事务内的两次读取,拿到的结果竟然不一样,因为另外一个事务对数据进行了修改。
REPEATABLE READ(可重复读)：可重复读隔离级别解决了上面不可重复读的问题(看名字也知道)，但是不能完全解决幻读。MySql默认的事务隔离级别就是：REPEATABLE READ
SERIALIZABLE(可串行化)：这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用。

redolog、binlog、undo-log

redo log

作用就是用于数据异常宕机的恢复工作，如果不发生宕机，永远用不到
redo log是一个环，大小是固定的，文件个数和大小是可以配置的。其中有两个指针，write和checkpoint，分别对应可以写入的位置，和已经刷新到磁盘的位置
随着事务操作的执行，就会生成Redo Log，在事务提交时会将产生Redo Log写入Log Buffer，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入到磁盘之后，Redo Log的使命也就完成了，Redo Log占用的空间就可以重用（被覆盖写入）
持久化策略
- 延迟写 0
  - 事务提交时，不会立即写入os buffer，而是每秒写入os buffer，在调用 fsync 函数将数据写入 redo log file 中。（肯可能会丢失1秒数据）
- 实时写，实时刷 1
  - 事务提交时，将log立即写入os buffer，并同时调用该fsyns 函数，将log 刷新到redo log file中。（不会丢失任何数据）
- 实时写，延时刷 2
  - 事务提交时，将log写入os buffer，每隔1秒将os buffer 中的数据 fsync 刷新到 redo log file中。

bin log 持久化策略

Row 模式
- 优点：能清楚记录每一行数据的修改细节，能完全实现主从数据同步和数据的恢复，而且不会出现某些特定情况下存储过程和func 无法被正确复制的问题
- 缺点：批量操作，产生大量的log，尤其是alter table会让日志暴涨
Statment模式
- 优点：日志量小，减少磁盘IO，提升存储和恢复速度
- 缺点：在某些情况下会导致主从数据不一致，例如now()
fixed 模式
- 以上2中模式混合使用，一般使用statement模式保存binlog，对于statement 模式无法复制的操作采用row模式保存binlog，Mysql会根据执行sql语句选择写入模式

redo log 和binlog的区别

redo log是innoDB存储引擎特有的；binlog是server层实现，所有引擎都可以使用。
redo log 物理日志，记录在某个数据页的修改，binlog是逻辑日志
redo log是一个环，循环写，binlog是追加写不会覆盖以前的日志
redo 用于异常重启恢复，binlog 用于备份

undo log

意为撤销或取消，以撤销操作为目的，将数据返回到某个状态的操作
Undo Log在事务开始前产生；事务在提交时，并不会立刻删除UndoLog，innodb会将该事务对应的undo log放入到删除列表中，后面会通过后台线程purge thread进行回收处理。Undo Log属于逻辑日志，记录一个变化过程。例如执行一个delete语句，Undo Log会记录一个insert语句；执行一个update语句，Undo Log会记录一个相反的update语句。

索引底层原理

1. 索引简介

索引是帮助MYSQL 高效获取数据的排好序的数据结构

2. 索引数据结构

二叉树
- 优点：(左小右大)，方便查询
- 缺点：如果索引值单相增长的话，二叉树就变成链表结构，查询某一个key的话，查询次数增加
红黑树
- 优点：红黑树（二叉平衡树），防止了索引值单边增长情况下，变为链表的情况下
- 缺点：当索引值上百万、千万的情况下，树会变的很深，会增加了查询频率，磁盘IO的消耗
B-Tree
- 叶节点具有相同的深度，叶节点的指针为空
- 所有索引元素不重复
- 节点中的数据索引从左到右依次递增排列
B+Tree(B Tree 变种)
- 非叶子节点不存储data，只存储索引，可以放更多的索引
- 叶子节点用双向指针连接，支持范围查询，提高取件访问的性能
- 叶子节点包含所有索引字段
- 根节点常驻内存
Hash表
- 针对索引列求Hash值（MD5，CRC16），从而定位到某列的数据
- 当是范围查找数据的话，A<1000 & A>10 ，效率会打折扣

3. mysql存储引擎（表级别）

Mylsam存储引擎
- 表文件
  - tableName.frm: frame 表结构框架的文件
  - tableName.MYD：表数据文件.存放表中的数据
  - tableName.MYI：表索引文件，使用B+Tree来实现索引，每个索引对饮的一条数据的地址
- 索引实现（非聚集）
  - MyISAM索引文件和数据是分离的(非聚集)
  - 根据索引对应的val(数据指针)，再根据指针来另一个文件查找对应的数据
Innodb存储引擎
- 标文件
  - tableName_lock.frm: frame 表结构框架的文件
  - tableName_lock.ibd：存放的是表数据和索引，索引对应的值和数据表的其他字段
- 索引实现（聚集）
  - 表数据文件本身就是按照B+Tree组织的一个索引结构文件
  - 聚焦索引: 叶节点包含完整的数据记录
  - 为什么InnoDB表必须有主键，并且推荐使用自增主键？
    - 1.因为InnoDB需要使用B-Tree来组织索引结构(主键索引)
    - 2.如果不自己设定主键索引，InnoDB会自动选择一列重复值较少的列所谓主键索引。如果不存在这样的列，会自动创建一个隐藏的列来维护索引
  - 为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）
结论
- Innodb(聚集索引) 的索引效率比MyISAM(非聚集索引)的高。
- auto_increment
  - 优点
    - 字段长度较uuid小很多，可以是bigint 类型或者int 类型，这个对检索的性能有影响
      
      在写的方面，因为是自增的，主键趋势自增，数据永远在后面，性能有很大提升
    - 数据库自动编号，速度快由于是增量增长，按照顺序存放对于检索非常有利
    - 数据型占用空间小易排序在程序中传递也方便
  - 缺点
    - 由于是自增，很容易通过网络爬虫知晓当前的业务量
    - 在高并发的情况下，竞争自增锁会降低数据库的吞吐能力
    - 数据迁移或者分库分表场景下，自增方式不在适用
- uuid
  - 优点
    - 不会冲突，进行数据拆分合并存储的时候能保证主键全局的唯一性(雪花算法)
    - 可以在应用层生成，提高数据库吞吐能力
  - 缺点
    - 影响插入速度，并造成硬盘适用率低，与自增相比最大的缺陷就是随机io
    - 字符串类型相比整形类型肯定更消耗空间，而且会比整形类型操作慢

4. mysql索引优化和数据结构

索引最左前缀原理：索引是根据在第一个字段相同的情况下，比较的第二个字段，在第二个字段相同的情况下，比较第三个字段。如果没有第一个字段，直接从第二个字段比较就是无序的，不走索引。

5. B-Tree和B+Tree的区别

B-Tree
- 优点：B树可以在内部节点存储键值和相关记录数据，因此把频繁访问的数据放在根节点的位置将大大提高热点数据的查询效率。
- 缺点：B树中每个节点不仅包含数据的key值，还有data，所以当data数据比较大的时候，会导致每个节点存储的key值减少，并且导致B树的层数变高增加查询时的IO此号
- 适用场景：b树主要用于文件系统以及部分数据库索引
B+Tree
- B+Tree是B-Tree的变种，B Tree 能解决的问题，B+Tree也能解决（降低树的高度，增大节点存储数据量）
- B+Tree扫库和扫表能力更强，如果我们需要根据索引进行数据表的扫描，对B tree进行扫描，需要吧整棵树遍历一遍，而B+tree只需要遍历叶子结点（叶子节点之前有引用）
- B+Tree 磁盘读写能力更强他的根节点和直接点不存储数据区，所有根节点和支节点同样大小的情况下保存的关键字要比B Tree要多。而叶子节点不保存子节点引用。所以 B+Tree 读写一次磁盘加载的关键字更多。比B Tree
- B+Tree 排序能力更强，B+Tree天然具有排序功能
- B+Tree查询效率更加稳定每次查询数据查询IO 次数一定是稳定的。