数据库基础知识概要

1. SQL语句类型

1. DDL（Data Definition Language，数据定义语言）：

DDL语句用于定义数据库对象（如表、索引、视图等）。常见的DDL语句包括：

　　CREATE：用于创建数据库对象，如创建表、索引、视图等。

　　ALTER：用于修改数据库对象的结构，如修改表的列、添加约束等。

　　DROP：用于删除数据库对象，如删除表、索引、视图等。

　　TRUNCATE：用于删除表中的所有数据，但保留表结构

2. DML（Data Manipulation Language，数据操作语言）：

DML语句用于对数据库中的数据进行操作（插入、更新、删除）。常见的DML语句包括：

• SELECT：用于从数据库中查询数据。
• INSERT：用于向表中插入新的数据。
• UPDATE：用于更新表中的数据。
• DELETE：用于删除表中的数据。

3. DQL（Data Query Language，数据查询语言）：

DQL语句用于从数据库中查询数据。DQL语句的核心是SELECT语句，可以使用SELECT语句查询满足特定条件的数据，并对结果进行排序、分组等处理

4. DCL（Data Control Language，数据控制语言）

DCL语句用于对数据库的访问权限进行管理。常见的DCL语句包括：

• GRANT：用于授予用户访问权限。
• REVOKE：用于撤销用户的访问权限。
• DENY：用于拒绝用户的访问权限。

2. 索引作用，底层结构及常见类型

索引在数据库中起着重要的作用，它可以提高数据库的查询性能和数据的检索速度。索引是一种数据结构，用于快速定位和访问数据库中的特定数据。

作用：

• 提高查询性能：通过使用索引，可以减少数据库查询的数据量，从而提高查询速度。
• 加速数据检索：索引可以帮助数据库快速定位和访问满足特定条件的数据，减少数据的扫描时间。

底层结构：
数据库索引的底层结构可以有多种实现方式，常见的包括以下几种：

1. B-树（B-Tree）索引：B-树是一种平衡的多路搜索树，它的特点是可以自动调整树的结构以适应数据的插入和删除操作。B-树索引常用于磁盘存储的数据库，因为它可以减少磁盘访问次数，提高查询效率。
2. B+树（B+Tree）索引：B+树是在B-树的基础上进行优化的一种数据结构。它与B-树类似，但在叶子节点上存储了所有的关键字和对应的数据指针，这样可以加快范围查询和顺序访问的速度。B+树索引是大多数关系型数据库中最常用的索引类型。
3. 哈希（Hash）索引：哈希索引使用哈希函数将关键字映射到一个固定长度的哈希值，然后将哈希值与数据的存储位置关联起来。哈希索引适用于等值查询，但不适用于范围查询或排序操作。
4. 全文（Full-Text）索引：全文索引用于对文本内容进行搜索，它可以对文本字段中的关键词进行索引和检索，支持全文搜索和模糊匹配。

常见类型：
在常见的关系型数据库中，常用的索引类型包括：

1. 主键索引（Primary Key Index）：用于唯一标识表中的记录，保证主键的唯一性和索引的快速访问。
2. 唯一索引（Unique Index）：用于保证某个列或列组合的唯一性，可以加速唯一性检查。
3. 聚集索引（Clustered Index）：指定表的物理顺序，表中的记录按照聚集索引的顺序存储。
4. 非聚集索引（Non-Clustered Index）：不指定表的物理顺序，独立存储索引的数据结构。
5. 复合索引（Composite Index）：使用多个列组合作为索引的键，支持多个列的联合查询。
6. 全文索引（Full-Text Index）：用于全文搜索和模糊匹配的索引类型，支持对文本内容进行搜索。

3. 事务的特性

1. 原子性（Atomicity）：事务是一个原子操作单元，要么全部执行成功，要么全部失败回滚。原子性确保事务中的所有操作要么全都执行，要么全都不执行，不会出现部分操作成功而部分操作失败的情况。
2. 一致性（Consistency）：事务在执行之前和执行之后，数据库的完整性约束没有被破坏。一致性确保数据库从一个一致的状态转移到另一个一致的状态，它定义了数据在事务执行过程中的合法变化。
3. 隔离性（Isolation）：事务的执行是相互隔离的，一个事务的操作不会被其他并发事务所干扰。隔离性确保事务在并发执行时，每个事务的操作都像是在独立执行，避免了并发读写操作导致的数据不一致问题。
4. 持久性（Durability）：一旦事务提交，其所做的修改将永久保存在数据库中，即使系统发生故障或重启。持久性确保事务提交后的修改是永久性的，不会因为系统故障而丢失。

4. 事务的隔离级别

1. 读未提交（Read Uncommitted）：

• 最低的隔离级别，事务中的未提交修改对其他事务都是可见的。
• 可能导致脏读（Dirty Read），即读取到其他事务尚未提交的数据，可能是不一致的数据。
• 存在幻读（Phantom Read），即在同一个事务中多次执行同样的查询，结果集不一致。

2. 读已提交（Read Committed）：

• 事务只能读取到已经提交的数据，未提交的数据对其他事务不可见。
• 避免了脏读的问题，但仍可能导致幻读。
• 大多数常见数据库的默认隔离级别。

3. 可重复读（Repeatable Read）：

• 保证了在同一事务中多次读取同一数据时，结果保持一致。
• 读取的数据是在事务开始时确定的快照，即使其他事务对数据进行修改也不可见。
• 避免了脏读和幻读的问题。

4. 序列化（Serializable）：

• 最高的隔离级别，通过强制事务串行执行来避免并发问题。
• 保证了事务之间的完全隔离，避免了脏读、幻读和不可重复读的问题。
• 性能较差，一般情况下只在特殊需求下使用。
• 
  

5. 事务并发引起的三大问题

1. 脏读（Dirty Read）：

• 脏读指的是一个事务读取了另一个事务尚未提交的数据。当一个事务读取到了被另一个事务修改但尚未提交的数据时，如果另一个事务最终回滚，则读取到的数据是无效的。
• 脏读可能导致数据不一致性和错误的结果。

2. 不可重复读（Non-repeatable Read）：

• 不可重复读指的是在同一个事务中，多次读取同一数据时，得到的结果不一致。这是因为在读取过程中，其他并发事务对该数据进行了修改或删除。
• 不可重复读可能导致事务在多次读取同一数据时无法保持一致性，破坏了事务的隔离性。

3. 幻读（Phantom Read）：

• 幻读是指在同一个事务中，多次执行同样的查询，得到的结果集不一致。这是因为在查询过程中，其他并发事务插入了新的数据行，导致结果集发生了变化。
• 幻读可能导致事务在同一查询中读取到不同的数据行，无法保持一致性。

6. 死锁的原因及解决办法：

死锁是指两个或多个事务因为互相等待对方释放资源而无法继续执行的状态。死锁的发生是由于以下原因之一或多个原因共同作用：

1. 互斥条件（Mutual Exclusion）：资源只能同时被一个事务占用，当某个事务占用了一个资源后，其他事务无法同时占用该资源。
2. 请求与保持条件（Hold and Wait）：一个事务在持有资源的同时，又申请其他事务所占有的资源。
3. 不可剥夺条件（No Preemption）：资源只能由持有者显式释放，其他事务无法强制抢占。
4. 循环等待条件（Circular Wait）：多个事务形成一个循环等待资源的链，每个事务都在等待下一个事务所占有的资源。

为了解决死锁问题，可以采取以下几种常用的解决办法：

1. 预防死锁（Deadlock Prevention）：

• 通过破坏死锁发生的四个必要条件中的一个或多个，来预防死锁的发生。
• 可以在系统设计阶段采用资源分配策略、事务调度策略等方式来预防死锁。

2. 避免死锁（Deadlock Avoidance）：

• 在运行时动态判断是否分配资源，避免可能导致死锁的资源分配情况。
• 通过资源分配的安全性检查和资源请求的合理判断，避免进入可能导致死锁的状态。

3. 检测与恢复（Deadlock Detection and Recovery）：

• 允许死锁发生，但通过周期性地检测系统中的死锁状态，并采取恢复措施来解除死锁。
• 可以使用图算法（如资源分配图）来检测死锁，并通过回滚、抢占资源等方式进行恢复。

4. 死锁忽略（Deadlock Ignorance）：

• 假设死锁很少发生或发生死锁的代价较低，可以忽略死锁问题，不采取专门的死锁处理措施。
• 此方法适用于某些特定环境下，如批处理系统等。

1. 乐观锁和悲观锁的理解及使用

乐观锁和悲观锁是在并发编程中使用的两种并发控制机制，用于解决多线程或多进程环境下的数据一致性问题。

1. 悲观锁（Pessimistic Locking）：
　　悲观锁的思想是假设并发访问会导致冲突，因此在访问共享资源之前，悲观锁会将资源锁定，确保其他线程无法修改资源。悲观锁的典型应用是数据库中的行级锁，使用SELECT...FOR

UPDATE语句锁定查询结果。

使用悲观锁的过程如下：

• 当一个线程要访问共享资源时，它会先尝试获取锁。
• 如果锁已经被其他线程获取，则当前线程会被阻塞，直到锁被释放。
• 当线程获得了锁之后，它可以安全地访问共享资源，其他线程无法修改该资源。
• 当线程完成操作后，释放锁，其他线程可以获取到锁并访问资源。

悲观锁的优点是保证了数据的一致性，但是它的缺点是在高并发环境下，锁的竞争会导致性能下降。

2. 乐观锁（Optimistic Locking）：
乐观锁的思想是假设并发访问不会导致冲突，因此在线程访问共享资源之前，不会加锁。相反，乐观锁会在更新资源时，检查在此期间是否有其他线程修改了资源。

使用乐观锁的过程如下：

• 当一个线程要更新共享资源时，它首先会读取资源的版本号或标识。
• 在进行更新之前，线程会检查资源的版本号是否发生了变化。
• 如果资源的版本号没有变化，线程会更新资源，并更新版本号。
• 如果资源的版本号发生了变化，表示有其他线程已经修改过资源，当前线程的操作可能会产生冲突。
• 在发生冲突时，可以选择进行回滚操作或者重试整个过程。

　　乐观锁的优点是在无冲突的情况下，不需要进行加锁操作，从而提高了并发性能。然而，如果冲突频繁发生，会导致大量的回滚和重试操作，降低性能。

总的来说，悲观锁适合对于冲突频繁发生的场景，可以保证数据的一致性；而乐观锁适合对于冲突较少发生的场景，可以提高并发性能。选择使用哪种锁要根据具体的应用场景和性能需求进行

权衡。

2. 聚集索引和非聚集索引的区别：

聚集索引（Clustered Index）和非聚集索引（Non-clustered Index）是数据库中常用的索引类型，它们在索引的组织方式和数据访问方式上存在一些区别。

聚集索引：

• 聚集索引定义了数据表的物理排序方式。每个表只能有一个聚集索引，它决定了表中数据行的物理存储顺序。如果一个表有聚集索引，那么数据行将按照聚集索引的排序顺序存储在磁盘上。
• 聚集索引的叶子节点包含了整个数据行的信息，因此当使用聚集索引进行数据查询时，可以直接通过索引找到所需的数据行。
• 由于每个表只能有一个聚集索引，一般情况下，聚集索引会选择主键作为索引键。

非聚集索引：

• 非聚集索引是基于表中的列创建的索引，它存储了索引键和指向对应数据行的指针。一个表可以有多个非聚集索引。
• 非聚集索引的叶子节点不包含完整的数据行，而是包含了索引键和指向对应数据行的指针。当使用非聚集索引进行数据查询时，首先通过索引找到对应的数据行的指针，然后再通过指针获取完整的数据行。
• 非聚集索引可以加快数据的查找速度，尤其是在涉及到过滤和排序的查询操作中。

区别总结：

• 聚集索引决定了数据行的物理存储顺序，而非聚集索引只是提供了数据行的逻辑顺序。
• 聚集索引的叶子节点包含完整的数据行，而非聚集索引的叶子节点只包含索引键和指向数据行的指针。
• 一个表只能有一个聚集索引，但可以有多个非聚集索引。

在实际使用中，根据具体的查询需求和数据特点，可以根据需要选择适当的索引类型，以提高数据库的查询性能和数据访问效率。

3.  为什么索引用B+树，而不用普通的二叉树

1. 磁盘访问效率： B+树是一种多叉树，它具有分支因子（即子节点的最大数量）更高的特点。相比之下，二叉树每个节点只有两个子节点。在磁盘上，每次读取或写入的开销是非常昂贵的操作，因此减少磁盘访问次数可以提高索引的性能。B+树的高分支因子意味着在相同高度的情况下，它可以存储更多的键值对，减少了磁盘I/O次数。而二叉树的分支因子较低，可能需要更多的磁盘访问来定位目标数据。
2. 顺序访问性能： B+树的叶子节点使用链表连接起来，形成一个有序的链表结构。这使得范围查询和顺序访问非常高效。例如，在数据库中，如果需要查询某个范围内的数据（如按时间排序的记录），B+树可以利用有序的叶子节点链表进行快速定位和遍历。而在二叉树中，由于没有有序链表结构，需要进行中序遍历才能获取有序数据，这会增加额外的开销。
3. 索引的稳定性： B+树作为一种自平衡树结构，对于插入和删除操作具有较好的稳定性。当在B+树中进行插入或删除操作时，只需要对树的部分节点进行修改，而不需要像二叉树那样进行全局的重新平衡。这种特性使得B+树更适合于高效地维护索引结构。
4. 缓存利用： 现代计算机系统通常都有层次化的缓存结构，其中内存缓存（如CPU缓存）的访问速度远高于磁盘。B+树由于具有高的分支因子，可以存储更多的键值对在每个节点中，因此在搜索过程中可以利用更好地利用缓存，减少内存访问的次数。而二叉树由于分支因子较低，可能需要更多的内存访问来获取相同数量的键值对。

综上所述，B+树相对于普通的二叉树具有更好的磁盘访问效率、顺序访问性能、稳定性和缓存利用，使其成为了广泛应用于索引结构的一种理想选择。

4. Hash索引和B+树索引区别：

Hash索引和B+树索引是两种常见的索引结构，它们在实现原理、适用场景和性能方面存在一些区别。

实现原理：

• Hash索引： Hash索引使用散列函数将索引键转换为存储位置的散列码。散列码经过映射后直接指向存储数据的位置，因此在访问数据时具有固定的时间复杂度（O(1)）。
• B+树索引： B+树索引是一种多叉树结构，具有根节点、内部节点和叶子节点。每个节点包含多个键值对，通过比较键值来导航到下一个节点。B+树索引通过在树中进行有序查找来定位数据，因此访问时间的复杂度与树的高度相关（通常为O(log n)）。

适用场景：

• Hash索引： Hash索引适用于等值查询，即根据精确的索引键值查找数据。它对于相等比较非常快速，但不适用于范围查询或排序操作。
• B+树索引： B+树索引适用于范围查询、排序操作和模糊查询。由于B+树的有序性，它可以支持按顺序遍历数据和快速定位范围内的数据。

数据访问性能：

• Hash索引： Hash索引具有固定的访问时间复杂度（O(1)），因此在等值查询方面非常高效。但是，由于散列函数的散列冲突可能导致链表的形成，这可能会影响性能，尤其是在高负载情况下。
• B+树索引： B+树索引的访问时间复杂度与树的高度相关，通常为O(log n)。尽管每次访问可能需要多次磁盘I/O，但B+树索引在范围查询和顺序访问方面具有良好的性能。

存储空间利用：

• Hash索引： Hash索引通常需要预先分配足够的散列槽位，以防止散列冲突。这可能会导致存储空间的浪费，尤其是在数据分布不均匀的情况下。
• B+树索引： B+树索引在内部节点上存储键值对和子节点指针，而叶子节点上存储键值对和指向数据的指针。相比之下，B+树索引通常可以更好地利用存储空间。

综上所述，Hash索引适用于等值查询，具有固定的访问时间，但不支持范围查询和排序操作。B+树索引适用于范围查询、排序操作和模糊查询，具有较好的顺序访问性能和稳定性，但访问时间复杂度与树的高度相关。选择使用哪种索引结构取决于具体的应用需求和数据访问模式。

5. 索引不适合哪些场景以及有哪些优缺点：

索引在大多数情况下可以显著提高数据库查询的性能，但并不适合所有场景。以下是索引不适合的一些场景以及索引的一些优点和缺点：

索引不适合的场景：

1. 低基数列： 当列中的唯一值较少时，例如性别列只有两个可能的取值（男或女），建立索引可能不会带来明显的性能提升。在这种情况下，扫描整个表的代价可能比使用索引更低。
2. 频繁更新的列： 如果一个列经常被更新，索引的维护成本会很高。每次更新列的值时，可能需要更新索引数据结构，这会增加写操作的开销。频繁更新的列可能会导致索引失去效益，甚至降低性能。
3. 小表： 对于非常小的表，建立索引可能没有太大意义。在小表中，全表扫描的代价可能相对较低，而使用索引进行查找可能会增加额外的开销。

索引的优点：

1. 加速查询： 索引可以大大加速查询操作，特别是在大型表中。通过使用索引，数据库引擎可以快速定位满足查询条件的数据行，减少了全表扫描的需要。
2. 支持排序和聚合操作： 索引可以使排序和聚合操作更高效。通过使用有序索引，数据库引擎可以避免对整个表进行排序，从而提高操作的性能。
3. 优化连接操作： 对于连接操作（如JOIN），索引可以帮助加速数据的查找和匹配，减少连接操作的成本。

索引的缺点：

1. 占用存储空间： 索引需要占用额外的存储空间。对于大型表和多个索引的情况，索引可能占据相当大的存储空间。
2. 增加写操作的开销： 当插入、更新或删除数据时，索引需要进行相应的维护操作，这会增加写操作的开销。如果频繁进行写操作，索引的维护成本可能会成为性能瓶颈。
3. 索引选择和调优困难： 在设计索引时，需要根据实际查询模式和数据访问模式进行权衡和选择。选择不当的索引或过多的索引可能会导致性能下降，而调优索引可能需要经验和测试。

综上所述，索引在大多数情况下是数据库性能优化的重要工具，但在某些场景下可能不适用或需要谨慎使用。正确的索引设计和调优可以提高查询性能，而错误的使用可能会导致额外的开销和性能下降。

6.最左前缀匹配原则是什么

最左前缀匹配原则是数据库索引设计中的一个重要原则，它指出在使用复合索引（Composite Index）时，索引将按照索引键的顺序进行匹配和检索，并且只能利用索引的最左前缀来进行匹配。

具体来说，如果一个复合索引包含多个列（例如(A, B, C)），那么在查询时，最左前缀匹配原则要求查询条件必须从索引的最左侧开始，并且连续地匹配到索引的某个位置为止。也就是说，查询条件可以是(A)、(A, B)或者(A, B, C)，但不能是(B)、(C)或者(B, C)。

遵循最左前缀匹配原则的好处是可以最大程度地利用索引的有序性，提高查询的效率。由于索引按照键的顺序存储，因此在查询时只需定位到满足最左前缀条件的索引位置，而不需要扫描整个索引。

举个例子，假设有一个复合索引 (A, B, C)，如果查询条件是(A = 1)，那么索引可以用于加速查询，因为最左前缀 (A) 匹配成功。但如果查询条件是(B = 2)，即使索引中包含了列 B，也无法利用索引进行加速，因为最左前缀不匹配。

需要注意的是，最左前缀匹配原则并不意味着只有最左侧的列可以使用索引，而是强调索引的有序性和连续性。在某些情况下，可以通过创建单列索引或调整索引顺序来更好地利用索引。

原创作者: ratelcloud 转载于: https://blog.51cto.com/ratelcloud/11852119