总结的一些MySql面试题

目录

一：基础篇

二：索引原理和SQL优化

三：事务原理

四：缓存策略

---

一：基础篇

1：定义：按照数据结构来组织、存储和管理数据的仓库；是一个长期存储在计算机内的、有组织的、可共享 的、统一管理的大量数据的集合；

2：MySql中含有内部连接池用来管理缓冲用户的连接，以及线程处理需要缓存的需要。并且使用IO多路复用的select + 阻塞IO。并且他的命令处理是在多线程中并发处理的。

3：数据库三范式：先保持原子性不可分割 / 确保表中的每列和主键完全依赖 / 确保每列和主键直接相关不是间接相关。但是有时候没有必要使用三范式，会导致数据库业务涉及到的表变多，造成更多的连表查询，导致整个性能降低。因此需要考虑反范式。

4：五大约束：非空约束，自增约束，唯一约束，主键约束包括非空且唯一，外键约束。其中外键约束在 innodb 中支持，并且满足事务。

5：删除数据

drop（DDL）：速度快，删除整张表结构和表数据，包括索引，约束，触发器等。但是删除后不能回滚，会进行释放空间。

truncate（DDL）：速度比较快，会删除表数据，其他字段会保留（自增字段置为1），但是也不能回滚，会释放空间，以页单位进行删除。

delete（DML）：速度慢，删除部分字段或者全部字段，其他保留，他是一个条件删除，可以进行回滚，是标记删除（实际并未删除），可以进行逐行删除。

6：去重是group by ，select distinct 。条件判断是：where，group by ... having , ...join ... on ... 。三种

7：视图：视图是逻辑表，自身不含数据，内容是通过查询得到。安全：使用视图，用户只能看到他们被允许查询到的结果集，而对表的权限管理无法做到限制某些行和列，视图却可以。数据独立，源表中的数据修改，对视图没有影响。视图可以减少重复语句的书写，并且还是个重构利器：如果需要将一个表进行拆分成两个表，我们可以使用视图来进行操作，创建出两个视图，这样我们只需要更改数据库结构，而不用修改应用程序。

8：触发器：触发器的执行是由事件自动触发，而不是程序调用和手工启动。比如当DML操作的时候，就会激活执行。

二：索引原理和SQL优化

MySql索引原理和SQL优化_mysql 全文索引sql-CSDN博客

1：索引是什么：索引是一种有序的存储结构，它将单个或多个列的值进行排序。

2：索引分类：主键索引，唯一索引，非空索引，普通索引，组合索引，全文索引。在主键索引的B+数种中包含数据的全部信息。全文索引是将存储在数据库中的整本书整篇文章的任意内容信息查找。关键词是：FULLTEXT。

3：为什么使用B+数索引，而不使用红黑树结构？

        首先B+数相对于高瘦的红黑树来说，B+树是胖矮的，在叶子节点中存放数据，并且叶子节点还串联在一起，而非叶子节点中存放索引信息。无论是叶子节点还是非叶子节点都是存放在页中的。这样当我们查找数据的时候，我们会拥有更少的磁盘IO，而且更方便范围查找，因为叶子节点是串联在一起的，找到第一个叶子节点后，可以相继找出其他节点。但是对于红黑树来说，需要不断进行搜索。并且每个索引都对应一个B+树。

为什么采用多路的树结构？一个节点有多条链路，相较于平衡二叉搜索树是一个更加矮胖的结构，树的高度更低，可以较少的磁盘io次数来索引数据。

为什么非叶子节点只存储索引信息？B+树节点映射固定的大小磁盘数据，可以包含更多的索引信息。能快速锁定数据所在叶子节点的位置。

为什么叶子节点依次相连？便于范围查询，避免中序遍历回溯回去查找下一个节点。

4：什么时候使用索引呢？

        首先我们的索引使用的位置是在where，group by ，order by 的后面使用索引。那么不适合使用索引：就是没有这一些判断条件以及区分度不高的列（数据很相似的），需要经常修改的列，表数据量少的。我们创建B+树类型的索引就是为了通过比较来找到我们所需要的数据，但是当区分度不高的时候，反而会降低速度，如果经常修改这个列，那么我们的B+的结构就要经常变化，更加影响速率，表的数据较少的时候，没有必要去创建索引，创建索引反而会浪费空间。

5：索引方式：

每一个索引都有一个B+树结构。

聚集索引：我们主键构造出的B+树，叶子节点中存放数据，数据也是索引的一部分。并且主键索引就是聚集索引，没有主键那就是唯一索引作为聚集索引。

辅助索引（二级索引）：我们在辅助索引B+树中，叶子节点并不是存储的数据，而是存储的主键id。当通过辅助索引查找到主键id后，我们再根据主键去查找聚集索引。这里也叫回表查询。
覆盖索引：是索引包含了查询所需的所有列，即索引本身能够覆盖查询的字段需求，无需再通过回表操作来获取数据。通过使用覆盖索引，可以提高查询性能，减少不必要的磁盘I/O和数据传输。也就是我们不要select * 而是select 。。。什么的。
最左匹配规则：对于组合索引来说，我们使用必须要满足从左往右的规则：id，name，age。这三个只能使用：id，name，age；id，name；id；这三种方式来使用索引。

6：索引的失效和索引原则

索引失效：

1：当where  A and B 的时候，其中一个没有索引，那么就会失效。

2：当索引字段参与了运算。

3：索引字段发生隐式转换。

4：LIKE模糊匹配，通配符以“%”开头。

5：在索引字段上使用NOT <> ！= 

6：组合索引中，没有满足最左匹配。

索引原则：

1：查询频次较高且数据量大的表建立索引，索引选择使用频次较高，过滤效果好的列或者组合。

2：使用短索引，这样节点包含的信息多，有较少的磁盘IO操作。

3：很长的动态字符串，考虑使用前缀索引。

4：组合索引中，考虑最左匹配原则，和覆盖索引。

5：选择区分度高的列，也就是值相同的越少越好。

6：扩展索引，在现有索引的基础上，添加复合索引，最多6个索引。

7：不要select * ，尽量列出所需要的列，方便使用覆盖索引。

8：索引列尽量设置为非空

9：可以开启自适应hash索引。

三：事务原理

MySql 事务原理 - 面试_面试题 mysql事物实现原理-CSDN博客

1：事务

事务是什么：本质是一个并发的控制单元，用户定义的一个操作序列，这些操作要么都做要么都不做，满足原子操作。

什么时候使用事务：并发连接访问的时候。

事务的目的：从一种一致性状态转变成另一种一致性状态的时候，保证系统始终处于一个完整且正确的状态。

2：ACID特性中的隔离性

脏读，不可重复读，幻读。以及持久性中使用redolog写入物理日志，写入磁盘。

undolog：通过MVCC记录事务DML操作提交后产生的行数据版本信息。记录DML操作步骤，用于回滚业务，通过逆运算回滚。

redolog：事务提交后，记录DML操作对应物理页修改的内容。

3：隔离级别

read_uncommitted（读未提交）：读（不做任何处理），写（自动加X锁）。脏读、不可重复读、幻读。

read_committed（读已提交）（RC）：读（通过MVCC，读取最新版本的数据），写（自动加X锁）。不可重复读、幻读。

repeatable_read（可重复读）（RR）：读（通过MVCC，读取开启事务前的行数据），写（自动加X锁）。幻读。

serializable（可串行化）：读（自动加S锁），写（自动加X锁）。

4：隔离级别的并发异常

脏读：一个事务读到另一个未提交事务修改的数据。

不可重复读：一个事务内两次读取同一个数据不一样。一个事务提交之后，另一个事务中也会发现这个被修改的变量。

幻读：一个事务内两次读取同一个范围内的记录得到的结果集不一样，当前读和快照读不一致。我们在一个事务中插入一个数据，我们另一个事务在查询的时候并未看到这个数据，但是插入的时候发现报错。

我们数据库默认的隔离级别就是RR，也就是可以发生幻读。我们可以不用提升隔离级别就能解决这个问题，那就是手动加锁。具体加锁操作看文章即可。

5：MVCC

MVCC是多版本并发控制，保证数据的一致性和并发性。可以使多个事务在同时访问数据的时候，各自看到不同版本的数据，不会互相打扰，这样就可以避免锁和等待了。

read_view 是一个事务开始时创建的视图，他决定了事务能够看到的数据库版本中的数据版本，其中包括自身的事务id，已启动但未提交的事务id列表。

其中聚集索引记录的隐藏列存储在行中，这个行中包括该事务id。以及数据会指向之前的数据版本，用于回滚操作。

6：事务的可见性问题

事务可以看到自身事务的修改。事务之间的可见性是：已经提交的事务是可以看到的，后启动的事务是不可见的，在事务列表中已启动但未提交的不可见，已提交可见的。

7：RC和RR的区别

因为RC和RR都采用了MVCC，但是RC是读已提交，每次读取数据就会产生一个read_view。而RR是开启事务的时候才会生成一个read_view，一直用到事务结束，也就是一直能看到一个数据版本。因此RC每次读都能看到最新数据，所以产生了不可重复读，而RR中解决了。

8：什么是快照读和当前读：快照读就是从之前拍摄的一个快照中进行读取数据，而当前读就是直接读取最新数据。快照读并未采用锁，当前读加锁了。

四：缓存策略

MySQL的缓存策略_mysql 数据库的缓存策略-CSDN博客

1：MySQL 缓存方案用来干什么？

首先是MySql的缓存方案，他自己的缓存方案和业务层面是没有关系的，虽然MySQl也是用来缓存热点数据的，但是这些热点数据并不是用户自定义的，而是索引，记录等。他的缓存是从它自身出发的。

2：redis缓存方案：redis是内存数据库读取速度十分快，因此我们采用redis作为我们的缓冲数据库，用来缓存用户定义的热点数据，用户直接从缓存中读取热点数据，降低数据库的读写压力。而MySql是主要作为数据的落盘。

3：提升MySql访问性能的方式

1：读写分离：利用MySql的主从复制，我们设置多个从数据库，一个主数据库，我们进行主从复制的操作，然后主数据库主要负责写的操作，而其他从数据库负责读的操作。这样读写分离之后，会降低主数据库读的压力。但是当碰到对于读的时效性很强的时候，我们不得不读取主数据库了。

2：连接池：我们MySql中存在连接池的组件，会开启多个线程去服务这几个连接。有了连接池可以大大提高并发访问数据库的能力。并且网络模型为select + 阻塞IO模型。我们当开启事务的时候，我们要保证这事务中的sql语句全部在一个线程中执行。

3：异步连接：可以采用非阻塞IO的方式，也就是异步方法。

4：热点数据的读写策略（redis）

1：安全为主

 如果要以安全为主，我们就要避免主数据库和从数据库读取的数据不同的问题。当我们先写入MySQL后，必然会出现MySQL与Redis数据不同的问题，那么我们就不能先写入MySQL。而是要先删除Redis中的数据，然后再写入MySQL，最后将MySQL中的数据同步到Redis中去，这样就保证两方的数据一致了。但是我们的缓存方案就是为了提升效率，现在却为了安全而降低了效率，这是我们不愿看到的。

2：速度为主

如果要以效率为主，我们可以先写入缓存，并且设置过期时间（大约是200毫秒），然后再写入MySQL，当写入MySQL后，我们再将MySQL中的数据同步到Redis中去。当同步到Redis中去的时候，这个过期时间也就到期了。过期时间是与MySQL网络传输时间+MySQL处理时间+MySQL同步到Redis的时间。有个问题是如果当写入MySQL写入失败，这个时候Redis中含有数据，那么他就会提供脏数据。但是这个问题也就200毫秒的存活时间，因为从数据库会找主数据库进行同步。

5：缓存问题的解决方法

1：缓存穿透

问题：如果黑客让客户端一直读取MySQL和Redis中都不存在的数据，那么所有的读取操作都落在了MySQL中，那么就会造成MySQL中访问的性能急剧降低。

解决：如果在Redis和MySQL中读取的数据都不存在，那么就在Redis中设置一个<Key,nil>，代表查找的这个热点数据不存在。或者部署布隆过滤器（类似于哈希表），使这些数据只能增加，不能删除，具体可以搜一搜。

2：缓存击穿

问题：如果Redis中没有，但是MySQL中有，也就是说本来一个热点数据，在Redis中存在，但是过期了，那么大量的并发请求读取操作就会落到MySQL中，这样就造成MySQL访问的性能急剧降低。

解决：我们可以将过热的数据设置成不过期的状态。或者是添加分布式锁，将并发的请求操作，变成串行执行。

3：缓存雪崩

问题：我们在写入Redis中的数据是需要加入过期时间的，但是当我们不小心将多个过热数据的过期时间设置成统一时间，就会面临大量热点数据集中失效的问题，虽然失效，但是在MySQL中还是存在这个数据，所以大量的请求读取操作就会落到MySQL中去，就会造成MySQL访问性能急剧降低。

解决：我们可以将这个过期时间给错开，避免同时过期。当然我们可以在重启MySQL的时候，先将一些热数据先缓存到Redis中。

五：一些小问题

1：MySQL的集群是用什么样的方式去增加并发量

使用连接池技术，合理使用索引，优化SQL语句，分库分表，使用存储过程。

2：B树和B+树的区别

1.B树只适合随机检索，B+树支持随机检索和顺序检索

2.B+树空间利用率高，可以减少IO次数，磁盘读写代价更低。 一般来说索引本身也很大，往往以索引文件的形式存储在磁盘上，这样索引查找过程就要产生磁盘IO消耗。B+树的内部节点只作为索引使用，其内部节点（非叶子节点）比B树更小，判断能容纳的节点中关键字更多，一次读取到的键更多。 3.B+树查询效率更稳定，因为数据存放在叶子节点。

4.B树在一定程度上也提高了磁盘IO性能，但没有解决遍历效率低下的问题。B+树的叶子节点都使用指针顺序连接在一起，只要遍历叶子节点就可以实现所有值。

5.增删文件时，B树需要重新调整树结构。B+树不需要调整树结构，因此B+树效率更高。

3：数据库的ACID怎么实现

1：原子性：要么都发生要么都不发生，所以需要实现回滚的操作，那么我们就要实现undolog的回滚日志。我们在数据库执行操作的时候，我们生成一个undolog，里面包含的是数据库的SQL，当执行失败的时候，我们通过我们的undolog进行回滚，也就是当插入insert的话，那我们就执行delete，如果是update，那我们就反向执行update。反正就是通过undolog进行回滚操作。

2：一致性：是数据库的完整性和一致性，我们使用其他三个特性来完成这一个特性。

3：隔离性：对于隔离性需要实现MVCC

4：持久性：对于持久性就是我们实现redolog，我们采用预先写的方法，也就是我们在修改之前先将操作写入日志，然后再写入bufferpool，这样mysql宕机之后的话，我们重启就可以读取到redolog的数据。

4：mysql的binlog是什么

binlog是二进制日志，他记录了数据库上的所有改变，并以二进制的形式保存在从磁盘中。它可以用来查看数据库的变更历史，数据库增量备份和恢复，mysql的主从数据库的复制。

5： 当前读和快照读

当前读：像select lock in share mode ，select for update ,update ;insert; delete 。这些操作都是一种当前读，为什么叫当前读，就是他读取的是当前记录的最新版本，读取时还要保证其他事务不能修改当前记录，所以会对记录进行加锁。

快照读：像不加锁的select就是快照读。快照读的实现是基于MVCC的操作实现的，他的操作避免了加锁的操作，只是拿取一个数据版本。

6：数据库锁

在MySQL中，锁可以分为共享锁和排它锁两种类型。共享锁用于读取数据，而排它锁用于修改数据。当一个事务获得了排它锁后，其他事务就不能再对该数据进行修改，直到该事务释放锁为止。

7：项目怎么进行死锁检测

可以使用jstack工具

或者写死锁检测的代码：Linux下的死锁检测组件（分模块讲解）_linux死锁检测-CSDN博客

8：什么时候使用读已提交

        读已提交是一种常见的隔离级别，它可以提高并发性和数据准确性：订单系统
        假设有一个在线商店，它需要处理大量订单。多个用户可能同时提交订单，如果不使用读已提交隔离级别，有可能会出现数据混乱或错误的情况。例如，一个用户提交订单时，第二个用户同时查看订单，但却看到了已提交但未完成的订单，导致订单重复或缺失。使用读已提交隔离级别可以避免这种情况，确保每个用户都只看到已经提交并生效的订单。

9：搜索慢怎么解决

        我们通过使用 EXPLAIN 来查看 SQL 语句的具体执行过程。 原理：模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的。

        首先我们需要找到SQL这个语句在哪里，通过 show processlist 列出较慢的连接通道来 以及使用慢查询日志来找到具体的SQL语句。再分析SQL中我们要先查看在where、group by、order by中是否使用索引，如果没有使用，那么就可以考虑是否添加索引，然后继续优化SQL语句中in和not in 变成联合查询，并且减少整体的联合查询。以及一个隐形的问题：age问题，应该存储出生年月，让客户端进行计算年纪。

https://github.com/0voice