后台解析CSV文件解决8000行以上上传超时问题的方案咨询

1. 前端逻辑优化（解决前端大文件解析卡顿+提交后无需等待） #

你原有前端在本地解析全量CSV生成JSON再提交的逻辑在文件过大时本身就会造成前端卡顿，且提交的payload体积过大，先做如下修改：

• 移除前端遍历全量CSV生成real_data的逻辑，改为只提交字段映射配置和原始CSV文件
• 改用AJAX异步提交表单，提交成功后直接提示用户后台处理中，无需等待

改后前端代码示例：

<!--CSV UPLOAD BUTTON-->
<button type="button" class="btn btn-primary float-right" data-toggle="modal" data-target="#exampleModal" style="height:48px;width:150px;">
  CSV Upload
</button>

<!-- CSV MODAL -->
<div class="modal fade" id="exampleModal" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
  <div class="modal-dialog modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title" id="exampleModalLabel">Upload Customers CSV file</h5>
        <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
      </div>
      <form enctype="multipart/form-data" id="csv-upload">
        <input name="_token" type="hidden" value="{{ csrf_token() }}"/>
        <div class="modal-body">
          <div class="row" width="100%" height="80px">
            <div class="col-md-6">
              <input type="file" name="csv_customers" accept=".csv,.txt" required>
              <br><strong>*</strong><small> = REQUIRED</small><br>
            </div>
          </div>
          <div id="dvCSV" class="table-responsive" style="max-height: 600px; display:none;"></div>
          <div id="fieldsCSV" style="display:none ">
            <div class="row" style="margin-top: 1px">
              <p class="col-sm-6" style="padding-top:5px">Customer Email<strong>*</strong></p>
              <select class="col-sm-6" data-style="btn btn-link" name="customer_email" id="customer_email" required></select>
            </div>
            <div class="row" style="margin-top: 1px">
              <p class="col-sm-6" style="padding-top:5px">Customer First Name<strong>*</strong></p>
              <select class="col-sm-6" data-style="btn btn-link" name="customer_firstname" id="customer_firstname" required></select>
            </div>
            <div class="row" style="margin-top: 1px">
              <p class="col-sm-6" style="padding-top:5px">Customer Last Name</p>
              <select class="col-sm-6" data-style="btn btn-link" name="customer_lastname" id="customer_lastname"></select>
            </div>
            <div class="row" style="margin-top: 1px">
              <p class="col-sm-6" style="padding-top:5px">Customer Phone<strong>*</strong></p>
              <select class="col-sm-6" data-style="btn btn-link" name="customer_phone" id="customer_phone" required></select>
            </div>
            <div class="row" style="margin-top: 1px">
              <p class="col-sm-6" style="padding-top:5px">Customer Address<strong>*</strong></p>
              <select class="col-sm-6" data-style="btn btn-link" name="customer_address" id="customer_address" required></select>
            </div>
            <div class="row" style="margin-top: 1px">
              <p class="col-sm-6" style="padding-top:5px">Customer City<strong>*</strong></p>
              <select class="col-sm-6" data-style="btn btn-link" name="customer_city" id="customer_city" required></select>
            </div>
            <div class="row" style="margin-top: 1px">
              <p class="col-sm-6" style="padding-top:5px">Customer State<strong>*</strong></p>
              <select class="col-sm-6" data-style="btn btn-link" name="customer_state" id="customer_state" required></select>
            </div>
            <div class="row" style="margin-top: 1px">
              <p class="col-sm-6" style="padding-top:5px">Customer Zip<strong>*</strong></p>
              <select class="col-sm-6" data-style="btn btn-link" name="customer_zip" id="customer_zip" required></select>
            </div>
          </div>
        </div>
        <div class="modal-footer">
          <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
          <button type="submit" class="btn btn-primary">Submit</button>
        </div>
      </form>
    </div>
  </div>
<div>

<script>
  var uploadedHeader = []
  $('input[name="csv_customers"]').change(function() {
    var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.csv|.txt)$/
    var option = ''
    if (regex.test($(this).val().toLowerCase())) {
      if (typeof (FileReader) != "undefined") {
        $('body').removeClass('loaded');
        var reader = new FileReader()
        reader.onload = function (e) {
          // 只读取第一行列名，不需要读全量数据
          var firstLine = e.target.result.split("\n")[0]
          uploadedHeader = firstLine.split(",")
          option += "<option value=-1>--</option>"
          for (var j = 0; j < uploadedHeader.length; j++) {
            option += "<option value="+j+">"+uploadedHeader[j]+"</option>"
          }
          $('select').html(option)
          $('#fieldsCSV').show()
          $('body').addClass('loaded');
        }
        // 只读取文件前1KB足够获取表头，避免大文件前端读取卡顿
        reader.readAsText($('input[name="csv_customers"]')[0].files[0].slice(0, 1024))
      } else {
        md.showNotification('top','right', 'This browser does not support HTML5.')
      }
    }
  })

  $('#csv-upload').submit(function(e){
    e.preventDefault()
    $('body').removeClass('loaded');
    let formData = new FormData(this)
    $.ajax({
      url: "{{ url('contacts/upload-csv') }}",
      method: 'POST',
      data: formData,
      processData: false,
      contentType: false,
      success: function(res) {
        $('body').addClass('loaded');
        $('#exampleModal').modal('hide')
        md.showNotification('top','right', 'CSV已提交后台处理，可在导入任务列表查看进度')
        // 可在这里跳转任务列表页，或者留在当前页都不影响后台处理
      },
      error: function() {
        $('body').addClass('loaded');
        md.showNotification('top','right', '提交失败，请重试')
      }
    })
  })
</script>

2. 后端改造核心逻辑 #

2.1 新增导入任务状态表 #

建表存储导入任务的全生命周期信息，字段参考：

2.2 上传接口改造 #

上传接口接收到请求后执行以下操作：

1. 验证参数合法性，校验必填字段映射是否配置
2. 存储CSV文件到服务器磁盘/对象存储
3. 新增一条任务记录到任务表，状态设为待处理
4. 将任务推送到异步队列，立刻返回成功响应，不需要等待处理完成

2.3 异步队列消费逻辑 #

使用对应框架的异步队列组件（如Laravel队列、Django Celery、Spring Boot Task等）编写消费逻辑：

1. 取出任务记录，更新状态为处理中
2. 流式读取CSV文件，逐行解析，按字段映射组装数据
3. 批量写入数据库（建议每100-500条执行一次批量插入，性能远高于单条插入）
4. 处理过程中定时更新任务的进度、成功行数
5. 全部处理完成后更新任务状态为成功，若中途报错则更新状态为失败并记录错误信息

3. 附加功能（可选） #

• 新增导入任务列表页，用户可查看所有历史导入任务的进度、状态、处理结果，失败任务支持下载错误行明细
• 新增全局通知，任务处理完成后给用户发站内信/通知提示
• 导入前做重复数据校验，避免重复插入

内容的提问来源于stack exchange，提问作者Jeff Sasse