mysql面试题整理

2022/7/22 2:22:49

本文主要是介绍mysql面试题整理,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

1 myisam 和 innodb 引擎的区别

  • innodb 支持事务,外键,myisam 不支持
  • innodb 支持 mvcc ,myisam 不支持
  • innodb 支持表锁、行锁,myisam 仅支持表锁
  • innodb 必须有主键,myisam 不需要

2 mysql有几种锁

  • 按锁粒度划分有三种:表锁、页锁、行锁
  • 加锁机制:可分为 悲观锁和乐观锁
  • 兼容性:意向所 和 排他锁
  • 实现:记录锁、gap锁、next-key锁、插入意向锁

3 事务和锁

  • update、delete、insert 无论是否在事务,都加锁。保证数据的一致性
  • 快照读:读取的是快照版本,也就是 mvcc 历史版本 readView 里的数据 ,普通的 SELECT 就是快照读
  • 当前读:读取的是最新版本,UPDATE、DELETE、INSERT、SELECT ...  LOCK IN SHARE MODE、SELECT ... FOR UPDATE 是当前读,需要加锁
  • RC、RR、SERIALIZABLE 级别的隔离,当前读都会需要借助锁实现
  • RR 隔离级别需要先 select ... for update 加锁进行当前读操作,才能防止幻读
  • 对于SERIALIZABLE隔离级别的事务来说,InnoDB规定使用加锁的方式来访问记录

4 什么情况会加锁

  • update、delete、insert 无论是否在事务,都加锁。保证数据的一致性
  • 在事务里,for update、LOCK IN SHARE MODE 会分别加一个 排他锁和共享锁,直至事务结束
  • select 无论是否在事务,都不加锁

5 隔离级别,具体的实现原理是什么

  • 读未提交
  • 读已提交
  • 可重复读
  • 串行化
  • 原理
    • 事务就ACID四个特性
    • 原子性:是使用 undo log来实现的,如果事务执行过程中出错或者用户执行了rollback,系统通过undo log日志返回事务开始的状态
    • 持久性:使用 redo log来实现,只要redo log日志持久化了,当系统崩溃,即可通过redo log把数据恢复
    • 隔离性:通过锁以及MVCC,使事务相互隔离开
    • 一致性:通过回滚、恢复,以及并发情况下的隔离性,从而实现一致性

6 redo log 、 undo log 、 binlog

  • redo log:
    • innodb 为了提高磁盘I/O读写性能,存在一个 buffer pool 的内存空间,数据页读入会缓存到 buffer pool,事务的提交则实时更新到 buffer pool,
      而不实时同步到磁盘(innodb 是按 16KB 一页同步的,一事务可涉及多个数据页,实时同步会造成浪费,随机I/O)。
      事务暂存在内存,则存在一致性问题,为了解决系统崩溃,保证事务的持久性,我们只需把事务对应的 redo 日志持久化到磁盘即可
  • undo log:
    • 事务需要保证原子性,也是说事务中的操作要么全部完成,要么什么也不做。如果事务执行到一半,出错了怎么办-回滚。但是怎么回滚呢,靠 undo 日志。undo 日志就是我们执行sql的逆操作
  • binlog:
    • binlog指二进制日志,它记录了数据库上的所有改变,并以二进制的形式保存在磁盘中,它可以用来查看数据库的变更历史、数据库增量备份和恢复、MySQL的复制
  • 一条更新语句,redo log 、 undo log 、 binlog的对应更新顺序流程是怎样的
  • 数据的更新插入删除都是两阶段提交的,如果 redo 不是两阶段提交;
    • redo 先写,binlog 后写,会导致依赖 binlog 同步的从库数据缺失。
    • binlog 先写,redo log 后写,则会导致从库多出未提交的脏修改。主从库数据会不一致

7 double insert buffer 、redo log buffer 、change buffer

  • double insert buffer:数据页的更新需要两次写doubleWrite,其原因:重做日志是对页层面的物理操作与备份。如果磁盘页坏了的时候,那么用重做日志去重做是没有意义的,所以需要一个副本,在页损坏的时候,用副本页去还原原本的页,然后再进行重做日志
    * 其中doubleWrite 原理结构有一个 doubleWrite buffer 和 共享128表空间页的物理磁盘
  • change buffer: Change Buffer与 Insert Buffer一样,适用对象还是非唯一的辅助索引
  • redo log buffer 类似 buffer pool,它是申请出来的一片连续内存,然后里面划分出了N多个空的redo log block。redo log 刷新到磁盘的时机如下
    • 后台线程每隔1秒自动刷盘
    • 关闭 mysql 服务
    • 记录达到 log buffer 空间一半时刷盘
    • 做 checkpoint 的时候
    • 事务提交时会刷盘。数据库中 innodb_flush_log_at_trx_commit 参数就控制了在事务提交时,如何将 buffer 中的日志数据刷新到磁盘file中
      • 参数值为0:提交事务也不进行刷盘操作
      • 参数值为1:提交事务一次就刷盘一次( 默认刷盘策略)
      • 参数值为2:每次提交事务时,只会将buffer中的内容写入页面缓存中,不会在将页面缓存中的数据刷盘到file中

8 check point 是怎么确定的

  • Sharp CheckPoint : 数据库关闭时,将所有脏页刷新到磁盘,默认的工作方式。但如果运行时,也使用Sharp方式,会对数据库的可用性造成很大影响
  • Fuzzy CheckPoint :每次只刷新一部分脏页
    • Master ThreadCheckPoint master thread 会以一定的频率从缓冲池的脏页列表中刷新一定比例的页会磁盘,这个过程是异步的,不会阻塞查询
    • Flush_lru_list checkPoint
      • LRU列表要保证有差不多100个空闲页可用。
      • Page Cleaner线程中,会检查LRU列表中是否有足够的可用空间,没有的话,则移除LRU尾部的页,如果这些页中有脏页,则需要CheckPoint
    • Async/Sync Flush CheckPoint
      • 重做日志不可用的情况下,需要强制将一些页刷回到磁盘,而此时脏页是从脏页列表中选取的。若将已经写入到重做日志的LSN记为redo_lsn
      • 用来保证重做日志的循环使用。MySQL 5.6 以后,该操作移到了 Page Cleaner Thread 中,故不会阻塞用户的查询
    • Dirty Page too much CheckPoint
      • 总的来说,是为了保证缓冲池中有足够可用的页。InnoDB—max_dirty_pages_pct 标识当缓冲池中脏页的数量占据 75%时,强制 CheckPoint,刷新部分脏页回磁盘

9 mysql 优化的切入点

  • explain分析sql语句,查看执行计划,优化sql
  • 查看是否涉及多表和子查询,优化Sql结构,如去除冗余字段,避免返回不必要的数据
  • 优化索引结构,看是否可以适当添加索引
  • 数量大的表,可以考虑进行分库/分表
  • 数据库主从分离,读写分离
  • 查看mysql执行日志,分析是否有其他方面的问题

10 导致索引失效的几种情况

  • 查询条件含 or、is null、is not null、in、exists、not in、!=、<> 都可能会导致索引失效
  • 列是字符串类型,查询时条件没有使用引号扣起来
  • like 前模糊匹配
  • 在列字段使用内置函数,或者做加减乘除运算
  • innodb 估算走全表扫描比走索引快,大概是 1/3 ?
  • 连接查询,两个表用作关联的字段,其编码不一致
  • 联合索引,不遵循最左前缀原则

11 varchar(20) 20 有什么意义 和 char(20) 的20 又有什么不同

  • vachar(20) 指明其字段长度最大是 20,其长度可变
  • char(20),指明其字段长度是 20,长度不可变,多余部分空格填充
  • int(10),指明有效长度是10 ,多余部分前面用零填充

12 给字符串列建索引 需要注意什么

  • 选择固定前缀子串作为索引,可以节约空间,缺点可能需要多次IO扫描
  • 如果不是字符串全长度作为索引,在索引覆盖时仍需要回表查询
  • 固定前缀的离散值必须够大,不然导致索引失效
    • 倒序存储
    • 字符串计算hash值,建立一个hash值的索引列。范围查询时索引失效

13 有哪几种索引

  • 索引结构上可分为两种类型
    • 聚簇索引
    • 非聚簇索引,二级索引
      • 一个表中只能拥有一个聚集索引,而非聚集索引一个表可以存在多
      • 聚集索引,索引中键值的逻辑顺序决定了表中相应行的物理顺序;非聚集索引,索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同
      • 索引是通过二叉树的数据结构来描述的,我们可以这么理解聚簇索引:索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点,只不过有一个指针指向对应的数据块
  • innodb的索引语法分为五种
    • 主键索引:数据列不允许重复,不允许为NULL,一个表只能有一个主键
    • 唯一索引:数据列不允许重复,允许为NULL值,一个表允许多个列创建唯一索引
    • 普通索引:基本的索引类型,没有唯一性的限制,允许为NULL值
    • 全文索引:是目前搜索引擎使用的一种关键技术,对文本的内容进行分词、搜索
    • 组合索引:多列值组成一个索引,用于组合搜索,效率大于索引合并
  • innodb对索引的优化
    • 索引下推:是 MySQL 5.6 引入的, 可以在索引遍历过程中,对索引中包含的字段先做判断,
      直接过滤掉不满足条件的记录,减少回表次数
    • 最左前缀原则:MySQl建立联合索引时,会遵循最左前缀匹配的原则,即最左优先。如果你建立一个(a,b,c)的联合索引,
      相当于建立了(a)、(a,b)、(a,b,c)三个索引
    • 覆盖索引:只需要在一棵索引树上就能获取 SQL 所需的所有列数据,无需回表,速度更快

14 innodb 索引的底层实现

  • innodb 索引分为 B+树索引 和 hash索引。哈希索引是自适应索引,由innodb 是否建立,DBA无法干预
  • B+ 和 hash 索引的区别
    • B+树可以进行范围查询,Hash索引不能。
    • B+树支持联合索引的最左侧原则,Hash索引不支持。
    • B+树支持order by排序,Hash索引不支持。
    • Hash索引在等值查询上比B+树效率更高。
       - B+树使用like 进行模糊查询的时候,like后面(比如%开头)的话可以起到优化的作用,Hash索引根本无法进行模糊查询。

14.1 为啥使用B+作为数据索引,而不是二叉树,或者平衡树,B树

  • 二叉树在特殊情况会退化为链表,查询操作是O(n)
  • 平衡二叉树,树的高度太高。而mysql的数据持久化在磁盘,读取磁盘IO会非常多。而B+是矮胖型树,磁盘IO小很多
  • B树会在非节点存在键值和数据,innodb的页默认是16KB,其高度不比平衡二叉树低多少。而B+在非叶子节点只存键值,树高度很低
  • B+树的叶子节点会使用前后指针连起来,那B+树用来范围查找,排序,分组及去重都非常简单快速

15 now() 和 current_date() 有什么区别

  • now()精确到时分秒,current_date() 只精确日期

16 blob 和 text 的区别

  • Blob 用于存储二进制数据,而 Text 用于存储大字符串
  • Blob 值被视为二进制字符串(字节字符串),它们没有字符集,并且排序和比较基于列值中的字节的数值。 
  • text 值被视为非二进制字符串(字符字符串)。它们有一个字符集,并根据字符集的排序规则对值进行排序和比较

17 建立索引的原则是什么,是否越多越好,为什么

  • 数据量少的不适合加索引
  • 更新比较频繁的也不适合加索引
  • 区分度低的字段不适合加索引(如性别)
  • 创建索引和更新索引要耗费时间
  • 索引需要占物理空间,除了数据表占用数据空间之外,每一个索引还要占用一定的物理空间
  • 以表中的数据进行增、删、改的时候,索引也要动态的维护
  • 定义有外键的数据列一般要建立索引
  • 在order by或者group by子句中,创建索引需要注意顺序

18 连接查询,innodb 有哪几种优化机制,了解吗

  • Index Nested-Loop Join (NLJ)select * from t1 straight_join t2 on (t1.a=t2.a);;t2 的字段 a 上有索引
    • 从表 t1 中读入一行数据 R
    • 从数据行 R 中,取出 a 字段到表 t2 里去查找
    • 取出表 t2 中满足条件的行,跟 R 组成一行,作为结果集的一部分
    • 重复执行步骤 1 到 3,直到表 t1 的末尾循环结束
    • 在形式上,这个过程就跟我们写程序时的嵌套查询类似,并且可以用上被驱动表的索引,所以我们称之为“Index Nested-Loop Join”,简称 NLJ
  • Simple Nested-Loop Join select * from t1 straight_join t2 on (t1.a=t2.b);;t2 的字段 b 上没有索引
    • 因为 t2.b 没有索引,只能循环遍历比较。 如果 t1 有100行数据,t2 有 1000 行,则需要 100 * 1000 = 10 0000。
  • Block Nested-Loop Join
    • Simple Nested-Loop Join 太低效了,innodb 会优化成 Block Nested-Loop Join
    • 把表 t1 的数据读入线程内存 join_buffer 中,由于我们这个语句中写的是 select *,因此是把整个表 t1 放入了内存
    • 扫描表 t2,把表 t2 中的每一行取出来,跟 join_buffer 中的数据做对比,满足 join 条件的,作为结果集的一部分返回
    • Block Nested-Loop Join 算在内存操作,速度快很多
  • Multi-Range Read,这个优化的主要目的是尽量使用顺序读盘。
    • 根据索引 a,定位到满足条件的记录,将 id 值放入 read_rnd_buffer 中
    • 将 read_rnd_buffer 中的 id 进行递增排序
    • 排序后的 id 数组,依次到主键 id 索引中查记录,并作为结果返回
  • Batched Key Access(BKA),(BKA)是对 NLJ 算法的优化
    • 我们就把表 t1 的数据取出来一部分,先放到一个临时内存 join_buffer
    • 然后按照 MRR 思想批量去 第二张表 t2 根据 ID 顺序查询数据
  • BNL 转 BKA
    • select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000; t2.b 没有索引
    • 一些情况下,我们可以直接在被驱动表上建索引(或者使用临时表),这时就可以直接转成 BKA 算法了

19 buffer pool

  • 预读:用于异步将磁盘的页读取到buffer pool中,预料这些页会马上被读取到
  • 自适应哈希索引:提升热点等值查询sql的效率
  • double write buffer,保障在innodb崩溃的时候,也能保证数据的一致性
  • redo log buffer 保证 redo log 刷盘的效率

20 innodb 什么时候会用到临时表

  • sort buffer、内存临时表和 join buffer。这三个数据结构都是用来存放语句执行过程中的中间数据,以辅助 SQL 语句的执行的。
    其中,我们在排序的时候用到了 sort buffer,在使用 join 语句的时候用到了 join buffer
  • 临时表的建立有以下几个场景
    • union 执行流程
    • group by 执行流程
      • 尽量让 group by 过程用上表的索引,使用 explain 确认没有 Using temporary 和 Using filesort。此时优化成不使用临时表,不排序
      • 如果 group by 需要统计的数据量不大,尽量只使用内存临时表;也可以通过适当调大 tmp_table_size 参数,来避免用到磁盘临时表
    • group by 和 order_id 字段不一致时。( group by order_id order by id)
    • join 语句,order by 的列不在驱动表里
    • distinct + order by
    • from 里的子查询

21 mysql 的主从同步整个流程是怎样的

  • 主库的更新事件(update、insert、delete)被写到binlog
  • 从库发起连接,连接到主库
  • 此时主库创建一个binlog dump thread,把binlog的内容发送到从库。
  • 从库启动之后,创建一个I/O线程,读取主库传过来的binlog内容并写入到relay log
  • 还会创建一个SQL线程,从relay log里面读取内容,从 Exec_Master_Log_Pos 位置开始执行读取到的更新事件,
    将更新内容写入到slave的db
  • 异步复制、半同步和全同步
    • 主库执行完提交事务后,立刻异步执行将结果返给给客户端,并不关心从库是否收到并处理。
      如果出现从库并未收到处理的情况,还是会有主从数据不一致的问题
    • 主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到 relay log 中才返回给客户端。
      相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延迟
    • 当主库提交事务之后,所有的从库节点必须收到、并且提交这些事务,然后主库线程才能继续做后续操作

22 mysql 读写分离时,存在主从延迟,有哪几种策略解决

  • 网络延迟
  • 当sql在进行锁表时,可能会导致大量 sql 积压,未同步到服务器,此时有主从延时
  • 如果服务器硬件性能好,可以设置 sync_binlog = 1 , innodb_flush_log_at_trx_commit = 1
  • 读写分离减少主库压力
  • 如果读从库存在延迟
    • 强制读主库
    • 写入redis缓存中间件,从 redis 读取最新数据
    • 延迟读

23 自适应哈希索引(AHI)了解过吗,innodb 在什么情况会用到 hash index

  • MySql数据库中的索引B+树的一般高度为3~4层,所以如果用索引B+树查找的话一般要进行3-4次的查找
  • Innodb存储引擎会监控对表上二级索引的查找,如果发现某二级索引被频繁访问,二级索引成为热数据,建立哈希索引可以带来速度的提升
  • 自适应哈希索引是通过缓冲池的B+树页来构造的,因此建立的速度很快,不需要对整张表构建哈希索引
  • hash自适应索引会占用innodb buffer pool
  • 自适应hash索引只适合搜索等值的查询,如 select * from table where index_col='xxx',而对于其他查找类型,如范围查找,是不能使用的
  • 哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序

24 B+树,它的优点在哪

  • B+Tree 的根节点和枝节点中都不会存储数据,只有叶子节点才存储数据
  • B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针,它的最后一个数据会指向下一个叶子节点的第一个数据,形成了一个有序链表的结构
  • 扫库、扫表能力更强(如果我们要对表进行全表扫描,只需要遍历叶子节点就可以了,不需要遍历整棵 B+Tree 拿到所有的数据)
  • B+Tree 的磁盘读写能力相对于 B Tree 来说更强,同数据量下磁盘I/0次数更少(根节点和枝节点不保存数据区,所以一个节点可以保存更多的关键字,一次磁盘加载的关键字更多)
  • 范围查询和排序能力更强(因为叶子节点上有下一个数据区的指针,数据形成了链表)

25 最左前缀匹配,索引下推,索引覆盖

  • 索引下推:是 MySQL 5.6 引入的, 可以在索引遍历过程中,对索引中包含的字段先做判断,
    直接过滤掉不满足条件的记录,减少回表次数
  • 最左前缀原则:MySQl建立联合索引时,会遵循最左前缀匹配的原则,即最左优先。如果你建立一个(a,b,c)的联合索引,
    相当于建立了(a)、(a,b)、(a,b,c)三个索引
  • 覆盖索引:只需要在一棵索引树上就能获取 SQL 所需的所有列数据,无需回表,速度更快

26 数据库的乐观锁和悲观锁是什么,怎么实现

  • 悲观锁:当前线程在修改,其他线程阻塞等待。select for update 、select in share lock
  • 乐观锁:其他线程过来,先放过去修改。当前线程如果看到别的线程没修改过则修改成功,如果别的线程修改过则修改失败或者重试。
    读的时候带一个版本号,修改的时候版本等值比较,未改动则修改成功,不相等则失败

27 union all 和 union 有什么区别,哪个效率更好

  • Union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序
  • Union All:对两个结果集进行并集操作,包括重复行,不进行排序
  • UNION 的效率高于 UNION ALL

28 多表关联查询,有什么优化建议不

  • 减少子查询,使用 join 代替
  • in和not in也不慎重使用的话,会导致全表扫描
  • 使用exists而不是in是一个不错的选择
  • 避免索引失效

29 超大表数据分页,怎么处理

  • select from table where age > 20 limit 1000000,10这种查询其实也是有可以优化的余地的。
    这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢。
    可以修改为select from table where id in (select id from table where age > 20 limit 1000000,10)。
    这样虽然也load了一百万的数据,但是由于索引覆盖,减少不必要的回表查询
  • 如果使用的索引列 ID 是连续的,我们还可以 select * from table where id > 1000000 limit 10
  • 业务上限制翻页次数

30 group by 产生的数据是不是有序的

  • group by xxx 默认会按 xxx 排序
  • 如果不想排序可以加语句 order by null,选择不排序

31 group by 和 order by 怎么优化

  • group by 非执行时选取的索引列时,则额外使用临时表并默认排序,所以可以尽量 group by (index_x) 或者 group by xxx order by null
  • group by xxx 和 order by yyy 不同的列时,也会使用临时表,尽量使得 xxx == yyy

32 分表分库,怎么分

  • 分表方案(水平分表,垂直分表,切分规则hash等)
    • 垂直分表:把长度较大且访问不频繁的字段,拆分出来创建一个单独的扩展表 xxx_ext 进行存储
    • 水平分表:分成多个结构相同的表,而每个表只占原表一部分数据,然后按不同的条件分散到多个数据库中
      • 库内分表:库内分表虽然将表拆分,但子表都还是在同一个数据库实例中,只是解决了单一表数据量过大的问题,并没有将拆分后的表分布到不同机器的库上,还在竞争同一个物理机的CPU、内存、网络IO
      • 分库分表:分库分表则是将切分出来的子表,分散到不同的数据库中,从而使得单个表的数据量变小,达到分布式的效果
    • 切分规则
      • ID RANGE: 从0到10000一个表,10001到20000一个表
      • HASH取模: 例如电商的 order 和 order_ext 关联一个 user_id ,然后user_id hash取模,分配到不同的数据库上
      • 地理区域:比如按照华东,华南,华北这样来区分业务,七牛云应该就是如此
      • 时间:按照时间切分,就是将6个月前,甚至一年前的数据切出去放到另外的一张表,因为随着时间流逝,这些表的数据 被查询的概率变小,所以没必要和“热数据”放在一起
  • 分库分表一些问题(事务问题?跨节点Join的问题)
    • 分布式事务
    • 跨节点Join的问题
    • 分页、排序,分组的坑
    • 全局唯一主键问题
  • 分库分表中间件(Mycat,sharding-jdbc等)

33 MVCC了解过不,它的实现原理是怎样的的

image.png

  • mvcc 是一系列的 undo log 相连行成的版本视图。它的实现原理主要是依赖数据记录中
    DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID 3个隐式字段,undo log ,read View 来实现的
  • MVCC其好处是读不加锁,读写不冲突,并发性能好
  • 详解可以看看这篇文章:数据库篇:mysql事务原理之MVCC视图+锁

34 limit 100000 很慢怎么办

  • 如果id是连续的,筛选大于上次查询的最大记录ID,再往下limit
  • 限制翻页页数
  • order by 索引列。再做限制

35 百万级别或以上的数据,你是如何删除的

  • 我们想要删除百万数据的时候可以先删除索引
  • 然后批量删除其中无用数据
  • 删除完成后重新创建索引


这篇关于mysql面试题整理的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程