Partition-based Network Behavior Monitoring System Database Design And Optimization Studies

21st century is the information age, and database technology is applied in various fields widely. The rapid development of Internet promotes the intense changes in the database. In a huge data environment, only the single software can not meet the needs any more, while a stable and efficient data layer solution is even more important and receives much concern.The source of this dissertation is a B/S-enterprise-based network behavior monitoring system. This project is developed on LAMP (Linux+Apache+Mysql+PHP) platform. The processing speed of MySQL in Linux is generally acknowledged as fastest currently, and massive network behavior records monitored are stored in MySQL. However, with a demand of frequently reading and writing huge amount of data, the main task of this thesis is to design and optimize the database, so that the database can remain running stablely and efficiently.In order to ensure the query efficiency, a database architecture based on static and active data partition is designed in this dissertation. Firstly, the mass data is separated into the static data and active data, which are also called as real-time ones and historical ones, and the copy, which is from real-time to historical one, are implemented through timing task dispatching. Then both the two kinds of data are separated into many partitions based on time granularity. The fundamental data, which is small and accessed frequently, are stored using memory engine and myisam engine. This solution greatly improves the performance of table join operation. In monitoring policy function module, in order to make the administrator operation easily, the control policies are applied using template approach, and group priority are implemented. Therefore, an appropriate and efficient database Schema is designed.In the aspect of Query optimizing, through analyzing the explain plan of query statements, which are used frequently, some appropriate indexes are designed firstly. As to the data columns not suitable for indexing, a data classification model is designed in this thesis, the data are divided into several small two-dimensional and one-dimensional data subset. These subset are stored as views, the original statement achieves recombinant by matching the corresponding data subset. The test result shows that it has avoided the response delay that caused by full table scan after query optimization. The query efficiency has been greatly improved, which proves the feasibility of this optimizing approach.