辽宁石油化工大学计算机与通信工程学院.pptVIP

数据库管理系统Database Management Systems Chapter 11 Physical Design 第11章 物理设计 Performance Issues All access to the data is routed through the DBMS. Additional layer could slow retrieval and storage. Programs cannot directly control access to the data Any access optimization must reside within the DBMS. Most common method: Index. Physical Data Storage Some database systems let the designer choose how to store data. Rows for each table. Columns within a table. The choice influences performance and storage requirements. The choice depends on the characteristics of the data being stored. Index Most database systems use an index to improve performance. Several methods can be used to store an index. An index can speed data retrieval. Maintaining many indexes on a table can significantly slow down data updates and additions. Choose indexes carefully to speed up certain large jobs. Table Operations Retrieve data Read entire table. Read next row/sequential. Read arbitrary/random row. Store data Insert a row. Delete a row. Modify a row. Reorganize/pack database Remove deleted rows. Recover unused space. Deleting Data Deletes are flagged. Space is reused if possible when new row is added. If not exactly the same size, some blank holes develop. Packing removes all deleted data and removes blanks. Data Storage Methods Sequential Fast for reading entire table. Slow for random search. Indexed Sequential (ISAM) Better for searches. Slow to build indexes. B+-Tree Similar to ISAM. Efficient at building indexes. Direct / Hashed Extremely fast searches. Slow sequential lists. Sequential Storage Common uses When large portions of the data are always used at one time. e.g., 25% When table is huge and space is expensive. When transporting / converting data to a different system. Operations on Sequential Tables Read entire table Easy and fast Sequential retrieval Easy and fast for one order. Random Read/Sequential Very weak Probability of any row = 1/N Sequential retrieval 1,000,000 rows means