MYSQL 的索引类型

黎 浩然/ 7 7 月, 2022/ 数据库/DATABASE, 计算机/COMPUTER/ 0 comments

MySQL提供了多种索引类型，每种索引都有其特定的应用场景和实现原理。这些索引类型包括B树索引、哈希索引、全文索引和R树索引。下面我将详细介绍这四种索引类型，包括它们的工作原理和一些简单的SQL代码示例：

B树索引

原理

• B树索引（B-Tree Index）是最常用的索引类型，它适用于全键值、键值范围和键值排序的搜索。
• B树索引以树状结构存储数据，每个节点都包含键值和指向数据记录的指针。这种结构支持高效的数据查找和排序。

示例代码

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

-- 创建B树索引
CREATE INDEX idx_name ON employees (name);

-- 使用索引进行查询
SELECT * FROM employees WHERE name = 'John Doe';

哈希索引

原理

• 哈希索引（Hash Index）基于哈希表实现，非常适合等值查询。
• 哈希索引将键值通过哈希函数转换成哈希码，然后直接访问对应的数据位置。哈希索引不支持范围查询。

示例代码

-- 假设我们使用的是MEMORY表，支持哈希索引
CREATE TABLE temp_data (
    key_col INT,
    value_col VARCHAR(100),
    INDEX USING HASH (key_col)
);

-- 使用索引进行查询
SELECT * FROM temp_data WHERE key_col = 10;

全文索引

原理

• 全文索引（Fulltext Index）专门用于文本数据，支持复杂的文本搜索。
• 使用倒排索引结构，其中索引存储了文本中每个词的位置信息，允许进行快速的文本搜索。 倒排索引（Inverted Index）是一种数据结构，用于支持快速的全文搜索，广泛应用于搜索引擎和数据库管理系统中。这种索引结构与传统的正向索引相反，正向索引是按文档来组织数据，映射文档到其包含的关键词；而倒排索引则是按关键词来组织数据，映射关键词到包含这些关键词的文档列表。倒排索引的组成倒排索引通常由两个主要部分组成：
  1. 词汇表（Term Dictionary）：
     • 这是一个关键词的集合，每个关键词在索引中唯一存在。
     • 词汇表通常还会存储一些关键词的附加信息，如词频（在所有文档中出现的次数）。
  2. 倒排列表（Posting List）：
     • 对于词汇表中的每一个关键词，都有一个对应的倒排列表，也称为倒排记录表。
     • 每个倒排列表包含所有包含该关键词的文档的引用（如文档ID）。这些引用可能还包括关键词在每个文档中的位置信息、在文档中的频率等，用以支持更复杂的搜索需求，如短语搜索、近邻搜索等。
  倒排索引的工作原理当进行搜索查询时，搜索引擎或数据库系统会：
  • 分析查询中的关键词。
  • 利用词汇表快速定位每个关键词的倒排列表。
  • 从倒排列表中检索所有包含这些关键词的文档。
  • 根据需要的排序和相关性算法，如TF-IDF（Term Frequency-Inverse Document Frequency）或其他排序逻辑，处理和返回结果。
  优点和应用
  • 快速检索：倒排索引可以极大地加速关键词的搜索过程，因为它直接指向包含关键词的所有文档。
  • 空间效率：尽管倒排索引可能需要额外空间来存储关键词和其文档列表，但它通过压缩技术可以非常空间效率。
  • 灵活性高：倒排索引支持复杂的查询，包括布尔查询、短语查询和近邻查询。

示例代码

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title, body)
);

-- 使用全文索引进行搜索
SELECT * FROM articles WHERE MATCH (title, body) AGAINST ('MySQL indexing' IN NATURAL LANGUAGE MODE);

R树索引

原理

• R树索引（R-Tree Index）是一种专门为地理空间数据设计的树形结构，适用于空间数据查询。
• R树通过存储最小边界矩形（MBR）来索引空间对象，支持高效的空间位置和范围查询。

示例代码

CREATE TABLE geom_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    geom GEOMETRY NOT NULL,
    SPATIAL INDEX (geom)
);

-- 插入地理空间数据
INSERT INTO geom_data (geom) VALUES (ST_GeomFromText('POINT(15 20)'));

-- 使用R树索引进行空间查询
SELECT * FROM geom_data WHERE MBRContains(geom, ST_GeomFromText('POINT(15 20)'));