MySQL高频基本面试问题整理

1.IP地址在数据库中如何储存？

现有一个IP地址，你觉得在数据库中以何种方式进行存储最好？性能分析对比 字符串存储 vs 整数存储？为什么数据库通常推荐将IPv4地址存储为32位整数而不是字符串的原因

在数据库中存储 IP 地址时，通常推荐将 IPv4 地址存储为32位整数而不是字符串。

当我们将IPv4地址”192.168.1.8”存储为字符串时，以十进制格式显示，类似于我们平时在浏览器中看到的网址，这种表示方法直观而易读。但一般每个IPv4地址都需要15个字符的存储空间（包括3个”.“分隔符和4个三位数的表示），当数据量大的时候可能会导致：存储空间的浪费+查询和索引时效率降低。因为数据库系统在处理字符串比较时，需要逐字符比较，这可能会增加查询时间。

当我们将IPv4地址存储为32位整数时，它们会以二进制格式表示，例如：3232235776。这种表示方法可能不太直观，但整数只需要4字节的存储空间，远远小于使用字符串存储所需的空间。这意味着在存储大量IPv4地址时，整数存储方式可以显著节省存储空间，还可以提高数据库查询和索引的效率。因为整数比较速度更快，数据库可以更快地执行查询操作，并且可以更有效地利用索引。

因此，虽然字符串存储方式可能更直观和易读，但整数存储方式在存储空间效率和查询效率方面更具优势，这就是为什么数据库通常推荐将IPv4地址存储为32位整数而不是字符串的原因。

2.事务的基本要素（高频考点）

数据库事务(Transanction)正确执行的四个基本要素：

原子性（Atomicity）：事务开始后所有操作，要么全部完成，要么全部不完成，不可能停滞在中间环节。事务执行过程中出错，会回滚（Rollback）到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位
一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到
隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账
持久性（Durability）：事务完成后，该事务所对数据库所作的更改将被保存到数据库之中，不能回滚

3.事务隔离级别（高频考点）

在没有隔离级别的情况下，可能会发生以下情况：

脏读（Dirty Read）：一个事务读取了另一个事务还未提交的数据，如果这个事务回滚，那么读到的数据就是无效的，这种情况称为脏读。
不可重复读（Non-repeatable Read）：一个事务在执行过程中多次读取同一数据，由于其他事务对该数据进行了修改，因此这些读取操作得到的结果可能不同，这种情况称为不可重复读。
幻读（Phantom Read）：一个事务按照相同的查询条件两次查询，但是得到的结果集却不同。这是因为其他事务对该表进行了新增或删除操作，导致当前事务查询到的结果集不一致，这种情况称为幻读。

这些情况都是由于多个事务之间的数据相互干扰导致的，而隔离级别就是用来解决这些问题的。

事务的隔离级别规定了在一个事务内的修改哪些在事务内和事务间可见，哪些不可见。 SQL标准定义了四个隔离级别，一般而言，隔离级别越高，安全性越高，但系统开销更大，并发性能也越差。

通过如下SQL命令可以查看和修改MySQL的事务隔离级别

-- 查看全局事务隔离级别
select @@global.tx_isolation
-- 查看当前会话事务隔离级别
select @@tx_isolation
-- 修改全局事务隔离级别
set global transaction isolation level repeatable read
-- 修改当前会话事务隔离级别
set session transaction isolation level repeatable read

在实际应用中，读未提交级别在并发时会导致很多问题，性能相对于其他隔离级别提高也有限，可串行化级别强制事务串行，并发效率很低，只适合于对数据一致性要求极高的场景，这两个隔离级别都很少使用。 因此在大多数数据库系统中，默认的隔离级别是RC（读已提交）或RR（可重复读）。

MySQL的InnoDB默认隔离级别是RR（可重复读），但与标准SQL不同的是，InnoDB在RR（可重复读）隔离级别下，使用Next-Key锁避免了幻读问题。 也就是说，InnoDB在RR隔离级别下已经能完全保证事务隔离性要求，即达到了SQL标准的Serializable隔离级别。

4.如何解决事务的并发问题(脏读、不可重复读和幻读)（高频考点）

解决事务并发问题（脏读、不可重复读和幻读）的方法通常涉及使用并发控制技术。下面是一些常见的方法：

锁定机制：使用锁定机制可以确保在一个事务修改数据时，其他事务无法同时修改相同的数据。这可以防止脏读和不可重复读。在使用锁定时，需要谨慎处理死锁的可能性，以避免系统陷入无限等待状态。
事务隔离级别：数据库管理系统提供不同的事务隔离级别，如读未提交（Read Uncommitted）、读提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。通过选择适当的隔离级别，可以控制并发事务之间的数据访问方式，从而解决不可重复读和幻读问题。
多版本并发控制（MVCC）：MVCC是一种用于处理并发事务的技术，允许每个事务在读取数据时看到之前事务的一致快照，而不会受到其他事务的修改影响。这可以解决不可重复读和幻读问题。
乐观并发控制：乐观并发控制假设事务冲突的概率较低，因此不会立即锁定数据。相反，它在提交事务之前检查是否有冲突，并采取适当的行动（如回滚事务）。乐观并发控制通常使用版本号或时间戳等机制来检测冲突。
串行化执行：将并发执行转换为串行执行，确保每个事务按顺序执行。尽管这种方法可以解决并发问题，但会降低系统的并发性能。

请注意，解决并发问题的方法取决于具体的应用场景和数据库管理系统。在实际情况中，可能需要结合使用上述方法或采取其他适合特定环境的措施。此外，开发人员还应该仔细设计数据库模式和事务逻辑，以减少并发问题的出现。

5.脏读的表现和具体解决并发问题（必考）

脏读是指一个事务读取到了另一个事务尚未提交的数据。具体表现为事务A读取了事务B正在修改但尚未提交的数据，如果事务B最终回滚，那么事务A读取到的数据就是无效的或错误的。

解决脏读的一种常见方法是使用锁定机制。 在事务A读取某个数据之前，可以为该数据加上共享锁，表示其他事务可以读取但不能修改。 只有当事务B提交后，事务A才能获取到最新的数据。这样可以保证事务A读取到的数据是经过正确提交的。

另一种解决脏读的方法是使用事务隔离级别中的“读提交”（Read Committed）隔离级别。在该隔离级别下，事务A只能读取到已经提交的数据，而不能读取到未提交的数据。这种隔离级别通过在 读取数据时应用短暂的共享锁或快照来实现。

除了以上方法， 还可以采用多版本并发控制（MVCC）来解决脏读问题。MVCC允许每个事务在读取数据时看到之前事务的一致快照，而不会受到其他事务的修改影响。通过维护不同版本的数据，并根据事务的隔离级别选择合适的数据版本，可以避免脏读的问题。

需要注意的是，解决脏读问题的方法可能会对并发性能产生一定的影响。因此，在实际应用中，需要根据具体场景和需求来选择适当的解决方案，并进行性能测试和优化。

6. 不可重复读的表现和具体解决并发问题（高频考点）

不可重复读是指在一个事务中，对同一数据进行多次读取，但在此期间其他事务对该数据进行了修改，导致事务每次读取到的数据不一致。

具体表现为：

事务A读取某个数据行的值。

在此期间，事务B修改了同一数据行的值，并且提交了修改。

事务A再次读取同一数据行的值，发现与之前读取到的值不同。

解决不可重复读的方法如下：

使用锁定机制： 在事务A读取某个数据之前，为该数据加上共享锁或排他锁，以阻止其他事务对该数据进行修改。这样可以确保事务A在读取期间其他事务不会修改数据。但需要注意，使用过多的锁定可能会影响并发性能。
使用事务隔离级别：选择适当的事务隔离级别可以解决不可重复读问题。在 “可重复读”隔离级别 下，事务A在开始后创建一个一致性快照，保证在整个事务期间读取的数据都是一致的。即使其他事务对数据进行修改，事务A仍然读取到的是最初创建的一致性快照中的数据。不同的数据库管理系统提供不同的隔离级别，需要根据具体情况选择适当的隔离级别。
使用乐观并发控制：乐观并发控制假设事务冲突的概率较低，因此在读取数据时不会立即锁定。相反，它在事务提交前检查是否有冲突，并采取适当的行动（如回滚事务）。乐观并发控制 通常使用版本号或时间戳等机制 来检测冲突。这种方法适用于读取频率较高、写入冲突较少的场景。
采用多版本并发控制（MVCC）来解决脏读问题。MVCC允许每个事务在读取数据时看到之前事务的一致快照，而不会受到其他事务的修改影响。通过维护不同版本的数据，并根据事务的隔离级别选择合适的数据版本，可以避免不可重复读的问题。
应用程序层面的解决方案：在应用程序中，可以通过在读取数据时添加适当的条件和查询语句，或者使用悲观锁或乐观锁来控制并发访问。这样可以避免不可重复读问题的发生。

需要根据具体的应用场景、数据库管理系统和性能要求选择适当的解决方法。在实际应用中，也需要注意权衡并发性能和数据一致性之间的关系，以达到最佳的解决方案。

7.幻读的表现和具体解决并发问题（高频考点）

幻读是指在一个事务中，对一个范围的数据进行查询，但在此期间其他事务插入了新的符合条件的数据，导致事务每次查询到的数据行数不一致。

具体表现为：

事务A执行一个范围查询，返回一组数据行。

在此期间，事务B插入了新的数据行，符合事务A查询条件。

事务A再次执行相同的范围查询，发现返回的数据行数与之前查询到的不同。

解决幻读的方法如下：

使用锁定机制： 在事务A执行范围查询之前，为该范围的数据加上间隙锁或共享锁，以防止其他事务在此期间插入符合条件的新数据。这样可以确保事务A在查询期间范围内的数据不会发生变化。但需要注意，使用过多的锁定可能会影响并发性能。
使用事务隔离级别：选择适当的事务隔离级别可以解决幻读问题。在 “串行化”隔离级别 下，确保每个事物按顺序逐个执行，避免并发带来的幻读问题。
使用乐观并发控制：乐观并发控制假设事务冲突的概率较低，因此在读取数据时不会立即锁定。相反，它在事务提交前检查是否有冲突，并采取适当的行动（如回滚事务）。乐观并发控制 通常使用版本号或时间戳等机制 来检测冲突。这种方法适用于读取频率较高、写入冲突较少的场景。
应用程序层面的解决方案：在应用程序中，可以通过合理的业务逻辑设计，在查询时添加适当的条件和查询语句，或者使用悲观锁或乐观锁来控制并发访问。这样可以避免幻读问题的发生。

8.简述MVCC多版本并发控制（高频考点）

MVCC（Multi-Version Concurrency Control）是一种并发控制机制，用于解决并发环境下的读写冲突和幻读问题。 它通过为每个事务创建独立的快照或版本，实现了事务之间的隔离性。

在MVCC中，每个数据行都会有多个版本，每个版本都有一个时间戳或事务ID范围。每个事务在开始时会获得一个时间戳或事务ID，并且只能读取早于它开始时间的数据版本。这样，即使其他事务在事务执行期间对数据进行了修改，当前事务也只能看到它开始时的一致性视图，避免了脏读和不可重复读问题。

每个事务都有一个版本链，版本链是由该事务创建的所有版本所组成的链表。在该链表上，每个版本都指向前一个版本，最后一个版本指向 NULL。版本链的作用是，当事务需要回滚时，可以沿着版本链将数据恢复到事务开始的状态。

具体的工作流程如下：

读操作：当一个事务要执行读操作时，系统会根据事务开始时的时间戳或事务ID选择对应的数据版本。如果存在多个版本，系统会选择最接近事务开始时间的数据版本。这样事务只能读取早于它开始时间的数据，避免了对未提交的数据进行读取。
写操作：当一个事务要进行写操作时，系统会为其生成一个新的数据版本，并在新版本上进行修改。原始数据不会被直接修改，而是保留在旧版本中供其他事务读取。这样，正在执行读操作的事务不会受到正在执行写操作的事务的影响。
冲突检测和回滚：在MVCC中，当一个事务要进行写操作时，系统会检测是否存在与其冲突的其他事务。如果存在冲突，例如两个事务试图同时修改同一数据行，其中一个事务会被回滚，以保持数据的一致性。

MVCC的优点是能够提供较好的并发性能，因为读操作不会阻塞其他读操作，并且读写操作之间的冲突较少。它可以有效地解决脏读、不可重复读和幻读问题，并在许多数据库管理系统中得到广泛应用，如PostgreSQL、Oracle等。

9.从CRUD角度说一下InnoDB的MVCC实现机制

MVCC可以认为是行级锁的一个变种，它可以在很多情况下避免加锁操作，因此开销更低。MVCC的实现大都都实现了非阻塞的读操作，写操作也只锁定必要的行。InnoDB的MVCC实现，是通过保存数据在某个时间点的快照来实现的。 一个事务，不管其执行多长时间，其内部看到的数据是一致的， 也就是事务在执行的过程中不会相互影响。

简述一下MVCC在InnoDB中的实现：

InnoDB的MVCC，通过在每行记录后面保存两个隐藏的列来实现：一个保存了行的创建时间，一个保存行的过期时间（删除时间），当然，这里的时间并不是时间戳，而是系统版本号，每开始一个新的事务，系统版本号就会递增。在RR隔离级别下，MVCC的操作如下：

select操作：
- InnoDB只查找版本早于（包含等于）当前事务版本的数据行。可以确保事务读取的行，要么是事务开始前就已存在，或者事务自身插入或修改的记录。
- 行的删除版本要么未定义，要么大于当前事务版本号。可以确保事务读取的行，在事务开始之前未删除。
insert操作：将新插入的行保存当前版本号为行版本号。
delete操作：将删除的行保存当前版本号为删除标识。
update操作：变为insert和delete操作的组合，insert的行保存当前版本号为行版本号，delete则保存当前版本号到原来的行作为删除标识。

由于旧数据并不真正的删除，所以必须对这些数据进行清理，innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除，这个过程叫做purge。

10.为什么选择B+树作为索引结构（高频考点）

Inodb存储引擎默认是 B+Tree索引；MyISAM 存储引擎默认是Fulltext索引；Memory 存储引擎默认 Hash索引；

InnoDB 存储引擎选择使用B+树作为索引结构，是因为B+树具有以下特点和优势：

聚簇索引支持：InnoDB 存储引擎的表数据是按照聚簇索引组织的，即数据行的物理存储顺序与聚簇索引的顺序一致。B+树的叶子节点上存储了完整的数据行，因此适合作为聚簇索引的索引结构。
范围查询高效：B+树是一种有序的索引结构，能够高效支持范围查询操作，例如大于、小于、区间查询等。 B+树的叶子节点形成了一个有序链表，可以通过链表遍历来获取范围查询结果。
数据访问快速：B+树的高度相对较低，通常只需要几层就可以覆盖大量的数据，因此在平均情况下，B+树的数据访问时间复杂度为O(log N)，其中N是索引的数据量。这使得B+树在大型数据库中处理大量数据时表现出色。
适应性强：B+树的节点大小相对较大，能够容纳更多的索引键和数据行。同时，B+树的叶子节点形成了有序链表，便于范围查询和顺序遍历。这种特性使得B+树适用于处理随机和顺序访问的场景。
支持快速插入和删除：B+树的结构特点使得插入和删除操作相对简单和高效。当插入新数据时，只需对树进行少量的节点分裂和合并操作。当删除数据时，只需进行节点合并和删除操作即可。

综上所述，B+树在适应性、范围查询、数据访问速度和快速插入删除等方面都具有优势，因此InnoDB 存储引擎选择B+树作为索引结构来满足对高性能和高并发的需求。

另外， B+树(叶节点保存数据，其他的节点全部存放索引)， 数据库索引采用B+树的主要原因是B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。 B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。正如上面所说，在数据库中基于范围的查询是非常频繁的，因此MySQL最终选择的索引结构是B+树而不是B树。

11.为什么平衡二叉树（或红黑树）不适合作为索引？

平衡二叉树（如AVL树）和红黑树都是自平衡的二叉搜索树，它们在某些场景下确实可以作为索引结构，但相比B+树，它们存在以下一些不适合作为索引的问题：

存储空间占用：平衡二叉树和红黑树每个节点需要存储键和指向左右子节点的指针，这会占用相对较多的存储空间。相比之下， B+树的内部节点只需要存储键，而数据行存储在叶子节点上，因此可以更高效地利用存储空间。
磁盘IO次数：在磁盘上存储数据时， 平衡二叉树和红黑树的节点相对较小，需要更多的节点才能覆盖大量的数据。这就意味着在访问大量数据时，需要进行更多的磁盘IO操作，增加了IO的开销。而 B+树的节点较大，能够容纳更多的键和数据行，减少了磁盘IO的次 数。
范围查询效率：平衡二叉树和红黑树在进行范围查询时需要遍历树的节点，而且 其节点的存储是分散的，这导致范围查询操作的效率较低。而B+树的叶子节点形成了有序链表，可以通过链表遍历来获取范围查询结果，从而提高了范围查询的效率。
插入和删除操作的复杂度： 平衡二叉树和红黑树在插入和删除操作时，需要进行节点的旋转和重新平衡操作，这增加了操作的复杂度和开销。相比之下，B+树的插入和删除操作相对简单，通常只需进行少量的节点分裂和合并操作。

综上所述，虽然平衡二叉树和红黑树可以作为索引结构，但在大规模数据和高并发访问的数据库场景下，B+树更适合作为索引结构，因为它能够更高效地利用存储空间、减少磁盘IO次数，并且具有更好的范围查询效率和简单的插入删除操作。

12.总是谈及索引，那索引的目的是什么？

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

索引（Index）是帮助数据库高效获取数据的数据结构。索引是在基于数据库表创建的，它包含一个表中某些列的值以及记录对应的地址，并且把这些值存储在一个数据结构中。最常见的就是使用哈希表、B+树作为索引。

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

数据库查询是数据库最主要的功能之一。而查询速度当然是越快越好。而当数据量越来越大的时候，查询花费的时间会随之增长。而索引，可以加速数据的查询。因为索引是有序排列的。

13.简单谈谈MYSQL Innodb 索引的数据结构，并简述 B+树的查找过程（高频考点）

任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。

如上图，是一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

B+树的查找过程

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用 二分查找确定 29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示两千万的数据，如果千万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

14.说说索引使用注意事项与数据类型选择，或则说说MySQL常见索引

索引使用注意事项

当使用索引时，有一些注意事项可以帮助优化查询性能和避免潜在的问题：

选择适当的列作为索引：选择频繁用于查询条件或连接条件的列作为索引可以提高查询性能。考虑到查询的选择性和频率，选择具有高基数（不同值数量多）的列作为索引更为有效。
避免过多索引：过多的索引不仅占用存储空间，还会增加写操作的开销。只创建必要的索引，避免创建冗余或不必要的索引。
注意索引列的顺序：多列索引的顺序很重要。根据查询的特点，将最频繁使用的列放在索引的前面，以提高查询的效率。
索引覆盖：尽量设计覆盖索引，即索引包含了查询所需的所有列。这样可以减少磁盘IO操作，提高查询效率。
避免过长的索引：较长的索引可能会导致较大的索引文件和较低的性能。选择适当长度的索引可以平衡存储和查询性能。
定期维护索引：索引需要定期维护以保持其性能。删除不再使用的索引，重建或重新组织索引可以帮助提高查询性能。
谨慎使用索引提示：某些数据库系统提供索引提示（index hints）的功能，允许手动指定使用特定的索引。但要谨慎使用，确保对性能有实际的提升，避免过度干预优化器的决策。
注意索引和数据更新的平衡：索引可以提高查询性能，但会增加数据更新的开销。在设计索引时，要权衡查询和更新的需求，避免过度索引导致更新操作变慢。
监测索引性能：定期监测索引的使用情况和性能，通过数据库的性能分析工具或查询执行计划来了解索引的效果，及时进行优化和调整。

总之，合理设计和使用索引是提高数据库查询性能的关键。根据具体的业务需求和数据库特点，选择适当的列作为索引，注意索引的顺序和覆盖，定期维护和监测索引的性能，可以有效提升查询性能并避免潜在的问题。

选择索引的数据类型

Mysql支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。

越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和cpu缓存中都需要更少的空间，处理起来更快。
简单的数据类型更好：整形数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应用内置的日期和时间数据类型，而不是字符串来存储时间；以及用整形数据存储IP地址。
尽量避免NULL：应该制定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为他们使得索引、索引的统计信息以及比较运算更加复杂。

MySQL常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引

INDEX（普通索引）：ALTER TABLE ‘table_name’ ADD INDEX index_name(‘col’)最基本的索引，没有任何限制
UNIQUE（唯一索引）：ALTER TABLE ‘table_name’ ADD UNIQUE(‘col’)，与“普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值。
PRIMARY KEY（主键索引）：ALTER TABLE ‘table_name’ ADD PRIMARY KEY(‘col’) 是一种特殊的唯一索引，不允许有空值。
FULLTEXT（全文索引）：ALTER TABLE ‘table_name’ ADD FULLTEXT(‘col’)，仅可用于MyISAM和InoDB，针对较大的数据，生成全文索引很耗时耗空间
组合索引：ALTER TABLE ‘table_name’ ADD INDEX index_name(‘col1’,‘col2’,‘col3’)

为了更多的提高mysql效率可建立组合索引，遵循“最左前缀”原则。创建复合索引应该将最常用（频率）做限制条件的列放在最左边，一次递减。组合索引最左字段用in是可以用到索引的。相当于建立了col1,col1col2,col1col2col3三个索引。

15.谈谈索引优化的思路（高频考点）

主要思路集中在正确创建索引、正确使用索引以及额外的一些扩展优化，具体见如下博客：

MySQL索引性能优化分析 icon-default.png?t=O83A https://blog.csdn.net/xiaofeng10330111/article/details/105360974?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522171389306616800182188332%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fblog.%2522%257D&request_id=171389306616800182188332&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~blog~first_rank_ecpm_v1~rank_v31_ecpm-1-105360974-null-null.nonecase&utm_term=%E7%B4%A2%E5%BC%95&spm=1018.2226.3001.4450

16.聚簇索引与非聚簇索引的区别？（高频考点）

聚簇索引（Clustered Index）和非聚簇索引（Non-Clustered Index）是数据库中常用的两种索引类型，它们有以下区别：

聚簇索引

数据物理存储顺序的决定者：聚簇索引对数据的物理存储顺序起到决定性作用。它会将数据行依据索引键的顺序在磁盘上进行物理存储。需要注意的是，一个表仅能存在一个聚簇索引。
完整数据行的承载者：聚簇索引的叶子节点包含了完整的数据行。这一特性使得聚簇索引能够满足覆盖索引的要求，也就是说，仅通过索引本身就能获取查询所需的全部列信息。
范围与顺序操作的高效助力：聚簇索引在范围查询和顺序遍历方面表现极为高效。由于相关的数据行在物理上存储于相邻位置，进行这类操作时可以显著减少磁盘 I/O 操作。

非聚簇索引

数据行位置的指向者：非聚簇索引的叶子节点并不包含完整的数据行，而是包含索引键以及指向数据行的物理地址（或指针）。
索引与数据存储顺序的分离：非聚簇索引的叶子节点会按照索引键的顺序在磁盘上存储，但数据行的物理存储顺序与索引键的顺序并无关联。
多索引结构的维护者：在一个表上可以创建多个非聚簇索引，每个非聚簇索引都独立维护着一套索引结构。
单值查找的优势者与范围操作的挑战者：非聚簇索引能够实现快速的索引查找，但在进行范围查询和顺序遍历时，往往需要进行额外的磁盘 I/O 操作。

区别总结

存储顺序决定权：聚簇索引决定数据的物理存储顺序，而非聚簇索引不具备这一功能。
叶子节点内容差异：聚簇索引的叶子节点包含完整的数据行，非聚簇索引的叶子节点则包含索引键和指向数据行的指针。
查询场景适用性：聚簇索引在范围查询和顺序遍历方面效果更佳，非聚簇索引在单值查找时表现更优。
索引数量限制：一个表只能拥有一个聚簇索引，但可以创建多个非聚簇索引。

对比如下

对比项	聚簇索引	非聚簇索引
数据物理存储顺序	决定数据的物理存储顺序，数据行按索引键顺序物理存储在磁盘上	不决定数据的物理存储顺序，数据行物理存储顺序与索引键顺序无关
叶子节点内容	叶子节点包含完整的数据行	叶子节点包含索引键和指向数据行的物理地址（或指针）
覆盖索引能力	可以满足覆盖索引需求，索引本身包含查询所需所有列	一般需回表查询数据，较难满足覆盖索引需求（除非恰好索引包含所有查询列）
查询效率	范围查询和顺序遍历高效，因相关数据行物理相邻，可减少磁盘 I/O 操作	单值查找有优势，能快速定位索引位置；范围查询和顺序遍历需额外磁盘 I/O 操作
表中数量限制	一个表只能有一个聚簇索引	一个表可以有多个非聚簇索引

需要根据具体的业务需求和查询模式来选择使用聚簇索引还是非聚簇索引。聚簇索引适合范围查询和顺序遍历的场景，而非聚簇索引适合单值查找和覆盖索引的场景。

17.InnoDB一棵B+树可以存放多少行数据？可否对InnoDB存储引擎B+树的树高进行推导

这个问题的简单回答是： 约2千万

在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是512字节，而文件系统（例如XFS/EXT4）的最小单元是块，一个块的大小是4k。

而对于我们的InnoDB存储引擎也有自己的最小储存单元——页（Page），一个页的大小是16K。InnoDB的所有数据文件（后缀为ibd的文件），他的大小始终都是16384（16k）的整数倍。

假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。

那么可以算出一棵高度为2的B+树，能存放1170*16=18720条这样的数据记录。

根据同样的原理我们可以算出一个高度为3的B+树可以存放：1170*1170*16=21902400条这样的记录。所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。

现实中基本结论： InnoDB存储引擎B+树的树高3-4层，对应推导可见：

InnoDB存储引擎B+树的树高推导

18.MyisAM索引与InnoDB索引相比较

索引支持差异：MyISAM 支持全文索引（FULLTEXT）和压缩索引，而 InnoDB 不支持这两种索引类型。
事务支持差异：InnoDB 具备事务处理能力，MyISAM 则不支持事务。
数据与索引存储方式差异：MyISAM 按顺序存储数据，索引叶子节点保存对应数据行的地址，辅助索引与主键索引差异不大；InnoDB 的主键节点同时存储数据行，其他辅助索引保存的是主键索引的值。
键值存储及加载差异：MyISAM 键值分离，索引加载到内存（key_buffer_size）中，数据缓存依赖操作系统；InnoDB 键值一起保存，索引和数据一同加载到 InnoDB 缓冲池中。此外，MyISAM 的主键（唯一）索引按升序存储，InnoDB 则不一定遵循此规则。
索引基数值统计差异：MyISAM 索引的基数值（Cardinality，可通过 show index 命令查看）是精确值，InnoDB 的则是估计值。这与信息统计机制有关，MyISAM 的统计信息存储在磁盘上，在执行 alter 表或 Analyze table 操作时更新；InnoDB 在表首次打开时将估计值保存在缓存区。
字符串索引处理差异：MyISAM 在处理字符串索引时采用增量保存方式。例如，若第一个索引是 ‘preform’，第二个是 ‘preformance’，则第二个索引保存为 ‘7，ance’。这种方式能缩短索引长度，但缺点是不支持倒序提取索引，只能按顺序遍历获取。

简单总结下 MyISAM 和 InnoDB 存储引擎的详细对比：

对比项	MyISAM	InnoDB
事务支持	不支持事务	支持 ACID 事务，可确保数据在并发环境下的一致性、完整性、隔离性和持久性
外键支持	不支持	支持外键约束，有助于维护表间数据的引用完整性
锁机制	表级锁（Table Lock），对整个表加锁，操作时会锁定整张表，并发操作时易产生锁冲突	行级锁（Row Lock），能更细粒度地控制并发访问，减少锁冲突，提高并发性能
并发控制	因表级锁限制，并发能力较弱，在高并发场景下性能易受影响	行级锁使并发能力较强，适合高并发的读写操作场景
崩溃恢复	需要手动修复（`myisamchk`），操作相对复杂且可能存在数据丢失风险	具备自动崩溃恢复功能，利用事务日志在重启时恢复到一致状态，数据安全性高
数据完整性	无事务保证，在异常情况下可能丢失数据，难以保证数据一致性	通过事务保证数据完整性，可在并发和异常情况下确保数据准确
查询性能	适用于大量读操作，由于索引和数据分离且是表级锁，在对并发要求不高的大量读场景下查询速度快	适用于读写混合，支持事务和行级锁，在需要保证数据一致性和完整性的写入操作上表现较好
存储结构	索引和数据分离存储，分别存储在不同文件中	聚簇索引（索引与数据存储在一起），主键索引和数据紧密关联
索引方式	仅支持非聚簇索引，索引和数据物理上分离	支持聚簇索引（PRIMARY KEY 作为索引），主键索引直接包含数据记录
全文索引	原生支持全文索引（MySQL 5.6 之前）	MySQL 5.6+ 开始支持全文索引
数据存储	仅存储表数据和索引文件	需要额外的事务日志（Redo、Undo），用于保证事务的原子性和持久性
表空间管理	每个表存储为 `.MYD`（数据文件）和 `.MYI`（索引文件），管理相对简单	所有表共享 `ibdata1` 或独立表空间，可根据需求灵活配置
适用场景	读密集型应用，如日志、统计系统，对并发和事务要求较低	事务密集型应用，如银行、电商系统，需要保证数据一致性和完整性

19.查询在什么时候不走（预期中的）索引（高频考点）

在数据库查询中，通常情况下，如果查询语句中的条件与数据库表的索引匹配，数据库会使用索引来加速查询。然而，并非所有情况下都能使用索引，以下是一些不走（预期中的）索引的情况：

对列进行函数或操作符运算：当查询条件里运用函数或操作符处理列时（像通过函数计算列值，或者利用通配符开展模糊匹配），很可能致使索引失效。比如，查询语句采用以通配符开头的 LIKE 操作符（如 WHERE column LIKE ‘%value’）。
列类型不一致：若查询条件里列的类型和索引列类型不相符，数据库或许无法利用索引。例如，索引列是整数类型，而查询条件中的列却是字符串类型。
多列索引使用不当：使用多列索引时，若查询条件未包含索引的左侧列，数据库可能无法启用该索引。例如，索引为 (column1, column2)，但查询条件仅为 WHERE column2 = value。
数据库统计信息有误：数据库依靠统计信息来决定是否使用索引以及选取最优执行计划。如果统计信息陈旧或不准确，数据库可能不会选择使用索引。
数据库选择全表扫描策略：某些情形下，数据库优化器可能判定全表扫描比使用索引更为高效。这种情况可能出现在小表上，或者查询会返回大部分数据行时。

这只是一些导致不走（预期中的）索引的常见情况，实际情况可能因数据库引擎、表结构和查询语句的复杂性而有所不同。对于特定的查询，请检查数据库执行计划或性能优化工具以了解索引使用情况。

20.SQL如何优化，给出思路（说出部分即可，一下也只是一些思路）

优化SQL查询是提升数据库性能和响应时间的关键步骤。以下是一些优化SQL查询的常见思路：

索引优化

审慎选择索引：在频繁用于查询的列上创建索引，可显著提升查询性能。然而，索引会增加写操作的开销，因此需权衡利弊后做出选择。仔细分析查询语句中的 WHERE 条件和连接条件，精准确定需要创建索引的列，进而构建恰当的索引以优化查询。
规避全表扫描：尽量避免对大表执行全表扫描操作，因为这会消耗大量的系统资源和时间。通过合理运用索引，并利用 WHERE 子句严格限定查询条件，尽可能缩小查询范围，从而减少需要扫描的数据量。

查询优化

精简查询列：仅选择查询所需的列，避免查询不必要的列。这样做能够有效减少数据库与应用程序之间传输的数据量，减轻数据库的处理负担。
优化 JOIN 操作：当查询涉及多个表时，选用合适的 JOIN 操作和连接条件，以最小化查询结果集的大小，并降低 JOIN 操作的复杂性。确保表之间存在合理的关联关系，避免进行过多的 JOIN 操作。
选用恰当 SQL 功能：使用合适的 SQL 函数和操作符，避免使用过于复杂的操作，以减少查询过程中的计算量和运行时间。
优化分页查询：在进行分页查询时，采用合适的分页技术，例如使用 LIMIT 和 OFFSET 进行分页，避免先检索整个结果集，再在应用程序中进行分页处理。

数据管理优化

更新统计信息：确保数据库的统计信息始终保持最新状态，以便数据库优化器能够根据最新的数据分布情况生成更优的查询计划。定期更新统计信息是实现这一目标的关键。
减少事务提交频率：频繁提交事务会增加数据库的额外开销。可以考虑采用批量操作的方式，将多个操作合并到一个事务中，从而减少事务提交的次数。
优化数据库结构：对数据库的结构进行优化，包括合理划分表和字段、选用合适的数据类型和约束、进行规范化或去规范化处理等。根据具体的业务需求和查询模式，优化数据库结构能够有效提高查询性能。

监控与调优

监控与针对性调优：借助数据库监控工具或分析查询执行计划，识别慢查询和高资源消耗的查询等问题，并进行针对性的优化调整。
选对连接类型：根据查询的实际需求，选择合适的连接类型，如 INNER JOIN、LEFT JOIN、RIGHT JOIN 等。合理选择连接类型可以减少不必要的数据计算和传输。
慎用子查询和临时表：在某些特定情况下，使用子查询或临时表可以简化复杂的查询逻辑，提高查询性能。但需要注意控制子查询和临时表带来的性能开销，避免过度使用。
少用通配符查询：尽量避免在查询中使用以通配符（如 %）开头的模糊查询，因为这通常会导致全表扫描。若无法避免此类查询，可以考虑使用全文索引或其他优化技术来提升性能。
调整数据库参数：根据数据库的具体运行情况，对数据库的参数设置进行调整，如内存缓存大小、并发连接数、线程池大小等，以提高数据库的整体性能和并发处理能力。
数据分区与分表：对于特别大的表，可以考虑采用数据分区或分表技术，将数据分散存储在不同的物理存储介质上，从而提高查询性能和数据管理效率。
缓存查询结果：对于一些频繁查询但数据变动不频繁的情况，可以考虑使用缓存来存储查询结果，以减轻数据库的查询压力。
维护索引性能：定期检查数据库中的索引，清理无用的索引，并对需要优化的索引进行重建，以确保索引的有效性和高性能。
利用监控工具：借助数据库性能监控工具，实时监控数据库的各项性能指标，如查询执行时间、锁等待时间、磁盘 I/O 等，及时发现潜在的性能问题并进行优化。

优化SQL查询是一个复杂而持续的过程，需要结合具体的业务需求、数据模型和数据库系统的特性来选择和实施优化策略。同时，不同的数据库系统可能有不同的优化技术和工具，需要针对具体的数据库进行进一步的研究和学习。

21.SQL执行顺序是什么样的？（高频考点）

SQL的执行顺序：from—where–group by—having—select—distinct—order by—limit

SQL执行顺序可以分为以下几个步骤：

FROM: 从指定的表中获取数据。这是查询的起点，确定了数据源。
WHERE: 对从表中选取的数据进行条件过滤。只有满足条件的行才会被包含在结果集中。
GROUP BY: 按指定的列对数据进行分组。相同值的行被聚合在一起形成分组。
HAVING: 对分组后的数据进行条件过滤。只有满足条件的分组才会被包含在结果集中。
SELECT: 选择要返回的列。这是最终生成结果集的步骤。
DISTINCT: 去除结果集中的重复行。如果查询中使用了 DISTINCT 关键字，则在选择列之后进行去重操作。
ORDER BY: 对结果集进行排序。按照指定的列顺序对结果集进行排序。
LIMIT / OFFSET: 对结果集进行分页处理。LIMIT 用于限制返回的行数，OFFSET 用于指定结果集的起始位置。

这是一般情况下的SQL执行顺序。需要注意的是，不同的数据库管理系统可能会有细微的差别，例如一些数据库可能会在执行 ORDER BY 之后再执行 SELECT，而另一些数据库可能会在 SELECT 之后再执行 ORDER BY。

22.explain是如何解析sql的

EXPLAIN是一个用于分析查询计划的SQL命令，它可以帮助了解查询的执行方式和性能瓶颈。当执行EXPLAIN命令时，数据库系统会解析SQL语句并生成查询计划，然后将计划的详细信息返回给用户。解析SQL的过程主要包括以下步骤：

语法解析：数据库系统首先对SQL语句进行语法解析，以确定语句的结构和语义是否正确。如果SQL语句存在语法错误，将会报错并无法继续执行。
查询优化：在语法解析后，数据库系统会对查询语句进行优化。优化器根据查询的结构、条件和表的索引等信息，生成一个最优的查询计划。优化器会考虑多种可能的执行路径，选择代价最低的执行计划。
查询计划生成：优化器根据查询优化的结果，生成查询计划。查询计划是一个执行引擎能够理解的指令序列，用于执行查询并返回结果。查询计划通常由一系列的操作符和它们之间的连接关系组成，如表扫描、索引查找、连接操作等。
执行计划解释：一旦查询计划生成，EXPLAIN命令将会解析查询计划，并将其详细信息返回给用户。这些信息包括操作符的执行顺序、使用的索引、估计的行数、执行操作的代价等。这些信息可以帮助用户理解查询的执行过程，检查查询的性能问题并进行优化。

总的来说，EXPLAIN命令通过解析SQL语句、优化查询、生成查询计划和解释计划的方式，提供了对查询执行的详细分析和优化的参考。通过仔细分析查询计划的输出，可以发现查询的瓶颈所在，找到性能优化的方向。

推荐以下文章进行扩展阅读分析：

MySQL查询请求执行过程分析 icon-default.png?t=O83A https://zyfcodes.blog.csdn.net/article/details/118000193?spm=1001.2014.3001.5502 基本使用explain分析SQLhttps://zyfcodes.blog.csdn.net/article/details/116803687?spm=1001.2014.3001.5502 简单的，使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

23.order by原理

原理分析：

根据MySQL排序原理划分的话，MySQL排序有两种方式，一个是通过有序索引直接返回数据，另一种是通过Filesort进行排序数据。

利用索引的有序性获取有序数据
利用内存/磁盘文件排序获取结果
1) 双路排序:是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序。
2)单路排序:是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。

平时判断一条SQL语句用的是哪种排序可以使用Explain或desc SQL语句来查看，在输出信息中 Extra字段 会具体显示用了哪种排序，如果Extra显示 Using index，则表示是通过有序索引直接返回有序数据的。如果显示 Using filesort，则表示SQL是通过Filesort进行排序返回数据的。

MySQL 中的 Filesort 并不一定是在磁盘文件中进行排序的，也有可能在内存中排序，内存排序还是磁盘排序取决于排序的数据大小和 sort_buffer_size 配置 的大小。

如果 “排序的数据大小” < sort_buffer_size: 内存排序。
如果 “排序的数据大小” > sort_buffer_size: 磁盘排序。

Filesort排序模式总共有三种模式：

双路排序（又叫回表排序模式）< sort_key, rowid >：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；
单路排序< sort_key, additional_fields >：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；
打包数据排序模式< sort_key, packed_additional_fields >：打包数据排序模式是单路排序的一种升级模式，与单路排序相似，区别是将 char 和 varchar 字段存到 sort buffer 中时，更加紧缩。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和需要查询的字段总大小来判断使用哪种排序模式。

如果 max_length_for_sort_data 比查询字段的总长度大，那么使用 < sort_key, additional_fields >排序模式；
如果 max_length_for_sort_data 比查询字段的总长度小，那么使用排序模式。

优化方式：

给order by 字段增加索引,orderby的字段必须在最前面设置
去掉不必要的返回字段
增大 sort_buffer_size 参数设置

24.InnoDB的行锁/表锁（高频考点）

详细的可见以下博客，内涵死锁的分析和举例：

MySQL事务及实现原理 icon-default.png?t=O83A https://zyfcodes.blog.csdn.net/article/details/86516943?spm=1001.2014.3001.5502 主要内容如下：

25.说说行锁算法（记录锁+间隙锁+下一键锁）（高频考点）

详细的可见以下博客，内涵死锁的分析和举例：

MySQL事务及实现原理 icon-default.png?t=O83A https://zyfcodes.blog.csdn.net/article/details/86516943?spm=1001.2014.3001.5502 主要内容如下：

26.myisam和innodb的区别，什么时候选择myisam

基本的区别：存储结构/存储空间/事物支持/CURD操作/外键

存储结构

MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。 .frm文件存储表定义。数据文件的扩展名为.MYD(MYD)。索引文件的扩展名是.MYI(MYIndex)。
InnoDB:所在的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

存储空间

MyISAM:可被压缩，存储空间较小。支持三种不同的存储格式：静态表（默认，但是注意数据末尾不能有空格，会被去掉）、动态表、压缩表。
InnoDB:需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

事物支持

MyISAM:强调的是性能，每次查询具有原子性，其执行速度比Innodb类型更快，但是不提供事物支持。
InnoDB:提供事务支持，外部键等高级数据库功能。具有事务（commit）、回滚（rollback）和崩溃修复能力（crach recovery capabilities）的事务安全（transaction-safe ACID compliant）型表。

CURD操作

MyISAM: 如果执行大量的select, MyISAM是更好的选择。（因为没有支持行级锁），在增删的时候需要锁定整个表格，效率会低一些。相关的是innoDB支持行级锁，删除插入的时候只需要锁定该行就行，效率较高。
InnoDB:如果你的数据执行大量的insert或update，出于性能方面的考虑，应该使用InnoDB表。 Delete从性能上Innodb更优，但delete from table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

外键

MyISAM: 不支持。
InoDB:支持。

选择上的考虑：

因为MyISAM相对简单所以在效率上要优于InnoDB。如果系统 读多，写少。对原子性要求低，那么MyISAM最好的选择。且MyISAM恢复速度快，可直接用备份覆盖恢复。

如果系统读少，写多的时候，尤其是并发写入高的时候。InnoDB就是首选了。

两种类型都有自己优缺点，选择那个完全要看自己的实际类弄。

27.binlog\redolog\undolog都是什么，起什么作用?（高频考点）

undoLog

也就是我们常说的回滚日志文件，主要用于事务中执行失败，进行回滚，以及MVCC中对于数据历史版本的查看。由引擎层的InnoDB引擎实现,是逻辑日志,记录数据修改被修改前的值,比如”把id=‘B’ 修改为id = ‘B2’ ，那么undo日志就会用来存放id =‘B’的记录”。当一条数据需要更新前,会先把修改前的记录存储在undolog中,如果这个修改出现异常,,则会使用undo日志来实现回滚操作,保证事务的一致性。当事务提交之后，undo log并不能立马被删除,而是会被放到待清理链表中,待判断没有事物用到该版本的信息时才可以清理相应undolog。它保存了事务发生之前的数据的一个版本，用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

redoLog

是重做日志文件是记录数据修改之后的值，用于持久化到磁盘中。redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。由引擎层的InnoDB引擎实现,是物理日志,记录的是物理数据页修改的信息,比如“某个数据页上内容发生了哪些改动”。当一条数据需要更新时,InnoDB会先将数据更新，然后记录redoLog 在内存中，然后找个时间将redoLog的操作执行到磁盘上的文件上。不管是否提交成功我都记录，你要是回滚了，那我连回滚的修改也记录。它确保了事务的持久性。

MVCC多版本并发控制是MySQL中基于乐观锁理论实现隔离级别的方式，用于读已提交和可重复读取隔离级别的实现。在MySQL中，会在表中每一条数据后面添加两个字段：最近修改该行数据的事务ID，指向该行（undolog表中）回滚段的指针。Read View判断行的可见性，创建一个新事务时，copy一份当前系统中的活跃事务列表。意思是，当前不应该被本事务看到的其他事务id列表。

binlog

由Mysql的Server层实现，是逻辑日志，记录的是sql语句的原始逻辑，比如”把id=‘B’ 修改为id = ‘B2’。binlog会写入指定大小的物理文件中，是追加写入的，当前文件写满则会创建新的文件写入。产生:事务提交的时候，一次性将事务中的sql语句，按照一定的格式记录到binlog中。用于复制和恢复在主从复制中，从库利用主库上的binlog进行重播(执行日志中记录的修改逻辑),实现主从同步。业务数据不一致或者错了，用binlog恢复。

28.数据库的乐观锁与悲观锁的区别是什么？乐观锁常用的两种实现方式是什么？

基本理解与区别：

数据的锁定分为两种，第一种叫作悲观锁，第二种叫作乐观锁。

悲观锁，就是对数据的冲突采取一种悲观的态度，也就是说假设数据肯定会冲突，所以在数据开始读取的时候就把数据锁定住。【数据锁定：数据将暂时不会得到修改】悲观锁是数据库实现，他阻止一切数据库操作。
乐观锁，认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让用户返回错误的信息，让用户决定如何去做。乐观锁是一种思想，具体实现是，表中有一个版本字段，第一次读的时候，获取到这个字段。处理完业务逻辑开始更新的时候，需要再次查看该字段的值是否和第一次的一样。如果一样更新，反之拒绝。之所以叫乐观，因为这个模式没有从数据库加锁。

实现方式对比：

悲观锁： 大多数情况下依靠数据库的锁机制实现，排它锁（当事务在操作数据时把这部分数据进行锁定，直到操作完毕后再解锁，其他事务操作才可操作该部分数据。这将防止其他进程读取或修改表中的数据。）。一般使用 select …for update 对所选择的数据进行加锁处理，例如select * from account where name=”Max” for update，这条sql 语句锁定了account 表中所有符合检索条件（name=”Max”）的记录。本次事务提交之前（事务提交时会释放事务过程中的锁），外界无法修改这些记录。

乐观锁： 大多数基于数据版本（Version）记录机制实现，具体可通过给表加一个版本号或时间戳字段实现，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断当前版本信息与第一次取出来的版本值大小，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，拒绝更新，让用户重新操作。

29.当前读和快照读

当前读：读取的是最新版本。UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。并且对读取的记录加锁, 阻塞其他事务同时改动相同记录，避免出现安全问题。

快照读：读取的是快照版本，也就是历史版本。普通的SELECT就是快照读

Read Committed隔离级别：每次select都生成一个快照读。
Read Repeatable隔离级别：开启事务后第一个select语句才是快照读的地方，而不是一开启事务就快照读。

30.一条sql的执行过程？

第一步：连接器（负责跟客户端建立连接、获取权限、维持和管理连接）

第二步：查询缓存

之前执行过的查询，MySQL以”Key - Value”的形式存在内存（key为SQL，value为结果集）；
只要对该表有一个更新，则这个表上的查询缓存都会被清空；
手动创建命令：select SQL_CACHE * from T（MySQL8中已经彻底废除此功能）；

第三步：分析器（词法分析 —— 语法分析）

词法分析：通过“select”，识别出为查询；通过“T”，识别出表名；通过条件“ID”，识别出ID那一列；等等；
语法分析：如果语法有误，则提示“You have an error in your SQL syntax”；

第四步：优化器

决定用哪个索引；联查表连接顺序；条件执行优先级，等等；

第五步：执行器（执行SQL）

第六步：存储引擎（提供读写接口，供执行器调用并获取结果集）

首先会判断你是否有该权限；
如果命中查询缓存，则会在返回结果的时候进行权限验证；

31.数据库什么时候决定分库分表？怎么考虑的？

决定进行数据库分库分表的时机和考虑因素如下

数据规模：当单一数据库的数据量增长到无法有效管理和处理的程度时，考虑进行分库分表。数据规模包括数据量的大小和数据增长的速度。
性能需求：如果单一数据库无法满足系统的性能需求，例如高并发读写、低延迟要求等，可以考虑采用分库分表来提高数据库的吞吐量和响应性能。
可扩展性：当系统需要支持水平扩展和负载均衡时，分库分表是一种常用的方案。通过将数据分布在多个数据库实例或表中，可以提高系统的扩展性和容量。
数据隔离需求：在某些场景下，需要对不同类型的数据进行隔离，例如不同地区、不同业务等。分库分表可以根据数据的属性将其分布到不同的数据库或表中，实现数据的隔离性。
数据访问模式：分库分表可以根据数据的访问模式进行优化。例如，将热点数据分离到单独的表中，以提高热点数据的访问性能。
数据一致性和可靠性：在考虑分库分表时，需要注意数据一致性和可靠性的问题。分库分表会引入分布式事务、数据同步和故障恢复等复杂性，需要综合考虑系统的一致性和可靠性需求。
维护和管理成本：分库分表会增加系统的复杂性，包括数据迁移、备份恢复、监控和调试等方面的工作。需要权衡维护和管理成本，确保分库分表带来的好处超过其引入的复杂性。

综合考虑以上因素，可以根据具体的业务需求和系统状况来决定是否进行数据库分库分表。在实际实施过程中，还需要细致规划和设计分库分表的策略，包括分片规则、数据迁移方案、一致性保证机制等。

32.mysql 底层查询b+树流程（高频考点）

MySQL使用B+树作为其底层存储引擎（如InnoDB）的索引结构，用于支持高效的数据查询。下面是MySQL底层查询B+树的一般流程：

解析SQL语句：MySQL接收到查询请求后，首先会进行SQL语句的解析和语法检查，确定查询的表、字段以及查询条件等信息。
确定查询的索引：MySQL会根据查询条件和表的索引信息来选择合适的索引用于查询。通常，MySQL会选择能够满足查询条件且选择性较好（即数据分布均匀）的索引。
从根节点开始遍历B+树：MySQL会从B+树的根节点开始进行查询。根节点通常存储了索引的最高层级信息，包括索引分裂信息和指向子节点的指针。
根据索引键值进行比较：MySQL会根据查询条件中的索引键值，比较它与当前节点中的索引键值的大小关系，以确定下一步的搜索方向。
按照索引键值进行分支选择：根据比较的结果，MySQL会沿着B+树的相应分支（指针）进一步搜索。如果查询条件中的索引键值大于当前节点的最大索引键值，则继续向右侧子节点搜索；如果小于当前节点的最小索引键值，则向左侧子节点搜索；如果在索引键值范围内，则向子节点继续搜索。
递归搜索：MySQL会根据查询条件不断地向下递归搜索，直到达到叶子节点。
访问叶子节点获取数据行指针：当到达叶子节点时，MySQL会获取叶子节点中存储的实际数据行的指针，这些指针可以用于进一步获取具体的数据行。
返回查询结果：MySQL会根据获取到的数据行指针，取出相应的数据行，并返回给用户作为查询结果。

需要注意的是，以上流程是一个简化的描述，实际的查询过程中还会涉及缓存、磁盘读取、并发控制等复杂的细节。MySQL通过优化索引结构和查询算法，以及利用缓存等技术手段，提供高效的数据查询能力。

33.设置主键的时候为啥是按1递增的？有啥好处？

在数据库中设置主键按照递增的方式，通常是为了使用自增主键（Auto-Increment Primary Key）。这种方式下，数据库会自动为每个新插入的记录分配一个唯一的主键值，且该值按照递增的方式生成。

以下是一些使用递增主键的好处：

唯一性： 递增主键保证了每个记录都有唯一的标识符，避免了主键冲突的问题。每次插入新记录时，数据库会根据当前已有的最大主键值自动生成一个新的主键， 确保新记录的主键与现有记录不重复。
索引性能：递增主键通常与聚集索引（Clustered Index）关联。 聚集索引的物理存储顺序与主键的顺序一致，因此插入新记录时，可以较容易地按顺序将记录添加到数据库页中， 减少磁盘IO操作。此外，递增主键还可以提高查询性能，因为基于聚集索引的范围查询和顺序访问会更加高效。
插入性能：递增主键的生成不需要额外的查询或判断，而是直接在内存中递增，因此插入新记录时的性能较高。与其他类型的主键相比，如GUID（全局唯一标识符），递增主键不需要生成随机值，减少了插入操作的开销。
缓存友好：递增主键有利于缓存的使用。当数据库表的数据被缓存在应用程序的内存中时，递增主键可以作为缓存键，方便唯一标识每个记录，提高缓存的查找和更新效率。

需要注意的是，递增主键并不是适用于所有场景的最佳选择。在某些特定的业务需求下，可能需要使用其他类型的主键，如GUID，以满足需要全局唯一标识符或随机性的要求。选择适当的主键类型应根据具体业务需求和数据库设计原则来进行权衡。

34.MySQL主从同步实现方式？有什么问题？如何解决？

MySQL主从同步是一种常见的数据库复制技术，用于将主数据库的数据变更同步到从数据库，以实现数据备份、读写分离等需求。以下是主从同步的实现方式和相关问题及解决方法：

实现方式：

基于二进制日志（Binary Log）的复制：主数据库将数据变更记录到二进制日志中，从数据库通过读取主数据库的二进制日志来获取数据变更，并应用到自己的数据库中。
基于GTID（Global Transaction Identifier）的复制：GTID是全局唯一的事务标识符，主数据库在进行事务提交时会生成GTID，并将其写入二进制日志中，从数据库通过读取主数据库的二进制日志中的GTID来进行数据同步。

问题及解决方法：

延迟：从数据库的数据同步可能存在一定的延迟，导致从数据库的数据不是实时更新的。 可以通过调整同步频率、优化网络传输、增加从数据库的资源等方式来减少延迟。
数据一致性：在主从同步过程中，可能会因为网络故障或其他原因导致数据不一致。 可以使用半同步复制、并行复制、串行化复制等技术来提高数据一致性。
主从切换：当主数据库发生故障或需要进行维护时，需要将从数据库提升为主数据库。在切换过程中，可能会出现数据丢失或冲突的问题。 可以使用半同步复制、自动切换工具、监控和报警系统等来确保切换的可靠性和及时性。
主从不一致：在特定情况下，可能会因为网络分区、主数据库故障恢复等原因导致主从不一致的情况。 可以通过手动修复、重建从数据库等方式来解决主从不一致的问题。

这些问题和解决方法可以根据具体的系统需求和情况进行调整和优化。同时，合理的配置和监控主从同步的相关参数和状态，以及定期进行数据校验和备份，也是确保主从同步的可靠性和稳定性的重要手段。

35.什么是覆盖索引？如果查询主键id下覆盖索引还生效吗？（高频考点）

覆盖索引是一种索引类型，它包含了查询所需的所有列，而无需访问实际的数据行。它通过包含查询需要的列的索引来提高查询性能，减少磁盘I/O操作和数据检索的成本。

举一个例子，假设有一个包含以下列的表：

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  city VARCHAR(50),
  email VARCHAR(100)
);

假设我们经常执行以下查询：

SELECT name, age FROM users WHERE city = 'Beijing';

为了优化这个查询，可以创建一个覆盖索引来包含city、name和age列。覆盖索引的创建方式可以根据数据库管理系统的具体实现而有所不同。

CREATE INDEX idx_users_city ON users (city, name, age);

现在，当执行上述查询时，数据库管理系统可以直接使用覆盖索引，无需查找实际的数据行。这样可以避免访问数据页和磁盘I/O操作，大大提高查询性能。覆盖索引对于具有大量列的表或需要频繁查询特定列的情况尤为有用。通过包含查询所需的所有列，覆盖索引可以减少磁盘I/O、减少内存使用，并提高查询效率。

需要注意的是，创建过多的索引可能会增加写操作的开销，并占用额外的存储空间。因此，在创建索引时需要权衡索引的效益和开销，根据具体的查询需求和数据访问模式进行优化。

如果在覆盖索引的例子中加入查询主键id，覆盖索引仍然有效，但是它可能会稍微降低一些效益。 这是因为主键id在每个索引中都是唯一的，并且通常存储在叶子节点上，因此访问主键id的成本相对较低。假设我们有以下查询：

SELECT id, name, age FROM users WHERE city = 'New York';

即使我们在覆盖索引中没有显式包含主键id列，但因为主键索引（如果有）通常也会包含主键id列，数据库管理系统可以利用主键索引来快速定位数据行，然后使用覆盖索引来获取其他列的值。这意味着覆盖索引仍然可以避免访问实际的数据行，减少磁盘I/O操作和数据检索的成本。

尽管如此，由于主键id在主键索引中本身就具有较低的成本，它的加入可能对覆盖索引的效益产生一些影响。具体影响的大小取决于数据库管理系统的实现方式和查询的特定情况。

总体而言，覆盖索引仍然是一个有效的优化手段，可以提高查询性能。在实际情况中，如果查询中包含主键id，并且主键索引已经在使用中，将其加入覆盖索引中可能会带来一些冗余。在这种情况下，根据具体情况，您可以选择是否包含主键id列，权衡查询性能和索引开销。

36.使用explain 查看数据已经使用了索引，但还是查询很慢，可能的原因是？

如果使用EXPLAIN查看查询的执行计划，并确认数据库已经使用了索引，但查询仍然很慢，可能存在以下几个可能的原因：

数据量过大：如果表中的数据量非常大，即使使用了索引，查询也可能需要花费较长的时间。在这种情况下，考虑优化查询条件、使用分页或者分区技术，以减少查询的数据量。
索引选择不当：索引的选择非常重要，如果选择的索引不合适，仍然会导致查询的性能问题。可能是使用了不适合查询条件的索引，或者存在重复或冗余的索引。通过分析查询的具体情况，重新评估索引的选择和设计。
索引失效：有时候索引会失效，导致查询无法充分利用索引带来的性能优势。可能是由于查询条件中使用了函数、类型转换或者运算符，导致无法使用索引。检查查询条件并确保它们与索引的列匹配，避免索引失效。
锁竞争和并发问题：如果查询过程中存在锁竞争或者其他并发问题，会导致查询的性能下降。这可能是由于其他会话持有锁，导致查询被阻塞。在这种情况下，可以考虑调整事务隔离级别、优化锁的使用，或者调整并发控制策略。
硬件和配置问题：查询性能也可能受到硬件资源的限制或者数据库配置的影响。可能是由于硬件资源不足，例如CPU、内存或者磁盘I/O的瓶颈。检查硬件资源的使用情况，以及数据库的配置参数是否合理。
查询优化和索引维护：数据库中的查询优化器可能会做出不理想的查询计划选择，或者索引需要进行维护的操作导致性能下降。在这种情况下，可以尝试重新编写查询、更新统计信息、重新构建索引或者调整数据库的配置参数。

以上只是一些可能导致查询仍然很慢的原因，具体情况需要根据数据库的具体环境和查询的特点进行分析和优化。可以结合查询的执行计划、系统监控和数据库性能调优工具来诊断问题，找到性能瓶颈并采取相应的优化措施。

37.对于sql如下select a from T where b=23 and c>4 order by d，其如何建立索引可以使查询高效，如何验证？分析下（高频考点）

针对SQL查询 SELECT a FROM T WHERE b=23 AND c>4 ORDER BY d，以下是关于如何建立索引以提高查询效率的建议和验证方法的分析：

针对WHERE子句中的条件b=23和c>4

通过为这两列创建索引，可以加快WHERE条件的筛选过程，提高查询效率。

针对ORDER BY子句中的列d

为列d建立单列索引，即创建索引CREATE INDEX idx_d ON T(d)。
创建此索引可以加快根据列d进行排序的操作，以便更快地返回按照排序顺序的结果。

为了验证建立的索引是否提高了查询效率，可以使用以下方法：

使用EXPLAIN命令查看查询的执行计划：

运行EXPLAIN SELECT a FROM T WHERE b=23 AND c>4 ORDER BY d，查看执行计划。
确保在执行计划中索引被使用，以及索引访问类型是否是最优的，如索引扫描或索引范围扫描。

使用实际的数据进行性能测试：

在有足够的数据量和真实的工作负载环境下，运行查询，并记录查询的执行时间。
在建立索引之前和之后运行相同的查询，对比执行时间。
如果索引建立正确，并且索引被有效使用，应该观察到索引创建后查询的执行时间有所减少。

请注意，具体的索引设计和优化策略可能因数据库管理系统的不同而有所差异。此外，索引的使用还需要综合考虑表的大小、数据分布、查询频率和其他相关因素。定期监控和调整索引，以适应实际查询需求和性能要求，是一个持续优化的过程。

当然，如果给a、b、c和d都建立一个索引可能会提高查询的速度，但也需要权衡索引的开销和性能收益。建立太多索引可能会带来以下一些问题：

索引维护成本：每个索引都需要占用存储空间，并且在数据更新时需要进行维护。如果表的数据频繁更新，过多的索引可能会导致写操作的性能下降。
索引存储开销：索引占用存储空间，尤其是在大型表中，建立过多的索引会占用大量的磁盘空间。这可能会导致数据存储和备份的成本增加。
查询性能下降：每个索引都需要在查询过程中进行维护和访问。如果过多的索引存在，查询优化器在选择最优执行计划时可能会有更多选择，这可能导致性能下降。

因此，在设计索引时，需要根据具体的查询需求和数据访问模式，权衡索引的开销和性能收益。一般来说，选择适当的索引来覆盖常用的查询条件、排序和连接操作会更有效。

38.1000万的db数据分页怎么处理？

处理具有1000万条数据的数据库分页时，以下是一些常见的处理方法和建议：

数据库索引优化：确保数据库表中用于分页的字段上建立了合适的索引，以提高查询效率。通常，在用于排序和分页的字段上创建索引是很重要的。
使用合适的分页查询语句：在数据库查询中使用合适的分页语句来获取数据。常见的方法是使用 LIMIT 和 OFFSET 子句，结合合适的排序条件，以检索所需页的数据。例如，使用 LIMIT 10 OFFSET 0 获取第一页的数据。
逐页加载：对于较大的数据集，可以使用逐页加载的方式来减少一次性获取所有数据的负担。在前端或应用层代码中，通过逐页加载每次获取一定数量的数据，并将结果合并显示。这样可以分散数据库和应用服务器的压力。
缓存数据：对于相对稳定的数据集，可以考虑将数据缓存在应用层或缓存系统中，以减少对数据库的频繁访问。这样可以大幅提高分页查询的响应速度和吞吐量。
数据预处理和分段：如果数据是静态或相对稳定的，可以考虑在后台预处理数据，并按照一定的规则进行分段或分桶存储。这样可以在分页查询时只检索特定段或桶的数据，而不需要扫描整个数据集。
使用游标分页：对于大型数据集，使用游标分页技术可以更高效地进行分页查询。游标分页通过记录上一次查询的位置或唯一标识，实现分页查询的连续性，而无需重复扫描和排序整个数据集。
数据库分片或分区：如果数据规模非常大且需要高吞吐量的分页查询，可以考虑采用数据库分片或分区技术，将数据分布在多个物理服务器上，以并行化查询和提高吞吐量。

根据具体的业务需求和数据访问模式，可能需要结合多种方法来处理大规模的数据库分页。重要的是根据系统性能要求和可用资源做出适当的权衡和决策。

39.DB搜索引擎的组成是什么？原理是什么？

Db搜索引擎（也称为数据库搜索引擎）是一种用于在数据库中进行快速检索和查询的软件系统。它的组成和原理如下：

组成：

索引：Db搜索引擎会对数据库中的数据建立索引。索引是对数据库中的特定字段进行预处理，以加快查询的速度。
查询处理器：该组件负责接收用户的查询请求，并解析查询语句，根据索引和查询算法来执行查询操作。
查询优化器：优化器会对查询进行优化，选择合适的索引和查询计划，以提高查询效率和性能。
存储引擎：存储引擎负责实际的数据存储和检索操作。它将查询请求转换成对数据库中数据的读取和写入操作。
数据库管理系统（DBMS）：Db搜索引擎通常基于某种数据库管理系统构建，比如MySQL、Oracle等。

原理：

索引构建：在数据库中的特定字段上建立索引，例如B树索引或哈希索引。索引使得搜索引擎可以快速定位数据。
查询解析：当用户发起查询请求时，搜索引擎会解析查询语句，识别关键词和限定条件。
查询优化：搜索引擎会根据查询条件和已有的索引信息，选择最优的查询计划来执行查询，以减少数据检索的成本。
数据检索：搜索引擎通过查询优化后的查询计划，利用存储引擎从数据库中读取符合查询条件的数据。
结果返回：搜索引擎将查询结果返回给用户，通常以表格或文档的形式呈现。

索引维护：随着数据库的更新和变化，索引需要定期维护和更新，以保持查询性能的稳定性。

Db搜索引擎的设计目标是提供高效、快速的数据检索功能，使得用户能够轻松地在大规模数据库中进行复杂的查询和分析。不同的搜索引擎可能采用不同的索引和查询算法来满足特定的需求。

基本SQL练习

1.查询平均成绩大于等于85的所有学生信息

要查询平均成绩大于等于85的所有学生信息，你可以使用 SQL 查询语句来实现。假设你有一个名为”students”的表，其中包含学生的信息和成绩数据，可以按照以下方式编写查询：

SELECT student_id, student_name, AVG(grade) AS average_grad
FROM students
GROUP BY student_id, student_name
HAVING AVG(grade) >= 85;

以上 SQL 查询会按照学生的ID和姓名分组，并计算每个学生的平均成绩。然后，通过HAVING子句过滤出平均成绩大于等于85的学生信息。查询结果将包含学生的ID、姓名以及对应的平均成绩。

2.有两张表：订单表有订单号和产品id，产品表有产品id 和产品名称，设计SQL实现返回产品名称和每一项产品的总订单数

可以使用SQL的JOIN和GROUP BY语句来实现这个需求。假设订单表为”orders”，包含”order_id”和”product_id”字段，产品表为”products”，包含”product_id”和”product_name”字段，可以按如下方式编写SQL查询：

SELECT products.product_name, COUNT(orders.product_id) AS total_orders
FROM products
JOIN orders ON products.product_id = orders.product_id
GROUP BY products.product_name;

这将返回产品名称以及每一项产品的总订单数。注意，这里使用JOIN将两个表关联，然后使用GROUP BY对产品名称进行分组，再通过COUNT函数计算每个产品的订单数。

3.查询每个部门的员工数量和平均工资

SELECT department, COUNT(*) AS employee_count, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

4.查询每个部门的最高工资和最低工资

SELECT department, MAX(salary) AS max_salary, MIN(salary) AS min_salary
FROM employees
GROUP BY department;

5.查询每个部门的员工数量和工资中位数

SELECT department,
COUNT(*) AS employee_count,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees
GROUP BY department;

6.查询每个商品类别的销售数量和销售总额

SELECT categories.category_name, SUM(order_items.quantity) AS total_quantity, SUM(order_items.quantity * order_items.price) AS total_sales
FROM order_items
JOIN products ON order_items.product_id = products.product_id
JOIN categories ON products.category_id = categories.category_id
GROUP BY categories.category_name;

7.查询每个商品类别的销售数量和销售总额，并按照销售总额从高到低排序

SELECT categories.category_name, SUM(order_items.quantity) AS total_quantity, SUM(order_items.quantity * order_items.price) AS total_sales
FROM order_items
JOIN products ON order_items.product_id = products.product_id
JOIN categories ON products.category_id = categories.category_id
GROUP BY categories.category_name
ORDER BY total_sales DESC;

8.查询每个商品类别的销售数量和销售总额，并只显示销售总额排名前3的商品类别

SELECT categories.category_name, SUM(order_items.quantity) AS total_quantity, SUM(order_items.quantity * order_items.price) AS total_sales
FROM order_items
JOIN products ON order_items.product_id = products.product_id
JOIN categories ON products.category_id = categories.category_id
GROUP BY categories.category_name
HAVING total_sales IN (
  SELECT DISTINCT TOP 3 SUM(order_items.quantity * order_items.price) AS total_sales
  FROM order_items
  JOIN products ON order_items.product_id = products.product_id
  JOIN categories ON products.category_id = categories.category_id
  GROUP BY categories.category_name
  ORDER BY total_sales DESC
)
ORDER BY total_sales DESC;

原文阅读