mysql CREATE INDEX 语句

说明

此语句用于创建指定表的索引。索引可以提高数据库的性能。

语法

CREATE [ VIRTUAL ] [ UNIQUE ] [ CLUSTERED ] INDEX index-name
ON [ owner.]table-name
( column-name [ ASC | DESC ], ...
| function-name ( argument [ ,... ] ) AS column-name )
[ { IN | ON } dbspace-name ]

参数

VIRTUAL 关键字 VIRTUAL 关键字主要由索引顾问使用。在索引顾问对查询计划进行求值期间以及使用 PLAN 函数时，虚拟索引将模拟真实物理索引的属性。可以结合使用虚拟索引和 PLAN 函数以充分利用索引在性能方面的提高，从而避免创建真实索引这一通常又耗时又消耗资源的过程。

虚拟索引对其它连接是不可见的，并且在关闭连接时删除。在对查询的实际执行计划进行求值时不使用虚拟索引，所以不会对性能造成影响。

虚拟索引的列数限制为四列。

有关详细信息，请参见启动索引顾问和索引顾问概述。

CLUSTERED 关键字 CLUSTERED 属性使表行按照与索引对应的大概键顺序来存储。当服务器尝试保留键顺序时，不保证实施总体群集。

如果存在聚簇索引，LOAD TABLE 语句便按照索引键的顺序在表中插入行，而 INSERT 语句将按照键顺序的定义尝试将新行添加到包含相邻行的表页上。

有关详细信息，请参见使用聚簇索引。

UNIQUE 关键字 UNIQUE 属性确保表中不会有两行的值在所有索引列中相同。每个索引键必须是唯一的，或者至少在一列中包含 NULL。

表的唯一约束和唯一索引是有区别的。唯一索引的列可以为 NULL，而唯一约束的列不能为 NULL。外键可以引用主键或者具有唯一约束的列，但不能引用具有唯一索引的列，因为唯一索引可以包括多个 NULL 实例。

ASC | DESC 选项除非显式指定降序 (DESC)，否则列以升序 (increasing) 排序。不管索引是升序排列还是降序排列，在执行升序或降序 ORDER BY 操作时都会使用索引。但是，如果通过混合的升序和降序属性来执行 ORDER BY，则仅当索引是用同样的升序和降序属性创建的时才使用索引。

function-name 参数 function-name 参数为内置函数创建索引。CREATE INDEX 语句的这种格式是执行以下操作的便捷方法：

向表中添加名为 column-name 的计算列。该列通过指定为内置函数的 COMPUTE 子句以及任何所指定的参数来定义。该列的数据类型依函数的返回类型而定。
填充表中现有行的计算列。
创建列的索引。

删除索引不会造成相关联的列被删除。CREATE INDEX 的这一格式不可用于所声明的临时表。

有关列计算列的详细信息，请参见使用计算列

IN | ON 子句缺省情况下，索引放在它的表所在的同一个数据库文件中。通过指定用来存放索引的 dbspace 名称，可以将索引放在单独的数据库文件中。此特性主要用于帮助大型数据库克服文件大小限制。

有关限制的详细信息，请参见大小和数量限制。

用法

CREATE INDEX 语句在指定表的指定列上创建排序索引。索引自动用于改进向数据库发出的查询的性能，以及通过 ORDER BY 子句对查询排序。索引一旦创建，在 SQL 语句中就不会再引用它，除非要校验 (VALIDATE INDEX) 或删除 (DROP INDEX) 它。

不能创建视图索引。

索引所有权在 CREATE INDEX 语句中无法指定索引所有者。索引总是归表的所有者所拥有。索引名称对每个所有者必须是唯一的。
不存在视图索引无法为视图创建索引。
索引名称空间每个索引的名称对给定的表必须是唯一的。
排它表使用只要 CREATE INDEX 影响了当前正由其它连接使用的表，就会禁止该语句。CREATE INDEX 可能很耗时，服务器在处理该语句时不会处理引用同一个表的请求。
自动创建的索引 Adaptive Server Anywhere 自动创建主键和唯一约束的索引。这些自动创建的索引保存在表所在的同一数据库文件中。

权限

必须是表的所有者，或者有 DBA 权限或 REFERENCES 权限。

副作用

自动提交。为内置函数创建索引也会生成一个检查点。

更新列统计信息。

另请参见

标准和兼容性

SQL/92 供应商扩展。
SQL/99 供应商扩展。
Sybase Adaptive Server Enterprise 的 CREATE INDEX 语句比 Adaptive Server Anywhere 的复杂。虽然在 Adaptive Server Anywhere 中允许使用 Adaptive Server Enterprise 语法，但一些子句和关键字会被忽略。
- Adaptive Server Enterprise 11.5 的完整语法如下：
  
  CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]
  INDEX index-name
  ON [ [ database.]owner.]table_name
        (column_name [, column_name], ...)
  [ WITH {
     {  FILLFACTOR | MAX_ROWS_PER_PAGE } = x,
         CONSUMERS = x,
     ... IGNORE_DUP_KEY,
     ... SORTED_DATA,
         [ IGNORE_DUP_ROW | ALLOW_DUP_ROW ]
     } ]
  [ ON segment_name ]
Adaptive Server Anywhere 允许使用（通过忽略）以下关键字：
- FILLFACTOR
- IGNORE_DUP_KEY
- SORTED_DATA
- IGNORE_DUP_ROW
- ALLOW_DUP_ROW
在 Adaptive Server Enterprise 和 Adaptive Server Anywhere 中，索引的物理放置方式是不同的。Adaptive Server Anywhere 支持 ON segment-name 子句，但 segment-name 引用 dbspace。

Adaptive Server Anywhere 中的唯一索引允许包含 NULL，其它方面都相同。Adaptive Server Enterprise 中的唯一索引允许最多一个空值的行，不允许重复的行。

对于 Adaptive Server Anywhere 和 Enterprise，给定表的索引名称必须都是唯一的。

示例

为 employee 表创建一个有两列的索引。

CREATE INDEX employee_name_index
ON employee
( emp_lname, emp_fname )

为 sales_order_items 表的 prod_id 列创建索引。

CREATE INDEX item_prod
ON sales_order_items
( prod_id )

使用 SORTKEY 函数为 product 表的 description 列创建索引，排序顺序根据俄语归类而定。该语句有一个副作用，向表中添加一个计算列 desc_ru。

CREATE INDEX ix_desc_ru
ON product (
 SORTKEY( description, 'rusdict' )
 AS desc_ru )