MySQL - 索引

MySQL - 数据库索引

索引是一种特殊的数据结构，数据库用它来显著加快对表的数据检索操作。把它想象成书后的索引：你不需要扫描每一页来查找某个主题，而是通过索引查找并直接跳转到正确的页面。类似地，数据库索引允许查询引擎查找行，而无需扫描整个表。

索引的成本

尽管索引对于读取性能（SELECT 查询）至关重要，但它们也有代价。每次你 INSERT、UPDATE 或 DELETE 一行时，数据库还必须更新包含该行的每个索引。这增加了写入操作的开销。因此，你应该策略性地创建索引，而不是仅仅为每个列创建索引。

索引对用户是不可见的，但它是数据库引擎高效执行查询的基本工具。

MySQL 索引类型

MySQL 提供了多种类型的索引，每种都适用于不同的目的。你可以在单个列上创建索引，也可以在多个列上创建索引（复合索引）。

主键

PRIMARY KEY（主键）是一种特殊类型的索引，它唯一标识表中的每条记录。它强制执行两条规则：被索引的列必须包含唯一值，并且不能包含 NULL 值。一个表只能有一个主键。它是最常见和最重要的索引。

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

唯一索引

UNIQUE 索引确保列中（或多列组合中）的所有值都是唯一的。与主键不同，一个表可以有多个唯一索引，并且它们可以允许一个 NULL 值。

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    UNIQUE INDEX (username),
    UNIQUE INDEX (email)
);

普通索引（非唯一）

这是最基本的索引类型。它唯一的目的是提高查询性能。它允许重复值和 NULL。你会在经常搜索或排序但不需要唯一性的列上使用它，例如 last_name 列。

CREATE INDEX idx_lastname ON Customers (last_name);

复合索引（多列）

复合索引是基于两个或更多列的索引。索引定义中列的顺序非常重要。如果查询是基于索引的前导列进行过滤，则可以使用该索引。例如，(last_name, first_name) 上的索引可以用于仅过滤 last_name 的查询，或者同时过滤 last_name 和 first_name 的查询。

CREATE INDEX idx_name ON Customers (last_name, first_name);

全文索引

FULLTEXT（全文）索引用于对 CHAR、VARCHAR 或 TEXT 列中的文本数据执行自然语言搜索。它不是简单的等值检查，而是允许你使用 MATCH() ... AGAINST() 语法在文本中搜索单词和短语。

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

-- Example query
SELECT * FROM Articles
WHERE MATCH(title, body) AGAINST('database performance' IN NATURAL LANGUAGE MODE);

降序索引 (MySQL 8.0+)

传统上，索引以升序存储数据。从 MySQL 8.0 开始，你可以显式创建一个以降序存储键的索引。这可以提高需要按降序排序的查询的性能，例如查找最新的帖子。

CREATE INDEX idx_created_desc ON Posts (created_at DESC);

如何选择要索引的列

决定为哪些列创建索引是数据库性能调优的关键。以下是一些通用指南：

• 主键： 你的表应始终拥有主键。
• 外键： 作为外键的列几乎总是应该被索引，以加快连接（JOIN）操作。
• WHERE 子句： 索引那些经常在 WHERE 子句中使用的列。
• ORDER BY 和 GROUP BY 子句： 对用于排序和分组的列进行索引可以消除缓慢的文件排序操作。
• 列基数： 优先索引高基数（许多唯一值）的列，如电子邮件地址，而不是低基数（很少唯一值）的列，如性别字段。

使用 EXPLAIN 分析查询性能

要查看 MySQL 是否正在为特定查询使用你的索引，你可以使用 EXPLAIN 命令。在 SELECT 语句前加上 EXPLAIN 会显示查询执行计划。

EXPLAIN SELECT * FROM Customers WHERE last_name = 'Smith';

查看 EXPLAIN 的输出。key 列将显示使用了哪个索引。如果为 NULL，则表示没有使用索引，并且 rows 列可能很高，这表明进行了全表扫描。这是诊断慢查询的强大工具。