Visual FoxPro - 索引的优点和缺点 [英] Visual FoxPro - Pros and Cons of Indexing

问题描述

我很困惑我在vfp数据库中的每个表的主键和外键上创建索引的优点和缺点。

I'm confusing what are pros and cons of that I create indexes on primary key and foreign keys of every table in a vfp database.

我一般都知道如果表有索引，我们可以在相互连接和过滤记录时获得更好的性能。

但是当我在表上创建索引时，VFP在磁盘上创建一个新文件CDX。因此，如果我有10个主键索引表，我将有10个CDX文件，我将在磁盘上有20个数据文件。磁盘上需要更多空间。例如，

I generally know that if tables have indexes, we can get better performance when joining each other and filtering records.
But when I create an index on a table, VFP creates a new file CDX on disk. Thus, if I have 10 tables with primary key indexes, I will have further 10 CDX files and totally I will have 20 data files on the disk. It would take more spaces on disk. For example,

CREATE TABLE orders( ;
    odrid I AUTOINC PRIMARY KEY, ;
    cusid I, ;
    odrcode V(10) ;
)
INDEX ON cusid TAG cudid && index on FOREIGN KEY 

它将创建两个文件orders.dbf和orders.cdx。
是否值得在主键和外键上创建索引？
如果索引或没有索引，性能如何不同？

It would create two files orders.dbf and orders.cdx. Is it worth to create indexes on primary keys and foreign keys ? How would performance be different with indexing or without indexing ?

推荐答案

请注意索引不是免费 - 所以不要索引你不需要索引的内容。

Please note that indices are not "free" - so do not index what you do not need to index.

每次更改文件（添加，更改，删除）时，相关的索引记录也必须是addec /根据索引表达式和索引过滤条件更改/删除。

Each time a file is changed (add, change, delete), the associated index records must also be addec/changed/deleted according to the index expressions - and index filter conditions.

大多数情况下索引文件明显小于原始数据表 - 但是当你的索引开始上升时最终可能会有一个大于原始DBF的CDX文件。虽然磁盘空间很便宜 - 管理该空间内的数据会变得更加昂贵 - 特别是对于索引（因为原始DBF文件只是在表的末尾添加记录 - 索引必须进行更多操作以保持记录顺序） 。

Most often index files are significantly smaller than the original data table - but when your indices start to rise you can end up with a CDX file larger than the original DBF. While "disk space is cheap" - managing the data within that space can become more expensive - especially with indices (since the original DBF file just adds a record to the end of the table - indices must do more manipulation to keep records in order).

指数显然是高效设计的一个非常重要的组成部分，但不要过度指责所有内容，否则会对性能产生负面影响。

Indices are clearly a very important component of efficient design, but do not go overboard and index everything or it will begin to have a negative impact on performance.

这篇关于Visual FoxPro - 索引的优点和缺点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！