| With the rapid development of business,the amount of data and the visits are on the rise,increasing the pressure on the database to read and write. Database performance has become one of the important site performance bottlenecks. Replace to the better performance of disk for read and write is a relatively simple solution when we can afford to pay the cost of hardware and the hardware does not reach the single bottleneck. But this is not the ultimate solution, the hardware performance limits, and the stand-alone hardware can not solve the distributed application deployment issues. Using a distributed architecture, the data will be split vertically and horizontally, and the data and access of databases are split to multiple databases, provide support separately, so that we can respond to the pressure brought by the business development on the database.The DAL that Data Access Layer is the distributed storage access middleware based on mysql and provides proxy access of mysql database. The user can use DAL in the same way of mysql. DAL implements asynchronous NIO, it can accept a large number of client requests. Each request is divided into several sql processing state, to achieve the pipelined processing of finite state machine type. Then we use connection pooling to access data distributed in multiple databases through a sub-library sub-table approach, while DAL achieve diversion, separate read and write, to reduce the pressure of the main library to read and write. By this way, DAL can achieve concurrent access control, clipping and other functions that mysql cann’t support, so that we can solve the pressure that the business growth brings to the database. Meanwhile, DAL integrated etrace to enable SQL tracing and SQL requests monitoring.In this paper, we do something to introduce the design and implementation for the sql process module as the sub-module of DAL. Firstly, we analyze the requirements. Then we introduce the requirement of several modules including sql’s analysis and control, sql parsing, sql routing table, sql execution set and sql task’s scheduling. Finally, we give a detailed analysis of the sql routing table and sql task’s scheduling modules, by analyzing their specific design and implementation through the ways of the code and of design.Using the distributed access middleware based on mysql,we can solve the problem of high concurrent access in a great extent, improve the system’s concurrent access ability and speed of response. While we can use inexpensive mysql database to do the distributed deployment, and provide users with transparent access, so that we can not only improve the user experience, but also save the cost, improve the stability of the system, and support long-term business development. The company has gradually adopts DAL, the system is stable, and the throughput and response performance improves significantly. |
