索引相关

概述

1. 索引是数据库管理系统中的一种数据结构，用于快速查找和检索表中的记录
2. 通过在列或列的组合上创建索引，可以显著提高查询性能

原理

1. 在InnoDB中，表的数据存储在表空间（tablespace）中，表空间由多个页（pages）组成，
   1. 每个页通常为16KB
   2. 每个页包含多个行
   3. 行数据包括表的各列数据
   4. 索引通过指向页和行偏移的指针来快速查找数据
2. 创建索引过程：
   1. 当您创建一个索引时，首先需要指定要在哪个表的哪一列上创建索引
      这一步骤将索引与目标表的指定列关联起来
   2. 数据库会扫描目标列中的所有值，并构建一个索引数据结构（如B+树）
   3. 在B+树的叶子节点上，存储索引键（即目标列的值）和指向实际数据行的指针
      每个叶子节点存储一个索引键值（例如，emp_name列的值）以及一个指向数据行的指针
      指针通常包括页编号和页内偏移，指示数据行在表空间中的确切位置

索引类型

1. 单列索引（Single-Column Index）：基于单个列创建的索引

   1. CREATE INDEX idx_column1 ON table_name(column1);

2. 多列索引（Multi-Column Index）：基于多个列创建的索引，也称为组合索引（Composite Index）

   1. CREATE INDEX idx_columns ON table_name(column1, column2);

3. 唯一索引（Unique Index）：保证索引列中的值是唯一的，不允许重复

   1. CREATE UNIQUE INDEX idx_unique_column1 ON table_name(column1);

4. 全文索引（Full-Text Index）：用于对文本数据进行全文搜索（仅限于CHAR、VARCHAR和TEXT列）

   1. CREATE FULLTEXT INDEX idx_fulltext_column1 ON table_name(column1);

5. 空间索引（Spatial Index）：用于地理数据的快速查询（如GEOMETRY类型）

   1. CREATE SPATIAL INDEX idx_spatial_column1 ON table_name(geometry_column);

数据结构

1. B树索引（B-Tree Index）
   1. 最常见的索引类型，适用于大多数查询
   2. B树是一种自平衡树结构，支持快速的查找、插入和删除操作
2. 哈希索引（Hash Index）
   1. 使用哈希表实现，适用于等值查询（=）
   2. 不适用于范围查询（<, >, BETWEEN）
3. 全文索引（Full-Text Index）
   1. 使用倒排索引结构，适用于全文搜索
4. 空间索引（Spatial Index）
   1. 使用R树或其他空间数据结构，适用于地理空间数据

优点

1. 提高查询速度：索引可以显著提高SELECT语句的查询速度
2. 加速排序和分组操作：索引可以加速ORDER BY、GROUP BY、DISTINCT等操作
3. 加速连接操作：索引可以加速JOIN操作，特别是在大表之间的连接

缺点

1. 增加存储空间：索引需要额外的存储空间来存储索引结构
2. 降低写操作性能：每次插入、更新或删除数据时，索引也需要相应地更新，这会降低写操作的性能

操作

在表创建时创建索引

在表创建后添加索引

删除索引

删除唯一索引

使用索引查询

1. 数据库管理系统会自动使用索引来优化查询
   1. 你不需要在查询中显式地指定使用哪个索引
   2. 数据库查询优化器会根据查询条件自动选择最优的索引

适用场景

1. 选择性高的列上创建索引
   1. 选择性高的列（不同值多的列）上创建索引可以显著提高查询效率
2. 避免在频繁更新的列上创建索引
   1. 在频繁更新的列上创建索引可能会降低写操作的性能
3. 合理使用组合索引
   1. 在多个列上进行查询时，组合索引可以显著提高查询效率
4. 监控和优化索引
   1. 定期使用数据库提供的工具（如EXPLAIN语句）来监控和优化索引