SQL数据库索引怎么用：创建、查看与删除

索引是数据库为加快数据查找而维护的辅助结构。它能让某些查询少扫描数据，但创建、更新和删除数据时也要维护索引，因此索引并非越多越好。索引还占用存储空间；设计不当时，即使查询条件中出现了索引列，数据库也未必会使用它。

本文以 MySQL 8.0 和 InnoDB 为主要示例，并补充 PostgreSQL 的对应写法。两种数据库的语法、执行计划和索引能力并不完全相同，实际操作前应确认所用数据库及版本。

一、索引是什么，适合解决什么问题

可以把索引想成按某些列组织的查找目录。没有合适索引时，数据库可能需要逐行检查表中的记录；有索引时，则可能先定位符合条件的记录，再读取对应数据。具体采用哪种策略由优化器决定。

索引通常值得考虑的场景包括：

• 查询经常按某列过滤，例如根据用户编号、状态筛选订单。
• 查询经常按某列排序，或按某些列分组。
• 表之间经常按关联字段连接，例如订单表的用户编号关联用户表主键。
• 需要保证某列或多列的值不重复，此时应考虑唯一索引或唯一约束。

以下情况则要谨慎：表很小，全表扫描可能更便宜；列的取值种类很少且数据分布不利于索引筛选；写入非常频繁，额外索引的维护成本可能明显；索引列很长，索引空间和写入成本较高。是否创建索引，应以真实查询和数据规模为依据，而不是只看列名。

二、先准备一张示例表

下面的建表和数据示例可以在 MySQL 8.0 中运行。我们先不为查询字段单独建立索引，稍后再观察执行计划。

CREATE DATABASE IF NOT EXISTS index_demo;
USE index_demo;

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO orders (user_id, status, created_at, total_amount) VALUES
(101, 'paid',   '2025-01-10 10:00:00', 89.90),
(101, 'shipped','2025-01-11 12:30:00', 120.00),
(102, 'paid',   '2025-01-12 09:15:00', 39.50),
(103, 'pending','2025-01-13 18:20:00', 220.00),
(102, 'paid',   '2025-01-14 08:45:00', 59.90);

表中的主键 id 已经由 PRIMARY KEY 声明。InnoDB 会为主键建立索引，因此通常不需要再对同一列重复创建普通索引。主键不仅用于查找，也有唯一性和非空约束的含义。

三、创建索引：普通索引、唯一索引与联合索引

1. 创建普通索引

MySQL 可以使用 CREATE INDEX 创建普通索引：

CREATE INDEX idx_orders_user_id
ON orders (user_id);

也可以在建表时声明索引。对已经存在的表，通常使用 CREATE INDEX 更直观。索引名由开发者指定，应采用易于识别的命名方式，例如表名、索引列和索引类型的组合。索引名在同一张表内需要避免重复。

现在按用户查询订单：

SELECT id, status, created_at, total_amount
FROM orders
WHERE user_id = 101;

该查询有机会使用 user_id 索引，但“有索引”不等于“必定使用索引”。如果表非常小，或条件能匹配表中大部分记录，优化器可能判断扫描整张表更划算。

2. 创建唯一索引

如果业务要求某个值不能重复，可以创建唯一索引：

CREATE UNIQUE INDEX uq_example
ON some_table (some_column);

以上语句要求表 some_table 和列 some_column 已存在。创建前，如果已有重复值，创建会失败。唯一索引适用于确实存在唯一性规则的场景，例如用户名、外部订单号等；它不能替代应用侧对输入格式和权限的校验。

对于新表，也可以用唯一约束表达业务规则：

CREATE TABLE accounts (
    id BIGINT NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_accounts_email (email)
) ENGINE=InnoDB;

唯一约束通常也由数据库通过唯一索引实现，但“约束”表达数据完整性规则，“索引”是用于查找和约束实现的结构。设计时应优先明确业务规则，再选择合适的约束和索引定义。唯一列是否允许 NULL、多个 NULL 如何处理，会受数据库和约束定义影响；不要把“唯一”简单理解为“必须非空”，需要强制非空时应另外声明 NOT NULL。

3. 创建联合索引

当查询通常同时使用多个字段时，可以创建联合索引。例如常见查询是按用户筛选，再按创建时间倒序查看订单：

CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at);

SELECT id, status, created_at
FROM orders
WHERE user_id = 101
ORDER BY created_at DESC;

联合索引的列顺序很重要。对常见的 B-tree 索引，可以先把它理解为按列从左到右组织：这个索引通常适合只按 user_id 查找，也适合按 user_id 和 created_at 一起筛选；但通常不能像单列 created_at 索引那样，直接高效支持只按 created_at 查找。

这就是常说的“最左前缀”原则。它是理解常规 B-tree 联合索引的实用规则，不意味着优化器在任何场景下都绝不可能以其他方式利用索引。索引能否满足过滤、排序或分组，还要结合查询条件、数据分布、数据库版本和执行计划判断。

规划联合索引时，应优先根据实际查询模式确定列顺序。例如，若查询是：

SELECT id, created_at
FROM orders
WHERE user_id = 101
  AND status = 'paid'
ORDER BY created_at DESC;

可以评估 (user_id, status, created_at) 这类索引是否适合。但不要机械地套用“选择性最高的列永远放最前面”：等值条件、范围条件、排序要求、其他查询是否复用该索引，都会影响设计。最好结合慢查询记录和执行计划验证。

4. 复合查询不一定需要把所有列都放进索引

把很多字段塞进一个索引可能增加存储和写入成本。若查询只返回少量列，某些数据库和场景可以利用覆盖索引减少回表；但覆盖索引的效果取决于数据库实现和查询计划，也不代表应该把整张表的字段都加入索引。MySQL 中可以在索引定义里包含需要的索引列；InnoDB 的二级索引还会保存主键值。应以测量结果为准。

四、索引创建时需要考虑的边界

1. 对索引列做函数或隐式类型转换

常见错误是对索引列做运算或函数处理，导致查询不能按预期使用普通索引。例如：

SELECT id
FROM orders
WHERE DATE(created_at) = '2025-01-10';

如果目标是查询某一天，通常可以改写为范围条件：

SELECT id
FROM orders
WHERE created_at >= '2025-01-10 00:00:00'
  AND created_at <  '2025-01-11 00:00:00';

范围边界比对时间列调用函数更容易利用普通索引，也能避免把一天结束时的精度问题写错。比较两侧的数据类型也应保持一致，避免数据库对索引列做隐式转换。

2. 前缀匹配与包含匹配

普通 B-tree 索引通常可以辅助处理从字符串开头开始的前缀匹配，例如 name LIKE 'Sam%'；而 name LIKE '%Sam%' 以通配符开头，通常无法通过普通 B-tree 索引直接定位匹配位置。具体能力也可能受字符集、排序规则、数据库和查询形式影响。

MySQL 支持为较长字符串创建前缀索引，例如：

CREATE INDEX idx_example_name_prefix
ON some_table (name(20));

前缀长度的单位和可用上限受列类型、存储引擎、字符集、行格式以及 MySQL 版本等因素影响。对于多字节字符集，一个字符可能占多个字节；不能把“长度 20”简单理解为固定占用 20 字节。创建前应检查实际环境中的引擎限制和完整报错信息。前缀索引只保存字符串开头的一部分，不能单独用于保证整列值唯一。

全文搜索、模糊包含匹配和地理空间检索等需求，可能需要全文索引、专用搜索系统或其他索引类型。普通 B-tree 索引并非所有检索问题的通用解法。

3. 索引数量和写入成本

每增加一个索引，数据库在插入、删除，以及被索引列更新时，都可能需要额外维护工作。索引过多还会增加磁盘占用、缓存压力和备份成本。重复索引也常被忽略，例如已经有 (user_id, created_at) 时，是否还需要单独的 user_id 索引，应结合实际查询、索引属性和执行计划判断，而不是一概保留或删除。

此外，索引通常无法消除返回大量结果、读取大量列、复杂计算或高成本连接本身的开销。某条查询慢，不应未经分析就只添加索引。

五、查看现有索引

MySQL

使用 SHOW INDEX 查看指定表的索引信息：

SHOW INDEX FROM orders;
SHOW INDEX FROM orders FROM index_demo;

结果中可以关注索引名称、索引列及其顺序、是否唯一、基数估计等字段。基数通常是估算值，不应把它当作实时精确统计。也可以查询数据字典了解当前数据库中的索引：

SELECT
    TABLE_NAME,
    INDEX_NAME,
    NON_UNIQUE,
    SEQ_IN_INDEX,
    COLUMN_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'orders'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

PostgreSQL

在 psql 命令行中，可以查看表的结构和索引：

\d orders
\di

也可以用系统视图查询当前 schema 下的索引：

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = current_schema()
  AND tablename = 'orders';

不同客户端对 \d、\di 这类命令的支持不同，它们是 psql 的元命令，不是通用 SQL。需要跨客户端使用时，可选择数据库系统视图或客户端提供的结构查看功能。

六、用执行计划确认索引是否有帮助

MySQL 可以使用 EXPLAIN 查看查询计划：

EXPLAIN
SELECT id, status, created_at
FROM orders
WHERE user_id = 101
ORDER BY created_at DESC;

可以关注访问方式、可能使用的索引、实际选择的索引、预计扫描行数等信息。不同 MySQL 版本的输出格式可能不同。MySQL 8.0 还支持 EXPLAIN ANALYZE，它会执行查询并报告实际运行信息，因此只应对可接受的查询在合适环境中使用。

EXPLAIN ANALYZE
SELECT id, status, created_at
FROM orders
WHERE user_id = 101
ORDER BY created_at DESC;

PostgreSQL 可使用类似语句：

EXPLAIN
SELECT id, status, created_at
FROM orders
WHERE user_id = 101
ORDER BY created_at DESC;

EXPLAIN ANALYZE 会实际执行查询。它适合评估真实执行时间和行数，但要注意查询的副作用：例如分析写入语句会实际修改数据。不要只凭计划里出现或没有出现某个索引，就判断设计正确。应在代表性数据量和接近生产的数据分布下测试，并关注实际扫描行数、耗时、排序、回表及读写负载。

七、删除索引

删除前先确认索引的用途：它可能支撑慢查询、唯一性规则或外键相关操作。删除索引不能顺便删除数据，但删除唯一索引可能取消原有唯一性保障。生产环境中应先评估影响并选择合适的变更窗口。

MySQL 删除索引使用 ALTER TABLE ... DROP INDEX：

DROP INDEX idx_orders_user_created ON orders;

也可以写成：

ALTER TABLE orders
DROP INDEX idx_orders_user_created;

普通索引删除后，相关查询仍可执行，只是执行计划和性能可能变化。主键索引不能当成普通索引随意删除：删除主键会影响表结构和数据组织，且可能受外键等依赖限制。删除唯一索引则会取消由该索引保障的唯一性。

PostgreSQL 使用 DROP INDEX：

DROP INDEX idx_orders_user_created;

PostgreSQL 的 DROP INDEX CONCURRENTLY 可以在特定场景下减少阻塞，但有使用限制，例如不能在事务块中执行。它不等于完全没有资源开销或风险。MySQL 的在线 DDL 能力则与具体版本、表结构和操作类型有关。大表上的索引创建和删除都可能产生明显的 I/O、锁等待或复制延迟，生产环境应先核对数据库版本与变更策略，不能假定所有索引 DDL 都是瞬间完成且完全无阻塞。

八、常见错误与排查思路

1. 看到筛选列就建索引。 先确认查询是否高频、表是否足够大、筛选是否有效，再用执行计划和真实负载验证。
2. 忽略联合索引顺序。(a, b) 和 (b, a) 并不等价；根据主要查询的过滤、排序和范围条件设计。
3. 认为所有条件都会命中索引。 函数、隐式类型转换、前导通配符以及低效的数据分布，都可能让普通索引无法发挥预期作用。
4. 把索引当作查询性能的唯一答案。 返回过多数据、扫描行数过大、排序或连接策略不合适时，还需要优化查询和数据访问方式。
5. 重复创建相似索引。 用索引清单和查询记录核对用途，确认没有重要依赖后再考虑清理。
6. 忽略 DDL 对生产环境的影响。 在测试环境预演，检查锁等待、磁盘空间、复制延迟和回滚方案，再安排生产变更。

九、索引不是安全机制

索引不是权限控制、加密或访问隔离手段。创建索引不会让某列自动变得安全，也不能阻止有权限的用户查询数据。敏感数据应通过最小权限、访问控制、传输与存储加密、审计和必要的数据脱敏等措施保护。

索引也不能防止 SQL 注入。查询仍应使用参数化语句处理数据值，例如：

SELECT id, status
FROM orders
WHERE user_id = ?;

其中问号是占位符示意，实际占位符形式取决于数据库驱动。参数化可以让数据值不被当作 SQL 代码解释，但表名、列名、排序方向等 SQL 结构通常不能直接用普通值参数绑定；如果必须动态拼接标识符，应使用程序提供的安全标识符引用方式，或通过白名单限定可选名称。

索引名、表名和列名也是数据库结构标识符，不能把不可信输入未经校验就拼接进索引管理语句。创建索引的权限应只授予需要管理结构的账号，避免让普通业务账号拥有不必要的 DDL 权限。

总结

使用索引的基本流程是：先找到真实的慢查询和访问模式，再按查询条件、排序与约束设计索引；创建后用索引列表确认结构，并用执行计划和实际负载验证效果；当索引不再需要时，评估依赖和生产影响后再删除。索引能改善部分查找，但会增加空间和写入成本，也不承担安全防护职责。理解这些边界，比单纯记住几条建索引语句更重要。