Research On Query Optimization Techniques Of Massive Data For Urban Rail Transit Network

In recent years, the amount of Guangzhou urban rail lines increases rapidly, the construction of urban rail transit network becomes extremely urgent. Thanks to the massive production data in Guangzhou urban rail system, the database of Guangzhou urban rail transit network will be very large and the records of a single big table will reach billions. Without optimization, the response time of queries will be too long to wait, and the massive data will exceed the storage and processing capacity of a single database which leading to the sharp decline in the query efficiency. So it’s necessary to make a research on query optimization techniques of massive data.Based on Oracle database, this thesis makes a comprehensive research on query optimization techniques of massive data. Firstly, this thesis analyzes and summarizes the characteristics of data and queries in urban rail transit network, and analyzes the factors which have an influence on the query efficiency of urban rail transit network, and then it proposes the query optimization strategy, that is, optimizing the queries in a single database by making use of the optimization techniques in Oracle, such as table partitioning, index, materialized view and SQL optimization, and storing the data in distributed databases by adding more database nodes which reduces the storage and processing pressure of each single database and further improves the query efficiency by utilizing the parallel processing capability of multiple databases. Then, the applicable situations and application methods of optimization techniques mentioned earlier are discussed in detail, and this thesis optimizes some typical queries of urban rail transit network by using these techniques.To make it possible to access the data stored in multiple databases transparently, according to the business characteristics of urban rail transit network, this thesis designs and implements a light-weight, multi-database access middleware. This thesis designs the overall structure of the middleware which is divided into the following five functional modules, that is, communication module, query parsing module, query routing module, database access module and results merging module, and the requirements, design and implementation of each of these modules are analyzed at length. This thesis mainly solves the data sharding, the communication between clients and server, SQL parsing, query routing and query results merging crossing multiple databases problems, and finally completes the development and test of the middleware. One of the middleware’s main characteristics is that data is sharded by urban rail lines, it helps to avoid the expensive table joins crossing databases and the system can be easily extended just by adding more database nodes.The test results shows that after optimizing by using these previously mentioned techniques comprehensively, the speed of queries in urban rail transit network obviously improves a lot. Therefore, these query optimization techniques is very suitable for optimizing queries of massive data in urban rail transit network, and this thesis possesses applicable value.