SQL CREATE INDEX

SQL CREATE INDEX 语句用于在数据库表中创建索引。索引可以提高查询性能，因为它允许数据库系统快速定位数据，而不需要扫描整个表。

语法

创建普通索引

CREATE INDEX index_name
ON table_name (column1, column2, ...);

创建唯一索引

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

创建降序索引

CREATE INDEX index_name
ON table_name (column1 DESC, column2 ASC, ...);

创建全文索引

-- MySQL
CREATE FULLTEXT INDEX index_name
ON table_name (column1, column2, ...);

-- PostgreSQL
CREATE INDEX index_name
ON table_name USING gin(to_tsvector('english', column1));

示例

示例 1：创建普通索引

-- 为 students 表的 name 列创建索引
CREATE INDEX idx_students_name
ON students (name);

-- 为 students 表的 grade 和 age 列创建复合索引
CREATE INDEX idx_students_grade_age
ON students (grade, age);

示例 2：创建唯一索引

-- 为 students 表的 student_id 列创建唯一索引
CREATE UNIQUE INDEX idx_students_student_id
ON students (student_id);

示例 3：创建降序索引

-- 为 students 表的 age 列创建降序索引
CREATE INDEX idx_students_age_desc
ON students (age DESC);

-- 为 students 表的 grade 列创建升序索引，age 列创建降序索引
CREATE INDEX idx_students_grade_age
ON students (grade ASC, age DESC);

示例 4：创建全文索引

-- MySQL：为 articles 表的 title 和 content 列创建全文索引
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    publish_date DATE NOT NULL
);

CREATE FULLTEXT INDEX idx_articles_fulltext
ON articles (title, content);

-- 使用全文索引进行搜索
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 教程');

删除索引

DROP INDEX index_name ON table_name;

-- 或者在某些数据库系统中
ALTER TABLE table_name
DROP INDEX index_name;

索引的优点和缺点

优点

1. 提高查询性能：索引可以大大加快数据的检索速度，特别是对于大型表。
2. 加速排序和分组：使用索引可以加速 ORDER BY 和 GROUP BY 操作。
3. 强制唯一性：唯一索引可以确保列中的值唯一。

缺点

1. 增加存储空间：索引需要额外的存储空间。
2. 减慢写入操作：插入、更新和删除操作会导致索引的更新，从而减慢这些操作的速度。
3. 增加维护成本：索引需要定期维护，特别是对于频繁修改的表。

何时使用索引

1. 频繁查询的列：对于经常出现在 WHERE 子句中的列，应该创建索引。
2. 连接列：对于经常用于表连接的列，应该创建索引。
3. 排序和分组列：对于经常用于 ORDER BY 和 GROUP BY 的列，应该创建索引。
4. 唯一性约束：对于需要确保唯一性的列，应该创建唯一索引。

何时不使用索引

1. 小表：对于行数较少的表，索引的优势不明显。
2. 频繁修改的列：对于经常被修改的列，索引会增加维护成本。
3. 低选择性的列：对于值重复率高的列（如性别），索引的效果不佳。
4. TEXT 或 BLOB 列：对于大型文本或二进制数据列，索引的效果通常不好。

小结

SQL CREATE INDEX 语句是创建索引的基本语句，通过创建合适的索引，可以显著提高数据库查询性能。然而，索引也有其缺点，需要根据实际情况权衡使用。在设计数据库时，应该根据查询模式和数据特点，合理创建和管理索引。