4种常见的数据库索引

数据库索引是优化数据库系统性能的关键组成部分。如果没有有效的索引，查询可能会变得缓慢且低效，从而导致用户体验不佳并降低生产力。在这篇文章中，我们将探讨创建和使用数据库索引的一些适用场景。

B-Tree

B-Tree 是一种自平衡树的数据结构，可保持数据的排序并允许在对数时间复杂度内搜索数据、插入数据和删除数据。 B-Tree索引广泛应用于MySQL、PostgreSQL等关系数据库中。

B-Tree 针对范围查询进行了优化，由于记录在索引中按排序顺序存储，故而可以有效地查找某个范围内的所有记录。使用 =、>、>=、<、<= 或 BETWEEN 运算符的表达式中会充分发挥其性能。

在 B-Tree 索引中查询如下：

• 数据库从树的根部开始，将搜索关键字与当前节点的关键字的值进行比较。

• 如果相等，则数据库返回该记录。

• 否则，数据库根据比较结果确定下一步要搜索的子树。

例如，假设我们有一个具有以下架构的产品表：

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10,2)
);

使用以下命令在price列上创建 B-Tre索引：

CREATE INDEX products_price_index ON products (price);

哈希索引

哈希索引是另一种流行的索引算法，用于加速查询。哈希索引使用哈希函数将键映射到索引位置。此算法对于精确匹配查询最有用，例如根据主键值搜索特定记录。哈希索引通常用于内存数据库，例如 Redis。

哈希索引的工作原理是根据哈希值将表中的每条记录映射到唯一的存储桶。 哈希值是使用哈希函数计算的。

为了在哈希索引中查找记录，数据库首先计算搜索键的哈希值，然后查找相应的存储位置。 如果该记录在存储位置处，则数据库将返回该记录。否则，数据库执行全表扫描。

哈希索引的查找速度非常快，但它们不适用于数据范围查询。这是因为哈希函数不保留表中记录之间的任何顺序。

哈希索引查询如下：

假设我们有一个具有以下的产品表：

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10,2)
);

我们可以使用以下命创建哈希索引：

CREATE INDEX products_name_hash ON products (name);
SELECT * FROM products WHERE name = 'iPhone 13 Pro';

B-Tree 对比 哈希索引

在以下情况下，哈希索引并不适用：

• 范围查询：哈希索引未针对范围查询进行优化， 查找某个值范围内的记录（使用 =、>、>=、<、<= 或 BETWEEN 运算符）。在这种情况下，B-Tree索引会更合适。
• 结果排序：哈希索引未针对排序结果进行优化，需要根据特定列对记录进行排序。在这种情况下，B-Tree索引会更合适。
• 大型数据集：哈希索引会占用大量内存， 因此不适合需要考虑内存使用情况的大型数据集。

位图索引(Bitmap Index)

位图索引用于具有少量不同值的列，例如布尔值的列或性别列。位图索引对应数值范围较少的列来说非常紧凑且高效。

SELECT * FROM employees WHERE gender = 'Female';

全文索引

全文索引用于索引大量文本数据，例如文档或网页。该索引算法将文本分解为单词或标记，并以允许高效搜索操作的方式对它们进行索引。全文索引对于涉及在文本中搜索特定单词或短语的查询最有用。全文索引通常用于 Elasticsearch 等搜索引擎。

通过全文索引，电子商务应用程序可以根据用户输入的搜索查询快速搜索大型产品目录。*全文索引允许基于多个单词和短语进行搜索，包括拼写错误、同义词，甚至相关概念。 *这使得用户更容易找到他们正在寻找的东西，即使他们不知道确切的产品名称或描述。

例如，假设一位顾客正在寻找一双新的跑鞋。他们在搜索栏中输入“跑鞋”。通过全文索引，电子商务应用程序可以快速搜索所有产品描述、名称和标签， 以查找与跑鞋相关的所有产品。搜索结果将根据相关性进行排序，相关性由搜索词在产品信息中出现的频率决定。

如果没有全文索引，搜索可能只会查看产品名称，而无法考虑可能与客户相关的其他因素，例如产品描述或标签。此外，搜索可能无法处理拼写错误或相关概念，例如“慢跑鞋”或“运动鞋”。

假设我们有一个名为 products 的表，其中包含以下列：id、名称、描述和标签。

CREATE FULLTEXT INDEX products_ft_index ON products(name, description, tags);

现在，假设一位顾客搜索“跑鞋”。我们可以使用以下查询来搜索与搜索词相关的产品：

ELECT id, name, description, MATCH(name, description, tags) AGAINST('running shoes') as relevance
FROM products
WHERE MATCH(name, description, tags) AGAINST('running shoes' IN BOOLEAN MODE)
ORDER BY relevance DESC

上图最后一列是相关性分数，表示每个产品与搜索词的匹配程度，分数越高匹配越接近，搜索结果根据相关性分数按降序排序， 因此相关性得分最高的产品（耐克跑鞋）显示在列表顶部。

下面是另一个示例查询，用于搜索包含“organic”和“coffee”一词的产品：

SELECT id, name, description, MATCH(name, description, tags) AGAINST('+"organic" +"coffee"') as relevance
FROM products
WHERE MATCH(name, description, tags) AGAINST('+"organic" +"coffee"' IN BOOLEAN MODE)
ORDER BY relevance DESC;

该查询搜索名称、描述或标签列中同时具有“organic”和“coffee”关键字的所有产品。每个结果的相关性得分也是根据关键字在列中出现的次数和位置来计算的。

输出结果包含“id”、“name”、“description”和“relevance”列，结果按“relevance”列降序排列。

全文索引的优点：

• 全文索引对于基于文本的列非常有效
• 非常适合搜索引擎和内容管理系统
• 支持搜索结果的相关性排序

全文索引的缺点：

• 全文索引会占用大量存储空间

• 对于非常大的数据集， 性能可能会下降

• 全文索引不适合数字或分类数据

总结

本文介绍了数据库常见的4种索引和其原理，包括B-Tree索引，哈希索引，位图索引和全文索引，每种索引都有自己的特点，在实际中请根据业务场景选择适合的索引。