结构化查询语言/索引管理
索引是所有 SQL 数据库的关键特性。它们提供对数据的快速访问。因此，几乎所有实现都支持 CREATE INDEX 语句。
然而，CREATE INDEX 语句不是 SQL 标准的一部分！其原因尚不清楚。可能是刻意避免所有实现问题。或者，它源于供应商实现的各种语法和缺乏找到妥协方案。
在本页，我们提供了一些关于索引的基本概念以及大多数实现中通用的语法。
CREATE [UNIQUE] INDEX <index_name> ON <table_name> (<column_name> [, <column_name>]);
DBMS 提供对存储在表中的数据的快速访问。人们可能会认为这种高速访问是由于现代计算机的快速硬件：每秒数百万个 CPU 周期，毫秒级的 I/O 速率，微秒或纳秒级的 RAM 访问等等。这是事实，但只是一部分。相反，智能软件算法的使用，特别是在处理大量数据时，是主要因素。
考虑向 DBMS 发出请求，以确定是否可以在包含 100 万个条目的表中找到一个特定姓名的人。使用原始的线性算法，系统必须读取平均 50 万行才能决定问题。该二分查找算法实现了一种更复杂的策略，该策略在读取 20 行或更少行后即可回答问题。在这种情况下，这种算法选择导致性能提升 25000 倍。为了真正理解这种改进的程度，您可能需要将您的薪水乘以 25000。
诚然，线性访问和二分查找算法之间的这种比较有点简单。首先，DBMS 通常读取包含多行而不是单行的块。但这并没有改变情况。如果一个块包含 100 行，将上面的例子从 100 万行修改为 1 亿行。其次，二分查找算法假设数据是有序的。这意味着在数据输入期间，需要额外的步骤将实际输入排序到现有数据中。这仅应用一次，并且与读取访问次数无关。总之，在数据输入期间有额外的工作，而在数据访问期间有更少的工作。这取决于数据的典型使用，以确定额外的工作是否值得。
索引是一个额外的存储，它保存从表中的原始数据复制或推导出的数据。它只包含冗余数据。索引包含哪些部分？在二分查找策略的常见情况下，索引保存表列的原始值以及指向原始行的反向引用。在大多数情况下，索引被组织成一个平衡树，其中列的值作为树的键，反向引用作为每个键的附加信息。
二分查找算法只是构建索引的众多选项之一。索引的共同特征是：它们仅包含冗余信息；在 CPU 周期、RAM 或磁盘空间方面使用额外的资源；并为大型数据集上的查询提供更好的性能。在小表或包含多个索引的表的情况下，索引的缺点（性能或资源使用方面）可能超过使用索引的好处。
如果一个应用程序根据某个条件检索数据 - 例如，电话簿应用程序中的个人姓名 - 并且该条件包含表的列，则该列应该有索引。
CREATE INDEX person_lastname_idx ON person(lastname);
索引有它自己的可自由选择的名称 - 例如person_lastname_idx - 并且是在特定表中的特定列上构建的。索引可以在 CREATE TABLE 语句之后直接定义和创建（当表中没有数据时）或者在一些或大量 INSERT 命令之后创建。创建之后，DBMS 应该处于能够比以前更快地回答以下问题的状态。
SELECT count(*)
FROM person
WHERE lastname = 'Miller';
索引可以在评估 WHERE 子句期间使用。DBMS 可以选择：一方面 - 读取所有person行并计算 lastname 为 'Miller' 的行；另一方面 - 读取索引（可能使用二分查找）并计算所有值为 'Miller' 的节点。使用哪种策略取决于很多决定。例如，如果 DBMS 知道大约 30% 的行包含 'Miller'，它可能会选择与知道只有 0.3% 的行包含 'Miller' 时不同的策略。
一个表可以有多个索引。
CREATE INDEX person_firstname_idx ON person(firstname);
在这种情况下，以下查询会发生什么？
SELECT count(*)
FROM person
WHERE lastname = 'Miller'
AND firstname = 'Henry';
同样，DBMS 有不止一种选择来检索预期的结果。它可以使用两个索引中的一个，读取结果行并查找缺少的另一个值。或者它读取两个索引并计算共同的反向引用。或者它忽略两个索引，读取数据并计算两个条件都适用的行。如前所述，这取决于很多决定。
如果一个应用程序通常在一个查询中搜索两列，例如姓名和姓氏，则为这两列构建一个索引可能很有用。这种策略与上面的例子有很大不同，在上面的例子中，我们为每列构建了两个独立的索引。
CREATE INDEX person_fullname_idx ON person(lastname, firstname);
在这种情况下，平衡树的键是姓氏和姓名的串联。DBMS 可以使用此索引来处理查询姓名和姓名的查询。它还可以使用此索引来处理仅查询姓氏的查询。但它不能在仅查询姓名的查询中使用此索引。姓名可以在平衡树的不同位置出现。因此，它对这种查询毫无用处。
在某些情况下，现有索引不能用于查询基础列。假设对姓名的查询应该是不区分大小写的。为此，应用程序将所有用户输入转换为大写并使用 UPPER() 函数来处理范围内的列。
-- Original user input was: 'miller'
SELECT count(*)
FROM person
WHERE UPPER(lastname) = 'MILLER';
由于 WHERE 子句中的条件只查找大写字符，并且索引是以区分大小写的方式构建的，因此平衡树中的键毫无用处：'miller' 排序的位置与 'Miller' 大不相同。为了解决这个问题，可以定义一个索引，它使用与 WHERE 条件完全相同的策略。
CREATE INDEX person_uppername_idx ON person(UPPER(lastname)); -- not supported by MySQL
现在，'UPPER()' 查询可以使用这种所谓的函数索引。
每个表的 主键都是唯一的，这意味着没有两行可以包含相同的值。有时，一列或几列的串联也是唯一的。要确保此条件，您可以定义一个唯一约束，或者您可以定义一个带有额外唯一条件的索引。（通常，唯一约束在后台默默地使用唯一索引。）
CREATE UNIQUE INDEX person_lastname_unique_idx ON person(lastname);
唯一索引只能在现有数据上创建，如果范围内的列确实只包含唯一的值（这在我们的数据库示例中并非如此）。
可以使用以下命令删除索引
DROP INDEX <index_name>;