本文主要是介绍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的对应更新顺序流程是怎样的
![](/images/baidian.png)
- 数据的更新插入删除都是两阶段提交的,如果 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](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/a9a92d8727a84e1e9000393eda68cab5~tplv-k3u1fbpfcp-watermark.image)
- 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面试题整理的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!