面试知识点整理（MySQL）

最新推荐文章于 2024-07-05 11:58:23 发布

Xhycz9

最新推荐文章于 2024-07-05 11:58:23 发布

阅读量1.1k

点赞数 40

文章标签：面试数据库职场和发展 java mysql

本文链接：https://blog.csdn.net/Xhycz9/article/details/135569912

版权

索引：

索引就是用来提高数据库查询效率。

索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

优点：提高数据检索效率、提高排序和分组性能、保证数据的唯一性、支持覆盖索引、优化连接操作

缺点：占用存储空间、增加写操作成本、索引维护开销、选择不当会导致性能下降

下面是聚集索引、覆盖索引、组合索引、前缀索引、唯一索引的详细定义、分类、作用、使用场景、优点和缺点：

1. 聚集索引（Clustered Index）：

定义： 聚集索引是一种特殊的索引，决定了数据在物理存储上的顺序，通常与表的主键相关联。在InnoDB等存储引擎中，一张表只能有一个聚集索引，数据行的存储顺序与聚集索引的顺序一致。
作用： 提高数据检索性能，加速范围查询和排序操作。
使用场景： 适用于频繁执行范围查询或排序操作的表，通常与主键关联。
优点： 高效的范围查询和排序，数据物理存储有序。
缺点： 插入和更新操作可能引发页面分裂，性能下降。

2. 覆盖索引（Covering Index）：

定义： 覆盖索引是一种索引，包含了查询所需的所有列，无需访问实际数据行即可满足查询需求。
作用： 提高查询性能，减少I/O操作。
使用场景： 适用于只需要返回索引列的查询，减少不必要的数据访问。
优点： 查询性能提高，减少了不必要的数据访问。
缺点： 需要额外的存储空间，对于频繁更新的表可能影响性能。

3. 组合索引（Composite Index）：

定义： 组合索引是由多个列组合而成的索引，用于同时查询多个列的数据。
作用： 提高多列条件查询的性能。
使用场景： 适用于需要同时查询多个列的情况，可减少索引的数量。
优点： 改善多列查询性能，减少索引维护开销。
缺点： 查询列的顺序对性能影响较大，不适用于所有情况。

4. 前缀索引（Prefix Index）：

定义： 前缀索引是一种索引，只包含列值的一部分，而不是整个列值。
作用： 减少索引的存储空间，节省内存和磁盘空间。
使用场景： 适用于长文本或字符列，但只需要索引列的前几个字符的查询。
优点： 节省存储空间，降低维护成本。
缺点： 查询性能可能不如完整索引，选择性降低。

5. 唯一索引（Unique Index）：

定义： 唯一索引保证索引列的值是唯一的，但允许多个NULL值存在。
作用： 确保列的唯一性。
使用场景： 适用于要求列数据唯一性的情况，但允许多个NULL值。
优点： 数据唯一性，提高查询性能。
缺点： 对唯一列的插入和更新操作有一定性能开销。

每种类型的索引都有其独特的用途和优劣势。在设计和优化数据库时，需要根据具体需求和数据模型来选择适当的索引类型，以达到最佳的查询性能和存储效率。

存储引擎：

InnoDB、MyISAM、Memory、Archive

1. InnoDB 存储引擎：

定义： InnoDB 是MySQL的一种事务型存储引擎，支持事务、外键、行级锁等特性。
作用： 支持事务性操作，提供高并发性和数据完整性。
使用场景： 适用于需要支持事务、外键约束、并发控制的应用，如电子商务、金融系统等。
优点： 数据完整性、高并发性、支持外键、行级锁、崩溃恢复。
缺点： 内存和磁盘消耗较大，对于只读操作性能略低。

2. MyISAM 存储引擎：

定义： MyISAM 是MySQL的一种非事务型存储引擎，不支持事务和外键。
作用： 不支持事务性操作，提供较高的读取性能。
使用场景： 适用于只读或读操作较多的应用，如博客、新闻站点。
优点： 高读取性能，支持全文索引。
缺点： 不支持事务、外键，不适合写入操作，崩溃后无法恢复。

3. Memory 存储引擎（也称为 Heap 存储引擎）：

定义： Memory 存储引擎将数据存储在内存中，速度非常快，但数据不持久化。
作用： 用于需要快速读写的临时数据存储，不支持事务和持久化。
使用场景： 适用于临时表、缓存等需要快速读写的应用，但数据不需要持久保存。
优点： 非常快的读写速度，适用于临时数据存储。
缺点： 数据不持久化，崩溃后数据丢失，不支持事务和外键。

4. Archive 存储引擎：

定义： Archive 存储引擎用于高度压缩的存储，适合大量历史数据的归档和查询。
作用： 用于节省存储空间和提供快速的数据归档和检索。
使用场景： 适用于需要长期保留大量历史数据的场景，如日志归档。
优点： 高度压缩，节省存储空间，适用于历史数据查询。
缺点： 不支持索引，只适合归档和查询，不适用于更新操作。

选择存储引擎应根据具体的应用需求，如事务支持、读写性能、数据完整性、数据持久性等方面进行权衡。通常，InnoDB适用于事务性应用，MyISAM适用于只读或读操作较多的应用，Memory适用于临时数据存储，Archive适用于数据归档。

InnoDB在大多数情况下都是最合适的，也是默认引擎

InnoDB支持事务、支持外键、是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高、不支持全文索引、不保存表的具体行数、支持表级锁和行级锁(默认)

锁：

按照锁的粒度分为：

行级锁(InnoDB引擎)：开销大，加锁慢；锁定力度最小，发生锁冲突的概率最低，高并发度

表级锁(MYISAM引擎)：开销小，加锁块；不会出现死锁；锁定力度大，发生锁冲突概率极高，低并发度

页级锁(BDB引擎):开销和加锁位于前两者之间；会出现死锁；锁定力度也位于两者之间，并发度一般

按照锁的类别分为共享锁和排它锁：

共享锁(读锁):当用户要读取数据时，加上共享锁。共享锁可以同时加上多个

排它锁(写锁):当用户要进行数据写入时，加上排它锁。排它锁只能加一个，他和其他的排它锁、共享锁都互斥

乐观锁和悲观锁：

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐一般会使用版本号机制或CAS算法实现

悲观锁：假设会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

乐观锁适用于写比较少的情况(读比较多)，即冲突比较少的情况下，省去锁开销，加大系统整体吞吐量

悲观锁适用于写比较多的情况，一般会经常发生冲突，这会导致上层应用会不断地进行retry，反倒降低了性能。

死锁：两个或两个以上事务在同一资源上进行相互占用，并请求锁定对方的资源，从而导致恶性循环的现象

解决方法：1.不同程序并发存取多个表，约定以相同的顺序访问表

2.同一事物，尽量做到一次锁定所需要的所有资源

3.对非常容易产生死锁的业务部分，可尝试升级锁定颗粒度，通过表级锁定来减少死锁

如果业务处理不好可以使用分布式事务所或者使用乐观锁

优化锁意见：

·使用较低的隔离级别

·设计索引去访问数据

·选择合理的事务大小，在记录显示加锁时，最好一次性请求足够级别的锁

·不同程序访问同一个表时，应尽量约定一个相同的顺序访问各表

·尽量使用相等条件访问数据，避免间隙锁对并发插入的影响

·数据查询，非必要不加锁

·对特定事物可以使用表锁来提高处理活着减少死锁的可能

MySQL事务

MySQL的事务是数据库操作的基本单位，事务要点包括以下内容：

事务的定义： 事务是一组SQL操作，要么全部执行成功，要么全部执行失败，具有原子性、一致性、隔离性和持久性（ACID）特性。

四大特性：
原子性（Atomicity）： 事务是不可分割的工作单元，要么全部成功，要么全部失败。如果在事务执行期间发生错误，将回滚事务，回到事务开始前的状态。
一致性（Consistency）： 事务执行前后，数据库的状态应保持一致性。这意味着事务必须满足数据库的约束和完整性规则。
隔离性（Isolation）： 多个事务可以并发执行，但每个事务应该被隔离开来，以防止互相干扰。隔离性有不同的隔离级别，如读未提交、读已提交、可重复读和串行化。
持久性（Durability）： 一旦事务提交，对数据库的修改应该永久保存在数据库中，即使系统崩溃也不会丢失。

事务的开始和结束： 事务通常以BEGIN或START TRANSACTION语句开始，以COMMIT或ROLLBACK语句结束。COMMIT表示提交事务，ROLLBACK表示回滚事务。

 #步骤一：开启事务（可选）
 start transaction;
 #步骤二：编写事务中的sql语句（insert、update、delete）
 #这里实现一下"李二给王五转账"的事务过程
 update t_account set balance = 50 where vname = "李二";
 update t_account set balance = 130 where vname = "王五";
 #步骤三：结束事务
 commit; #提交事务
 # rollback; #回滚事务:就是事务不执行，回滚到事务执行前的状态

事务的隔离级别： MySQL支持多个事务隔离级别，包括读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。隔离级别定义了事务之间的可见性和并发控制方式。不同的隔离级别会影响事务的性能和一致性。
1. mysql中的四种事务隔离级别如下：
  1. read uncommitted（读未提交数据）：允许事务读取未被其他事务提交的变更。（脏读、不可重复读和幻读的问题都会出现）。
  2. read committed（读已提交数据）：只允许事务读取已经被其他事务提交的变更。（可以避免脏读，但不可重复读和幻读的问题仍然可能出现） 3.repeatable read（可重复读）：确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新(update)。（可以避免脏读和不可重复读，但幻读仍然存在）
  3. serializable（串行化）：确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作，所有并发问题都可避免，但性能十分低下（因为你不完成就都不可以弄，效率太低）
  🌴脏读：对于两个事务T1,T2，T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的 🌴不可重复读：对于两个事务T1,T2，T1读取了一个字段，然后T2更新了该字段之后，T1在读取同一个字段，值就不同了 🌴 幻读：对于两个事务T1,T2，T1在A表中读取了一个字段，然后T2又在A表中插入了一些新的数据时，T1再读取该表时，就会发现神不知鬼不觉的多出几行了…
  
  脏读（Dirty Read）、幻读（Phantom Read）和可重复读（Repeatable Read）是与并发事务处理相关的数据库隔离级别问题，它们反映了不同程度的数据一致性和并发访问之间的权衡。
  1. 脏读（Dirty Read）：
    - 定义：脏读发生在一个事务中读取了另一个事务尚未提交的数据。如果另一个事务在稍后被回滚，读取的数据就是无效或“脏”的。
    - 作用：脏读可能导致错误的数据分析和决策，因为事务读取了未经验证的数据。
    - 使用场景：较少，通常不希望允许脏读，因为它可能导致不一致的数据。
    - 避免方法：通过将数据库隔离级别设置为可重复读或更高级别，可以避免脏读。这确保了事务只能读取已经提交的数据。
  2. 幻读（Phantom Read）：
    - 定义：幻读发生在一个事务中查询了一个范围内的数据，在该事务查询之前和之后，另一个事务插入、更新或删除了符合该范围的数据，导致事务看到了在其执行期间新增或删除的数据。
    - 作用：幻读可能导致事务的查询结果不一致，因为在事务执行期间数据发生了变化。
    - 使用场景：出现在更多的情况下，特别是在多个事务同时操作数据的情况下。
    - 避免方法：通过将数据库隔离级别设置为可重复读或更高级别，可以避免幻读。此外，使用锁定或乐观锁定等机制来保护数据范围也可以减少幻读的发生。
  3. 可重复读（Repeatable Read）：
    - 定义：可重复读隔离级别保证在一个事务内多次读取相同数据时，将会看到一致的数据状态，即使其他事务并发执行也不会影响。
    - 作用：可重复读提供了较高的数据一致性，确保事务内的查询结果不会发生变化。
    - 使用场景：在需要保证数据一致性的情况下，特别是在长时间事务或需要数据快照的情况下。
    - 避免方法：可重复读是一种较高的隔离级别，可以通过设置数据库隔离级别为可重复读来实现。
  避免脏读、幻读和可重复读的方法包括：
  - 使用合适的数据库隔离级别：设置事务的隔离级别为至少可重复读，以确保数据的一致性。
  - 使用锁定：在需要时使用行级锁或范围锁定来保护数据，以防止其他事务的干扰。
  - 使用乐观锁定：在执行更新操作前检查数据的版本或时间戳，以确保数据没有发生变化。
  - 合理设计数据库架构和事务：尽量避免长时间的事务和频繁的数据修改操作，以减少并发冲突的概率。
  选择适当的方法取决于应用程序的需求和性能要求，需要仔细权衡数据一致性和并发性。不同数据库管理系统可能会提供不同的隔离级别和锁定机制，因此需要了解所使用的数据库的特定行为。
事务的并发控制： 并发控制机制确保多个事务可以安全地并发执行而不会导致数据不一致。常见的并发控制机制包括锁和多版本并发控制（MVCC）。
事务的回滚和保存点： 事务可以通过ROLLBACK语句回滚到之前的状态，也可以使用保存点（SAVEPOINT）在事务内部创建回滚点。
事务的嵌套： MySQL支持事务的嵌套，一个事务内部可以包含其他事务，但要注意事务的提交和回滚会影响外部事务。
事务的锁： 事务可以使用锁来保护共享资源，包括行级锁、表级锁和页级锁等。
事务的性能： 事务的性能取决于数据库设计、索引使用、隔离级别的选择以及并发控制等因素。需要权衡事务的原子性和性能。

了解和合理使用事务是数据库管理和应用开发中的关键要点，可以确保数据的一致性和可靠性。

事务管理：MySQL提供了SQL语句来管理事务，包括BEGIN、COMMIT和ROLLBACK等。通过这些语句，应用程序可以显式地开始、提交和回滚事务。此外，MySQL还支持自动提交模式，其中每个SQL语句都被视为一个独立的事务。
MVCC（多版本并发控制）：InnoDB存储引擎使用MVCC来实现高度的隔离性。MVCC允许事务在读取数据时不阻塞其他事务的写入操作，从而提高了并发性能。每个事务看到的数据版本都是事务开始时的版本，这使得不同事务之间的读操作不会互相干扰。
日志和恢复：MySQL使用事务日志来记录所有的数据修改操作，这有助于数据库在崩溃或故障时进行恢复。数据库管理系统可以通过重做日志来恢复数据库到崩溃前的状态，确保数据的一致性和持久性。