数据库索引基础：原理与创建方法

在数据库的世界里，索引就像是一本书的目录，它能帮助我们快速定位到所需的数据，大大提高数据库的查询效率。在这一小节中，我们将深入探讨数据库索引的原理、不同类型索引的特点，并且通过具体的 SQL 代码示例，分步骤演示如何创建索引，同时避免索引过度创建导致的性能下降问题。

索引的原理

什么是索引

简单来说，索引是一种数据结构，它存储了表中某些列的值以及这些值对应的行在磁盘上的物理地址。就好比我们在图书馆找书，如果没有图书索引，我们就需要在茫茫书海中一本一本地查找，效率非常低。而有了索引，我们可以根据索引快速定位到所需图书的位置。

在数据库中，当我们执行查询语句时，如果没有索引，数据库系统就需要逐行扫描整个表，直到找到符合条件的记录。而使用索引，数据库系统可以直接根据索引找到对应记录的物理地址，从而大大减少了查询所需的时间。

索引的工作机制

索引的工作机制基于数据结构，常见的索引数据结构有 B 树（B-tree）和 B+ 树（B+ -tree）。以 B+ 树为例，它是一种平衡的多路搜索树，所有的数据都存储在叶子节点上，非叶子节点只存储索引键和指向下一层节点的指针。

当我们执行一个查询时，数据库系统会从 B+ 树的根节点开始，根据查询条件中的索引键值，通过比较和查找，逐步向下遍历 B+ 树，直到找到符合条件的叶子节点，然后根据叶子节点中存储的物理地址，直接访问相应的数据行。

不同类型索引的特点

主键索引

主键索引是一种特殊的唯一索引，它要求索引列的值必须唯一，并且不能为 NULL。在创建表时，如果指定了主键，数据库系统会自动为该主键列创建主键索引。

例如，我们创建一个用户表，指定用户 ID 为主键：

CREATETABLEusers(user_idINTPRIMARYKEY,usernameVARCHAR(50),emailVARCHAR(100));

在这个例子中，user_id列就是主键，数据库会自动为user_id列创建主键索引。主键索引的优点是可以确保表中每行数据的唯一性，并且可以快速定位到指定主键值的记录。

唯一索引

唯一索引要求索引列的值必须唯一，但可以为 NULL。与主键索引不同的是，一个表可以有多个唯一索引。

例如，我们为用户表的email列创建唯一索引：

CREATEUNIQUEINDEXidx_emailONusers(email);

这样，email列中的每个值都必须是唯一的，当我们插入或更新数据时，如果email列的值已经存在，数据库会报错。唯一索引的作用是确保数据的完整性，同时可以提高根据该列进行查询的效率。

普通索引

普通索引是最基本的索引类型，它不要求索引列的值唯一，也可以为 NULL。普通索引可以加速对索引列的查询操作。

例如，我们为用户表的username列创建普通索引：

CREATEINDEXidx_usernameONusers(username);

当我们执行类似SELECT * FROM users WHERE username = 'John'的查询时，使用普通索引可以快速定位到username为 ‘John’ 的记录。

全文索引

全文索引主要用于在文本字段中进行全文搜索。它可以对文本内容进行分词处理，然后创建索引。

例如，我们创建一个文章表，并为文章内容列创建全文索引：

CREATETABLEarticles(article_idINTPRIMARYKEY,titleVARCHAR(200),contentTEXT,FULLTEXT(content));

当我们执行全文搜索时，可以使用MATCH...AGAINST语句：

SELECT*FROMarticlesWHEREMATCH(content)AGAINST('关键词');

全文索引可以帮助我们在大量文本数据中快速找到包含特定关键词的记录。

创建索引的 SQL 代码示例及步骤

步骤一：创建测试表

首先，我们创建一个简单的测试表products，用于演示索引的创建过程：

CREATETABLEproducts(product_idINT,product_nameVARCHAR(100),priceDECIMAL(10,2),categoryVARCHAR(50));

步骤二：创建主键索引

如果我们希望product_id列的值唯一且不为 NULL，我们可以将其设为主键，数据库会自动创建主键索引：

ALTERTABLEproductsADDPRIMARYKEY(product_id);

步骤三：创建唯一索引

假设我们希望product_name列的值也是唯一的，我们可以为其创建唯一索引：

CREATEUNIQUEINDEXidx_product_nameONproducts(product_name);

步骤四：创建普通索引

为了提高根据category列进行查询的效率，我们可以为category列创建普通索引：

CREATEINDEXidx_categoryONproducts(category);

步骤五：验证索引创建

我们可以使用SHOW INDEX FROM语句来查看表中已创建的索引：

SHOWINDEXFROMproducts;

该语句会返回表中所有索引的信息，包括索引名称、索引列、索引类型等。

避免索引过度创建导致的性能下降问题

虽然索引可以提高查询效率，但过度创建索引也会带来一些问题。首先，索引会占用额外的磁盘空间，因为它需要存储索引数据结构。其次，在插入、更新和删除数据时，数据库系统需要同时更新相应的索引，这会增加数据库的维护成本，降低数据操作的性能。

为了避免索引过度创建，我们需要遵循以下原则：

• 只在经常用于查询条件的列上创建索引：例如，如果我们经常根据category列进行查询，那么为category列创建索引是有必要的；但如果某个列很少用于查询条件，就不需要为其创建索引。
• 避免在低选择性的列上创建索引：低选择性的列是指该列的值重复率很高，例如性别列，只有 ‘男’ 和 ‘女’ 两个值。在这种情况下，创建索引并不能显著提高查询效率，反而会增加索引维护的成本。
• 控制索引的数量：一个表中的索引数量不宜过多，一般建议每个表的索引数量不超过 5 个。

总结

通过本小节的学习，我们了解了数据库索引的原理，包括什么是索引以及它的工作机制。同时，我们掌握了不同类型索引的特点，如主键索引、唯一索引、普通索引和全文索引。并且，我们通过具体的 SQL 代码示例，分步骤演示了如何创建索引。最后，我们还学习了如何避免索引过度创建导致的性能下降问题。

掌握了数据库索引的原理和创建方法后，下一节我们将深入学习数据库查询优化的具体策略，进一步完善对本章数据库优化基础主题的认知。

🍃 系列专栏导航

• 🔖 《深入浅出数据库优化》

• 🍃 博客概览：《程序员技术成长导航，专栏汇总》