Hsqldb 简明教程
HSQLDB - Indexes
database index 是一个数据结构,它提高了表中操作的速度。可以使用一列或多列创建索引,为快速随机查找和高效排序对记录的访问提供基础。
A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to the records.
在创建索引时,应考虑哪些列将用于创建 SQL 查询,并在这些列上创建一列或多列索引。
While creating an index, it should be considered what are the columns which will be used to make SQL queries, and create one or more indexes on those columns.
实际上,索引也是一种表,它保留主密钥或索引字段以及指向实际表中每个记录的指针。
Practically, indexes are also type of tables, which keep the primary key or the index field and a pointer to each record into the actual table.
用户看不到索引。它们仅用于加速查询,并将由数据库搜索引擎使用来快速查找记录。
The users cannot see the indexes. They are just used to speed up queries and will be used by the Database Search Engine to quickly locate records.
对具有索引的表,INSERT 和 UPDATE 语句需要更多时间,而 SELECT 语句在这些表上运行得更快。原因是在插入或更新时,数据库也需要插入或更新索引值。
The INSERT and UPDATE statements take more time on tables having indexes, whereas SELECT statements run faster on those tables. The reason being while inserting or updating, the database needs to insert or update the index values as well.
Simple & Unique Index
您可以在表上创建唯一索引。 unique index 表示两行不能具有相同的索引值。以下是创建表索引的语法。
You can create a unique index on a table. A unique index means that two rows cannot have the same index value. Following is the syntax to create an Index on a table.
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2,...);
可以使用一个或多个列创建索引。例如,使用 tutorial_author 在 tutorials_tbl 上创建索引。
You can use one or more columns to create an index. For example, create an index on tutorials_tbl using tutorial_author.
CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author)
可以在表上创建简单索引。从要创建简单索引的查询中省略该 UNIQUE 关键字。 simple index 允许表中存在重复值。
You can create a simple index on a table. Just omit the UNIQUE keyword from the query to create a simple index. A simple index allows duplicate values in a table.
如果您想按降序对某列中的值进行索引,您可以在列名后添加保留字 DESC。
If you want to index the values in a column in a descending order, you can add the reserved word DESC after the column name.
CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author DESC)
ALTER Command to Add & Drop INDEX
有四类用于向表中添加索引的语句 −
There are four types of statements for adding indexes to a table −
-
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) − This statement adds a PRIMARY KEY, which means that indexed values must be unique and cannot be NULL.
-
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) − This statement creates an index for which the values must be unique (with the exception of NULL values, which may appear multiple times).
-
ALTER TABLE tbl_name ADD INDEX index_name (column_list) − This adds an ordinary index in which any value may appear more than once.
-
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) − This creates a special FULLTEXT index that is used for text-searching purposes.
以下是添加到现有表的索引的查询。
Following is the query to add index in an existing table.
ALTER TABLE testalter_tbl ADD INDEX (c);
你可以使用 DROP 子句和 ALTER 命令来删除任何索引。以下是删除上述创建的索引的查询。
You can drop any INDEX by using the DROP clause along with the ALTER command. Following is the query to drop the above-created index.
ALTER TABLE testalter_tbl DROP INDEX (c);
Displaying INDEX Information
你可以使用 SHOW INDEX 命令列出与表关联的所有索引。此语句经常会使用垂直格式输出(通过 \G 指定),以避免长行换行。
You can use the SHOW INDEX command to list out all the indexes associated with a table. Vertical-format output (specified by \G) often is useful with this statement, to avoid long line wraparound.
以下是显示表索引信息的通用语法。
Following is the generic syntax to display the index information about a table.
SHOW INDEX FROM table_name\G