推荐|MySQL高频基本面试问题整理

当我们将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.事务隔离级别（高频考点）

具体展述可见：MySQL事务及实现原理http://iyenn.com/rec/1691263.html?spm=1001.2014.3001.5502

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

脏读（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索引性能优化分析http://iyenn.com/rec/1691264.html?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）是数据库中常用的两种索引类型，它们有以下区别：

聚簇索引：

聚簇索引决定了数据的物理存储顺序。聚簇索引将数据行物理上按照索引键的顺序存储在磁盘上。一个表只能有一个聚簇索引。
聚簇索引的叶子节点包含了完整的数据行，因此聚簇索引可以满足覆盖索引的需求，即索引本身就包含了查询所需的所有列。
聚簇索引对于范围查询和顺序遍历非常高效，因为相关的数据行物理上存储在相邻的位置，可以减少磁盘IO操作。

非聚簇索引：

非聚簇索引的叶子节点不包含完整的数据行，而是包含索引键和指向数据行的物理地址（或指针）。
非聚簇索引的叶子节点按照索引键的顺序存储在磁盘上，但数据行的物理存储顺序与索引键的顺序无关。
非聚簇索引可以在一个表上有多个，每个非聚簇索引都维护着一份独立的索引结构。
非聚簇索引可以提供快速的索引查找，但在范围查询和顺序遍历时需要进行额外的磁盘IO操作。

区别总结：

聚簇索引决定了数据的物理存储顺序，非聚簇索引不决定数据的物理存储顺序。
聚簇索引的叶子节点包含完整的数据行，非聚簇索引的叶子节点包含索引键和指向数据行的指针。
聚簇索引对于范围查询和顺序遍历效果较好，非聚簇索引在单值查找时效果较好。
一个表只能有一个聚簇索引，但可以有多个非聚簇索引。

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

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+树的树高推导http://iyenn.com/rec/1691265.html?spm=1001.2014.3001.5502

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’，第二个是‘preformence’，则第二个保存是‘7，ance’，这个明显的好处是缩短索引，但是缺陷就是不支持倒序提取索引，必须顺序遍历获取索引

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

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

1. 使用函数或操作符对列进行操作：如果查询条件中使用了函数或操作符对列进行操作（如使用函数计算列的值或使用通配符进行模糊匹配），这可能会导致无法使用索引。例如，查询语句中使用了LIKE操作符以通配符开头（例如WHERE column LIKE '%value'）。
2. 列类型不匹配：如果查询条件中的列类型与索引列的类型不匹配，数据库可能无法使用索引。例如，索引列是整数类型，但查询条件中的列是字符串类型。
3. 使用多列索引时，查询条件未涵盖索引的左侧列：如果使用了多列索引，但查询条件未涵盖索引的左侧列，数据库可能无法使用索引。例如，索引是(column1, column2)，但查询条件只包含WHERE column2 = value。
4. 数据库统计信息不准确：数据库使用统计信息来决定是否使用索引以及选择最佳执行计划。如果统计信息过时或不准确，数据库可能会选择不使用索引。
5. 数据库选择全表扫描的执行计划：在某些情况下，数据库优化器可能认为全表扫描比使用索引更有效。这可能发生在小表上或查询返回大部分数据行时。

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

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

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

选择合适的索引：通过在频繁查询的列上创建索引，可以提高查询性能。但是索引也会增加写操作的开销，因此需要权衡选择。分析查询语句的WHERE条件和连接条件，确定哪些列需要索引，并创建适当的索引来优化查询。
避免全表扫描：尽量避免在大表上执行全表扫描操作，它会消耗大量的资源和时间。通过合理的索引和使用WHERE子句限定查询条件，尽量缩小查询范围，以减少扫描的数据量。
减少查询返回的列：只选择需要的列，避免查询不必要的列。这样可以减少数据库传输的数据量和减轻数据库的负担。
使用JOIN优化：当查询涉及多个表时，使用合适的JOIN操作和连接条件，以最小化查询的结果集和减少JOIN操作的复杂性。确保表之间有适当的关联关系，避免过多的JOIN操作。
使用适当的SQL函数和操作符：使用合适的SQL函数和操作符，避免使用过于复杂的操作，以减少查询的计算量和运行时间。
分页查询优化：在需要分页查询时，使用合适的分页技术，例如使用LIMIT和OFFSET进行分页，而不是检索整个结果集然后在应用程序中进行分页处理。
定期更新统计信息：确保数据库的统计信息是最新的，以便优化查询计划的生成。通过定期更新统计信息，数据库优化器可以根据最新的数据分布情况生成更好的查询计划。
避免频繁的提交事务：频繁的提交事务会增加数据库的开销。可以考虑批量操作，将多个操作放在一个事务中，减少提交的次数。
数据库结构优化：优化数据库的结构，包括合理划分表和字段、使用合适的数据类型和约束、规范化和去规范化等。根据具体业务需求和查询模式，优化数据库结构可以提高查询性能。
监控和调优：通过数据库监控工具或查询执行计划，分析慢查询、高消耗的查询等问题，进行针对性的调优。 11 使用合适的连接类型：根据查询需要选择合适的连接类型，如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查询请求执行过程分析http://iyenn.com/rec/1691266.html?spm=1001.2014.3001.5502 基本使用explain分析SQLhttp://iyenn.com/rec/1691267.html?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事务及实现原理http://iyenn.com/rec/1691263.html?spm=1001.2014.3001.5502主要内容如下：

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

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

MySQL事务及实现原理http://iyenn.com/rec/1691263.html?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

为列b和列c分别建立单列索引，即分别创建索引CREATE INDEX idx_b ON T(b)和CREATE INDEX idx_c ON T(c)。
通过为这两列创建索引，可以加快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;

文章知识点与官方知识档案匹配，可进一步学习相关知识

MySQL入门技能树SQL高级技巧CTE和递归查询90403 人正在系统学习中

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