sql21_0">3. Mysql(21)

1. 第三范式的作用与原理？（B站）

   数据库范式有 3 种：

   • 1NF(第一范式)：属性不可再分。

     1NF 是所有关系型数据库的最基本要求 ，也就是说关系型数据库中创建的表一定满足第一范式。

   • 2NF(第二范式)：1NF 的基础之上，消除了非主属性对于码的部分函数依赖。

   • 3NF(第三范式)：3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖 。

     作用：

     1. 减少数据冗余：通过确保非主键字段直接依赖于主键，避免了在多个表中重复存储相同的数据。
     2. 避免更新异常：当更新操作只影响一部分记录时，第三范式有助于保证数据的一致性和完整性，减少了因为更新一部分数据而可能引起的错误。
     3. 提高数据一致性：由于数据冗余减少，数据的维护更加容易，从而提高了数据的一致性。

     原理：

     1. 直接依赖：非主键字段必须直接依赖于主键，这意味着任何非主键字段不能依赖于另一个非主键字段。
     2. 消除传递依赖：如果存在非主键字段依赖于另一个非主键字段，或者依赖于由多个字段组成的主键的一部分，这样的设计就违反了第三范式。

     举例：

     假设有一个学生表，包含学号、姓名、系名和系主任等字段。如果系主任的信息依赖于系名，而系名又依赖于学号，那么系主任就传递依赖于学号。这种情况下，学生表就不符合第三范式，因为它存在传递依赖。

     为了满足第三范式，可以将学生表拆分为两个表：

     1. 学生表：学号、姓名、系名
     2. 系表：系名、系主任

     这样，系主任直接依赖于系表的主键（系名），而学生表中的系名也依赖于系表的主键，从而消除了传递依赖。

2. MySQL基础架构？Mysql的存储引擎及架构？sql>mysql为什么用innodb?（康泰）

   1.MySQL基础架构？

   组件的基本作用：

   • 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
   • 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
   • 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
   • 优化器： 按照 MySQL 认为最优的方案去执行。
   • 执行器： 执行语句，然后从存储引擎返回数据。

   简单来说 MySQL 主要分为 Server 层和存储引擎层：

   • Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，还有一个通用的日志模块 binlog 日志模块。
   • 存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。

   

   2.为什么用innodb/和MyISAM对比？

   • InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。
   • InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别。MyISAM 不提供事务支持。
   • InnoDB 支持外键，MyISAM 不支持外键。
   • InnoDB 支持MVCC，MyISAM 不支持 MVCC，
   • InnoDB 支持数据库异常崩溃后的安全恢复，MyISAM 不支持。
   • InnoDB 的性能比 MyISAM 更强大。

3.存储引擎架构

MySQL 存储引擎采用的是 插件式架构 ，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

3. SQL语句在MySQL中的执行过程？

   分为查询语句和更新语句两种。

   1.查询语句

   sql>mysql">select * from tb_student  A where A.age='18' and A.name=' 张三 ';
   

   • 权限校验（如果命中缓存）—>查询缓存—>分析器—>优化器—>权限校验—>执行器—>引擎

   • 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 SQL 语句为 key 在内存中查询是否有结果，如果有直接返回缓存，如果没有，执行下一步。

   • 通过分析器进行词法分析，提取 SQL 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student，需要查询所有的列，查询条件是这个表的 id=‘1’。然后判断这个 SQL 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。

   • 接下来就是优化器进行确定执行方案，上面的 SQL 语句，可以有两种执行方案：a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。

   • 再次进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

   2.更新语句

   sql>mysql">update tb_student A set A.age='19' where A.name=' 张三 ';
   

   这条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志啦，这就会引入日志模块了，MySQL 自带的日志模块是 binlog（归档日志） ，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志），我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

   • 分析器---->权限校验---->执行器—>引擎—redo log(prepare 状态)—>binlog—>redo log(commit 状态)
   • 先查询到张三这一条数据，不会走查询缓存，因为更新语句会导致与该表相关的查询缓存失效。
   • 然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
   • 执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
   • 更新完成。

   假设 redo log 处于预提交状态，binlog 也已经写完了，这个时候发生了异常重启会怎么样呢？
   这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

   • 判断 redo log 是否完整，如果判断是完整的，就立即提交。
   • 如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。

4. 介绍一下sql>mysql里面的索引？（百度）索引底层实现？（腾讯、字节）

   • 索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

   • 索引的作用就相当于书的目录。我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

   • MySQL的索引是通过一种叫做B+树的数据结构来实现的。B+树是一种平衡树，它可以保持数据有序，并且可以高效地进行搜索、插入和删除操作。在MySQL中，索引是在存储引擎层面实现的，而不是在服务器层面。不同的存储引擎可能使用不同的数据结构来实现索引，例如InnoDB和MyISAM。

5. 介绍B+树的结构？（滴滴）B+树和B树的区别？B+树的叶子结点之间是单向链表还是双向链表？（腾讯）

   1.介绍B+树的结构？

   • B 树也称 B-树,全称为 多路平衡查找树 ，B+树的每个节点包含多个子节点，并且每个节点可以存储多个键值对（键和数据指针）。在B+树中，所有的键值对都存储在叶子节点上，而非叶子节点仅存储键值对的键部分。

   2.B+树和B树的区别？

   • B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
   • B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
   • B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
   • 在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+树的范围查询，只需要对链表进行遍历即可。

   综上，B+树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

   3.B+树的叶子结点之间是单向链表还是双向链表？

   B+树的叶子节点是相互链接的，形成一个链表。这使得顺序访问操作（如范围查询）更加高效，因为可以通过叶子节点间的指针快速地移动到前一个或后一个叶子节点。

6. 介绍一下事务（百度)，事务的四大特性？undo log如何保证事务的原子性？redolog如何保证事务的持久性？（快手）

   1.事务是逻辑上的一组操作，要么都执行，要么都不执行。

   2.事务的四大特性（ACID）

   • 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
   • 一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
   • 隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
   • 持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

   只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的！

   3.undo log如何保证事务的原子性？

   ​ Undo Log记录了事务对数据修改操作之前的数据状态，事务失败或回滚时，可以使用这些日志来回滚到事务开始前的状态，确保数据的一致性。

   ​ 当事务中的操作对数据进行修改时，如INSERT、UPDATE或DELETE，数据库系统会先记录一条Undo Log，然后再执行实际的数据修改。如果事务成功提交，Undo Log中的信息将不再需要，并会在事务提交后被清除或保留以供MVCC（多版本并发控制）使用。如果事务失败或用户执行了ROLLBACK，数据库系统将利用Undo Log中的信息将数据回滚到事务开始前的状态。

   4.Redolog如何保证事务的持久性？

   ​ Redo Log（重做日志）是MySQL InnoDB存储引擎中的一种日志文件，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

   ​ 在InnoDB引擎的内存结构中，主要的内存区域就是缓冲池，在缓冲池中缓存了很多的数据页。当我们在一个事务中，执行多个增删改的操作时，InnoDB引擎会先操作缓冲池中的数据，如果缓冲区没有对应的数据，会通过后台线程将磁盘中的数据加载出来，存放在缓冲区中，然后将缓冲池中的数据修改，修改后的数据页我们称为脏页。而脏页则会在一定的时机，通过后台线程刷新到磁盘中，从而保证缓冲区与磁盘的数据一致。假如刷新到磁盘的过程出错了，但提示给用户事务成功，而数据却没有持久化下来，这就出现问题了，没有保证事务的持久性。

   ​ Redo Log由两部分组成：一是内存中的重做日志缓存（redo log buffer），二是磁盘上的重做日志文件（redo log file）。

   ​ 有了redo log之后，当对缓冲区的数据进行增删改之后（事务运行过程中），会首先将操作的数据页的变化，记录在redo log buffer中。事务提交时，会将redo log buffer中的数据刷新到redo log磁盘文件中。过一段时间之后，如果刷新缓冲区的脏页到磁盘时，发生错误，此时就可以借助于 redo log 进行数据恢复，这样就保证了事务的持久性。而如果脏页成功刷新到磁盘，此时redo log就没有作用了，就可以删除了。

   

   TIP：那为什么每一次提交事务，要刷新redo log到磁盘中呢，而不是直接将buffer pool中的脏页刷新到磁盘呢？

   ​ 因为在业务操作中，我们操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘。而redo log在往磁盘文件中写入数据，由于是日志文件，所以都是顺序写的。顺序写的效率，要远大于随机写。这种先写日志的方式，称之为 WAL（Write-Ahead Logging）

7. 事务还没提交的时候，redo log 能不能被持久化到磁盘呢？（字节）

   事务还没有提交的时候，redo log 是有可能被持久化到磁盘的。 redolog 的具体落盘操作是这样的：在事务运行的过程中，MySQL 会先把日志写到 redolog buffer 中，等到事务真正提交的时候，再统一把 redolog buffer 中的数据写到 redolog 文件中。不过这个从 redolog buffer 写到 redolog 文件中的操作也就是 write 并不就是落盘操作了，这里仅仅是把 redolog 写到了文件系统的 page cache 上，最后还需要执行 fsync 才能够实现真正的落盘。

   也就是说，redolog 其实存在三种状态：

   • 事务执行过程中，存在 MySQL 的进程内存中的 redolog buffer 中
   • 事务提交，执行 write 操作存在文件系统的 page cache 中，但是没有执行 fsync 操作持久化到磁盘
   • 事务提交，执行 fsync 操作持久化到磁盘

   主要有三种可能的原因：

   1. 第一种情况：InnoDB 有一个后台线程，每隔 1 秒轮询一次，具体的操作是这样的：调用 write 将 redolog buffer 中的日志写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。而在事务执行中间过程的 redolog 都是直接写在 redolog buffer 中的，也就是说，一个没有提交的事务的 redolog，也是有可能会被后台线程一起持久化到磁盘的。
   2. 第二种情况：innodb_flush_log_at_trx_commit 设置是 1，这个参数的意思就是，每次事务提交的时候，都执行 fsync 将 redolog 直接持久化到磁盘（还有 0 和 2 的选择，0 表示每次事务提交的时候，都只是把 redolog 留在 redolog buffer 中；2 表示每次事务提交的时候，都只执行 write 将 redolog 写到文件系统的 page cache 中）。举个例子，假设事务 A 执行到一半，已经写了一些 redolog 到 redolog buffer 中，这时候有另外一个事务 B 提交，按照 innodb_flush_log_at_trx_commit = 1 的逻辑，事务 B 要把 redolog buffer 里的日志全部持久化到磁盘，这时候，就会带上事务 A 在 redolog buffer 里的日志一起持久化到磁盘
   3. 第三种情况：redo log buffer 占用的空间达到 redolog buffer 大小（由参数 innodb_log_buffer_size 控制，默认是 8MB）一半的时候，后台线程会主动写盘。不过由于这个事务并没有提交，所以这个写盘动作只是 write 到了文件系统的 page cache，仍然是在内存中，并没有调用 fsync 真正落盘

8. (并发事务带来了哪些问题？)介绍脏读、不可重复读、幻读以及实际案例？Mysql的常见的事务隔离级别，为了解决什么问题？这些隔离级别是如何实现的？（字节3、美团2、滴滴4、B站、百度）

   在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

   1.定义及实际案例

   （1）脏读（Dirty read）

   ​ 一个事务读取数据并且对数据进行了修改，这个修改对其他事务来说是可见的，即使当前事务没有提交。这时另外一个事务读取了这个还未提交的数据，但第一个事务突然回滚，导致数据并没有被提交到数据库，那第二个事务读取到的就是脏数据，这也就是脏读的由来。

   ​ 例如：事务 1 读取某表中的数据 A=20，事务 1 修改 A=A-1，事务 2 读取到 A = 19,事务 1 回滚导致对 A 的修改并未提交到数据库， A 的值还是 20。

   

   （2）不可重复读（Unrepeated read）

   指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

   例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 再次读取 A =19，此时读取的结果和第一次读取的结果不同。

   

   （3）幻读（Phantom read）

   幻读与不可重复读类似。它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

   例如：事务 2 读取某个范围的数据，事务 1 在这个范围插入了新的数据，事务 2 再次读取这个范围的数据发现相比于第一次读取的结果多了新的数据。

   

   TIP：不可重复读和幻读有什么区别？

   • 不可重复读的重点是内容修改或者记录减少比如多次读取一条记录发现其中某些记录的值被修改；
   • 幻读的重点在于记录新增比如多次执行同一条查询语句（DQL）时，发现查到的记录增加了。

   幻读其实可以看作是不可重复读的一种特殊情况，单独把幻读区分出来的原因主要是解决幻读和不可重复读的方案不一样。

   • 举个例子：执行 delete 和 update 操作的时候，可以直接对记录加锁，保证事务安全。而执行 insert 操作的时候，由于记录锁（Record Lock）只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁（Gap Lock）。也就是说执行 insert 操作的时候需要依赖 Next-Key Lock（Record Lock+Gap Lock） 进行加锁来保证不出现幻读。

   2.Mysql的事务隔离级别？为了解决什么问题？

   （1）READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

   （2）READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

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

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

   隔离级别	脏读	不可重复读	幻读
   READ-UNCOMMITTED	√	√	√
   READ-COMMITTED	×	√	√
   REPEATABLE-READ	×	×	√
   SERIALIZABLE	×	×	×

   3.这些隔离级别是如何实现的？

   ​ MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。SERIALIZABLE 隔离级别是通过锁来实现的，READ-COMMITTED 和 REPEATABLE-READ 隔离级别是基于 MVCC 实现的。不过， SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。

   ​ MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。但是！InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的，主要有下面两种情况：

   • 快照读：由 MVCC 机制来保证不出现幻读。
   • 当前读：使用 Next-Key Lock 进行加锁来保证不出现幻读，Next-Key Lock 是行锁（Record Lock）和间隙锁（Gap Lock）的结合，行锁只能锁住已经存在的行，为了避免插入新行，需要依赖间隙锁。

   因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED ，但是你要知道的是 InnoDB 存储引擎默认使用 REPEATABLE-READ 并不会有任何性能损失。InnoDB 存储引擎在分布式事务的情况下一般会用到 SERIALIZABLE 隔离级别。

9. 并发事务的控制方式有哪些：锁和mvcc?（字节）

   1.锁

   MySQL 中并发事务的控制方式无非就两种：锁 和 MVCC。锁可以看作是悲观控制的模式，多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模式。

   锁 控制方式下会通过锁来显式控制共享资源而不是通过调度手段，MySQL 中主要是通过 读写锁 来实现并发控制。

   • 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
   • 排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁（锁不兼容）。

   读写锁可以做到读读并行，但是无法做到写读、写写并行。另外，根据锁粒度的不同，又被分为 表级锁(table-level locking) 和 行级锁(row-level locking) 。InnoDB 不光支持表级锁，还支持行级锁，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。

   TIP：InnoDB有哪几类行锁？

   InnoDB 行锁是通过对索引数据页上的记录加锁实现的，MySQL InnoDB 支持三种行锁定方式：

   • 记录锁（Record Lock）：属于单个行记录上的锁。
   • 间隙锁（Gap Lock）：锁定索引记录之间的间隙，不包括记录本身。
   • 临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

   在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

   2.MVCC

   MVCC 是多版本并发控制方法，即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。

   MVCC 在 MySQL 中实现所依赖的手段主要是: 隐藏字段、read view、undo log。

   • undo log : undo log 用于记录某行数据的多个版本的数据。
   • read view 和 隐藏字段 : 用来判断当前版本数据的可见性。

10. for update的原理？for update加的是什么锁？间隙锁是什么？for update能够防止幻读吗？（字节）

    • for update 的原理是在事务中，当执行 select … for update 时，会将查询的结果集加锁，其他事务不能修改这些数据直到当前事务提交或回滚。for update 加的是排它锁（Exclusive Lock），也就是行级锁，它阻止其他事务对这些行的读取和修改。

    • 间隙锁（Gap Lock）是 InnoDB 存储引擎为了防止幻读而使用的一种锁，它锁定的是索引记录之间的间隙，但不包括索引本身。间隙锁的主要目的是防止其他事务在被锁定的范围内插入新的数据。

    • for update 可以防止幻读，因为在可重复读隔离级别下，InnoDB 会使用间隙锁来锁定查询中涉及的间隙，从而阻止其他事务在这些间隙中插入新行。这样，即使其他事务试图插入新行，也会被阻塞，直到当前事务结束。

    • 在可重复读隔离级别下，for update 通常用于以下场景：

      1. 当需要确保被读取的数据在事务结束前不被其他事务修改时。
      2. 当需要防止其他事务在相同的查询条件下读取或插入新的行时。

      需要注意的是，for update 可能会导致死锁，因为它会锁定多行数据。在使用时，应该谨慎以避免长时间锁定资源，并且在设计事务时考虑到可能的死锁情况。

11. 数据库余额为100，有一个事务A和一个事务B，两个事务同时扣减50，理论上剩余0，但实际上剩余50，是怎么做到的？

    答：REPEATABLE-READ(可重复读) 隔离级别下的mvcc

12. 索引失效的情况有哪些？（小米）

    • 不满足最左匹配原则
    • 使用了select *
    • 索引列上有计算
    • 索引列使用了函数
    • 字段类型不同
    • like左边包含%
    • 列对比
    • 使用or关键字
    • not in 和 not exists
    • order by的坑

13. 如果有索引 联合索引（a，b，c），查询 SELECT * FROM table where a=1 AND c=1会走索引么？c=1 呢？b=1 AND c=1呢？（滴滴）

    答：

    查询 a=1 AND c=1：根据最左前缀匹配原则，查询可以使用索引的前缀部分。因此，该查询仅在 a=1 上使用索引，然后对结果进行 c=1 的过滤。

    查询 c=1 ：由于查询中不包含最左列 a，根据最左前缀匹配原则，整个索引都无法被使用。

    查询b=1 AND c=1：和第二种一样的情况，整个索引都不会使用。

    ---

    最左前缀匹配原则

    • 最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，这样可以提高查询效率。

    • 最左匹配原则会一直向右匹配，直到遇到范围查询（如 >、<）为止。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配。

    • 假设有一个联合索引(column1, column2, column3)，其从左到右的所有前缀为(column1)、(column1, column2)、(column1, column2, column3)（创建 1 个联合索引相当于创建了 3 个索引），包含这些列的所有查询都会走索引而不会全表扫描。

    • 我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

14. 联合索引 （a，b，c），select * from table where a=1 and b＞3 and c＝2，为什么只能使用a字段，b＞3就不能用？（得物）

    答：b > 3可以走索引，因为查询出来的字段 a = 1 内部的字段 b 是排好序的，所以b > 3可以走索引，但是b > 3是范围查询，在这个范围内的字段 c 不是有序的，因此 c 字段用不上索引，但是可以走索引下推。

    索引下推：

    索引下推（Index Condition Pushdown，简称 ICP） 是 MySQL 5.6 版本中提供的一项索引优化功能，它允许存储引擎在索引遍历过程中，执行部分 WHERE字句的判断条件，直接过滤掉不满足条件的记录，从而减少回表次数，提高查询效率。

    • 假设我们有一个名为 user 的表，其中包含 id, username, zipcode和 birthdate 4 个字段，创建了联合索引(zipcode, birthdate)。

    sql>mysql"># 查询 zipcode 为 431200 且生日在 3 月的用户
    # birthdate 字段使用函数索引失效
    SELECT * FROM user WHERE zipcode = '431200' AND MONTH(birthdate) = 3;
    

    • 没有索引下推之前，即使 zipcode 字段利用索引可以帮助我们快速定位到 zipcode = '431200' 的用户，但我们仍然需要对每一个找到的用户进行回表操作，获取完整的用户数据，再去判断 MONTH(birthdate) = 3。
    • 有了索引下推之后，会将过滤的场景下推到存储引擎层面上，存储引擎会在使用zipcode 字段索引查找zipcode = '431200' 的用户时，同时判断MONTH(birthdate) = 3。这样，只有同时满足条件的记录才会被返回，减少了回表次数，还可以减少存储引擎层和 Server 层的数据传输量。

15. 如何给下列sql语句加上索引

    （1）SELECT * FROM table WHERE a > 1 and b = 2;

    答：如果建立索引(a,b),那么a>1是可以走到索引的，但是b=2就没法走到索引了。但是如果建立索引(b,a),那么sql语句会被自动优化成 where b=2 and a> 1，这样a和b都能走到索引，所以建立索引(b,a)比较合适。

    （2）SELECT * FROM table WHERE a = 1 ORDER BY b;

    答：对于上述情况，其实加上一个联合索引（a,b）就行，因为在a=1的情况下，b的顺序都是排好的，避免了再次排序。

    （3）SELECT * FROM table WHERE a > 1 ORDER BY b;

    答：这种情况下如果建立了联合索引(a,b)，那么在a>1的情况下b是无序的，需要对b再次排序，所以直接在字段a上建立索引就可以了，完全没必要用联合索引。

    （4）SELECT * FROM table WHERE a = 1 AND b = 2 AND c > 3 ORDER BY c;

    答：上述sql语句直接建立索引(a,b,c)就可以了，具体原因不再赘述。

16. 有表 test ，id为主键，字段(a,b,c)为联合索引，另外还有一个字段d和e，除了e为varchar，其余全是int。

    （1）select * from test where a>1;

    ​ 结果却是不走索引的(用explain查看type为ALL),这是为什么呢？咱们查询条件是a>1,的确是符合最左匹配原则的，那为什么没有走索引呢？其实这个也和咱们sql>mysql的自动优化有关，咱们先来看一看 走索引的过程。首先会把a>1的情况都根据索引筛选出来，因为a是unsigned的，也就是是大于0的，所以a>1的情况基本上是把所有数据筛选出来了，其实这个还不是问题的关键所在，因为咱们查询的字段是select * ,所以每一次查询出来结果之后还需要回表，回表次数是非常多的，所以sql>mysql在内部直接自动优化了，让它不走索引，直接一次性全部查出来算了。

    （2）select id from test where a>1;

    ​ 这次是走索引的了，原理其实和上面情况一样，但是因为咱们查询的字段是id，不需要回表（二级索引的叶子节点通常包含索引列和主键列，也就是索引覆盖了），同样，查询字段 id,a,b,c这四个字段都是不需要回表的。

    （3）select * from test where a>999999;

    ​ 咱们用explain查看这次却是走索引的了，为什么呢？因为虽然需要回表，但是咱们的限制条件a比较大，所以相对的回表次数也少了很多，所以这次走索引比较划算，性能会比较好。

    （4）sql1: select * from test order by a,b,c;
    sql2: select a from test order by a,b,c;

    ​ 答：请问这俩sql哪个会走索引？答案是sql2会走索引，sql1不会，因为联合索引a,b,c的确是按照a,b,c的顺序排好的，但是如果只是select a ，那直接找到叶子节点就可以返回，如果是select * 就还是需要不断的回表，所以sql>mysql会直接选择不走索引直接查询出来，然后用内存进行排序。

17. 一个表有a，b，c，d四项，a是唯一索引，b是普通索引，select * from table where a=1和select * from table where b=1两个语句查询流程有什么区别？（腾讯）

18. select * from tbl_works where status = 1 limit 1000000 10; status是索引字段，这个有什么问题，如何进行优化？执行过程是什么？（深度分页问题）（字节）[https://mp.weixin.qq.com/s/Mx3G2R88EHO0koC8-HcWDA]

    即使 B 是索引字段，性能可能仍然会受到影响，尤其是当偏移量（offset）非常大时。这是因为数据库需要扫描大量的行然后才返回结果，这会导致查询速度变慢。

    执行过程：

    1. 索引查找：数据库首先会在索引 status 上执行一个范围查询，找到第一个 status = 1 的行。
    2. 回表：由于查询需要返回 所有列 的值，数据库可能需要根据索引查找的结果回表查询这些列的具体值。
    3. 跳过记录：数据库继续在索引 status 上查找，跳过接下来的999999行（因为已经找到第一个满足条件的行），然后开始收集接下来的10行。
    4. 返回结果：收集完10行数据后，查询完成并返回结果。

    优化方法：

    （1）子查询分页方式

    sql>mysql">SELECT t1.* FROM tbl_works t1
    WHERE t1.id in (SELECT t2.id from (SELECT id FROM tbl_works WHERE status=1 limit 100000, 10) as t2) 
    

    sql>mysql">// 通过自连接与join定位到目标 ids，然后再将数据取出。
    SELECT * FROM tbl_works t1 
    JOIN (SELECT id from tbl_works WHERE status=1  limit 100000, 10) as t2
    ON t1.id = t2.id  
    

    • 相比于优化前的 SQL，优化后的 SQL 将不需要频繁回表查询了，因为子查询中只查询主键 ID，这时可以使用索引覆盖来实现。那么子查询就可以先查询出一小部分主键 ID，再进行查询，这样就可以大大提升查询的效率了。

19. 查询（MySQL）性能如何优化？实际场景分析索引；（美团）

    MySQL 性能优化是一个系统性工程，涉及多个方面，在面试中不可能面面俱到。因此，建议按照“点-线-面”的思路展开，从核心问题入手，再逐步扩展，展示出你对问题的思考深度和解决能力。

    1. 抓住核心：慢 SQL 定位与分析

    性能优化的第一步永远是找到瓶颈。面试时，建议先从 慢 SQL 定位和分析 入手，这不仅能展示你解决问题的思路，还能体现你对数据库性能监控的熟练掌握：

    • 监控工具： 介绍常用的慢 SQL 监控工具，如 MySQL 慢查询日志、Performance Schema 等，说明你对这些工具的熟悉程度以及如何通过它们定位问题。
    • EXPLAIN 命令： 详细说明 EXPLAIN 命令的使用，分析查询计划、索引使用情况，可以结合实际案例展示如何解读分析结果，比如执行顺序、索引使用情况、全表扫描等。

    2. 由点及面：索引、表结构和 SQL 优化

    定位到慢 SQL 后，接下来就要针对具体问题进行优化。 这里可以重点介绍索引、表结构和 SQL 编写规范等方面的优化技巧：

    • 索引优化： 这是 MySQL 性能优化的重点，可以介绍索引的创建原则、覆盖索引、最左前缀匹配原则等。如果能结合你项目的实际应用来说明如何选择合适的索引，会更加分一些。
    • 表结构优化： 优化表结构设计，包括选择合适的字段类型、避免冗余字段、合理使用范式和反范式设计等等。
    • SQL 优化： 避免使用 SELECT *、尽量使用具体字段、使用连接查询代替子查询、合理使用分页查询、批量操作等，都是 SQL 编写过程中需要注意的细节。

    3. 进阶方案：架构优化

    当面试官对基础优化知识比较满意时，可能会深入探讨一些架构层面的优化方案。以下是一些常见的架构优化策略：

    • 读写分离： 将读操作和写操作分离到不同的数据库实例，提升数据库的并发处理能力。
    • 分库分表： 将数据分散到多个数据库实例或数据表中，降低单表数据量，提升查询效率。但要权衡其带来的复杂性和维护成本，谨慎使用。
    • 数据冷热分离：根据数据的访问频率和业务重要性，将数据分为冷数据和热数据，冷数据一般存储在存储在低成本、低性能的介质中，热数据高性能存储介质中。
    • 缓存机制： 使用 Redis 等缓存中间件，将热点数据缓存到内存中，减轻数据库压力。这个非常常用，提升效果非常明显，性价比极高！

    4. 其他优化手段

    除了慢 SQL 定位、索引优化和架构优化，还可以提及一些其他优化手段，展示你对 MySQL 性能调优的全面理解：

    • 连接池配置： 配置合理的数据库连接池（如 连接池大小、超时时间 等），能够有效提升数据库连接的效率，避免频繁的连接开销。
    • 硬件配置： 提升硬件性能也是优化的重要手段之一。使用高性能服务器、增加内存、使用 SSD 硬盘等硬件升级，都可以有效提升数据库的整体性能。

    5.总结

    在面试中，建议按优先级依次介绍慢 SQL 定位、索引优化、表结构设计和 SQL 优化等内容。架构层面的优化，如读写分离和分库分表、数据冷热分离 应作为最后的手段，除非在特定场景下有明显的性能瓶颈，否则不应轻易使用，因其引入的复杂性会带来额外的维护成本。

20. SQL优化？

    1. 避免使用select * ，而是使用具体的字段代替，使用select * 不会走覆盖索引，会有大量的回表操作，性能比较低。

    2. 尽量使用union all代替union，因为使用union需要对结果进行遍历比较，去掉重复的结果，所以更耗时，而union all可以出现重复的结果，不需要进行遍历。

    3. 小表驱动大表：以小表的结果集驱动大表的结果集。

    4. 批量操作：每次获取数据库连接都会耗费一定的性能，所以我们可以使用批量操作，但是批量操作需要把握一个度，否则会导致数据库响应过慢，如果数据很多，可以分多批次进行处理。

    5. 多用limit：这样查询的结果就会小很多。

    6. 使用连接查询代替子查询：使用子查询需要创建临时表，耗费一定的性能。

    7. join表不宜过多。

    8. 尽量使用inner join代替 left join，因为使用inner join，数据库会默认以小表为驱动，而使用left join是以左边的表为驱动。

    9. 对查询较频繁的字段添加索引，这样可以更快的进行查询。

    10. 进行分组前应尽量缩小数据范围。

    11. sql优化，判断是否走了索引，可以使用explain执行计划查看。

21. 给你一个需求，你需要写一个连接池，你现在可以写一下。

22. Mysql和ES(Elasticsearch)数据不一致会怎么样？同步延迟能否接收，怎么处理不一致？（字节）

23. sql>mysql中如何定位慢查询？

    （1）启用慢查询日志，通过修改 MySQL 配置文件（my.cnf 或 my.ini）来启用它。

    slow_query_log = 1                # 启用慢查询日志
    slow_query_log_file = /var/log/sql>mysql/sql>mysql-slow.log  # 设置慢查询日志文件位置
    long_query_time = 2               # 设置慢查询的时间阈值为 2 秒
    log_queries_not_using_indexes = 1 # 记录未使用索引的查询
    

    （2）分析慢查询日志

    sql>mysqldumpslow -s t /var/log/sql>mysql/sql>mysql-slow.log
    

    （3）EXPLAIN分析SQL查询，可以知道查询是否使用了索引，是否有全表扫描的情况，从而针对性地优化 SQL 语句和数据库结构。