select_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: 'example',
    charset:  'UTF8MB4_0900_AI_CI',
    connectionLimit: 100,
    queueLimit: 0,
  });
  return db;
}

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

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

  db = createDb();
  start = 1;
  end = start + count - 1; // 1 ～ count
  console.time(`不开启事务循环查询${count}条`);
  for (let index = start; index <= end; index++) {
    await db.query('SELECT * FROM example WHERE id = ?', [index]);
    if (index % 100 === 0) {
      console.log(`已查询${index - start + 1}条`);
    }
  }
  console.timeEnd(`不开启事务循环查询${count}条`);
  // 不开启事务循环查询10000条: 12.673s
  await db.end();

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

  db = createDb();
  start = 20001;
  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('SELECT * FROM example WHERE id = ?', [index]);
    if (index % 100 === 0) {
      console.log(`已查询${index - start + 1}条`);
    }
  }
  await connection.commit();
  connection.release();
  console.timeEnd(`开启事务循环查询${count}条`);
  // 开启事务循环查询10000条: 12.519s
  await db.end();

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

  db = createDb();
  start = 30001;
  end = start + count - 1;
  const ids = [];
  for (let index = start; index <= end; index++) {
    ids.push(index);
  }

  console.time(`一次性批量查询${count}条`);
  await db.query('SELECT * FROM example WHERE id IN (?)', [ids]);
  console.timeEnd(`一次性批量查询${count}条`);
  // 一次性批量查询10000条: 55.135ms
  await db.end();

  console.log(`====== 一次性范围查询${count}条 ======`);

  db = createDb();
  start = 40001;
  end = start + count - 1;
  console.time(`一次性范围查询${count}条`);
  await db.query('SELECT * FROM example WHERE id BETWEEN ? AND ?', [start, end]);
  console.timeEnd(`一次性范围查询${count}条`);
  // 一次性范围查询10000条: 21.264ms
  await db.end();

})();