insert_benchmark.js

'use strict';

const mysql = require('mysql2/promise');

// CREATE TABLE `example` (
//   `id` int unsigned NOT NULL AUTO_INCREMENT,
//   `title` varchar(1024) NOT NULL DEFAULT '',
//   `created_at` bigint NOT NULL,
//   PRIMARY KEY (`id`)
// ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;

function createDb() {
  const db = mysql.createPool({
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: '123456',
    database: 'mydb',
    charset:  'UTF8MB4_0900_AI_CI',
    connectionLimit: 100,
    queueLimit: 0,
  });
  return db;
}

(async () => {
  const count = 10000;
  let db;
  let start = 1;
  let end = start + count - 1;

  console.log(`====== 不开启事务循环插入${count}条 ======`);

  db = createDb();
  console.time(`不开启事务循环插入${count}条`);
  for (let index = start; index <= end; index++) {
    await db.query('INSERT INTO example (title, created_at) VALUES (?)', [[`title${index}`, Date.now()]]);
    if (index % 100 === 0) {
      console.log(`已插入${index - start + 1}条`);
    }
  }
  console.timeEnd(`不开启事务循环插入${count}条`);
  // 不开启事务循环插入10000条: 32.599s
  await db.end();

  console.log(`====== 开启事务循环插入${count}条 ======`);

  db = createDb();
  start = end + 1;
  end = start + count - 1;
  console.time(`开启事务循环插入${count}条`);
  const connection = await db.getConnection();
  await connection.beginTransaction();
  for (let index = start; index <= end; index++) {
    await connection.query('INSERT INTO example (title, created_at) VALUES (?)', [[`title${index}`, Date.now()]]);
    if (index % 100 === 0) {
      console.log(`已插入${index - start + 1}条`);
    }
  }
  await connection.commit();
  connection.release();
  console.timeEnd(`开启事务循环插入${count}条`);
  // 开启事务循环插入10000条: 13.927s
  await db.end();

  console.log(`====== 一次性批量插入${count}条 ======`);

  db = createDb();
  start = end + 1;
  end = start + count - 1;
  const rows = [];
  for (let index = start; index <= end; index++) {
    rows.push([`title${index}`, Date.now()]);
  }

  console.time(`一次性批量插入${count}条`);
  await db.query('INSERT INTO example (title, created_at) VALUES ?', [rows]);
  console.timeEnd(`一次性批量插入${count}条`);
  // 一次性批量插入10000条: 111.223ms
  await db.end();

})();