学习笔记
Mysql索引详记
2023-09-16
Mysql
0

目录

MySQL索引常见概念
MySQL索引结构

MySQL索引常见概念

MySQL索引是一种用于提高数据库查询性能的重要工具。索引是一种数据结构，它们可以帮助数据库系统快速地定位和访问存储在表中的数据行。在使用索引时，需要注意一些重要的方面，下面是关于MySQL索引的详细讲解：

  1. 索引是什么：

    • 索引是一种数据结构，类似于书中的目录，它提供了一种快速查找数据的方式，而不必扫描整个表。

  2. 索引的类型：

    • MySQL支持多种类型的索引，其中最常见的是B树索引（B-tree index）。其他类型包括哈希索引（Hash index）和全文索引（Full-Text index）等。B树索引是最常用的索引类型，它适用于各种查询条件。

  3. 索引的创建：

    • 在创建表时，可以为一个或多个列创建索引。使用CREATE INDEX语句或在CREATE TABLE语句中的INDEX子句来定义索引。例如： 
      sql
      CREATE INDEX index_name ON table_name (column1, column2);

  4. 主键索引：

    • 主键索引是一种特殊的索引，用于唯一标识表中的每一行。每个表只能有一个主键索引，通常是表的主键列。主键索引不允许有重复值或NULL值。

  5. 唯一索引：

    • 唯一索引确保索引列的值在整个表中是唯一的，但允许NULL值。一个表可以有多个唯一索引。

  6. 普通索引：

    • 普通索引（或非唯一索引）没有唯一性要求，允许重复值和NULL值。

  7. 复合索引：

    • 复合索引是基于多个列的索引，它可以提高查询效率，特别是在多条件查询时。但需要注意，复合索引的顺序很重要，因为它影响了查询的效率。

  8. 聚簇索引（Clustered Index）：

    • 聚簇索引是一种特殊的索引，它决定了表中数据行的物理存储顺序。每个表只能有一个聚簇索引，通常是主键索引，或者如果没有主键，则是第一个UNIQUE索引。
    • 当你在查询表时，聚簇索引决定了数据行的物理存储顺序，因此，它通常用于根据主键来快速查找和排序数据。
    • 由于数据行的物理顺序与聚簇索引一致，因此对于按照聚簇索引的顺序进行范围查询非常高效。

  9. 非聚簇索引（Non-Clustered Index）：

    • 非聚簇索引是通常所说的普通索引，它们不决定数据行的物理存储顺序。一个表可以有多个非聚簇索引。
    • 非聚簇索引存储了索引列的值和指向相应数据行的指针。当你在查询时使用非聚簇索引，数据库引擎首先使用索引快速定位到数据行，然后再根据指针获取实际数据。
    • 非聚簇索引通常用于查询条件不是主键或聚簇索引的情况，或者用于加速排序和连接操作。

  10. 覆盖索引（Covering Index）：

  • 覆盖索引是一种特殊类型的非聚簇索引，它包含了在查询中所需的所有列，而不仅仅是索引列。
  • 当你查询时使用覆盖索引，数据库引擎可以直接从索引中获取数据，而不需要回表到实际数据行，这可以显著提高查询性能。
  • 覆盖索引对于选择性较高的查询非常有用，因为它可以减少磁盘I/O和内存开销。
  1. 回表（Index Seek vs. Table Lookup）：
  • 在查询中，回表是指数据库引擎在使用非聚簇索引时需要访问实际数据行的操作。这种操作可以显著影响查询性能，因为它需要额外的I/O操作和内存消耗。
  • 如果查询只使用索引列并且所有需要的数据都包含在索引中，就可以避免回表，这称为覆盖索引。
  • 当数据库引擎无法使用覆盖索引时，就需要回表，这可能会导致性能下降，特别是在大型表上。
  1. 索引的优点：
  • 提高查询性能：索引可以显著减少数据扫描的时间，从而加快查询速度。
  • 加速排序：当使用ORDER BY子句时，索引可以帮助避免全表扫描，提高排序性能。
  • 加速连接：在连接操作中，索引可以加速关联表的查找。
  1. 索引的缺点：
  • 占用存储空间：索引会占用额外的存储空间，特别是在大表上可能会占用大量空间。
  • 更新代价高：插入、更新或删除数据时，索引需要维护，这可能会导致性能下降。
  • 不适用于所有情况：并不是所有列都适合创建索引，需要根据查询模式来选择合适的索引。
  1. 优化索引：
    • 为了获得最佳性能，需要根据具体的查询需求来选择和设计索引。
    • 使用EXPLAIN语句来分析查询计划，了解MySQL是如何使用索引的。
    • 定期优化表，删除不再需要的索引，以减少维护成本和存储开销。

MySQL索引结构

Mysql的索引结构B+树（B+ Tree）是一种自平衡的树状数据结构，广泛用于数据库系统和文件系统中，旨在提供高效的数据插入、删除和查找操作。B+树的设计考虑了磁盘I/O和数据检索效率，使其成为管理大量有序数据的理想选择。

下面是有关B+树的详细讲解：

  1. 结构：

    • B+树是一个多叉树，每个节点可以包含多个子节点。它由以下类型的节点组成：
      • 根节点：树的顶层节点。
      • 内部节点：存储索引数据，通常包含键值和指向子节点的指针。
      • 叶子节点：存储实际数据条目，通常按键值顺序排列，并且相互连接形成一个有序链表。

  2. 特点：

    • 所有叶子节点都在同一层，这简化了范围查询。
    • 所有数据都存储在叶子节点，而内部节点仅用于索引。
    • 叶子节点之间通过指针连接成有序链表，这使得范围查询非常高效。
    • B+树是自平衡的，因此插入和删除操作的性能是可预测的。

  3. 插入操作：

    • 插入数据时，首先通过内部节点找到叶子节点，然后在叶子节点中插入数据。如果插入后叶子节点的数据条目数量超过了一个阈值，可以进行分裂操作，将部分数据移到新的叶子节点中，同时更新内部节点的索引。

  4. 删除操作：

    • 删除数据时，首先通过内部节点找到叶子节点，然后从叶子节点中删除数据。如果删除后叶子节点的数据条目数量低于一个阈值，可以进行合并操作，将数据合并到相邻的叶子节点中，并更新内部节点的索引。

  5. 查找操作：

    • 查找数据时，从根节点开始逐层搜索，直到找到叶子节点。然后，在叶子节点上进行二分查找或线性查找以找到目标数据。

  6. 范围查询：

    • 由于叶子节点之间通过指针连接成有序链表，B+树非常适合范围查询。可以从起始叶子节点开始，沿着指针链表遍历叶子节点，获取范围内的数据。

  7. 优点：

    • 高效的数据插入、删除和查找操作。
    • 对于范围查询非常高效，因为叶子节点之间有序连接。
    • 自平衡性质确保树的高度保持较低，保持了性能稳定性。

  8. 应用：

    • 数据库系统中常用于索引数据表。
    • 文件系统中用于存储文件和目录的元数据。

总之，B+树是一种高效的数据结构，特别适用于需要高效插入、删除和范围查询的场景。它在数据库系统和文件系统中发挥着关键作用，使得数据的管理和检索变得高效可靠。

总结：

  • 聚簇索引决定了数据行的物理存储顺序，通常是主键索引。
  • 非聚簇索引用于非主键列的查询，它们包含索引列的值和指向实际数据行的指针。
  • 覆盖索引包含了查询所需的所有列，可以减少回表操作，提高查询性能。
  • 回表是指在使用非聚簇索引时需要额外访问实际数据行的操作，可能影响性能。

相关信息

总之，MySQL索引是数据库性能优化的关键工具之一，但需要谨慎使用，根据具体情况选择合适的索引类型和列，并定期进行性能优化以确保数据库的高效运行。