MySQL - 非聚簇索引

MySQL: 聚集索引与二级索引

索引是特殊的查找表，数据库搜索引擎可以使用它们来加快数据检索。数据库无需逐行扫描整个表，而是可以使用索引快速找到所需数据的位置。在 MySQL 中，InnoDB 存储引擎（默认）主要使用两种类型的索引：聚集索引（clustered index）和二级索引（secondary index）。

理解它们之间的区别是设计高性能数据库的关键。

聚集索引

在 InnoDB 中，表的数据是按照其聚集索引的顺序物理存储的。可以将其想象成一本字典，其中单词（数据）已经按字母顺序（由聚集索引键）排序。因为数据本身就是索引，所以一个表只能有一个聚集索引。

InnoDB 自动按照以下优先级选择聚集索引：

1. 表的 PRIMARY KEY（主键）。
2. 如果没有 PRIMARY KEY，则选择第一个所有键列都为 NOT NULL 的 UNIQUE 索引。
3. 如果上述两者都不存在，InnoDB 会在包含行 ID 值的合成列上内部生成一个名为 GEN_CLUST_INDEX 的隐藏聚集索引。

正因如此，选择一个好的主键是您可以做出的最重要的性能决策之一。

二级索引（非聚集索引）

InnoDB 表上的任何其他索引都是二级索引。与聚集索引不同，二级索引不包含行数据本身。相反，它是一个独立的数据结构，包含被索引的列值以及一个指向对应行的聚集索引键（主键）的指针。

当您使用二级索引进行查询时，MySQL 会执行两次查找：

1. 首先，它搜索二级索引以找到匹配行的主键值。
2. 其次，它使用该主键值在聚集索引中查找完整的行数据。

创建二级索引的语法

CREATE INDEX index_name ON table_name (column_name1, column_name2, ...);

实际示例

让我们创建一个 employees 表。id 列将成为我们的主键，因此也是我们的聚集索引。然后，我们将在 department 列上添加一个二级索引，以加快按部门的搜索速度。

-- 创建表。'id' 成为聚集索引。
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    hire_date DATE NOT NULL
);

-- 创建一个二级索引，以便快速查找按部门分类的员工。
CREATE INDEX idx_department ON employees (department);

验证

要查看表上的索引，SHOW INDEXES 命令比 DESCRIBE 更具信息量。

SHOW INDEXES FROM employees;

输出将显示两个索引：

Table	Non_unique	Key_name	Column_name
employees	0	PRIMARY	id
employees	1	idx_department	department

• PRIMARY：这是我们的聚集索引。Non_unique 为 0，表示值必须是唯一的。
• idx_department：这是我们的二级索引。Non_unique 为 1，因为多个员工可以在同一个部门。

索引最佳实践

• 为搜索创建索引：为经常在 WHERE 子句、JOIN 条件和 ORDER BY 子句中使用的列添加索引。
• 选择好的主键：一个简短、唯一且不变的主键（如 INT AUTO_INCREMENT）是聚集索引的理想选择。
• 复合索引：如果您经常一起按多个列进行过滤（例如，WHERE department = 'Sales' AND hire_date > '2022-01-01'），请在两个列上创建复合索引：CREATE INDEX idx_dept_hire_date ON employees (department, hire_date);。列的顺序很重要！
• 不要过度索引：每个索引都会消耗存储空间并减慢写入操作（INSERT、UPDATE、DELETE），因为索引也必须更新。仅创建能提供明显性能优势的索引。

现代索引：表达式索引

自 MySQL 8.0.13 起，您可以创建基于表达式而非仅列的索引。这对于涉及函数的查询非常有用。

-- 假设我们经常以不区分大小写的方式按姓氏搜索员工。
-- 传统的 'last_name' 索引对此类查询无济于事：
-- SELECT * FROM employees WHERE LOWER(last_name) = 'chen';

-- 我们可以直接在表达式上创建索引！
CREATE INDEX idx_lastname_lower ON employees ((LOWER(last_name)));

现在，该查询将能够使用此函数索引，使其速度大大加快。

使用 EXPLAIN 分析索引使用情况

查看索引是否被使用的最实际方法是 EXPLAIN 命令。它显示了查询的执行计划。以下是一个从应用程序角度出发的实际示例。

-- 首先，让我们向 employees 表添加一些数据。
INSERT INTO employees (first_name, last_name, department, hire_date) VALUES
('Ayla', 'Chen', 'Engineering', '2021-06-15'),
('Ben', 'Carter', 'Sales', '2020-02-01');

-- 现在，让我们分析一个查询。我们正在搜索一个未索引的列。
EXPLAIN SELECT * FROM employees WHERE last_name = 'Chen';

-- 输出将显示 'type' 为 'ALL'，表示全表扫描。

-- 让我们添加一个索引。
CREATE INDEX idx_lastname ON employees (last_name);

-- 再次运行分析。
EXPLAIN SELECT * FROM employees WHERE last_name = 'Chen';

-- 现在输出将显示 'type' 为 'ref'，'key' 为 'idx_lastname'，
-- 表明索引已用于快速查找。

在客户端程序中，您可以对应用程序的查询运行 EXPLAIN 来诊断性能问题，并决定何处需要新索引。这是任何使用数据库的开发人员的基本技能。