mysql菜鸟教程

首页 >> mysql菜鸟教程

专栏导航

13.2 创建索引：CREATE INDEX

在了解了索引的重要性和工作原理之后，本节将详细介绍如何在实际的表中创建索引。MySQL 提供了多种创建索引的方式，你可以根据需求选择合适的索引类型和创建方法。

一、创建索引的基本语法

使用 CREATE INDEX 语句可以在已存在的表上创建索引。其基本语法如下：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名
ON 表名 (列名1 [ASC | DESC], 列名2 [ASC | DESC], ...);

索引名：建议使用有意义的名称，通常包含表名和列名，例如 idx_users_username。
表名：要在其上创建索引的表。
列名：指定要索引的列。可以指定多列（复合索引），并用逗号分隔。
ASC | DESC：指定索引的排序方向，默认为 ASC（升序）。多数情况下不需要指定，但复合索引中列的排序方向可能会影响多列排序的性能。
UNIQUE：创建唯一索引，要求索引列的值必须唯一。
FULLTEXT：创建全文索引，用于对文本内容进行搜索。
SPATIAL：创建空间索引，用于地理空间数据类型。

二、在已有表上创建索引

1. 创建普通单列索引

最常见的索引类型，用于加速对特定列的查询。

CREATE INDEX idx_users_email ON users(email);

这个例子在 users 表的 email 列上创建了一个普通索引 idx_users_email。

2. 创建唯一索引

唯一索引不仅能加速查询，还能保证列值的唯一性，类似于唯一约束。

CREATE UNIQUE INDEX idx_users_username ON users(username);

如果表中已有重复值，创建唯一索引会失败。

3. 创建复合索引（多列索引）

复合索引可以同时加速涉及多个列的查询，尤其当查询条件包含这些列时。

CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

复合索引中列的顺序非常重要。这个索引对以下查询有效：

WHERE user_id = ?
WHERE user_id = ? AND order_date = ?
WHERE user_id = ? ORDER BY order_date

但对于 WHERE order_date = ? 可能不会使用该索引（取决于条件，一般不会）。

4. 创建全文索引

全文索引适用于对文本内容（如文章正文）进行关键词搜索。

CREATE FULLTEXT INDEX idx_articles_content ON articles(content);

注意：全文索引只能用于 CHAR、VARCHAR、TEXT 类型的列。

5. 创建空间索引

空间索引用于地理空间数据类型（如 GEOMETRY、POINT 等），此处略。

三、使用 ALTER TABLE 添加索引

除了 CREATE INDEX，还可以通过 ALTER TABLE 语句添加索引。这种方式在语法上略有不同，但效果相同。

添加普通索引

ALTER TABLE users ADD INDEX idx_users_phone (phone);

添加唯一索引

ALTER TABLE users ADD UNIQUE INDEX idx_users_email (email);

添加主键索引

主键是一种特殊的唯一索引，通常在创建表时定义，但也可以用 ALTER TABLE 添加（前提是表中没有主键）：

ALTER TABLE users ADD PRIMARY KEY (id);

添加全文索引

ALTER TABLE articles ADD FULLTEXT INDEX idx_articles_content (content);

四、在创建表时定义索引

在 CREATE TABLE 语句中，也可以直接定义索引，这是更常见的做法。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    created_at DATETIME,
    INDEX idx_email (email),                       -- 普通索引
    UNIQUE INDEX idx_phone (phone),                -- 唯一索引
    INDEX idx_created_at (created_at)              -- 另一普通索引
);

也可以定义复合索引：

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10,2),
    INDEX idx_user_date (user_id, order_date)      -- 复合索引
);

五、查看索引

要查看表中已存在的索引，可以使用 SHOW INDEX FROM 语句：

SHOW INDEX FROM users;

这将返回一个结果集，包含索引名称、列名、唯一性、排序方式等信息。

六、创建索引的注意事项

1、索引命名规范：建议使用统一的命名规则，例如 idx_表名_列名，uniq_表名_列名表示唯一索引，便于识别。

2、索引列的选择：

优先为经常出现在 WHERE、JOIN、ORDER BY、GROUP BY 子句中的列创建索引。
选择区分度高的列（即不同值多的列），例如身份证号比性别更适合建索引。
避免在频繁更新的列上建过多索引，因为更新索引开销大。

3、复合索引的列顺序：

将最常用、区分度最高的列放在最左边。
考虑查询模式，使索引能被更多查询使用。

4、索引数量控制：索引并非越多越好，每个索引都会增加写入开销和存储空间。通常一张表的索引数应控制在个位数（如 5 个以内）。

5、大表建索引的时机：在生产环境的大表上创建索引可能耗时较长，且会锁定表（取决于 MySQL 版本和存储引擎），建议在业务低峰期执行，或使用工具（如 pt-online-schema-change）进行在线变更。

6、冗余索引：避免创建重复或冗余的索引。例如，已有复合索引 (a, b)，再单独创建 (a) 索引是冗余的（因为复合索引也可以用于只查询 a 的情况，但效率可能略低，需权衡）。

7、前缀索引：对于长文本列（如 VARCHAR(255)），可以只索引前几个字符以节省空间，例如：

CREATE INDEX idx_articles_title ON articles(title(20));

七、实践示例

假设我们有一个电商数据库，需要为 orders 表创建索引以优化常见查询。

-- 创建普通索引：经常按用户查询订单
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 创建复合索引：经常按用户和日期范围查询
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- 为订单状态创建索引（如果状态区分度不高，效果有限）
CREATE INDEX idx_orders_status ON orders(status);

使用 SHOW INDEX 验证：

SHOW INDEX FROM orders;

八、小结

创建索引可以使用 CREATE INDEX 或 ALTER TABLE 语句。
支持多种索引类型：普通索引、唯一索引、全文索引、空间索引。
可以在建表时直接定义索引，也可以在表创建后添加。
索引的设计需要根据实际查询模式进行优化，避免过多或冗余的索引。
使用 SHOW INDEX 查看已有索引，便于分析和维护。