MySQL数据库面试题

理论知识

• 数据库三大范式？

第一范式：保证每一列的原子性，不可分；

第二范式：保证每一列都和主键有关联；

第三范式：保证每一列都和主键有直接关联；

• 数据库优化思路？

优化原则：

1. 尽量减少磁盘IO，尽量增加索引命中；尽量少进行全表扫描；
2. 尽量只返回所需数据，不要返回多余数据；

优化思路：

1. SQL和索引优化
2. 索引优化：where条件字段尽量走索引，查询条件不要进行函数运算，这样索引不起作用，会走全表扫描；按照最左匹配原则，组合索引区分度大的放在最左边，他会最先匹配，可以提高查找的效率。
3. select优化：尽量避免使用select *这样的SQL语句，这样让优化器无法完成索引覆盖，降低了优化器的执行效率，同时返回了更多的数据，占用了跟多网络带宽。
4. 多表union关联的时候小表在前，大表在后，这是由于MySQL在进行多表union操作的时候，会先全表扫描前一张表，再关联后一张表。
5. 多数场景建议使用join/union，不建议使用子查询，由于子查询会生成中间临时表，对于性能的消耗比较大。join连接查询是通过嵌套循环来实现的，效率也不会太高。
6. 使用合理的分页来提高效率，在SQL中利用limit来限制查询的起始位置和条数，能够在SQL做就不要再代码中做。
7. 库表优化、配置优化
8. 合理使用存储引擎，比如根据不同的业务场景选择使用Innodb还是MySIAM的存储引擎，或者开启数据库缓存，要开启多大的等，这些可以通过修改数据库配置项实现。
9. 代码优化、架构优化
10. 代码主要涉及到批量操作，数据压缩等；
11. 还有集群部署，读写分离，分库分表的架构设计；

• 数据库问题排障思路？

12. 慢查询

思路：

1. 查询慢查询日志；
2. 通过expain查询慢查询的sql原因；
3. 优化索引；

案例：

对于未唯一字段加唯一索引，会引入全表扫描，如果数据比较多，就会形成慢查询。解决方案是对于非唯一字段加普通索引，对唯一字段加唯一索引。

1. 死锁

思路：

1. 通过show status查看加锁线程；
2. 找出加锁sql和加锁对象；
3. 优化sql；

案例：

在未加索引的变中，多个事务同时按照同一字段条件进行更新操作，可能会造成死锁，原因是没有索引，在进行更新操作时会加表锁，就会阻塞其他事务的操作，形成死锁。修复的方案是对条件字段加索引，从而形成行锁，提升并发度。

1. cpu飙升
2. 通难过top命令查看是否是mysqld的进程导致；
3. 通过show processlist查看session的连接时间和状态等信息；

找到性能最大连接的sql，进行sql和索引优化；

系统结构

• mysql的架构图？

1. 第一层负责权限和连接，连接器判断该连接是否有权限，有权限的话会先查询缓存，缓存命中的话就直接返回，否则进入到第二层；
2. 第二层进行SQL语法分析和优化，分析器解析和判断SQL语言是否正确，然后给优化器进行优化，包括索引相关的优化；
3. 第三层负责在底层存储引擎上执行，最后解析后的SQL语句会提交给存储引擎执行。

• mysql里面的innodb和myisam引擎的差别？

4. innodb是支持事务的，myisam不支持事务。
5. innodb支持聚簇索引，myisam不支持聚簇索引；
6. innodb适合写操作，myisam适合读操作，这个和他们锁的隔离级别有关；innodb是行级锁，他的原理是在索引的索引项上加锁，如果对没加索引的字段进行查询，就会变成表锁，而myisam用的是表锁；

• mysql中undo和redo日志的区别？

7. redo日志是重做日志。保存的是一个事务T在进行X操作后的新值N，可以表示为：；是innodb存储引擎特有的，支持crash之后恢复没有刷盘的数据恢复。
8. undo日志是回退日志。保存的是一个事务T在进行Y操作后的老值O，可以表示为：； 支持事务失败后进行事务回滚。

• mysql的bin日志？

bin日志是mysql运行的日志，里面记录了mysql运行的操作，他是在server层实现的日志。

既可以保存的是逻辑日志，即保存的是sql语句，通过bin 日志做数据恢复。也可以保存实际的数据格式。

• mysql的buffer pool什么用途？

mysql执行一句更新操作的数据不会立即写入磁盘，会先放到buffer pool中，这是内存中的一片暂存区，等一段时间会刷入磁盘中。这样设计的作用是提高读写数据的效率。

• mysql的innodb存储引擎处理一条SQL语句的流程？

1. 事务开始，从内存buffer pool或者磁盘中获取这条记录所需数据；
2. 在buffer pool中将旧值O写入到undo日志中，以防事务失败需要回滚；
3. 将这条记录旧值O修改新值N，并将新值N写入到redo日志中，以防数据库crash导致数据丢失；这个是存储引擎实现的；
4. 将数据写入到buffer pool的binlog中，等待满了再刷盘到磁盘中；
5. 事务结束，redo日志添加commit标识，表示一次事务提交结束。

锁和事务

• 数据库事务的特性？

数据库事务：数据库事务的四大特性是ACID。

1. 原子性：就是所有操作要么全不做，要不全做。基于undo日志来实现。
2. 一致性：就是在并发情况下数据库由一个状态转移到另一个状态的数据要一致。通过数据的隔离级别来实现。
3. 隔离性：多个事务的操作互相不影响，数据有4种隔离级别来解决。基于锁+mvcc机制来实现。
4. 持久性：数据库中事务一旦提交，数据持久到硬盘中。基于redo日志来实现。

• 数据库并发访问存在的问题？

5. 脏读：事务A读取一个数据，事务B对该数据进行修改，修改前后的数据都可以读到，但是数据会不一样，导致了脏读。脏读强调的是读到其他事务未提交的数据。
6. 不可重复读：事务A读一个数据，事务B对该数据进行修改后提交，导致事务A前后读取的数据值不一致。不可重复度强调的是读到其他事务提交后的数据和提交前的数据不一致。
7. 幻读：事务A读取一个数据，事务B在insert、delete增删记录，事务A两次读取的数据条数不一致；

• 数据库隔离级别？

数据库的隔离级别是为了解决数据库中常见的三类问题，包括：脏读、不可重复读和幻读的问题，为了解决这个问题数据库提出了四类隔离级别，分别是：

1. 未提交读：没有提交就能够读；
2. 已提交读：提交后才能读；
3. 可重复读：未提交前读取的数据一致；
4. 串性化读：读写操作加锁；

总结：

1. 随着事务隔离级别的严格，并发性能降低。
2. 可重复读+next-key lock/mvcc来解决幻读问题。

• mysql的悲观锁机制？

mysql加锁是通过锁住索引来实现的，如果没有索引和主键则会锁住整张表。mysql是通过record lock和next-key lock来实现加锁策略的。

mysql默认是autocommit提交事务，如果需要使用手动设置悲观锁，需要关闭该设置。

手动设置悲观锁的步骤：

1. begin；
2. Select * from xxx where xxx for update，需要在select语句中加上for update进行强制加锁；
3. 进行各种操作，这样的话在还么有commit之前，该行记录一直处于锁定状态；
4. commit；

• mysql的乐观锁mvcc机制原理？

mysql是通过mvcc机制来实现乐观锁的。mvcc是一种不加锁的方式解决读写冲突的机制，支持mysql上了写锁，还能继续支持读，解决读操作阻塞写操作的问题。

原理：

主要通过隐含字段、undo日志和read view来实现。

1. 每条记录会有几个隐含字段，包括row_id,trx_id,roll_ptr，其中roll_id可以指向undo日志，回滚到之前状态。
2. 每次进行读操作时，会生成一个read view快照，读取之后比较快照中数值，来判断是否进行了一致性读。

• mysql如何处理死锁问题？

问题说明：

两个或多个事务占用同一个共享资源，并且互相请求对方资源的锁，这样就会造成死锁。比如事务A请求事务B加锁的数据，同时事务B请求事务A加锁的数据，这样就会形成死锁情况。

解决方案：

1. 让数据的查询尽量索引完成，避免索引加锁失败，从行级锁升级成表锁。
2. 保持事务的顺序执行；尽量减少加锁范围，减少出现死锁的概率；事务执行简短，减少加锁时间；
3. 死锁检测。执行之前做循环引用的检测。
4. 死锁超时退出。目前mysql处理死锁的方法是，将持有最少行级互斥锁的事务回滚。

• mysql如何避免幻读？

在快照读情况下，即不加锁的非阻塞读，像普通select操作下通过mvcc避免幻读；通过乐观锁类避免。

对于可重复读的隔离级别下，select * from table的语句是采用快照读的模式，新的事务会读快照；

在当前读情况下，当前读表示需要读取当前最新数据，是加锁的阻塞读写操作，像普通update/insert/delete和加update的select操作，是通过next-key lock避免幻读；通过悲观锁来避免。

对于可重复读的隔离级别，如果是当前读场景，需要通过间隙锁(Gap Lock)模式来避免幻读，这样行间间距会被加锁，不会被新的事务插入新的数据，间隙锁加行锁合起来称为next-key lock；

MySQL是怎么解决幻读问题的？

https://www.cnblogs.com/jian0110/p/15080603.html

MySQL高级-MVCC（超详细整理）：

https://blog.csdn.net/SIESTA030/article/details/123113437

• 数据库里的乐观锁和悲观锁？

所有需要加锁的操作都是在进行多线程情况下才需要的。

1. 乐观锁比较好理解，就是预测所有线程对数据的操作都是不会冲突的，所以每次对数据进行操作时候都不会加上锁；mysql内部机制可以通过mvcc机制来是实现乐观锁；也可以通过在数据库设计时加上version字段，在并发情况下操作判断version字段是否变更来判断冲突。
2. 悲观锁则相反，就是认为每次线程对数据的操作都可能存在冲突，所以需要对数据加上行锁、表锁等。mysql主要通过record lock+nextKey lock来实现。

• 数据库的锁分类？

mysql数据库的锁分为行锁和表锁。

行锁。是innodb存储引擎的默认加锁方式，分为共享锁（读锁）和独占锁（写锁）

表锁。是myisam存储引擎的默认加锁方式，表锁开销小，并发度低，适合读多写少的常见。

行锁的分类：

1. 记录锁(Record lock)：即是行级锁，是innodb存储引擎的默认加锁方式。
2. 间隙锁(Gap lock)：即是Gap Lock，对记录之间的左开右闭的区间进行加锁；
3. 临键锁（Next key Lock）：记录锁+间隙锁
4. 共享锁（读锁）&独占锁（写锁）：共享锁又称S锁/读锁，对记录进行读操作时进行加的锁，可以共享；独占锁又称X锁/写锁，对记录进行写操作时进行家的所，只能独占；特点是行锁开销大，并发大，适合读少写多场景。
5. 意向共享锁&意向排它锁：为了解决对记录加了共享锁&独占锁，在对表加表锁的时候需要遍历，所以在对行加共享锁&独占锁时候，会对表加上意向共享锁&意向排它锁，下次加表锁一目了然；
6. 插入意向锁：就是多个事务在进行插入的时候，如果插入的间隙不是同一个就加上插入意向锁，那么就不需要加上间隙锁；

一张图彻底搞懂 MySQL 的锁机制：

一张图彻底搞懂 MySQL 的锁机制 | MySQL 技术论坛

阿里二面：怎么解决MySQL死锁问题的？：

阿里二面：怎么解决MySQL死锁问题的？ - 文章详情

• Mysql只操作一条记录也有可能发生死锁吗？

有可能。

Mysql的加锁对象是索引，不是记录，所以当一个事务对该记录的普通索引加锁，并请求主键索引的锁，但另一个事务对该记录的主键索引已加锁，并请求该记录的普通锁，那么就会进入死锁状态。

• 如何避免数据库死锁发生？

1. 保持事务的顺序执行；
2. 事务执行简短，减少加锁时间；
3. 设置加锁超时失效时间，保障死锁后也能自动释放；

索引

• mysql索引类别？

1. 按「逻辑结构」分类：B+ tree索引、Hash索引、Full-text索引。
2. 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
3. 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
4. 按「字段个数」分类：单列索引、联合索引。

• mysql索引B+树的原理？B树和B+树的区别？

B+树是一种典型的外存排序方式：

1. B+树是B树的一种进化，B树是一种多叉平衡排序二叉树，引入多叉，就可以减少对磁盘IO的次数，再简单讲述下B树的结构，每个结点中都有关键词、关键词所指向的指针，还有指向子结点的指针。针对每次查询操作，因为他是一颗排序树，所以他能够以最短的路径找到需要查询的关键词，找到对应的数据。
2. 现在再来讲B+树就容易理解了，他是为了解决IO次数还是太多，就是说还需要减少B树的层次，外加需要减轻中间结点的磁盘容量，所以B+树和B树最大的区别是他将所有真正指向文件的指针保存在叶子结点中，而非叶子结点他只是保存指向文件的指针，这样就能减轻占用磁盘大小，同时增加每个结点中关键池的个数，保证每个结点中关键词的个数和子树一致，这样就能减少树的层次。同时B+树的叶子节点前后也是通过指针联系起来的。

B树和B+树区别：

1. B树中间节点也保存数据，B+树数据保存在叶子节点，使得B+树有数据冗余，即节点中的数可能多次出现。
2. B+树的叶子节点前后形成链路，这样便于范围查找。
3. B+树的节点中保存的数据个数和出度一致，B树保存的数据比出度少一，使得B+节点中能保存更多的数据，降低了树的高度，减少了磁盘IO的次数。

选择B+树结构作为索引的原因？

1. B+树作为平衡树，其查询的时间效率为O(logN)，由于叶子节点中还有数据，相较于B树，可以减少再次进行磁盘IO的次数；
2. 相较于平衡二叉树AVL，AVL是二叉树，所以树的高度较高，进行磁盘IO的次数比B+树更多，而数据库查询的主要瓶颈就是在磁盘IO上；
3. 相较于B树，由于B+树的叶子节点直接指向节点数据，可以快速进行数据访问，同时叶子节点间是有序的，能够进行范围查找；
4. 相较于哈希表的索引结构，B+树的叶子节点是有序排列的，可以进行范围查找，这个在哈希表等结构中无法实现，需要做全表扫描，时间开销非常大；
5. 参考资料：数据库两大神器【索引和锁】 - 掘金

• Mysql聚簇索引和非聚簇索引？

聚簇索引是索引和数据在一起的，一个表中只有一个聚簇索引，就是主键索引。非聚簇索引则索引和数据分开保存。默认的innoDB支持聚簇索引，MYISAM不支持聚簇索引。聚簇索引和非聚簇索引都是B+树的一种实现。

1. 聚簇索引：聚簇索引是innoDB的索引方式，是主键索引的实现方式，索引和记录都保存在叶子节点中。如果没有主键索引，就用第一个非空唯一索引替代，如果还是没有就用表的隐含主键替代。
2. 非聚簇索引：非聚簇索引保存的是MYISAM的索引方式。叶子节点保存的是索引，正式记录保存在索引指向的位置，每次查找需要进行IO操作。

• MySQL索引的最左前缀原则？

就是在建立组合索引的时候，最频繁、区分度最高的字段放在最左侧；建立联合索引的B+树也是按照多字段的先后顺序新建的，如果where条件最左不是联合索引最左字段，则不会走到联合索引中去；

总结：联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。

• mysql回表机制？

mysql回表就是根据一个非主键的字段进行查询，第一次查询到的结果只是查询字段的索引和主键字段，需要再次根据主键查主键索引，这种过程成为回表。

• mysql覆盖索引机制？

将需要查询的字段和条件字段建立组合索引，这样根据查询条件在组合索引中就能查到响应字段，就不需要回表了。

• mysql索引下推机制？

针对条件语句中有多个条件的情况，在没有索引下推的情况下，会先根据最左索引查询处理的记录进行回表操作，再根据主键去继续满足下一个条件的记录，存在多次回表操作。

而有索引下推的情况是根据最左索引查询到了记录，会继续根据二级索引查询是否满足下一个条件，如果满足则返回，不满足则继续查询。

举例说明：就有点像查询成语字典，索引下推是根据首字查询成语，再在字典正文中继续查找，就不需要再回头看目录了。

• mysql的主键一定是自增的吗？

不一定。

主键可以自己选择，但一般会选择自增字段作为主键，原因如下：

1. 自增字段长度小，节省空间。
2. 自增字段都是在索引末尾进行插入，减少了从中间插入导致的页分裂等情况。
3. 自增字段保障了主键唯一性，不存在主键冲突的可能。

• mysql一定要设置主键吗？不设置主键会怎么样？

mysql不是一定要设置主键，如果使用innodb的存储引擎，表的主键索引是聚簇索引，如果没有设置主键，则会用第一个非空的唯一索引做主键索引，如果不存在非空索引，则会自动生成一个隐含row_id做主键索引。

• 数据库索引的优势？

1. 通过索引查找，可以减少全表扫描，减少IO次数，提升查询效率。
2. 索引可以进行范围查找，可以进行排序和分组等操作。

• 索引怎么建？如何优化？

3. 在区分度高的字段上建立索引。
4. 建立联合索引的时候考虑是否会形成索引覆盖，这样会减少回表的次数。
5. 建立组合索引时，遵循最左匹配原则。将查询频率比较高，区分度比较大的字段放在最左侧。
6. 为作为查询条件和进行排序操作的字段建立索引，比如where、group by、order by字段建立索引。
7. 索引字段作为条件查询时，不要进行函数计算，这样不会命中索引。

索引失效的情况：

1. 条件查询中有计算和函数处理。
2. 条件查询的联合索引非最左字段。

集群

• mysql主从同步的方式？

1. 步骤一：主库的更新事件(update、insert、delete)被写到binlog；
2. 步骤二：从库发起连接，连接到主库；
3. 步骤三：主库创建一个binlog dump thread，把binlog的内容发送到从库。
4. 步骤四：从库启动之后，创建一个I/O thread，读取主库传过来的binlog内容并写入到relay log；
5. 步骤五：还会创建一个SQL thread，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到从库的db中；

总结：核心是通过binlog来实现同步，主从间都有专门的线程来传授和处理数据；

• mysql如何保障数据不丢失？

mysql通过二阶段日志提交/WAL来保障数据不丢失。

1. 二阶段提交：mysql会首先将日志信息保存到redo log中，之后保存到binlog中，他们会先保存在buffer pool中，等满足一定条件会刷盘到磁盘中；
2. WAL机制：WAL是先写日志再刷盘操作。就是mysql会先将操作写入到redo log+binlog中，再刷入磁盘；

优势：

1. 由于是顺序刷盘，相比随机效率会高很多；
2. 写入buffer，等到buffer满了再统一刷盘，这样效率会高很多；

总结：通过WAL来实现，写入磁盘先先写入binlog+redolog，同时保障先写入buffer pool在写入磁盘。

MySQL 基础技术（三）—— MySQL 如何保证数据不丢失？：MySQL 基础技术（三）—— MySQL 如何保证数据不丢失？_mysql如何保证数据不丢失_java晴天过后的博客-CSDN博客

面试必刷，MySQL面试夺命20问，值得收藏背诵！：

面试必刷，MySQL面试夺命20问，值得收藏背诵！ - 知乎

其他

• 数据库中，一行记录有则更新，没有则新增的方法？

1. 利用replace into...命令，其原理是如果没有则插入，如果有则先删除老数据再插入新数据：replace INTO `test_db`.`account` (`id`, `name`, `balance`) VALUES (12323, 'abcdddd', 333)
2. 利用...on duplicate key update ...命令，其原理是如果没有则插入，如果有则更新该记录：INSERT INTO `test_db`.`account` (`id`, `name`, `balance`) VALUES (1111, 'bbbaadssdfasdfbb', 1231230) on DUPLICATE KEY UPDATE id=values(id),name=values(name),balance=values(balance);