Shulou(Shulou.com)06/01 Report--

This article introduces the knowledge of "what is the performance tuning method of database report data export". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations! I hope you can read it carefully and be able to achieve something!

Report data export is a common and troublesome problem, this function is mainly placed in the management background, system operators often need to export some data for analysis. The difficulties encountered in the report export are the large amount of data and the time-consuming query, which is the most likely to lead to the system OOM or bring down the database.

How to solve the problem of slow query

Report data export is usually accompanied by complex sql statements and uses a lot of query conditions, so when designing tables, we will add redundant columns to reduce multi-table join queries. If it is a project in recent years, considering the rapid growth and large amount of data in the report, we may use separate tables to solve the problem, but the amount of data in a single table is still very large. And when you encounter complex query statements, you also need to look up multiple tables and aggregate the results, which is still a time-consuming operation.

At present, relational database is also divided into OLAP and OLTP database. Ali AnalyticDB (adb) belongs to analytical distributed database (OLAP), which is mainly used for data analysis and uses column storage to improve query efficiency. Adb is very suitable for report statistics query business.

How to reduce memory usage

Poi is an API class library that provides reading and writing excel files, but improper use can easily lead to the system OOM,miniexcel is written to solve this problem, of course, the author also considered another problem when writing miniexcel, that is, a large number of data query will take up too much memory, report data export is usually a large number of data, such as hundreds of thousands, millions, so miniexcel also provides paging query export function.

In addition, miniexcel also supports custom import and export. Miniexcel is a lightweight and scalable report import and export tool based on poi encapsulation, which supports .xls, .xlsx and csv formats.

Easyexcel is an open source excel import and export tool of Ali, and it is also packaged based on poi, so there is not much difference in performance between easyexcel and miniexcel. We tested this with easyexcel and miniexcel, and the comparison shows that miniexcel takes slightly more time to export and the size of the exported file is slightly better than easyexcel, in part because miniexcel gives up support for table styles.

At present, miniexcel is being promoted within the Onion Group, and the author will always maintain this project. Friends in the industry are very welcome to use it and work together to improve miniexcel.

Recently, our management background was frequently dropped by the operating system kill, and the author also got a copy of dump data from the operation and maintenance system, but since the dump data provided by the operation and maintenance staff was exported under normal conditions of the system, the reason could not be found out. However, from this data, we can see that under normal use, the heap memory consumed by the system is about 2.3g, most of which is consumed by mybatis and spring. The other memory-consuming operation is a data export operation, in which more than 500,000 records are stored in the heap, so the author speculates that the frequent hang of the system is caused by performing multiple data export tasks at the same time.

Although miniexcel provides the function of paging export, this feature is not widely used in our project for only one reason. Although using the paging export function can reduce the use of memory and avoid multiple people exporting large amounts of data at the same time, the system will consume too much memory and end up being kill by the operating system, but because the query is time-consuming, paging query will be more time-consuming. Therefore, the paging function is currently only used for paging query data export that can be indexed.

If we change the data query of report export operation from mysql database to query analytical data warehouse adb, and combine the paging export function provided by miniexcel with the high-performance query of adb, we can perfectly solve the problem that data export takes up a lot of memory and causes the system to hang up frequently. This is a recommendation for optimizing the business functions related to the export of report data.

This is the end of the content of "what is the performance tuning method for the export of database report data". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.