Ms Access 简明教程

MS Access - Indexing

索引是一种数据结构，一种特殊的数据结构，旨在提高数据检索速度。如果您经常按特定字段搜索表或按该字段对记录进行排序，则可以通过为该字段创建索引来加快这些操作。Microsoft Access 在表中使用索引的方式与您在书中使用索引查找数据的方式相同。

An index is a data structure, a special data structure designed to improve the speed of data retrieval. If you often search a table or sort its records by a particular field, you can speed up these operations by creating an index for the field. Microsoft Access uses indexes in a table as you use an index in a book to find data.

  1. In some instances, such as for a primary key, Access automatically creates an index for you.

  2. At other times, you might want to create an index yourself.

  3. An index stores the location of records based on the field or fields that you choose to index.

  4. After Access obtains the location from the index, it can then retrieve the data by moving directly to the correct location.

  5. In this way, using an index can be considerably faster than scanning through all of the records to find the data.

  6. Indexes can speed up searches in queries, but they can also slow down performance when adding or updating records.

现在让我们来了解 Microsoft Access 默认创建的索引以及我们如何自己创建索引，并学习删除任何不必要的索引。在 Access 数据库中打开我们创建的 tblEmployees 表。

Let us now look into what indexes Microsoft Access creates by default and how to create them ourselves and learn how to delete any unnecessary indexes. Open the tblEmployees table in Access database which we have created.

  1. We haven’t really played with indexes in this database but that doesn’t mean we don’t have any.

  2. In fact, any field that is set as a primary key in Access is automatically indexed.

  3. Access creates additional secondary indexes depending on the names of your fields.

现在，让我们转到“文件”菜单，然后选择“选项”。

Let us now go to the File menu and select Options.

file menu

您将看到“Access 选项”窗口。

You will see the Access Options window.

object designer

转到 Object Designers ，您将看到一个标题为“导入/创建自动编制索引”的部分，并在文本框中看到 ID;key;code;num。默认情况下，Access 会自动为以这些名称开头或结尾的字段添加二级索引，这适用于您导入的字段以及您手动创建的字段。

Go to the Object Designers and you will see a section labeled AutoIndex on Import/Create and in the textbox you will see ID;key;code;num. By default, access automatically adds a secondary index to fields that start or end with these names and that goes for fields you have imported as well as ones you have manually created.

如果要使任何字段建立索引，可以转到 Field 选项卡。

If you want to make any field indexed you can go to the Field tab.

indexed

选择您希望建立索引的任何字段，然后选中“字段验证”部分中的“已建立索引”复选框。您还有替代选项可以创建或删除索引。您可以返回 Design View 。

Select any field that you want indexed and check the Indexed checkbox in Field Validation section. You also have alternate options for creating or removing an index. You can go back to the Design View.

design view

您可以通过选择任何字段来调整索引。您还可以在下面的字段属性区域中查看它们的建立索引方式。具有 No 下拉菜单项旁边选定的任何字段表示该指定字段没有索引。您可以通过单击下拉菜单并选择其他两个选项（ Yes (Duplicate OK) 和 Yes (No duplicates) ）来更改该选项。

You can adjust indexes by selecting any field. You can also see how they are indexed in the field properties area below. Any field that has No selected next to indexed, means there is no index for that given field. You can change that by clicking on the drop-down menu and choosing the other two options — Yes (Duplicate OK) and Yes (No duplicates).

compression

最后一个选项 Yes (No Duplicates) 表示 Access 将自动禁止该字段中的重复值。现在让我们为我们的姓氏字段创建索引。

The last option Yes (No Duplicates) means that Access will automatically prohibit duplicate values in that field. Let us now create an index for our last name field.

duplicate

让我们选择 LastName 建立索引并说 Yes (Duplicates OK) 。当我们保存时，Access 将创建该索引。您可以查看和调整表索引的另一个区域是 Show/Hide 组中的“表设计”区域。

Let us select LastName to index and say Yes (Duplicates OK). As we save, Access will create that index. Another area where you can view and adjust your indexes for a table is the tables design area in the Show/Hide group.

duplicate yes

如果您单击此“索引”按钮，则会弹出一个特殊视图，显示针对该表创建的所有索引。

If you click on this Indexes button that will bring up a special view displaying all the indexes created for this table.

special view

我们现在有两个 tblEmployees 索引，一个基于主键字段自动创建，另一个是我们刚为 LastName 字段创建的。这些是在 Microsoft Access 表中处理索引的不同方式。

We now have two indexes for tblEmployees —one that was created automatically based on the primary key field and one that we just created for the LastName field. These are the different ways to deal with indexes in Microsoft Access tables.