mysql 面试题

事务是什么

事务是数据库管理系统（DBMS）中的一个重要概念，用于管理对数据库进行读写操作的一组相关操作。事务是一个原子性、一致性、隔离性和持久性（ACID）的工作单元。

具体来说，事务具有以下四个特性：

1. 原子性（Atomicity）：事务是一个不可分割的工作单元，要么全部执行成功，要么全部执行失败。如果事务中的任何一步操作失败，系统会自动回滚（Rollback）到事务开始之前的状态，保证数据的一致性。

2. 一致性（Consistency）：事务在执行过程中会使数据库从一个一致性状态转移到另一个一致性状态。事务开始之前和结束之后，数据库的完整性约束（例如唯一性约束、外键约束等）必须得到满足。

3. 隔离性（Isolation）：事务的执行不受其他事务的干扰，每个事务都应该感觉到它是在独立执行的。隔离性确保并发执行的事务之间不会相互影响，避免了由并发执行导致的数据不一致性问题。

4. 持久性（Durability）：一旦事务提交（Commit），其所做的修改将会永久保存在数据库中，并对后续的其他事务可见。即使在数据库系统发生故障或者重新启动之后，已经提交的事务所做的修改也应该得以保留。

事务可以通过BEGIN、COMMIT和ROLLBACK等命令来进行控制，其中：

• BEGIN：标志一个事务的开始。
• COMMIT：标志一个事务的结束，并将事务中的操作永久保存到数据库中。
• ROLLBACK：回滚事务，取消事务中的所有操作，将数据库恢复到事务开始之前的状态。

通过事务，数据库可以保证数据的一致性、可靠性和并发控制，是数据库系统中非常重要的一部分。

什么是存储过程

MySQL 存储过程是一种在 MySQL 数据库中存储的一组预编译 SQL 语句的集合，它们经过命名并存储在数据库中，可被多次调用执行。存储过程可以接受参数、执行条件判断、循环、异常处理等操作，使得数据库逻辑更加模块化和可重用。

以下是一个简单的 MySQL 存储过程示例，它接受一个参数并返回该参数的平方值：

DELIMITER //

CREATE PROCEDURE calculate_square (IN number INT, OUT square INT)
BEGIN
    SET square = number * number;
END //

DELIMITER ;

在这个示例中：

在这个示例中：

• DELIMITER 用于设置 SQL 语句的结束符，默认为分号 ;，在定义存储过程时，需要使用 // 作为结束符，而在最后使用 DELIMITER ; 恢复默认结束符。
• CREATE PROCEDURE 用于创建一个存储过程，后面跟着存储过程的名称和参数列表。
• IN 关键字指定参数为输入参数，表示存储过程可以接受传入的值。
• OUT 关键字指定参数为输出参数，表示存储过程可以将计算结果返回。
• 存储过程体以 BEGIN 开始，以 END 结束，其中包含了具体的 SQL 逻辑。
• 在这个示例中，计算了参数的平方，并将结果赋值给输出参数。

DROP PROCEDURE IF EXISTS staff_zt_test;
--删除存储过程
CREATE PROCEDURE staff_zt_test()
BEGIN
DECLARE myname varchar(1024);
declare local_status int; 
DECLARE mynamelist CURSOR FOR (select  name from user as uc1 where uc1.name  like 'GZPT%');

DECLARE CONTINUE HANDLER FOR NOT FOUND SET local_status=0;
set local_status=0;
OPEN mynamelist;
set local_status=(SELECT count(*) from (select  name from user as uc where  uc.name  like 'GZPT%') as uc2 );

while local_status>0 DO
FETCH mynamelist INTO myname;
#要处理的sql语句`
UPDATE user as f  set f.name = SUBSTR(myname,6,11) WHERE f.name = myname;
#处理完每条数据之后，需要给游标为值减一 
set local_status=(local_status-1);
END WHILE;
CLOSE mynamelist;
END;

CALL staff_zt_test();

这段代码创建了一个名为 staff_zt_test 的存储过程，用于更新名字以 "GZPT" 开头的用户的名字，去除 "GZPT" 前缀。这是一个常见的需求，可能是为了数据清洗或者统一命名规范。

在存储过程中：

• DECLARE 用于声明变量和游标。
• CURSOR 创建了一个游标，用于遍历满足条件的记录。
• CONTINUE HANDLER FOR NOT FOUND 声明了一个异常处理器，用于在游标遍历结束时跳出循环。
• 使用 OPEN 打开游标，FETCH 逐行获取记录，然后执行 UPDATE 语句进行数据更新。
• 在循环中，通过减少 local_status 的值来控制循环次数。
• 最后使用 CLOSE 关闭游标。

需要注意的是，存储过程的执行需要在正确的数据库中运行，并且要确保存储过程中使用的表和字段都是存在的，否则会出现错误。

mysql 有几种锁

MySQL中的锁通常是通过SQL语句或事务中的锁定操作来使用，而不是在配置文件中进行设置。以下是一些常见的MySQL锁的使用方式：

1. 表级锁：

   • 表锁定（Table Locking）：通过 LOCK TABLES 和 UNLOCK TABLES 语句可以对整个表进行锁定和解锁操作。这种方式适用于需要对整个表进行读写操作的场景，但会影响到其他事务对同一表的并发访问。

2. 行级锁：

   • 行锁定（Row Locking）：通过在事务中使用 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE 等语句可以对查询结果中的行进行锁定。这种方式适用于需要对特定行进行读写操作的场景，可以降低锁的粒度，提高并发性能。

3. 页级锁：

   • 页锁定（Page Locking）：MySQL在某些情况下会自动选择页级锁来进行并发控制，但大多数情况下，MySQL会根据需要自动升级锁的粒度，因此开发人员一般不需要直接操作页级锁。

4. 其他锁：

   • 意向锁（Intention Locking）：用于表级锁定和行级锁定之间的协调。
   • 间隙锁（Gap Locking）：用于防止其他事务在范围查询中插入新行。
   • Next-Key锁：是间隙锁和行锁的组合，用于防止幻读。

一文搞懂Mysql中的共享锁、排他锁、悲观锁、乐观锁及使用场景_数据库共享锁-CSDN博客

mysql 表锁 行锁 间隙锁 / 读锁 写锁_mysql lock_write-CSDN博客

MVCC的理解

MVCC（Multi-Version Concurrency Control，多版本并发控制）是一种数据库并发控制技术，常见于许多现代关系型数据库系统中，如MySQL、PostgreSQL等。它通过在数据库中维护多个数据版本，以实现并发事务的隔离性和一致性。

MVCC的基本思想是：在事务执行期间，每个事务看到的数据版本都是一个"快照"，而不是实际的数据。这意味着即使在事务执行期间其他事务对同一数据进行了修改，事务也只会看到之前的数据版本，从而保证了事务的隔离性。

MVCC的实现通常依赖以下几个关键组件：

1. 版本号或时间戳：数据库为每个数据版本分配一个唯一的版本号或者时间戳，用于标识数据的不同版本。当事务开始时，数据库会为该事务创建一个"读视图"，记录事务开始时数据库中已经存在的数据版本。

2. Undo Log（回滚日志）：在事务开始时，数据库会将事务的修改操作写入Undo Log中。当事务需要回滚时，可以根据Undo Log中的信息将数据恢复到事务开始之前的状态。

3. Read View（读视图）：在每个事务开始时，数据库会为该事务创建一个读视图，用于记录事务开始时数据库中已经存在的数据版本。当事务执行读操作时，数据库会根据读视图确定要读取的数据版本，从而保证事务读取到的数据是一致的。

MVCC的优点包括：

• 提高并发性能：多个事务可以同时读取同一数据，不会相互阻塞。
• 降低锁竞争：MVCC减少了锁的使用，降低了锁竞争的概率。
• 保证数据一致性：每个事务看到的数据都是一致的，不会受到其他事务的影响。

总的来说，MVCC是一种高效的并发控制技术，能够提高数据库系统的并发性能和事务隔离性，同时保证了数据的一致性和可靠性。

Mysql的事务隔离级别

事务隔离级别，是为了解决多个并行事务竞争导致的数据安全问题的一种规范。

具体来说，多个事务竞争可能会产生三种不同的现象。

1.(如图）假设有两个事务T1/T2同时在执行，T1事务有可能会读取到T2事务未提交的数据，但是未提交的事务T2可能会回滚，也就导致了T1事务读取到最终不一定存在的数据产生脏读的现象。

2.（如图）假设有两个事务T1/T2同时执行，事务T1在不同的时刻读取同一行数据的时候结果可能不一样，从而导致不可重复读的问题。

3.（如图），假设有两个事务T1/T2同时执行，事务T1执行范围查询或者范围修改的过程中，事务T2插入了一条属于事务T1范围内的数据并且提交了，这时候在事务T1查询发现多出来了一条数据，或者在T1事务发现这条数据没有被修改，看起来像是产生了幻觉，这种现象称为幻读。

而这三种现象在实际应用中，可能有些场景不能接受某些现象的存在，所以在SQL标准中定义了四种隔离级别，分别是：

1. 读未提交，在这种隔离级别下，可能会产生脏读、不可重复读、幻读。
2. 读已提交（RC)，在这种隔离级别下，可能会产生不可重复读和幻读。
3. 可重复读（RR），在这种隔离级别下，可能会产生幻读
4. 串行化，在这种隔离级别下，多个并行事务串行化执行，不会产生安全性问题。

这四种隔离级别里面，只有串行化解决了全部的问题，但也意味着这种隔离级别的性能是最低的。

1、怎么解决数据库幻读

幻读是由于数据库事务并发执行时，某个事务在两次查询之间，另一个事务插入了新的数据行，导致第一个事务在第二次查询时看到了之前不存在的数据行，从而产生了幻觉一样的现象。为了解决数据库中的幻读问题，可以采取以下一些方法：

1. 使用更高的事务隔离级别：

   • 将事务隔离级别设置为Serializable（串行化），这是最高的事务隔离级别，可以避免幻读问题的发生。但是需要注意，设置为Serializable级别会增加系统的开销和性能损耗。

2. 使用锁：

   • 可以在事务中使用锁来保证数据的一致性，例如在需要避免幻读的范围内对数据行或数据表进行加锁。可以使用行级锁或范围锁来防止其他事务在此期间插入新的数据行。

3. 使用乐观锁：

   • 乐观锁是一种乐观地假设并发事务之间不会发生冲突的并发控制机制，通常通过版本号或时间戳来实现。在执行更新操作时，先读取数据的版本信息，然后在更新时检查版本信息是否发生变化，如果未发生变化则执行更新操作，否则进行相应的处理。

4. 调整业务逻辑：

   • 重新审视业务逻辑，尽量避免在一个事务中执行长时间的读操作，以减少幻读的可能性。可以将事务拆分成多个较小的事务，或者调整事务的执行顺序，以减少事务之间的并发冲突。

5. 使用合适的索引：

   • 使用合适的索引可以提高查询的效率，减少长时间的查询操作，从而降低发生幻读的可能性。特别是在频繁查询的列上创建索引，可以有效减少幻读问题的发生。

综上所述，解决数据库中的幻读问题可以通过调整事务隔离级别、使用锁、乐观锁、调整业务逻辑和使用合适的索引等方法来实现。具体选择哪种方法需要根据业务需求和系统特点进行综合考虑和评估。

2、使用乐观锁展开来说说

使用乐观锁是一种基于乐观假设的并发控制机制，它假设在事务并发执行的情况下，冲突的概率较低，因此在读取数据时不会加锁，而是在更新数据时检查是否存在冲突。以下是使用乐观锁的一般步骤和方法：

1. 为数据表添加版本字段：

   • 在数据库表中添加一个版本字段（例如version），用于记录数据的版本信息。版本字段通常是一个整数类型，每次更新数据时都会递增。

2. 读取数据并记录版本号：

   • 当需要更新数据时，首先读取要更新的数据，并记录当前的版本号。这可以通过在查询语句中获取版本字段的值来实现。

3. 更新数据时检查版本号：

   • 在更新数据时，再次查询数据并获取最新的版本号。然后，比较更新前记录的版本号和最新的版本号是否一致：
     • 如果一致，则表示在读取数据后没有其他事务对该数据进行更新，可以继续执行更新操作。
     • 如果不一致，则表示其他事务已经修改了数据，当前事务更新的数据可能已经过时，需要进行相应的处理，例如回滚事务或重新尝试更新操作。

4. 执行更新操作：

   • 如果版本号一致，则执行更新操作，并更新版本号字段的值。这可以通过在更新语句中递增版本字段的值来实现。

5. 处理更新冲突：

   • 如果在更新数据时发现版本号不一致，则需要处理更新冲突。通常的处理方式包括：
     • 回滚事务并重新尝试更新操作。
     • 向用户显示更新冲突的消息，让用户选择如何处理。
     • 采取其他合适的业务逻辑处理方式，如记录日志、发送通知等。

乐观锁的优点是不需要加锁，可以提高系统的并发性能；缺点是可能会出现更新冲突，需要额外的处理逻辑。因此，在使用乐观锁时需要根据业务场景和系统需求来进行权衡和选择。

举个例子，假设有两个用户同时想要更新同一行数据的情况。使用乐观锁时，系统不会在读取数据时立即对数据进行加锁。而是先让两个用户都读取数据，并记录下读取时的版本号。

然后，当第一个用户要更新数据时，系统会再次检查数据的版本号是否与之前记录的版本号相同。如果相同，就表示在第一个用户读取数据后，没有其他用户对数据进行了修改，那么就允许第一个用户进行更新操作，并更新版本号。如果版本号不同，则表示在第一个用户读取数据后，另一个用户已经对数据进行了修改，这时第一个用户的更新操作可能已经过时，需要相应的处理。

这种乐观的方式避免了在读取数据时立即对数据进行加锁，从而提高了系统的并发性能。但是，如果在更新时发现有冲突发生，就需要根据实际情况进行处理，例如回滚事务或者重新尝试更新操作。

3、悲观锁

举个例子，如果一个事务想要更新某个数据行，它会先尝试获取该数据行的写锁（排他锁）。如果成功获取到了写锁，就表示它可以对数据进行操作，其他事务则必须等待当前事务释放锁才能访问相同的数据行。这样就可以保证数据在操作时不会受到其他事务的干扰。

悲观锁的优点是能够确保数据的一致性，因为在访问数据之前就已经对数据进行了加锁，其他事务无法对数据进行修改。但是，悲观锁的缺点是会增加系统的开销和性能损耗，因为它会阻塞其他事务对数据的访问，从而降低了系统的并发性能。

4、乐观锁 悲观锁的使用

【MySQl】MySQl中的乐观锁是怎么实现的_mysql 乐观锁-CSDN博客

1、乐观锁适用于读多写少的场景，可以省去频繁加锁、释放锁的开销，提高吞吐量

2、在写比较多的场景下，乐观锁会因为版本不一致，不断重试更新，产生大量自旋，消耗 CPU，影响性能。这种情况下，适合悲观锁

5、死锁

死锁是指在多个并发事务中，每个事务都在等待其他事务释放所占用的资源，从而导致所有事务都无法继续执行的情况。简单来说，就是多个事务之间相互等待对方释放资源，最终导致所有事务都无法完成。

假设有一个银行的数据库系统，包含了两个账户表（account1和account2），每个表包含账户ID和余额字段。现在有两个事务同时进行转账操作，一个从账户1向账户2转账，另一个从账户2向账户1转账。如果这两个事务同时执行，可能会发生死锁。

下面是一个可能导致死锁的示例：

1. 事务1：从账户1向账户2转账：

   BEGIN TRANSACTION;
   UPDATE account1 SET balance = balance - 100 WHERE account_id = 1;
   -- 在更新账户1的同时，需要锁定账户2
   UPDATE account2 SET balance = balance + 100 WHERE account_id = 2;
   COMMIT;
   

• 事务2：从账户2向账户1转账：

  BEGIN TRANSACTION;
  UPDATE account2 SET balance = balance - 100 WHERE account_id = 2;
  -- 在更新账户2的同时，需要锁定账户1
  UPDATE account1 SET balance = balance + 100 WHERE account_id = 1;
  COMMIT;
  

在这种情况下，如果事务1先获取了账户1的锁并等待账户2的锁，同时事务2先获取了账户2的锁并等待账户1的锁，那么就会发生死锁。因为事务1等待事务2释放账户1的锁，而事务2等待事务1释放账户2的锁，形成了循环等待的情况。

当发生死锁时，MySQL通常会选择一个事务作为死锁牺牲品（victim），回滚该事务并释放其持有的锁，从而解除死锁。然后，其他事务可以继续执行。

6、自旋锁

MySQL 数据库中 MyISAM 和 InnoDB 存储引擎的区别

MySQL数据库---存储引擎（MyISAM与InnoDB）_mysql存储引擎-CSDN博客

mysql 分库分表

在MySQL中，分库分表通常涉及以下几个方面：

1. 水平拆分数据库（分库）：

   • 将单个数据库的数据按照某种规则（如用户ID、地理位置等）拆分存储到多个数据库实例中。每个数据库实例负责存储一部分数据，可以独立扩展和管理。

2. 垂直拆分表（分表）：

   • 将单个数据库的表按照某种规则（如数据类型、访问频率等）拆分为多个子表，每个子表只包含部分字段或数据。这样可以减少单个表的数据量，提高查询性能。

分库分表能够有效地提高数据库系统的性能和可扩展性，但也会增加系统的复杂度和管理成本。因此，在使用分库分表时需要仔细评估业务需求和系统架构，选择合适的分片策略和工具，并考虑好数据一致性、事务管理、扩展性等方面的问题。

索引实现原理及优化