数据库 概念和索引总结

目录

基础概念

数据库定义及其关系

数据库存在原因

SQL和MySQL

数据库三大范式

数据库连接泄漏

触发器 

索引

含义

索引的优缺点

索引的数据结构

B+树和hash索引

使用B+树的好处

hash索引和B+树的区别

前缀索引

什么是前缀索引

什么是最左前缀匹配原则

添加索引的原则

---

基础概念

数据库定义及其关系

数据库定义及其功能

• 数据库是有组织的、结构化的数据集合，用于存储、管理和检索数据
• 主要功能：数据存储、数据管理、数据检索、安全性保障以及并发控制

关系数据库和非关系型数据库（数据库的两种基本类型）

• 关系型数据库使用表格结构来存储数据，通过SQL语言进行对应操作，常用的用MySQL
• 非关系数据库，则主要适用于处理非结构化或半结构化的数据，例如Redis
• 两者主要区别：数据存储方式、查询语言、拓展性以及数据处理类型

数据库管理系统DBMS

• 作用：用于创建和管理数据库的软件系统，提供用户与数据库交互的接口
• 常见的有MySQL、Oracle等 

数据库设计中的规范化

• 规范化是数据库设计中用于减少数据冗余和提高数据一致性的过程。其通过分解表格，将数据组织到不同的表中，确保数据的结构化和一致性 

数据库存在原因

高效数据管理和检索

• 数据库可以高效的存储和组织大量数据，使得数据增删查改快速且可靠
• 数据库相比于简单文件系统的优势
  • 数据库提供高效的查询能力、数据一致性保障以及并发处理支持，这些都是文件系统难以支持
  • 数据库可以通过索引、视图、存储过程等功能来优化数据操作和管理

数据一致性和完整性

• 通过事务管理系统，确保多个并发操作的时候，数据保持一致性和完整性
• 如何保证数据一致性和完整性
  • 通过事务，数据库可以确保一组操作要么全部完成，要么全部回滚，保证了数据的一致性
  • 通过外键约束、触发器等功能维护数据的完整性 

并发处理和多用户支持

• 数据库可以同时支持多个用户并发访问，通过锁机制和事务管理，确保数据的正确性和稳定性
• 数据库如何处理多用户并发操作
  • 数据库使用锁（行锁或者表锁）和事务隔离级别来管理并发操作，防止脏读、幻读和不可重复读的问题
  • 常用隔离级别有读未提交、读已提交、可重复读和串行化

数据安全和访问控制

• 数据库提供了多层次的安全机制，例如用户认证、权限控制和数据加密，确保数据在存储和传输过程中的安全性
• 如何确保数据库安全
  • 通过用户角色管理、权限分配、数据加密、审计日志等方法，可以有效控制访问权限，防止未授权的访问
  • 数据库系统还可以通过备份恢复机制来防范数据丢失 

数据备份和恢复

• 数据库系统支持自动和手动的数据备份和恢复机制，防止系统崩溃、数据丢失等突发情况，从而保证数据的长期可用性
• 如何实现数据备份和恢复
  • 数据库通常提供全量备份、增量备份和日志备份方式，可以定期备份数据并在出现故障的时候进行回复，从而确保数据的完整性和连续性

支持复杂数据关系和查询

• 数据库可以建立复杂的数据关系，同时支持复杂的数据查询
• 什么情况下需要使用关系型数据库而不是简单的数据存储
  • 数据之间存在复杂关系，并且需要进行多表连接、事务处理或者复杂查询的时候，关系数据库是最佳选择
  • 其可以维护数据一致性和完整性，同时支持复杂的查询请求 

SQL和MySQL

 两者主要区别

• SQL是一种查询语言，用于操作关系型数据库；MySQL是一个具体关系型数据库关系系统，使用SQL来管理数据
• MySQL大部分是遵循SQL标准，但是其中也包含一些特有的拓展和功能

数据库三大范式

第一范式

• 含义：表中每个字段都是不可分割的原子值，也就是数据项必须是最小单元，不可以再分解
• 第一范式中，每一列的数据都应该是原子的，不应该包含多值。例如在一个表中只有一个电话字段，里面不可以同时存放多个电话号码，应该将它们分成多个记录
• 总结（第一范式是什么，为什么遵循）
  • 第一范式要求每个字段都应该是不可再分的原子值，从而确保数据的完整性和一致性，避免不必要的复杂性和数据冗余

第二范式

• 含义：满足第一范式的基础上，要求每个非主键字段完全以来主键，而不能依赖主键的一部分。一个表必须值描述一个实体或者概念
• 如果理解第二范式
  • 第二范式要求表中的每一个非主键字段都应完全依赖于主键，而不是仅仅依赖于主键的一部分，有助于消除冗余数据，确保数据的完整性 

第三范式

• 含义：满足第二范式下，要求非主键字段不能够互相以来，即每个非主键字段只可以以来于主键
• 第三范式消除了传递依赖，确保每个非主键字段都直接依赖于主键，而不是通过其他非主键字段间依赖于主键，进一步减少了数据冗余
• 第三范式的定义以及如何改进数据库设计
  • 第三范式要求非主键字段之间不能够相互依赖，也就是没有传递以来，从而使得数据结构更加清晰，减少冗余，提高了数据库的维护性和可拓展性 

数据库连接泄漏

含义

• 应用程序中，数据库连接在打开之后未能够被正确关闭和释放，导致这些连接被长期占用，从而无法被其他请求或者进程调用
• 从而导致数据库连接池中的连接资源耗尽，进而影响应用程序的性能，甚至导致系统崩溃

连接泄漏的表现

• 连接耗尽：数据库连接池中的连接被逐渐耗尽，新数据库请求无法获取可用连接，从而导致请求被阻塞或者超时
• 内存占用增加：未释放的数据库连接占用系统资源，从而导致系统资源耗尽
• 系统不稳定：长时间存在连接泄漏会导致应用程序不稳定，甚至出现系统崩溃的情况

连接泄漏的原因

• 未关闭连接：程序在使用完数据库连接后，没有显式的关闭连接，导致连接没能及时释放
• 异常处理不当：数据库操作中，如果抛出异常，链接可能无法执行关闭操作，从而导致连接泄漏
• 忘记释放资源
• 连接池配置错误导致连接泄漏 

重要问题总结

• 什么是数据库泄漏？其会导致什么问题
  • 数据库连接泄漏是指数据库连接，在使用后未能够正确关闭或者释放，导致连接资源被长期占用，可能会导致连接池资源耗尽，影响应用性能，甚至导致系统崩溃
• 如果防止数据库连接泄漏
  • 每次数据库操作后需要正确关闭和释放连接，使用try - finally语句
  • 使用连接池管理哦工具并设置合理的配置来防止连接泄漏
• 如何检测和修复数据库连接泄漏
  • 通过监控工具检测数据库连接的使用情况，分析连接池的日志和监控数据来发现泄漏问题 

触发器 

含义

• 特殊存储过程，在特定的数据库事件发生的时候，自动触发并执行定义的操作；触发其通常是用于数据表发生变化的时候，执行数据效验、日志记录等操作，确保数据的一致性和完整性
• 特点
  • 自动执行：指定事件发生的时候，它会自动执行
  • 表关联：触发器通常和一个或者多个表关联，当表中的数据发生变化时自动触发执行
  • 触发事件多样
• 作用
  • 数据验证和完整性的维护，在数据插入或者更新的时候，自动验证数据是否符合条件，防止不合规则的数据进入数据库
  • 自动维护日志，数据表发生变化的时候，自动记录变更历史和日志
  • 自动更新关联表，主表数据变化的时候，自动更新关联表的数据
• 触发器分类
  • 按照时间分类
    • DEFORF触发器：数据操作之前执行，用于实际操作之前进行检查或者查找工作
    • AFTER触发器：数据操作完成后执行，用于操作之后进行进一步处理
  • 触发事件
    • INSERT触发器
    • UPDATE触发器
    • DELETE触发器

重要问题总结

• 什么是触发器？它的作用是什么？

  • 触发器是数据库中一种特殊的存储过程，在特定事件发生时自动执行。它用于维护数据的一致性、执行复杂的业务逻辑、记录日志等。

• 请举一个触发器的使用场景。

  • 可以提到在订单系统中，当插入新订单时，触发器自动更新库存表中的商品库存数量，确保库存数据的准确性。

• BEFORE 触发器和 AFTER 触发器有什么区别？

  • BEFORE 触发器在数据操作之前执行，通常用于数据验证或准备；AFTER 触发器在数据操作之后执行，常用于记录日志或更新关联表。

• 触发器与存储过程的区别是什么？

  • 触发器是自动执行的，当特定事件发生时触发；存储过程需要手动调用，可以在程序或SQL语句中执行。触发器通常用于响应数据表的变化，而存储过程用于实现可重复使用的业务逻辑。

索引

含义

索引的优缺点

索引优点

• 加快数据检索速度：索引通过建立排序结构，使得数据库可以快速定位所需的数据，大大减少了查询的时间。对于大数据量的表，索引能够显著提高查询性能，尤其是在条件查询和排序操作中
  • 为什么使用索引可以提高查询速度？
    • 索引类似于书本的目录，通过索引，数据库可以直接跳转到目标数据，而不必逐一扫描每一行，从而提高查询效率
• 提高排序和分组操作的效率：索引可以提高 ORDER BY 和 GROUP BY 操作的性能，因为数据在索引中已经是排序的，可以直接使用索引进行排序和分组
  • 索引如何优化排序操作？
    • 当数据已经按索引排序时，数据库可以直接利用索引完成排序操作，而不必对查询结果再进行排序
• 辅助约束的实现：索引可以帮助快速验证数据的唯一性（如主键、唯一键），并加速外键的检查和约束的实施
  • 索引在约束实现中有什么作用？
    • 索引可以快速定位记录，从而有效地验证唯一性和外键约束，确保数据完整性

 索引缺点

• 增加存储空间开销：每个索引都需要额外的存储空间，尤其是对大表或多个列建立索引时，索引的存储空间需求可能非常大，导致存储成本增加
  • 索引对存储空间有什么影响？
    • 索引需要额外的磁盘空间来存储索引结构，特别是在多个列或多个索引的情况下，可能会显著增加数据库的存储开销
• 降低数据写入速度：在插入、更新或删除数据时，数据库不仅需要修改数据，还需要更新相关的索引结构。因此，索引会降低写入操作的性能，尤其是在频繁写操作的场景中
  • 为什么索引会降低写操作的性能？
    • 当表中的数据发生变化时，索引也必须同步更新，这增加了额外的计算和 I/O 操作，从而降低了写入速度。
• 维护复杂性：随着数据的增长和变化，索引可能需要重建或维护，这增加了数据库管理员的工作量。此外，不适当的索引策略可能导致查询性能的下降
  • 如何管理和维护索引？
    • 索引需要定期检查和维护，例如重建或重新组织索引，以确保它们保持高效。此外，应该定期评估和优化索引策略，避免不必要的索引造成资源浪费
• 可能导致查询优化器选择不佳：如果存在大量索引，查询优化器可能选择次优的索引，导致查询性能下降。优化器需要在多个可能的索引之间进行选择，这有时会导致非最佳的查询计划
  • 索引是否总是会提高查询性能？
    • 不一定。如果索引过多，查询优化器可能会选择次优的索引，反而可能降低查询性能。合理设计和使用索引至关重要

索引的数据结构

B-树（B-Tree）

• 结构：B-树是一种自平衡的多叉树数据结构，其中每个节点可以包含多个子节点。B-树确保所有叶子节点处于同一层级，并且支持高效的顺序访问和快速的插入、删除操作
• 特点：
  • 多用于文件系统和数据库索引。
  • 适合范围查询（如 BETWEEN 和 >）。
  • 平衡性保证了查找、插入、删除操作的时间复杂度为 O(log n)。
• 为什么数据库索引常用 B-树？
  • B-树能够保持平衡，确保快速的查询和更新操作，适合范围查询，同时可以高效管理大量数据

B+树（B+Tree）

• 结构：B+树是 B-树的一种变体，所有的数据都存储在叶子节点中，而非叶子节点仅存储键值和指向子节点的指针。叶子节点通过链表相连，便于顺序访问
• 特点：
  • 常用于数据库和文件系统的索引结构。
  • 支持高效的顺序和范围查询。
  • 叶子节点通过链表连接，提高了顺序扫描的效率。
  • B+树中，非叶子节点仅存储键，空间利用率较高
• B+树相比 B-树有什么优势？
  • B+树将所有数据存储在叶子节点，叶子节点间通过链表连接，顺序访问和范围查询更加高效。非叶子节点只存储键值，空间利用率更高

 哈希表（Hash Table）

• 结构：哈希表通过哈希函数将键值映射到哈希表中的特定位置，从而实现快速的查找。哈希表适用于等值查询（如 =）。
• 特点：
  • 查找、插入和删除操作的时间复杂度为 O(1)，非常高效。
  • 不适合范围查询（如 BETWEEN 或 >），因为哈希表中的数据没有顺序。
  • 处理冲突的方法包括链地址法、开放地址法等
• 哈希索引的优缺点是什么？
• 哈希索引对等值查询非常高效，时间复杂度为 O(1)。但它不适用于范围查询，也不能排序，索引的灵活性较差

 倒排索引（Inverted Index）

• 结构：倒排索引通常用于全文搜索引擎中，它将文档中的每个词与包含该词的文档列表相关联，方便快速查找。
• 特点：
  • 适合全文检索场景，比如搜索引擎。
  • 支持高效的关键词查询。
  • 在处理大文本数据时非常有效，但构建和维护成本较高。
• 倒排索引通常用于什么场景？
  • 回答建议：倒排索引广泛用于搜索引擎和全文检索系统，能够快速查找包含特定关键词的文档或记录

B+树和hash索引

使用B+树的好处

优势总结

• 高效的范围查询和顺序访问：

  • 原因：B+ 树的叶子节点按顺序存储数据，并通过链表相连。这种结构使得 B+ 树在处理范围查询（如 BETWEEN 和 >）和顺序扫描时非常高效。
  • 好处：能够快速地检索连续范围内的数据，如查找某个区间内的记录，或对结果集进行排序。

•  更高的磁盘读写效率：

  • 原因：B+ 树的非叶子节点只存储键值和指向子节点的指针，而所有数据都存储在叶子节点中。这种设计使得每个节点可以包含更多的键值，从而减少了树的高度。
  • 好处：树的高度降低，意味着在检索数据时，磁盘 I/O 操作次数减少，从而提高了查询性能，特别是在处理大规模数据时表现优异。

• 稳定的查询性能：

  • 原因：由于 B+ 树的所有数据都存储在叶子节点中，并且所有叶子节点在同一层级，查找任何数据所需的路径长度相同。
  • 好处：这种一致性使得查询操作的时间复杂度稳定为 O(log n)，无论数据量如何增长，查询性能都相对稳定。

• 更高的空间利用率：

  • 原因：B+ 树的非叶子节点不存储实际数据，仅存储键值和指针，因此一个节点可以存储更多的键值。相比 B-树，B+ 树的非叶子节点占用的空间更小。
  • 好处：更高的空间利用率意味着在同样的内存或磁盘空间下，B+ 树可以管理更多的索引，这对于大型数据库尤为重要。

• 便于顺序访问：

  • 原因：由于叶子节点通过链表相连，B+ 树支持对所有数据进行顺序遍历。这种特性在需要对数据进行排序或执行范围查询时尤为有用。
  • 好处：数据库可以轻松实现 ORDER BY 或 GROUP BY 等操作，不需要额外的排序过程。

•  方便的增删操作：

  • 原因：B+ 树在插入和删除数据时，会自动调整树的结构以保持平衡，并将数据的插入和删除操作限制在叶子节点，这使得更新操作相对简单且高效。
  • 好处：插入和删除操作对树的结构影响较小，调整过程高效，能够迅速恢复平衡，保证树的高度不至于显著增加

hash索引和B+树的区别

区别总结

• 数据结构：

  • Hash 索引：
    • 使用哈希表作为底层数据结构。哈希表通过哈希函数将键值映射到特定的哈希桶中，从而实现快速查找。
  • B+ 树索引：
    • 使用 B+ 树（多叉平衡树）作为底层数据结构。B+ 树的所有叶子节点存储数据，并按顺序排列。非叶子节点仅存储键值和指向子节点的指针。

• 查询效率：

  • Hash 索引：
    • 哈希索引对等值查询非常高效，查找时间复杂度为 O(1)。但它不适合范围查询，因为哈希表中的数据是无序的。
  • B+ 树索引：
    • B+ 树索引适用于等值查询和范围查询，查找时间复杂度为 O(log n)。由于数据在叶子节点中按顺序存储，B+ 树对范围查询（如 BETWEEN、> 等）非常高效。

• 适用场景：

  • Hash 索引：
    • 适合等值查询（如 =），例如通过主键或唯一键查找记录。由于哈希表中的数据没有顺序，Hash 索引不适用于排序操作或范围查询。
  • B+ 树索引：
    • 适用于等值查询和范围查询，特别是在需要对结果集进行排序或按某个范围进行检索时。B+ 树在 ORDER BY、GROUP BY、BETWEEN 等操作中表现优异。

• 插入和删除效率：

  • Hash 索引：
    • 哈希索引的插入和删除操作通常比较简单且高效。但在哈希冲突频繁的情况下，处理冲突（如链地址法）可能导致性能下降。
  • B+ 树索引：
    • B+ 树索引在插入和删除操作时需要维护树的平衡，可能会导致节点分裂或合并，因此效率略低于哈希索引，但依然在 O(log n) 的时间复杂度内。

• 磁盘空间和内存占用：

  • Hash 索引：
    • 哈希索引通常只需存储哈希表和指向数据记录的指针，内存和磁盘占用相对较小。但在冲突多的情况下，可能需要更多的内存来存储链表或其他冲突处理结构。
  • B+ 树索引：
    • B+ 树索引需要额外的空间来存储树的结构（节点、指针等），相对占用的磁盘和内存空间较大，但在大多数情况下，B+ 树的结构设计能有效利用空间。

• 冲突处理：

  • Hash 索引：
    • 哈希索引可能会出现哈希冲突（多个键值映射到同一个哈希桶），需要使用冲突解决算法（如链地址法或开放地址法），这可能会影响查找效率。
  • B+ 树索引：
    • B+ 树由于其有序结构，不存在哈希冲突问题，数据查找更为稳定。

• 排序能力：

  • Hash 索引：
    • 不具备排序能力，因为哈希表中的数据是无序的。使用哈希索引进行排序查询（如 ORDER BY）效率较低。
  • B+ 树索引：
    • 由于数据在叶子节点中按顺序排列，B+ 树天然支持排序操作，ORDER BY 和 GROUP BY 查询效率较高。

前缀索引

什么是前缀索引

对字符串类型的数据进行部分索引的一种技术，也就是值索引字段的前N个字符，而不是整个字段，目的就是优化存储空间和提高查询性能，尤其是索引较长的字符传的时候。

优点总结

• 减少索引大小：

  • 原因：对字符串的前 N 个字符进行索引，可以显著减少索引的大小，因为索引结构只存储前 N 个字符的信息。
  • 好处：在存储大表时，能够节省磁盘空间和内存空间，同时还可以提高索引的创建和维护效率。

• 提升查询性能：

  • 原因：较小的索引可以加快查询操作，尤其是在需要扫描索引时，较小的索引大小意味着更少的 I/O 操作。
  • 好处：尽管前缀索引不如完整索引精确，但在许多情况下，它可以提供足够好的性能，同时显著减少资源占用。

• 适用场景：

  • 原因：前缀索引适用于列的前几个字符可以有效区分不同数据的场景。例如，URL、电子邮件地址、或者文件路径的前几字符通常已经足够区分大部分记录。
  • 好处：在处理长字符串列时，前缀索引可以避免对整个字符串进行索引，从而节省资源。

缺点总结

• 准确性降低：

  • 原因：由于前缀索引只索引字符串的部分内容，因此可能会出现索引冲突（不同的完整值有相同的前缀），从而影响查询的精确性。
  • 影响：如果两个或多个记录的前 N 个字符相同，前缀索引可能无法唯一标识这些记录，需要进一步通过回表（读取原始表）进行筛选，可能会降低查询效率。

• 不适用于所有查询：

  • 原因：前缀索引主要适用于等值查询，而不适用于范围查询（如 LIKE '%abc'）或完整匹配查询。
  • 影响：在需要完整匹配或复杂查询时，前缀索引的效果不如完整索引

部分问题总结

• 什么是前缀索引，为什么使用它？

  • 回答建议：前缀索引是一种只索引字符串前 N 个字符的索引技术，主要用于减少索引大小和提高查询性能，特别是在处理长字符串时。

• 前缀索引有什么局限性？

  • 回答建议：前缀索引可能降低查询的精确性，因为它只索引部分字符串，可能出现不同记录具有相同前缀的情况。此外，前缀索引不适用于范围查询或完整匹配查询。

• 在什么情况下适合使用前缀索引？

  • 回答建议：前缀索引适用于列的前几个字符可以有效区分数据的场景，例如 URL、电子邮件地址、文件路径等长字符串列。在这种情况下，前缀索引可以有效节省存储空间并提高查询性能

什么是最左前缀匹配原则

查询优化机制首先使用索引的最左边的列进行查询，然后依次向右匹配更多的列，直到查询条件不再满足索引顺序的时候

含义理解

• 频繁查询字段：对经常出现在查询条件中的字段添加索引，以提高检索速度。

• 高选择性字段：优先索引选择性高的字段，以更有效地过滤数据。

• 组合索引：针对常用组合查询建立组合索引，按最左前缀匹配原则设计。

• 避免频繁更新字段：对频繁更新的字段慎用索引，以免影响写性能。

• 避免冗余索引：减少重复索引，节省资源并优化性能。

• 覆盖索引：设计包含查询所需字段的索引，避免回表，提高效率。

• 监控与调整：定期分析查询性能，适时优化索引结构。

添加索引的原则

• 频繁查询字段加索引：加速常用查询条件的检索。
• 高选择性字段优先：提高索引过滤效果。
• 组合查询用组合索引：优化多字段联合查询。
• 避免频繁更新字段的索引：防止影响写性能。
• 避免冗余索引：节省资源，提升效率。
• 用覆盖索引提高效率：减少回表操作，直接获取数据。
• 定期优化索引结构：根据查询需求调整索引