arXivDaily arXiv每日学术速递 周一至周五更新
arXiv 2501.07449cs.DBcs.PF
收藏

论逻辑数据库设计对数据库规模、查询复杂度、查询性能与能耗的影响

On the effects of logical database design on database size, query complexity, query performance, and energy consumption

Toni Taipalus

AI总结:

本研究基于IMDb数据集与PostgreSQL开展实证分析,探究数据库规范化对规模、查询复杂度、性能与能耗的影响,发现1NF到2NF优化收益显著,2NF到4NF收益甚微且增加存储需求。

摘要:

数据库规范化理论是关系数据库逻辑设计的基础。规范化可减少数据冗余,进而消除潜在的数据异常,但会增加读操作的计算成本。尽管规范化理论已应用数十年,但其对数据库规模和效率的影响程度在很大程度上仍不明确。本研究使用Internet Movie Database(IMDb,互联网电影数据库)公开数据集与PostgreSQL,探究数据库规范化的影响。结果相当直观地表明:(i) 从1NF(第一范式)规范化到2NF(第二范式)时,磁盘上的数据库规模减少10%,但从2NF到4NF(第四范式)时无此效果;(ii) 表的总数与表行总数从1NF到2NF再到4NF单调递增;(iii) 查询复杂度随规范化程度加深而提升。但令人意外的是,结果还显示:(iv) 从1NF规范化到2NF使吞吐量提升至原来的4倍;(v) 相应地,从1NF到2NF的规范化使每笔事务的能耗降低74%。结果表明,从2NF到4NF的规范化在吞吐量和能耗方面的收益极小,却会使存储空间需求增加约7%。尽管这些结果仅代表一个特定案例,但它们为数据库规范化的实际效果与影响幅度提供了所需的实证评估。

英文摘要:

Database normalization theory is the basis for logical design of relational databases. Normalization reduces data redundancy and consequently eliminates potential data anomalies, while increasing the computational cost of read operations. Despite decades worth of applications of normalization theory, it still remains largely unclear to what extent normalization affects database size and efficiency. In this study, we study the effects of database normalization using the Internet Movie Database (IMDb) public dataset and PostgreSQL. The results indicate, rather intuitively, that (i) database size on disk is reduced through normalization from 1NF to 2NF by 10%, but not from 2NF to 4NF, (ii) the number of tables and table rows in total increase monotonically from 1NF to 2NF to 4NF, and that (iii) query complexity increases with further normalization. Surprisingly, however, the results also indicate that (iv) normalization from 1NF to 2NF increases throughput by a factor of 4, and consequently, (v) energy consumption per transaction reduces by 74% with normalization from 1NF to 2NF. The results imply that the gains of normalization from 2NF to 4NF in terms of throughput and energy consumption are minimal, yet increase the storage space requirements by approximately 7%. While these results represent merely one specific case, they provide needed empirical evaluation on the practical effects and magnitude of database normalization.

机构、总结、中文摘要均由AI分析而来,可能存在错误,请以论文正文为准。

↑