结构化查询语言(Structured Query Language)
索引是所有SQL数据库的重要功能,用于加快数据访问。因此,几乎所有的实现都支持 CREATE
INDEX
语句。
然而,CREATE INDEX
并非SQL标准的一部分。这可能是由于实现中的复杂性问题,或者因为不同厂商实现的语法差异太大,难以找到统一的标准。本页将介绍索引的基本概念和广泛支持的语法。
创建索引语法
CREATE [UNIQUE] INDEX <index_name> ON <table_name> (<column_name> [, <column_name>]);
索引的概念
数据库管理系统(DBMS)为表中的数据提供了快速访问能力。虽然现代计算机硬件性能极高,但实现快速访问的核心在于高效的软件算法。
例如,在一个有100万条记录的表中,查找某个人名。如果使用简单的线性搜索算法,平均需要检查50万行才能得到结果。而二分查找算法只需读取20行甚至更少,性能提升高达25,000倍。
二分查找依赖数据有序存储,这意味着在插入数据时需要额外排序。然而,这种额外开销仅在数据输入时发生,而不会影响数据访问性能。因此,对于常用的查询场景,这种额外工作是值得的。
索引的特点:
- 索引是一种冗余存储,包含从表中复制或派生的数据。
- 索引通过附加资源(如CPU、内存或磁盘空间)实现性能提升。
- 在数据量较小或索引过多的情况下,索引的开销可能超过其带来的性能收益。
基本索引
如果查询经常基于某个字段的条件,例如电话簿应用按人名检索电话,则应为该字段创建索引:
CREATE INDEX person_lastname_idx ON person(lastname);
效果:
-
创建索引后,查询效率显著提升,例如:
SELECT count(*) FROM person WHERE lastname = 'Miller';
-
索引的选择取决于数据库管理系统(DBMS)的内部决策。例如,如果约30%的记录都包含 'Miller',可能选择不使用索引。
多列索引: 可以为多列创建单独的索引,例如:
CREATE INDEX person_firstname_idx ON person(firstname);
当查询包含多个条件时,DBMS会选择适当的策略,例如:
SELECT count(*)
FROM person
WHERE lastname = 'Miller' AND firstname = 'Henry';
多列复合索引
当查询经常同时使用多列条件时,可以为多个列创建一个复合索引:
CREATE INDEX person_fullname_idx ON person(lastname, firstname);
效果:
- 该索引适用于按
lastname
和firstname
的联合查询,也可以用于仅按lastname
查询。 - 但该索引无法用于仅按
firstname
的查询,因为firstname
在平衡树中的位置不确定。
函数索引
如果查询中使用了函数处理字段(如不区分大小写),常规索引可能失效。为此,可创建函数索引:
CREATE INDEX person_uppername_idx ON person(UPPER(lastname)); -- MySQL 不支持
示例查询:
SELECT count(*)
FROM person
WHERE UPPER(lastname) = 'MILLER';
此索引可优化上述查询。
唯一索引
唯一索引确保列值唯一,类似于UNIQUE CONSTRAINT
:
CREATE UNIQUE INDEX person_lastname_unique_idx ON person(lastname);
- 如果创建唯一索引的列已有重复值,则创建失败。
- 唯一索引常用于需要确保列值唯一的场景。
删除索引
索引可以通过以下命令删除:
DROP INDEX <index_name>;
总结
索引是一种重要的优化工具,可以显著提升查询性能。然而,索引的使用需要权衡开销与收益,过多的索引可能影响写操作性能。在实际应用中,应根据具体场景选择合适的索引策略。