MySQL data export csv file – a lot

Recently, I encountered a demand for downloading files. Exporting the data in mysql to a file in csv format should be quite simple. However, the amount of data is too large, and there are millions of data in the table. Conditions of hundreds of thousands of data.

Step 1:

To optimize the sql index, return structure, and its own sql statement, the first thing that comes to mind is that if such a large amount of data is loaded into the memory at one time, the server must not be able to bear it, and an oom exception may occur. So we must reduce the amount of data, add paging, or stream loading data, you can use mysql cursor, set each return fetchSize=”8000″ and so on.

Both methods are used, and the result is that streaming loading can ensure that the heap memory remains within a certain controllable range, but the result is that the query speed will be affected to a certain extent, and the response interface is about one minute.

You can use the thread pool plus paging to make each thread execute different SQL statements in parallel, and finally merge the files.

Ado
controller:

@GetMapping("/getDownloadDtl")
    @Transactional(rollbackFor = Exception. class)
    public void sendFileStream(@RequestParam("period_td") String periodTd
            , @RequestParam("period_type") int periodType
            , @RequestParam("data_type") Integer dataType
            , @RequestParam("relativepath") String relativePath
            , @RequestParam(value = "area_name", required = false, defaultValue = "") String areaName
            , @RequestParam(value = "watchman_name", required = false, defaultValue = "") String watchmanName
            , @RequestParam(value = "dealer_shortname", required = false, defaultValue = "") String dealerShortName
            , @RequestParam(value = "employee_no", required = false, defaultValue = "") String employeeNo
            , HttpServletResponse response) {<!-- -->
// public void sendFileStream(@RequestBody ){<!-- -->

        //1 Confirm the file name
        String[] split = relativePath. split("/");
        String fileName = split[split. length - 1];

        //2 define the output stream

        BufferedOutputStream os = null;
        try {<!-- -->
            os = new BufferedOutputStream(response. getOutputStream());
        } catch (IOException e) {<!-- -->
            e.printStackTrace();
        }
        try {<!-- -->
            response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        } catch (UnsupportedEncodingException e) {<!-- -->
            e.printStackTrace();
        }
        //Arbitrary type of binary stream data
        response.setContentType("text/csv");

        //3 Get data 4 Transfer the file stream to the front end
        try {<!-- -->
            String meterHeader = mPageManger. getMeterHeader(dataType);
            os.write((meterHeader + "\r\
").getBytes());
            List<String> data = mPageManger.getData(periodTd, periodType, dataType, areaName, watchmanName, dealerShortName, employeeNo);
            for (String item : data) {<!-- -->
                os.write((item + "\r\
").getBytes());
            }
        } catch (Exception e) {<!-- -->
            e.printStackTrace();
        } finally {<!-- -->
            if (os != null) {<!-- -->
                try {<!-- -->
                    os. close();
                    System.gc();
                } catch (IOException e) {<!-- -->
                    e.printStackTrace();
                }
            }
        }
    }

pageManger

@Component
public class PageManger {<!-- -->

    @Autowired
    private DmkSalCluActionCallDtlMapper mDmkSalCluActionCallDtlMapper;

    @Autowired
    private DmkSalCluSecondtimeActionDtlMapper mDmkSalCluSecondtimeActionDtlMapper;

    int pageSize = 1000;


    public int getTotalCount(String periodTd, int periodType, Integer dataType, String areaName, String watchmanName, String dealerShortName, String employeeNo) {<!-- -->
        switch (dataType) {<!-- -->
            case 1:
                int countb = mDmkSalCluActionCallDtlMapper.getCountb(periodTd, periodType, areaName, watchmanName, dealerShortName, employeeNo);
                return countb;
            case 2:
                int countc = mDmkSalCluActionCallDtlMapper.getCountc(periodTd, periodType, areaName, watchmanName, dealerShortName, employeeNo);
                return countc;
            case 3:
                int countd = mDmkSalCluSecondtimeActionDtlMapper.getCountd(periodTd, periodType, areaName, watchmanName, dealerShortName, employeeNo);
                return countd;
        }
        return 0;
    }

    public List<String> getData(String periodTd, int periodType, Integer dataType, String areaName, String watchmanName, String dealerShortName, String employeeNo) {<!-- -->
        int totalCount = getTotalCount(periodTd, periodType, dataType, areaName, watchmanName, dealerShortName, employeeNo);
        // define the result set
        List<String> result = Collections. synchronizedList(new ArrayList<>());
        int pageNum = (totalCount + (pageSize - 1)) / pageSize;
        // create thread pool
        ExecutorService executor = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());
        // Submit the task
        List<Future<List<String>>> futures = new ArrayList<>();
        for (int i = 0; i < pageNum; i ++ ) {<!-- -->
            final int curPageStart = i * pageSize;
            switch (dataType) {<!-- -->
                case 1:
                    Future<List<String>> future1 = executor. submit(() -> {<!-- -->
                        List<DmkSalCluActionCallDtl> data1 = mDmkSalCluActionCallDtlMapper.findDmkSalCluActionCallDtlPage(periodTd, periodType, areaName, watchmanName, dealerShortName, employeeNo, curPageStart, pageSize);
                        List<String> collect = data1.stream().map(DmkSalCluActionCallDtl::toValue).collect(Collectors.toList());
                        return collect;
                    });
                    futures. add(future1);
                    break;
                case 2:
                    Future<List<String>> future2 = executor. submit(() -> {<!-- -->
                        List<DmkSalCluActionCallDtl> data2 = mDmkSalCluActionCallDtlMapper.findDmkSalCluActionCallDtlTimelyPage(periodTd, periodType, areaName, watchmanName, dealerShortName, employeeNo, curPageStart, pageSize);
                        List<String> collect = data2.stream().map(DmkSalCluActionCallDtl::toValue).collect(Collectors.toList());
                        return collect;
                    });
                    futures. add(future2);
                    break;
                case 3:
                    Future<List<String>> future3 = executor. submit(() -> {<!-- -->
                        List<DmkSalCluSecondtimeActionDtl> data3 = mDmkSalCluSecondtimeActionDtlMapper.findDmkSalCluSecondtimeActionDtlPage(periodTd, periodType, areaName, watchmanName, dealerShortName, employeeNo, curPageStart, pageSize);
                        List<String> collect = data3.stream().map(DmkSalCluSecondtimeActionDtl::toValue).collect(Collectors.toList());
                        return collect;
                    });
                    futures. add(future3);
                    break;
                default:
                    return result;
            }
        }
        // wait for all tasks to complete and get results
        for (Future<List<String>> future : futures) {<!-- -->
            try {<!-- -->
                result. addAll(future. get());
            } catch (InterruptedException | ExecutionException e) {<!-- -->
                e.printStackTrace();
            }
        }
        return result;
    }

    public String getMeterHeader(Integer dataType) {<!-- -->
        switch (dataType) {<!-- -->
            case 1:
                return DmkSalCluActionCallDtl.toTitle();
            case 2:
                return DmkSalCluActionCallDtl.toTitle();
            case 3:
                return DmkSalCluSecondtimeActionDtl.toTitle();
        }
        return "";
    }
}