Mysql篇（五）

1、为什么推荐使用自增 id 作为主键？

推荐使用自增 id 作为主键的主要原因是为了提高数据库性能和管理效率。以下是使用自增 id 作为主键的一些好处：

1. 快速插入和查询：自增 id 是一个递增的数值，每次插入新记录时，数据库可以直接将其追加到数据页的末尾，无需查找合适的位置，从而提高插入效率。同时，在使用自增 id 作为主键时，对于查询操作，数据库可以更快速地定位到相应的数据行。

2. 减少索引碎片：使用自增 id 作为主键可以有效地减少索引碎片的产生。由于数据是逐渐追加的，新的数据总是附加到已有数据的末尾，不会出现频繁的数据移动，从而减少了索引的维护成本。

3. 数据唯一性：自增 id 作为主键可以确保每条记录都有唯一的主键值，不会出现重复的主键，确保数据表中的数据完整性。

4. 管理方便：使用自增 id 作为主键，可以方便地对数据进行排序、分页和删除等操作，也方便跟踪数据的插入顺序。

5. 不暴露敏感信息：自增 id 是一个抽象的、内部的标识符，不会暴露实际数据的信息，可以增加数据的安全性。

需要注意的是，虽然推荐使用自增 id 作为主键，但在特定的业务场景下，也可能存在其他合适的主键选择。例如，对于某些情况下需要保持一定数据关联的情况，可能需要选择其他业务相关的字段作为主键。在选择主键时，需要根据具体业务需求和数据特性进行合理的权衡和选择。

2、Innodb 事务为什么要两阶段提交?

InnoDB 存储引擎采用两阶段提交（Two-Phase Commit，简称2PC）是为了保证事务的原子性和持久性。两阶段提交是一种分布式事务协议，用于处理跨多个节点（如多个数据库服务器）的事务，以确保所有参与者在事务提交时都能达成一致的结果。

两阶段提交包含以下两个阶段：

1. 第一阶段（准备阶段）：

   • 事务的协调者（Coordinator）向所有参与者（Participants）发送准备请求，询问它们是否可以执行事务并准备好提交。
   • 每个参与者执行事务的前半部分，将数据更新到本地的数据页中，并写入事务日志，但此时不提交事务。

2. 第二阶段（提交阶段）：

   • 如果所有参与者都准备好提交，则协调者发送提交请求给所有参与者，要求它们提交事务。
   • 每个参与者执行事务的后半部分，将之前准备好的数据更新写入到磁盘，并将事务标记为已提交。此时事务的提交就是不可撤销的。
   • 如果任何一个参与者无法提交，协调者会发送回滚请求给所有参与者，要求它们回滚事务，确保所有参与者都处于一致的状态。

两阶段提交的主要目的是为了保证在分布式环境中的事务一致性和持久性，即所有参与者都能达成相同的结果，要么全部提交，要么全部回滚。通过两个阶段的协调，可以确保数据的一致性，避免数据不一致或部分提交的情况发生。

需要注意的是，两阶段提交也有一些缺点，其中主要问题是在第二阶段（提交阶段）如果协调者发生故障或网络通信问题，会导致事务一直处于等待状态，可能出现阻塞和性能问题。因此，在实际应用中，也可以考虑使用更复杂的分布式事务协议来解决这些问题，例如基于消息队列的分布式事务方案。

3、WAl 是什么?有什么好处?

WAL 是 Write-Ahead Logging（预写式日志）的缩写，是数据库管理系统中的一种重要技术，用于保证事务的持久性和恢复性。

WAL 的基本思想是在修改数据库之前，先将对数据的修改操作记录到一个日志文件中，然后再将修改操作应用到数据库中。这样，当数据库发生故障或崩溃时，可以通过回放日志文件中的操作，将数据库恢复到崩溃前的状态，确保事务的原子性和一致性。

WAL 的好处包括：

1. 提高事务性能：WAL 可以将多个事务的写操作合并成一个日志写操作，减少了随机磁盘写入的频率，提高了事务的性能。

2. 保证事务的持久性：WAL 确保了事务的持久性，即使数据库发生崩溃或故障，也能通过回放日志来恢复到事务提交之前的状态。

3. 支持事务回滚：如果某个事务需要回滚，可以通过撤销对应的日志操作，实现事务的回滚操作。

4. 提高并发性能：WAL 可以将事务的提交过程延迟到后台执行，从而减少了提交时的磁盘写入，提高了并发性能。

5. 减少数据丢失风险：由于先将操作记录到日志文件中，即使数据库崩溃或断电，数据也不会立即丢失，可以通过日志文件进行恢复。

总体而言，WAL 技术是数据库管理系统中非常重要的一种机制，它通过将数据修改操作记录到日志中，保证了事务的持久性和恢复性，同时提高了数据库的性能和并发能力。

4、什么是回表?

回表（Lookup）是数据库查询过程中的一种操作，当使用非聚集索引（或辅助索引）进行查询时，如果需要获取查询结果中的其他列数据时，就需要进行回表操作。

回表指的是在使用索引进行查询后，数据库还需要根据索引中的指针或引用，再次访问数据表中的实际数据行，以获取其他列的数据。这是因为非聚集索引只包含部分数据的引用信息，而不是实际的数据内容。当数据库需要获取查询结果中的其他列数据时，必须通过回表操作到主表中查询对应的数据行。

例如，假设有一个表 user，包含列 id、name 和 age。如果在 name 列上创建了一个非聚集索引，然后执行以下查询语句：

SELECT id, name FROM user WHERE name = 'John';

数据库首先会使用索引快速定位到满足条件的行，但索引中只包含了 id 和 name 列的引用。如果查询结果还需要 age 列的值，那么数据库会执行回表操作，根据索引中的引用去主表中查询相应的 age 列的值，最终返回完整的查询结果。

回表操作会增加额外的IO和CPU开销，因为需要在主表中进行额外的数据查找。为了减少回表的开销，可以使用覆盖索引（Covering Index）或调整查询语句，尽量包含索引中已有的列，避免回表操作，从而提高查询性能。

5、一条 Sql 语句查询一直慢会是什么原因?

一条 SQL 查询语句执行慢的原因可能有多种，以下是一些常见的原因：

1. 缺乏索引：如果查询涉及的列没有适当的索引，数据库可能需要进行全表扫描，导致查询变慢。在频繁执行的查询中，缺乏合适的索引可能会对性能产生重大影响。

2. 大数据量：如果查询的表包含大量数据，即使有索引，查询也可能变慢。在处理大数据量时，数据库需要更多的时间来搜索和检索数据，从而影响查询性能。

3. 锁竞争：如果查询涉及的数据行被其他事务锁定，查询可能会被阻塞，从而导致慢查询。长时间的锁竞争可能会影响并发性能和查询响应时间。

4. 不合理的查询计划：数据库查询优化器可能会选择不合理的执行计划，导致查询效率低下。这可能是由于统计信息不准确或查询语句写得不够优化。

5. 硬件资源限制：数据库服务器的硬件资源（如CPU、内存、磁盘IO）有限，如果查询过于复杂或大量并发请求，可能导致资源争用，从而影响查询性能。

6. 数据库配置不当：数据库的配置参数可能没有调整到最佳状态，可能会导致性能下降。例如，数据库连接池设置不当、缓冲区大小不合理等。

7. 错误的索引选择：虽然有索引，但是可能选择了不合适的索引类型或多余的索引，导致查询性能下降。

8. 子查询或关联查询：复杂的子查询或关联查询可能导致性能下降，特别是在数据量大的情况下。

9. 数据库碎片：数据库表和索引的碎片化可能导致查询性能下降，应定期进行表优化和索引重建。

要解决查询慢的问题，可以通过以下方法进行优化：

• 确保表中的列都有适当的索引。
• 优化查询语句，尽量减少不必要的子查询或关联查询。
• 定期优化数据库表和索引，避免碎片化。
• 确保数据库服务器的硬件资源充足，并进行合理的配置。
• 使用数据库性能监控工具定位慢查询，并进行性能分析和优化。

6、主从延迟要怎么解决?

主从延迟是指在主从复制环境中，从节点（Slave）相对于主节点（Master）的数据复制存在一定的时间差，即从节点上的数据更新相对于主节点上的数据更新是有一定延迟的。主从延迟可能会导致数据不一致，降低系统性能和可用性。

解决主从延迟的方法主要有以下几种：

1. 优化网络连接和硬件性能：

   • 确保主节点和从节点之间的网络连接稳定和高速，避免网络延迟对数据复制造成影响。
   • 提升硬件性能，尤其是从节点的计算能力、磁盘性能和内存大小，以更快地处理数据复制。

2. 优化复制线程设置：

   • 对于 MySQL，可以调整从节点的 slave_net_timeout 参数，增加复制线程超时时间，避免复制线程因超时而终止。
   • 调整 slave_parallel_workers 参数，允许多个复制线程并行处理，提高数据复制效率。

3. 使用半同步复制：

   • 在 MySQL 中，可以考虑启用半同步复制，这样主节点在提交事务前，至少一个从节点确认收到并写入数据，减少数据复制的延迟。

4. 使用多从节点：

   • 可以增加从节点的数量，将数据复制负载分摊到多个从节点上，减轻单个从节点的压力，从而减少复制延迟。

5. 使用并行复制：

   • 在 MySQL 5.7 及以上版本，可以启用并行复制功能，提高从节点复制数据的并发度，加快数据复制速度。

6. 减少主节点的负载：

   • 如果主节点的负载过高，可能导致数据复制的延迟。可以优化查询语句，增加索引，减少锁竞争，从而降低主节点的负载。

7. 定期监控和优化：

   • 定期监控主从复制状态，及时发现延迟问题，并进行调整和优化。
   • 定期优化数据库表和索引，避免碎片化，提高数据复制效率。

综合考虑以上方法，可以根据实际情况选择合适的解决方案，从而减少主从延迟，确保数据复制的准确性和及时性。

7、删除表数据后表的大小却没有变动,这是为什么?

如果在删除表数据后，表的大小没有发生变动，可能是由于数据库的存储引擎采用了延迟回收空间的机制，导致删除操作并没有立即释放表空间。

在某些数据库存储引擎中，如 InnoDB，删除数据时，并不会立即释放已删除数据所占用的磁盘空间，而是将这些空间标记为可重用状态，以便在后续插入数据时可以重复利用这些空间，从而减少频繁的磁盘空间分配操作。

这种延迟回收空间的机制可以提高数据库性能，避免频繁的磁盘空间分配和释放操作，但也会导致在删除大量数据后，表的大小并不会立即减小。当数据库需要更多空间来存储新数据时，才会逐渐回收已删除数据的空间。

如果你希望立即释放已删除数据所占用的空间，可以考虑执行表的优化操作，例如使用 OPTIMIZE TABLE 命令来重建表，从而回收已删除数据的空间。但需要注意，频繁执行优化操作也会带来一定的性能开销，因此需要根据实际情况谨慎使用。

8、为什么 VarChar 建议不要超过255?

在 MySQL 中，VARCHAR 类型表示可变长度的字符串，它可以存储不超过指定长度的字符数据。在早期的 MySQL 版本中，VARCHAR 类型的最大长度限制为 255 字符，这是因为 MySQL 5.0.3 及之前版本中的 VARCHAR 列使用了 1 字节来存储列长度信息。

在 MySQL 5.0.3 之后的版本，VARCHAR 列的最大长度限制扩展为 65,535 字符（64KB），这是因为在这个版本之后，VARCHAR 列的长度信息由 2 字节来存储，允许更大的最大长度。

然而，建议在实际使用时尽量避免过大的 VARCHAR 列，尤其是超过 255 字符的长度，原因如下：

1. 存储空间：VARCHAR 列的存储空间与实际存储的数据长度相关，如果 VARCHAR 列过大，会占用较多的存储空间。在数据量大的情况下，会增加数据库的存储需求。

2. 索引长度限制：对于 InnoDB 存储引擎，索引列的长度不能超过 767 字节。如果 VARCHAR 列作为索引列，并且超过了长度限制，就无法创建索引，从而影响查询性能。

3. 数据传输：较大的 VARCHAR 列会占用更多的网络传输资源，增加数据传输的开销。

4. 查询性能：较大的 VARCHAR 列可能会导致更慢的查询速度，尤其是在涉及大量数据的查询操作时，可能会影响性能。

综上所述，尽管现代 MySQL 版本中 VARCHAR 列的最大长度限制远大于 255 字符，但在设计数据库表时，建议根据实际业务需求和数据特点合理选择 VARCHAR 列的长度，避免过度使用过大的 VARCHAR 列，以优化数据库性能和存储效率。

9、分布式式事务怎么实现?

分布式事务是指涉及多个独立数据库或服务的事务，要保证这些数据库或服务在同一个事务中要么全部提交成功，要么全部回滚失败，以确保数据的一致性和完整性。

实现分布式事务有多种方式，以下是一些常见的实现方法：

1. 两阶段提交（Two-Phase Commit，2PC）：

   • 两阶段提交是最经典的分布式事务协议，包含准备阶段和提交阶段，确保所有参与者在事务提交时都能达成一致的结果。
   • 在准备阶段，协调者向所有参与者发送准备请求，询问它们是否可以执行事务并准备好提交。每个参与者执行事务的前半部分，但此时不提交事务。
   • 在提交阶段，如果所有参与者都准备好提交，则协调者发送提交请求给所有参与者，要求它们提交事务。如果任何一个参与者无法提交，协调者会发送回滚请求给所有参与者，要求它们回滚事务。

2. TCC（Try-Confirm-Cancel）：

   • TCC 是一种补偿型的分布式事务实现方法，将事务拆分为三个阶段：尝试执行、确认执行和取消执行。
   • 在尝试执行阶段，事务参与者尝试执行事务，但此时并不提交。如果所有参与者都成功执行，则进入确认执行阶段，提交事务。如果任何一个参与者失败，就进入取消执行阶段，进行回滚操作。

3. SAGA：

   • SAGA 是一种异步的分布式事务模式，通过一系列的局部事务来实现全局事务。每个局部事务都是独立的，并且可以单独提交或回滚。
   • 当一个局部事务提交后，会触发下一个局部事务，形成一个链式的事务序列。如果某个局部事务失败，可以通过回滚链中的前面事务来进行补偿。

4. XA 分布式事务：

   • XA 是一种数据库的分布式事务协议，可以实现在多个数据库之间的分布式事务。
   • XA 分布式事务通过 XA 接口来实现，数据库管理系统需要支持 XA 接口来协调多个数据库的事务。

实现分布式事务是一项复杂的工作，需要综合考虑数据一致性、事务可靠性、性能开销等因素。不同的场景和需求可能适用不同的分布式事务实现方法。在实际应用中，需要根据具体业务需求和技术栈选择合适的分布式事务解决方案。

10、为什么不要使用长事务?

长事务是指在数据库中持续运行时间较长的事务，可能涉及大量的数据操作，或者在事务内包含复杂的业务逻辑。虽然长事务在某些情况下可能是必要的，但通常不建议频繁使用长事务，原因如下：

1. 锁竞争：长事务可能会持有数据库中的锁资源，导致其他事务无法访问相同的数据，从而引发锁竞争问题，降低并发性能。

2. 数据库资源占用：长事务会占用数据库的资源（如内存、日志空间等），尤其是对于 InnoDB 存储引擎，长事务会导致事务日志文件较大，影响数据库性能。

3. 回滚风险：长事务在执行过程中可能对数据做了很多修改，如果最终需要回滚，可能会导致大量的数据回滚操作，耗费时间和资源。

4. 难以排查问题：长事务的执行过程可能复杂且持续时间较长，一旦出现问题，排查和调试困难，可能导致数据不一致等问题。

5. 并发控制：长事务可能导致其他事务长时间等待，影响并发性能，增加事务冲突和死锁的概率。

为避免使用长事务，可以采取以下措施：

1. 事务拆分：将长事务拆分成多个较短的事务，尽量减少事务持续的时间。

2. 优化查询：优化复杂查询语句，减少事务内的查询操作，尽量使用索引和合适的查询语句。

3. 限制事务时长：可以设置数据库的事务超时时间，强制终止长时间运行的事务，避免事务过长。

4. 提交频率：对于长事务中不需要整体回滚的部分，可以适时提交事务，释放资源。

5. 数据库参数调优：根据具体场景，调整数据库参数以优化事务处理性能。

总的来说，长事务可能导致性能下降、资源占用增加、难以维护等问题，因此在设计数据库应用时，应尽量避免频繁使用长事务，保证事务的简洁、高效和可维护性。

11、说说你的 Sql 调优思路

当进行 SQL 调优时，可以采取以下思路和步骤：

1. 收集性能指标：首先，需要收集数据库的性能指标，包括查询执行时间、资源利用率、锁等待情况、索引使用情况等。可以通过数据库性能监控工具或查询执行计划来获取这些信息。

2. 确定性能瓶颈：根据收集到的性能指标，确定数据库中存在的性能瓶颈，找出影响查询性能的具体原因，例如慢查询、高锁竞争、缺乏索引等。

3. 优化查询语句：对于慢查询，可以对查询语句进行优化，确保查询条件合理、使用索引、避免全表扫描等，提高查询效率。

4. 添加合适的索引：根据查询频率和查询条件，添加合适的索引可以大幅提高查询性能。但需要注意不要过度索引，以避免索引维护的开销和空间浪费。

5. 调整数据库参数：根据数据库的实际情况和硬件配置，调整数据库的参数设置，例如缓冲区大小、并发连接数、日志设置等，以优化数据库性能。

6. 优化表结构：合理设计数据库表结构，避免冗余数据和多余的列，确保表的规范化和合理分割，提高查询效率。

7. 分区表：对于大数据量的表，可以考虑使用分区表，将数据分散到多个分区中，减少查询范围，提高查询性能。

8. 避免全表操作：尽量避免全表操作，对于大数据量的表，优化查询条件，限制查询范围，以提高查询效率。

9. 缓存机制：对于一些频繁查询的结果，可以考虑使用缓存机制，将查询结果缓存起来，减少数据库压力。

10. 数据库复制与负载均衡：对于读写分离的数据库架构，可以将读操作分发到多个从节点，减轻主节点的压力，提高并发性能。

11. 定期维护和监控：定期对数据库进行维护和优化，监控数据库性能和运行状况，及时发现问题并进行处理。

综合以上步骤，SQL 调优的思路是找出性能瓶颈、优化查询语句和索引、调整数据库参数和表结构，以提高数据库的性能和响应速度。调优是一个迭代的过程，需要不断收集性能指标，分析优化效果，并不断优化，以达到更好的数据库性能。

12、池化设计思想

池化设计思想是一种常见的软件设计模式，主要用于资源的复用和管理，以提高系统性能和效率。池化设计思想的核心理念是将资源预先创建并保存在一个池中，当需要使用资源时，从池中获取资源，而不是每次需要资源时都创建新的资源实例。

常见的池化设计包括连接池、线程池、对象池等。下面分别介绍这些池化设计的思想：

1. 连接池：

   • 连接池是一种常用的数据库连接管理机制，用于提高数据库连接的复用和管理。在应用程序初始化时，会预先创建一定数量的数据库连接，并将这些连接保存在连接池中。
   • 当应用程序需要访问数据库时，从连接池中获取一个空闲的连接来执行数据库操作，而不是每次都创建新的数据库连接。当数据库操作完成后，将连接归还给连接池，供其他请求使用。

2. 线程池：

   • 线程池是一种管理线程的机制，用于减少线程创建和销毁的开销，提高线程的复用性和系统的响应性能。
   • 在应用程序初始化时，会创建一组线程并保存在线程池中。当有任务需要执行时，从线程池中获取空闲的线程来执行任务，任务执行完毕后，线程归还给线程池。
   • 线程池可以控制同时执行的线程数量，避免线程数量过多导致系统负担过重。

3. 对象池：

   • 对象池是一种管理对象的机制，用于减少对象的创建和销毁开销，提高对象的复用性。
   • 在应用程序初始化时，会创建一定数量的对象并保存在对象池中。当需要使用对象时，从对象池中获取一个空闲的对象来执行操作，操作完成后，对象归还给对象池。
   • 对象池可以节省对象的创建和销毁开销，特别适用于对象创建开销较大的情况。

池化设计思想在很多场景中都有应用，特别是在需要频繁使用和释放资源的情况下，通过池化机制可以大幅提高系统的性能和资源利用率，避免资源的频繁创建和销毁。

13、什么事是数据库连接池？为什么需要数据库连接池？

数据库连接池是一种管理数据库连接的机制，用于提高数据库连接的复用和管理效率。数据库连接池在应用程序初始化时，会预先创建一定数量的数据库连接，并将这些连接保存在连接池中。当应用程序需要访问数据库时，从连接池中获取一个空闲的数据库连接来执行数据库操作，而不是每次都创建新的数据库连接。当数据库操作完成后，将连接归还给连接池，供其他请求使用。

为什么需要数据库连接池？

1. 资源复用：数据库连接的创建和销毁是一项相对耗时的操作，通过使用连接池，可以将已创建的数据库连接进行复用，避免频繁创建和销毁连接，从而减少资源浪费。

2. 提高性能：数据库连接池能够保持一定数量的可用连接，避免每次请求都需要重新建立连接，从而减少了连接的建立和关闭开销，提高了数据库操作的性能和响应速度。

3. 控制连接数：数据库连接池可以设置最大连接数和最小连接数，通过合理配置连接池大小，可以控制同时打开的连接数量，避免过多的连接导致数据库性能下降。

4. 连接管理：数据库连接池可以对连接进行有效管理，确保连接的有效性和可用性。如果连接出现异常，连接池可以自动进行回收和重新创建，保证连接的健壮性。

5. 避免连接泄漏：在应用程序代码中手动管理数据库连接容易出现连接泄漏问题，而数据库连接池可以帮助自动管理连接的打开和关闭，避免连接泄漏，提高系统的稳定性和可靠性。

总的来说，数据库连接池是一种有效管理数据库连接的机制，通过复用连接、控制连接数和连接管理，可以提高数据库访问性能，减少资源浪费，增加系统稳定性。在高并发的应用场景下，使用数据库连接池是非常重要的数据库优化措施。