备战实习，会定期的总结常考的面试题，大家一起加油！ 🎯

往期文章：

• 【面试题】计算机网络篇-10道常见面试题p1
• 【面试题】JVM篇-10道常见面试题p1
• 【面试题】Java并发篇-10道常见面试题p1
• 【面试题】Java基础篇-常见面试题总结p1
• 【面试题】Java基础篇-常见面试题总结p2

参考文章：

• https://blog.csdn.net/qq_45966440/category_11098049.html

• https://csp1999.blog.csdn.net/article/details/116069114

• https://joonwhee.blog.csdn.net/article/details/106893197

• https://www.pdai.tech/md/db/sql-mysql/sql-mysql-overview.html

• https://javaguide.cn/

注意：

如果本文中有错误的地方，欢迎评论区指正！🍭

文章目录

• 常见基础篇
• • 1.说一下你对MySQL架构的了解？
  • 2.说说数据库的三范式？
  • 3.说说一条SQL查询语句在MySQL中如何执行的？
  • 4.说说MySQL中的数据类型有哪些？
  • 5.说下InnoDB 和 MyISAM 的区别？
  • 6.什么是Buffer Pool？
• 索引篇
• • 1.谈谈你对索引的理解？
  • 2.说一下索引有哪些类型？
  • 3.说下MySQL的索引有哪些？
  • 4.说说什么是B+树？
  • 5.谈谈聚集索引与非聚集索引？
  • 6.说一下什么是覆盖索引？
• 事务篇
• • 1.聊聊什么是事务？
  • 2.说说事务的ACID特性？
  • 3.聊聊并发事务带来的问题？
  • 4.事务的隔离级别有哪些？
• 优化篇
• • 1.说说日常在项目中你是怎么优化SQL的？
  • 2.说说什么是最左前缀原则？什么是最左匹配原则？
  • 3.说说explain分析执行计划中各个字段的含义？
  • 4.说说什么情况下索引会失效即查询不会走索引？
• 主从复制&读写分离篇
• • 1.主从复制中涉及到哪三个线程？
  • 2.主从同步出现延迟的原因？
  • 3.当主库宕机后，数据可能丢失，这种情况怎么解决？
  • 4.谈谈你对数据库读写分离的理解？
• 锁篇
• • 1.MySQL 遇到死锁问题，你是如何解决的？
  • 2.说说MySQL中的乐观锁和悲观锁是什么以及它们的区别？
  • 3.说说表级锁和行级锁两者区别？
  • 4.说说共享锁和排他锁？

常见基础篇

1.说一下你对MySQL架构的了解？

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)
• 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）
• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确
• 优化器： 按照 MySQL 认为最优的方案去执行
• 执行器：当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果

大体来说，MySQL可以分为Server层和存储引擎两部分：

• Server 层

  主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。

• 存储引擎

  主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

附：更加详细的功能可以参考 Server 层基本组件介绍

2.说说数据库的三范式？

• 第一范式：确保每列保持原子性，数据表中的所有字段值都是不可分解的原子值
• 第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。也就是说确保表中的每列都和主键相关
• 第三范式：任何非主属性不依赖于其它非主属性。也就是说确保每列都和主键列直接相关而不是间接相关

3.说说一条SQL查询语句在MySQL中如何执行的？

1. 首先应用程序把查询SQL语句发送给服务器端执行
2. 查询缓存，如果查询缓存是打开的，服务器在接收到查询请求后，并不会直接去数据库查询，而是在数据库的查询缓存中找是否有相对应的查询数据，如果存在，则直接返回给客户端。只有缓存不存在时，才会进行下面的操作
3. 查询优化处理，生成执行计划。这个阶段主要包括解析SQL、预处理、优化SQL执行计划
4. MySQL根据相应的执行计划完成整个查询
5. 最后将查询结果返回给客户端

👩‍💻面试官追问：那你在说说MySQL查询的指令执行顺序？

编写顺序：

SELECT DISTINCT
	<select list>
FROM
	<left_table> <join_type>
JOIN
	<right_table> ON <join_condition>
WHERE
	<where_condition>
GROUP BY
	<group_by_list>
HAVING
	<having_condition>
ORDER BY
	<order_by_condition>
LIMIT
	<limit_params>

执行顺序：

4.说说MySQL中的数据类型有哪些？

大致可以分为四类：

1. 整数
   TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT分别占用8、16、24、32、64位存储空间。
2. 浮点数
   FLOAT、DOUBLE及DECIMAL为浮点数类。
   • DECIMAL是利用字符串进行处理的，能存储精确的小数。相比于FLOAT和DOUBLE，DECIMAL的效率更低些。
   • FLOAT、DOUBLE及DECIMAL都可以指定列宽，例如FLOAT(5,2)表示一共5位，两位存储小数部分，三位存储整数部分。
3. 字符串
   字符串常用的主要有CHAR和VARCHAR
4. 日期
   比较常用的有year、time、date、datetime、timestamp等

👩‍💻面试官追问：详细说一下MySQL中char 和 varchar 的区别？

• char∶固定长度类型，比如︰订阅char(10)，当你输入"abc"三个字符的时候,它们占的空间还是10个字节，其他7个是空字节。

  • 优点∶效率高
  • 缺点∶占用空间
  • 适用场景︰存储密码的md5值，固定长度的，使用char非常合适

• varchar︰可变长度，存储的值是=每个值占用的字节+一个用来记录其长度的字节的长度

  从空间上考虑varchar比较合适。从效率上考虑char比较合适，二者使用需要权衡。

表格对比：

对比项	char	varchar
长度特点	长度固定，存储字符	长度可变，存储字符
长度不足情况	插入的长度小于定义长度时，则用空格填充	小于定义长度时，按实际插入长度存储
性能	存取速度比varchar快得多	存取速度比char慢得多
使用场景	适合存储很短的，固定长度的字符串，如手机号，MD5值等	适合用在长度不固定场景，如收货地址，邮箱地址等

👩‍💻面试官继续问：详细说一下MySQL中字段类型DATETIME 和 TIMESTA的区别？

类型	占据字节	范围	时区问题
datetime	8 字节	1000-01-01 00:00:00到 9999-12-31 23:59:59	存储与时区无关，不会发生改变
timestamp	4 字节	1970-01-01 00:00:01 到 2038-01-19 11:14:07	存储的是与时区有关，随数据库的时区而发生改变

应该尽量使用timestamp，相比于datetime它有着更高的空间效率

5.说下InnoDB 和 MyISAM 的区别？

对比	InnoDB	MyISAM
事务	支持	不支持
锁类型	行锁、表锁	表锁
MVCC	支持	不支持
外键	支持	不支持
索引	聚簇索引、5.7以后支持全文索引	非聚簇索引、支持全文索引
安全性	支持数据库异常崩溃后的安全恢复	不支持
缓存	仅缓存索引，还缓存真实数据，对内存要求较高	只缓存索引，不缓存真实数据
备份	InnoDB 支持在线热备份	不支持

👩‍💻面试官追问：平时选择存储引擎的时候这两个怎么取舍？

• InnoDB

  是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。

• MyISAM

  如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择MyISAM存储引擎是非常合适的

6.什么是Buffer Pool？

• Buffer Pool 是 InnoDB 维护的一个缓存区域，用来缓存数据和索引在内存中，主要用来加速数据的读写，如果 Buffer Pool 越大，那么 MySQL 就越像一个内存数据库，默认大小为 128M。
• InnoDB 会将那些热点数据和一些 InnoDB 认为即将访问到的数据存在 Buffer Pool 中，以提升数据的读取性能。
• InnoDB 在修改数据时，如果数据的页在 Buffer Pool 中，则会直接修改 Buffer Pool，此时我们称这个页为脏页，InnoDB 会以一定的频率将脏页刷新到磁盘，这样可以尽量减少磁盘I/O，提升性能。

索引篇

1.谈谈你对索引的理解？

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。

索引的作用就相当于目录的作用。打个比方：我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

👩‍💻面试官追问：说说索引的优缺点？

优点 ：

• 使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性

缺点 ：

• 从时间角度考虑。创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率
• 从空间角度考虑。索引需要使用物理文件存储，也会耗费一定磁盘空间

👩‍💻面试官继续问：说说哪些情况适合建立索引哪些不适合？

适合建立索引：

• 在最频繁使用的、用以缩小查询范围的字段上建立索引
• 在频繁使用的、需要排序的字段上建立索引

不适合建立索引的情况：

• 对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引
• 对于一些特殊的数据类型，不宜建立索引，比如︰**文本字段(text)**等

2.说一下索引有哪些类型？

• 主键索引

  数据表的主键列使用的就是主键索引。一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

• 唯一索引(Unique Key)

  唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。

• 普通索引(Index)

  普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。

• 前缀索引(Prefix)

  前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。

• 全文索引(Full Text)

  全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。（Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引）

其中唯一索引、普通索引、前缀索引、全文索引又称为二级索引（辅助索引）

3.说下MySQL的索引有哪些？

• B+Tree 索引
  • 是大多数 MySQL 存储引擎的默认索引类型。
• 哈希索引
  • 哈希索引能以 O(1) 时间进行查找，但是失去了有序性。
  • InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。
• 全文索引
  • MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。
  • 全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。
  • InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。
• 空间数据索引
  • MyISAM 存储引擎支持空间数据索引(R-Tree)，可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

InnoDB、MyISAM、Memory三种存储引擎对各种索引类型的支持：

索引	InnoDB引擎	MyISAM引擎	Memory引擎
B+Tree 索引	支持	支持	支持
哈希索引	不支持	不支持	支持
空间数据索引	不支持	支持	不支持
全文索引	5.6版本之后支持	支持	不支持

4.说说什么是B+树？

1. B+树是基于B树和叶子节点顺序访问指针进行实现，它具有B树的平衡性，并且通过顺序访问指针来提高区间查询的性能
2. 进行查找操作时，首先在根节点进行二分查找，找到一个key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的data
3. 插入、删除操作会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性

👩‍💻面试官追问：那你说说B 树和B+树两者有何异同呢？

B树结构图：

可以看出：

• B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是独立的，B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

👩‍💻面试官追问：为什么MySQL数据库要用B+树存储索引？而不用红黑树、Hash、B树？

• 红黑树

  如果在内存中，红黑树的查找效率比B树更高，但是涉及到磁盘操作，B树就更优了。因为红黑树是二叉树，数据量大时树的层数很高，从树的根结点向下寻找的过程，每读1个节点，都相当于一次IO操作，因此红黑树的I/O操作会比B树多的多。

• hash 索引

  如果只查询单个值的话，hash 索引的效率非常高。但是 hash 索引有几个问题：①不支持范围查询 ②不支持索引值的排序操作 ③不支持联合索引的最左匹配规则。

• B树索引

  • B树索相比于B+树，在进行范围查询时，需要做局部的中序遍历，可能要跨层访问，跨层访问代表着要进行额外的磁盘I/O操作
  • B树的每个节点都存储数据，而B+树只有叶子节点才存储数据，所以查找相同数据量的情况下，B树的高度更高，IO更频繁。
  • 以页为单位读取使得一次 I/O 就能完全载入一个节点，且相邻的节点也能够被预先载入；所以数据放在叶子节点，本质上是一个Page页

👩‍💻面试官继续追问：B+树中一个节点到底多大合适？

1页或页的倍数最为合适。因为如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出1页，造成资源的浪费。所以为了不造成浪费，所以最后把一个节点的大小控制在1页、2页、3页等倍数页大小最为合适。

这里说的“页”是 MySQL 自定义的单位（和操作系统类似），MySQL 的 Innodb 引擎中1页的默认大小是16k，可以使用命令SHOW GLOBAL STATUS LIKE 'Innodb_page_size' 查看。

5.谈谈聚集索引与非聚集索引？

• 聚集索引：即索引结构和数据一起存放的索引。主键索引属于聚集索引

  在 Mysql 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据

• 非聚集索引：即索引结构和数据分开存放的索引。二级索引属于非聚集索引

  MYISAM 引擎的表的.MYI 文件包含了表的索引， 该表的索引(B+树)的每个非叶子节点存储索引， 叶子节点存储索引和索引对应数据的指针，指向.MYD 文件的数据。

如图：

如上图，主键索引的叶子节点保存的是真正的数据。而辅助索引叶子节点的数据区保存的是主键索引关键字的值

假如要查询name = C 的数据，其搜索过程如下：

1. 先在辅助索引中通过C查询最后找到主键id = 9
2. 在主键索引中搜索id为9的数据，最终在主键索引的叶子节点中获取到真正的数据。所以通过辅助索引进行检索，需要检索两次索引

之所以这样设计，一个原因就是：如果和MyISAM一样在主键索引和辅助索引的叶子节点中都存放数据行指针，一旦数据发生迁移，则需要去重新组织维护所有的索引

👩‍💻面试官追问：聚集索引的优缺点有哪些？

• 聚集索引的优点

聚集索引的查询速度非常的快，因为整个B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

• 聚集索引的缺点

  1. 依赖于有序的数据

     因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢

  2. 更新代价大

     如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的

👩‍💻面试继续问：非聚集索引的优缺点有哪些？

• 非聚集索引的优点

  更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的

• 非聚集索引的缺点

  1. 跟聚集索引一样，非聚集索引也依赖于有序的数据
  2. 可能会二次查询(回表)。这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询

👩‍💻面试继续追问：非聚集索引一定回表查询吗？

不一定，原因详细看下面第6问

6.说一下什么是覆盖索引？

覆盖索引：指的是在一次查询中，如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为覆盖索引，而不再需要回表查询

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。

举个栗子：

• 如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。
• 再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。

事务篇

1.聊聊什么是事务？

事务是用户定义的一个数据库操作序列，这些操作要么全不做，要么全做，是一个不可分割的工作单位。

举个栗子：就拿最经典的转账例子来说，假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

2.说说事务的ACID特性？

1. 原子性（Atomicity）

   事务是最小的执行单位，不允许分割。事务的原子性确保事务所有的操作要么全部提交成功，要么全部失败回滚。

2. 一致性（Consistency）

   执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的。

3. 隔离性（Isolation）

   并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。

4. 持久性（Durability）

   一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其执行结果有任何影响。

3.聊聊并发事务带来的问题？

1. 脏读（Dirty read）

   当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据可能就是“脏数据”，这就是脏读。

2. 丢失修改（Lost to modify）

   两个事务T1和T2读入同一数据并修改。T2提交的结果破坏了T1提交的结果，导致T1的修改被丢失。

3. 不可重复读（Unrepeatableread）

   事务T1读取某一数据后，事务T2对其进行了修改，当事务T1再次读该数据时，得到与前一次不同的值。因此称为不可重复读。

4. 幻读（Phantom read）

   和不可重复读很类似：

   事务T1按一定条件从数据库中读取某些数据记录后，事务T2删除了其中部分记录，当T1再次按相同条件读取数据时，发现某些记录神秘消失了。

   事务T1按一定条件从数据库中读取某些数据记录后，事务T2插入一些记录，当T1再次按相同条件读取数据时，发现多了一些记录。

4.事务的隔离级别有哪些？

SQL 标准定义了四个隔离级别：

1. 读未提交（Read Uncommitted）

   最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

2. 读已提交（Read Committed）

   允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

3. 可重复读（Repeatable Read）

   对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

4. 可串行化（Serializable）

   最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

总结一张表：

隔离级别	脏读	不可重复读	幻读
读未提交（Read Uncommitted）	√	√	√
读已提交（Read Committed）	×	√	√
可重复读（Repeatable Read）	×	×	√
可串行化（Serializable）	×	×	×

👩‍💻面试官追问：MySQL的默认隔离级别是什么？

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看，MySQL 8.0 该命令改为SELECT @@transaction_isolation;

优化篇

1.说说日常在项目中你是怎么优化SQL的？

可以从几个方面考虑：

优化表结构

• 尽量使用数字型字段。若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

• 尽可能的使用 varchar 代替 char。可变长字段存储空间小，可以节省存储空间。

• 当索引列大量重复数据时，可以把索引删除掉。比如有一列是性别，只有男、女，这样的索引是无效的。

优化查询

• 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
• 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。建议使用 union 替换 or
• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
• in 和 not in 也要慎用，否则会导致全表扫描
• 优化嵌套查询时可以将子查询尽量替换为多表连接查询（JOIN）
• 任何查询也不要出现select *！

索引优化

• 尽量使用复合索引，而少使用单列索引
• 最左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列
• 对作为查询条件和 order by的字段建立索引
• 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

其他优化

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案：

• 如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。
• 手动开启事务后在进行数据插入
• 数据有序插入（按主键）

附：更多详细的优化sql操作可以参考SQL优化

2.说说什么是最左前缀原则？什么是最左匹配原则？

最左前缀原则，就是最左优先，在创建多列索引时，要根据业务需求，where 子句中使用最频繁的一列放在最左边。

当我们创建一个组合索引的时候，如 (a1,a2,a3)，相当于创建了（a1）、(a1,a2)和(a1,a2,a3)三个索引，这就是最左匹配原则。

3.说说explain分析执行计划中各个字段的含义？

详细explain的文章可以参考：MySQL高级详解

在 select 语句之前增加 explain 关键字，会返回执行计划的信息。

各个字段含义：

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）等
table	展示这一行的数据是关于哪一张表的
type	表示表的连接类型，性能由好到差的连接类型为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	这一列是 mysql 估计要读取并检测的行数，注意这个不是结果集里的行数
extra	其他额外的执行计划信息。比如有 Using index、Using where、Using temporary等

4.说说什么情况下索引会失效即查询不会走索引？

答案→优化篇-1-优化查询

主从复制&读写分离篇

1.主从复制中涉及到哪三个线程？

主从复制主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程

• binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中
• I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）
• SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）

主从复制具体过程分成三步：

1. Master 主库在事务提交时，会把数据变更作为时间 Events 记录在二进制日志文件 Binlog 中
2. 主库推送二进制日志文件 Binlog 中的日志事件到从库的中继日志 Relay Log
3. Slave 从库重做中继日志中的事件，将改变反映它自己的数据

2.主从同步出现延迟的原因？

假如一个服务器开放N个连接给客户端，这样有会有大并发的更新操作，但是从服务器的里面读取 binlog 的SQL线程仅有一个，导致从库SQL可能会跟不上主库的处理速度。

👩‍💻面试官追问：主从同步出现延迟的解决办法？

• 网络方面：尽量保证主库和从库之间的网络稳定，延迟较小；
• 硬件方面：从库配置更好的硬件，提升随机写的性能；
• 配置方面：尽量使 MySQL 的操作在内存中完成，减少磁盘操作。或升级 MySQL5.7 版本使用并行复制；
• 建构方面：在事务中尽量对主库读写，其它非事务的读在从库。消除一部分延迟带来的数据库不一致。增加缓存降低一些从库的负载。

3.当主库宕机后，数据可能丢失，这种情况怎么解决？

可以使用半同步复制或全同步复制。

• 半同步复制

  主库修改语句写入binlog后，不会立即给客户端返回结果。而是首先通过log dump 线程将 binlog 发送给从库，从库的 I/O 线程收到 binlog 后，写入到 relay log，然后返回 ACK 给主库，主库收到 ACK 后，再返回给客户端成功

• 全同步复制

  主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响

4.谈谈你对数据库读写分离的理解？

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

👩‍💻面试官追问：读写分离为什么能够提高性能？

1. 主从服务器负责各自的读和写，极大程度缓解了锁的争用
2. 从服务器可以使用MyISAM，提升查询性能以及节约系统开销
3. 增加冗余，提高可用性

锁篇

1.MySQL 遇到死锁问题，你是如何解决的？

1. 查看死锁日志 show engine innodb status;
2. 找出死锁Sql
3. 分析sql加锁情况
4. 模拟死锁案发
5. 分析死锁日志
6. 分析死锁结果

2.说说MySQL中的乐观锁和悲观锁是什么以及它们的区别？

• 乐观锁

  认为对于同一个数据的并发操作，是不会发生修改的。在更新数据的时候，会采用不断尝试更新的方式来修改数据。也就是先不管资源有没有被别的线程占用，直接申请操作，如果没有产生冲突，那就操作成功，如果产生冲突，说明有其他线程已经在使用了，然后在不断地尝试。

  乐观锁实现方式：一般通过版本号和CAS算法实现。

• 悲观锁

  认为对于同一个数据的并发操作，一定是会发生修改的，哪怕没有修改，也会认为修改。因此对于同一个数据的并发操作，悲观锁采取加锁的形式。悲观的认为，不加锁的并发操作一定会出问题。

  悲观锁的实现方式：使用共享锁和排它锁，select...lock in share mode，select…for update。

3.说说表级锁和行级锁两者区别？

• 表级锁

  MySQL 中锁定粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。

• 行级锁

  MySQL 中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。 InnoDB 引擎都支持表级锁，MyISAM不支持。

👩‍💻面试官追问：InnoDB 的行锁是怎么实现的？

InnoDB 行锁是通过索引上的索引项来实现的。意味者，只有通过索引条件检索数据，InnoDB 才会使用行级锁，否则，InnoDB将使用表锁！

• 对于主键索引：直接锁住锁住主键索引即可。
• 对于普通索引：先锁住普通索引，接着锁住主键索引，这是因为一张表的索引可能存在多个，通过主键索引才能确保锁是唯一的，不然如果同时有2个事务对同1条数据的不同索引分别加锁，那就可能存在2个事务同时操作一条数据了。

👩‍💻面试官继续追问：InnoDB 锁的算法有哪几种？

一共三种：

• Record lock：记录锁（行锁），单条索引记录上加锁，锁住的永远是索引，而非记录本身。
• Gap lock：间隙锁，在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。
• Next-key lock：临键锁，Record lock 和 Gap lock 的结合，即除了锁住记录本身，也锁住索引之间的间隙。

关于三种锁详细的讲解可以参考：MySQL如何解决幻读和不可重复度？

4.说说共享锁和排他锁？

• 共享锁

  又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。一个事务获取了共享锁，其他事务可以再加共享锁，但是不能加排他锁。

• 排他锁

  又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务可以对数据行读取和修改。

最后喜欢的小伙伴，记得三联哦！😏🍭😘