创建索引的CREATE INDEX 语句语法

基本语法

CREATE [UNIQUE] INDEX index_name 
ON table_name (column_name1 [ASC | DESC], column_name2 [ASC | DESC], ...);

参数说明

• CREATE INDEX: 标准的创建索引命令。
• UNIQUE (可选): 指定创建唯一索引。唯一索引会强制要求索引列的组合值在表中不能重复（允许有 NULL 值，但具体行为可能因数据库而异）。
• index_name: 要创建的索引的名称。建议使用有意义的名称，如 idx_table_column。
• table_name: 要在其上创建索引的表的名称。
• (column_name1, column_name2, ...): 括号内列出要包含在索引中的一个或多个列。可以指定列的排序顺序 (ASC 升序或 DESC 降序)，但大多数数据库默认为 ASC，且对于 B-Tree 索引，顺序通常对查询优化器影响不大。

示例

假设有一个名为 employees 的表，包含 id, name, department, salary 等字段。

1. 创建普通索引 (加快按部门查询的速度)

   CREATE INDEX idx_employees_department ON employees (department);

2. 创建唯一索引 (确保员工姓名不重复)

   CREATE UNIQUE INDEX idx_employees_name ON employees (name);

   如果表中已有同名员工，此命令将失败。

3. 创建复合索引 (同时基于部门和薪水查询)

   CREATE INDEX idx_employees_dept_salary ON employees (department, salary);

   这个索引对 WHERE department = 'IT' AND salary > 5000 这样的查询非常有效。

重要注意事项

• 性能权衡: 索引能加速 SELECT 查询，但会减慢 INSERT, UPDATE, DELETE 操作，因为数据库需要同时维护索引。
• 选择合适的列: 通常在 WHERE 子句、JOIN 条件、ORDER BY 和 GROUP BY 中频繁使用的列上创建索引。
• 复合索引顺序: 复合索引中列的顺序很重要。查询条件需要使用索引的最左前缀才能有效利用索引（最左前缀原则）。
• 数据库差异: 不同的数据库系统（如 MySQL, PostgreSQL, Oracle, SQL Server）在语法细节和索引类型上可能略有差异。

验证索引

创建后，可以使用以下命令查看表的索引信息（具体命令因数据库而异）：

-- MySQL
SHOW INDEX FROM employees;
-- PostgreSQL
\d employees
-- SQL Server
sp_helpindex employees

总之，CREATE INDEX 是一个强大的工具，合理使用可以显著提升数据库查询性能。