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

目录

MySQL索引常见概念
MySQL索引结构
一、磁盘I/O效率优化
二、范围查询与有序性支持
三、数据维护的稳定性
四、对比其他结构的局限性
1. B树的不足：
2. 哈希索引的缺陷：
3. R树与全文索引的场景限制：
五、实际业务场景适配性
总结

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

提示

mysql索引结构为什么要用B+树而不是用B树或者hash结构？ MySQL选择B+树作为主要索引结构，而非B树或哈希结构，主要基于以下核心原因，结合搜索结果分析如下：

一、磁盘I/O效率优化

B+树的设计充分适配磁盘存储特性，显著降低I/O开销：

  1. 节点存储密度高：
    • B+树的非叶子节点仅存储键值和指针，不包含实际数据，相比B树（非叶子节点存储数据）能容纳更多键值。例如，4KB的磁盘页中，B+树节点可存储数百个键值，而B树可能仅存储几十个键值，导致B+树高度更低（通常3-4层即可管理上亿数据），减少查询时的磁盘访问次数。
  2. 批量读取优化：
    • 每次磁盘IO读取一个节点页时，B+树能一次性加载大量键值到内存，利用磁盘顺序读特性（顺序读速度远高于随机读），而哈希结构需随机访问磁盘块，效率低下。

二、范围查询与有序性支持

B+树的底层结构天然支持高效范围查询和排序操作：

  1. 叶子节点链表化：
    • 所有叶子节点通过指针串联成有序链表，执行如WHERE age BETWEEN 20 AND 30时，只需定位起始节点并顺序扫描后续链表，无需反复回溯树结构。而B树需多次跨层遍历，哈希结构则完全不支持范围查询。
  2. 排序与分页优化：
    • 基于叶子节点的有序链表，B+树可直接利用顺序扫描实现ORDER BY和LIMIT操作，避免额外排序开销。

三、数据维护的稳定性

B+树在频繁增删改场景下具备更强的平衡性与维护效率：

  1. 分裂与合并代价低：
    • 数据仅存储在叶子节点，插入/删除操作仅影响局部节点，且B+树通过节点分裂和合并保持平衡，而B树的非叶子节点修改可能触发多级结构调整。
  2. 事务与锁粒度优化：
    • B+树的叶子节点独立存储数据，更新操作仅需锁定最小单元（如单个叶子节点），而B树的非叶子节点修改可能涉及全局锁，影响并发性能。

四、对比其他结构的局限性

1. B树的不足：

  • 数据分散存储：B树的非叶子节点和叶子节点均存储数据，导致热点数据可能集中在高层节点，范围查询时需多次跨层访问，效率较低。
  • 高度较高：非叶子节点存储数据占用空间大，相同数据量下B树高度高于B+树，增加I/O次数。

2. 哈希索引的缺陷：

  • 不支持范围查询：哈希表通过哈希函数直接定位数据，仅适用于等值查询（如WHERE id=5），无法支持BETWEEN或ORDER BY。
  • 哈希冲突：冲突处理（如链表法）会引入额外开销，且动态扩容成本高。
  • 无法利用局部性原理：哈希结构的随机分布特性导致顺序扫描时磁盘IO效率极低。

3. R树与全文索引的场景限制：

  • R树专为多维数据（如地理坐标）设计，但维护复杂度高；全文索引基于倒排索引，仅适用于文本模糊匹配，均无法替代B+树的通用性。

五、实际业务场景适配性

MySQL作为通用关系型数据库，需兼顾OLTP与OLAP场景：

  • OLTP高频等值查询：B+树的单值查询效率接近哈希（通过自平衡树结构实现O(log N)复杂度），且支持唯一索引约束。
  • OLAP复杂分析查询：范围扫描、聚合统计等操作依赖B+树的有序链表特性，如统计某时间段订单量。
  • 混合负载场景：B+树在读写均衡性上表现优异，而哈希结构在写密集场景下易因冲突退化性能。

总结

B+树通过低高度结构、有序链表和分离索引与数据的设计，完美平衡了磁盘I/O效率、查询多样性与维护成本，成为关系型数据库索引的黄金标准。尽管新兴技术如FPGA加速B+树（HyperTree）进一步提升并发性能，但其核心结构仍依赖B+树的底层逻辑。对于开发者而言，理解B+树特性有助于优化索引设计（如组合索引顺序、覆盖索引应用），充分发挥MySQL性能潜力。