mysql 面试总结

最新推荐文章于 2024-07-16 13:36:52 发布

yue_xin_tech

最新推荐文章于 2024-07-16 13:36:52 发布

阅读量835

点赞数

分类专栏：面试 mysql 文章标签： mysql 面试数据库

本文链接：https://blog.csdn.net/h_l_f/article/details/122851944

版权

mysql 同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

面试

5 篇文章 0 订阅

订阅专栏

1. 事务

ACID 特性

原子性(Atomicity)：事务是一个不可分割的单位，因此在一个事务里的所有操作要么全部生效，要么全部不生效。
一致性(Consistency)：也可以理解为是预期状态的正确性，即从一个正确的状态到另一个正确的状态，这里的状态往往是由业务来定义的。比如转账中的一个扣钱一个加钱，是我们规定的一个数据流转，那么执行前的账户余额和转账后的账户余额就得满足加减特性，这就是所谓的业务正确。
隔离性(Isolation)：事务并发执行时，各个事务之间相互影响的程度。
持久化(Durability)：通过日志等手段，只要我们的事务提交成功了，那么就意味着这次的数据操作是成功的。即使下次重启了程序，也不会丢失此处的操作结果。

隔离级别

未提交读： 即所谓的脏读，事务读取的数据可能是另一个事务已修改但还没提交的，这部分数据有可能产生回滚。导致后续的操作依赖了无效的数据。
已提交读： 如果想防止脏读，就需要等待其他事务提交后再进行读取操作。
可重复读： 已提交读的隔离级别考虑到了数据回滚的无效性，却无法阻止事务的多次提交。比如事务 A 不断的对表进行修改提交，那么事务 B 就会在不同的时间点读取到不同的数据。为了让事务 B 在执行期间读取的数据都是一致的，就有了可重复读的隔离级别，即事务 B 在执行期间，其他事务不得进行修改操作。
可串行化： 上面的可重复读隔离级别保证了事务执行期间读取的一致性。然而这里并不包括插入、删除操作。即会出现读多读少数据的情况，这种现象叫做幻读。为了解决幻读，只得进行串行化执行事务，才能互不影响。而此时的事务并发性是最低的

2. 索引

Mysql 的索引分类

从数据结构划分： B+ 树、hash 索引、全文索引
从物理结构划分： 聚集索引、非聚集索引
从逻辑用户划分： 主键、唯一索引、复合索引、普通单列索引

聚集索引、非聚集索引、主建的区别

聚集索引：在索引的叶子节点直接存 data 数据, 使用的是物理排序，一个表只能有一个字段设置了聚集索引（因为物理排序规则只能有一个），允许有 null 的数据存在，数据也不一定是唯一的。
主建：唯一标识某行记录，不允许有 null 的数据，要求数据必须唯一。在设置某个字段为主建时，数据库一般会自动在这个主建上建立一个唯一索引，并且如果之前表没有创建过聚集索引，还会在这个主建上建立一个聚集索引。
非聚集索引：索引的叶子节点存的是数据域的记录指针，需要跳转查找。排序规则是逻辑排序，因此可以有多个非聚集索引存在。

有哪些情况会让索引失效？

在 where 字段上使用了函数或其他隐式转换
Like 模糊查询，开头使用了 “%”，例如 like ‘%hello%’
where 条件里使用了 or
建立了复合索引，但 where 条件里使用的是第二个字段的搜索

最左匹配原则是指？

mysql 建立联合索引后，是按最左匹配原则来筛选记录的，即检索数据是从联合索引的第一个字段来筛选的。如果 where 里的条件只有第二个字段，那么将无法应用到索引。

索引的底层数据结构 B+ 树是怎么样的？

B+ 树是二叉搜索树的一个扩充，是多路搜索树。它只在叶子节点存储具体的数据或者数据的指向指针，而非叶子节点存放索引数据。这样可以降低磁盘 IO，还能充分利用磁盘的预读功能，批量的加载索引数据。

B+ 树

b 树 b+树 b-树的区别

b 树就是 b-树，国外叫 b-tree, 也就是 b 树。
b-tree 是在非叶子节点存放了数据，在查询索引时，只要找到索引值也就可以找到数据了，这样可以提前终止搜索。但每个节点就得存储索引值+数据值，占用的页空间会比较大，需要的磁盘 io 次数也会变多，即使是不需要关心的数据也会被预加载出来，浪费性能。
b+树是将索引值存在非叶子节点，数据值存在叶子节点，这样可以压缩树的高度，减少磁盘 io。

为什么不能在重复率高，例如`性别`字段上建立索引？

对于性别这种索引，由于重复率高，对于 B+树(多路搜索树)来讲，得遍历多条路径，搜索代价大。还不如全表扫描，这样不需要维护索引，降低开销。

Mysql 的 hash 索引是怎么样，有什么优缺点？

hash 索引将列通过 hash 运算得到 hash code，然后将 hash code 跟数据行的指针地址关联在一起，下次查找时只需查找对应 hash code 的数据行地址即可。

hash 索引非常的紧凑，查找速度很快，适用于内存存储引擎的应用。不过它只能精确查询，不支持范围查找，也不能直接进行排序。限制还是挺多的。

hash 索引

Mysql 的全文索引

全文索引主要是用于文档查找，像我们可能会从多篇文章中查找包含某些词语的文章，这时就可以使用全文索引了。虽然 like 也可以使用，但是效率太低了。全文索引在接收到文档时，会对它进行分词处理，以获取到关键词。然后会将关键词和属于这个文档的 id 关联起来。下次查找，就会先到关键词列表里找到关联的文档 id ，最后利用文档 id 去查找到文档数据。

3. 日志

日志类别

binlog： 二进制日志，记录了数据库对数据的修改记录，包括了 DDL：例如表的创建，数据更新等。但并不包括 select 这些查询语句。binlog 日志是属于逻辑语句的记录，可用于主从数据库的同步。
relay log： 中继日志，用于主从备份恢复使用的。有主服务器的 binlog 逻辑操作语句，以及当前的恢复位置。
慢查询日志： 记录在 mysql 里执行时间超过预期值的耗时语句
redo log： redo log 是对加载到内存数据页的修改结果的记录，和 binlog 不同的是，binlog 记录的是逻辑操作语句，偏向于过程记录。而 redo log 是一个数据页的修改日志，偏向于结果的记录。
undo log： 回滚日志主要用于回滚数据，和 redo log 不一样的是，undo log 是逻辑日志，是一种相反操作的记录，比如在回滚时，如果是 insert 操作时，则会逆向为 delete，delete 操作时，逆向为 insert 操作，更新则恢复到当时的版本数据。

redo log 相关概念：writepos、checkpoint、prepare、commit

redo log 是用来记录当前数据页的修改情况，由于性能问题，每次修改并不会实时同步到硬盘。而是先在内存中修改，然后将修改情况记录到 redo 里，再定时的去将 redo 刷新到硬盘里。因此，redo log 有 2 个位置，一个是 writepos，自己写日志的位置；另一个是 checkpoint，是定时的将数据页同步到硬盘的位置。

redo log 在写 binlog 日志前会先记录 redo log，记录完后标记为 prepare 状态。当 binlog 也写入完成后，才将 redo log 标记为 commit 状态。只有当 redo log 是 commit 状态时，事务才能真正的 commit。这样能防止主从节点根据 binlog 同步有可能事务不一致的情况。

4. Mysql 里的锁

Mysql 里的锁有哪些？

乐观/悲观锁

乐观锁：在读取数据时会假设各个事务互不影响，它们会处理好属于自己的那部分数据。如果在更新数据时，发现有其他事务修改了属于自己的数据，则会回滚之前的一切操作。
悲观锁：采取了先获取锁再访问的保守策略，如果已经有其他事务获取了锁，则必须等待锁释放才能继续。

共享/排它锁

共享锁：又称读锁，当前事务在读取时，允许其他事务并发读取，但不允许其他事务上排它锁，必须等自己释放了才能继续。
排它锁：又称写锁，在写锁占有时，如果其他事务想上读写锁，则得排队等待。

表锁/行锁

表锁：在操作数据时，直接将整张表锁住，操作粒度很大，很容易让其他事务在等待，但不会产生死锁。
行锁：针对的是行记录的并发控制，锁粒度很细，能支持高并发，但是不排除会有死锁情况产生。在 mysql 里行锁依赖索引实现，如果没有索引存在，则会直接进行表锁！

行锁

记录锁：只锁住某一条记录。当对唯一索引(包括主键)进行精确查询时，会使用记录锁。
间隙锁：当使用范围查询时，会对符合条件的区间数据上锁。在涉及到普通索引（即不是唯一索引）的查询时，都会使用间隙锁。
Next-key 锁：临建锁，可以理解为记录锁 + 间隙锁。当对唯一索引进行范围查找或对唯一索引进行查找但结果不存在时（可以理解为锁住不存在的记录），会使用临建锁。

上面的间隙锁、临建锁有效的防止了事务幻读情况产生，避免了在查找期间有数据新增或删除。

意向锁

表锁的一种，它仅仅表示一种操作意向。当我们使用粒度比较小的行锁时，在检测是否有锁时，需要一行一行的检查，效率较低。有了意向锁之后，则不需一行一行的排查，只需检测对应的意向锁即可。

事务里锁的应用是怎么样的？

可重复读

可重复读使用的是 MVCC 快照，所以在读取数据时大多数时候不需要使用锁。

但使用了 UPDATE, DELETE，或 SELECT with FOR UPDATE（排它锁）或 FOR SHARE（共享锁），则会根据下面的情况来使用锁：

在唯一索引上精确查找某条记录时，使用记录锁
对于其他的搜索，InnoDB 将会锁定扫描到的索引范围，使用间隙锁或临建锁来防止幻读的产生

读提交

也是使用 MVCC 机制来读取数据，不过在使用 UPDATE, DELETE，或 SELECT with FOR UPDATE（排它锁）或 FOR SHARE（共享锁）时和上面的机制不一样，当存储引擎将筛选到的记录交给 mysql server 层后，会对不相干的数据进行解锁，所以不会涉及间隙锁或临建锁。它们只会在做外键约束检查和重复键检查时使用到。由于间隙锁的禁用，可能会出现幻读现象。

未提交读

在 mysql 的 innodb 存储引擎里做 SELECT操作不会做任何锁动作，如果是 myisam 存储引擎，则会上共享锁。
如果使用UPDATE, DELETE，或 SELECT with FOR UPDATE（排它锁）或 FOR SHARE（共享锁）则和读提交一样的原则。

可序列化读

可序列化读在使用 select 时，一般会自动的转化为 SELECT … FOR SHARE（共享锁），以保证读写序列化。

lock in share mode 和 for update 里间隙锁什么时候会应用？

lock in share mode, for update 如果 where 条件是非索引类的，则不会加间隙锁；
lock in share mode, for update 如果 where 条件是主键类的，并且找不到记录时会加间隙锁；如果找到记录了则会将间隙锁给释放了。比如 where 主键 = 3 能找到记录时则不会加间隙锁，找不到时会在该数据的前后叶子节点间加间隙锁；此时假如记录里只有 1，8，9，则会在 1, 8 之间加间隙锁
lock in share mode, for update 如果 where 条件是非聚集索引类的，会加间隙锁，即使找不到记录。

锁超时的配置

当 mysql 获取锁超时时候，如果系统变量 innodb_rollback_on_timeout 为 off ,则当前事务只会回滚最后一条 sql, 所以建议设置 innodb_rollback_on_timeout 为 on, 这样在获取锁超时时可以回滚全部 sql。

5. MVCC 是指什么？

MVCC 即多版本并发控制，它利用了 undo log 会在数据修改时保留上一个修改记录指针的特点，使得每个事务对数据的修改能有自己的历史版本追溯，就像镜像备份一样。当进行读操作时，如果有其他写操作的事务并发进行，那么此时可以根据事务的隔离级别选择读取最新版本亦或自己之前版本的数据。MVCC 不需要加锁的，它能提高事务的并发处理能力。

6. mysql 的复制技术

全同步复制：只有等所有的 slave 节点将同步的日志写入 relay log，并且响应 ack 确认后，此次的事务才会提交。数据完整性高，但性能低
半同步复制：只要有一个 salve 节点响应 ack 后就可以认为同步成功，但细分为了两种，一种是 AFTER_COMMIT：先在主库提交事务, 然后同步从库, 等待从库的 ack 确认. 才告诉客户端是否 Ok。另一种是 AFTER_SYNC：主库先不提交事务, 只有从库有 replay log ,回复了 ack 后才进行提交事务。后面一种数据一致性较高
异步复制：一旦有需要复制的就通知 slave, 但不会等待确认成功才进行后续操作。

7. 存储引擎

Mysql 存储引擎有哪些以及特点？

InnoDB： 它是 mysql 的默认存储引擎，能够实现 ACID 特性的事务，并且能提交、回滚、恢复数据，能很好的保障用户数据。同时支持了行级锁、聚集索引以及外键约束，是一个完善的存储引擎。
MyISAM： 是 mysql 最开始的存储引擎，占用空间小，能快速存储，但不支持事务，提供了基于表级别的锁粒度，适用于配置或只读功能的应用程序。
Memory： 数据都是存在内存里的，能提供快速访问，不过应该较少人使用，毕竟一旦断电数据也就丢失了。
CSV： 带有逗号分隔值的文本文件，没有索引存在。但是兼容性很好，可以跟其他的程序交换数据。