校园网数据库的性能优化技术及其应用

【作者】 邓小善；

【导师】 罗大庸；

【作者基本信息】 中南大学 ， 计算机应用技术， 2008， 硕士

【摘要】 校园网数据库具有多媒体、异构、查询频繁、数据量大等特点,随着校园网应用的深入,校园网数据库的数据量变得越来越庞大,数据库的访问性能因此而显著降低。如何优化由结构化数据组成的校园网数据库以提升其性能,如何合理设计多媒体数据库的结构以提升其查询效率,如何有针对性地设计校园网数据库的查询算法,对于这些方面的研究,既具有理论意义,更具有实践指导意义。数据库逻辑设计优化的主要方法是使数据库结构设计尽量满足第三范式。本文在阐述利用规范化设计来优化数据库性能的基础之上,为了减少数据库的多表连接,避免频繁使用聚集函数,优化数据访问频率差别较大的大容量数据库的性能,提出了有针对性的反范式设计方法来优化由结构化数据组成的校园网数据库。同时,通过分析校园网中多媒体数据库的特点,研究了基于数据分类与扩充关系模型来构建多媒体数据库的方法,此方法操作简单,性能良好。提升数据库的查询性能可以通过建立合适的索引、利用存储过程、优化SQL语句等方法来实现。本文在分析了查询优化方法的基础之上,为解决大容量数据库中统计分析查询十分缓慢的问题,根据校园网数据库的查询重复性强、数据更新时间有规律性等特点,研究了基于语义信息与择机预取的查询优化算法,它通过保存、分析重复性的查询语义信息,择机预取访问频率高的数据到Cache中,达到较好地提升查询速度的目的。本文以湖南省教育厅资助科研项目为课题来源,在校园网数据库的性能优化实践中,利用性能监控与分析工具分析数据库性能状况,利用规范化与反规范化技术改进数据库的结构设计,利用基于数据分类与扩充关系模型的方法重构多媒体数据库的逻辑设计,利用基于语义信息与择机预取的查询优化算法改善大容量数据库的统计分析查询性能。优化实践后的测试结果表明,校园网数据库性能得到了较大幅度的提升。更多还原

【Abstract】 Campus network database usually takes on such characteristics as multi-media, heterogeneous, frequent queries,and a large quantity of data. But with the deepening of the campus network applications, the campus network database has become an ever-growing volume of data, resulting in a significant reduction in the database performance. It has theoretical significance as well as practical significance to know how to optimize the performance of the campus network database which is on the composition of structured data to enhance its performance, and how to reasonably design the structure of multi-media database to enhance the efficiency of its inquiry, as well as how to pertinently design query algorithm of the campus network database.The main way of optimization for database logic design is to design the right database structure to meet the Third Normal Form. On the base of expatiating the use of normalized designs to optimize database performance, and in order to reduce the number of multi-table joins and avoid frequently using the clustering function,furthermore, in order to optimize the performance of large-capacity databases among which there are large differences in the frequency of visits, the author of this paper puts forward denormalization design methods to optimize campus network database which are composed by structured data. Meanwhile, by analyzing the features of the multi-media database in the campus network, the author also adopts method which is based on data classification and expansion of relation models to builde a multi-media database. This method is easily operated and its capability is good.As we know, we can upgrade the database query performance by establishing an appropriate index, using the stored procedures and optimizing SQL statements. The author firstly analyzes the optimization methods of query. Secondly, according to the features of campus network database such as the queries repeatability is strong, there are some laws in updating the data, and in order to resolve the problem that the high-capacity database query of the statistical analysis is very slow, the author brings forward a method of query optimization algorithm which is based on semantic information and choosing a right chance to prefetch. By preserving, analyzing the repetitive semantic information, it will choosing a right chance to prefetch to check the high-frequency data to the Cache to successfully achieve the purpose of enhancing the speed of inquiry.The source of this article is A Project Supported by Scientific Research Fund of Hunan Provincial Education Department. In the practice of the performance optimization of campus network database, we can use performance monitoring and analysis tools to analyze the database performance status, or use the normalized technology and denormalized technology to improve structure design, or use the method based on data classification and the expansion of relation models to reconstruct logic design of multi-media database, or use the method of query optimization algorithm based on the semantic information and choosing the right chance to prefetch to improve the query performance of the statistical analysis in large-capacity databases. In the practice of optimizing the performance of it, the result indicates that the performance of the campus network data has been increased dramatically.更多还原