掌握MySQL和MariaDB中的索引：优化之路

MySQL 和 MariaDB 都无缝利用平衡树（B 树）索引的高效性来优化数据操作。这种共享索引机制可确保快速的数据检索，提升查询性能，并最大限度地减少磁盘输入/输出 (I/O)，从而带来更灵敏、更高效的数据库体验。

本文将深入探讨索引，指导您创建索引，并分享在 MySQL 和 MariaDB 数据库中更有效地使用索引的技巧。

什么是索引？

当您在 MySQL 数据库中查询特定信息时，查询会搜索数据库表中的每一行，直到找到正确的行。这可能需要很长时间，尤其是在数据库规模庞大的情况下。

数据库管理员使用索引来加快数据检索过程并优化查询效率。索引构建了一种数据结构，通过系统地组织数据来最大限度地减少必须搜索的数据量，从而更快、更有效地执行查询。

假设您想在以下 Customer 表中查找名字为 Ava 的客户：

MySQL Workbench 的客户表预览

在 first_name 列中添加 B 树索引可以创建一个结构，以便更高效地搜索所需信息。该结构类似于一棵树，根节点位于顶部，向下分支到底部的叶节点。

它类似于一棵组织良好的树，其中每一层都根据数据的排序顺序引导搜索。

下图显示了 B 树索引的搜索路径：

B 树客户表

Ava 列在第一位，William 列在最后，按字母升序排列——B 树就是按照字母升序排列姓名的。B 树系统将列表的中间值指定为根节点。由于 Michael 位于字母列表的中间，因此它就是根节点。然后，树向外分支，Michael 的左右两侧都有值。

随着您沿着树的层级向下移动，每个节点都会提供更多键（直接链接到原始数据行），以引导搜索按字母顺序排列的姓名。然后，您会在叶节点找到每个客户名字的数据。

搜索从比较 Ava 和根节点 Michael 开始。在确定 Ava 在字母顺序上排在 Michael 之前后，搜索向左移动。它向下移动到左侧子节点（ Emily ），然后再次向左移动到 Daniel，再向左移动到 Ava，最后到达包含 Ava 信息​​的叶节点。

B 树就像一个简化的导航系统，可以有效地引导搜索到特定位置，而无需检查数据集中的每个名称。这就像在一本精心排列的目录中导航，只需跟随精心布置的路标，就能直接到达目的地。

索引类型

索引有多种类型，可用于各种用途。下面我们将讨论这些不同的类型。

1. 单级索引

单级索引（或称平面索引）将索引键映射到表数据。索引中的每个键都对应一个表行。

customer_id 列是 Customer 表中的主键，用作单级索引。该键标识每个客户，并关联他们在表中的信息。

customer_id 键与单个客户详细信息之间的关系非常简单。单级索引在表包含少量行或列且不同值较少的情况下非常有效。例如，status 或 category 等列就是很好的选择。

对于基于单个列定位特定行的简单查询，请使用单级索引。对于较小的数据集，其实现简单、直接且高效。

2. 多级索引

与用于有组织的数据检索的单级索引不同，多级索引采用分层结构。它们具有多层引导。顶级索引将搜索定向到较低级别的索引，依此类推，直到存储数据的叶级。这种结构减少了搜索过程中所需的比较次数。

考虑一个包含 address 和 customer_id 列的多级索引。

第一层组织地址。第二层（在每个地址内）进一步组织客户 ID。

这种组织方式非常适合需要有序搜索层次结构的更大规模数据集。对于像 last_name 这样具有中等基数（特定列中数据值的唯一性）的列，它也非常有用。

3. 聚集索引

MySQL 中的聚集索引决定了索引的逻辑顺序以及数据在表中的顺序。如果将聚集索引应用于 Customer 表中的 customer_id 列，则行将根据该列的值进行排序。这意味着表中数据的顺序反映了聚集索引的顺序，从而通过减少磁盘 I/O 来提高特定模式的数据检索性能。

当数据检索模式与客户 ID 的顺序一致时，此策略非常有效。它也适用于具有高基数的列，例如 customer_id。

虽然聚集索引在特定模式下的数据检索性能方面具有优势，但需要注意其潜在的缺点。基于聚集索引对行进行排序会影响插入和更新操作的性能，尤其是在插入或更新模式与聚集索引的顺序不一致的情况下。这是因为新数据的插入或更新必须以保持排序顺序的方式进行，这会导致额外的开销。

4. 非聚集索引

非聚集索引赋予数据库结构更大的灵活性。假设您在 email 列上使用非聚集索引。与聚集索引不同，它不会更改表中条目的顺序。

相反，它会构建一个新的结构，将键（在本例中为电子邮件地址）映射到数据行。当您在数据库中查询特定的电子邮件地址时，非聚集索引会将搜索直接引导到相关行，而无需依赖表的顺序。

非聚集索引的灵活性是其主要优势。它们能够高效地搜索多列，而无需对存储的数据进行排序。该系统使非聚集索引用途广泛，因为它们可以处理不遵循表主顺序的查询。

当数据检索模式与字母顺序不同，并且对于基数中等到高的列（例如电子邮件）时，非聚集索引非常有用。

如何创建索引

我们已经从总体上回顾了索引的概念，现在让我们回顾一些使用 MySQL Workbench 创建索引的实际示例。

先决条件

要继续学习，您需要：

• 一个 MySQL 数据库（兼容 MariaDB）
• 一些 SQL 和 MySQL 经验
• MySQL Workbench

如何创建客户表

1. 启动 MySQL Workbench 并连接到您的 MySQL 服务器。
2. 运行以下 SQL 查询来创建 Customer 表：

   CREATE TABLE Customer (
   customer_id INT PRIMARY KEY,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   email VARCHAR(100),
   phone_number VARCHAR(15),
   address VARCHAR(255)
   );

3. 插入以下数据：

   -- Adding Data to the Customer Table
   INSERT INTO Customer (customer_id, first_name, last_name, email, phone_number, address)
   VALUES
   (1, 'John', 'Doe', 'john.doe@email.com', '123-456-7890', '123 Main St'),
   (2, 'Jane', 'Smith', 'jane.smith@email.com', '987-654-3210', '456 Oak Ave'),
   (3, 'Robert', 'Johnson', 'robert.johnson@email.com', '111-222-3333', '789 Pine Rd'),
   (4, 'Emily', 'Williams', 'emily.williams@email.com', '555-666-7777', '101 Cedar Ln'),
   (5, 'Michael', 'Brown', 'michael.brown@email.com', '444-555-8888', '202 Elm St'),
   (6, 'Sophia', 'Davis', 'sophia.davis@email.com', '999-888-7777', '303 Maple Ave'),
   (7, 'Daniel', 'Miller', 'daniel.miller@email.com', '777-888-9999', '404 Birch Rd'),
   (8, 'Olivia', 'Jones', 'olivia.jones@email.com', '333-222-1111', '505 Pine St'),
   (9, 'William', 'Wilson', 'william.wilson@email.com', '111-333-5555', '606 Oak Ln'),
   (10, 'Ava', 'Moore', 'ava.moore@email.com', '888-777-6666', '707 Cedar Ave');

单级索引

在 MySQL 和 MariaDB 中，优化查询性能的一种策略是使用单级索引。

要向 Customer 表添加单级索引，请使用 CREATE INDEX 语句：

-- Creating a Single-Level Index on "customer_id"
CREATE INDEX idx_customer_id ON Customer(customer_id);

执行成功后，数据库将返回以下代码来确认索引创建：

0 row(s) affected Records: 0  Duplicates: 0 Warnings: 0

现在，数据库可以以最佳方式处理基于 customer_id 列值过滤数据的查询，从而大幅提升效率。

多级索引

MySQL 和 MariaDB 提供多级索引，超越了单列索引。这些索引跨越多个级别或列，将多个列的值组合到一个索引中，从而提高查询执行效率。

使用以下代码在 MySQL 或 MariaDB 中创建多级索引，重点关注 address 和 customer_id 列：

-- Creating a Multi-Level Index based on "address" and "customer_id"
CREATE INDEX idx_address_customer_id ON Customer(address, customer_id);

策略性地使用多级索引可以显著提升查询性能，尤其是在处理多列数据时。

聚集索引

除了单级索引和多级索引之外，MySQL 和 MariaDB 还使用聚集索引。这是一种动态工具，它通过将数据行与索引指针的顺序对齐来增强数据库性能。

例如，将聚集索引应用于 Customer 表中的 customer_id 列可以对齐客户 ID 的顺序。

-- Creating a Clustered Index on "customer_id"
CREATE CLUSTERED INDEX idx_customer_id_clustered ON Customer(customer_id);

由于优化了数据顺序，此策略显著提升了特定模式的数据检索性能，同时减少了磁盘 I/O。

非聚集索引

非聚集索引可以根据列优化查询，而无需强制数据按特定顺序排列。在 MySQL 和 MariaDB 中，无需指定索引是否为非聚集索引。

表架构本身就隐含了这一点。只有主键或第一个非空唯一键可以是聚集索引。表的其他索引均隐式为非聚集索引。以下是非聚集索引的示例：

-- Creating a Non-clustered Index on "email"
CREATE INDEX idx_email_non_clustered ON Customer(email);

非聚集索引允许高效地搜索多列，从而构建更通用、响应速度更快的数据库。

最佳实践和要点

当处理值范围较小的列（例如状态或类别）时，请选择单级索引。当处理值范围较大的列（例如电子邮件）时，请使用多级索引和非聚集索引。

在聚集索引和非聚集索引之间进行选择时，您首选的数据检索模式至关重要。对于聚集索引，请选择基数较高的列（例如客户 ID）。对于非聚集索引，请选择基数中等到较高的列（例如电子邮件）。

如何优化索引

为了提升索引的性能，您可以使用一些实用的策略，例如覆盖索引和删除冗余索引。

1. 覆盖索引

覆盖索引通过创建覆盖所有必要数据的索引来提高查询性能。覆盖索引是指索引包含完成查询所需的所有列，避免了访问数据行的需要。

-- Create a Covering Index on "first_name" and "last_name"
CREATE INDEX idx_covering_name ON Customer(first_name, last_name);

2. 删除冗余索引

删除冗余索引，但务必谨慎，因为删除索引可能会影响某些查询性能。

-- Remove an Unnecessary Index
DROP INDEX idx_unnecessary_index ON Customer;

定期检查并删除冗余索引，以确保数据库结构精简高效。

3. 避免过度索引

避免过度索引等常见陷阱。虽然索引可以提升查询性能，但创建过多索引可能会降低性能。务必保持平衡，避免过度索引，因为过度索引可能会导致存储需求增加和性能下降。

4. 分析查询模式

在创建索引之前忽略查询模式分析也是一个常见陷阱。了解经常执行的查询，并重点关注 WHERE 子句或 JOIN 条件中使用的索引列，对于实现最佳性能至关重要。

小结

本文探讨了 MySQL 和 MariaDB 的索引，重点介绍了 B 树机制的效率。文章涵盖了索引基础知识和各种索引类型（单级、多级、聚集和非聚集）。