数据库设计教程chap13

File organizations include Heap, Hash, Indexed Sequential Access Method (ISAM), B+-Tree, and Clusters. Some DBMSs (particularly PC-based DBMS) have fixed file organization that you cannot alter.
© Pearson Education Limited, 2004 4
Step 4.1 Analyze transactions
Use this information to identify the parts of the database that may cause performance problems. To select appropriate file organizations and indexes, also need to know high-level functionality of the transactions, such as:
© Pearson Education Limited, 2004 16
Step 4.3 Choose indexes – Guidelines for choosing ‘wish-list’
(1) Do not index small tables. (2) Index PK of a table if it is not a key of the file organization. (3) Add secondary index to any column that is heavily used as a secondary key. (4) Add secondary index to a FK if it is frequently accessed. (5) Add secondary index on columns that are involved in: selection or join criteria; ORDER BY; GROUP BY; and other operations involving sorting (such as UNION or DISTINCT).
transactions that run frequently and will have a significant impact on performance; transactions that are critical to the business; times during the day/week when there will be a high demand made on the database (called the peak load).
columns that are updated in an update transaction; criteria used to restrict records that are retrieved in a query.
© Pearson Education Limited, 2004 5
Chapter 13
Physical Database Design – Step 4 (Choose File Organizations and Indexes) Transparencies
© Pearson Education Limited, 2004
1
Chapter 13 - Objectives
Consists of the following steps: Step 4.1 Analyze transactions Step 4.2 Choose file organizations Step 4.3 Choose indexes
© Pearson Education Limited, 2004 3
column that is used most often for join operations - this makes join operation more efficient, or column that is used most often to access the records in a table in order of that column.
adding an index record to every secondary index whenever record is inserted; updating a secondary index when corresponding record is updated; increase in disk space needed to store the secondary index; possible performance degradation during query optimization to consider all secondary indexes.
Step 4.1 Analyze transactio expected transactions, so investigate most ‘important’ ones. To help identify which transactions to investigate, can use:
© Pearson Education Limited, 2004
10
Example Transaction Analysis Form
© Pearson Education Limited, 2004
11
Step 4.2 Choose file organizations
To determine an efficient file organization for each base table.
© Pearson Education Limited, 2004 2
Step 4 Choose file organizations and indexes
Determine optimal file organizations to store the base tables, and the indexes required to achieve acceptable performance.
© Pearson Education Limited, 2004
14
Step 4.3 Choose indexes
If ordering column chosen is key of table, index will be a primary index; otherwise, index will be a clustering index. Each table can only have either a primary index or a clustering index. Secondary indexes provide additional keys for a base table that can be used to retrieve data more efficiently.
© Pearson Education Limited, 2004 15
Step 4.3 Choose indexes
Have to balance overhead in maintenance and use of secondary indexes against performance improvement gained when retrieving data. This includes:
© Pearson Education Limited, 2004
13
Step 4.3 Choose indexes
Or could order records in table by specifying a primary or clustering index. In this case, choose the column for ordering or clustering the records as:
transaction/table cross-reference matrix, showing tables that each transaction accesses, and/or transaction usage map, indicating which tables are potentially heavily used.
© Pearson Education Limited, 2004
9
Step 4.1 Analyze transactions – Data usage analysis
For each transaction determine:
(a) Tables and columns accessed and type of access. (b) Columns used in any search conditions. (c) For query, columns involved in joins. (d) Expected frequency of transaction. (e) Performance goals of transaction.
© Pearson Education Limited, 2004 12
Step 4.3 Choose indexes
Determine whether adding indexes will improve the performance of the system.
One approach is to keep records unordered and create as many secondary indexes as necessary.