【面试题】MySQL常见面试题总结

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

系列文章目录

• 【面试题】面试题分享之JVM篇
• 【面试题】面试题分享之Java并发篇
• 【面试题】面试题分享之Java集合篇（三）

注意：文章若有错误的地方，欢迎评论区里面指正 🍭 

参考文章:

• https://pdai.tech/md/interview/x-interview.html#_8-2-mysql
• https://blog.csdn.net/qq_45966440/article/details/122006579

基础篇

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

​

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

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

• Server层：

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

• 存储引擎：

        存储引擎层是MySQL的底层组件，负责管理数据的存储和检索。它是数据库管理系统用来处理不同表类型的SQL操作的组件，提供了数据的物理存储和索引结构。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

2、说一说数据库的三范式

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

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

这里大家可以结合上面的架构图去理解记忆。

1. 首先，应用程序把SQL查询语句发送给Server层。
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>

执行顺序：

1. from
2. on
3. join
4. where
5. group by
6. having
7. select
8. order by
9. limit

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。CHAR长度不可变，效率较高；VARCHAR长度可     变，但效率没有CHAR高。

       4.日期

        比较常用的有year、time、date、datetime、timestamp等。

👩‍💻面试官追问：详细说一说CHAR和VARCHAR的区别

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

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

5.说一下InnoDB 和 MyISAM 的区别？

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

• InnoDB

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

• MyISAM

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

总之，具体的选择还要看业务场景根需求。

索引篇

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

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

索引的作用就相当于目录的作用。打个比方：我们在查字典的时候如果没有目录，我们需要一页一页的去查，效率非常的低，但是，如果有目录后，我通过目录能直接找到所在字的位置，我们只需要翻到那一页即可。

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

• 优点：

1. 提高查询速度：索引可以加速数据的检索速度，通过索引，数据库系统可以避免全表扫描，直接定位到需要的数据行，从而大大减少I/O操作次数，提高查询效率。

2. 保证数据的唯一性：通过创建唯一索引，可以保证数据库表中每一行数据的某列或多列组合是唯一的，从而防止数据的重复插入。

3. 加速表和表之间的连接：在使用数据库的连接查询时，如果连接条件被索引了，那么查询速度就会提升。

4. 使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间：因为索引是已经排序好的数据结构，所以利用索引可以快速地完成分组和排序操作。

5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能：数据库优化器会基于索引的存在来选择最优的查询计划。

• 缺点：

1. 创建索引和维护索引需要时间：当对表进行增删改操作时，索引也需要被相应地更新，这会增加额外的I/O操作，降低写操作的性能。

2. 索引需要占用物理空间：除了数据表本身要占用数据空间之外，每一个索引还要占用一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护：这就会降低增删改操作的性能。

4. 虽然索引可以提高查询速度，但是会降低更新表的速度：因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

5. 索引只是提高效率的一个因素：如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句，提高索引的利用率。

6. 并不是所有的表都需要建立索引：并不是所有的索引都会提高查询性能，有些索引可能从未被使用到，或者会很少使用到，这样的索引称为冗余索引，反而会降低系统的性能。因此，需要定期审查和优化索引。

大家在回答的时候，如果记不住所有的话，回答上面标红的部分，也是可以的。

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

• 适合建索引：
  • 在最频繁使用的、用以缩小查询范围的字段上建立索引，比如：频繁作为WHERE条件语句查询的字段
  • 在频繁使用的、需要排序和分组的字段上建立索引
  • 关联字段：如外键字段，当表与表之间进行关联查询时，关联字段上建立索引可以加速查询过程。
• 不适合建索引：
  • 对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。
  • 对于一些特殊的数据类型，不宜建立索引，比如︰**文本字段(text)**等。
  • 频繁更新的字段：因为索引本身也需要被更新，如果字段更新频繁，那么索引的维护成本会很高，从而影响写入性能。

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

1. 普通索引：
   • 这是最基本的索引，它没有任何限制，允许在定义索引的列中插入重复值和空值。
   • 主要用于加速查询，常以B+树和哈希索引为主。
   • 创建方式多样，可以在创建表时直接创建，也可以后续通过修改表结构添加。
2. 唯一索引：
   • 与普通索引类似，但索引列的值必须唯一，不允许有重复值。
   • 允许有空值，但如果是组合索引，则列值的组合必须唯一。
3. 主键索引：
   • 主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。
   • 在定义主键时自动创建，所有属性列唯一而且不能为空。
4. 组合索引：
   • 指在多个字段上创建的索引。
   • 只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。
   • 使用组合索引时遵循最左前缀集合。
5. 全文索引：
   • 主要用来查找文本中的关键字，而不是直接与索引中的值相比较。
   • 类似于搜索引擎，用于提高文本匹配的速度。

6. 前缀索引(Prefix)

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

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

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三种存储引擎对各种索引类型的支持：

4、说说什么是B+树？

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

👩‍💻面试官继续问：为什么使用B+树？

• 范围查询效率高：由于B+树的叶子节点形成有序链表，支持范围查询非常高效。对于数据库查询来说，范围查询是非常常见的操作，因此B+树可以更好满足整个需求。
• 顺序访问性能好：B+树的叶子节点形成有序链表，在连续的数据查询中，B+树可以利用磁盘预读特性，提高数据的顺序访问性能，减少磁盘I/O次数。
• 更少的磁盘I/O：B+树的非叶子节点只存储关键字和指针，相比于B树，每个节点可以存储更多的关键字，减少磁盘I/O次数，提高了数据访问效率。
• 内容友好：B+树索引的内部节点只包含键值，相对于B树来说更加紧凑，可以节省内存空间。在数据库系统中，索引通常需要缓存在内存中以提高查询性能，因此B+树索引更加适合。

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

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

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

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

事务篇

1、谈谈你对事务的理解？

事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。通俗来说：就是要么同时成功，要么同时失败。

举个经典的转账例子来说：

        假如小明暗恋小红，小明520当天给小红转账了520元，这个过程涉及到两个关键的操作，小明的账户需要扣除520元，小红的账户增加了520元。如果在转账时银行系统突然卡顿或者其他原因，导致小明的账户扣除了520元，而小红的账户没有增加520元，这样的情况肯定是不允许出现的。事务就是要保证转账和收账两个操作同时成功或者同时失败。

2、说说事务的ACID特性？

• A原子性(atomicity) 指的是一个事务中的操作要么全部成功，要么全部失败。
• C一致性(consistency) 指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱，假设中间sql执行过程中系统崩溃A也不会损失100块，因为事务没有提交，修改也就不会保存到数据库。
• I隔离性(isolation) 指的是一个事务的修改在最终提交前，对其他事务是不可见的。
• D持久性(durability) 指的是一旦事务提交，所做的修改就会永久保存到数据库中。

👩‍💻面试官继续问：ACID靠什么保证的呢？

• A原子性(atomicity) 由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql。
• C一致性(consistency) 一般由代码层面来保证。
• I隔离性(isolation) 由MVCC来保证。
• D持久性(durability) 由内存和redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复。

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

• 丢失修改：

        T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。

• 脏读：

        T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改（回滚），那么 T2 读取的数据是脏数据。

• 不可重复读：​​​​​​

        T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

• 幻读：

        T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

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

• 未提交读(READ UNCOMMITTED) 事务中的修改，即使没有提交，对其它事务也是可见的。可能会导致脏读、不可重复读、幻读。
• 提交读(READ COMMITTED) 一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。可能会导致不可重复读、幻读。
• 可重复读(REPEATABLE READ) 保证在同一个事务中多次读取同样数据的结果是一样的。可能造成幻读。
• 可串行化(SERIALIZABLE) 强制事务串行执行。

锁篇

1、MySQL 锁的类型有哪些呢？

MySQL中的锁类型非常丰富，根据不同的分类标准，可以有多种划分方式。我们今天从两个维度来说。

• 共享锁(简称S锁)和排他锁(简称X锁)
  • 读锁是共享的，可以通过lock in share mode实现，这时候只能读不能写。
  • 写锁是排他的，它会阻塞其他的写锁和读锁。从颗粒度来区分，可以分为表锁和行锁两种。
• 表锁和行锁
  • 表锁会锁定整张表并且阻塞其他用户对该表的所有读写操作，比如alter修改表结构的时候会锁表。
  • 行锁又可以分为乐观锁和悲观锁
    • 悲观锁可以通过for update实现
    • 乐观锁则通过版本号实现。

两个维度结合来看：

• 共享锁(行锁):Shared Locks
  • 读锁(s锁),多个事务对于同一数据可以共享访问,不能操作修改
  • 使用方法:
    • 加锁:SELECT * FROM table WHERE id=1 LOCK IN SHARE MODE
    • 释锁:COMMIT/ROLLBACK
• 排他锁（行锁）：Exclusive Locks
  • 写锁(X锁)，互斥锁/独占锁,事务获取了一个数据的X锁，其他事务就不能再获取该行的读锁和写锁（S锁、X锁），只有获取了该排他锁的事务是可以对数据行进行读取和修改
  • 使用方法:
    • DELETE/ UPDATE/ INSERT -- 加锁
    • SELECT * FROM table WHERE ... FOR UPDATE -- 加锁
    • COMMIT/ROLLBACK -- 释锁
• 意向共享锁(IS)
  • 一个数据行加共享锁前必须先取得该表的IS锁，意向共享锁之间是可以相互兼容的 意向排它锁(IX) 一个数据行加排他锁前必须先取得该表的IX锁，意向排它锁之间是可以相互兼容的 意向锁(IS、IX)是InnoDB引擎操作数据之前自动加的，不需要用户干预; 意义： 当事务操作需要锁表时，只需判断意向锁是否存在，存在时则可快速返回该表不能启用表锁
  • 意向共享锁(IS锁)（表锁）：Intention Shared Locks
    • 表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁 前必须先取得该表的IS锁。
  • 意向排它锁(IX锁)（表锁）：Intention Exclusive Locks
    • 表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他 锁前必须先取得该表的IX锁。

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

1. 分析死锁图：使用 SHOW ENGINE INNODB STATUS 命令可以显示当前正在运行的事务和死锁信息，通过分析输出可以找出导致死锁的事务和资源。

2. 检查事务隔离级别：事务隔离级别可能影响死锁的发生，如果使用的是可重复读或串行化隔离级别，可能会增加死锁的风险。

3. 重现死锁：如果无法从日志或死锁图中找到足够的信息，可以尝试使用与实际环境相似的测试数据和并发请求来模拟死锁情况。

4. 使用锁信息查询：通过查询 INFORMATION_SCHEMA 中的INNODB_LOCKS和INNODB_LOCK_WAITS 表，可以查看当前锁的状态和哪些事务在等待哪些锁。

5. 死锁日志分析：通过 show engine innodb status 查看死锁日志，分析死锁的具体情况，包括事务的状态、持有的锁和等待的锁。

6. 优化事务执行顺序：重新排列事务的执行顺序，以减少死锁的可能性。

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

• 乐观锁

        乐观锁的核心思想是，认为数据在大部分情况下不会发生冲突，因此在数据操作时不会立即加锁。乐观锁通常通过数据版本控制（Version Control）来实现，即在数据表中增加一个版本号字段或时间戳字段。

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

• 悲观锁

        悲观锁的核心思想是，认为数据在操作过程中很可能发生冲突，因此在数据操作前就加锁，确保数据在当前事务中的一致性和完整性。

悲观锁实现方式：在读取数据时就加锁，其他事务必须等待锁释放后才能进行操作。在MySQL中，悲观锁可以通过 SELECT ... FOR UPDATE 语句实现，将数据行锁定，直到当前事务结束。

区别 

• 锁的时机：乐观锁在数据更新时检查冲突，悲观锁在数据读取时就加锁。
• 锁的粒度：乐观锁通常不需要数据库锁机制，通过逻辑上的版本控制实现；悲观锁则依赖数据库的锁机制，可能涉及到行锁、表锁等。
• 性能影响：乐观锁在没有冲突的情况下可以提高性能，减少了锁的开销；悲观锁可能会因为锁的争用导致性能下降。
• 适用场景：乐观锁适用于冲突较少的环境，悲观锁适用于冲突较多或对数据一致性要求高的环境。
• 实现复杂度：乐观锁的实现相对简单，主要依赖版本号或时间戳；悲观锁的实现依赖数据库的锁机制，可能更复杂。

4、说说表级锁和行级锁两者区别？

• 表级锁

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

• 行级锁

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

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

InnoDB 的行锁是通过给索引上的索引项加锁来实现的。这意味着，只有在通过索引条件检索数据时，InnoDB 才使用行级锁；如果操作的数据没有使用到索引，那么锁就会退化为表锁。

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

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

• 记录锁（Record Locks）：锁定单个索引记录。
• 间隙锁（Gap Locks）：锁定一个区间，但不包括区间的记录。间隙锁主要用于防止幻读，确保在范围查询中，即使在区间内没有记录，也不会插入新的记录。
• 临键锁（Next-Key Locks）：结合了记录锁和间隙锁，锁定一个索引记录及该记录前面的区间。

5、什么是排他锁和共享锁？、

上面我们也有提到过这两个锁，在这里简单给大家说一下。

排他锁（Exclusive Lock）：

• 排他锁是一种行锁，也称为写锁。当一个事务获取了排他锁后，其他事务无法再获取该数据的任何锁，包括共享锁和排他锁。
• 排他锁适用于需要修改数据的操作，它确保在事务修改数据时，其他事务无法读取或修改相同的数据，从而保证了数据的一致性。

共享锁（Shared Lock）：

• 共享锁是一种共享访问锁，也称为读锁。多个事务可以同时获取相同数据项的共享锁，彼此之间不会排斥。
• 共享锁只适用于读操作，它允许多个事务同时读取相同数据，提高了并发性能。

优化篇

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

可以从以下几个方面来回答：

优化表结构

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

• .尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

• 最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库。备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。注意：不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

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

优化查询

• 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

• 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。建议使用 union 替换 or。

• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。

• in 和 not in 也要慎用，否则会导致全表扫描。

• 优化嵌套查询时可以将子查询尽量替换为多表连接查询（JOIN）。

• 任何查询也不要出现 select * ！！！

索引优化

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

其他优化

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

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

注：更多的优化方案大家可以参考这篇文章SQL优化，写的很不错。

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

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

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

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

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

• 模糊查询时，条件以%开头。如：

select id from t where name like ‘%abc%’

• .如果在 where 子句中使用参数，也会导致全表扫描。如：

select id from t where num = @num

• .应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2 = 100  //修改前select id from t where num = 100*2 //修改后

• 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3) = ’abc’       -–name以abc开头的id//应修改为：
select id from t where name like 'abc%'

其他的情况参考优化篇第一题的优化查询的答案。

主从复制&读写分离篇

1、说说MySQL的主从复制？

主要涉及三个线程: binlog 线程、I/O 线程和 SQL 线程。

• binlog 线程 : 负责将主服务器上的数据更改写入二进制日志中。
• I/O 线程 : 负责从主服务器上读取二进制日志，并写入从服务器的中继日志中。
• SQL 线程 : 负责读取中继日志并重放其中的 SQL 语句。

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

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

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

主从同步延迟是数据库主从复制架构中常见的问题，通常由多种因素引起，包括主库负载过高、从库负载过高、网络延迟、硬件性能不足、MySQL配置不合理等。

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

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

• 优化主库性能：确保主库能够高效处理事务，避免长时间运行的事务或大量小事务的累积。

• 优化从库性能：提高从库的硬件配置，比如使用更快的CPU、更多的内存和更快的存储系统。

• 网络优化：确保主从服务器之间的网络连接是高速且稳定的，减少数据传输延迟。

• 减少锁等待：优化SQL查询，避免在从库上执行大型的、长时间的查询操作，这可能会引起锁等待，从而导致延迟。

• 并行复制：在MySQL 5.6及以后的版本中，可以使用并行复制的方式，通过多个工作线程并行执行从库的SQL线程任务，以提高复制效率。

• 优化配置：合理配置MySQL的复制相关参数，比如sync_binlog、innodb_flush_log_at_trx_commit等，以减少I/O操作的延迟2022。

• 硬件升级：升级服务器硬件，比如使用SSD代替传统硬盘，可以显著提高I/O性能22。

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

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

• 全同步复制

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

• 半同步复制

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

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

数据库读写分离是一种常见的数据库架构策略，用于提高数据库的并发处理能力，同时确保数据的一致性和可用性。

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

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

总结

为了方便大家理解，这里给大家整理的面试题答案会有点长，大家一定要在理解的基础上记忆，面试的时候用自己的话把大致意思表达出来即可。