node.js - 将超过 300 万数据迁移到 MongoDB

Question

我正在尝试将 mysql 数据移动到 MongoDB。该记录超过 300 万行。每次它都会耗尽内存，即使在修改我的 Node 启动脚本之后也是如此

"scripts": {
    "debug": "node debug app.js",
    "start": "node --max_old_space_size=5120 --optimize_for_size --max_executable_size=5120 --stack_size=5120 app.js",
    "test": "node ./node_modules/mocha/bin/mocha test/bootstrap.test.js "
}

我尝试使用普通的回调函数和 promise ，但仍然没有任何结果。

整个功能

var citiesInit = function(_setup) {
  var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : 'password',
    database : 'WorldCities'
  });
  connection.connect();
  connection.query('SELECT * FROM thecities', function(err, rows, fields){
    if (err) {
      console.log("The error: ", err);
    }
    var cityData = [];

    rows.forEach(function(theCity){
      var data = {
        name: theCity.city,
        state_code: theCity.region,
        country_code: theCity.country,
        population: theCity.population,
        location: {
            type: "Point",
            coordinates: [parseFloat(parseFloat(theCity.longitude).toFixed(4)), parseFloat(parseFloat(theCity.latitude).toFixed(4))]
        },
        max_latitude: parseFloat(parseFloat(theCity.latitude).toFixed(4)),
        max_longitude: parseFloat(parseFloat(theCity.longitude).toFixed(4)),
        min_latitude: parseFloat(parseFloat(theCity.latitude).toFixed(4)),
        min_longitude: parseFloat(parseFloat(theCity.longitude).toFixed(4))
      }

      var cityCreate = City.create(data);
      cityData.push(cityCreate);
    })

    console.log('The Saved row is: ', cityData.length);

    //With Promise
    Promise.all([cityData]).spread(function (cityData){
      if (cityData) {
        console.log(cityData.length);
        Setup.findOrCreate({slug: 'city', setup: true}, {name: 'City', slug: 'city', setup: true}).exec(function cb(err, setup){
            console.log("Cities initialized successfully");
        });
      }
    })
    .catch(function (err){
      console.log(err);
    })

    //Without Promise
    City.create(cityData).exec(function cityCB(err, cities){
      if (err) {
        console.log(err);
      }

      if (cities.length) {
        console.log(cities.length);
        Setup.findOrCreate({slug: 'city', setup: true}, {name: 'City', slug: 'city', setup: true}).exec(function cb(err, setup){
            console.log("Cities initialized successfully");
        });
      }
    })
  });
};

我也尝试过使用Streams

connection.query('SELECT * FROM thecities')
    .stream()
    .pipe(stream.Transform({
      objectMode: true,
      transform:function(data, encoding, callback){
        var data = {
          name: data.city,
          state_code: data.region,
          country_code: data.country,
          population: data.population,
          location: {
              type: "Point",
              coordinates: [parseFloat(parseFloat(data.longitude).toFixed(4)), parseFloat(parseFloat(data.latitude).toFixed(4))]
          },
          max_latitude: parseFloat(parseFloat(data.latitude).toFixed(4)),
          max_longitude: parseFloat(parseFloat(data.longitude).toFixed(4)),
          min_latitude: parseFloat(parseFloat(data.latitude).toFixed(4)),
          min_longitude: parseFloat(parseFloat(data.longitude).toFixed(4))
        }

        City.create(data).exec(function cityCB(err, city){
          if (err) {
            console.log(err);
          }

          //if (city.state) { console.log(city.state) }
          callback();
        })
      }
    }))
    .on('finish', function(){
      connection.end();
      Setup.findOrCreate({slug: 'city', setup: true}, {name: 'City', slug: 'city', setup: true}).exec(function cb(err, setup){
          console.log("Cities initialized successfully");
          organizationInit();
      });
    })

关于如何解决这个问题有什么帮助吗？谢谢。

Answer 1

MySQL 支持游标批量获取较小数量的查询以节省内存:

• https://dev.mysql.com/doc/refman/5.7/en/declare-cursor.html
• https://dev.mysql.com/doc/refman/5.7/en/fetch.html

请参阅How to loop through a table using a cursor in MySQL?