MySQL - 创建索引

MySQL - 创建索引

数据库索引的工作方式与书后的索引非常相似。您无需扫描每一页（每一行）来查找信息，只需在索引中查找主题，索引会准确地告诉您要去哪一页。同样，数据库索引允许数据库引擎快速查找具有特定列值的行，从而显著提高数据检索（查询）的速度。

为什么要使用索引？

• 更快的 SELECT 查询：这是主要好处。索引对于 WHERE 子句、JOIN 条件和 ORDER BY 子句最有效。
• 强制唯一性：UNIQUE 索引确保在索引列中没有两行可以具有相同的值。PRIMARY KEY 是一种特殊类型的 UNIQUE 索引。
• 提高 JOIN 性能：对外键（外部键）列进行索引可以显著加快连接操作。

然而，索引也有成本。它们消耗存储空间并减慢数据修改操作（INSERT、UPDATE、DELETE）的速度，因为索引也必须更新。因此，只应创建实际使用的索引。

MySQL 中的索引类型

MySQL 支持几种索引类型，其中 B-Tree 是最常见的：

• B-Tree：大多数存储引擎（如 InnoDB 和 MyISAM）的默认索引类型。它适用于各种查询，包括相等（=）、范围（>、<、BETWEEN）和前缀（LIKE 'prefix%'）查找。
• UNIQUE 唯一索引：一种 B-Tree 索引，对索引列强制执行唯一性。PRIMARY KEY 是一种特殊类型的 UNIQUE 索引。
• FULLTEXT 全文索引：用于对基于字符的列（CHAR、VARCHAR、TEXT）执行全文搜索。它允许您在文本内容中搜索单词和短语。
• SPATIAL 空间索引：用于索引地理空间数据类型，允许对地理数据进行高效查询（例如，查找特定半径内的所有位置）。

在新表上创建索引

您可以在 CREATE TABLE 语句中直接定义索引。

CREATE TABLE Products (
    id INT AUTO_INCREMENT PRIMARY KEY, -- PRIMARY KEY 自动是一个索引
    product_code VARCHAR(20) NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    category_id INT,
    price DECIMAL(10, 2),

    -- 在 product_code 上创建一个 UNIQUE 索引
    UNIQUE INDEX idx_product_code (product_code),

    -- 在 category_id 上创建一个简单（B-Tree）索引
    INDEX idx_category_id (category_id)
);

在现有表上创建索引

您可以使用 CREATE INDEX 或 ALTER TABLE 为现有表添加索引。

使用 CREATE INDEX

CREATE INDEX 语句清晰明了。

-- 在 product_name 列上创建一个常规索引
CREATE INDEX idx_product_name ON Products(product_name);

使用 ALTER TABLE

ALTER TABLE 语句功能更强大，可用于多种模式修改。

-- 使用 ALTER TABLE 创建与上述相同的索引
ALTER TABLE Products ADD INDEX idx_price(price);

唯一索引和复合索引

唯一索引

如前所述，UNIQUE 索引防止重复值。尝试插入 Products 表中已存在的 product_code 将导致错误。

CREATE UNIQUE INDEX idx_product_code ON Products(product_code);

复合索引

复合（或多列）索引是在两个或更多列上创建的索引。索引中列的顺序非常重要。一个 (col1, col2) 上的索引可以用于仅基于 col1 或同时基于 col1 和 col2 的查询。它通常不用于仅基于 col2 的查询。

-- 为按类别和价格查找创建复合索引
CREATE INDEX idx_category_price ON Products(category_id, price);

此索引对于 SELECT * FROM Products WHERE category_id = 5 ORDER BY price; 这样的查询非常有效。

如何查看索引是否被使用

EXPLAIN 命令是一个强大的工具，用于分析 MySQL 如何执行查询。它显示使用了哪些索引（如果有的话）。

EXPLAIN SELECT * FROM Products WHERE product_code = 'XYZ-123';

在 EXPLAIN 的输出中，查看 key 列。如果它显示了你的索引名称（例如，idx_product_code），则索引被有效使用。如果它是 NULL，则 MySQL 正在执行全表扫描，你可能需要添加或调整索引。

索引最佳实践

• 选择性地索引：不要对所有列都建立索引。只对经常在 WHERE、JOIN 或 ORDER BY 子句中使用的列建立索引。
• 考虑基数：索引在基数高（许多唯一值）的列上效果最好。对仅有 ‘Male’、‘Female’、‘Other’ 的 gender 列进行索引通常不如对 username 列进行索引有效。
• 索引外键：始终在外键列上创建索引。这能显著提高 JOIN 性能。
• 明智地使用复合索引：对于对多列进行过滤的查询，复合索引通常优于多个单列索引。
• 保持索引小巧：为你的列使用最有效的数据类型，以保持索引紧凑和快速。

删除索引

如果一个索引未使用或不再需要，应该删除它以节省空间并提高写入性能。你可以使用 DROP INDEX。

-- 指定索引名称及其所属的表
DROP INDEX idx_price ON Products;