SQL CREATE INDEX 语句

在本教程中,你将学习如何在表上创建索引,以提高数据库的性能。

什么是索引

索引是与表关联的数据结构,该表基于一个或多个列(索引键)中的值提供对表中行的快速访问。

假设你在数据库中有一个 customers 表,并且你希望使用以下语句找出名称以字母 A 开头的所有客户。

SELECT cust_id, cust_name, address FROM customers 
WHERE cust_name LIKE 'A%';

要查找此类客户,服务器必须在 customers 表中逐个扫描每一行,并检查名称列的内容。虽然它适用于行数较少的表,但想象如果表包含数百万行,则可能需要多长时间才能回答查询。在这种情况下,你可以通过将索引应用于表来加快速度。

创建索引

你可以使用以下 CREATE INDEX 语句创建索引 :

CREATE INDEX index_name ON table_name (column_name); 

例如,要在 customers 表的 name 列上创建索引,可以使用:

CREATE INDEX cust_name_idx ON customers (cust_name);

默认情况下,索引将允许重复条目并按升序对条目进行排序。要要求唯一索引条目,请在 UNIQUE 之后添加关键字 CREATE ,如下所示:

CREATE UNIQUE INDEX cust_name_idx 
ON customers (cust_name);

在 MySQL 中,你可以查看特定表上的可用索引,如下所示:

mysql> SHOW INDEXES FROM customers \G 

提示:\G 终止一个 SQL 语句,而不是用 ;,这样假如他们对于当前窗口太宽的话,将垂直显示结果,而不是正常的表格格式。

创建多列索引

你还可以构建跨多个列的索引。例如,假设你的数据库中有一个名为 users 的表,其中包含 first_namelast_name 列,并且你经常使用这些列访问用户的记录,那么你可以在两个列上一起构建索引以提高性能,如下所示:

CREATE INDEX user_name_idx ON users (first_name, last_name); 

提示: 你可以将数据库索引视为书籍的索引部分,以帮助你快速查找或查找书籍中的特定主题。

索引的缺点

应谨慎创建索引。因为,每次在表中添加,更新或删除行时,都必须修改该表上的所有索引。因此,你拥有的索引越多,服务器需要执行的工作就越多,最终会导致性能降低。

以下是创建索引时可以遵循的一些基本准则:

  • 你经常用于检索数据的索引列。
  • 不要为从未用作检索键的列创建索引。
  • 用于连接的索引列,以提高连接性能。
  • 避免使用包含太多 NULL 值的列。

此外,小表不需要索引,因为在小表的情况下,服务器扫描表通常更快,而不是首先查看索引。

注意: 创建表时,大多数数据库系统(如 MySQL,SQL 服务器等)会自动为 PRIMARY KEYUNIQUE 列创建索引。

删除索引

你可以使用以下语句删除不再需要的索引。

DROP INDEX index_name ON table_name ; 

下面的语句将从 customers 表删除索引 *CUST_NAME_IDX*。

DROP INDEX cust_name_idx ON customers;

此外,如果删除表,则还会删除所有关联的索引。

警告: 你应该在删除索引之前彻底调查。作为一般经验法则,不要盲目地创建或删除索引。